Question: Simple Technology, a Kansas based comapny, is working on a project to design and implement a database for tracking employeees, their personal information, and information

Simple Technology, a Kansas based comapny, is working on a project to design and implement a database for tracking employeees, their personal information, and information about projects. Some of the attributes that they want to store in the database is given as a tabular format below.

PCode PTitle PManager PBudget PDept PDuration ENum Ename ECity EZip EDeptNo EDeptName HRate
P1 Payroll Martin 100K Accounting 12 E13 A.Smith Liberty 64068 D4 R&D 22
P1 Payroll Martin 100K Accounting 12 E30 I.Jones Liberty 64068 D3 IT 18.5
P1 Payroll Martin 100K Accounting 12 E31 P Jouet Liberty 64069 D4 R&D 21
P2 Budget Baker 780K Finance 16 E11 R. Ali Liberty 64069 D4 R&D 21
P2 Budget Baker 780K Finance 16 E13 A.Smith Liberty 64068 D4 R&D 18
P2 Budget Baker 780K Finance 16 E9 S. Gilbert Buckner 64076 D5 Management 25.5
P3 Hiring Lewis 100K HR 24 E9 S.Gilbert Buckner 64076 D5 Management 23.25
P3 Hiring Lewis 100K HR 24 E31 P.Jouet Liberty 64069 D4 R&D 17
P4 Millennium Lewis 780K Finance 12 E10 K.Ross Buckner 64076 D8 Sales 17
P5 Millennium Martin 200K HR 12 E27 A.Smith Buckner 64076 D9 Operation 16.5

a) Write all functional dependencies for the above relation

b) Using step-by-step process, normalize the above relation to BCNF. Make sure you go through all normalization steps from 1NF to BCNF. Show your work. Your work must include the following:

i. Indicate all anomalies lead to the violation of each normalization conditions.

ii. List schemas for the final set of relations that are normalized to BCNF. Make sure to indicate all the primary, foreign and surrogate keys (if used) in the final schemas.

iii. Indicate referrential integrity constraint for the final set of schemas

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

please a,b 1.23 in detail Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information, and information...

Q:

write the answer clearly thank you so much 1. Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information,...

Q:

Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information such as their address and salaries, and...

Q:

Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information such as their address and salaries, and...

Q:

Normalize to BCNF using the 4 step straight-to-BCNF process. Show work. List final set of relations normalized to BCNF. Include schemas for all normalized relations. Indicate primary, foreign, and...

Q:

Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information such as their address and salaries, and...

Q:

2. Simple Technology, a Kansas based company, is working on a second project to design and implement a database for tracking vital information for an educational institution. Some of the attributes...

Q:

2. Simple Technology, a Kansas based company, is working on a second project to design and implement a database for tracking vital information for an educational institution. Some of the attributes...

Q:

please help answer with little bit explain Simple Technology, a Kansas based company, is working on a second project to design and implement a database for tracking vital information for an...

Q:

Simple Technology, a Kansas based company, is working on a second project to design and implement a database for tracking vital information for an educational institution. Some of the attributes that...

Q:

Write code segments that accomplish each of the following tasks: a) Calculate the integer part of the quotient when integer a is divided by integer b. b) Calculate the integer remainder when integer...

Q:

The Supplies account began the year with a balance of $380. During the year, supplies in the amount of $980 were purchased. At the end of the year (December 31), the inventory of supplies on hand was...

Q:

Metlock Company shows the following entries in its Equipment account for 2 0 2 6 . All amounts are based on historical cost. Prepare correcting entry if necessary. ( If no entry is required, select "...

Q:

On 1 March 2007 DB Limited issued R560 000 15% debentures at R98. The debentures were to be redeemed at par in four equal annual payments starting 28 February 2010. Required: Journalise the above...