1 / 18

Second Normal Form (2NF)

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:

micol
Download Presentation

Second Normal Form (2NF)

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. 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.

  2. 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

  3. 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

  4. 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.

  5. 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)

  6. 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

  7. 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

  8. 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?

  9. 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

  10. 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

  11. 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

  12. Assignment 3E-R Model

  13. Assignment 4

  14. Assignment 5-1

  15. Assignment 6 (I) Due beginning of class Wednesday

  16. Assignment 5 (II) 2NF and 3NF Part II: 5 points Due: Friday

  17. 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

  18. Test 1 • Friday, March 11 • Relational Data Model • Database Design Including normalization (Assignment 5-2 and 6-2) • 50 points

More Related