7.11

In the BCNF decomposition algorithm, suppose you use a functional dependency $\alpha \to \beta$ to decompose a relation schema $r(\alpha ,\beta ,\gamma )$ into $r_1(\alpha ,\beta )$ and $r_2(\alpha ,\gamma )$.

a. What primary and foreign-key constraint do you expect to hold on the decomposed relations?

b. Give an example of an inconsistency that can arise due to an erroneous update, if the foreign-key constraint were not enforced on the decomposed relations above.

c. When a relation schema is decomposed into 3NF using the algorithm in Section 7.5.2, what primary and foreign-key dependencies would you expect to hold on the decomposed schema?

---

a. $\alpha$ should be a primary key for $r_1$, and $\alpha$ should be the foreign key from $r_2$, referencing $r_1$.

b. If the foreign key constraint is not enforced, then a deletion of a tuple from $r_1$ would not have a corresponding deletion from the referencing tuples in $r_2$. Instead of deleting a tuple from $r$, this would amount to simply setting the value of $\alpha$ to null in some tuples.

c. For every schema $r_i(\alpha \beta )$ added to the decomposition because of a functional dependency $\alpha \to \beta$, $\alpha$ should be made the primary key. Also, a candidate key $\gamma$ for the original relation is located in some newly created relation $r_k$ and is a primary key for that relation. Foreign-key constrains are created as follows: for each relation $r_i$ created above, if the primary key attributes of $r_i$ also occur in any other relation $r_j$, then a foreign-key constraint is created from those attributes in $r_j$, referencing (the primary key of) $r_i$.