Fourth Normal Form (4NF) is a level of database normalization that builds on the concepts of Third Normal Form (3NF). In a 4NF database, there are no multi-valued dependencies between two or more sets of candidate keys.
A multi-valued dependency (MVD) exists when there is a dependency between two non-key attributes such that each non-key attribute can have multiple values for a single value of the other non-key attribute. For example, in a table of orders, if we have attributes for both the items ordered and the suppliers of those items, a multi-valued dependency would exist if a particular item could be supplied by multiple suppliers.
To illustrate this, let's consider a table of student course enrollments:
| Student ID | Course | Instructor | Grades | Textbooks |
|---|---|---|---|---|
| 1001 | Database Systems | Dr. Smith | A | Database Systems textbook |
| 1001 | Database Systems | Dr. Smith | A | SQL Programming textbook |
| 1001 | Web Development | Dr. Jones | B | Web Development textbook |
| 1002 | Web Development | Dr. Jones | A | Web Development textbook |
| 1002 | Software Engineering | Dr. Patel | B | Software Engineering textbook |
In this table, we can see that there are multi-valued dependencies between the Course and Textbooks attributes, because each course can have multiple textbooks. To bring this table to 4NF, we would need to split it into two tables: one for course enrollments, and another for textbooks, with a foreign key linking them:
Student_Courses table:
| Student ID | Course | Instructor | Grades |
|---|---|---|---|
| 1001 | Database Systems | Dr. Smith | A |
| 1001 | Web Development | Dr. Jones | B |
| 1002 | Web Development | Dr. Jones | A |
| 1002 | Software Engineering | Dr. Patel | B |
Textbooks table:
| Course | Textbook |
|---|---|
| Database Systems | Database Systems textbook |
| Database Systems | SQL Programming textbook |
| Web Development | Web Development textbook |
| Software Engineering | Software Engineering textbook |
In this revised schema, the multi-valued dependency has been eliminated, and the database is in 4NF.
