Question

Given the following relation schemas and the sets of FD's: a- R(A,B,C,D) F={ABẠC,C7D, D´A, BC+C} b- R(A,B,C,D) F={BẠC, BD, AD>B} C- R(A,B,C,D) F={AB-C, DC+D, CD+A, AD+B} d- R(A,B,C,D) F={AB=C, C+D, D™B, DE} e- R(A, B, C, D, E) F= {AB+C, DB+E, AE>B, CD+A, ECD} In each case, (i) Give all candidate keys (ii) Indicate the BCNF violation Give the minimal cover and decompose R into a collection of relations that are BCNF. Is it lossless? Does it preserve the dependencies? Indicate the 3NF, and decompose R into a 3 NF decomposition (iv)

Answer 1

PO up a) R(A, B, C, D) F= { ABC, CD, D A, BLTe3 p.) 4 + 4 AS Bt - SB} et = 3 C, D, A so the candidate keys are } Lots D,AB 4B, BCt, Bot | ART & A, B, C, D } . BC t & A, B, C, D u AC+₂ & A,C,D} 4Dt & A, D } BOT & A, B, C, D} . I co t = 4A, C, D} w for the BCNF Nipletion, Relation R " said to be in BCNF ill whenever there is a non triwasan | AT AL .. AND BIB₂ - .. BN for R(Al Azr. And 1 6 a supukey 48 C. The closure of ABT = {A, B, CD} therefore the IL-Hos of this FD is superkey Son . CAD: The set closure of ct å { CIDAY: the litis I of this to is not a surf supukes B CNF Wolation is A: The not a set closure of at uş D,A3 & the LHS superley Bent violation e y. But Vialation L BCAC Louis of The this set closure of Bet u & A, B, C, D3: the FD is superkey. buzzet

Page:- Page: Date: lic) pg dn F- & AB C 7 CAD, DA, BCA C } a A B C ✓ CABY = (AB CD) c→DV (ABt) (AB) ASSE DAU ct a Aco) BC CX ct (c) It a SADY sta&D) BCT - Ş BCDAS So, the minimal camera BCT = { B C DA} ABC С - D 4 ter RABCO) AB C, CD, DA BLC b. Candidate key: 46t, BCT, BDT The table is in INF as there i no multivalued hf AB CD) attribute a INFT 2NE Bi ABCD pril (CD) ARIZODA) Ri/ AB CD) Ru(co) L Riz/DA) buzzet

Page Now for BENF R(ABCD) try (CD) DRI DA | For checking the composition to be losseless the taking the union of Ru & R12. Both both will give a relation RICARD) which is lossy So it it doesn't preserve the dependencia. F= { BC, BD, ADB ? IR (A, B, GD) Bt & B C,D} + → şcy liv the s o atafo } AB+ = { A B C D u 4 ct a $46) 407 - ABCD 3 BCt = $ B C D } Bot $ BUD} o ABT & ADt are candidate key. 0 0 w. The BCNF closure of Bt verlation { B CD} - L.Hs of this FD is not sukuke The closure of AD to { AB CD} . CHis of this FD is Super Rey buzzet

til R(A,B,C,D) F = {BC, BD, AD B] Bt - BCD ? B C BD ADABV B- SBD) BtoBoei ADT - ADBLU Apt. SAD3 So the minimal cover is B-C, BD, ADB. S c andidate key 487,407 urbin) INF The relation is in INF, no multivalue uttribute. - 2NER 44 A8BC) Ru (BOD) SNE R, (ABCD) Ru (Bco) 4 R12 (BD) BEN RI (AB CD) Ru(BC) LR12 (BD) T for checking the Ru & R2 which I so this is a pressure the PD. decomposition we take the doein't give the relation lossy decomposition which union of RI (ABCA) doesn't buzzet

Comment:-Same procedure can be followed in other parts too....I have a time limit to solve question therefore i am able to solve two parts with 4 subparts each....