Chapter 7, Problem 30E

a.

Explanation of Solution

To compute B⁺:

• To compute B^+, the closure of an attribute “B” over a set of functional dependencies “F”, refer the algorithm in Figure 7.19 of the textbook.
• The result is initialized to {B}.
• For the first repetition,
  • The condition is not satisfied in first and second functional dependency.
  • The condition B is a subset of {B} is satisfied for the functional dependency $\text{B}\to \text{D}$ Hence “D” is added to the result.
    • Therefore, the result = {B,D}.
  • The condition “D” is a subset of {B,D} is satisfied for the functional dependency $\text{D}\to \text{A}$ Hence “A” is added to the result

b.

Explanation of Solution

To prove:

“AG” is super key.

Proof:

• This can be proved by proving the existence of functional dependency so that $\text{AG}\to \text{ABCDEG}$ It is given that $\text{A}\to \text{BCD}$.
• The union of the similar sets is the set with itself (AA=A).
• To get  $\text{A}\to \text{ABCD}$, use Armstrong’s augmentation axiom with “A”.
• $\text{BC}\to \text{DE}$ is given and to get $\text{ABCD}\to \text{ABCDE}$, use Armstrong’s augmentation axiom with ABCD

c.

Explanation of Solution

Canonical cover:

• “D” is irrelevant in first and second dependencies because of third dependency.
• Hence remove the attribute “D” from the first and second functional dependencies to achieve $\text{A}\to \text{BC}$ and $\text{BC}\to \text{E}$.
• According to the sub-part “a”, B⁺ is computed as {B,D,A,C,E}.
• The functional dependency $\text{B}\to \text{E}$ can be determined from the above set and from this the functional dependency $\text{BC}\to \text{E}$ becomes irrelevant

d.

Explanation of Solution

Third Normal Form (3NF) decomposition:

• In the canonical cover, functional dependency is exists such that the attribute set id is a subset of other dependency.
• Hence, each functional dependency will generate its own relation.
• The relations according to the functional dependencies in the canonical cover are,
  • r1(A,B,C)
  • r2(B,D,E)
  • r3(D,A)
• “G” has to part of all super keys as it is not dependent on any attribute...

e.

Explanation of Solution

Boyce-Codd Normal Form (BCNF) decomposition:

• To compute the BCNF decomposition of the schema relation “r” using the original set “F” of functional dependency, follow the steps below,
  • For the relation not being in BCNF, the first original functional dependency is responsible.
  • So, according to first functional dependency , the original relation is decomposed as follows,
    ...