Fourth Normal Form (4NF)
A table is in Fourth Normal Form (4NF) when it is in 3NF and has no multivalued dependencies.
You might encounter poorly designed databases, or you might be asked to convert spreadsheets into a database format in which multiple multivalued attributes exist. For example, consider the possibility that an employee can have multiple assignments and can also be involved in multiple service organizations. Suppose employee 10123 does volunteer work for the Red Cross and United Way. In addition, the same employee might be assigned to work on three projects: 1, 3, and 4.
The following Figure illustrates how that set of facts can be recorded in very different ways.
You May Also Like:
Normalization and its advantages
1st Normal Form
2nd Normal Form
The problems with the tables in the above figure are as follows:
The attributes ORG_CODE and ASSIGN_NUM each may have many different values. In normalization terminology, this situation is referred to as a multivalued dependency.
A multivalued dependency occurs when one key determines multiple values of two other attributes and those attributes are independent of each other.
i.e One employee can have many service entries and many assignment entries. Therefore, one EMP_NUM can determine multiple values of ORG_CODE and multiple values of ASSIGN_NUM; however, ORG_CODE and ASSIGN_NUM are independent of each other.
The presence of a multivalued dependency means that if versions 1 and 2 are implemented,
the tables are likely to contain quite a few null values; in fact, the tables do not even have a viable candidate key. (The EMP_NUM values are not unique, so they cannot be PKs. No combination of the attributes in table versions 1 and 2 can be used to create a PK because some of them contain nulls).
Such a condition is not desirable, especially when there are thousands of employees, many of whom may have multiple job assignments and many service activities.
Version 3 at least has a PK, but it is composed of all of the attributes in the table. In fact, version 3 meets 3NF requirements, yet it contains many redundancies that are clearly undesirable.
The solution is to eliminate the problems caused by the multivalued dependency. You do this by creating new tables for the components of the multivalued dependency. In this example, the multivalued dependency is resolved by creating the ASSIGNMENT and SERVICE_V1 tables depicted in the following Figure.
You May Also Like:
