CP363 : Fourth Normal Form

Fourth Normal Form

An attribute may multidetermine another (X→→Y). For example, a student (identified by Student_ID) may take many courses (identified by Course). Thus Student_ID→→Course. This is called a multivalued dependency.

A table violates fourth normal form when it contains two or more independent multivalued dependencies.

For example, assume that a student may take more than one course and have more than one major. Thus student has two multivalued dependencies Student_ID→→Course and Student_ID→→Major. 4NF is violated by the following table:

Note that BCNF is not violated because there are no functional dependencies in this table. In fact the primary key for this table is made up of all three attributes.

This table contains two independent multivalued dependencies. Course and Major are each multidetermined by Student_ID, but are independent of each other. In such a case every possible combination of Student_ID, Course, and Major must be included in the table to make sure that no data is left out. (Imagine if a student was taking five courses and had two majors - matching each major to all courses is the only way to insure consistency of data). This can be solved by decomposing the table into two separate multidetermined relationships:

and

Note that the two tables have a total of 5 tuples, while the original table has 6. As in the original table, the primary keys for each of these tables consists of all the attributes in the tables.

We will not go beyond 4NF in our discussions.