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. 
 

Patient # Name First Seen Social Worker Visit Date Visit Time Reason for Visit New symptoms Severe leg pain for past 2 days Level of Pain 9844 John Miller 10/1/2008 Matt Baker 10/11/2009 2:30 pm Severe leg pain 4 10/18/2009 11:30 am Follow-up, also None need flu shot 2 1/3/2010 10 am Routine None 3/15/2010 10:30 am Routine None 4211 1/3/2009 Lynn Riley Sheryl Franz 1/3/2010 2 pm Referred by Primary care physician Physical 2/11/2010 9 am None 3/22/2010 4:00 pm Routine and Greater difficulties 1 B12 Shot with writing & buttoning shirts 8766 Juan Ortega 2/2/2009 Matt Baker 2/2/2010 9:30 am Blurred vision in right eye Follow-up 2/14/2010 9:30 am ???? New symptoms Pins/needles in both legs; trouble with balance 3/18/2010 1 MVCH FIGURE 4-5 MS Center patient sign-in data