190 likes | 350 Views
Second Normal Form (2NF). A relation R is in 1NF, and every non-primary-key attribute is fully functionally dependent on the primary key Then R is in 2NF. No Partial FDs on PK. Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start FDs:
E N D
Second Normal Form (2NF) A relation R is in 1NF, and every non-primary-key attribute is fully functionally dependent on the primary key Then R is in 2NF. No Partial FDs on PK.
Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start FDs: PNo, Start ---> RNo, RName, PAddress, Finish, Rent, ONo, OName PNo ---> PAddress, ONo, OName Lease1 (PNo, PAddress, ONo, OName) Primary Key: Pno Alternate Key: None Foreign Key: None FDs: PNo ---> PAddress, ONo, OName PAddress Pno ONo ---> OName Lease2 (RNo, RName, PNo, Start, Finish, Rent) Primary Key: PNo, Start Alternate Key: None Foreign Key: PNo References Lease1 FDs: PNo, Start ---> RNo, RName, Finish, Rent RNo ---> RName
Lease1 (PNo, PAddress, ONo, OName) Primary Key: PNo FDs: PNo ---> PAddress, ONo, OName PAddress PNo ONo ---> OName Table Instance PNo PAddress ONo OName P1001 1001 main O100 Tina P1002 2001 main O109 Tony P1009 1009 first O109 Tony P2009 2009 first O109 Tony In 2NF But still some Redundancy Reason? Transitive Functional Dependency on PK PNo ---> Ono, ONo ---> OName PNo ---> Oname No cycle: Not Ono ---> Pno and Not Oname ---> PNo
Third Normal Form (3NF) Relation R in 2NF, and No non-Primary-Key attribute is transitively functionally dependent on the primary key Then R is in 3NF. No Transitive FDs on PK.
Lease1 (PNo, PAddress, ONo, OName) Primary Key: PNo FDs: PNo ---> PAddress, ONo, OName PAddress ---> PNo ONo ---> OName Table Instance PNo PAddress ONo OName P1001 1001 main O100 Tina P1002 2001 main O109 Tony P1009 1009 first O109 Tony P2009 2009 first O109 Tony Not in 3NF! PNo ---> ONo, Oname Ono --> Oname (Oname is transitively functionally dependent on Pno)
Decompose Lease1 into 3NF Lease1 (PNo, PAddress, ONo, OName) PNo ---> PAddress, ONo, OName PAddress ---> PNo ONo ---> OName Lease11 (ONo, OName) Primary Key: Ono Alternate Keys: None Foreign Keys: None FDs: ONo ---> Oname Better table name? Owner Lease12 (PNo, PAddress, ONo) Primary Key: PNo Alternate Keys: PAddress Foreign Keys: Ono references Lease11 FDs: PNo ---> PAddress, ONo PAddress Pno Better table name? Property
Table Instances Lease1 PNo PAddress ONo OName P1001 1001 main O100 Tina P1002 2001 main O109 Tony P1009 1009 first O109 Tony P2009 2009 first O109 Tony Lease12 PNo PAddress Ono P1001 1001 main O100 P1002 2001 main O109 P1009 1009 first O109 P2009 2009 first O109 Lease11 ONo OName O100 Tina O109 Tony
Relation R R (A, B, C, D, E, F) Primary Key: A, B Alternate Keys: None Functional Dependencies: A, B ---> C, D, E, F C ---> D E ---> F Is it in 2NF? Is it in 3NF?
Table Instance A B C D E F 1 x 10 100 se 400 1 y 20 200 cis 1000 2 x 30 100 cis 1000 2 y 10 100 ct 400 A, B ---> C, D, E, F C ---> D E ---> F
R (A, B, C, D, E, F) PK: A, B AK: None FK: None FDs: A, B ---> C, D, E, F C ---> D E ---> F R3 (A, B, C, E) Primary Key: A, B Alternate Keys: None Foreign Key: C References R1 E References R2 Functional Dependencies: A, B ---> C, E Decompose R into 3NF R1 (C, D) Primary Key: C Alternate Keys: None Foreign Key: None Functional Dependencies: C ---> D R2 (E, F) Primary Key: E Alternate Keys: None Foreign Key: None Functional Dependencies: E ---> F
Table Instances R (A, B, C, E) A B C E 1 x 10 se 1 y 20 cis 2 x 30 cis 2 y 10 ct R (A, B, C, D, E, F) A B C D E F 1 x 10 100 se 400 1 y 20 200 cis 1000 2 x 30 100 cis 1000 2 y 10 100 ct 400 R1 (C, D) C D 10 100 20 200 30 100 R2 (E, F) E F se 400 cis 1000 ct 400
Assignment 6 (I) Due beginning of class Wednesday
Assignment 5 (II) 2NF and 3NF Part II: 5 points Due: Friday
Quiz 2 • Friday, March 4 • Derive table schemas from E-R Model (Mapping E-R Model to Database Schema) • DBDL • Functional Dependency Assignment 5-1 and 6-1 • 20 points
Test 1 • Friday, March 11 • Relational Data Model • Database Design Including normalization (Assignment 5-2 and 6-2) • 50 points