MCS-274 Homework 4 (Spring 2007)

Due March 20, 2007

• Exercise 6.4, p. 400.
• Exercise 6.6, p. 401. Note that as in Example 6.6.4, you need not literally list all functional dependencies: You can leave out trivial ones and ones implied by those you list.
• Exercise 6.x1: [Ullman 1982, Ex. 7.1(a,b), pp. 262-263] A brokerage firm has a database with attributes B (broker), O (office of broker), I (investor), S (stock), Q (quantity of stock owned by an investor), and D (dividend paid by a stock) with functional dependencies S->D, I->B, IS->Q, and B->O. Suppose all the attributes are in a single table. Find a key for the table. How many keys does the table have? Justify your answer.
• Exercise 6.x2: [Ullman 1982, Ex. 7.2, p. 263] Suppose the database from the previous exercise is decomposed into two tables, with headings ISQD and IBO. What redundancies and anomalies do you foresee?
• Exercise 6.x3: On p. 391, the O'Neils claim that the BCNF decomposition in Figure 6.29 doesn't preserve FD (5). Show that they are right by giving a sample collection of rows for the three tables that violates FD (5) without violating any FD that is confined to one of the tables. Two rows per table is sufficient.