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