Chapter15-FurtherDependencies.pptx

# Multivalued dependencies and fourth normal form

• 57

This preview shows pages 44–50. Sign up to view the full content.

Multivalued Dependencies and Fourth Normal Form Definition: A multivalued dependency ( MVD ) X >> Y specified on relation schema R , where X and Y are both subsets of R , specifies the following constraint on any relation state r of R : If two tuples t 1 and t 2 exist in r such that t 1 [ X ] = t 2 [ X ], then two tuples t 3 and t 4 should also exist in r with the following properties, where we use Z to denote ( R 2 ( X υ Y )): t 3 [ X ] = t 4 [ X ] = t 1 [ X ] = t 2 [ X ]. t 3 [ Y ] = t 1 [ Y ] and t 4 [ Y ] = t 2 [ Y ]. t 3 [ Z ] = t 2 [ Z ] and t 4 [ Z ] = t 1 [ Z ]. An MVD X >> Y in R is called a trivial MVD if (a) Y is a subset of X , or (b) X υ Y = R . Further Dependencies 44

This preview has intentionally blurred sections. Sign up to view the full version.

Multivalued Dependencies and Fourth Normal Form Inference rules for functional and multivalued depedencies : IR1 ( reflexive rule for FDs ): If X Y , then X Y . IR2 ( augmentation rule for FDs ): { X Y }  XZ YZ . IR3 ( transitive rule for FDs ): { X Y , Y Z }  X Z . IR4 ( complementation rule for MVDs ): { X >> Y }  X >> ( R ( X Y ))}. IR5 ( augmentation rule for MVDs ): If X >> Y and W Z then WX >> YZ . IR6 ( transitive rule for MVDs ): { X >> Y , Y >> Z }  X >> ( Z - Y ). IR7 ( replication rule for FD to MVD ): { X > Y }  X >> Y . IR8 ( coalescence rule for FDs and MVDs ): If X >> Y and there exists W with the properties that (a) W Y is empty, (b) W > Z , and (c) Y Z , then X > Z . Further Dependencies 45
Multivalued Dependencies and Fourth Normal Form Definition: A relation schema R is in 4NF 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 . Further Dependencies 46

This preview has intentionally blurred sections. Sign up to view the full version.

Multivalued Dependencies and Fourth Normal Form Further Dependencies 47 Fig. 15.4 Decomposing a relation state of EMP that is not in 4NF. (a) EMP relation with additional tuples. (b) Two corresponding 4NF relations EMP_PROJECTS and EMP_DEPENDENTS.
Multivalued Dependencies and Fourth Normal Form Non-additive( Lossless) Join Decomposition into 4NF Relations: PROPERTY NJB’ The relation schemas R 1 and R 2 form a lossless (non-additive) join decomposition of R with respect to a set F of functional and multivalued dependencies if and only if ( R 1 R 2 ) >> ( R 1 - R 2 ) or by symmetry, if and only if ( R 1 R 2 ) >> ( R 2 - R 1 )). Further Dependencies 48

This preview has intentionally blurred sections. Sign up to view the full version.

Multivalued Dependencies and Fourth Normal Form Algorithm 15.7: Relational decomposition into 4NF relations with non-additive join property Input: A universal relation R and a set of functional and multivalued dependencies F.
This is the end of the preview. Sign up to access the rest of the document.
• Fall '09
• SUNANHAN
• Database normalization

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern