6.  Given the dependency diagram shown in Figure Q4.6, answer Questions 6a through 6c:

 


Figure Q4.6 The Dependency Diagram for Questions 6a through 6c

 


a.  Identify and discuss each of the indicated dependencies.

 

C1 ® C2 represents a partial dependency, because C2 depends only on C1, rather than on the entire primary key composed of C1 and C3.

 

C4 ® C5 represents a transitive dependency, because C5 depends on an attribute (C4) that is not part of a primary key.

 

C1, C3 ® C2, C4, C5 represents a functional dependency, because C2, C4, and C5 depend on the primary key composed of C1 and C3.

 

b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.

 


Figure Q4.6b The Dependency Diagram for Question 6b

 


c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.

 


Figure Q4.6c The Dependency Diagram for Question 6c

 


18. The manager of a consulting firm has asked you to evaluate a database that contains the following table structure.

 

Attribute name           Sample value

 

CLIENT_NUM          289

CLIENT_NAME       James D. Smith

DATE                         12-Mar-02

CONTRACT              5842

CLASS_1                    Database

CLASS_2                    Networking

CLASS_3

CLASS_4

REGION                    SE

CONS_NUM_1         25

CONS_NAME_1       Angela M. Jameson

CONS_NUM_2         34

CONS_NAME_2       Gerald K. Ricardo    

CONS_NUM_3        

CONS_NAME_3      

CONS_NUM_4        

CONS_NAME_4      

 

The table was created to enable the manager to match clients with consultants. The objective is to match clients within a given region with consultants in that region, and to make sure that the client's need for specific consulting services is properly matched to the consultants' expertise. For example, if the client is in the banking business 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 the banking business. The following basic business rules are maintained:

 

¨      Each client is located in one region, but a region can contain many clients

 

¨      Each consultant can serve many clients and each client can be served by many consultants

 

¨      A client can sign more than one contract, but each contract is signed by one client.

 

¨      Each contract covers one classification. (For example, if a client requires consulting work in database and networking, two contracts must be executed and signed.)

 

¨      Each consultant is located in one region, but a region can contain many consultants.

 

 

 

¨      Each consultant has one or more areas of expertise (class) and each area of expertise (class) may have many consultants in it. For example, a consultant might be classified as an expert in database and networking and the consulting company might employ many consultants who are networking experts.

 

¨      A client might need for consulting in more than one classification. For example, a client might require help in database, banking, and networking.

 

Given this brief description of the requirements and the business rules, draw the dependency diagram for the following (very poor!) table structure. Label all transitive and/or partial dependencies.

 

The sample data shown in this problem description indicate that it takes both the CLIENT_NUM = 289 and the CONTRACT = 5842 to uniquely identify all remaining attributes. (A client can have several different contracts, 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 it takes more that the CLIENT_NUM to identify 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, 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.)

 

Use the next records to illustrate these points:

 

Attribute name           Sample value                          Attribute name           Sample value

 

CLIENT_NUM           290                                          CLIENT_NUM           290

CLIENT_NAME         Suzanna G. Harmon                  CLIENT_NAME         Suzanna G. Harmon

DATE                          5/12/1999                                DATE                          5/12/1999

CONTRACT               5843                                        CONTRACT               5844

CLASS_1                    Database                                  CLASS_1                    Accounting

CLASS_2                    Networking                              CLASS_2                   

CLASS_3                                                                    CLASS_3       

CLASS_4                                                                    CLASS_4

REGION                     SE                                            REGION                     SE

CONS_NUM_1          25                                            CONS_NUM_1          25

CONS_NAME_1        Angela M. Jameson                  CONS_NAME_1        Angela M. Jameson

CONS_NUM_2          34                                            CONS_NUM_2         

CONS_NAME_2        Gerald K. Ricardo                   CONS_NAME_2        

CONS_NUM_3                                                          CONS_NUM_3

CONS_NAME_3                                                        CONS_NAME_3                   

CONS_NUM_4                                                          CONS_NUM_4

CONS_NAME_4                                                        CONS_NAME_4                   

 

 

As you examine these records, note that

 

¨      Both James D. Smith (from the record shown in the problem) and Suzanna G. Harmon (from the record shown here) are from the SE region

 

¨      Both have the same problems (database and networking) and the same consultants (Angela M. Jameson and Gerald K. Ricardo.)

 

Note, incidentally, that the multivalued attributes have been handled poorly by creating different attributes for each of the expected multivariate attribute values. This approach creates many unnecessary nulls and, if a client and/or contract require more than four classes and/or more than four consultants, the table structure must be altered which, in turn, generates more nulls for the remaining clients.

 

Although the table structure shown here indicates that (apparently) several consultants can work on the same contract, while different contracts can make use of the same consultants and areas of expertise, it is clear that the CONTRACT identifies CONS_NUM_1 through CONS_NUM_4. In short, the CONTRACT number uniquely determines the consultants assigned to it. (Given the problem's sample data, if you know the contract number is 5842, you also know that Angela M. Jameson and Gerald K. Ricardo are assigned to it.) Therefore, partial dependencies exist between the CONTRACT number and the CONS_NUM attributes. Thee dependencies show up in Figure P4.18.

 


Figure P4.18 The Dependency Diagram for Problem 18

As you examine Figure P4.18, note that a client can have multiple contracts and multiple problem areas. Therefore, the additional records we have shown in this discussion demonstrate that CLIENT_NUM does not uniquely identify any of the CLASS_1 through CLASS_4 attribute values, nor does the CLIENT_NUMBER uniquely identify the CONTRACT number. Therefore, there are no partial dependencies between CLIENT_NUM and the CLASS_1 through CLASS_4 attributes, nor is there a partial dependency between CLIENT_NUMBER and CONTRACT. On the other hand, because one-customer signs each contract, the CLIENT_NUMBER is dependent on the CONTRACT number. Finally, if you examine the brief summary of business rules, you realize that, if you know the CLIENT_NUM, you also know the REGION... and if you know the consultant's number (CONS_NUM_1 through CONS_NUM_4), you also know the REGION. 

 

19. Break up the dependency diagram you drew in problem 18 to produce dependency diagrams that are in 3NF. 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 P4.19, 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 18's sample values are probably best handled through the use of composite entities. We will resolve those issues in the answers to Problems 20 and 21.)

 


Figure P4.19 The Dependency Diagrams for Problem 19

 


Also, keep in mind that it would be a good idea to make the attributes more atomic. For example, the CLIENT_NAME ought to be decomposed into CLIENT_FNAME, CLIENT_LNAME, and CLIENT_INITIAL. The CONS_NAME must be similarly decomposed.

 

Finally, 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. There is no way to standardize billing charges by class, nor is there a way to keep track of billable hours by class, by consultant, and by class. As an exercise, you can expand this small system to make it implementable. Better yet, use database software such as Microsoft SQL Server to build applications based on this expanded system. Naturally, the system must be able to handle invoicing and reporting of consulting activities by consultant, by type, by client, and so on.

 

20. Using the results of problem 19, draw the E-R diagram.

 


Figure P4.20 The ERD for Problem 20

 

 


Whether or not optionalities are included in the ERD depends on the business rules and on the operational requirements. We have included optionalities in Figure P4.20A to show the effect on the cardinalities.


Figure P4.20A The Dependency Diagram for Problem 20

 


      

For questions 29, see diagram below.