In Case Exercise 1 in Chapter 5, you created the physical data model for Dr. Z’s database that keeps track of patients checking in. You may recall that Dr. Z decided to use SQL Server. Instructions for installing SQL Server and SQL Server Management Studio Express are available in the Pine Valley sample database area of this book’s Web site.

a. Using the design you created in Chapter 5, create the database and tables using SQL. Be sure to create the SQL assertions necessary to ensure referential integrity and other constraints.

b. Populate the database with sample data. (MVCH Figure 4-5 in Chapter 4 provides some sample data, but you need a few more patients and visits for the queries in part c.)

c. Write and test some queries that will work using your sample data. Write queries that i. Select information from only one of the tables (e.g., an alphabetical listing of all patients, an alphabetical listing of all the patients assigned to one of the social workers, etc.).
ii. Aggregate information from one attribute in a table (e.g., How often has patient 8766 visited the MS Center at MVCH in a given month? How many patients are assigned to each social worker?).
iii. Try out the various functions, such as MIN, MAX, and AVG (e.g., What is the average level of pain reported by Dr. Z’s patients? What is the worst level of pain his patients have experienced?).