Multivalued Dependencies and Fourth Normal Form

Multivalued Dependencies and Fourth Normal Form

AU: May-14, Dec.-16, 19, Marks 16

Concept of Multivalued Dependencies

• A table is said to have multi-valued dependency, if the following conditions are true,

1) For a dependency A→B, if for a single value of A, multiple values of B exists, then the table may have multi-values dependency.

2) Also, a table should have at-least 3 columns for it to have a multi-valued dependency.

3) And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other.

If all these conditions are true for any relation(table), it is said to have multi-valued dependency.

• In simple terms, if there are two columns A and B - and for column A if there are multiple values of column B then we say that MVD exists between A and B

• The multivalued dependency is denoted by →

• If there exists a multivalued dependency then the table is not in 4th normal form. 

• For example: Consider following table for information about student

Student

Here sid =1 leads to multiple values for courses and skill. Following table shows this

Here sid and course are dependent but the Course and Skill are independent. The multivalued dependency is denoted as :

sid→ Course

sid→ Skill

Fourth Normal Form

Definition: For a table to satisfy the Fourth Normal Form, it should satisfy the following two conditions:

1) It should be in the Boyce-Codd Normal Form(BCNF).

2) And, the table should not have any multi-valued dependency.

For example: Consider following student relation which is not in 4NF as it contains multivalued dependency.

Student Table

Now to convert the above table to 4NF we must decompose the table into following two tables.

Student_Course Table

Key: (sid,Course)

Student Skill Table

Key: (sid, Skill)

Thus the tables are now in 4NF.

Review Questions

1. Explain first normal form, second normal form, third normal form and BCNF with example.  AU: Dec.-16, Marks 13

2. Explain Boyce Codd normal form and fourth normal form with suitable example.   AU: May-14, Marks 16

3. Exemplify the multi-value dependency and fourth normal form 4NF.   AU: Dec.-19, Marks 6