lecture5-Schema Design.pptVIP

Current Trends Object DBs and Object-Relational DB’s may permit complex attributes 1st normal form unnecessary Data Warehouses huge historical databases, seldom or never updated after creation joins expensive or impractical argues against normalization Everyday relational DBs aim for BCNF, settle for 3NF * key: (AB) 1NF Key: E, (AB) 2NF Key: (AB) (AD) 3NF Key: A,B BCNF Key: C 2NF Key: A, D BCNF Key: (AD) 1NF 3NF and BCNF, Continued We can get (1) with a BCNF decomposition. Explanation needs to wait for relational algebra. We can get both (1) and (2) with a 3NF decomposition. But we can’t always get (1) and (2) with a BCNF decomposition. street-city-zip is an example. Multivalued Dependencies Fourth Normal Form A New Form of Redundancy Multivalued dependencies (MVD’s) express a condition among tuples of a relation that exists when the relation is trying to represent more than one many-many relationship. Certain attributes are independent of one another, and their values must appear in all combinations. Example Drinkers(name, addr, phones, beersLiked) A drinker’s phones are independent of the beers they like. Thus, each of a drinker’s phones appears with each of the beers they like in all combinations. This repetition is unlike FD redundancy. name-addr is the only FD. Tuples Implied by Independence If we have tuples: name addr phones beersLiked sue a p1 b1 sue a p2 b2 sue a p2 b1 sue a p1 b2 Then these tuples must also be in the relation. Definition of MVD A multivalued dependency (MVD) X --Y is an assertion that if two tuples of a relation agree on all the attributes of X, then their components in the set of attributes Y may be swapped, and the result will be two tuples that are also in the relation. Example The name-addr-phones-beersLiked example illustrated the MVD name--phones and the MVD name -- beersLiked. Picture of MVD X --Y X Y others equal exchange MVD Rules Every FD is an MVD (promotion ). If X -Y, then swappi