Transcript BCNF and 3NF Decomposition

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 BCNF
Decomposition:
C, T
C, S, G
H, R, C
H, R, S
 Lost Dependency: HT  R




 List of subschema after 3NF
Synthesis:





C, T
C, S, G
H, R, C
H, S, R
H, T, R
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 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
 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
Test case 3
 R = ABCDE
 F = { AB --> C , CD --> B , BC --> E }
 List of subschema after
BCNF Decomposition:
 List of subschema after
3NF Synthesis:
 B, A, D
 A, B, C
 B, C, E
 C, D, B
 A, B, C
 B, C, E
 Lost Dependency: CD  B
 C, A, D
Test case 4
 R = ABCDEG
 F = { CD --> B , BE --> A }
 List of subschema after
BCNF Decomposition:
 C, D, B
 C, D, E, A
 E, G, C, D
 Lost Dependency: BE  A
 List of subschema after
3NF Synthesis:
 C, D, B
 B, E, A
 E, G, C, D
Example - NFL
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)
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)
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
3NF
 Same as BCNF
UML Design
Relational schema from UML
 Player:
 PlayerID
 PlayerName
 Position
 Team:
 SeasonStats:






 FranchiseName
 City
 Mascot
 Conference
Season
FranchiseName
Coach
Wins
Losses
Ties
 PlayerStats:




PlayerID
Season
FranchiseName
JerseyNo
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 )
Bank example
Attributes:
 Cname (C)
 Street (S)
 City (T)
 Accno (A)
 Bname (B)
 Balance (N)
 Bcity (Y)
 Assets (E)
 LoanNo (L)
 LoanAmt (M)
Dependencies
 Cname -> Street, City
 Accno -> Banme, Cname, Balance
 Bname -> Bcity, Assets
 LoanNo -> Bname, Cname, LoanAmt
BCNF Decomposition
 Customer
 Cname
-Loan
LoanNo
LoanAmt
 Street
 Branch
 Bname
 Bcity
 Assets
 Deposit
 Bname
 Balance
 Cname
 AccNo
-???
AcctNo
LoanNo
3NF
 Same as BCNF
UML
UML to Relational
 Customer( Cname,Street, City)
 Deposite (Accno ,Bname, Cname, Balance)
 Branch (Bname , Bcity, Assets)
 Borrow (LoanNo, Bname, Cname, LoanAmt)
Thank You