FOURTH NORMAL FORM (4NF) - IBM Mainframe

A group of tables that satisfies the first, second and third normal forms are sufficiently well designed. However, isolating independent multiple relationships will further improve the data model when one-to-many and many-to-many relationships between tables are involved. In other words, no table should contain two or more one-to-many or many-to-many relationships that are not directly related to the key. These kinds of relationships are called multi-valued dependencies (MVDs). Multi-valued dependencies are the result of the first normal form, which prohibited an attribute from having a set of values. If we have two or more multi-valued independent attributes in the same relation (table), we get into a situation where we have to repeat every value of one of the attributes with every value of the other attribute to keep the relation state consistent and to maintain independence among the attributes involved. This constraint is specified by a multi-­valued dependency.

Consider a table (relation) EMPLOYEE that has the attributes Name, Project and Hobby. A row in the EMPLOYEE table represents the fact that an employee works for a project and has a hobby. But an employee can work in more than one project and can have more than one hobby. The employee's projects and hobbies are independent of one another. To keep the relation state consistent we must have a separate tuple to represent every combination of an employee's project and an employee's hobbies. This constraint is specified as a multi-valued dependency on the EMPLOYEE relation. So whenever two independent one-to-many relationships (A:B and A:C) are mixed in the same relation, a multi-valued dependency arises. We will see the employee table and how the multi-valued dependency can be avoided using the fourth normal form. Given below is the EMPLOYEE table and its contents:

EMPLOYEE Table

EMPLOYEE Table

As we have seen, the above relation has two multi-valued dependencies—(Name, Project) and (Name, Hobby). We resolve this by decomposing the EMPLOYEE table into two tables that satisfy the fourth normal form as follows:

PROJECT and HOBBY Tables

PROJECT and HOBBY Tables

PROJECT and HOBBY Tables

Even though this is not a violation of the third normal form this data structure makes it difficult to store a phone number of a contact without a company and makes it impossible to store multiple phone numbers for the same contact without repeating the company information.

The Fourth Normal Form is violated because there are two independent relationships represented in the COMPANY table—one between CONTACTS and COMPANIES and another between CONTACT and PHONE_NUMBER, which should be avoided. This can be achieved as shown in Figure 43.4 and defined in the DDL that follows.

CONTACTS-COMPANY-PHONE Relationship

CONTACTS-COMPANY-PHONE Relationship

CONTACTS-COMPANY-PHONE Relationship

This allows companies and phone numbers to have independent, one-to-many relationships with CONTACTS table, which satisfies the fourth normal form.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics