Functional Dependencies

If A and B are sets of attributes in a relation (table) then B is functionally dependent on A (A -> B) if for any two tuples (rows) t1 and t2 in the table if t1(A) = t2(A) then t1(B) = t2(B). A is called the determinant. B is fully functionally dependent on A if B is functionally dependent on A but not on any proper subset of A. A functional dependence is partial if there is some attribute that can be removed from A and yet the dependency still holds.

Here are some axioms and rules that govern functional dependency where A, B, and C are subsets of the attributes (columns) of the relation R.

• Reflexivity: If B is a subset of A, then A -> B
• Augmentation: If A -> B, then A, C -> B, C
• Transitivity: if A -> B and B -> C, then A -> C
• Self-Determintation: A -> A
• Decomposition: If A -> B, C, then A -> B and A -> C
• Union: If A -> B and A -> C, then A -> B, C
• Composition: If A -> B and C -> D then A, C -> B, D

Q. 1: Given the relation R shown below. State whether or not the following functional dependencies are satisfied by the relation.

1. A -> B
2. A -> C
3. AB -> C
4. C -> A
5. BC -> A
6. AC -> B

Q. 2: Show that the inference rule is if AB -> C and C -> A then C -> B is invalid by giving a counter example of relation R having attributes A, B, C, and D where AB -> C and C -> A but not C -> B.

Q. 3: Given the relation R (A, B, C, D) and the functional dependencies: A -> B and BC -> D, determine which of the dependencies can be derived from the axioms and rules listed above:

1. AC -> D
2. B -> D
3. AD -> B

Normalization

The process of normalization is to produce a set of relations (tables) with the following properties:

• minimal number of attributes necessary to support the data requirements
• attributes with a close logical relationship (functional dependency) are found in the same relation
• minimal redundancy, with each attribute represented only once, the exception being attributes that form the foreign keys

Un-Normal Form

An un-normalized table has multiple values for some or all entries. An entry is the intersection of a row and column.

First Normal Form (1NF)

To go from an un-normalized table to a table in First Normal Form (1NF) you must have at most a single value at all intersections of the rows and columns. You can do in two ways:

• Flattening: fill the missing entries of each incomplete row of the table with copies of their corresponding non-repeating attributes
• Decomposition: requires the table to be split into two or more tables

There are several problems relations in 1NF. In the table Project the primary key is composite (Proj-ID, Emp-ID) and these are the anomalies that arise:

1. Insertion Anomaly: We cannot insert information about any new employee that is going to work for a particular department unless that employee is already assigned to a project.
2. Deletion Anomaly: If we delete the row that contains employee Roche we also lose information of the Hourly Rate for employees working in the Tech Support department.
3. Update Anomaly: If an employee moves to another department we have to update every row in the Project table where that employee appears.

Second Normal Form (2NF)

A relation is in Second Normal Form if it is in First Normal Form and no non-prime attribute is partially dependent on any key. For example, in the relation R (A, B, C, D) if AB -> C and AB -> D and A -> D, then this relation is not in 2NF. The solution here is to decompose this relation into two R (A, B, C) and R (A, D).

For the Project table we could split that table into three:

• Project (Proj-ID, Proj-Name, Proj-Mgr-ID)
• Employee (Emp-ID, Emp-Name, Emp-Dept, Emp-Hrly-Rate)
• Hours-Assigned (Proj-ID, Emp-ID, Total-Hours)

• Insertion Anomaly: Consider a situation where we would like to set in advance the rate to be charged by the employees of a new department. We cannot insert that information until there is at least one employee assigned to that department. But the rate that a department charges should be independent of the number of employees.
• Deletion Anomaly: If we delete the row for an employee who happens to be the only employee left in that department then we lose information about the rate the department charges.
• Update Anomaly: If the department hourly rate changes then we will have to change the hourly rate for all employees working for that department.

Third Normal Form (3NF)

A relation is in Third Normal Form if it is already in 2NF and no nonprime attribute is transitively dependent on the key or no nonprime attribute functionally determines any other nonprime attribute. For example, if in a relation R (A, B, C) we have A -> B, and B -> C, and A -> C then the solution is to split the relation to R (A, B) and R (B, C).

The relation Employee is not in 3NF, there is a transitive dependency of a nonprime attribute on the primary key of the relation. The nonprime attribute Emp-Hrly-Rate is transitively dependent on the key through the functional dependency Emp-Dept -> Emp-Hrly-Rate. To transform this relation into a 3NF relation it is necessary to remove any transitive dependency. Two new tables are created:

• Employee (Emp-ID, Emp-Name, Emp-Dept)
• Charges (Emp-Dept, Emp-Hrly-Rate)

The primary key is (Region, Course, Section) for the above table. The table is in 3NF as the two nonprime attributes (Time, Location) determines the other attributes. However, if there is only one instructional facility per city, then we will have Location -> Region. This dependency does not violate the 3NF condition, however if delete the last row of the table we may lose information about the location of that learning center.

Here is another table that is in 3NF.

The Manufacturer relation has two candidate keys (ID, Item-No) and (Name, Item-No) that overlap on the attribute Item-No. The relation Manufacturer has a proble if one of the manufacturers changes its name. The new name has to be updated everywhere it occurs.

Boyce-Codd Normal Form (BCNF)

This is a stricter form of the 3NF condition. A relation is in BCNF if it is already in 3NF and all nonprime attributes must be fully dependent on every key. Or, all prime attributes must be fully dependent on all keys of which they are not part of.

Here is another statement of BCNF: A relation is in BCNF if and only if every determinant is a candidate key.

To convert the Manufacturer table into BCNF one can decompose that table to two tables as follows

• Manufacturer (ID, Name)
• Manufacturer-Part (ID, Item-No, Quantity)

• Manufacturer (ID, Name)
• Manufacturer-Part (Name, Item-No, Quantity)

Lossless or Lossy Decomposition

We have to decompose our tables such that when we join the resultant tables we get back the original relation. Here is a situtaion that we have to be careful about