1 / 12

Normalization

This article provides a detailed overview of the normalization process in database design, specifically focusing on university course data. It explains the transformation of an unnormalized table into First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Through examples, we cover key concepts such as functional dependencies, the importance of primary keys, and the decomposition of tables to eliminate redundancy. By following these steps, database designers can ensure efficient data management that adheres to normalization principles.

louvain
Télécharger la présentation

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. Unnormalized Table • University Course No Course Desc StdSSN StdCity StdClass OfferNo OffTerm OffYear Grade Jun Jun Fall Fall Winter Fall 2000 2000 2000 2000 3.5 3.3 3.1 3.4 O1 O2 O3 O2 Co1 Co2 Co3 Co2 S1 S2 C1 C2 DB VB OO VB

  3. Relations Unnormalized • UNIVERSITY(StdSSN, StdCity, StdClass, (OfferNo, OffYear, Grade, CourseNo, CrsDesc)) 1NF • UNIVERSITY(StdSSN, StdCity, StdClass, OfferNo, OffYear, Grade, CourseNo, CrsDesc)

  4. Conversion to 1NF • No repeating group • Primary key will expand in converting a non-1NF table to 1NF

  5. Second Normal form • 1 NF. If every nonkey column is dependent on the whole key, not part of the key. • UNIVERSITY(StdSSN, StdCity, StdClass, OfferNo, OffYear, Grade, CourseNo, CrsDesc)

  6. Functional Dependencies • StdSSN, OfferNo -> Grade • StdSSN -> StdCity, StdClass • OfferNo -> OffTerm, OffYear, CourseNo, CrsDesc • CourseNo -> CrsDesc

  7. 2NF conversion Split into 3 Tables: • Student(StdSSN, StdCity, StdClass) • Offer(OfferNo, OffTerm, OffYear, CourseNo, CrsDesc) • Grade (StdSSN, OfferNo, Grade)

  8. Third Normal Form • 2NF, Determinants are the candidate keys. • Offer(OfferNo, OffTerm, OffYear, CourseNo, CrsDesc) • CourseNo -> CrsDesc • Split into two: • Offer(OfferNo, OffTerm, OffYear, CourseNo, CrsDesc) • Courses(CourseNo, CrsDesc)

  9. 3NF Tables • Student(StdSSN, StdCity, StdClass) • Offer(OfferNo, OffTerm, OffYear, CourseNo) • Courses(CourseNo, CrsDesc) • Grade (StdSSN, OfferNo, Grade)

  10. Decomposition of 1NF Table into 2NF Tables

  11. Decomposition of 1NF Table into 2NF Tables IssueDate is determined by CatalogID alone, not by both CatalogID and ProductID

  12. Conversion of 2NF Table into 3NF Tables ZipCode determines the value for State, and ZipCode is not the key to the table

More Related