Explain About 4NF? Give One Example?

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 IDCourseInstructorGradesTextbooks
1001Database SystemsDr. SmithADatabase Systems textbook
1001Database SystemsDr. SmithASQL Programming textbook
1001Web DevelopmentDr. JonesBWeb Development textbook
1002Web DevelopmentDr. JonesAWeb Development textbook
1002Software EngineeringDr. PatelBSoftware 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 IDCourseInstructorGrades
1001Database SystemsDr. SmithA
1001Web DevelopmentDr. JonesB
1002Web DevelopmentDr. JonesA
1002Software EngineeringDr. PatelB

Textbooks table:

Database SystemsDatabase Systems textbook
Database SystemsSQL Programming textbook
Web DevelopmentWeb Development textbook
Software EngineeringSoftware Engineering textbook

In this revised schema, the multi-valued dependency has been eliminated, and the database is in 4NF.