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 2 in Chapter 4, you wrote CREATE TABLE commands for each relation of Dr. Z’s small database, which was to be created in Microsoft Access. Since then, Dr. Z has decided to use Microsoft SQL Server, consistent with other databases at MVCH. Reconsider your previous CREATE TABLE commands in answering the following questions:

a. Would you choose different data types for any fields? Why?

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

c. Which fields require data values? Are there any fields that may take on null values?

d. Suppose the reason for a visit and the patient’s social worker are not entered. What procedures would you use for handling these missing data? Can you and should you use a default value for this field? Why or why not?

e. Using Microsoft Visio (or other tool required by your instructor), draw the physical data model that shows the data types, primary keys, and foreign keys.

reference exercise

The Multiple Sclerosis (MS) Center, headed by Dr. “Z,” has been using a spreadsheet to keep track of information that patients provide upon signing in for a clinic visit. One of the staff members thought it would be better to use a relational database for recording this information and imported the spreadsheet as a table into a Microsoft Access database (MVCH Figure 4-5).

a. What would you suggest as the primary key for this table?

b. Is this table a relation? Why or why not?

c. Can you identify any problems with this table structure? Are there any insertion, deletion, or update anomalies?

d. Diagram the functional dependencies for this table.

e. Using the normalization steps described in this chapter, develop a set of 3NF relations.

f. Using a tool such as Microsoft Visio, draw the relational schema, clearly indicating referential integrity constraints.

g. Write CREATE TABLE commands for all relations in your schema. Make reasonable assumptions concerning the data type for each attribute in each of the relations.