Answer the following questions about the Employee Assignment

Answer the following questions about the Employee Assignment table:

(Assume that all the data is shown in the table)

What is the primary key? Justify your answer. (2 points)

List all functional dependencies. (2 points)

What normal form(s) does the table satisfy? Justify your answer. (2 points)

Normalize the table below to remove all anomalies (i.e. create two or more smaller tables to replace the table below). (2 points)

Write a definition for 1NF, 2NF, 3NF. (2 points)

Employee Assignment Table

Emp_Id

Emp_Last

Name

Emp_

Location

Dept_

Name

Project_Id

Project_Name

Project_

Director

Proj_

Location

Project_

Budget

100

Barker

Huntsville

IST

IST350

Web Development

David

H104

$5,000

100

Barker

Huntsville

IST

IST463

Database

Design

Green

H317

$10,000

200

Brown

Huntsville

IST

IST463

Database

Design

Green

H317

$10,000

300

Jones

Parkway

ACC

ACC201

Account

Management

Miller

P202

$8,000

400

Smith

Parkway

MKT

MKT320

Marketing

Strategy

Blake

P231

$5,000

400

Smith

Parkway

MKT

MKT430

Online

Marketing

Rodriquez

P240

$10,000

Emp_Id	Emp_Last Name	Emp_ Location	Dept_ Name	Project_Id	Project_Name	Project_ Director	Proj_ Location	Project_ Budget
100	Barker	Huntsville	IST	IST350	Web Development	David	H104	$5,000
100	Barker	Huntsville	IST	IST463	Database Design	Green	H317	$10,000
200	Brown	Huntsville	IST	IST463	Database Design	Green	H317	$10,000
300	Jones	Parkway	ACC	ACC201	Account Management	Miller	P202	$8,000
400	Smith	Parkway	MKT	MKT320	Marketing Strategy	Blake	P231	$5,000
400	Smith	Parkway	MKT	MKT430	Online Marketing	Rodriquez	P240	$10,000

Solution

1- Project_id is the primary key as it\'s the only unique key/ column present in the table and hence helps in uniquely identify each row or column here.

2- Functional dependencies- It is the relationship between two attributes in a table where one attribute uniquely defines the other attribute. If Y is dependent on X over relationship R then we write it as X-> Y. Here the functional dependencies are:

Dept_name-> Project_id

Emp_location -> proj_location

Please note: columns after proj_location are not visible.

3- The table is in first normal form i. e. 1 NF

This can be justified by the fact that each row has no column with more than one value, however we have columns having repeated values but distinct rows.

4- To make this into 2nd normal form we can break it into following tables:

Employee details table having the first fivr columns of the given table i. e. Emp_id, emp_lastname, emp_location, dept_name and proj_location.

And second table as project details table with columns project_id , project_name ,project_director and the following columns.