A relation R is in Third Normal Form (3NF) if and only if the following conditions are satisfied simultaneously:
1. R is already in 2NF
2. No nonprime attribute is transitively dependent on the key.
Another way of expressing the conditions for Third Normal Form is as follows:
1. R is already in 2NF
2. No nonprime attribute functionally determines any other nonprime attribute.
These two sets of conditions are equivalent.
As these two definitions of 3NF imply, the objective of transforming relations into 3NF is to remove all transitive dependencies. So, first we are going to explain the concept of transitive dependency.
We’ll be covering the following topics in this tutorial:
Transitive Dependencies
Assume that A, Band C are the set of attributes of a relation R and following functional dependencies are satisfied simultaneously: A –>B, B -/->A (B not functionally depends A) B–>A, A–>C and C-/->A (C not functionally depends A). Observe that C –> B is neither prohibited nor required. If all these conditions are true, we will say that attribute C is transitively dependent on attribute A. It should be clear that these functional dependencies determine the conditions for having a transitive dependency of attribute C on A. If any of these functional dependencies are not satisfied then attribute C is not transitively dependent on attribute A.
The figure shown below summarizes these conditions. In this diagram the arrows are equivalent to the symbol “-” that we use for denoting functional dependencies.
Notice that the functional dependency A –> C may not be explicitly indicated but it holds true due to the Transitivity axiom. The requirements that B -/->A (B not functionally depends A) and C -/-> A (C not functionally depends A) are necessary to ensure that attributes A and B are nonprime attributes.
Rule to transform a relation into Third Normal Form
To transform a 2NF relation into a 3NF we will follow the approach indicated by figure. In this figure assume that any FD not implicitly indicated does not hold. An asterisk indicates the key attribute and the arrows denote functional dependencies. The dashed line indicates that the FD A –> C may not be explicitly given but it is always present because it can be derived using the inference axioms.
The above rule, simply states that if B depends on A and C depends on B, then it can be decomposed to (A, B) and (B, C).
Conversion of STUDENT_SYSTEM_CHARGE (Rollno, Name, System_Used,Hourly_Rate) to Third Normal Form:The relation STUDENT_SYSTEM_CHARGE is not in 3NF because there is a transitive dependency of a non prime attribute on the primary key of the relation. In this case, the Non prime attribute Hourly_Rate is transitively dependent on the primary key Rollno through the functional dependency System_Used –> Hour1y_Rate. Notice that all other conditions required by the definition are met by this set of FDs. In particular, we have that Rollno a System_Used and System_Used a Hourly_Rate.
To transform this relation into a 3NF relation, it is necessary to remove any transitive dependency of a nonprime attribute on the key. According to the figure it is necessary to create two new relations.
The scheme of the first relation is:
STUDENT_SYSTEM (Rollno, Name, System_Used).The scheme of the second relation is:
CHARGES (System_Used, Hourly_Rate).Observe that in the second relation, the System _Used attribute has been made the primary key of the relation as required by the diagram.
Using the second definition of 3NF, we can determine that the STUDENT_SYSTEM_CHARGE relation is not in 3NF by noticing that System_Usedà Hourly_Rate, here both attributes are nonprime. To transform this relation to 3NF we use the same general procedure of above figure.
Removal of anomalies of Second Normal form
Insertion anomalies
In the revised structure of STUDENT_SYSTEM and CHARGES, it is possible to insert in advance the rate to be charged from the students for a particular system (in CHARGES database). It means that we can insert that hourly rate of laptop in CHARGES table independent from whether it is used by any student or not.
Update anomalies
If the Hourly_Rate for a particular system changes, we need only to change a single record in CHARGES database for that particular system.
Delete anomalies
We delete the tuple of a student who happens to be the only student left which is working on a particular system without losing the information about the rate that we charge for that particular system (We can get is information from CHARGES database).
Data Anomalies in 3NF Relations
The 3NF helped us to get rid of the data anomalies caused either by transitive dependencies on the PK or by dependencies of a nonprime attribute on another nonprime attribute. However, relations in 3NF are still susceptible to data anomalies particularly when the relations have two overlapping candidate keys or when a nonprime attribute functionally determines a prime attribute. The following example will illustrate this.
Example
Consider the Manufacturer relation shown below where each manufacturer has a unique ID and name. Manufacturers produce items (identified by their unique item numbers) in the amounts indicated. Manufacturers may produce more than one item and different manufacturers may produce the same items.
Manufacturer ( Id_No, Name, Item No, Quantity)
This Manufacturer relation has two candidate keys: (ID, Item_No) and (Name, Item_No) that overlap on the attribute Item_No. The relation is in 3NF because there is only one nonprime attribute and therefore it is impossible that this attribute can determine another nonprime attribute.
The relation Manufacturer is susceptible to update anomalies. Consider for example the case in which one of the manufacturers changes its name. If the value of this attribute is not changed in all of the corresponding Tuples there is the possibility of having an inconsistent database.
We can take another case of Supplier-Part table having following attributes:
(Sno, Sname, Pno, Qty)
Here, let us suppose that Sname (supplier name) is unique for each Sno (supplier number) as shown below:
This Supplier-Part relation has two candidate keys: (S’10, Pno) and (Sname, Pno) that overlap on the attribute Pno. The relation is in 3NF because there is only one non prime attribute and therefore it is impossible that this attribute can determine another non prime attribute.
The relation is susceptible to update anomalies. Consider for example, the case in which one of the supplier changes its name, then we have to make multiple changes which is equal to the number part supplied by that particular supplier. This relation is in second and third normal form because it has only one non-key attribute Qty which is FFD on Primary key. Sname cannot be called as non key attribute because it can participate in the primary key in case of (Sname, Pno) primary key. This relation is normalized with the help of Boyce-Codd normal form.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular