1.Is the corona table from BarBeerDrinker database in BCNF?
Yes/No? Eitherwayyouhavetoproveyouranswer(BCNFornot)usingnon- trivial functional dependencies. If you find one dependency f which violates BCNF,youdonothavetofindotherfunctionaldependencies.Butyouhaveto provethatfviolatesBCNF. Sincetherearesomanyattributes,youcangroup attributes, say into country attributes and corona stats and use notation X->Y where Y is a set of attributes.
2. Is original TripAdvisor table that we used in class in BCNF?
Yes/No?Eitherwayyouhavetoproveyouranswer(BCNFornot)usingnon- trivialfunctionaldependencies. Inthiscase,sincetherearefewerattributesthan forcoronadataset,youshouldfindasmanyfunctionaldependenciesasyoucan (as long as they are not implied by these you found already).This is requested even if you find a functional dependency which violates BCNF.
3)WriteSQLquerywhichwillcheckiffunctionaldependency f=User-> User_reviews is satisfied by the table TripAdvisor. The query should return “Yes” if f is satisfied, and “No” if f is violated in our Tripadvisor table instance.
Incasethefunctionaldependency fisviolated, writeanSQLquerywhichwill returnatleast twotuplesinTripAdvisortable whichviolatef–inotherwords, counterexample (it takes two tuples to demonstrate that fd is violated)
4*. WriteSQLquerywhichwillverifyifdecompositionoftableSellsfrom BarBeerDrinkerdatabaseintotwotablesSells1[Bar,Beer]andSells2[Beer,Price] is lossless.
Sells1andSells2aresimpleprojectionsofSelltableontotheirrespectiveattribute sets.Thequeryshouldreturn“Lossless”incasethedecompositionisinfact lossless and “Lossy” if it is not. This check is performed against specific instance ofSellstablewhichwehaveinourpracticedb.
*) Extra credit
THEORY
5) LetR=ABCDEGHKandF={ABK→C,A→DG,B→K,K→ADH,H→GE}. IsitinBCNF? Proveyouranswer.
6) LetR(ABCDEFG)bearelationandF={A->C,A->D, B->F,E->F,F->G}
- Show example of lossless join decomposition of R into threetablesR1, R2 and R3 and demonstrate that this decomposition is lossless using chase algorit hm
- Findonekeyforthisschemeandprovethatitisakeyusingclosure algorithm and definition of a key.
7) ConsiderR=ABCDEG,withthesetof dependenciesF={AB→D,AB→C, AC → E, B → D, BE → A, E → G}. Suppose we have decomposedit into relations with set of attributes R1={ABD}, R2={ACE}, R3={ADEG}. Show using chase algorithmif this decomposition has lossless join property.
8) Let R(ABCD) be a relation and F={A->B, C->D, BC->A}. Apply chase algorithm to test if decomposition of R onto R1(AB), R2(AC), R3(BCD) is lossless.
9)LetR(ABCDE) bearelationand F={A→B,BC→E,andED→A}.Decompose R into BCNF using BCNF decomposition algorithm. Remember that you need to compute projections of F to check if the decomposed tables are in BCNF.
10)Let R(ABCDEFGH)bearelationandF={AB→E,C→D,D→E,FG→A}. Decompose R in BCNF using BCNF decomposition algorithm. Remember that you need to compute projections of F to check if the decomposed tables are in BCNF. UsingChasealgorithmdemonstrateifthedecompositionyouobtainedis in fact lossless.
