1

If someone walk me though the following questions I'd be very grateful.

Consider a relation R(A, B, C, D, E) with the following functional dependencies:

A, B → C
D, E → C
B → D

i) decompose R so that it is in 2nd normal form.

Second part of the question: ii) suppose R is populated with the following data:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

Show that your 2NF decomposition is lossless with respect to joins. To do this , produce a join expression amongst the decomposed tables, and show that its execution results in the original table above.

Thanks.

0

I figured it out and got it confirmed by the lecture, hopefully this will help someone else:

i)

The key is {A,B,E}.

This means that FD1 and FD3 are both partial — but note that FD2 is not, because {D,E} is not a partial key. The decomposition creates one table for each of the FDs, and one for the PK FD:

R1(A,B,E) R2(A,B,C) R3(B,D)

-

ii) Using the above we can create the following tables:

        `R1                R2              R3`
| A  | B  | E  |   | A  | B  | C  |    | B  | D  |
| a1 | b1 | e1 |   | a1 | b1 | c1 |    | b1 | d1 | 
| a2 | b1 | e2 |   | a2 | b1 | c2 |    | b2 | d3 | 
| a2 | b2 | e2 |   | a2 | b2 | c1 |    

Joining R1 and R2 gives:

        R1R2                  R3
| A  | B  | E  | C  |    | B  | D  | 
| a1 | b1 | e1 | c1 |    | b1 | d1 | 
| a2 | b1 | e2 | c2 |    | b2 | d3 | 
| a2 | b2 | e2 | c1 | 

Joining R1R2 with R3:

| A  | B  | E  | C  | D  | 
| a1 | b1 | e1 | c1 | d1 | 
| a2 | b1 | e2 | c2 | d1 | 
| a2 | b2 | e2 | c1 | d3 |

which is equal to:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

Hence decomposition is lossles with respect to joins.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.