1 / 16

Normalization in Database Design: An Essential Guide

Learn the key concepts of normalization in database design, including its purpose, terminology, types of normal forms, and practical examples to eliminate data anomalies effectively.

dewaynec
Download Presentation

Normalization in Database Design: An Essential Guide

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. Chapters 13 & 14 Normalization

  2. Agenda • Definition • Purpose • Terminology • Types of Normal Form • Examples

  3. Definition • Technique for producing a set of relations having the real primary key(s)

  4. Purpose • Eliminate insertion anomalies • Eliminate deletion anomalies • Eliminate modification anomalies

  5. Terminology • Functional dependency • Determinant • Transitive dependency

  6. Types of Normal Form • First normal form • No repeating group • Second normal form • every non-primary-key attribute is fully functionally dependent on the whole primary key • Third normal form • No transitive dependency • Boyce-Codd normal form • Every determinant is a candidate for primary key

  7. Types of Normal Form • Fourth normal form • No multi-valued dependency • Fifth normal form • No join dependency

  8. Relation Holds Book Information in a Bookstore BOOKS ( TITLE, ISBN, AUTHOR, PUB_NAME, PUB_ADD, TOTAL_COPIES_ORDERED, COPIES_IN_STOCK, PUB_DATE, CATEGORY, SELL_PRICE, COST) • Unique: ISBN, PUB_NAME

  9. BOOK (ISBN, TITLE, AUTHOR, PUBLISHER_NAME, TOTAL_COPIES_ORDEORED, COPIES_IN_STOCK, PUBLICATION DATE, CATEGORY, COST, SELLING_PRICE) • PUB (PBULISHER_NAME, PUBLISHER_ADDRESS)

  10. Relation Holds Student Information in the Dormitories • COLLDORM (STUNAME, STUID, HOMEADD, HOMEPHONE, DORMROOM, ROOMMATE_NAME, DORMADD, STATUS, MEALPLAN, ROOMCHARGE, MEALCHARGE) • Unique: STUID, DORMADD, DORMROOM, MEALPLAN

  11. STUDENT (STUID, STUNAME, HOMEADD, HOMEPHONE, DORMROOM, ROOMMATE_NAME, STATUS, MEALPLAN) • DORM(DORMROOM, DORMADD, ROOMCHARGE) • MEALS (MEALPLAN, MEALCHARGE)

  12. Rental Relation • ClientRental (clientNo, cName, propertyNo, pAdd, rentStart, rentFinish, rent, ownerNo, oName)

  13. Inspection Relation • Inspection (Pno, Padd, IdateAndItime, Comment, Sno, Sname)

  14. Work Relations • W1 ( EMPID, EMPNAME, DATE_HIRED, JOB_TITLE, JOB_LEVEL) • W2 (EMPID, EMPNAME, JOB_TITLE, RATING_DATE, RATER_NAME, RATING) • W3 (EMPID, EMPNAME, PROJECT#, PROJECT_NAME, PROJ_BUDGET, EMP_MANGER, HOURS_ASSIGN) • W4 (EMPID, EMPNAME, SCHOOL_ATTENDED, DEGREE, GRADUATION_DATE) • W5 (EMPID, EMPNAME, SSNO, DEPENDENT_NAME, DEPDENT_ADDRESS, RELATION_TO_EMP)

  15. Points To Remember • Definition • Purpose • Terminology • Types of Normal Form • Examples

  16. Assignment • Review chapters 5, 6, 13,14 • Read chapters 19, 20 • Assignments • Normalize 13.13-13.16, 14.9-14.10 to fourth normal form (identify the primary key, every determinant, highest violation, and normalized relations with one underline for pk and double underline for fk) • Chapter 13 assignment and assignment 9 due date: • Project (revised Enhanced ER) • Project (Normalization) • Project due date:

More Related