CS327e

Lab 3 Solution

Spring 2004

4.25 Consider the following relation definition and sample data:

PROJECT-HOURS (EmployeeName, ProjectID, TaskID, Phone, TotalHours)

Where EmployeeName is the name of an employee

ProjectID is the name of a project

TaskID is the name standard work task

Phone is the employee’s telephone number

TotalHours is the hours worked by the employee on this project

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

A. EmployeeName à ProjectID

NO: There are multiple ProjectIDs for each EmployeeName

B. EmployeeName àà ProjectID

YES: Each EmployeeName has two or more ProjectIDs

C. EmployeeName à TaskID

NO: There are multiple TaskIDs for each EmployeeName

D. EmployeeName àà TaskID

YES: Don has multiple (2) TaskIDs

E. EmployeeName à: Phone

YES: Each EmployeeName has exactly one Phone value

F. EmployeeName à: TotalHours

YES: Each EmployeeName has exactly one TotalHours value

G. (EmployeeName, ProjectID) à TotalHours

YES: This is true based upon the fourth assumption stated above. Looking at the data only, it is more probable that EmployeeName à TotalHours. This is because the TotalHours is the same for an EmployeeName regardless of the ProjectID.

H. (EmployeeName, Phone ) à TaskID

NO: There are multiple TaskIDs for a given EmployeeName, Phone combination

I. ProjectID à TaskID

NO: There are multiple TaskIDs for a given ProjectID

J. TaskID à ProjectID

NO: There are multiple ProjectIDs for a given TaskID

Answer these questions:

K. What are all of the determinants?

If we were to purely look at the data, it is possible to assume that

Phone à EmployeeName, TotalHours

TotalHours à EmployeeName, Phone

TaskID à EmployeeName, Phone

But for the problem on hand, we will assume that this is not the case, as this is quite unlikely to happen in the real world.

EmployeeName à Phone

EmployeeName à à TaskID

EmployeeName à à ProjectID

EmployeeName, ProjectID à TotalHours

Hence the determinants are

(EmployeeName)

(EmployeeName, ProjectID)

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

YES, it does contain a partial dependency. Since the key is EmployeeName + ProjectID + TaskID but EmployeeName à Phone, there is a partial dependency.

M. Does this relation contain a multi-value dependency? If so, what are the unrelated attributes?

YES: The related attributes are ProjectID and TaskID. EmployeeName à à ProjectID and EmployeeName à à TaskID

N. What is the deletion anomaly that this relation contains?

If Employee Don no longer has the TaskID B-1 two rows must be deleted, Row 1 and Row 3. The deletion of one fact requires deletion or two rows.

O. How many themes does this relation have?

It would appear that there are at least three themes. 1) Employees and their Tasks 2) Employees and their Phone Numbers and 3) Employees and hours worked on a project.

P. Redesign this relation to eliminate the modification anomalies. How many relations did you use? How many themes does each of your new relations contain?

EMPLOYEE (EmployeeName, Phone)

EMPLOYEE_TASKS (EmployeeName, TaskID)

PROJECT-HOURS (EmployeeName, ProjectID, TotalHours)

Three relations are required, one for each theme. Each relation now carries one theme.

5.74 Answer question 3.37 if you have not already done so. Create a database design for your answer to question 3.37(c). Your design should include a specification of tables, attributes, primary and foreign keys, referential integrity constraints, and referential integrity actions.

Referential integrity constraint:

EMAILMESSAGE.TrueAddress must exist in SENDER.TrueAddress

EMAILMESSAGE.Subject must exist in SUBJECT.Subject

EMAILADDRESS.TrueAddress must exist in SENDER.TrueAddress

DICTIONARYENTRY.TrueAddress must exist in SENDER.TrueAddress