CS 457/554 - Database Systems
Functional Dependency & Decomposition Homework Solution

  1. Key of relation R: AB
    Because AB+ = ABCDEFGHIJ
    But A+ = ADEIJ and B+ = BFGH

    Decomposition:

    ABCDEFGHIJ
    
    Violation: A -> DE
    
       ADEIJ (A+)  key = A
    
            Violation: D -> IJ
    
    	DIJ (D+)   key = D
    
    	ADE   key = A
    
       ABCFGH  key = AB
    
            Violation: B -> F
    
    	BFGH (B+)
    
                Violation: F -> GH
    
    	    FGH (F+)   key = F
    
    	    BF    key = B
    
    	ABC
    
    
    Final answer:
    
       (D I J)
       (A D E)
       (F G H)
       (B F)
       (A B C)
    
    
  2. Key of relation R: ABD

    Decomposition:

    ABCDEFGHIJ
    
    Violation: AB -> C
    
       ABCI (AB+)  key = AB
    
            Violation: A -> I
    
    	AI (A+)   key = A
    
    	ABC   key = AB
    
       ABDEFGHJ   key = ABD
    
            Violation: BD -> EF
    
    	BDEF (BD+)  key = BD
    
    	ABDGHJ    key = ABD
    
                Violation: AD -> GH
    
    	    ADGHJ (AD+)   key = AD
    
                    Violation: H -> J
    
    		HJ (H+)   key = H
    
    		ADGH    key = AD
    
    	    ABD    key = ABD (trivial relation)
    
    
    
    Final answer:
    
       (A I)
       (A B C)
       (B D E F)
       (H J)
       (A D G H)
       (A B D)  - it is OK to omit the trivial relation...
    
    
  3. Given:
       Tuple#       A         B          C
       ----------------------------------------
         #1         10        b1         c1
         #2         10        b2         c2
         #3         11        b4         c1
         #4         12        b3         c4
         #5         13        b1         c1
         #6         14        b3         c4
    
       1. A -> B	CANNOT hold because 2 different B values for same value A=10:
    		(10 b1 c1) and (10 b2 c2) 
    
       2. B -> C	may hold 
    
       3. C -> B    CANNOT hold because 2 different B values for same value C=c1:
    		(10 b1 c1) and (11 b4 c1)
    
       4. B -> A    CANNOT hold because 2 different A values for same value B=b1:
    		(10 b1 c1) and (13 b1 c1)
    
       5. C -> A    CANNOT hold because 2 different A values for same value C=c1:
    		(10 b1 c1) and (13 b1 c1)
    
    
       Potential keys: AB and AC
    
       BC is not a potential key because of the tuples: (10 b1 c1) and (13 b1 c1).
    
  4. The closures are:
        AB+ = A B C
        CD+ = C D E B
        DE+ = D E B
    
  5. AD must be part of any key
        AD+ = A D
    
        Try adding B:  ABD+ = A B D C E   OK
        Try adding C:  ACD+ = A C D E B   OK
        Try adding E:  ADE+ = A D E B C   OK
    
     All keys: ABD, ACD and ADE