Fourth Normal Form (4NF)

Fourth Normal Form (4NF) is a level of database normalization that requires a relation to be in BCNF and have no non-trivial multivalued dependencies other than the candidate key, to eliminate redundant data and maintain data consistency. If a table violates this standard, it needs to be split into two tables to achieve 4NF.

For a relation R to be in 4NF, it must meet two conditions −

1. It should be in Boyce-Codd Normal Form (BCNF).
2. It should not have any non-trivial multivalued dependencies.

Example:

To remove the multivalued dependency (MVD) in the “Students” table example, we can create two new tables, one for “Courses” and another for “Textbooks,” and establish a relationship between them using foreign keys.

Here’s how we can create the tables:

Table 1: Students

Table 2: Courses

Table 3: Textbooks

So, we removed the multivalued dependency by splitting the “Course” and “Textbook” columns into separate tables.

We have also added a new “Course ID” column to the “Students” table. It has a foreign key that references the “Course ID” column in the “Courses” table. Similarly, the “Textbooks” table also has a “Course ID” column that serves as a foreign key referencing the “Course ID” column in the “Courses” table.

Hence, we have achieved the fourth normal form (4NF) for the “Students” table. It has done after by removing the multivalued dependency and creating separate tables. The Resultant schema eliminates data redundancy and improves data integrity, making it easier to manage and query the database.

Challenges of Fourth Normal Form

Achieving 4NF can be challenging, as it requires careful analysis of functional dependencies, selecting appropriate keys, and balancing between normalization and performance. Achieving 4NF can be time-consuming, and it may require additional storage space.

Examples of Achieving Fourth Normal Form

An example of achieving 4NF is splitting a table with MVDs into two or more tables, with each table containing attributes that are functionally dependent on the primary key. In another example, an intersection table can be used to eliminate MVDs by creating a separate table that links two other tables with MVDs.

Example:

MOVIE

The table is not in it 4th Normal Form, as the various locations can have the same movie, and multiple movies can have same listing. Therefore, let’s split the table to achieve 4th Normal Form.

LOCATION

GENRE

Now, the table has achieved its 4th Normal Form, by splitting the tables and converting it into its violation free table.

In database management systems, normalization is an essential process to ensure that data is organized efficiently and effectively. Multivalued dependency (MVD) is a concept that helps to identify and eliminate data redundancy and anomalies, and Fourth Normal Form (4NF) is a normalization form that addresses the challenges associated with multivalued dependency.

In this article, we will discuss multivalued dependency, the Fourth Normal Form, and its importance in database management systems.

Multivalued Dependency (MVD)

Multivalued dependency (MVD) is a type of dependency that exists when a table contains more than one multivalued attribute and changes to one attribute can affect another attribute. In other words, MVD occurs when a table has a non-trivial relationship between attributes that are not part of the same composite key.

• Multivalued dependency occurs when two attributes in a table are independent of each other but, both depend on a third attribute.
• A multivalued dependency consists of at least two attributes that are dependent on a third attribute that’s why it always requires at least three attributes.

Example: Suppose there is a bike manufacturer company that produces two colors(white and black) of each model every year.

Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and independent of each other.

In this case, these two columns can be called as multivalued dependent on BIKE_MODEL. The representation of these dependencies is shown below:

BIKE_MODEL -> -> MANUF_YEAR

BIKE_MODEL -> -> COLOR

This can be read as “BIKE_MODEL multidetermined MANUF_YEAR” and “BIKE_MODEL multidetermined COLOR”.