Case Description You have been introduced to the Mountain View Community Hospital (MVCH)...

Case Description

You have been introduced to the Mountain View Community Hospital (MVCH) case in the preceding chapters. This chapter continues the case, with special emphasis on logical design for the relational data model. Although the hospital will continue to evaluate newer technology (e.g., object-oriented databases, XML, and XML databases), it is expected that relational technology will continue to dominate its systems development over the next few years.

The assistant administrator at MVCH has asked you to review the data used in the patient billing and accounting systems. Occasional errors have been discovered in patient statements and the patient records maintained by the hospital. As part of this effort, you have selected four user views for analysis. Simplified versions of these views are shown in MVCH Figures 4-1 through 4-4 and described briefly here:

• Patient bill (MVCH Figure 4-1) This statement is presented to the patient (or patient representative) when the patient is discharged. Assume that each item on the bill

Reference figure 4-1

has a unique description and that the charge for a particular item may vary from one patient to another.

• Room utilization report (MVCH Figure 4-2) This is a daily report that is distributed to qualified personnel. The information can also be retrieved online by a qualified staff member. It shows the status of each room and bed location in the hospital and is used primarily for scheduling and tracking the utilization of facilities. The Location column in this report records the room number and bed location in the room. The Accom column indicates the type of accommodation (PR = private, SP = semiprivate).

• Patient display (MVCH Figure 4-3) This display is presented on demand to any qualified doctor, nurse, or other staff member. Assume that for each location there is a unique telephone number.

• Daily physician report (MVCH Figure 4-4) This report is prepared daily for each physician on the staff of MVCH. It shows the patients who have been treated on that day by the physician and the name of the treatment (or procedure). To simplify the analysis, assume that each patient may receive only one treatment from a given physician on a given day. (We ask you to comment on this assumption later.)

a. Using the normalization steps described in this chapter, develop a set of 3NF relations for each of the four user views.

b. For each user view, draw a relational schema for the 3NF relations you developed in Case Exercise 1a. Be sure to show the functional dependencies and referential integrity constraints for each schema.

c. Merge the relations for the four user views into a single set of 3NF relations, using the guidelines presented in this chapter. Draw a single relational schema for the four user views and show the referential integrity constraints.

d. Suggest any refinements to the design in Case Exercise 1c that would promote data quality and integrity.

e. How would you change your approach to accommodate the rule that a patient may receive multiple treatments from a given physician on a given day?