MCS-243 HW 2: Database Design (January 2000)

Due: January 20, 2000, 9:00 am

Do the following exercises from the textbook:
  1. Exercise 5.4, pp. 377-378.
  2. Exercise 5.6, p. 378. Note that as in Example 5.6.4, you need not literally list all functional dependencies: You can leave out trivial ones and ones implied by those you list.
  3. [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.
  4. [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?
  5. On p. 366, O'Neil claims that the BCNF decomposition in Figure 5.26 doesn't preserve FD (5). Show that he is 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.

Instructor: Max Hailperin