Case Description HIPAA’s privacy and security rules mandate audit controls “that record...

Case Description

HIPAA’s privacy and security rules mandate audit controls “that record and examine activity in information systems that contain or use electronic protected health information” [§164.312(b)]. How can DDL triggers be used in support of this mandate?

Case Exercises

Using the small sample database you created for Dr. Z in Case Exercise 1 in Chapter 6, write queries that illustrate the more complex queries covered in this chapter:

a. Select information from two or more tables (e.g., all the details of all the visits of a patient).

b. Use subquery syntax (e.g., a listing of all the patients who reported pain that exceeded the average pain for all visits).

c. Return a result table that could be used to produce a report, sorted by patient name or date, for a particular week or after a particular date, or a listing of patient visits for patients assigned to a specific social worker.

Reference Case Exercise 1in Chapter 6,

Case Description

This case segment uses the physical designs you constructed for Mountain View Community Hospital (MVCH) in Chapter 5 to complete the case questions and case exercises.

Case Exercises

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 text’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?).