Lecture 6 ----------- Announcements --------------- - Hw1 due today at midnight, Hw2 to be given out tomorrow, due next thursday - My office hours from 4-4:45PM today only. Online office hours will start at 6PM. Normalization: 3NF/BCNF decompositions, 4NF and 4NF decomposition --------------------------------------------------------------------- 3NF Decomposition -------------------- Suppose R is a relation that is not in 3NF with respect to F. Assume that F is in minimal cover form (if not, convert to minimal cover). 1. For each functional dependency X->Y in F, and create a new relation with attributes X,Y. 2. If relation R1 is completely contained in R2 (in terms of attributes), remove R1. 3. If there is no relation that contains all the attributes in a key, then create one extra relation that contains all the attributes in one of the keys. ---- This decomposition guarantees the resulting relations are in 3NF. + it is lossless + dependency preserving! --------------------------------- R1(A,B,C,D,E,F,G,H) B->CD AD->EF F->A DF->G Keys: ABH, BFH R11(B,C,D) {B->CD} Key: B, in 3NF, BCNF R12(A,D,E,F) {AD->EF, F->A} Key: AD, FD, in 3NF, not in BCNF --> R13(A,F) {F->A} remove not needed R14(D,F,G) {DF->G} Key: DF, in 3NF, BCNF R15(B,F,H) {} Key: BFH, in 3NF, BCNF Check lossless: F = {B->CD, AD->EF, F->A, DF->G} Decomposition: BCD ADEF DFG BFH A B C D E F G H a1 b c d e1 f1 g1 h1 a b2 c2 d e f g2 h2 a3 b3 c3 d e3 f g h3 <- a4 b c4 d4 e4 f g4 h <- F->A A B C D E F G H a1 b c d e1 f1 g1 h1 a b2 c2 d e f g2 h2 <- a3 b3 c3 d e3 f g h3 <- a3 b c4 d4 e4 f g4 h F->A A B C D E F G H a1 b c d e1 f1 g1 h1 a b2 c2 d e f g2 h2 a b3 c3 d e3 f g h3 <- a3 b c4 d4 e4 f g4 h <- F->A A B C D E F G H a1 b c d e1 f1 g1 h1 a b2 c2 d e f g2 h2 a b3 c3 d e3 f g h3 a b c4 d4 e4 f g4 h --------------------------------- BCNF Decomposition ------------------ Suppose R is a relation that is not in BCNF with respect to F. Assume that F is in minimal cover form (if not, convert to minimal cover). Apply the following recursively until all resulting relations are in BCNF: - Find X->Y in F that violates BCNF. Then create two new relations: R1 contains all attributes in X+ R2 contains all attributes in R except (X+-X) ---- This decomposition guarantees the resulting relations are in BCNF. + it is lossless + NOT necessarily dependency preserving! R(A,B,C,D) F={A->B, B->C, C->D} Key:A B->C violates BCNF B+ = {B,C,D} B+-{B} = {C,D} B->CD R11(B,C,D) {B->C,C->D} Key: B, not in BCNF C->D violates R11 R111(C,D) {C->D} Key: C, in BCNF R112(B,C) {B->C} Key: B, in BCNF R12(A,B) {A->B} Key:A, in BCNF Result: (AB) (BC) (CD) R2(A,B,C,D,E,F,G) AB->CD D->EF x violate BCNF F->AG x violate BCNF Key: AB, FB, BD D->EF D+ = {D,E,F,A,G} D->EFAG R21(A,D,E,F,G) {D->EF, F->AG}, Key: D, not in BCNF F->AG violates BCNF R211(A,F,G) {F->AG} in BCNF R212(D,E,F) {D->EF} in BCNF R22(B,C,D) {BD->C} Key: BD, in BCNF Result: Rx(A,F,G) Fx = {F->AG} Ry(D,E,F) Fy = {D->EF} Rz(B,C,D) Fz = {BD->C} Dependency preserving? Fx union Fy union Fz ≡ F Fnew ≡? F {F->AG, D->EF, BD->C} ≡? {AB->CD, D->EF, F->AG} F implies Fnew (given!) Is everything in F implied by Fnew? AB->CD ? Given Fnew={F->AG, D->EF, BD->C}, AB+ = {A,B} CD is not in the closure, so AB->CD is lost! D->EF yes! in Fnew F->AG yes! in Fnew ---------------------- TVShows(Showname, StartYear, EndYear, Actor, Role, DateOfBirth, JoinedCast, LeftCast, Genre) Showname -> StartYear EndYear Actor -> DateOfBirth ShowName Actor Role -> JoinedCast LeftCast ShowName ->> Genre Key: ShowName, Actor, Role, Genre (ShowName, StartYear, EndYear) (Actor, DateOfBirth) (ShowName, Actor, Role, JoinedCast, LeftCast) (ShowName, Actor, Role, Genre) not in 4NF 4NF: (Showname, Actor, Role) (Showname, Genre) ---------------------------------- Characters(Name, Power, Ability) Yoshi Flutter Lay Egg Yoshi Eat Eggs Sacrifice Yoshi Eat Eggs Fireballs Students(RIN, Hobby, PhoneNum, PhoneType) 1111 Beekeeping 1234 Mobile 1111 Skydiving 5678 Mobile Multivalued dependencies: X->>Y 1) If Y is a subset of X, then X->> Y (Trivial), or X union Y is all the attributes in R 2) X->> Y, Y->> Z then X->> Z 3) no splitting of combining 4) If X->>Y and Z is the remaining set of attributes in R, X->>Z 5) All f.d.s can be promoted to an MVD (If X->Y then X->> Y) Students(RIN, Hobby, PhoneNum, PhoneType) RIN ->> Hobby RIN ->> PhoneNum PhoneType MVDs do not change the key! A relation is in 4NF if for all non-trivial MVDs, the left hand side is a superkey. --------------- A person can have many hobbies A person can have many phones StudentInfo(RIN, Hobby, PhoneNum, WhentoUsePhone) RIN ->> Hobby RIN ->> PhoneNum WhenToUsePhone Key: RIN, Hobby, PhoneNum, PhoneType In BCNF, not in 4NF because RIN is not a superkey 4NF decomposition: S1(RIN, Hobby) RIN->> Hobby Key: RIN, Hobby S2(RIN, PhoneNum, WhentoUsePhone) RIN ->> PhoneNum WhenToUsePhone Key: RIN, PhoneNum, WhentoUsePhone