Question

Q3:

Given a relational schema

? = {?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?} and a set of functional dependencies

? = {? → ? ? ? ?, ? → ? ? ?, ? → ?, ? ? → ?} and a key(R) = AI

1. Is R in BCNF? If yes, justify your answer.

2. If no, explain why and decompose R for two levels only.

3. Check whether the decomposition in step 2 dependency preserved or not.

Answer 1

Given

• ? = {?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?}
• Set of functional dependencies:
• ? = { ? → ? ? ? ?, ? → ? ? ?, ? → ?, ? ? → ? } and a
• key(R) = A I

Key Attribute : A , I

Non Key Attribute: B ,C ,D, E ,F ,G ,H ,J, K

Answer(1) NO, Relation R is not in  BCNF.

Reason :

Partial Dependency is exist in the FD's.  

Given below 3 FD's violate 2NF condition. so They are NOT in 2NF , 3NF and BCNF

? → ? ? ? ?,

? → ? ? ?,

? → ?,

Answer(2). Because of partial depency in FD's , Relaion R is not in BCNF.

Decomposition:   ? = {?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?}

R1(A I K J) R2 ( AE BCDFGH)

? → J   ? ? → ?   ? → ? ? ? ?, ? → ? ? ?,

• Again Partial dependency in Both table so again decompose:

R1(A I K ) R11(I J) R2 ( A BCDE)    R22 ( E FGH)

? ? → ?    ? → J ? → ? ? ? ?, ? → ? ? ?

• Now the Relation R into BCNF Relation:
• We need 4 tables then only it satisfy BCNF Decompostion:Initial in 2 table is was not satisfying BCNF Decomposition.

Answer(3). Yes the decomposition in step 2 is dependency preserving.

R1(A I K )	R11(I J)	R2 ( A BCDE)	R22 ( E FGH)
? ? → ?	? → J	? → ? ? ? ?,	? → ? ? ?

all dependency are preserved