Suppose that you have been given the table structure and data shown in Table 6.9, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal.

Table P6.9 Sample PROFESSOR Records

Given the information in Table 6.9:

 a. Draw the dependency

 The dependency diagram is shown in Figure P6.9a.

Figure P6.9a The Dependency Diagram for Problem 9a

Note that Figure P6.9a reflects several ambiguities. For example, although each PROF_OFFICE value shown in Table P6.9 is unique, does that limited information indicate that each professor has a private office? If so, the office number identifies the professor who uses that office. This condition yields a dependency. However, this dependency is not a transitive one, because a non- key attribute, PROF_OFFICE, determines the value of a key attribute, EMP_NUM. (We have indicated this potential transitive dependency through a dashed dependency line.)

NOTE

The assumption that PROF_OFFICE ® EMP_CODE is a rather restrictive one, because it would mean that professors cannot share an office. One could safely assume that administrators at all levels would not care to be tied by such a restrictive office assignment requirement. Therefore, we will remove this restriction in the remaining problem solutions.

Also, note that there is no reliable way to identify the effect of multivalued attributes on the dependencies. For example, EMP_NUM = 123 could identify any one of five advisees. Therefore, knowing the EMP_NUM does not identify a specific ADVISEE value. The same is true for the COMMITTEE_CODE and JOURNAL_CODE attributes. Therefore, these attributes are not marked with a solid arrow line. However, if you know that EMP_NUM = 123, you will also know all five advisees, all four committee codes, and all three journal codes for that employee number value. But you do not have a unique identification for each of those attribute values. Therefore, you cannot conclude that EMP_NUM ® ADVISEE, nor can you conclude that EMP_NUM ® COMMITTEE_CODE or that EMP_NUM ® JOURNAL_CODE.

b.  Identify the multivalued dependencies.

 Table P6.9 shows several professor attributes – ADVISEE, COMMITTEE_CODE, and JOURNAL_CODE -- that represent multivalued dependencies.

c. Create the dependency diagrams to yield a set of table structures in 3NF.

 The dependency diagrams are shown in Figure P6.9c. Note that we have assumed that it is possible that professors can share an office.

Figure P6.9c The Dependency Diagram for Problem 9c

Eliminate the multivalued dependencies by converting the affected table structures to 4NF.

 The structures shown in Figure 6.9d1 conform to the 4NF requirement. Yet this normalization does not yield a viable database design. Here is another opportunity to stress that normalization without data modeling is a poor way to generate useful databases. (Note that we have assumed that an advisee can have only one advisor, but that an advisor can have many advisees.)

Figure P6.9d1 The Initial Dependency Diagrams for Problem 9d

The dependency diagrams shown in Figure P6.9d1 constitute an attempt to eliminate the shortcomings of the ―system‖ shown in Figure P6.9c. Unfortunately, while this solution meets the normalization requirements, it lacks the ability to properly link the professors to committees and journals. (That’s because the relationships between professors and journals and between professors and committees are M:N.) This solution would yield tables P6.9d1 and P6.9d2. (One would expect a professor to be an employee, so it’s reasonable to assume that – at some point -- we’ll have to create a supertype/subtype relationship between employee and professor. (To save space, we show only the first three EMP_NUM value sets from Table P6.9.)

Table 6.9d1 Implementation of the M:N Relationship between EMP_NUM and COMMITTEE_CODE

The PK of the table shown in Table P6.9d1 is EMP_NUM + COMMITTEE_CODE.

Table 6.9d2 Implementation of the M:N Relationship between EMP_NUM and JOURNAL_CODE

 The PK of the table shown in Table P6.9d2 is EMP_NUM + JOURNAL_CODE. Because EMP_CODE = 104 does not show any entries in the JOURNAL_CODE, the employee code does not occur in Table P6.9d2.

The preceding table structures create multiple redundancies. Therefore, this solution is not acceptable. Here is yet another indication that normalization, while very useful, is not always (usually?) capable of producing implementable solutions. For example, the preceding examples illustrate that mulivalued attributes and M:N relationships cannot be effectively modeled without first using the ERD. (After the ERD has done its work, you should, of course, use dependency diagrams to check for data redundancies!) Figure P6.9e shows a more practical solution to the problem and its structures all conform to the normalization requirements.

e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in Part c. (Note: You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions.)

 Given the discussion in the previous problem segment d, we have incorporated additional features in the Crow’s Foot ERD shown in Figure P6.9e. Note that we have eliminated the M:N relationships in this design by creating composite entities. This design is implementable and it meets design standards. Normalization was part of the process that led to this solution, but it was only a part of that solution. Normalization does not replace design!

Figure P6.9e The Crow’s Foot ERD for Problem 9e