ICT201 Consulting Firm DB

The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in the Table 1 below:

Table 1 was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultation 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 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 minimise 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. For example, the consulting company might employ many consultants who are networking experts.

Tasks to be completed:

a) 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 structure. Label all transitive and/or partial dependencies.

b) Break up the dependency diagram you drew in task (a) to produce dependency diagram that are in 3NF and write the relational schema. (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 there are no multivalued attributes, that the naming conventions are met, and so on.)

c) Using the results of task (b), draw the Crow's Foot ERD.

d) Create a database using MySQL, with primary keys, foreign keys, and other attributes mentioned for each entity of the ERD in task (c) using proper constraints.

e) Populate each of the tables created in task (d) with some significant data (See Table 1 for sample data)

f) Create FOUR queries. (One SELECT, one Numeric function and two JOIN).