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

Violation: D -> IJ

DIJ (D+)   key = D

ABCFGH  key = AB

Violation: B -> F

BFGH (B+)

Violation: F -> GH

FGH (F+)   key = F

BF    key = B

ABC

(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: H -> J

HJ (H+)   key = H

ABD    key = ABD (trivial relation)

(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