NOTES: The goal here is NOT to have you become SQL Server experts, but rather to give you practice applying the logical and physical design techniques we've studied. Do not feel obligated to use every bell and whistle you can find in the DBMS and do not feel obligated to go out and buy a book on the DBMS. If there is something you're trying to do that's giving you fits, please ask us and we'll do something about it. You can also check out the online documentation (SQL Server Books Online, which is an option at the same level as the Enterprise Manager).

Some guidelines you must follow:

• Every relation should have a primary key.
• You should know the FDs and normal form for each relation.
• Unique constraints and foreign key constraints must be specified where needed.
• You must use at least one "check" constraint somewhere in the database.
• You must use at least one "trigger" somewhere in your database to enforce a multi-table integrity constraint.
• You must create at least two indexes in the database in addition to the primary key indexes that SQL Server creates for you automatically.
• Your database must contain at least one view.

What to hand in:

1. Your E/R diagram from Homework 1. If you've made any design changes since then, turn in a correspondingly changed diagram. I.e., the E/R diagram and your relational database should "match".
2. For each relation and view, the create table statement (or its equivalent). Be sure to include primary and foreign key and check constraints. Also write down any functional dependencies that hold on the relation and the highest normal form (considering just 1NF, 2NF, 3NF, and BCNF) satisfied by the relation. You do NOT need to write down the "obvious" functional dependencies, which are defined as those in which the primary key determines the fields that are not in the primary key.
3. For each index, the create index statement (or its equivalent). Include information about whether it is clustered or non-clustered. Also for each index, including the primary key indexes, explain why you created the index in the first place and why you chose the characteristics you did (clustering, etc.).
4. For each trigger, the create trigger statement (or its equivalent) and any explanation of the trigger you feel is necessary.
5. For anything that you could not model in SQL Server, describe it and explain why it couldn't be modeled in the DBMS.
6. Note that SQL Server has the ability to generate SQL scripts, so it doesn't matter whether you use SQL or a friendlier interface to create your DB objects. Use SQL Server help and look for "Generate SQL Scripts".

1. (5 pts) A description of the query in English.
2. (5 pts) The query in SQL.
3. (5 pts) The (non-empty) results of the query.
4. (5 pts) The plan chosen by the SQL Server optimizer to execute this query. Describe the plan in terms we have used in class, not in SQL Server jargon. Note that you may not be given much information about the kind of join performed, so just explain it in as much detail as you can. Explain why you think the optimizer chose this plan. You can find the plan as follows: In SQL 6.5, in the Enterprise Manager's SQL Query Tool window, click on the little yellow wrench on the toolbar of that window. Under "Query Flags", turn on "Show Plan" and "Show Stats I/O", then go back to the query window and execute the query. In SQL 7, run the query then click the Display Plan button on the SQL Server Query Analyzer Toolbar (shortcut is ctrl-L).

1. (15 pts) Exercise 19.2, Parts 1, 2, and 5.
2. (10 pts) Exercise 19.4, Parts 2 and 3. To "describe" the operations of the CC protocols, simply specify the sequences of lock requests, who is waiting on whom (and when), and when each transaction finishes.
3. (30 pts) Exercise 20.4, Parts 1-3. Assume that a checkpoint was taken just prior to LSN 00, and that the dirty page table and transaction table were empty at the time of the checkpoint. Also do the following:
   • For the log you came up with in Part (3), answer the questions of Exercise 20.3, Parts 2a - 2c. Please label your answers as Parts 4-6 of this question.