1 / 23

Normal Forms

Normal Forms. By Christopher Archibald October 16 th 2007. Overview. Database Normalization 1 st Normal Form 2 nd Normal Form 3 rd Normal Form Boyce- Codd Normal Form (BCNF) Lossless-Join. Normalization. Normalization is a technique for designing relational table to:

phong
Download Presentation

Normal Forms

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. Normal Forms By Christopher Archibald October 16th 2007

  2. Overview • Database Normalization • 1st Normal Form • 2nd Normal Form • 3rd Normal Form • Boyce- Codd Normal Form (BCNF) • Lossless-Join

  3. Normalization • Normalization is a technique for designing relational table to: • Minimize duplication of information • Reduce the potential for data anomalies

  4. Normal Form • Normal forms provide a stepwise progression toward the goal of fully normalized relation schema that are free for data redundancies.

  5. First Normal Form (1NF) • 1NF definition: A schema R is in 1NF only when the attributes comprising the schema are atomic and single-valued • No Multi-valued attributes • No composite attributes • No repeating groups (2 columns can not store similar information) • Can’t have a Null Attribute • Must have a Primary Key

  6. First Normal Form Example • This is in 1NF (Has primary Key, no repeating group, No Null attributes and No multivariable • What happens if James gets a Second Phone Number?

  7. First Normal FormExample • No longer in 1NF because Telephone Number has a multivariable. • Now we need to redesign our table

  8. First Normal FormExample • Not in First Normal forum • Tel. No. 3 is a null attribute • Tel. No. 1-2 repeat similar information (Repeating group)

  9. First Normal Form • This is in First Normal Form • Telephone Number is no long a repeating group • No Multivariable • No Null Attributes • Has a Primary Key

  10. Second Normal Form (2NF) • 2NF Definition: A relation schema R is in 2NF if every non-prime attribute in R is fully functionally dependent on the primary key of R. • Must be 1NF • An Attribute that is not part of the candidate key must be dependent on the candidate key and not a part of the candidate key

  11. Second Normal FormExample • Only Candidate key is (Employee, Skill) • Not in 2NF • Current Work Location is dependent on Employee • Can Cause an Anomaly Updating Jones Work location for Typing and Shorthand but not Whittling. Then asking “What is Jones current work location”, can cause a contradictory answer, because there are 2 different locations.

  12. Second Normal Form Example • Both tables are in 2NF • Meets 1NF requirements • No non-primary key attribute is dependent on part of a key.

  13. 1NF and 2NF • 1NF and 2NF remove most anomalies • Following table is in 2NF • There is redundancy under Winner/Winner DoB • Al Fredrickson and Chip Masterson • Can cause an anomaly

  14. Third Normal Form (3NF) • 3NF Definition: A relation schema R is in 3NF if no non-prime attribute is functionally dependent on another non-prime attribute in R • Table must be in 2NF • Eliminate field that do not depended on the primary key by placing them in different tables

  15. Third Normal FormExample • Table is in 2NF but fails to meet 3NF • Winner Date of Birth is Dependent on Winner • If Al Fredrickson Date of birth is update in the first row but not the second ask, “What Al Fredrickson Date of birth” will result in 2 different dates.

  16. Third Normal FormExample • Table is in 3NF • Meets 1NF and 2NF • No non-primary Key attribute is Dependent on another non-primary Key attribute • Update Anomalies cannot occur in these tables

  17. Boyce-Codd Normal Form (BCNF) • BCNF Definition: A relation Schema R is in BCNF if for every non-trivial functional dependency in R, the determinant is a superkey of R • Does not allow Functional Dependency that is not part of a Candidate key • Most 3NF meet the requirement of a BCNF

  18. Boyce-Codd Normal FormExample • Candidate key (Tutor ID, Student ID) And (SSN, Student ID) • Table is in 3NF, but not BCNF • SNN is dependent on Tutor ID but (Tutor id, SNN) is not a Candidate key

  19. Other Normal Forms • There is also • Fourth normal form • Fifth normal Form • Domain/key Normal form • Sixth normal form • Which will be covered in chapter 9

  20. Lossless-Join Decomposition • The principle behind Lossless-Join decomposition is that the decomposition of a relation schema, R, should be strictly reversible, i.e. • When we break tuples in to different tables for normalization we should be able to combined them and get what we started

  21. Lossless-Join Decomposition

  22. Lossless-Join Decomposition

  23. Lossless-Join Decomposition

More Related