CS220: DATABASE SYSTEMS
LECTURE - 4TH NORMAL FORM
CS-14BCD
FOURTH NORMAL FORM
FOURTH NORMAL FORM (4NF)
Fourth normal form eliminates
independent many-to-one relationships
between columns.
To be in Fourth Normal Form,
- a relation must first be in Boyce-Codd
Normal Form.
- has no multi-valued dependency.
3
EXAMPLE (NOT IN 4NF)
Scheme {MovieName, ScreeningCity, Genre)
Primary Key: {MovieName, ScreeningCity, Genre)
1. All columns are a part of the only candidate
key, hence BCNF
2. Many Movies can have the same Genre
3. Many Cities can have the same movie
Movie ScreeningCit Genre
4. Violates 4NF y
Hard Code Los Angles Comedy
Hard Code New York Comedy
Bill Durham Santa Cruz Drama
Bill Durham Durham Drama
The Code Warrier New York Horror 4
EXAMPLE OF MVD
• For a dependency A → B, if for a single
value of A, multiple values of B exists,
then the relation will be a multi-valued
dependency.
5
EXAMPLE OF MVD
STU_ID COURSE HOBBY
21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
59 Physics Hockey
• The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.
• In the STUDENT relation, a student with STU_ID, 21 contains two courses,
Computer and Math and two hobbies, Dancing and Singing.
So there is a Multi-valued dependency on STU_ID, 6
which leads to unnecessary repetition of data.
EXAMPLE OF MVD
STU_ID COURSE
21 Comput
er
Student-Course
21 Math
34 Chemist
ry
STU_ID HOBBY
74 Biology
21 Dancing
59 Physics Student-Hobby
21 Singing
34 Dancing
74 Cricket
7
59 Hockey
FOURTH NORMAL FORM (4NF)
Example 2 (Not in 4NF)
Scheme {Manager, Child, Employee}
1. Primary Key {Manager, Child, Employee}
2. Each manager can have more than one child
3. Each manager can supervise more than one
employee
Manager Child Employee
4. 4NF Violated
Jim Beth Alice
Mary Bob Jane
Mary NULL Adam
8
FOURTH NORMAL FORM (4NF)
Example 3 (Not in 4NF)
Scheme {Employee, Skill, ForeignLanguage}
1. Primary Key {Employee, Skill, Language }
2. Each employee can speak multiple languages
3. Each employee can have multiple skills
4. Thus violates 4NF Employe Skill Languag
e e
1234 Cooking French
1234 Cooking German
1453 Carpentr Spanish
y
1453 Cooking Spanish
2345 Cooking Spanish 9
4NF - DECOMPOSITION
1. Move the two multi-valued relations
to separate tables
2. Identify a primary key for each of the
new entity.
10
4NF - DECOMPOSITION
Example 1 (Convert to 4NF)
Old Scheme {MovieName, ScreeningCity, Genre}
New Scheme {MovieName, ScreeningCity}
New Scheme {MovieName, Genre}
Movie Genre Movie Screening
City
Hard Code Comedy Hard Code Los Angles
Bill Durham Drama Hard Code New York
The Code Horror Bill Durham Santa Cruz
Warrier
Bill Durham Durham
The Code New York
Warrier
11
4NF - DECOMPOSITION
Example 2 (Convert to 4NF)
Old Scheme {Manager, Child, Employee}
New Scheme {Manager, Child}
New Scheme {Manager, Employee}
Manager Child Manager Employee
Jim Beth Jim Alice
Mary Jane
Mary Bob
Mary Adam
12
4NF - DECOMPOSITION
Example 3 (Convert to 4NF)
Old Scheme {Employee, Skill, ForeignLanguage}
New Scheme {Employee, Skill}
New Scheme {Employee, ForeignLanguage}
Employee Skill Employee Language
1234 Cooking 1234 French
1453 Carpentry 1234 German
1453 Cooking 1453 Spanish
2345 Cooking 2345 Spanish
13
FIFTH NORMAL FORM (5NF)
FIFTH NORMAL FORM (5NF)
- Fifth normal form is satisfied when all
tables are broken into as many tables as
possible in order to avoid redundancy.
Once it is in fifth normal form it cannot
be broken into smaller relations
without changing the facts or the
meaning.
15
FIFTH NORMAL FORM (5NF)
- A relation break up into two relations
must contain lossless join Property,
which makes certain that no invalid or
extra tuples of attributes are created
when relations are again joined
together through a natural join.
16
FIFTH NORMAL FORM (5NF)
A database is said to be in 5NF, if and only if,
- It’s in 4NF
- If we can decompose table further to eliminate redundancy
and anomaly, and when we re-join the decomposed tables by
means of candidate keys, we should not be losing the original
data or any new record set should not arise. In simple words,
joining two or more decomposed table should not lose
records nor create new records.
- 5NF is satisfied when all the tables are broken into as many
tables as possible in order to avoid redundancy.
- 5NF is also known as Project-join normal form (PJ/NF).
17
JOIN DEPENDENCY- 5NF
- A table or relation can be recreated or re-designed by
joining multiple tables and each table of this contain a
subset of attributes and values of the table, then the
table is Join Dependency.
- If the join R1 and R2 over S is equal to the relation R
then we can say that a join dependency exists, where R1
and R2 are the decomposition R1(P, Q, S) and R2(Q, T)
of a given relation R(P, Q, S, T). As a possibility, R1 and
R2 are a lossless decomposition of R.
18
5NF- EXAMPLE
19
Thank you
20