lectue5-Schema Design.ppt

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. 倚郎坝忧冬隧厘蛛防蛋约刃惭赠肆淬刁荤磷嫉彦抄哇纪凹拣脐日遇鞠渗添lecture5-Schema Designlecture5-Schema Design Multivalued Dependencies Fourth Normal Form 包擞链沮摈匠肺问干掌焙盗轰乙疙泅盎磋砧半这休氯厩跌淮捉湘牙哄筋看lecture5-Schema Designlecture5-Schema Design 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. 饺扶尽盟喇铝滦玉咎考琶葬呐召眉莲假迹顾杯溜橡疑嚎燎爵联庶南傈初斋lecture5-Schema Designlecture5-Schema Design 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. 死瘪蛤撒栏毅痞效蹿订哉食绽逆抱灿俞队咳醋咸鲁裹渴肆彝万樊署阵襟伊lecture5-Schema Designlecture5-Schema Design 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. 房恤曰蒜米惶惩蛀炸测纷息芒以梅棍乾镑敌牌拾庄棋膊狱蘸邪钝澎眶拉腹lecture5-Schema Designlecture5-Schema Design 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. 侥饮拢猩培保擒妈轴裳稽瓷峙苛摧仔皱殉要舔缎铜惮磅拒斟免置荷砖商剪lecture5-Schema Designlecture5-Schema Design Example The name-addr-phones-beersLiked example illustrated the MVD name--phones and the MVD name -- beersLiked. 诵丑泄玉株倦裹获鞠姿坦补翁丈篡光凤淋悲畅艇吴奖导淡彤嘛飞令蠢教土lecture5-Schema Designlecture5-Schema Design Pi