CS457 Syllabus & Progress

## Intro to Functional Dependencies

• Cause of the anomalies

• The cause of the anomalies in relations is:

 Duplication of information due to dependencies between different attributes in the relation !!!!

• The normal forms 2NF, 3NF and BCNF are based on the concept of:

 Functional dependency

We will spend a few lectures studying functional dependencies and their applications before discussion the 2NF and 3NF

• Functional Dependencies

• Let X and Y be 2 set of attributes in a relation R

• We say that Y is functionally dependent on X (or X functionally determines Y , notation: X Y) iff (if and only if):

 ``` X → Y <=> for any two tuples t1 and t2 of the relation R: if t1[X] = t2[X] then: t1[Y] = t2[Y] (i.e., whenever you have same values in X then you must have same values in Y as well) ```

• Example: when you must see if B → D

• Example: A case where the attribute D is not functionally dependent on an attribute B:

The definition of functional dependency is not satisfied because:

• Tuple 1 and tuple 2 have same values for the B attribute

But:

 Tuple 1 and tuple 2 have different values for the attribute D !!!

When this happens, we know for sure that D is not functionally dependent on B

Summary:

 If X → Y, then the attribute values in the Y-attributes in both tuples (1 and 2) must be equal whenever the X-attributes have equal values

• Example of Functional Dependencies

• Consider the following relation: that represent information about the employees AND the projects that an employee they work on.

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) ```

• The key of this relation is: (SSN, PNumber)

• Sample content of the Employee1 relation:

SSN FName LName PNumber PName Hours
111-11-1111 John Smith pj1 DBApplet 20
111-11-1111 John Smith pj2 WebServer 10
111-22-3333 Jane Doe pj1 DBApplet 5

• Some functional dependencies in the Employee1 relation:

 SSN → fname, lname PNumber → PName SSN, PNumber → Hours But also: SSN, PNumber → fname, lname !!! And also: SSN, PNumber → PName !!!

Explanation:

 ``` SSN, PNumber → fname, lname is valid because: whenever we have the same values for (SSN, PNumber), we will also have the same value for (fname, lname) (The attribute SSN is sufficient for the functional dependency, adding PNumber to SSN will not break the functional dependency) ```

• My "everyday English" definition of Functional Dependency

• This is my own "common sense" unscientific definition of Functinal Dependency:

• The attribute X functionally determines an attribute Y   iff:

 If you tell me the value of attribute X, then I can know the value of the attribute Y also !!!

• Example:

• SSN → FName, LName

 If you tell me someone's SSN, then I can know the FName and LName also !!!