1 / 20

BCNF and 3NF Decomposition and UML Design

BCNF and 3NF Decomposition and UML Design. -By Kishor, Rohit, CK. Test case 1. R = CGHRST F = { C --> T , CS --> G , HR --> C , HS --> R , HT --> R }. List of subschema after 3NF Synthesis: C, T C, S, G H, R, C H, S, R H, T, R. List of subschema after BCNF Decomposition: C, T

maille
Download Presentation

BCNF and 3NF Decomposition and UML Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BCNF and 3NF Decompositionand UML Design -By Kishor, Rohit, CK

  2. Test case 1 • R = CGHRST • F = { C --> T , CS --> G , HR --> C , HS --> R , HT --> R } • List of subschema after 3NF Synthesis: • C, T • C, S, G • H, R, C • H, S, R • H, T, R • List of subschema after BCNF Decomposition: • C, T • C, S, G • H, R, C • H, R, S • Lost Dependency: HT  R

  3. Test case 2 • Address, Course, Department, Grade, Hour, Level, Name, Phone, Room, STudent-id, Teacher, Units • R = ACDGHLNPRSTU • F = { C --> TU , S --> NAP , T --> LD , CS --> G , CS --> G , HS --> R , HT --> R } • List of subschema after 3NF Synthesis: • T, L, D • C, T, U • S, N, A, P • C, S, G • H, S, R • C, H, S • H, T, R • List of subschema after BCNF Decomposition: • T, L, D • T, C, U • S, N, A, P • C, S, G • H, S, R • C, H, S • Lost Dependency: H, T R

  4. Test case 3 • R = ABCDE • F = { AB --> C , CD --> B , BC --> E } • List of subschema after 3NF Synthesis: • A, B, C • C, D, B • B, C, E • C, A, D • List of subschema after BCNF Decomposition: • B, A, D • B, C, E • A, B, C • Lost Dependency: CD  B

  5. Test case 4 • R = ABCDEG • F = { CD --> B , BE --> A } • List of subschema after 3NF Synthesis: • C, D, B • B, E, A • E, G, C, D • List of subschema after BCNF Decomposition: • C, D, B • C, D, E, A • E, G, C, D • Lost Dependency: BE  A

  6. Example - NFL

  7. Attributes • Franchise Name (A) • Player Name (B) • City (C) • Mascot (D) • Coach (E) • Conference (F) • Season (G) • Wins (H) • Losses (I) • Ties (M) • Jersey Number (J) • Player Position (K) • Player ID (L)

  8. Dependencies • (Franchise Name) -> (City, Mascot, Conference) • (Player Id) -> (Player Name, Player Position) • (Player Id, Season) -> (Franchise Name) • (Player Id, Franchise Name, Season) -> (Jersey Number) • (Season, Franchise Name) -> (Wins, Losses, Ties, Coach)

  9. BCNF Decomposition • Player Table • Player ID • Player Name • Position • Team Table • Franchise Name • City • Mascot • Conference • Player Season Stats • Player Id • Season • Franchise Name • Jersey Number • Team Season Stats • Franchise Name • Season • Coach • Wins • Losses • Ties

  10. 3NF • Same as BCNF

  11. UML Design

  12. Relational schema from UML • Player: • PlayerID • PlayerName • Position • Team: • FranchiseName • City • Mascot • Conference • SeasonStats: • Season • FranchiseName • Coach • Wins • Losses • Ties • PlayerStats: • PlayerID • Season • FranchiseName • JerseyNo

  13. UML to Relational Model • Player (Player ID, Player Name, hise Name, Season, Position) • Team (Franchise Name, City, Mascot, Coach, Conference, Season) • Standings (Team Name, Season, Conference, Wins, Losses, Ties, Rank )

  14. Bank example Attributes: • Cname (C) • Street (S) • City (T) • Accno (A) • Bname (B) • Balance (N) • Bcity (Y) • Assets (E) • LoanNo (L) • LoanAmt (M)

  15. Dependencies • Cname -> Street, City • Accno -> Banme, Cname, Balance • Bname -> Bcity, Assets • LoanNo -> Bname, Cname, LoanAmt

  16. BCNF Decomposition • Customer • Cname • Street • Branch • Bname • Bcity • Assets • Deposit • Bname • Balance • Cname • AccNo • -Loan • LoanNo • LoanAmt • -??? • AcctNo • LoanNo

  17. 3NF • Same as BCNF

  18. UML

  19. UML to Relational • Customer( Cname,Street, City) • Deposite (Accno ,Bname, Cname, Balance) • Branch (Bname , Bcity, Assets) • Borrow (LoanNo, Bname, Cname, LoanAmt)

  20. Thank You

More Related