### 4 Normal Form

```4 Normal Form
Nathanael Chow
CS 157A
Fall 2006
Dr. Lee
Overview
●
●
●
●
●
●
1 NF
2 NF
3 NF
BCNF
4 NF
Conclusion
Database Normalization
●
●
Each data in the database should
represent once
Purpose of normalization: to
eliminate insert, update, and delete
anomalies
First Normal Form (1 NF)
●
●
●
All values in the columns are atomic (simple,
indivisible). This is, they contain no repeating
values.
There are no repeating groups: two columns do not
store similar information in the same table.
Basically: 1 NF is to eliminate duplicate columns
1st Normal Form Example
Un-normalized Students table:
123
123A James
555
102-8 104-9
124
123B Smith
467
209-0 102-8
Normalized Students table:
123
123
124
124
123A
123A
123B
123B
James
James
Smith
Smith
555
555
467
467
102-8
104-9
209-0
102-8
Second Normal Form (2 NF)
●
A relation is in 2 NF if it is in 1 NF and every
non-key attribute is fully functionally dependant
on the primary key
2nd Normal Form Example
Students table
Student#
123
123A
124
123B
Registration table
Student#
123
123
124
124
Class#
102-8
104-9
209-0
102-8
James
Smith
555
467
Third Normal Form (3 NF)
●
●
●
A relation is in 3 NF if it is 2 NF and no transitive
dependencies exist.
Transitive dependency is a functional dependency
between non-key attribute
Basically: 3 NF is to eliminate column not depend
upon the primary key.
transitive dependency
transitive
dependency
Cust_ID
Name
Salesperson
Region
Cust_ID
Salesperson
Name
Salesperson
Region
Region
Cust_ID
Name
Salesperson
Boyce-Codd Normal Form (BCNF)
●
●
A relation is in BCNF if it is in 3 NF and every
determinant is a candidate key; in other words,
each determinant can be used as a primary key.
Determinant: an attribute on which some other
attribute is fully functionally dependent

Ex: A --> B (A is called the determinant)
BCNF Example
●
●
●
●
●
Given: R (A, B, C , D)
A --> B, C, D
B --> A, C, D
C --> A , B, D
D --> A, B, C
BCNF Example (Cont...)
●
Determinants:

●
Candidate keys:

●
A, B, C, and D
A, B, C, and D
Since all the determinants are candidate keys,
this is BCNF.
4th Normal Form (4 NF)
●
A relation is a 4 NF if it is BCNF and


●
There is no multivalued dependency in the relation
or
There are multivalued dependency but the attributes,
which are multivalued dependent on a specific
attribute, are dependent between themselves
What is a multivalued dependency (MVD)?
Definition of MVD
●
A multivalued dependency X->-> Y is an
assertion that if two tuples of a relation agree on
all the attributes of X, then their components in
the set of attributes Y may be swapped, and the
result will be two tuples that are also in the
relation
MVD Example
X Y Z
A B1 C1
A B2 C2
R(x, y, z)
X Y Z
A B1 C1
A B2 C2
X ->-> Y
MVD Example
X Y Z
A B1 C1
A B2 C2
R(x, y, z)
X Y Z
A B1 C1
A B2 C2
X ->-> Y
A
A
B2 C1
B1 C2
4 NF Example
Assume the following relation:
Employee (Eid:pk1, Language:pk2, Skill:pk3)
Eid
Language
Skill
100
English
Teaching
100
Kurdish
Politic
100
French
Cooking
200
English
Cooking
200
Arabic
Singing
4 NF Example (conti...)
Eid
100
100
100
200
200
Language
English
Kurdish
French
English
Arabic
Skill
Teaching
Politic
Cooking
Cooking
Singing
Recall that a relation is in BCNF if all its determinant are candidate
keys.
Because relation Employee has only one determinant (Eid, Language,
Skill), which is the composite primary key.
Since the primary is a candidate key, R is in BCNF.
Assume that there is no MVD, then this relation is 4 NF.
4 NF Example (conti...)
Assume the following relation with multi-value dependency:
Employee (Eid:pk1, Languages:pk2, Skills:pk3)
Eid --->> Languages
Eid --->> Skills
Languages and Skills are dependent.
This says an employee speaks several languages and has several
skills. However for each skill, a specific language is used when that
skill is practiced.
4 NF Example (conti...)
Thus employee 100 when she teaches, she uses English;
but when she cooks, she uses French. This relation is
in fourth normal form.
Eid
Language
Skill
100
English
Teaching
100
Kurdish
Politic
100
French
Cooking
200
English
Cooking
200
Arabic
Singing
Not 4 NF Example
Assume the following relation with multivalued dependency:
Employee (Eid:pk1, Languages:pk2, Skills:pk3)
Eid --->> Languages
Eid --->> Skills
Languages and Skills are independent.
Not 4 NF Example (conti...)
Eid
100
100
100
200
Language
English
Kurdish
French
Arabic
Skill
Teaching
Politic
Cooking
Singing
Insertion anomaly: To insert row (200 English
Cooking) we have to insert two extra rows (200
Arabic cooking), and (200 English Singing)
otherwise the database will be inconsistent.
Not 4 NF Example (conti...)
Here is the table after the insertion:
Eid
100
100
100
100
200
200
200
200
Language
English
Kurdish
English
Kurdish
Arabic
English
Arabic
English
Skill
Teaching
Politics
Politics
Teaching
Singing
Cooking
Cooking
Singing
Conclusion of Steps in Normalization
References
●
●
http://www.cs.sjsu.edu/faculty/lee/cs157/cs157a.
html
http://www.cs.sjsu.edu/faculty/lee/cs157/cs157al
ecturenotes.htm
```