Fourth Normal Form (4NF)

A relation R is in Fourth Normal Form (4NF) if and only if the following conditions are satisfied simultaneously:

R is already in 3NF or BCNF.

If it contains no multi-valued dependencies.

Multi-Valued Dependency (MVD)

MVD is the dependency where one attribute value is potentially a ‘multi-valued fact’ about another. Consider the table

In this example, ‘Address’ is a multi-valued fact ‘Customer Name’ and the converse is also true.

For example, the attribute ‘Address’ takes on the two values ‘New Delhi’ and ‘Amritsar’ for the single ‘Customer_Name’ value ‘Raj’. The attribute ‘Customer_Name’ takes on the values ‘Raj’ and ‘Suneet’ for the single ‘address; value ‘Amritsar’.

MVD can be defined informally as follows:

MVDs occur when two or more independent multi valued facts about the same attribute occur within the same table. It means that if in a relation R having A, B and C as attributes, B and Care multi-value facts about A, which is represented as A- -B and A J C, then multi value dependency exist only if B and C are independent of each other.

There are two things to note about this definition.

Firstly, in order for a table to contain MVD, it must have three or more attributes.

Secondly, it is possible to have a table containing two or more attributes which are interdependent multi valued facts about another attribute.

This does not give rise to an MVD. The attributes giving rise to the multi-valued facts must be independent of each other consider the following table:

The table lists students, the textbooks; they have borrowed, the librarians issuing them and the date of borrowing. It contains three multi-valued facts about students, the books they have borrowed, the librarians who have issued these books to the and the dates upon which the books were borrowed. However, these multi-valued facts are not independent of each other. There is clearly an association between librarians, the textbooks they have issued and the dates upon which they issued the books. Therefore, there are no MVDs in the table. Note that there is no redundant information in this table. The fact that student ‘Ankit’, for example, has borrowed the book ‘Mechanics’ is recorded twice, but these are different borrowing, one in April and the other in June and therefore constitute different items of information.

Now consider another table example involving Course, Student_name and text_book.

This table lists students, the courses they attend and the textbooks they use for these courses. The text books are prescribed by the authorities for each course, that is, the students have no say in the matter. Clearly the attributes ‘Student_name’ and ‘Text_book’ are multi-valued facts about the attribute ‘Course’. However, since a student has no influence over the text books to be used for a course, these multi-valued facts about courses are independent of each other. Thus the table contains an MVD. Multi-value facts are represented by.

Here, in above database following MVDs exists:

Course → Student_name

Course → Text book

Here, Student_name and Text_book are independent of each other.

Anomalies of database with MVDs

This form of the table is obviously full of anomalies. If a new student join the physics. we have to make two insertions for that student in the database, which is equal to the number of physics textbooks. Consider the problem if there are hundred textbooks for a subject. Similarly, if a new textbook is introduced for a course, then again we have to make multiple insertions in the database, which is equal to number of students for that course. So, there is a high degree of redundancy in the database, which will lead to update problems.

The above database is in First, Second and Third normal form because for each row column intersection we have at-most single entry and primary key is the combination of three columns (Course, Student_name, Text_book). So, it does not have any non-key attribute. It satisfies second and third normal form because it only refers to non-key attributes. The relation is also in BCNF, since all three attributes concatenated together constitute its key, yet it is clearly contained anomalies and requires decomposition with the help of fourth normal form.

Solution of above anomalies with Fourth Normal Form

This problem of MVD is handled in Fourth Normal Form. Here, is the rule for transforming a relation to 4NF given by Fagin.

Rule to transform a relation into Fourth Normal Form

A relation R having A, B, and C, as attributes can be non loss-decomposed into two projections R1(A,B) and R2(A,C) if and only if the MVD A → B|C hold in R.

Looking again at the un-decomposed COURSE_STUDENT_BOOK table, it contains a multi-valued dependency as shown below:

Course → Student_name

Course → Text_book

To put it into 4NF, two separate tables are formed as shown below:

course_student (Course, Student_name)
course_book (Course, text_book)
Now, we can easily check that all the above anomalies of STUDENT_ COURSE_BOOK database are removed. For example, if now a new student joins a course then we have to make only one insertion in COURSE_STUDENT table and if a new book introduced for a course then again we have to make a single entry in COURSE_BOOK table, so this modified database eliminate the problem of redundancy which also solves the update problems.
Practice Session 3:
Example: Consider the following database of STUDENT and normalize it.
student (Student_Name, Equipment, Language)
Solution: In order to normalize it first it flattens with first normal form,
 
This table lists students, the equipment they have allocated to them and the foreign languages in which they are fluent. This database shows that ‘equipment’ and ‘language’ are independent multi-valued facts about ‘Student_Name’ that is it contains a multi-valued dependency.
 
This form of the table is obviously full of anomalies. There is a high degree of redundancy that will lead to update problems. If Raj has his workstation taken away, then the information about his language skills is lost and if he acquires a PC, then all the information about his language skills has to be repeated, that is, three new rows have to be inserted (because of the entity integrity rule).
The table is in BCNF, since all three attributes concatenated together constitute its key, yet it contain anomalies and requires decomposition. Since the database contain MVDs, so it should be decomposed with the help of rule of fourth normal form decomposition. The database contains the following MVDs:
Student_Name → Equipment
Student_Name → Language
So, it should be decomposed in following database according to forth normal form.
student_equipement (Student_Name, Equipment)
student_language (Student_Name, Language)
Example: Suppose that employees can be assigned to multiple projects. Also suppose that employees can have multiple job skills as shown in database. Try to normalize the” database.
Solution: In order to normalize it we flatten the database with first normal form as shown below:
This database shows that Project_No and Skill are independent multi-valued facts about Emp_No that is it contains “a multi-valued dependency.
This form of the table is obviously full of anomalies. There is a high degree of redundancy that will lead to update problems. Since the database contains MVDs, so it should be decomposed with the help of rule of fourth normal form. Here, the database contain the following MVDs:
Emp_No → Project_No
Emp_No→ Skill
Here, Project_No and Skill are independent to each other, so it should be decomposed in to following database according to forth normal form.
emp_project (Emp_No, Project_No)
emp_skill (Emp_No, Skill)
You’ll also like:
First Normal Form (1NF) 
Third Normal Form (3NF) 
Fifth Normal Form(5NF) 
Second Normal Form (2NF) 
Boyce-Codd Normal Form (BCNF) 
   
Next →
← Prev