Project #2: Due Mar. 26 Noon

PDA (Personal Database Application), Courtesy of J. Widom

Part 1: Normalization

1. If TA pointed out some errors from Project #1, fix those, and tell us How and Where you fixed.

2. For each relation in your schema, specify a set of non-trivial functional dependencies for the relation. Any functional dependencies that actually hold in the real-world scenario that you're modeling should be specified. If you miss any obvious ones (eg, Key), then you will lose points.

3. For ONE relation of your choice, from the initial functional dependencies that you have gathered, derive all valid functional dependencies. Show the detailed steps of your reasoning as much as you can.

4. Is each relation in your schema in Boyce-Codd Normal Form (BCNF) with respect to the functional dependencies you specified? For each relation, write down if it is or not, and tell us why.

5. If any relation is not in BCNF, decompose the relation into smaller relations so that resulting relations are in BCNF. Show us again why this new decomposed relations are in BCNF. If TA finds any relation not in BCNF but you missed, then you will lose points.

Part 2: SQL

1. Write SEVEN data retrieval SQL queries, and their corresponding English description. Queries must exhibit "interesting" SQL features that you learned in class (e.g., aggregates, sub-query, grouping and HAVING, various joins, set, ANY/ALL/IN/EXIST, etc). If all your seven queries only use simple features like simple projection or WHERE clause like a='b', then you will not get full points.

   At the end, show us: (1) query itself, and (2) answers returned from DB2. If NULL was returned, tell us so or populate mode data.

2. Write THREE data modification SQL queries (one for each INSERT, DELETE, and UPDATE), and their corresponding English description. Queries must use subquery concept (e.g., "INSERT INTO Foo (subquery)", not "INSERT INTO Foo VALUES (....)")

   At the end, show us: (1) query itself, and (2) answers BEFORE and AFTER modification queries are executed.

3. NOTE: Don't just make up a bogus query -- think hard if you can re-use this query when you build a web interface for project #3 later. For instance, if you work in eBay project, customers need a feature to register himself or search some products; Those must be implemented in your SQL queries.

Part 3: Turn-In

1. Turn in hard-copy of your final report to 313A, IST Building by Due.
2. Like Proj #1, post your report to project team web space for public access.