Normalization

11.1 Top View

Eliminate anomalies by minimizing duplication: divide data between tables

Students
St-NameStatus
Bensenior
Danfreshment
Courses
Course-NameCourse-#
db 670
ds 680
Graders
St-NameCourse-#Salary
Ben 670500
Ben 680500
Dan 670400
Dan 680600

11.2 The Approach

11.3 Types of Normal Forms

  • Functional dependencies over primary keys:
    • 1st normal form
    • 2nd normal form
    • 3rd normal form
    • Boyce-Codd normal form

  • Multivalues dependencies: 4NF
  • Join dependencies: 5NF
|--------| |--1NF---| |  2NF   | |--------| ||-3NF--|| ||BCNF  || ||------|| |||4NF-||| ||-5NF-||| ---------|

11.4 First Normal Form

Allow only atomic values in attributes

Normalization through Decomposition

Normalization through Unrolling Multivalues

ABC
x1{ a,b}
z2c
ABC
x1a
x1b
z2c

Normalization through Unrolling Attributes

ABC
x1{ a,b}
z2c
ABCC’
x1ab
z2cNULL

11.5 Second Normal Form

Attributes not in the primary key should be fully dependent on the primary key.

SSN NAME PROJ-# PROJ-NAME PROJ-LOC HOURS
partial functional
dependencies
{SSN }NAME
{ PROJ-# }{ PROJ-NAME, PROJ-LOC }

full functional
dependencies
{SSN, PROJ-# }HOURS

Normalization

Set a new table for each partial key and its dependent attributes.

SSN PROJ-# HOURS
SSN NAME
PROJ-# PROJ-NAME PROJ-LOC

11.6 Third Normal Form

Attributes not in the primary key should be functionally dependent only on the primary key.

SSN NAME DEPT-# DEPT-NAME

DEPT-# DEPT-NAME

Every non-key attribute must be a fact about the key, the whole key, and nothing but the key.

Normalization

SSN NAME DEPT-#
DEPT-# DEPT-NAME

11.7 Example

Exercise 10.27, p. 329 Consider the universal relation R = {A,B,C,D,E,F,G,H,I,J} and the set of functional dependencies

  1. AB C
  2. BD EF
  3. AD GH
  4. A I
  5. H J

For the above data

  1. Determine the key of R
  2. Decompose R into 2NF relations
  3. Decompose the outcome into 3NF relations

Sketch of Solution

11.8 Boyce-Codd Normal Form (BCNF)

If X Y is non-trivial then X is a super key

PROJECT DEPT MANAGER

Normalization

PROJECT MANAGER
MANAGER DEPT

11.9 Generalization

               |---|---|--|---|---|---|---|--|---|----                |   |   |  |   |   |   |   |  |   |   |                |---|---|--|---|---|---|---|--|---|---|                      |      ||    | prim ary k ey        --------|    |                         |         |    ||         2 N F           |---------------|      ||         3 N F                     |     --------|                         ||        |     |       B C N F           |----------------

Assume keys instead of primary keys.

Non-2NFPartial dependency
 |||||||||||||||      |||| -|      --|| |||--||-||  ||- |||Key  -||X||||-   |||Y|||-     |||||||||
Non-3NF transitive dependency:
 |||||||||     ||||||      ||||| -- Key   ---||-| X  |--|||-|Y  ||- -||||||||-    |||||||-   -||||||-
non-BCNF Y is not in X, Y key, and X is not a superkey.
  ||||||-||||| --|    -||||-|||-|||-||-|||- --Key  ||Y|-||||||||||||X|--  |||||| ||||||-         ||       ||||

11.10 Assignment #6

Due: Mo, Feb 23

Problems 10.18 (a, c, e, g, i), 10.20, 10.26 and 10.29 from textbook (pages 328–330 4th ed; 373–374 5th ed).

Notes

Q&A

Reference: Ch. 10.3–10.6 in textbook.