An initial dependency diagram depicting only the primary key dependencies is shown in Figure P6.1a below.

Figure P6.1a Initial dependency diagram for Problem 1.

There are no composite keys being used, therefore, by definition, there is not an issue with partial dependencies and the entities are already in 2NF. Based on the descriptions of the attributes, it appears that the patient name, phone number, and address can be determined by the patient id number. Therefore, the following transitive dependency can be determined.

App_PatientID à (App_Name, App_Phone, App_Street, App_City, App_State, App_Zip)

As discussed in the chapter, ZIP_Codes can be used to determine a city and state; therefore, we also have the transitive dependency:

App_Zip à App_City, App_State

Figure P6.1b depicts the dependency diagram with these transitive dependencies included.

Figure P6.1b Revised dependency diagram for Problem 1.

Since the first transitive dependency completely encloses the second transitive dependency, it is appropriate to resolve the first transitive dependency before resolving the second. Figure P6.1c shows the results of resolving the first transitive dependency.

Figure P6.1c Resolving the first transitive dependency

Finally, the second and final transitive dependency can now be resolved as shown in the final dependency diagram in Figure P6.1d.

Figure P6.1d Final dependency diagram for Problem 1

Note that at this time we have resolved all of the transitive dependencies. Decisions on whether or not to denormalize, and perhaps not remove the final transitive dependency, have yet to be made. Also, the structures have not yet had the benefit of additional design modifications such as achieving proper naming conventions for the attributes in the new tables. However, creating the fully normalized structures is an important set toward making informed decisions about the compromises in the design that we may choose to make.