---

"Good" and "bad" Functional Dependencies

---

• The trivial functional dependency

  • Fact:

    A key of a relation will always functionally determines every attributes in the relation

    Example:

    SSN is key in the relation Employee: +-----------+--------+---------+----------+ | ssn | fname | lname | salary | +-----------+--------+---------+----------+ | 123456789 | John | Smith | 30000.00 | | 333445555 | Frankl | Wong | 40000.00 | | 999887777 | Alicia | Zelaya | 25000.00 | | 987654321 | Jennif | Wallace | 43000.00 | | 666884444 | Ramesh | Narayan | 38000.00 | | 453453453 | Joyce | English | 25000.00 | | 987987987 | Ahmad | Jabbar | 25000.00 | | 888665555 | James | Borg | 55000.00 | +-----------+--------+---------+----------+ Notice that: SSN → fname (If you know SSN, you also know fname) SSN → lname And so on !!!

    ---

    ---

  • We call the functional dependency:

    Key   →   attribute in relation

    a natural functional dependency    or a trivial functional dependency

    ---

    ---

  • Note:

    The functional dependency Key   →   any attribute in relation        is unavoidable !!!

  ---

  ---

  ---

  ---

• "Good" and "bad" functional dependency

  • Good and bad functional dependencies:

    A "good" functional dependency is the natural (or trivial) functional dependency In other words: The functional dependency: key → attribute in relation is a good functional dependency All other kinds of functional dependencies are bad functional dependencies These bad functional dependencies will cause anomalies in relations !!!!

  ---

  ---

  ---

  ---

• Illustration of good and bad functional dependencies with a concrete example
  • Consider the following relation:

    Employee1(SSN, FName, LName, PNumber, PName, Hours) Keys: (SSN, PNumber) Functional dependencies of the relation Employee1: 1. SSN → FName, LName 2. PNUMBER → PName 3. SSN, PNumber → Hours

    ---

    Example: a "good" functional dependency

    SSN, PNumber → Hours is a "good" functional dependency (because SSN,PNumber is a key of Employee1)

    ---

    Example: some "bad" functional dependencies

    SSN → fname, lname is a "bad" functional dependency (SSN, fname, lname) should be taken out and put together in another relation by their own PNumber → PName is a "bad" functional dependency (PNumber, PName) should be taken out and put together in another relation by their own

  ---

  ---

  ---

  ---

• Why a "bad" functional dependencies will cause anomalies
  • Example in 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

    ---

  • Example of a bad functional dependency:

    PNumber → PName (PNumber is not a key)

    Why a bad functional dependency will cause anomalies:

    Since the LHS (= PNumber) of the functional dependency is not a key: You can have multiple tuples in the database with the same PNumber value Example: 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 Since: PNumber → PName then tuples with equal value for PNumber will have the same PName value !!!! The functional dependency has caused duplication of information !!!!

    Therefore:

    A "bad" functional dependency will cause duplication of information !!!!

    And this fact will in turn cause:

    An update anomaly in the relation: When we update the PName from "DBApplet" to "DatabaseApplet", there will be multiple updates in the table.

    (Insert and delete anomalies will follow with similar logic).

  ---

  ---

  ---

  ---

• Why a "good" functional dependency does not cause anomalies
  • Example in 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

    The key is: (SSN, PNumber)

    ---

    ---

  • Important fact:

    The value of (SSN, PNumber) in each tuple is unique That is because (SSN, PNumber) is a key in the relation !!!!

    Therefore:

    There is only one tuple with a specific (SSN, PNumber) value One tuple can never cause a duplication of information !!!! (You need multiple (repeated) tuples to get duplication of information !!!)

  ---

  ---

  ---

  ---

• Removing anomalies from relations

  • The $64,000 question:

    What can we do to remove anomalies from "bad" relations ????

  • Answer:

    We will break up the relation into multiple relations Each (smaller) relation will only have "good" functional dependencies !!!

  ---

  ---

  ---

  ---

  ---

  ---

• Decomposing a relation

  • A decomposition of a relation R:

    Decomposition of relation R = a collection of relations R1, R2, ..., Rn such that: Every attribute of R appears at least once in R1, R2, ..., Rn

    Example of decomposition:

    Original relation: Employee1( SSN, FName, LName, PNumber, PName, Hours ) A decomposition of Employee1: R1(SSN, PName, Hours) R2(PNumber, FName, LName) Another decomposition of Employee1: R3(SSN, FName, LName) R4(PNumber, PName) R5(SSN, PNumber, Hours)

  ---

  ---

  ---

  ---

• Bad and good decompositions
  • Example of a bad decomposition:

    Employee1(SSN, FName, LName, PNumber, PName, Hours) Decomposed into: R1(SSN, PName, Hours) R2(PNumber, FName, LName) what does this relation mean ???

    ---

    ---

  • Example of a good decomposition:

    Employee1(SSN, FName, LName, PNumber, PName, Hours) Decomposed into: R3(SSN, FName, LName) -- Employee info R4(PNumber, PName) -- Project info R5(SSN, PNumber, Hours) -- Info on who works on which project

    ---

    ---

  • $64,000 question:

    How do you decide on the decomposition

    Answer:

    The normal forms (introduce by E.F. Codd) are the guiding criterias for better relations

  ---

  ---

  ---

  ---

• Pre-requisite to studying "decomposition"
  • Comment:

    Before we can study: on the question: How to decompose a relation into better relations        We must first: Understand the theory on functional dependencies        The material is quite mathematical ....