1 / 12

Normalization

Learn why normalization is important, what causes anomalies, the 4 normal forms, and how to normalize a relation to 3NF and beyond. Correct anomalies and design problems with normalization.

mikecarr
Download Presentation

Normalization

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

  2. Topics • Why normalization is needed • What causes anomalies • What the 4 normal forms are • How to normalize a relation to 3NF • Beyond 3NF - BCNF

  3. Why Normalization • This is a Non-Normal form since it is NOT a Relation: • Multi-valued attributes or “repeating groups”

  4. Why Normalization • Anomalies • Insertion Anomaly • Deletion Anomaly • Update Anomaly • To analyze design • To identify problems • To correct anomalies • Normalization allows us to start with a table and produce a new collection of tables that represent the same information but is free of problems.

  5. What Causes Anomalies • Existence of functional dependencies • Key:a set of one or more attributes in a relation that identifies uniquely ALL the other attributes in the relation • Functional Dependency: a set of one or more attributes in a relation that identifies uniquely SOME of the other attributes in the relation • Transitive Dependency:aspecial kind of functional dependency. A --> B --> C

  6. The 4 Normal Forms • 1NF (First Normal Form) • No repeating columns • 2NF (Second Normal Form) • 1NF + No sub-key => non-key • 3NF (Third Normal Form) • 2NF + No non-key => non-key • BCNF (Forth Normal Form) • 3NF + No non-key => sub-key

  7. Converting a Relation to 3NF - An Algorithm • Determine the key to relation T • Determine the FDs in relation T • For every FD whose LHS is not a key in relation T, obtain 2 relations T1 and T2 so that T1 contains all attributes of the FD, T2 contains all the attributes of T except the RHS attributes of the FD • If the FD is transitive (A-->B-->C), always decompose B-->C first

  8. Converting To 3NF - An Example • Key to the relation • Student_ID, Course_ID • FDs in the relation • STUDENT_ID ---> STUDENT_NAME, MAJOR • COURSE_ID ---> COURSE_TITLE, INSTRUCTOR_NAME -->OFFICE • STUDENT_ID + COURSE_ID ---> GRADE

  9. Converting To 3NF - An Example

  10. Converting To 3NF - An Example

  11. Converting To 3NF - An Example

  12. Boyce-Codd BCNF • Every determinant is a key (3NF + no non-key -->subkey) • A student can have 1 or more majors • A student has 1 advisor per major • A major can have 1 or more advisors • An advisor advises 1 major • An advisor can advise 1 or more students in the major

More Related