1 / 12

Normalization

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

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