1 / 20

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 the PK. Third Normal Form (3NF). Relation R in 2NF, and

thalia
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 the PK.

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

  3. Boyce-Codd Normal Form (BCNF) Definition R in 1NF and Every determinant (the left side of a FD) is a candidate key.

  4. BCNF and 3NF BCNF is stronger than 3NF If R in BCNF, then R in 3NF. If R not in 3NF, then R not in BCNF.

  5. Proof If R not in 3NF, then PK ---> B, and B ---> C, (PK ---> C) NO cycle for transitive FD, i.e. B ---> PK : False B is not candidate key but a determinant (B ---> C ) So, R is not in BCNF.

  6. Example Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start Alternate Key: PNo, Finish PAddress, Start PAddress, Finish FDs: PNo, Start ---> All other attributes PNo, Finish ---> All other attributes PAddress, Start ---> All other attributes PAddress, Finish ---> All other attributes PNo ---> PAddress, ONo, OName (Pno not a candidate key) PAddress ---> PNo, ONo, Oname (Paddress not a candidate key) RNo ---> Rname (Rno not a candidate key) ONo ---> OName (Ono not a candidate key) Not in BCNF. How many tables?

  7. Decompose Lease into BCNF Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) PNo ---> PAddress, ONo, OName (Pno not a candidate key) PAddress ---> PNo, ONo, Oname (Paddress not a candidate key) RNo ---> Rname (Rno not a candidate key) ONo ---> OName (Ono not a candidate key) Owner (ONo, OName) ONo ---> Oname Renter (RNo, RName) RNo ---> RName Property (PNo, PAddress, ONo) PNo ---> PAddress, ONo PAddress ---> PNo, Ono Only 4 tables, not 5. Ono will not be in Lease. Lease (RNo, PNo, Start, Finish, Rent) PNo, Start ---> All PNo, Finish ---> All

  8. Example R (A, B, C, D, E) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C  All B, C, D  All B, D  A

  9. Table Instance A B C D E 2 10 x u ct 1 20 y v cis 2 10 z u se 1 20 x v cs FDs: A, B, C  All B, C, D  All B, D  A

  10. Decomposing to BCNF R (A, B, C, D, E) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C  All B, C, D  All B, D  A B, D and A should be in a new table with (B, D) as PK B and D should remain in the original table as FK A should not remain in the original table PK must be changed to B, C, D.

  11. Decomposing to BCNF R1 (A, B, D) PK: B, D AK: NONE FK: None FDs: B, D  A R (A, B, C, D, E) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C  All B, C, D  All B, D  A R2 (B, C, D, E) PK: B, C, D AK: NONE FK: B, D References R1 FDs: B, C, D  All

  12. Table Instance A B C D E 2 10 x u ct 1 20 y v cis 2 10 z u se 1 20 x v cs A B D 2 10 u 1 20 v B C D E 10 x u ct 20 y v cis 10 z u se 20 x v cs

  13. Selecting B, C, D as PK at the Beginning R (A, B, C, D, E) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C  All B, C, D  All B, D  A R (A, B, C, D, E) PK: B, C, D AK: A, B, C FK: None FDs: A, B, C  All B, C, D  All B, D  A A is Partial on PK!

  14. Review: Normalization • 1NF Remove multi-value attributes Why: each element is not a set (first order logic) • 2NF Remove partial FDs on PK Why: remove redundant data • 3NF Remove transitive FDs on PK Why: remove redundant data • BCNF Strong requirement Any candidate keys In most cases, 3NF is enough.

  15. Lossless Decomposition After a relation is normalized into two or more relations, the original relations could be obtained by joining new relations Primary Key and Foreign Key

  16. Decompose Lease into BCNF Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Owner (ONo, OName) ONo ---> OName Renter (RNo, RName) RNo ---> RName Property (PNo, PAddress, ONo) PNo ---> PAddress, ONo PAddress ---> PNo, Ono Lease (RNo, PNo, Start, Finish, Rent) PNo, Start ---> All other attributes PNo, Finish ---> All other attributes How to get Property data for a lease? How to get Renter data for a lease? How to get Owner data for a lease?

  17. De-Normalization • Normalized relations Minimal redundancy Need join operation to get results • How far should we go? • Where to stop?

  18. Review: Database Design A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases • Conceptual database design Understanding client data E-R (EER) Model Contract between clients and designers • Logical database design Mapping E-R Model to (relational) database schema (Derive relational schema from E-R Model) DBDL Normalization • Physical database design

  19. Assignment 6-1Due WednesdayAssignment 5-2Due Friday

  20. Quiz 2 • Friday, March 4 • 20 points • Derive table schemas from E-R Model (Mapping E-R Model to Database Schema) • DBDL • Functional Dependency • Which Notes? • Assignment 4, 5-1 and 6-1

More Related