Home‎ > ‎CIS 54‎ > ‎Lab 4 - FDs‎ > ‎

Lab 4 Answers

Review Questions

2.24: Area = Length x Width 

This equation describes the FD:

(Length, Width) -> Area

The length and width functionally determine the area, or the area functionally depends on the length and the width. 

2.25: Explain the meaning of A -> (B, C). 

A functionally determines B and C so it is also true that A -> B and A -> C. if A functionally determines both B and C together then it does so with B and C alone. For example if:

if: 
CustomerId -> (CustomerName, CustomerEmail)

Then:
CustomerId -> (CustomerEmail)
CustomerId -> (CustomerName)

2.26: Explain the meaning of (D, E) -> F

D and E functionally determine F or F functionally depends on D and E. However: 

  D -> F and E -> F are not necessarily true.

If (D, E) -> F is minimal then then neither of the above can be true.  For example:

If:
(FirstName, LastName) -> Email 

Then: 
wrong: FirstName -> Email
wrong: LastName -> Email

2.27. Explain the differences between 2.25 and 2.26. 

In order for an FD to hold all of the left side attributes taken together determine each of the right side attributes alone (and therefore together). 

2.28. Define primary key in terms of functional dependencies. 

The primary key of a relation are those attributes that functionally determine all of the other attributes in the relation. 

Chapter Exercises 

2.40: What is a multivalued dependency and how is it resolved in 4NF? 

A multivalued dependency is where an attribute determines a set of values of another attribute. For example a database that contained the classes I'm teaching would have the MVD: 

InstructorName ->-> SectionNumber

Where my name would determine CIS-54, CIS-77 and CIS-192 this semester. Multivalued dependencies are resolved in 4NF by placing them in their own table. 

A:  An example of the STUDENT(StudentNumber, StudentName, SibilingName, Major)  

1, Amy Atkins, Ally Atkins, CS
2, Amy Atkins, Abe Atkins, CS
3, Bob Barker, Bill Barker, CS
4, Bob Barker, Betty Barker, CS
5, Bob Barker, Bing Barker, CS

B: List candidate keys:

(StudentNumber, SiblingName)  

C: State FDs in the relation

StudentNumber -> (StudentName, Major)
(StudentNumber, SiblingName) -> (StudentName, Major)
StudentNumber ->-> SiblingName

D: Why isn't this a well formed relation?

The attributes StudentName and Major are functionally dependent only on a part of the composite key. There is an MVD in this relation.

E: Discuss 4NF. 

4NF solves anomalies that arise in BCNF when relations contain multivalued dependencies. The MVDs are resolved by placing them into their own well-formed relation. 

F: Divide the tables into a set of well formed relations.

STUDENT(StudentNumber, StudentName, Major)
SIBLING(StudentNumber, SiblingName

Case Questions


A. What are the FDs? 

    3. (ProjectID, EmployeeName) -> EmployeeSalary  true, because below is true.  
    4. EmployeeName -> EmployeeSalary

B. What is the PK of project? 

(ProjectID, EmployeeName) is the primary key of project. It cannot be ProjectID because ProjectID is repeated in the data.   

C. Are all of the nonkey attributes in project dependent on the PK? 

Yes. 

D. In what normal form is project? 

The table is a relation (1NF) but it is not in 2NF. To be in 2NF answer the question, "are all of the nonkey attributes determined by the entire primary key?" The answer is no because EmployeeSalary is not determined by ProjectID. 

E. Describe two modification anomalies. 
  1. Updating the salary of an employee must be done multiple times. 
  2. An employee that is assigned to no project will create NULLs.
  3. Removing a project may disappear an employee. 
F. Is ProjectID a determinant? 

No, because it does not appear on the left side of any FD. 

G. Is EmployeeName a determinant? 

Yes, because it's on the left side of FD #4

H. Is (ProjectID, EmployeeName) a determinant? 

Yes, because it's on the left side of FD #3. 

I. Is EmployeeSalary a determinant? 

No, it does not appear on the left side of any FD. 

J. Does the relation contain a transitive dependency. If so what is it? 

A transitive dependency is one where:

A -> B 
B -> C

Therefore: 

A->C 

No. There is no dependency of the above form. 

K. Redesign the relation to eliminate modification anomalies. 

First let's construct a well formed table for Employee based on EmployeeName -> Salary

EMPLOYEE ( EmployeeName, Salary ) 

Next we must recognize the MVD: 

ProjectID ->-> EmployeeName

PROJECT ( ProjectID, EmployeeName )


Comments