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

In Case Exercise 3 from Chapter 4, you developed the relational schema for Dr. Z’s Multiple Sclerosis (MS) Clinic Management System.

a. Do you see any opportunities for user-defined data types? Which fields? Why?

b. Are any fields candidates for coding? If so, what coding scheme would you use for each of these fields?

c. Are there any fields that may take on a null value? If so, which ones?

d. Do you see any opportunities for denormalization of the relations you designed in Chapter 4? If not, why not? If yes, where and how might you denormalize?

e. Do you see an opportunity for using a bitmap index for this database? Why or why not?

f. Can you think of a situation with this set of tables where you might want to use a join index?

• 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.