In this Assignment, you are implementing and writing several SQL queries on a relational Database.

Requirements

You have been appointed as the manager of a consulting firm and asked you to evaluate a database

that contains the table structure shown in table $1$ below. Table below 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 consultant$$s expertise. For example, if the client needs help with

database design and is in the Southeast, the objective is to make a match with a consultant who is 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 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.

Tasks to be completed:

$1.$ Given this brief description of the requirements and the business rules, write the

relational schema and draw the dependency diagram for the preceding table structure.

Label all transitive and partial dependencies.

$2.$ Break up the dependency diagram you drew in task $(1)$ to produce a dependency

diagram that are in $3$NF 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$.)$

$3.$ Design a crow$$s foot ER Diagram for task $(2)$ with all the entity names, attribute names,

primary and foreign keys, relationships, cardinality, and participation indicated.

$4.$ Create a database using MySQL$,$ with primary keys, foreign keys, and other attributes mentioned

for each entity of the ERD in task $(3)$ using proper constraints.

$5.$ Populate each of the tables created in task $(4)$ with some significant data $($minimum $8$ rows in

each table$)$

$6.$ Create queries using the following criteria:

$$ Select

$$ Numeric Function

$$ Order by

$$ Group by

$$ Aggregate Functions

$$ Join

$$ update

Prepare a business$-$style report that contains the following: Each SQL query and

associated output. Create a table for consultant with given attributes from the figure of table for sample data: $\backslash$table$[[$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,$$2,985,00\mathrm{.}00,$$$670,300\mathrm{.}00,$$$1,250,000\mathrm{.}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$.$ Dimarco,Gerald K$.$ Ricardo$],[$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 Chen,$],[$CONSULTANT$\_$REGION$\_4,,$West,$]]$ Give me the commands to create a table for consultant attribute and also show the picture of it$.$ Thank you.