Exercise 3.3.1: For each of the following relation schemas and sets of FD’s: c)

Question

Exercise 3.3.1: For each of the following relation schemas and sets of FD’s:

c) R(A, B, C, D) with FD’s AB --> C, BC --> D, CD --> A, and AD --> B.

               e) R(A, B, C, D, E) with FD’s AB --> C, DE --> C, and B --> D.

Do the following:

i)                   Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them. However, it is not necessary to give violations that have more than one attribute on the right side.

ii)                Decompose the relations, as necessary, into collections of relations that are in BCNF.

Explanation / Answer

For part c

R(A,B,C,D) with FD’s AB --> C, BC --> D, CD --> A, and AD --> B

We can see AB+= {A,B,C,D} where X+ = set of attribute that can be generated from X in R

BC+= {A,B,C,D}

CD+= {A,B,C,D}

AD+= {A,B,C,D}

So every FD in R(A,B,C,D) of the form X--> A, X is the superkey from which entire attribute can be generated. So R(A,B,C,D) is already in BCNF form.

For part e

None of the FD among AB --> C, DE --> C, and B --> D has superkey as left side.

We can consider ABE as superkey since ABE+ = {A,B,C,D,E}

Then all FD AB --> C,DE --> C, and B --> D are violating BCNF property since none of AB,DE and B is key.

To decompose in BCNF, lets take AB-->C first

Since AB+ = {A,B,C,D}

As per decomposition rule of BCNF, we will partition R(A,B,C,D,E) into R1(A,B,C,D) and R2(A,B,E)

After this decomposition R2(A,B,E) is in BCNF but in  R1(A,B,C,D), FD B --> D is violating BCNF since B is not key(AB is key).

We decompe R1(A,B,C,D) into R11(B,D) and R12(A,B,C) which will now be in BCNF.

Thus R(A,B,C,D,E) decomposed into  R11(B,D) , R12(A,B,C) and R2(A,B,E) will be in BCNF

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.