UNIVERSITY OF WASHINGTON

CSE 594: DATABASE MANAGEMENT SYSTEMS

AUTUMN 1999

Homework 5: SQL Server DB, Concurrency Control, Recovery

See the web page for homework guidelines, due dates, and policies. Note that this homework is worth 150 points.

  1. SQL SERVER DATABASE CREATION (50 points): Create relations to model the data described in your E/R diagram from Homework 1. Use SQL Server. There are two items you will need now that you did not necessarily have in the E/R diagram: Domain information and functional dependencies. As much information as possible should be transferred from the E/R diagram to your SQL Server database. Where something cannot be transferred, you'll need to make note of that that and explain why.

    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:

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

  2. DATABASE POPULATION (25 points): Populate each relation in your database with a reasonable amount of data. "Reasonable" implies that join queries between related tables will return non-empty results, at the very least. Print the contents of each table and turn them in. If you don't want to print the whole table, that's fine, you can print just a selection (not a projection!) from the table.

  3. DATABASE QUERY (20 points): Write an SQL query involving at least 3 of your relations. Turn in the following:
    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).

  4. CONCURRENCY CONTROL AND RECOVERY (55 points):
    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.