What do you mean by 4NF in DBMS. Explain with example.
Database Management System
Computer Science Engineering
514
Vedhika
Fourth Normal Form (4NF) is a database normalization technique used to further eliminate data redundancy and improve data integrity beyond 3NF.
In 4NF, a table is said to be in 4NF if it is already in 3NF and there are no non-trivial multivalued dependencies between any combination of the table's columns.
To explain 4NF with an example, consider a table called "Courses" table with the following columns:
The "Course Materials" column contains a list of materials needed for each course, such as textbooks, workbooks, and lab equipment. However, some courses may require multiple copies of the same material, or some materials may be required for multiple courses.
This table is not in 4NF because there are non-trivial multivalued dependencies between the Course ID and the Course Materials column. The Course Materials column is not functionally dependent on the Course ID alone, as it can have multiple values for a single Course ID, and the same material can be used in multiple courses.
To normalize this table to 4NF, we need to split it into two tables: "Courses" and "Materials."
The "Courses" table will have the following columns:
The "Materials" table will have the following columns:
We also need a third table called "Course_Materials" to represent the many-to-many relationship between the "Courses" and "Materials" tables. This table will have the following columns:
By doing this, we have eliminated the multivalued dependency between the Course ID and the Course Materials column, and we have ensured that each table contains data that is relevant and not redundant.
Fourth Normal Form (4NF) is a database normalization technique used to further eliminate data redundancy and improve data integrity beyond 3NF. In 4NF, a table is said to be in 4NF if it is already in 3NF and has no multi-valued dependencies.
Lets discuss the example of Fourth Normal Form (4NF) in details.
STUDENT table
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.
So to make the above table into 4NF, we can decompose it into two tables:
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 | Hocke |