Question

Given the following spreadsheet EMPLOYEE2 Emp ID Name Dept Name Salary Course_Title Date Completed 100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Alan Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info Systems 43,000 SPSS Info Systems 43,000 C++ Finance Marketing Marketing 48,000 SPSS 48,000 Surveys 52,000 Tax Aco 6/19/200X 10/7/200X 12/8/200X 1/12/200X 4/22/200X 55,000 42.000 SPSS 2,000Java 6/19/200X 8/12/200X If a student simply put all shown attribute into a database table EMPLOYEE2 (Emp ID, name, Dep.Name, Slary Course Title, Based on your analysis on the given data in this sheet. Answer the the following Questions B1. List all the functional dependencies (FDs.). Assuming all possible FDs are implied by the given set of data B2. What is the key of EMPLOYEE2 B3. What Normal Form (NF) this table is in? Why give your detailed explanation. You must use the definition of corresponding normal form to justify your answer B4. Describe Insert/update/delete anomalies this table may have

Answer 1

1)

FD1:

Emp ID ---> Name, Dept Name, Salary, Course_Title, Date Completed;

FD2:

Name ---> Emp ID,Name Dept Name, Salary, Course_Title, Date Completed;

2)

EMP ID is the primary key of EMPLOYEE2

3) & 4)

A table is said to be not normalised because of the in consistence.

The given table is not normalised as with the below scenarios:

Update anomaly:

If we want to update the emp Susan Martin here we have to update same in two rows which is in consistent.

Insert anomaly:

When we want to insert an emp with out knowing the dept name of him then when dept row cant allow null values then we can not update the employee table.

Delete anomaly:

when an updation of department table modified there then must be change in here also.sometimes we delete some departments that may leads to deletion of employee record also.