The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in the table below. Table: Sample CLIENT records Attribute name Sample value Sample value Sample value CLIENT_NUM 298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith James D. Smith CLIENT_REGION Midwest Southeast Southeast CONTRACT_DATE 10-Feb-2018 15-Feb-2018 12-Mar-2018 CONTRACT_NUMBER 5841 5842 5843 CONTRACT_AMOUNT R22,985,00.00 R1,670,300.00 R11,250,000.00 CONSULT_CLASS_1 Database administration Internet services Database design CONSULT_CLASS_2 Web applications Database administration CONSULT_CLASS_3 Network installation CONSULT_CLASS_4 CONSULTANT_NUM_1 29 34 25 CONSULTANT_NAME_1 Rachel G. Carson Gerald K. Ricardo Angela M. Jamison CONSULTANT_REGION_1 Midwest Southeast Southeast CONSULTANT_NUM_2 56 38 34 CONSULTANT_NAME_2 Karl M. Spenser Anne T. Zuma Gerald K. Jele CONSULTANT_REGION_2 Midwest Southeast Southeast CONSULTANT_NUM_3 22 45 CONSULTANT_NAME_3 Julian H. Donatello Geraldo J. Rivera CONSULTANT_REGION_3 Midwest Southeast CONSULTANT_NUM_4 18 CONSULTANT_NAME_4 Donald Tau CONSULTANT_REGION_4 West The table 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 needs help with database design and is INF2603/202 5 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 manager tries to match consultant and client locations to minimise travel expenses, 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 databases and networking.) • Each consultant is located in one region. • A region can contain many consultants. • Each consultant has one or more areas of expertise (classes). 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.
The manager of a consulting firm has asked you to evaluate a
Table: Sample CLIENT records Attribute name Sample value Sample value Sample value CLIENT_NUM 298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith James D. Smith CLIENT_REGION Midwest Southeast Southeast CONTRACT_DATE 10-Feb-2018 15-Feb-2018 12-Mar-2018 CONTRACT_NUMBER 5841 5842 5843 CONTRACT_AMOUNT R22,985,00.00 R1,670,300.00 R11,250,000.00 CONSULT_CLASS_1 Database administration Internet services Database design CONSULT_CLASS_2 Web applications Database administration CONSULT_CLASS_3 Network installation CONSULT_CLASS_4 CONSULTANT_NUM_1 29 34 25 CONSULTANT_NAME_1 Rachel G. Carson Gerald K. Ricardo Angela M. Jamison CONSULTANT_REGION_1 Midwest Southeast Southeast CONSULTANT_NUM_2 56 38 34 CONSULTANT_NAME_2 Karl M. Spenser Anne T. Zuma Gerald K. Jele CONSULTANT_REGION_2 Midwest Southeast Southeast CONSULTANT_NUM_3 22 45 CONSULTANT_NAME_3 Julian H. Donatello Geraldo J. Rivera CONSULTANT_REGION_3 Midwest Southeast CONSULTANT_NUM_4 18 CONSULTANT_NAME_4 Donald Tau CONSULTANT_REGION_4 West
The table 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 needs help with database design and is
INF2603/202
5
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 manager tries to match consultant and client locations to minimise travel expenses, 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 databases and networking.)
• Each consultant is located in one region.
• A region can contain many consultants.
• Each consultant has one or more areas of expertise (classes). 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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
