MULTIVALUED DEPENDENCIES

1 / 14

# MULTIVALUED DEPENDENCIES - PowerPoint PPT Presentation

MULTIVALUED DEPENDENCIES. Ha Do. Functional Dependency. a. 4. b. Q. c. \$. Domain (X). Range (Y). Recall that if X uniquely determines Y, then Y is functionally dependent on X.

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

## MULTIVALUED DEPENDENCIES

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

### MULTIVALUED DEPENDENCIES

Ha Do

Functional Dependency

a

4

b

Q

c

\$

Domain (X)

Range (Y)

Recall that if X uniquely determines Y, then Y is functionally dependent on X.

You may recall math the terms Domain and Range. The domain is the set of all values possible of X and the range is the set of all possible values of Y.

The relation is a function because each of the elements of X maps exactly to one element of Y.

Functional Dependency

Anna

Smith

John

Jones

Lila

Cooper

Elsa

Chris

Employee (X)

Dependent (Y)

Multivalued Dependency
Definition of MVD
• A multivalued dependency is a full constraint between two sets of attributes in a relation.
• In contrast to the functional independency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependency is also referred as a tuple-generating dependency. The multivalued dependency also plays a role in 4NF normalization.
full constraint
• A constraint which expresses something about all attributes in a database. (In contrary to an embedded constraint.) That a multivalued dependency is a full constraint follows from its definition, where it says something about the attributes R − β.
• tuple-generating dependency
• A dependency which explicitly requires certain tuples to be present in the relation.
A Formal Definition

Let R be a relation schema and let and . The multivalued dependency α ->> β holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[α] = t2[α], there exist tuples t3 and t4 in r such thatt1[α] = t2[α] = t3[α] = t4[α]t3[β] = t1[β]t3[R − β] = t2[R − β]t4[β] = t2[β]t4[R − β] = t1[R − β]

Definition of MVD (cont.)
• A multivalued dependency on R, X ->>Y, says that if two tuples of R agree on all the attributes of X, then their components in Y may be swapped, and the result will be two tuples that are also in the relation.
• i.e., for each value of X, the values of Y are independent of the values of R-X-Y.

sue a p2 b1

sue a p1 b2

Then these tuples must also be in the relation.

Tuples Implied by name->->phones

If we have tuples:

sue a p1 b1

sue a p2 b2

Example

Here is possible data satisfying these MVD’s:

name areaCode phone beersLiked manf

Sue 650 555-1111 Bud A.B.

Sue 650 555-1111 WickedAle Pete’s

Sue 415 555-9999 Bud A.B.

Sue 415 555-9999 WickedAle Pete’s

But we cannot swap area codes or phones by themselves.

That is, neither name->->areaCode nor name->->phone

holds for this relation.

Properties of MVD
• f α ->> β, Then α ->> R − β
• If α ->> β and δ  γ , Then αδ ->> βγ
• If α ->> β and If β ->> γ, then α ->> γ - β

The following also involve functional dependencies:

• If α ->> β , then α ->> β
• If α -> β and β -> γ, then α -> γ – β
• A decomposition of R into (X, Y) and (X, R-Y) is a lossless-join decomposition if and only if X ->> Y holds in R.
Decomposition Theorem

The split of relations is guaranteed to be lossless if the intersection of the attributes of the new tables is a key of at least one of them.

The join connects tuples depending on the attribute (values) in the intersection. If these values uniquely identify tuples in the other relation we do not lose information.

Example of lossy decomposition

Decomposition

Original table

Reconstruction

References
• Silberschatz, Korth, Sudarshan. Database System Concepts, 5th Edition
• http://en.wikipedia.org/wiki/Multivalued_dependency
• http://www.stanford.edu/~ullman/dscb/pslides/mvds.ppt
• http://www.cs.sjsu.edu/faculty/lee/cs157/cs157a.html