UNIT II DATABASE DESIGN

 2.9 Multivalued Dependency (MVD)and Fourth Normal Form

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.

NIKE_MODEL	MANUF_YEAR	COLOR
M2011	2008	White
M2001	2008	Black
M3001	2013	White
M3001	2013	Black
M4006	2017	White
M4006	2017	Black

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".

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

Student ID	Course ID
1	1
1	2
2	3
2	4
2	5
3	6

Table 2: Courses

Course ID	Course Name
1	Math
2	Science
3	Art
4	History

Table 3: Textbooks

Textbook ID	Textbook Name	Course ID
1	Algebra	1
2	Calculus	2
3	Biology	2
4	Chemistry	2
5	Art History	3
6	American History	4

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 1: Splitting a Table with MVDs

MOVIE

Movie	Location	Genre
PIE	USA	STRATEGY
DOOM	UK	THRILLER
DUNE	UK	ACTION
INTERSTELLAR	USA	SCIENCE-FICTION
DHAMAAL	INDIA	COMEDY

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

MOVIE	LOCATION
PIE	USA
DOOM	UK
DUNE	UK
INTERSTELLAR	USA
DHAMAAL	INDIA

GENRE

MOVIE	GENRE
PIE	STRATEGY
DOOM	THRILLER
DUNE	ACTION
INTERSTELLAR	SCIENCE-FICTION
DHAMAAL	COMEDY

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