Normalization

1
Chapter 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 (Project-Join Normal Form)
• No join dependency
• No nonloss projection

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, DORMROOM,
  ROOMMATE_NAME, STATUS, MEALPLAN)
• DORM(DORMROOM, DORMADD, ROOMCHARGE)
• MEALS (MEALPLAN, MEALCHARGE)

12
Rental Relations

• ClientRental (clientNo, cName, propertyNo, pAdd,
  rentStart, rentFinish, rent ownerNo, oName)

13
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)

14
Points To Remember

• Definition
• Purpose
• Terminology
• Types of Normal Form
• Examples

15
Assignment

• Review chapters 5-14
• Read chapters 19, 20
• Assignments
• Normalize 13.13-13.16 to four normal form
  (Individual assignment)
• Due date
• Project (Normalization)
• Due date