CS457 Syllabus & Progress

## "Good" and "bad" Functional Dependencies

• "Good" and "bad" functional dependencies...

• 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 | +-----------+--------+---------+----------+ SSN functionally determines every attribute in Employee !!! ```

• We call the functional dependency:

 Key   →   attribute in relation

a natural functional dependency or a trivial functional dependency

• What make a dependency good and bad:

• A "good" functional dependency is a natural 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 "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)

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

• 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 ```

every tuple with identical value for PNumber will have the same PName value !!!!

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 can be constructed as well).

• Another example of a bad Functional Dependency

• Let us examine the relation Employee1 again:

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

• The key of the Employee1 relation is:

 (SSN, PNumber)

• Sample content:

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 following functional dependency is not natural for this relation:

 ``` SSN → fname, lname ^^^ SSN is NOT a key of the relation !!! ```

This functional dependency cause the duplication of information:

• Since the LHS (= SSN) of the functional dependency is not a key:

 You can have multiple tuples in the database with the same SSN value

• Since SSN fname , these tuples must have the same fname value !!!

 Duplication of information is guaranteed !!!

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

The information that employee 111-11-1111 is Joghn is stored multiple times !!!!

• What are we gonna do about it ?????

• The \$64,000 question:

 What can we do about it ????

• 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 is

 A collection of relations R1, R2, ..., Rn, such that: every attribute of R appears in R1, R2, ..., Rn at least once

In other words, decomposing of a relation R is

 Breaking a relation R up into multiple relations R1, R2, ..., Rn but making sure that you include every attribute of R least once in one of the relations R1, R2, ..., Rn

• Clearly, you can break a relation R up in many ways and some ways aren't useful...

• Bad and good decompositions

• A bad decomposition is for example:

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

• You must decompose with a goal

That goal is to make "better relations"

• A good decomposition is for example:

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) Decomposed into: R1(SSN, PNumber, Hours) - works_on information R2(SSN, FName, LName) - employee information R3(PNumber, PName) - project information ```

• The \$64,000 question is:

 How do you decide on the decomposition

Answer:

 The normal forms will be the guiding criterias for better relations When a relation R violates the guiding criteria of a normal form , we must decompose the relation R into R1, R2, .., Rn in such a way that R1, R2, .., Rn comply with the guiding criteria of the normal form

• Before we can present a treatise on how to decompose relations, we must get through the theory on functional dependency...

• The material is quite mathematical ....