MultiValued Dependencies and Fourth Normal Form A multivalued

Multi-Valued Dependencies and Fourth Normal Form A multi-valued dependency occurs when a determinant determines more than one dependent, and the dependents are independent of each other Ex. : course implies teacher; course implies text, where teacher and text are independent A table is said to be in 4 NF if and only if it is in the BCNF and multi-valued dependencies are functional dependencies

A tuple in EMP relation represent the fact that an employee whose name os Ename works on the project whose name is Pname and has a dependent whose name is Dname. An Employee can work on several projects and may have several dependents, and the employee’s projects and dependents are independent of one another. To keep the relation state consistent, we must have a separate tuple to represent every combination of an employee’s dependent and an employee’s project. This constraint is specified as a multivalued dependency on the EMP relation.

Multivalued Dependencies and Fourth Normal Form (a) The EMP relation with two MVDs: ENAME —>> PNAME and ENAME —>> DNAME. (b) Decomposing the EMP relation into two 4 NF relations EMP_PROJECTS and EMP_DEPENDENTS.

Consider this example of a database of teaching courses, the books recommended for the course, and the lecturers who will be teaching the course: Teaching database Course Book Lecturer AHA Silberschatz John D AHA Nederpelt William M AHA Silberschatz William M AHA Nederpelt John D AHA Silberschatz Christian G AHA Nederpelt Christian G OSO Silberschatz John D OSO Silberschatz William M

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa. Put formally, there are two multivalued dependencies in this relation: {course} —>>{book} and equivalently {course} —>> {lecturer}.

Multivalued Dependencies and Fourth Normal Form Definition: A relation schema R is in 4 NF with respect to a set of dependencies F (that includes functional dependencies and multivalued dependencies) if, for every nontrivial multivalued dependency X —>> Y in F+, X is a superkey for R. Note: F+ is the (complete) set of all dependencies (functional or multivalued) that will hold in every relation state r of R that satisfies F. It is also called the closure of F.

Multivalued Dependencies and Fourth Normal Form Decomposing a relation state of EMP that is not in 4 NF. (a) EMP relation with additional tuples. (b) Two corresponding 4 NF relations EMP_PROJECTS and EMP_DEPENDENTS.

Fifth Normal Form Join dependency: relation R satisfies the JD (X, Y, …Z) it is the join of its projections on X, Y, …Z where X, Y, …Z are subsets of the set of attributes of R A relation is in 5 NF/PJNF (Projection-join normal form) every join dependency in R is implied by the candidate keys of R 5 NF is the ultimate normal form with respect to projection and join

Fifth Normal Form S# P# J# J# S# P# S 1 P 1 J 2 S 1 P 1 SP S 1 P 2 J 1 S 1 P 2 S 2 P 1 J 1 S 2 P 1 S 1 P 1 JS Relation SPJ Join over P# PJ S# P# J# S 1 P 1 J 2 Join over (J#, S#) S 1 P 1 J 1 S 1 P 2 J 1 • SPJ is the join of all of its three projections, S 2 P 1 J 2 Spurious not of any two! S 29 P 1 J 1