7.11

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

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

  2. 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.

  3. 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?

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

  2. 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.

  3. For every schema \(r_i(\alpha\beta)\) added to the decomposition because of a functional dependency \(\alpha \rightarrow \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\).