TABLE P6.10 ATTRIBUTE NAME CLIENT_NUM CLIENT_NAME CLIENT REGION CONTRACT_DATE CONTRACT_NUMBER CONTRACT_AMOUNT SAMPLE VALUE 298 Marianne R. Brown Midwest 10-Feb-2018 5841 $2,985,000.00 Database Administration SAMPLE VALUE 289 James D. Smith Southeast 15-Feb-2018 5842 $670,300.00 Internet Services SAMPLE VALUE 289 James D. Smith Southeast 12-Mar-2018 5843 $1,250,000.00 Database Design Database Administration Network Installation CONSULT_CLASS_1 CONSULT_CLASS_2 Web Applications CONSULT_CLASS_3 CONSULT_CLASS_4 CONSULTANT_NUM_1 CONSULTANT_NAME_1 29 CONSULTANT_REGION_1 CONSULTANT_NUM_2 CONSULTANT_NAME_2 CONSULTANT REGION_2 CONSULTANT_NUM_3 CONSULTANT_NAME_3 CONSULTANT_REGION_3 Rachel G. Carson Midwest 56 Karl M. Spenser Midwest 22 Julian H. Donatello Midwest 34 25 Gerald K. Ricardo Southeast Angela M. Jamison Southeast 38 34 Anne T. Dimarco Gerald K. Ricardo Southeast Southeast 45 Geraldo J. Rivera Southeast CONSULTANT_NUM_4 CONSULTANT_NAME_4 CONSULTANT REGION_4 18 Donald Chen West
Hi, can you help me with this question? I have an exam coming up, and I'm using the book problems to study.
The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10. (This should be attached).
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. 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 on client.
Each contract might cover multiple consulting classifications.
Each consultant is located in one region.
A region can contain many consultants.
Each consultant has one or more areas of expertise.
Each area of expertise (class) can have many consultants.
Part A of the question:
Given this 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. Label all transitive and/or partial dependencies.
Step by step
Solved in 2 steps
