Fourth normal form (4NF)
Multivalue dependency occurs when two attributes (or columns) in a table
are independent of one another, but both depend on a third attribute.
For a dependency A ->
B, if for a single value of A, multiple value of B exists, then the table may
have multi-valued dependency. The table should have at least 3 attributes and B
and C should be independent for A ->> B multivalued dependency.
- A
relation will be in 4NF if it is in Boyce Codd normal form and has no
multi-valued dependency.
- 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
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, which
leads to unnecessary repetition of data.
Decompose above table into two tables to make it in
4NF:
student_course
STU_ID |
COURSE |
21 |
Computer |
21 |
Math |
34 |
Chemistry |
74 |
Biology |
59 |
Physics |
STU_ID |
HOBBY |
21 |
Dancing |
21 |
Singing |
34 |
Dancing |
74 |
Cricket |
59 |
Hockey |
Fifth normal form (5NF)
Join dependency is a constraint which is similar to functional
dependency or multivalued dependency.
A relation that has join dependency cannot be divided into two(or more) relations such that the resulting tables can be combined to form the original table.
A relation is in 5NF if it
is in 4NF and do not contains any join dependency and joining should be
lossless.
5NF is also known as
Project-join normal form (PJ/NF).
consider example:
SUBJECT |
LECTURER
|
SEMESTER |
Computer |
Anshika |
Semester 1 |
Computer |
John |
Semester 1 |
Math |
John |
Semester 1 |
Math |
Akash |
Semester 2 |
Chemistry |
Praveen |
Semester 1 |
decompose it into three
relations P1, P2 & P3:
P1:
SEMESTER |
SUBJECT |
Semester 1 |
Computer |
Semester 1 |
Math |
Semester 1 |
Chemistry |
Semester 2 |
Math |
P2:
SUBJECT |
LECTURER |
Computer |
Anshika |
Computer |
John |
Math |
John |
Math |
Akash |
Chemistry |
Praveen |
P3:
SEMSTER |
LECTURER |
Semester 1 |
Anshika |
Semester 1 |
John |
Semester 1 |
John |
Semester 2 |
Akash |
Semester 1 |
Praveen |
The natural Join of all
the three relations will give original relation. So original relation has no join
dependency. Three decomposed relations are in 4NF and have no join
dependency.