Download
1 / 14
140 likes | 278 Views
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.
E N D
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: name addr phones beersLiked 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
