Homework(3(solutions((4.3$1.$$πsname((σcolor=red(Parts)$⋈$Catalog)$⋈ Suppliers)$$2.$$πsid((σcolor=red$∨$color=green(Parts)$⋈$Catalog)$$3.$$πsid(σcolor=red$v$address=’221$Packer$Ave’((Parts$⋈$Catalog)$⋈$Suppliers))$$4.$$πsid(σcolor=red(Parts)$⋈$Catalog)$∩$πsid(σcolor=green(Parts)$⋈$Catalog)$$5.$$πsid,pid(Catalog)/πpid(Parts)$$6.$πsid,pid(Catalog)/πpid(σcolor=red(Parts))$$7.$πsid,pid(Catalog)/πpid(σcolor=red$∨$color=green(Parts))$$8.$πsid,pid(Catalog)/πpid(σcolor=red(Parts))$∪$πsid,pid(Catalog)/πpid(σcolor=green(Parts))$$9.$$πsid1,sid2(σcost1$>$cost2(ρ(CatPairs(1$→$sid1,$4$→$sid2,$3$→$cost1,$5$→$cost2),$Catalog$⋈pid=pid$Catalog)))$(I$am$assuming$here$that$the$result$of$joining$Catalog$to$Catalog$on$pid$is$a$relation$with$5$attributes$which$will$be$renamed$(sid1,$pid,$cost1,$sid2,$cost2),$in$that$order.)$$10.$$πpid(Catalog$⋈pid=pid,sid≠sid$Catalog)$$11.$This$is$easy$with$aggregation,$but$harder$with$just$the$operators$in$the$book.$$I$did$it$using$the$book$operators$just$to$show$ how $it$can$be$done.$$First$I$cross$Catalog$with$Catalog$to$get$a$relation$consisting$of$all$pairs$of$Catalog$records.$$Then$I$select$only$the$entries$where$the$cost$of$the$first$part$is$greater$than$or$equal$to$the$cost$of$the$second,$and$join$that$with$Suppliers$to$get$all$pairs$of$parts$supplied$by$Yosemite$Sham.$$This$will$ensure$that$there$is$at$least$one$part$that$occurs$in$a$pair$with$every$other$part$because$it$has$higher$(or$equal)$cost$than$all$other$parts.$$The$final$step$does$a$division$to$find$those$first$parts$in$the$pair$that$go$with$every$part—these$are$the$ones$that$have$maximum$cost.$$$$$$$$$ρ(CatPairs(1$→$sid1,$2$→$pid1,$3$→$cost1,$4$→$sid2,$5$→$pid2,$6$→$cost2),$Catalog$×$Catalog)$$$$$$$$ρ(CostPairs,$σsname=’Yosemite$Sham’(Supplier)$⋈sid1=sid$∧$sid2=sid$σcost1$≥$cost2(CatPairs))$$$$$$$$πpid1,pid2(CostPairs)/πpid2(CostPairs))$$12.$$πpid,sid(Catalog)/πsid(Catalog)$∩$πpid,cost(Catalog)/πcost(σcost$<$200(Catalog))$$4.4$1.$$Nothing—the$πsid$operation$returns$an$empty$set.$$(If$it$was$projecting$to$pid,$then$it$would$be$computing$the$names$of$suppliers$that$sell$red$parts$for$less$than$$100.$$I$feel$like$this$is$a$mistake$in$the$book.)$$2.$$Nothing—it’s$trying$to$project$to$sname$from$a$relation$that$only$has$sid.$$3.$$Names$of$suppliers$that$provide$red$parts$for$less$than$$100$and$green$parts$for$less$than$$100.$$4.$$sids$of$suppliers$that$provide$red$parts$for$ l ess$than$$100$and$green$parts$for$less$than$$100.$$5.$$Names$of$suppliers$that$provide$red$parts$for$less$than$$100$and$green$parts$for$less$than$$100.$$$$19.8$1.$$a.$$The$minimal$cover$is${AB$a>$C,$AC$a>$B,$BC$a>$A}.$$It$is$in$BCNF.$$$$$$$$$$$$$b.$$The$minimal$cover$is${AB$a>$C,$AC$a>$B,$BC$a>$A,$B$a>$D}.$$It$is$in$1NF.$$The$B$a>$D$FD$violates$3NF$because$B$is$a$proper$subset$of$a$key$(key$ABC).$$It$can$be$decomposed$into$ABC,$BD$which$are$both$in$BCNF$$$$$$$$$$$$c.$$$The$minimal$cover$is${AB$a>$C,$AC$a>$B,$BC$a>$A,$E$a>$G}.$$This$is$not$in$3NF$because$in$E$a>$G,$E$is$a$proper$subset$of$a$key$(key$ABCE).$$It$can$be$decomposed$into$ABCE,$EG$which$are$both$in$BCNF.$$
View Full Document
Unlocking...