版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
1、 NormalizationQuestionsandAnswersDatabaseSystems,CSCI4380-01SibelAdalOctober28,2002Question1SupposeyouaregivenarelationR=(A,B,C,D,E)withthefollowingfunctionaldependencies:CEfD,DfB,CfA.Findallcandidatekeys.IdentifythebestnormalformthatRsatisfies(1NF,2NF,3NF,orBCNF).IftherelationisnotinBCNF,decomposei
2、tuntilitbecomesBCNF.Ateachstep,identifyanewrelation,decomposeandre-computethekeysandthenormalformstheysatisfy.Answer.TheonlykeyisC,ETherelationisin1NFDecomposeintoR1=(A,C)andR2=(B,C,D,E).R1isinBCNF,R2isin2NF.DecomposeR2into,R21=(C,D,E)andR22=(B,D).BothrelationsareinBCNF.Question2Supposeyouaregivenar
3、elationR=(A,B,C,D,E)withthefollowingfunctionaldependencies:BCfADE,DfB.Findallcandidatekeys.IdentifythebestnormalformthatRsatisfies(1NF,2NF,3NF,orBCNF).IftherelationisnotinBCNF,decomposeituntilitbecomesBCNF.Ateachstep,identifyanewrelation,decomposeandre-computethekeysandthenormalformstheysatisfy.Answ
4、er.ThekeysareB,CandC,DTherelationisin3NFItcannotbeputintoBCNF,evenifIremoveDandputintoarelationoftheform(B,C,D)(IneedCforthefunctionaldependency),theresultingrelationwouldnotbeinBCNF.Question3SupposeyouaregivenarelationR=(A,B,C,D,E)withthefollowingfunctionaldependencies:BDfE,AfC.Showthatthedecomposi
5、tionintoR1=(A,B,C)andR2=(D,E)islossy.Youcanshowusinganymethod.Mysuggestionistoshowhowspurioustuplesresultfromthisdecompositionwithrespecttothetablebelow:ABCDE1234518344FindasingledependencyfromasingleattributeXtoanotherattributeYsuchthatwhenyouaddthedependencyXYtotheabovedependencies,thedecompositio
6、ninpartaisnolongerlossy.Answera.Ifweweretodecomposetherelationsinto:ABCDE1234518344andthenjointhetwo(inthiscasewithacartesianproduct),wewouldget:ABCDE12345183451234418344Tuples2and3arenotintheoriginalrelation.Hence,thisdecompositionislossy.b.Thisdecompositioncannotbemadelossless.Theproblemisthereisn
7、olongerawaytomakedecompositionoftheform(A,B,C),(C,D,E)canbemadelosslessbyaddinganFDBC.sureBDEholdsacrosstworelationssincesureBDEholdsacrosstworelationssincetheydonotshareanyattributes.However,alossyQuestion4YouaregiventhefollowingsetoffunctionaldependenciesforarelationR(A,B,C,D,E,F),F=ABC,DCAE,EF.Wh
8、atarethekeysofthisrelation?IsthisrelationinBCNF?Ifnot,explainwhybyshowingoneviolation.Isthedecomposition(A,B,C,D)(B,C,D,E,F)adependencypreservingdecomposition?Ifnot,explainbriefly.Answer.Whatarethekeysofthisrelation?A,B,DandB,C,D.IsthisrelationinBCNF?Ifnot,explainwhybyshowingoneviolation.No,allfunct
9、ionaldependenciesareactuallyviolatingthis.Nodependencycontainsasuperkeyonitsleftside.Isthedecomposition(A,B,C,D)(B,C,D,E,F)adependencypreservingdecomposition?Ifnot,explainbriefly.Yes,ABCandDCAarepreservedinthefirstrelation.DCEandEFarepreservedinthesecondrelation.Question5Youaregiventhebelowfunctiona
10、ldependenciesforrelationR(A,B,C,D,E),F=ABC,ABD,DA,BCD,BCE.IsthisrelationisinBCNF?Ifnot,showalldependenciesthatviolateit.Isthisrelationin3NF?Ifnot,showalldependenciesthatviolateit.Isthefollowingdependencyimpliedbytheabovesetofdependencies?Ifso,showhowusingtheAmstrongsAxiomsgiveninthebook(p.362-363):A
11、BCtAEAnswer.Keysfortherelation:A,B,B,D,B,C.NotinBCNFsinceDtAdoeshaveasuperkeyonthelefthandside.In3NFsinceinDtA,Aispartofakey.BCtE(given)ABCtAEbytheaugmentationrule.Question6YouaregiventhetablebelowforarelationR(A,B,C,D,E).Youdonotknowthefunctionaldependenciesforthisrelation.Thisquestionisindependent
12、ofQuestion2above.ABCDEa1221s1ae2364e2ba1991b5cb2132z8dSupposethisrelationisdecomposedintothefollowingtwotables:R1(A,B,C,D)andR2(A,C,E).Isthisdecompositionlossless?Explainyourreasoning.Answer.R1R2R1R2ABCDEABCDACEa1221s1aa1221s1a1ae2364e2be2364e2e4ba1991b5ca1991b5a1cb2132z8db2132z8b2da1221s1aa1991b5cS
13、incethelasttworowsarenotintheoriginalrelation,thenthisdecompositionislossy.Question7YouaregiventhebelowsetoffunctionaldependenciesforarelationR(A,B,C,D,E,F,G),F=ADtBF,CDtEGC,BDtF,EtD,FtC,DtF.Findtheminimalcoverfortheabovesetoffunctionaldependenciesusingthealgorithmdescribedinclass.Givesufficientdeta
14、iltoshowyourreasoning,butbesuccinct.Youdonothavetolistallthecasesyoutest/considerforthealgorithm.Showallstepswhereyoumakechangestotheabovesetindetail.Usingthefunctionaldependenciesthatyoucomputedinstepa,findthekeysforthisrelation.IsitinBCNF?Explainyourreasoning.Supposewedecomposetheaboverelationinto
15、thefollowingtworelations:R1(A,B,C,D,E)R2(A,D,F,G)Usethefunctionaldependenciesintheminimalcover.Foreachrelation,writedownthefunctionaldependenciesthatfallwithinthatrelation(youcandecomposeadependencyoftheformADtBFintotwoi.e.ADtBandADtFwhencomputingthis).Usingthesefunctionaldependencies,determineifthi
16、sdecompositionislosslessand/ordependencypreserving.Explainyourreasoning.Answers.a.Step1.ADB,ADF,CDE,CDG,CDC,BDF,ED,FC,DFStep2.removeCDC,ADF,andBDF.ADB,CDE,CDG,FC,DF,EDStep3.removeDfromCDEandCDGADB,DE,DG,FC,DF,EDFinallyrecombineADB,DEGF,FC,ED.Keys:A,D,A,E.NotinBCNFsincethelastthreefunctionaldependenc
17、iesdonothaveasuperkeyonthelefthandside.R1(A,B,C,D,E)Dependencies:ADB,DE,EDR2(A,D,F,G)Dependencies:DGF.Notfunctionaldependencypreserving,thedependencyFCisnotpreserved.head(R1)Qhead(R2)=A,DR1:ADABCDEisnottruesinceCisnotimpliedbyA,DR2:ADADFGistruesincethisisimpliedbyDGFasfollows:ADADinclusionrule,since
18、DGF,usesetaccumulationrule,ADADGF.Hence,thisisalosslessdecomposition.Question8YouaregiventhefollowingsetFoffunctionaldependenciesforarelationR(A,B,C,D,E,F):F=ABCD,ABDE,CDF,CDFB,BFD.FindallkeysofRbasedonthesefunctionaldependencies.IsthisrelationinBoyce-CoddNormalForm?Isit3NF?Explainyouranswers.Canthe
19、setFbesimplified(byremovingfunctionaldependenciesorbyremovingattributesfromthelefthandsideoffunctionaldependencies)withoutchangingtheclosureofF(i.e.F+)?Hint.Considerthestepsoftheminimalcoveralgorithm.Doanyofthemapplytothisfunctionaldependency?Answer.Keys:A,B,CandA,C,DItisnotinBCNF.CounterexampleABDE
20、andABDisnotasuperkey.Itisnotin3NF.CounterexampleABDE,andABDisnotasuperkeyandEisnotprimeattribute(partofakey).LetFbeobtainedbyreplacingCDFBwithCDB.AccordingtoFandF,CD+=C,D,B,F.Hence,wecanremoveFfromthisfunctionaldependencywithoutchangingthemeaningofthesystem.Question9ConsiderrelationR(X,Y,Z).Relation
21、Rcurrentlyhasthreetuples:(6,4,2),(6,6,8)and(6,4,8).WhichofthefollowingthreefunctionaldependenciescanyouinferdonotholdforrelationR?Explainyouranswer.YXAnswer.Thefirstfunctionaldependencyholds,buttherestdonothold.Thesecondandthirdtuplesbothhave8forZbutdifferentvaluesofY.Thefirstandthirdtuplesbothhave6
22、and4forXandYbutdifferentvaluesforZ.Question10ConsidertherelationR(V,W,X,Y,Z)withfunctionaldependenciesZTY,YTZ,XTY,XTV,VWTX.ListthepossiblekeysforrelationRbasedonthefunctionaldependenciesabove.ShowtheclosureforattributeXgiventhefunctionaldependenciesabove.SupposethatrelationRisdecomposedintotworelati
23、ons,R1(V,W,X)andR2(X,Y,Z).Isthisdecompositionalosslessdecomposition?Explainyouranswer.Answer.V,W,X,WX+=X,V,Y,ZYesitislossless.Tobelosslesstheattributesincommonbetweenthetworelationsmustdeterminealltheattributesinoneofthetworelations.TheonlyattributeincommonisXanditfunctionallydeterminesalltheattribu
24、tesinR2.Question11GivenrelationR(W,X,Y,Z)andsetoffunctionaldependenciesF=XTW,WZTXY,YTWXZ.ComputetheminimalcoverforF.Answer.Step1:XTW,WZTX,WZTY,YTW,YTX,YTZStep2:DontneedWZTX,sinceWZTYandYTXDontneedYTW,sinceYTXandXTWThisleavesXTWWZTY,YTX,YTZStep3:OnlyneedtoconsiderWZTY.CanteliminateWorZ.Sonothingiseli
25、minated.Step4:XTWWZTY,YTXZistheminimalcoverQuestion12GivenrelationR(W,X,Y,Z)andsetoffunctionaldependenciesG=ZTW,YTXZ,XWTY,whereGisaminimalcover:DecomposeRintoasetofrelationsinThirdNormalForm.Isyourdecompositioninparta)alsoinBoyceCoddNormalForm?Explainyouranswer.Answer.Possiblekeys:Y,X,Z,W,XR1=(Z,W),
26、R2=(X,Y,Z),R3=(X,Y,W)Yes.Ineachofthethreerelations,theleftsideofthefuncationaldependenciesthatapplyaresuperkeysfortherelation.Hence,allthreerelationssatisfythedefinitionofBCNF.Question13ConsiderarelationnamedEMP_DEPTwithattributes:ENAME,SSN,BDATE,ADDRESS,DNUMBER,DNAME,andDMGRSSN.ConsideralsothesetGo
27、ffunctionaldependenciesforEMPDEPT:G=SSNENAMEBDATEADDRESSDNUMBER,DNUMBERDNAME,DMGRSSM.CalculatetheclosuresSSN+andDNAME+withrespecttoG.IsthesetoffunctionaldependencesGminimal?Ifnot,findaminimalsetoffunctionaldependenciesthatisequivalenttoG.ListanupdateanomalythatcanoccurforrelationEMPDEPT.ListaninsertionanomalythatcanoccurforrelationEMPDEPT.ListadeletionanomalythatcanoccurforrelationEMPDEPT.Answer.SSN+=SSN,ENAME,BDATE,ADDRESS,DNUMBER,DNAME,DMGRSSNDNAME+=DNAMEItisminimal.Sinceeverymemberofadepartmenthasareferencetothemanagerofthatdepartment(i.e.,Dmgrssn),whenthedepartmentmanager
溫馨提示
- 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫(kù)網(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ì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 寶雞文理學(xué)院《外國(guó)文學(xué)》2021-2022學(xué)年第一學(xué)期期末試卷
- 室內(nèi)芳香噴霧項(xiàng)目評(píng)價(jià)分析報(bào)告
- 擱腳凳市場(chǎng)環(huán)境與對(duì)策分析
- 斬骨刀項(xiàng)目評(píng)價(jià)分析報(bào)告
- 工業(yè)馬達(dá)用正時(shí)皮帶相關(guān)項(xiàng)目實(shí)施方案
- 山西省忻州市2024-2025學(xué)年高三上學(xué)期10月月考試題 語(yǔ)文 含答案
- 座椅家具項(xiàng)目評(píng)價(jià)分析報(bào)告
- 寶雞文理學(xué)院《工筆人物畫(huà)》2022-2023學(xué)年第一學(xué)期期末試卷
- 手動(dòng)松土器市場(chǎng)環(huán)境與對(duì)策分析
- 帶輪子的購(gòu)物袋項(xiàng)目評(píng)價(jià)分析報(bào)告
- 中國(guó)紡織技術(shù)的起源與發(fā)展(教學(xué)設(shè)計(jì))
- 2024-2030年扭力工具行業(yè)市場(chǎng)現(xiàn)狀供需分析及重點(diǎn)企業(yè)投資評(píng)估規(guī)劃分析研究報(bào)告
- Unit3SportsandFitnessReadingforwriting教學(xué)設(shè)計(jì)2023-2024學(xué)年人教版高中英語(yǔ)必修第一冊(cè)
- (高清版)JTG∕T 3372-2024 公路黃土隧道設(shè)計(jì)與施工技術(shù)規(guī)范
- DL∕T 741-2019 架空輸電線路運(yùn)行規(guī)程
- 2024-2030年中國(guó)計(jì)算機(jī)仿真行業(yè)發(fā)展動(dòng)向與競(jìng)爭(zhēng)對(duì)手經(jīng)營(yíng)分析研究報(bào)告
- 第五單元 跟作家學(xué)寫(xiě)作 把事情寫(xiě)清楚 單元任務(wù)群整體 教學(xué)設(shè)計(jì) -2024-2025學(xué)年語(yǔ)文四年級(jí)上冊(cè)統(tǒng)編版
- 2023年會(huì)展行業(yè)薪酬報(bào)告
- 2024社工初級(jí)考試題庫(kù)及參考答案1套
- 健康服務(wù)與管理導(dǎo)論全套教學(xué)課件
- 小學(xué)數(shù)學(xué)人教版三年級(jí)下冊(cè) 長(zhǎng)方形、正方形面積的計(jì)算教學(xué)設(shè)計(jì)
評(píng)論
0/150
提交評(píng)論