Please DO NOT COPY anwser from other, write you own throught, thank you. Conside
ID: 3888432 • Letter: P
Question
Please DO NOT COPY anwser from other, write you own throught, thank you.
Consider the relation schema ORDER={Customer, Date, OrderID, Employee, Manager}and the following set of FDs:
• Customer, DateOrderID
• EmployeeManager
• OrderID, DateCustomer, Employee
• DateManager
1) Identify a lossless BCNF decomposition for the above relation schema ORDER. You need to include the main steps used for identifying the BCNF decomposition. Check if this BCNF decomposition is dependency-preserving and justify your answer.
2) Identify alossless and dependency-preserving 3NF decomposition for the above relation schema ORDER. You need to include the main steps used for identifying the 3NF decomposition.
Explanation / Answer
a,b,c,d,e
a,b->c
d->e
c,b->a,e
b->e
make all RHS single attributed
a,b->c
d->e
c,b->a
c,b->e this dependency is unnecessary
b->e
necessary attributes for key - b,d
bd+=abcde
so ,bd is key
take a violating FD d->e, or b->e where determinant d or b are not a superkey
prepare to decompose:
d+=de
b+=be
So , R1=(abcd) ,key bd
R2=(be) key b ,
R3=(de) key d
check R1 for BCNF condition:
a,b->c
d->e
c,b->a
b->e
a,b->c is a violation FD, as (a,b) is not superkey
c,b->c is a violation FD, as (c,b) is not superkey
ab+=abce
cb+=abce
R11=(abc) key ab and cb
d is already in R2, and has no relation with other column
R12=(bd) key bd
So , finally we got,
R11=(abc) key ab and cb
R12=(bd) key bd
R2=(be) key b
R3=(de) key d
This decomposition is not dependency preserving, as the dependency c,b->a,e is not preserved
2) lets make RHS single attributes
a,b->c
d->e
c,b->a
c,b->e this dependency is unnecessary
b->e
a,b->c
d->e
c,b->a
b->e
key here is : bd, as b,d not in RHS
Removing transitive dependency ,we get
R11=(abc) key ab and cb
R12=(bd) key bd
R2=(be) key b
R3=(de) key d