4NF and 5NF

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

 student_hobby

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.