---

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 see the same attribute value in X then you must see the same attribute value in Y also.

  ---

  ---

  ---

  ---

• Example of Functional Dependencies

  • Consider the following relation:

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

    that stores information about the employees AND the projects that an employee works on.

    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 !!!