Search code examples
mysqldatabasefunctional-dependenciesnatural-join

Databases: Lossless decomposition and natural join


The question I'm answering is:

Consider R(A,B,C) with the FD A --> B. Give a vaild instance of r of R such that when we project r on relations R1(A,B) and R2(B,C) and then join R1 and R2 again, we do not get the same instance r.

I cannot think of a situation in which this will occur. Since B is dependent on A and both tables will join on the B value, wouldn't the natural join always be the same?

Ex.

r1 = (1, 4, 6)
r2 = (2, 6, 4)

R1:  A | B     R2:  B | C
    ---|---        ---|---
     1 | 4          4 | 6
     2 | 6          6 | 4

R:  A | B | C
   ---|---|---
    1 | 4 | 6
    2 | 6 | 4

Solution

  • As pointed out by @ErwinSmout the first example is incorrect. If I understand correctly C doesn't depend on neither A nor B so given r like:

    r1 = (1,4,6)
    r2 = (1,4,7)
    
     A | B | C
    ---|---|---
     1 | 4 | 6
     1 | 4 | 7
     1 | 4 | 6
     1 | 4 | 7
    

    We will get doubled rows after joining it back together. Also

    multiple A can have the same B so then you would end up with incorrect values.

    r1 = (1,4,6)
    r2 = (2,4,7)
    
     A | B | C
    ---|---|---
     1 | 4 | 6
     1 | 4 | 7
     2 | 4 | 6
     2 | 4 | 7