The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10.

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 client.
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.
b. Break up the dependency diagram you drew in Problem 10a to produce dependency diagrams that are in 3NF and write the relational schema.
c. Using the results of Problem 10b, draw the Crow’s FootERD.

Transcribed Image Text:

Attribute Name CLIENT NUM CLIENT NAME CLIENT REGION CONTRACT DATE CONTRACT NUMBER CONTRACT AMOUNT CONSULT CLASS 1 CONSULT CLASS 2 CONSULT CLASS 3 CONSULT CLASS 4 CONSULTANT NUM 1 CONSULTANT NAME 1 CONSULTANT REGION 1Midwest CONSULTANT NUM 2 CONSULTANT NAME 2 Karl M. Spenser CONSULTANT REGION 2 Midwest CONSULTANT NUM 3 CONSULTANT NAME 3Julian H. Donatello CONSULTANT REGION 3 Midwest CONSULTANT NUM 4 CONSULTANT NAME 4 CONSULTANT REGION 4 Sample Value 298 Marianne R. Brown Midwest 10-Feb-2014 5841 $2.985.00.00 Database Administration Intemet Services Database Design Web Applications Sample value Sample Value 289 James D Sh James D. Smith Southeast 15-Feb-2014 5842 $670,300.00 289 Southeast 12-Mar-2014 5843 $1,250,000.00 Database Administration Network Installation 34 Gerald K Ricard Angela M. Jamison Southeast 38 Anne T. Dimarco Gerald K Ricardo Southeast 45 Geraldo J. Rivera Southeast 18 Donald Chen West 29 25 Rachel G. Carson Southeast 56 34 Southeast