Chapters 13

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