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

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-2008	15-Feb-2008	12-Mar-2008
CONTRACT_NUMBER	5841	5842	5843
CONTRACT_AMOUNT	$2,985,000.00	$670,300.00	$1,250,000.00
CONSULT_CLASS_	Database Administration	Internet Services	Database Design
CONSULT_CLASS_	Web Applications	Â	Database Administration
CONSULT_CLASS_	Â	Â	Network Installation
CONSULT_CLASS_	Â	Â	Â
CONSULTANT_NUM_	29	34	25
CONSULTANT_NAME_	Rachel G. Carson	Gerald K. Ricardo	Angela M. Jamison
CONSULTANT_REGION_	Midwest	Southeast	Southeast
CONSULTANT_NUM_	56	38	34
CONSULTANT_NAME_	Karl M. Spenser	Anne T. Dim Arco	Gerald K. Ricardo
CONSULTANT_REGION_	Midwest	Southeast	Southeast
CONSULTANT_NUM_	22	45	Â
CONSULTANT_NAME_	Julian H. Donatello	Geraldo J. Rivera	Â
CONSULTANT_REGION_	Midwest	Southeast	Â
CONSULTANT_NUM_	Â	18	Â
CONSULTANT_NAME_	Â	Donald Chen	Â
CONSULTANT_REGION_	Â	West	Â

Table P5.18 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 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 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 design 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. 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.

Q28

Break up the dependency diagram you drew in Problem 18 to produce dependency diagrams that are in 3NF and write the relational schema.

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-2008	15-Feb-2008	12-Mar-2008
CONTRACT_NUMBER	5841	5842	5843
CONTRACT_AMOUNT	$2,985,000.00	$670,300.00	$1,250,000.00
CONSULT_CLASS_	Database Administration	Internet Services	Database Design
CONSULT_CLASS_	Web Applications	Â	Database Administration
CONSULT_CLASS_	Â	Â	Network Installation
CONSULT_CLASS_	Â	Â	Â
CONSULTANT_NUM_	29	34	25
CONSULTANT_NAME_	Rachel G. Carson	Gerald K. Ricardo	Angela M. Jamison
CONSULTANT_REGION_	Midwest	Southeast	Southeast
CONSULTANT_NUM_	56	38	34
CONSULTANT_NAME_	Karl M. Spenser	Anne T. Dim Arco	Gerald K. Ricardo
CONSULTANT_REGION_	Midwest	Southeast	Southeast
CONSULTANT_NUM_	22	45	Â
CONSULTANT_NAME_	Julian H. Donatello	Geraldo J. Rivera	Â
CONSULTANT_REGION_	Midwest	Southeast	Â
CONSULTANT_NUM_	Â	18	Â
CONSULTANT_NAME_	Â	Donald Chen	Â
CONSULTANT_REGION_	Â	Â