Case Description Up to this point, you have developed the conceptual and logical models...

Case Description

Up to this point, you have developed the conceptual and logical models for Mountain View Community Hospital’s database. After considering several options, the hospital has decided to use Microsoft SQL Server, a relational DBMS, for implementing the database. Before the functional database is actually created, it is necessary to specify its physical design to ensure that the database is effective and efficient. As you have learned, physical database design is specific to the target environment and must conform to the capabilities of the DBMS to be used. It requires a good understanding of the DBMS’s features, such as available data types, indexing, support for referential integrity and other constraints, and many more. (You can alternatively assume that MVCH chose another DBMS with which you are familiar and then answer the following questions accordingly.)

Case Exercises

MVCH Figure 5-1 shows a portion of the data model for MVCH’s database that represents a set of normalized relations based on the enterprise model shown in MVCH Figure 1-1 and additional business rules provided in the Chapter 2 case segment. Recall that TREATMENT refers to any test or procedure ordered by a physician for a patient and that ORDER refers to any order issued by a physician for treatment and/or services such as diagnostic tests (radiology, laboratory).

Using the information provided below regarding data volume and access frequencies, and following the example provided in Figure 5-1, modify the E-R model shown in MVCH Figure 5-1 to create a preliminary composite usage map. a. Data volume analysis:

• Recall from an earlier case segment that the hospital performs more than a million laboratory procedures and more than 110,000 radiology procedures annually. Add these two figures to arrive at the number of records for the ORDER DETAIL table.

• There are approximately 250 PHYSICIANS, 20,000 PATIENTS, and 200,000 physician ORDERS in this database.

• ICD-9 procedure codes for treatments (lab procedures, radiology procedures, etc.) fall into approximately 3,500 major categories. Use this number to approximate the number of TREATMENT records. b. Data access frequencies per hour:

• Across all applications that use the MVCH database, there are approximately 100 direct accesses to PHYSICIAN, 35 to ORDER, 200 to PATIENT, and 150 to TREATMENT.

• Of the 200 accesses to PATIENT, 30 accesses then also require ORDER data, and of these 30, there are 20 subsequent accesses to PHYSICIAN, and 30 accesses to ORDER DETAIL.

• Of the 35 direct accesses to ORDER, 10 accesses then also require PHYSICIAN data, and 20 require access to PATIENT data, ORDER DETAIL data, and TREATMENT data.

• Of the 100 direct accesses to PHYSICIAN, 20 also access ORDER, ORDER DETAIL, and TREATMENT data.

• Of the 150 direct accesses to TREATMENT, 10 also access ORDER DETAIL data and associated ORDER and PHYSICIAN data.