Question

Normalize this database so that it is in third normal form.

Draw an ER diagram of the resulting database.

Write a Database Design Outline for the resulting database.

Assignment ID	Temp Code	Temp Name	Dept Code	Dept Name	Job Code	Job Description	Date	Hours Spent	Rate /hour
11772	1013	Bill Wong	ENG	Engineering	TC	Technical Consulting	2/27/2011	3	$100
11765	1015	Melanie East	MKT	Marketing	TC	Technical Consulting	1/25/2011	4	$120
11767	2051	Ed North	ACT	Accounting	CLR	Clerical	1/31/2011	2	$50
11768	2051	Ed North	MKT	Marketing	CLR	Clerical	2/10/2011	4	$55
11769	3356	John Edokway	SLS	Sales	SC	Strategic Consulting	2/15/2011	5	$90
11773	3356	John Edokway	MKT	Marketing	EDT	Editing	3/20/2011	6	$60
11771	4076	Marissa Estevez	ENG	Engineering	EDT	Editing	2/26/2011	7	$65
11766	4079	Hannah Goldberg	ENG	Engineering	TC	Technical Consulting	1/25/2011	4	$110
11770	4079	Hannah Goldberg	MKT	Marketing	EDT	Editing	2/25/2011	5	$70

Answer 1

Normalization to 3NF

Functional Dependencies

TempCode -> TempName

DeptCode -> DeptName

JobCode -> JobDescription

AssignmentID , TempCode, DeptCode, JobCode -> Date, HoursSpent, RatePerHour

Tables in 3NF

Temp(TempCode , TempName)

Dept(DeptCode , DeptName)

Job(JobCode , JobDescription)

Assignment(AssignmentID , TempCode,DeptCode,JobCode,Date,HoursSpent,RatePerHour)

underlined are primary keys and italicized are foreign keys.

ER Diagram

Do ask if any doubt. Please up-vote.