Table P6.10 Sample CLIENT Records

Table P6.10 was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultant in that region, and to make sure that the client’s need for specific consulting services is properly matched to the consultant’s expertise. For example, if the client need help with database design and is located in the Southeast, the objective is to make a match with a consultant who is located in the Southeast and whose expertise is in database design. (Although the consulting company manage tries to match consultant and client locations to minimize travel expense, it is not always possible to do so.) The following basic business rules are maintained:

• Each client is located in one region
•  A region can contain many clients.
• Each consultant can work on many contracts
•  Each contract might require the services of many consultants.
• A client can sign more than one contract, but each contract is signed by only one
• Each contract might cover multiple consulting classifications. (For example, a contract may list consulting services in database and networking.)
• Each consultant is located in one region.
•  A region can contain many consultants.
• Each consultant has one or more areas of expertise (class). For example, a consultant might be classified as an expert in both database design and networking.
• Each area of expertise (class) can have many consultants in it. For example, the consulting company might employ many consultants who are networking experts.a. Given that brief description of the requirements and the business rules, write the relational schema and draw the dependency diagram for the preceding (and very poor) table structure. Label all transitive and/or partial dependencies.

  Here is a perfect illustration of the value of business rules. If the business rules had not been available, the sample record would produce ambiguities. For example, if you only look at the sample data in the one available record, defining the relationships between client, contract, date, consultant, and expertise would have been difficult, at best. The business rules augment the original data and their use removes the ambiguities. The business rules help establish that a client can sign more than one contract, so you need more than the client number to identify the remaining attributes. Clearly, another client can sign a contract on the same date, so the CLIENT_NUM is not the determinant of the date. Also, the same client can sign multiple contracts on the same date or on different dates, using the same set of consultants for each contract or a different set of consultants for each contract. Remember also that the consultants have more than one area of expertise, so the same consultant may work on different contracts for the same client or for different clients.

  Given the combination of the business rules and the sample record in the original problem – or given the use of the two records provided in the first part of this discussion -- the dependencies show up in Figure P6.10a.

   

  Figure P6.10a The ConsultCo Dependency Diagram

  Note: The REGION attribute has been duplicated to show all of the dependencies in a single diagram

   

  The relational schema is written as follows:

   

  CONTRACT(CLIENT_NUM, CLIENT_NAME, DATE, CONTRACT, CLASS_1, CLASS_2, CLASS_3, CLASS_4, REGION, CONS_NUM_1, CONS_NAME_1, CONS_NUM_2, CONS_NAME_2, CONS_NUM_3, CONS_NAME_3, CONS_NUM_4, CONS_NAME_4)

   

  Or, if you prefer that the PK be the first listed attribute, you can write the relational schema this way: CONTRACT(CONTRACT, CLIENT_NUM, CLIENT_NAME, DATE, CLASS_1, CLASS_2,

  CLASS_3, CLASS_4, REGION, CONS_NUM_1, CONS_NAME_1, CONS_NUM_2, CONS_NAME_2, CONS_NUM_3, CONS_NAME_3, CONS_NUM_4, CONS_NAME_4)

   

  In any case, remind your students that the order in which the attributes are listed is immaterial in a relational database environment.

  b. Break up the dependency diagram you drew in Problem 10a to produce dependency diagrams that are in 3NF and write the relational schema. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.                                                                                                                                                                                                                                                                                       To complete the structures, we have added the REGION_NAME and we have modified the attribute names to make them conform to our naming conventions. Although the normalization procedure has left us with the 3NF system shown in Figure P6.10b, it is not possible to see that some of the relationships between the entities are of the M:N variety. (It would be appropriate to point out that the multivalued attributes encountered in Problem 10's sample values are probably best handled through the use of composite entities. Similarly, the M:N relationship between contract and consultant would have to be handled through a composite entity, perhaps named ASSIGNMENT, to indicate the assignment of consultants to contracts. We will resolve those issues in the answers to subsequent problems.) Here is yet another indication that normalization, while very useful as a tool to help eliminate data redundancies, is incapable of serving as the sole source of good database design.  

Figure P6.10b The ConsultCo Dependency Diagrams in 3NF

The relational schemas are written as follows: CLIENT(CLIENT_NUM, CLIENT_NAME, REGION_CODE)

CLASS(CLASS_CODE, CLASS_DESCRIPTION)

CONTRACT(CONTR_NUM, CLIENT_CODE, CONTR_DATE, REGION_CODE)

CONSULTANT(CONS_NUM, CONS_NAME, REGION_CODE)

REGION(REGION_CODE, REGION_NAME)

Keep in mind that the preceding dependency diagrams and relational schemas do not (yet) define a practical design. For example, processing requirements usually dictate that the attributes be made more atomic. (Printing mailing labels, creating mailing lists and phone directories would mandate the decomposition of CLIENT_NAME into CLIENT_FNAME, CLIENT_LNAME, and CLIENT_INITIAL. The CONS_NAME must be similarly decomposed.)

Also, remember that this simple system lacks many important entities and attributes. For instance, at this point there's no way to contact the clients, nor can clients contact the consultants. Clearly, we ought to add addresses and phone numbers. However, we have added some crucial relationships to enable us to track billing charges by class and to track billable hours by class, by consultant, and by class. (Note also that the ASSIGN_CHG_HOUR is written into the ASSIGNMENT table by the applications software from the CLASS table to ensure the historical accuracy of the charges. If the CLASS_CHG_HOUR changes, we must preserve the original charge per hour that was in effect when the assignment charge was made.) You can let your students use database software such as Microsoft Access to implement this system. Naturally, you can add tables and attributes to enable the system to handle invoicing and reporting of consulting activities by consultant, by type, by client, and so on. We have added a few of the appropriate entities and attributes in the answer to problem 10c.

c. Using the results of Problem 10b, draw the Crow’s Foot ERD.

The Crow’s Foot ERD is shown in Figure P6.10c.

Figure P6.10c The ConsultCo ERD for Problem 10c

The addition of the ASSIGNMENT entity addresses the problem of keeping track of billable hours and charges by consultant and that the addition of the SKILL entity enables the end user to track all consultant qualifications.

Whether or not optionalities are included in the ERD depends on the business rules and on the operational requirements. For example, you can infer from Figure P6.10c that the ASSIGNMENT entity  does  not  necessarily  contain  a  given  CLASS  code.  (Perhaps  there  is  a  ―customer  support‖ classification that may not have been used – yet.) Similarly, you can infer that a given CONTRACT number has not (yet) been used in the ASSIGN entity. (It is again worth emphasizing that many optionalities exist for operational reasons. That’s why the optionality is often used as the default condition. In any case, the database designer is obligated to develop precise business rules to make sure that the data environment is properly reflected in the design.)