tribute Name Sample Value Sample value Sample Value CLIENT_NUM 298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith
Posted: Fri May 20, 2022 11:34 am
tribute 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-2020 15-Feb-2020 12-Mar-2020 CONTRACT_NUMBER 5841 5842 5843
CONTRACT_AMOUNT $2,985,00.00 $670,300.00 $1,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. 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 Table 1:
Sample CLIENT Records 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 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 minimise travel expense, it is
not always possible to do so.) The following basic business rules
are maintained: (ICT201-DataBase System) Semester 2 2021 3 • 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)
298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith James D.
Smith CLIENT_REGION Midwest Southeast Southeast CONTRACT_DATE
10-Feb-2020 15-Feb-2020 12-Mar-2020 CONTRACT_NUMBER 5841 5842 5843
CONTRACT_AMOUNT $2,985,00.00 $670,300.00 $1,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. 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 Table 1:
Sample CLIENT Records 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 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 minimise travel expense, it is
not always possible to do so.) The following basic business rules
are maintained: (ICT201-DataBase System) Semester 2 2021 3 • 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)