9

.pdf

School

University of Washington *

*We aren’t endorsed by this school

Course

4

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

8

Uploaded by HighnessMonkey10676

Report
BCNF Decomposition Example: Decompose into BCNF - Restaurant(id, name, rating, popularity, rec) 1. id » name, rating 2. rating popularity 3. popularity rec This example is covered in both the section slides and the lecture slides. Given R(A, B, C, D, E), and functional dependencies: A— C,BD - A,D » E 1. Find the following closures: {A}+, {B}+, {D}+, and {BD}+ A+ ={A,C} B* = {B} D* = {D, E} BD*={A, B,C, D, E} 2. Decompose R into BCNF. In each step, explain which functional dependency you used to decompose and explain why further decomposition is needed. Your answer should consist of a list of table names and attributes. Make sure you indicate the keys for each relation. There are multiple ways to break down {ABCDE} depending on what FD you do first. R1{ABCDE} D->E R2{D, E} R3{A, B, C, D} A->C R2{D, E} R4{A, C} R5{A, B, D}
R1{ABCDE} A->C R2{A, C} R3{A, B, D, E} D->E R2{A, C} R4{D, E} R5{A, B, D} R1{A, B,C, D, E} BD -> ABCDE This gives us back the original table. But because of our other FD’s, this is not in BCNF. Use the other FDs to break this down further.
Relational Algebra RA Operators: O = Select (WHERE/HAVING) TU = Project (SELECT) P><] = Natural Join Y = Group/Aggregation O = Duplicate Elimination (DISTINCT) Example: Make this SQL query into RA (remember FJWGHOS) SELECT R.b, T.c, max(T.a) AS T_max FROM Table R AS R, Table T AS T WHERE R.b = T.b GROUP BY R.b, T.c HAVING max(T.a) > 99 nR.b, T.c, T_max 0T_max > 99 YR.b, T.c, MAX(T.a) -> T_max PAhRrb=1.b N\ Table RR Table TT
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help