版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡介
1、Answers for this exercise may vary because of different interpretations.Some possible FDs:Social Security numbernameArea codestateStreet address, city, statezipcodePossible keys:Social Security number, street address, city, state, area code, phone numberNeed streetaddress,city,statetouniquelydetermi
2、nelocation.A personcouldhavemultipleaddresses.The same istrueforphones.Thesedays,a personcouldhave a landline and a cellular phoneAnswers for this exercise may vary because of different interpretationsSome possible FDs:IDx-position, y-position, z-positionIDx-velocity, y-velocity, z-velocityx-positio
3、n, y-position, z-positionIDPossible keys:IDx-position, y-position, z-positionThe reason why the positions would be a key is no two molecules can occupy the same point.The superkeysareanysubsetthatcontainsA1.Thus,thereare2(n-1)suchsubsets,sinceeach of the n-1 attributes A2 through An may independentl
4、y be chosen in or out.The superkeysareanysubsetthat containsA1orA2.Thereare2(n-1)suchsubsetswhenconsideringAandthen-1attributesAthroughA .Thereare(n-2)suchsubsetswhen212nconsideringA2andthen-2attributesA3throughAn.We donotcountA1inthesesubsetsbecausetheyarealreadycountedinthefirstgroupofsubsets.Thet
5、otalnumberofsubsets is 2(n-1)+ 2 (n-2) .(n-2)The superkeys are any subset that contains A1,A 2 or A3,A 4. There are 2such subsetswhen considering12and the n-2 attributes3nThereare2(n-2)2(n-4)suchA,A A throughA .subsetswhenconsideringA 3,A 4andattributesA5throughAn alongwiththeindividualattributes1an
6、d2We getthe(n-4)termbecausewehavetodiscardthesubsetsthatAA .2contain the key A1 ,A 2 to avoid double counting. The total number of subsets is 2(n-2)+ 2(n-2) 2 (n-4) .(n-2)The superkeys are any subset that contains A1,A 2 or A1,A 3. There are 2such subsetswhen considering A123through An(n-3)such subs
7、ets,A and the n-2 attributes A. There are 2when consideringA 1,A 3and the n-3attributesA4throughAnWe do notcountA2inthesesubsets because they are already counted in the first group of subsets. The total numberof subsets is 2(n-2)+ 2 (n-3) .We couldtryinferencerulestodeducenew dependenciesuntilwe are
8、satisfiedwe havethem all.A moresystematicwayistoconsidertheclosuresofall15nonemptysetsofattributes.+=A,B+=B,C+ = ACD, and D+ = AD. Thus, theFor the single attributes we have Aonly new dependency we get with a single attribute on the left is CA.Now consider pairs of attributes:AB + = ABCD, so we get
9、new dependency ABD. AC+ = ACD, and ACD is nontrivial. AD+ =AD, so nothing new. BC+ = ABCD, so we get BCA, and BCD. BD+ = ABCD, giving us BDAand BDC. CD + = ACD, giving CDA.+ = ACD, but the closures of the other sets are eachFor the triples of attributes, ACDABCD. Thus, we get new dependencies ABCD,
10、ABDC, and BCDA.Since ABCD + = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above are:CA, ABD, AC D, BCA, BCD, BDA, BDC, CDA, ABCD, ABDC, and BCDA.From the analysis of closures above, we find that AB, BC, and BD are keys. All other setseither do not have ABCD as th
11、e closure or contain one of these three sets.The superkeys are all those that contain one of those three keys. That is, a superkey thatis not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeysare ABC, ABD, BCD, and ABCD.+ = ABCD, B+ = BCD, C+ = C, and D+ = D. Thus,i)
12、 For the single attributes we have Athe new dependencies are AC and AD.Now consider pairs of attributes:+= ABCD, AC+= ABCD, BC+AB= ABCD, AD= BCD, BD= BCD, CD= CD. Thus the newdependencies are ABC, ABD, ACB, ACD, ADB, ADC, BCD and BDC.For the triples of attributes, BCD+ = BCD, but the closures of the
13、 other sets are eachABCD. Thus, we get new dependencies ABCD, ABDC, and ACDB.Since ABCD + = ABCD, we get no new dependencies.The collection of 13 new dependencies mentioned above are:AC, AD, ABC, ABD, ACB, ACD, ADB, ADC, BCD, BDC, ABCD, ABDC and ACDB.ii)Forthesingleattributeswe haveA += A,B +=B,C +=
14、C,andD+ =D. Thus,there are no new dependencies.Now consider pairs of attributes:AB += ABCD, AC+ = AC, AD+ = ABCD, BC+ = ABCD, BD+ = BD, CD+ = ABCD. Thus the newdependencies are ABD, ADC, BCA and CDB.For the triples of attributes, all the closures of the sets are each ABCD. Thus, we getnew dependenci
15、es ABCD, ABDC, ACDB and BCDA.Since ABCD + = ABCD, we get no new dependencies.The collection of 8 new dependencies mentioned above are:ABD, ADC, BCA, CDB, ABCD, ABDC, ACDB and BCDA.iii)Forthesingleattributeswe haveA +=ABCD, B + =ABCD, C + =ABCD, andD+=ABCD. Thus, the new dependencies are AC, AD, BD,
16、BA, CA, CB, DB and DC.Since all the single attributes closures are ABCD, any superset of the single attributeswill also lead to a closure of ABCD. Knowing this, we can enumerate the rest of the newdependencies.The collection of 24 new dependencies mentioned above are:AC,AD,BD,BA,CA,CB,DB,DC,ABC,ABD,
17、ACB,ACD,ADB,ADC,BCA, BCD, BDA, BDC, CDA, CDB, ABCD, ABDC, ACDB and BCDA.Since A 1A2 AnC contains A1A2 An, then the closure of A1A2 AnC contains B. Thus it followsthat A1A2 AnCB.A ACC. ThusAACB. Using the concept of trivial dependencies, we can show that A1 2n1 2nA1A2 AnC BC.B B because of the FD AA
18、AFrom AA A E E E , we know that the closure contains B1 2n 1 2j1 2m1 2nB1B2 Bm. The B1B2 Bm and the E1E2 Ej combine to form the C1C2 Ck. Thus the closure of A1A2A E E E contains D as well. Thus, AAAEEED.n1 2j1 2n1 2jFrom A 1A2 AnC1C2 Ck , we know that the closure contains B1B2 Bm because of the FD A
19、1A2 AnB B B . The CC C also tell us that the closure of AA A CC C contains DD D . Thus,1 2m1 2k1 2n 1 2k1 2jA1A2 AnC1C2CkB1B2Bk D1D2 Dj .If attribute A represented Social Security Number and B represented a person s name, thenwe would assume AB but B A would not be valid because there may be many pe
20、ople with thesame name and different Social Security Numbers.Let attribute A represent Social Security Number, B represent gender and C represent name.SurelySocialSecurityNumberandgendercanuniquelyidentifya person s name (i.e.ABC). A Social Security Number can also uniquely identify a person s name
21、(i.e. AC).However, gender does not uniquely determine a name (i.e. BC is not valid).LetattributeArepresentlatitudeandBrepresentlongitude.Together,bothattributescan uniquely determine C, a point on the world map (i.e. ABC). However, neither A nor Bcan uniquely identify a point (i.e. AC and B C are no
22、t valid).Exercise 3.2.5Givena relationwithattributesA1A2 An,wearetoldthattherearenofunctionaldependencies of the form BB BC where B B Bis n-1 of the attributes from AA A1 2n-11 2n-11 2nand CistheremainingattributefromA1A2 An.Inthiscase,thesetB1B2 Bn-1andanysubset do not functionally determine C. Thu
23、s the only functional dependencies that we canmake are ones where C is on both the left and right hand sides. All of these functionaldependencies would be trivial and thus the relation has no nontrivial FD s.Exercise 3.2.6+Let s prove this by using the contrapositive. We wish to show that if Xis not
24、 a subsetof Y + , then it must be that X is not a subset of Y.If X+ is not a subset of Y+, there must be attributes A1A2 An in X +that are not in Y+. Ifany of these attributes were originally in X, then we are done because Y does not containany of the A1A2 An. However, if the A1A2 An were added by t
25、he closure, then we must examineissomethecasefurther.Assume thattherewas some FDCCCAAAwhereAA A12m12j12jsubset of A1A2 An. It must be then that C1C2 C or some subset of C1C C is in X. However,the attributes Cm2mA A are onlyC C cannot be in Y because we assumed that attributes A11 2m2nin X + and are
26、not in Y+. Thus, X is not a subset of Y.+?Y +.By proving the contrapositive, we have also proved if X ? Y, then XExercise 3.2.7+ is outlined on pg. 76. Using that algorithm, we can prove thatThe algorithm to find X(X +) + = X +. We will do this by using a proof by contradiction.Suppose that(X+) +X+.
27、Thenfor(X +) +,itmustbethatsomeFDallowedadditionalattributestobeaddedtotheoriginalset X+. For example,X+AwhereAissomeattribute not in X+. However, if this were the case, then X+ would not be the closure of X.The closure of X would have to include A as well. This contradicts the fact that we weregive
28、ntheclosureofX,X+.Therefore,itmust bethat(X+) += X+orelseX+isnottheclosure of X.Ifallsetsofattributesareclosed,thentherecannotbeanynontrivialn +functionaldependencies.SupposeA1A2.A n Bisanontrivialdependency.ThenA 1A2.AcontainsBand thus AA .Anis not closed.1 2If the only closed sets are? and A,B,C,D
29、, then the following FDs hold:ABACADBABCBDCACBCDDADBDCABCABDAC BAC DAD BAD CBC ABC DBD ABD CCD ACD BABC DABD CACD BBCD AIf the only closed sets are?, A,B and A,B,C,D, then the following FDs hold:BACACBCDDADBDCAC BAC DAD BAD CBC ABC DBD ABD CCD ACD BABC DABD CACD BBCD AExercise 3.2.9We canthinkofthis
30、problemasasituationwheretheattributesA,B,Crepresentcitiesandthefunctionaldependenciesrepresentone waypathsbetweenthecities.Theminimalbases are the minimal number of pathways that are needed to connect the cities. We do notwant to create another roadway if the two cities are already connected.The sys
31、tematic way to do this would be to check all possible sets of the pathways. However,we can simplify the situation by noting that it takes more than two pathways to visit thetwo other cities and come back. Also, if we find a set of pathways that is minimal, addingadditional pathways will not create a
32、nother minimal set.The two sets of minimal bases that were given in example 3.11 are:AB, BC, CAAB, BA, BC, CBThe additional sets of minimal bases are:CB, BA, ACAB, AC, BA, CAAC, BC, CA, CBWe needtocomputetheclosuresofallsubsetsofABC,althoughthereisnoneedtothinkabout theempty setorthe set of allthree
33、attributes. Herearethecalculationsfor the remaining six sets:A +=AB +=BC +=ACEAB + =ABCDEAC + =ACEBC + =ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: CAand ABC. Note that BC-A is true, but follows logically from C-A, and therefore may beomitted from our lis
34、t.We needtocomputetheclosuresofallsubsetsofABC,althoughthereisnoneedtothinkabout theempty setorthe set of allthreeattributes. Herearethecalculationsfor the remaining six sets:+A =AD+C =CAB + =ABDE+AC =ABCDE+We ignore D and E, so a basis for the resulting functional dependencies for ABC is: ACWe need
35、tocomputetheclosuresofallsubsetsofABC,althoughthinkabout theempty setorthe set of allthreeattributes. Herefor the remaining six sets:+B =B+C =CB.thereisno needtoarethecalculations+AB =ABD+AC =ABCDE+BC =ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: ACBand BC
36、A.We needtocomputetheclosuresofallsubsetsofABC,althoughthereisnoneed tothinkabout theempty setorthe set of allthreeattributes. Herearethecalculationsfor the remaining six sets:A +=ABCDEB+=ABCDEC +=ABCDE+AB =ABCDEAC + =ABCDEBC + =ABCDEWe ignore D and E, so a basis for the resulting functional depende
37、ncies for ABC is: AB,BC and C A.For step one of Algorithm 3.7, suppose we have the FD ABCDE. We want to use Armstrong sAxioms to show that ABCD and ABCE follow. Surely the functional dependencies DED andDEEholdbecausetheyaretrivialandfollowthereflexivityproperty.Usingthetransitivity rule, we can der
38、ive the FD ABCD from the FDs ABCDE and DED. Likewise, wecan do the same for ABCDE and DEE and derive the FD ABCE.For steps two through four of Algorithm 3.7, suppose we have the initial set of attributesof the closure as ABC. Suppose also that we have FDs CD and D E. According to Algorithm3.7,theclo
39、sureshouldbecomeABCDE. TakingtheFD CD andaugmentingbothsideswithattributes AB we get the FD ABCABD. We can use the splitting method in step one to getthe FD ABCD. Since D is not in the closure, we can add attribute D. Taking the FD DEand augmenting both sides with attributes ABC we get the FD ABCDAB
40、CDE. Using again thesplitting method in step one we get the FD ABCDE. Since E is not in the closure, we canadd attribute E.Givena set of FDs,wecan provethata FD FfollowsbytakingtheclosureoftheleftsideofFDF.ThestepstocomputetheclosureinAlgorithm3.7canbemimickedbyArmstrong s axioms and thus we can pro
41、ve F from the given set of FDs using Armstrong saxioms.InthesolutiontoExercise3.2.1wefoundthatthereare14nontrivialdependencies,including the three given ones and eleven derived dependencies. They are: CA, CD, DA,ABD, ABC, ACD, BCA, BCD, BDA, BDC, CDA, ABCD, ABDC, and BCDA.We also learned that the th
42、ree keys were AB, BC, and BD. Thus, any dependency above thatdoes not have one of these pairs on the left is a BCNF violation. These are: CA, CD,DA, ACD, and CDA.One choice is to decompose using the violation CD. Using the above FDs, we get ACD andBC asdecomposedrelations.BC issurelyinBCNF,sinceanyt
43、wo-attributerelationis.Using Algorithm 3.12 to discover the projection of FDs on relation ACD, we discover thatACD is not in BCNF since C is its only key. However, DA is a dependency that holds inABCD and therefore holds in ACD. We must further decompose ACD into AD and CD. Thus, thethree relations
44、of the decomposition are BC, AD, and CD.Bycomputingtheclosuresofall15nonemptysubsetsof ABCD,we can findallthenontrivial FDs. They are BC, BD, ABC, ABD, BCD, BDC, ABC D and ABDC. From theclosures we can also deduce that the only key is AB. Thus, any dependency above that doesnot contain AB on the lef
45、t is a BCNF violation. These are: BC, BD, BCD and BDC.One choice is to decompose using the violation BC. Using the above FDs, we get BCD andABasdecomposedrelations.ABissurelyinBCNF,sinceanytwo-attributerelationis.Using Algorithm 3.12 to discover the projection of FDs on relation BCD, we discover tha
46、t BCD is in BCNF since B is its only key and the projected FDs all have B on the left side.Thus the two relations of the decomposition are AB and BCD.C, BCD, CDA, ADB, ABD, ADC, BCA, CDB, ABCD, ABDC, ACDB and BCDA.We also found out that the keys are AB, AD, BC, and CD. Thus, any dependency above tha
47、tdoes not have one of these pairs on the left is a BCNF violation. However, all of the FDscontain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF.B, BC, CD, DA, AC, AD, BD, BA, CA, CB, DB, DC, ABC, ABD, ACB,AC D, AD B, AD C, BC A,
48、 BC D,BD A,BDC,CDA,CD B,ABC D, ABDC,ACD BandBCD A.We also found out that the keys are A,B,C,D. Thus, any dependency above that does not haveone of these attributes on the left is a BCNF violation. However, all of the FDs contain akey on the left so there are no BCNF violations.No decomposition is ne
49、cessary since all the FDs do not violate BCNF.Bycomputingtheclosuresofall31nonemptysubsetsofABCDE,wecanfindallthenontrivialFDs.Theyare ABC,DEC,BD,ABD, BC D, BE C, BE D, ABC D,ABDC,ABEC, ABED, ADEC, BCED, BDEC, ABCED, and ABDEC. From the closures we can alsodeduce that the only key is ABE. Thus, any
50、dependency above that does not contain ABE ontheleftisaBCNF violation.Theseare:AB C,DEC,BD,ABD,BC D,BEC,BED,ABC D, ABDC, ADEC, BCED and BDEC.One choice is to decompose using the violation ABC. Using the above FDs, we get ABCD andABE asdecomposedrelations.UsingAlgorithm3.12todiscovertheprojectionofFD
51、sonrelation ABCD, we discover that ABCD is not in BCNF since AB is its only key and the FDB D followsforABCD. UsingviolationBD tofurtherdecompose, we getBD andABC asdecomposedrelations.BDisinBCNFbecauseitisatwo-attributerelation.UsingAlgorithm 3.12 again, we discover that ABC is in BCNF since AB is
52、the only key and ABCis the only nontrivial FD. Going back to relation ABE, following Algorithm 3.12 tells usthatABE isinBCNF becausetherearenokeysandnonontrivialFDs. Thusthethreerelations of the decomposition are ABC, BD and ABE.Bycomputingtheclosuresofall31nonemptysubsetsofABCDE,wecanfindallthenont
53、rivial FDs. They are: CB, CD, CE, DB, DE, ABC, ABD, ABE, ACB, ACD,ACE, ADB, ADC, ADE, BCD, BCE, BDE, CDB, CDE, CEB, CED, DEB, ABCD,ABC E,ABDC,ABD E,ABEC,ABED,ACD B,ACD E,ACE B,ACE D, ADEB,ADEC,BCD E,BCE D,CDE B,ABCD E,ABCE D,ABDE C andACDE B.Fromtheclosureswecanalsodeducethatthekeysare AB,AC andAD.
54、Thus,any dependencyabovethatdoesnotcontain one of the above pairs on the left is a BCNF violation. These are: CB, CD, CE,D B,DE, BCD, BCE, BDE, CDB, CDE, CEB, CED, DE B, BCDE, BCE D and CDE B.One choice is to decompose using the violation DB. Using the above FDs, we get BDE andABC asdecomposedrelati
55、ons.UsingAlgorithm3.12todiscovertheprojectionofFDsonrelation BDE, we discover that BDE is in BCNF since D, BD, DE are the only keys and alltheprojectedFDscontainD,BD,orDEintheleftside.Going backtorelationABC,following Algorithm 3.12 tells us that ABC is not in BCNF because since AB and AC are itsonl
56、y keys and the FD CB follows for ABC. Using violation CB to further decompose, weget BC and AC as decomposed relations. Both BC and AC are in BCNF because they are two-attribute relations. Thus the three relations of the decomposition are BDE, BC and AC.Exercise 3.3.2Yes, we will get the same result
57、. Both AB and A+BC have A on the left side and part ofthe processofdecompositioninvolvesfindingtoformone decomposed relationand AAplus the rest of the attributes not in A+ as the second relation. Both cases yield thesame decomposed relations.Exercise 3.3.3Yes, we will still get the same result. Both
58、 AB and A BC have A on the left side andpart of the process of decomposition involves finding A+ to form one decomposed relationand A plus the rest of the attributes not in A+ as the second relation. Both cases yieldthe same decomposed relations.This is taken from Example 3.21 pg. 95.Suppose that an
59、 instance of relation R only contains two tuples.ABC123425The projectionsofR onto therelationswith schemas A,B andB,C are:BCAB23122542If we do a natural join on the two projections, we will get:ABC123125423425The result of the natural join is not equal to the original relation R.This is the initial
60、tableau:ABCDEabcd1e1a1bcde1ab1cd1eThis is the final tableau after applying FDs BE and CE A.ABCDEabcd1e1abcde1ab1cd1eSince there is not an unsubscripted row, the decomposition for R is not lossless for this set of FDs.We can usethe finaltableau as an instance ofR as an example for why the join is not
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 2024-2030年全球及中國便攜式手持裂隙燈行業(yè)市場現(xiàn)狀供需分析及市場深度研究發(fā)展前景及規(guī)劃可行性分析研究報(bào)告
- 2024-2030年全球及中國SUV車載充電器CPU行業(yè)市場現(xiàn)狀供需分析及市場深度研究發(fā)展前景及規(guī)劃可行性分析研究報(bào)告
- 2024-2030年全球及中國GCC室內(nèi)位置服務(wù)(LBS)行業(yè)市場現(xiàn)狀供需分析及市場深度研究發(fā)展前景及規(guī)劃可行性分析研究報(bào)告
- 2024-2030年全球與中國轉(zhuǎn)換座行業(yè)發(fā)展現(xiàn)狀及未來競爭戰(zhàn)略規(guī)劃報(bào)告
- 2024-2030年全球與中國美縫膠行業(yè)供需現(xiàn)狀及投資風(fēng)險(xiǎn)分析報(bào)告
- 2024-2030年先進(jìn)功能材料行業(yè)市場現(xiàn)狀供需分析及投資評(píng)估規(guī)劃分析研究報(bào)告
- 2024-2030年全球與中國汽車儀表行業(yè)競爭態(tài)勢(shì)及投資盈利預(yù)測(cè)報(bào)告
- 2024-2030年全球與中國建筑PVB薄膜行業(yè)市場發(fā)展分析及發(fā)展趨勢(shì)與投資前景研究報(bào)告
- 2024-2030年全球與中國化妝品玻璃包裝供需現(xiàn)狀及未來銷售趨勢(shì)預(yù)測(cè)報(bào)告
- 2024-2030年全息箔市場發(fā)展態(tài)勢(shì)展望及競爭格局預(yù)測(cè)報(bào)告
- 中國古典園林-留園調(diào)研分析
- 學(xué)會(huì)傾聽小學(xué)二年級(jí)心理健康教育課件
- 升壓站測(cè)量放線施工方案
- 職業(yè)院校技能大賽(零部件測(cè)繪與CAD成圖技術(shù)賽項(xiàng))備考試題庫-上(單選題)
- 廚房風(fēng)險(xiǎn)點(diǎn)告知卡
- 2021版中醫(yī)癥候醫(yī)保對(duì)應(yīng)中醫(yī)癥候醫(yī)保2
- 機(jī)械設(shè)計(jì)基礎(chǔ)第4版柴鵬飛課后參考答案
- 施工現(xiàn)場巡查崗位職責(zé)
- 滬外教2011版七年級(jí)英語上冊(cè)《UNIT1 Back to School》教案及教學(xué)反思
- 環(huán)境保護(hù)稅課件
- 2024屆江蘇省蘇州市振華中學(xué)數(shù)學(xué)七年級(jí)第一學(xué)期期末質(zhì)量檢測(cè)模擬試題含解析
評(píng)論
0/150
提交評(píng)論