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

Review the exercises below and select several to attempt. You will probably need to add to your prototype and populate your tables with sample data in order to test your queries:

a. For a given physician, which treatments has that physician performed on each patient referred by that physician to the hospital?

b. For the query in part a, also include physicians who have not referred patients to the hospital.

c. For each patient, what is the average number of treatments performed on him or her by each physician who has treated that patient?

d. List all patients who have received no treatments.

e. For each nurse in charge, what is the total number of hours worked by all employees who work in the care center which that nurse supervises?

f. Which technicians have more than one skill listed? Which technicians have no skills listed?

g. Determine whether any outpatients were accidentally assigned to resident beds.

h. Determine which item is consumed most.

i. Determine which physicians prescribe the most expensive item.

j. Return a result table that could be used to produce a hospital report, such as nursing staff assigned to each care center.

k. Use the UNION statement to provide a combined listing of care center names and their locations as well as laboratories and their location. The list should be sorted by location, in ascending order. (You should use aliases to rename the fields in this query.)