Normalization

11.1 Top View

Eliminate anomalies by minimizing duplication: divide data between tables

• Each relational scheme deals with a single theme
• Likely to introduce interrelation (i.e., referential integrity) constraints
• Make it more efficient to maintain databases
• May make data more difficult to access

11.2 The Approach

• Normal forms provide
  • Yard sticks for identifying bad designs
  • Guidelines for eliminating the problems

• Analysis: Top-down
  • Evaluate the relation schemas against the normal forms
  • Decompose when the criteria are not met

• Synthesis: Composes relations

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

11.4 First Normal Form

Allow only atomic values in attributes

Normalization through Decomposition

• Attributes violating 1NF and place it in a separate relation, along with the primary key

  A $\underset{¯}{B}$ C x 1 $\left\{\right.$ a,b$\left.\right\}$ z 2 c

  A $\underset{¯}{B}$ x 1 z 2

  $\underset{¯}{B}$ $\underset{¯}{C}$ 1 a 1 b 2 c

• The new relation uses all the attributes for a primary key

Normalization through Unrolling Multivalues

• Introduces duplicated data

Normalization through Unrolling Attributes

• Assumes a limit on the cardinality of multivalues
• Needs NULL values

11.5 Second Normal Form

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

Normalization

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

11.6 Third Normal Form

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

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

Normalization

• Create a separate relationship schema for the non-key attributes that functionally depend on non-key attributes
• Designate the latter attributes as primary keys.

11.7 Example

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

1. $AB\to C$
2. $BD\to EF$
3. $AD\to GH$
4. $A\to I$
5. $H\to 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

• Draw a schema with arrows showing the functional dependencies (e.g., fig 14.11(a))
• Find the primary key
• Find the closures of the proper nonempty subsets of the primary key
• Use the above closures to perform 2NF decompositions
• Find the closures of the proper nonempty subsets of the attributes not included in the primary key
• Use the above closures to perform 3NF decompositions

11.8 Boyce-Codd Normal Form (BCNF)

If $X\to Y$ is non-trivial then $X$ is a super key

Normalization

11.9 Generalization

Assume keys instead of primary keys.

Non-2NF	Partial dependency
Non-3NF	transitive dependency:
non-BCNF	$Y$ is not in $X$, $Y$ $\subseteq$ key, and $X$ is not a superkey.

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

• If you submit your homework electronically, use the departmental submit utility: submit XXX lab6 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
  • Files are restricted to 1,048,576 bytes
  • The submit program issues error messages to the student’s CSE email account

Q&A

Reference: Ch. 10.3–10.6 in textbook.