CS327e

Lab 3 Solution

Fall 2003

4.24 Consider the following relation definition and sample data:

PROJECT (ProjectID, EmployeeName, EmployeeSalary)

Where ProjectID is the name of a work project

EmployeeName is the name of an employee who works on that project

EmployeeSalary is the salary of the employee whose name is EmployeeName

Assuming that all of the functional dependencies and constraints are apparent in this data, which of the following statements is true?

A. ProjectID à EmployeeName

FALSE: There are multiple Employee Names fro each project. (see project 100A)

B. ProjectID à EmployeeSalary

FALSE: There are multiple Employee Salaries for each project. (see project 100A)

C. (ProjectID, EmployeeName) à EmployeeSalary

FALSE: Each employee’s salary is always the same, regardless of the project. (see Smith)

D. EmployeeName à EmployeeSalary

TRUE: An Employee Name always has the same Salary. (see Smith)

E. EmployeeSalary à ProjectID

FALSE: There are multiple ProjectIDs for a given Salary. (see Salary 51K)

F. EmployeeSalary à (ProjectID, EmployeeName)

FALSE: There are multiple ProjectID and EmployeeName combinations for a given Salary (see Salary 51K)

Answer these questions:

G. What is the key of PROJECT?

ProjectID and EmployeeName (Composite Key)

H. Are all non-key attributes (if any) dependent on all of the key?

No: EmployeeSalary is the non-key attribute and it is dependent on the EmployeeName only.

I. In what normal form is PROJECT?

Project is in first normal form because it has no multi-valued attributes. It is not in second normal form because it has a partial dependency. Key: ProjectID+EmployeeName but EmployeeName à EmployeeSalary.

J. Describe two modification anomalies from which PROJECT suffers.

Insertion Anomaly: You cannot add an Employee until the Employee is assigned to a Project. Likewise, you cannot add a Project until and Employee is assigned to the Project.

Update Anomaly: If you want to change Smith’s Salary you will need to change three rows of data in order to change one Employee’s salary.

Deletion Anomaly: If Parks did not work on Project 200C and worked in Project 200D only, deletion of ProjectC would delete the fact that Park’s salary was 28K.

K. Is ProjectID a determinant?

No

L. Is EmployeeName a determinant?

YES: EmployeeName à EmployeeSalary

M. Is (ProjectID, EmployeeName) a determinant?

No

N. Is EmployeeSalary a determinant?

No: In this particular case it appears that it could a determinate because no two people have the same salary. Using logic however, one would assume that there is no business rule in a firm that says two people cannot have the same salary.

O. Does this relation contain a partial dependency? If so, what is it?

YES, the relation does contain a partial dependency. The key is ProjectID+EmployeeName but EmployeeName à EmployeeSalary.

P. Redesign this relation to eliminate the modification anomalies.

PROJECT (ProjectID, EmployeeName)

EMPLOYEE (EmployeeName, EmployeeSalary)

5.73 Answer question 3.36 if you have not already done so. Create a database design for your model. Your design should include a specification of tables, attributes, primary and foreign keys, referential integrity constraints, and referential integrity actions.

Referential integrity constraint:

TICKET.License_Id must exist in DRIVER.License_Id

VEHICLE..License_Id must exist in DRIVER.License_Id

VIOLATIONS.TicketNumber must exist in TICKET.TicketNumber

TICKET.TicketNumber must exist in OFFICER.TicketNumber