1 / 17

Unnormalized Form (UNF)

Unnormalized Form (UNF). student courses John CS363 CS334 CS323 Multi-Value attribute Common in reports. First Normal Form (1NF). No multi-value attributes Done when mapping E-R model to relational schema.

dane-dodson
Download Presentation

Unnormalized Form (UNF)

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. Unnormalized Form (UNF) student courses John CS363 CS334 CS323 • Multi-Value attribute • Common in reports

  2. First Normal Form (1NF) • No multi-value attributes • Done when mapping E-R model to relational schema

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

  4. Partial FDs Partial FDs If X, W  Y and X  Y Then X, W  Y is a partial FD Example: Sno, Bno  staffAddress, staffPhone Sno  staffAddress, staffPhone

  5. Full FDs A FD is a full FD if it’s not partial. A, B  X, Y is a full FD if A X, Y is false B  X, Y is false Sno  staffAddress, staffPhone Pno, Start  Rno, Rent, End

  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 (Partial on Primary Key) PAddress ---> PNo, ONo, OName RNo ---> RName ONo ---> Oname

  7. Table Instance RNo RName PNo PAddress Start Finish Rent Ono OName R101 John P1001 1001 main 1-1-04 12-31-04 350 O100 Tina R102 Mike P1001 1001 main 1-1-05 2-28-05 380 O100 Tina R103 Kay P1001 1001 main 3-1-05 12-31-05 380 O100 Tina R101 John P1002 2001 main 1-1-05 12-31-05 550 O110 Tony R105 Scot P1009 1009 first 1-1-04 5-31-04 350 O109 Tony PNo, Start ---> All other attributes (Partial on Primary Key) PNo ---> PAddress, ONo, OName Not in 2NF! Poor Performance Redundancy Inconsistency

  8. Decompose Lease into 2NF Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start Partial FD on Primary Key PNo ---> PAddress, ONo, OName Remove partial dependent on Primary Key Create a new relation Which attributes are in the new relation? All attributes in (both side of) the partial FD Lease1 (PNo, PAddress, ONo, OName) Which attributes are left in the original relation? PK remains the same Lease2 (RNo, RName, PNo, Start, Finish, Rent) PK and FK?

  9. Lease1 (PNo, PAddress, ONo, OName) Primary Key: PNo Alternate Key: PAddress Foreign Key: None (from this conversion Functional Dependencies: PNo ---> All other attributes PAddress ---> PNo, ONo, OName ONo ---> OName In 2NF? Lease2 (RNo, RName, PNo, Start, Finish, Rent) Primary Key: PNo, Start Alternate Key: PNo, Finish Foreign Key: PNo references Lease1 Functional Dependencies: PNo, Start ---> All other attributes PNo, Finish ---> All other attributes RNo ---> Rname In 2NF?

  10. Relation/Table Instances Lease Rno RName PNo PAddress Start Finish Rent Ono OName R101 John P1001 1001 main 1-1-04 12-31-04 350 O100 Tina R102 Mike P1001 1001 main 1-1-05 2-28-05 380 O100 Tina R103 Kay P1001 1001 main 3-1-05 12-31-05 380 O100 Tina R101 John P1002 2001 main 1-1-05 12-31-05 550 O110 Tony R105 Scot P1009 1009 first 1-1-04 5-31-04 350 O109 Tony Lease1 PNo PAddress ONo OName P1001 1001 main O100 Tina P1001 1001 main O100 Tina P1001 1001 main O100 Tina P1002 2001 main O110 Tony P1009 1009 first O109 Tony Lease1 = PNo, PAddress, Ono, OName (Lease)

  11. Removing Duplicates Lease1 PNo PAddress ONo OName P1001 1001 main O100 Tina P1001 1001 main O100 Tina P1001 1001 main O100 Tina P1002 2001 main O110 Tony P1009 1009 first O109 Tony Lease1 P1001 1001 main O100 Tina P1002 2001 main O110 Tony P1009 1009 first O109 Tony

  12. Lease RNo RName PNo PAddress Start Finish Rent Ono OName R101 John P1001 1001 main 1-1-04 12-31-04 350 O100 Tina R102 Mike P1001 1001 main 1-1-05 2-28-05 380 O100 Tina R103 Kay P1001 1001 main 3-1-05 12-31-05 380 O100 Tina R101 John P1002 2001 main 1-1-05 12-31-05 550 O110 Tony R105 Scot P1009 1009 first 1-1-04 5-31-04 350 O109 Tony Lease2 RNo RName PNo Start Finish Rent R101 John P1001 1-1-04 12-31-04 350 R102 Mike P1001 1-1-05 2-28-05 380 R103 Kay P1001 3-1-05 12-31-05 380 R101 John P1002 1-1-05 12-31-05 550 R105 Scot P1009 1-1-04 5-31-04 350 Lease2 = RNo, RName, RNo, Start, Finish, Rent (Lease)

  13. Some FDs are lost Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) PAddress, Start ---> All other attributes PAddress, Finish ---> All other attributes Lease1 (PNo, PAddress, ONo, OName) Lease2 (RNo, RName, PNo, Start, Finish, Rent)

  14. Example Relation R (A, B, C, D, E, F) Primary Key: A, B Alternate Key: None Foreign Key: None Functional Dependencies: A, B ---> All A ---> C B ---> E, F In 2NF? NO!

  15. Table Instance A B C D E F 1 x 10 100 cs se 1 y 10 200 cs ee 2 x 20 300 cs se 2 y 20 400 cs ee Functional Dependencies: A, B ---> All A ---> C B ---> E, F

  16. Decompose table R into 2NF R (A, B, C, D, E, F) Functional Dependencies: A, B ---> All A ---> C B ---> E, F R1 (A, C) Primary Key: A Alternate Key: None Foreign Key: None Functional Dependencies: A ---> C R2 (B, E, F) Primary Key: B Alternate Key: None Foreign Key: None Functional Dependencies: B ---> E, F R3 (A, B, D) Primary Key: A, B Alternate Key: None Foreign Key: A References R1 B References R2 Functional Dependencies: A, B ---> D

  17. Table Instance R A B C D E F 1 x 10 100 cs se 1 y 10 200 cs ee 2 x 20 300 cs se 2 y 20 400 cs ee R3 A B D 1 x 100 1 y 200 2 x 300 2 y 400 R1 A C 1 10 2 20 R2 B E F x cs se y cs ee

More Related