4nf multivalued dependency
Download
Skip this Video
Download Presentation
4NF & Multivalued Dependency

Loading in 2 Seconds...

play fullscreen
1 / 16

4NF & Multivalued Dependency - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

4NF & Multivalued Dependency. By Kristina Miguel. Review. Superkey – a set of attributes which will uniquely identify each tuple in a relation Candidate key – a minimal superkey Primary key – a chosen candidate key Secondary key – all the rest of candidate keys

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 4NF & Multivalued Dependency' - abe


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
review
Review
  • Superkey – a set of attributes which will uniquely identify each tuple in a relation
  • Candidate key – a minimal superkey
  • Primary key – a chosen candidate key
  • Secondary key – all the rest of candidate keys
  • Prime attribute – an attribute that is a part of a candidate key (key column)
  • Non-prime attribute – a non-key column
review cont
Review (cont.)
  • 1NF
    • Eliminate repeating groups.
      • Make a separate table for each set of related attributes, and give each table a primary key.
  • 2NF
    • Eliminate redundant data.
      • Each attribute must be functionally dependent on the primary key.
      • If an attribute depends on only part of a multi-valued key, remove it to a separate table.
  • 3NF
    • Eliminate columns not dependent on key.
      • If attributes do not contribute to a description of the key, remove them to a separate table.
      • Any transitive dependencies are moved into a smaller table.
  • BCNF
    • Every determinant in the table is a candidate key.
      • If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
  • All normal forms are additive, in that if a model is in 3NF, it is by definition also in 2NF and 1NF.
multivalued dependency mvd
Multivalued Dependency (MVD)
  • A MVD XY,
    • Holds for some relation R, so that when you fix the values for one set of attributes, then the values in certain other attributes are independent of the values of all the other attributes in the relation.
    • Is an assertion that two attributes or sets of attributes are independent of one another.
    • For each value of X, the values of Y are independent of the values of R-X-Y.
mvd cont
MVD (cont.)
  • More precisely, for MVD AB
    • For each pair of tuples t and u of relation R that agree on all the A’s, we can find in R some tuple v that agrees
      • With both t and u on the A’s,
      • With t on the B’s, and
      • With u on all attributes of R that are not among the A’s or B’s.
mvd cont1
MVD (cont.)
  • Representation of XY

XY others

equal

exchange

mvd example
MVD Example

Drinkers(name, addr, phones, beersLiked)

  • A drinker’s phones are independent of the beers they like.
    • namephones and namebeersLiked.
  • Thus, each of a drinker’s phones appears with each of the beers they like in all combinations.
mvd example cont
MVD Example (cont.)
  • Tuples Implied by namephones
    • If we have tuples:
    • Then these tuples must also be in the relation.

name addr phones beersLiked

sue a p1 b1

sue a p2 b2

sue a p2 b1

sue a p1 b2

slide9
4NF
  • Definition
    • A relation R is in 4NF if and only if, for every one of its non-trivial multivalued dependencies XY, X is a superkey—that is, X is either a candidate key or a superset thereof.
      • NontrivialMVD means that:
      • Y is not a subset of X, and
      • X and Y are not, together, all the attributes.
decomposition into 4nf
Decomposition into 4NF
  • If XY is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF.
    • XY is one of the decomposed relations.
    • All but Y – X is the other.
decomposition into 4nf method
Decomposition into 4NF Method
  • Find a 4NF violation in R, say AB, where A is not a superkey.
  • If there is such a 4NF violation, break the schema for the relation R that has the 4NF violation into two schemas.
    • R1, whose schema is A’s and B’s.
    • R2, whose schema is the A’s and all attributes of R that are not among the A’s or B’s.
  • Find the FD’s and MVD’s that hold in R1 and R2. Recursively decompose R1 and R2 with respect to their projected dependencies.
4nf decomposition example
4NF Decomposition Example

Drinkers(name, addr, phones, beersLiked)

FD: nameaddr

MVD’s: namephones

namebeersLiked

  • Key is {name, phones, beersLiked}.
  • All dependencies violate 4NF.
4nf decomposition example cont
4NF Decomposition Example (cont.)
  • Decompose using nameaddr:
  • Drinkers1(name, addr)
    • In 4NF; only dependency is nameaddr.
  • Drinkers2(name, phones, beersLiked)
    • Not in 4NF. MVD’s namephones and namebeersLiked apply. No FD’s, so all three attributes form the key.
4nf decomposition example cont1
4NF Decomposition Example (cont.)
  • Decompose Drinkers2
    • Either MVD name ->-> phones or name ->-> beersLiked tells us to decompose to:
      • Drinkers3(name, phones)
      • Drinkers4(name, beersLiked)
summary
Summary
  • A multivalued dependency is a statement that two sets of attributes in a relation have sets of values that appear in all possible combinations.
  • If a relation is in 4NF, then every nontrivial MVD is really an FD with a superkey on the left.
references
References
  • http://www.datamodel.org/NormalizationRules.html
  • http://www.almaden.ibm.com/cs/people/fagin/tods77.pdf
  • http://www.bkent.net/Doc/simple5.htm
  • http://infolab.stanford.edu/~ullman/dscb.html
ad