Third 3rd normal form, transitive dependency, Example of the third normal form in database systems
In this tutorial, we will learn about the followings;
- third 3rd normal form in a database system
- transitive dependency
- Example of the third normal form
What is a third normal form?
A database table is in 3rd normal form if the following two conditions are true;
- A database table is in second normal form and in first normal form.
- There is no transitive dependency.
What is a transitive dependency?
A transitive dependency is an indirect dependency.
What is an example of transitive dependency?
- X depends on Y
- Y depends on Z
- So we can say that X depends on Z
Example of a third normal form:
Table 1: Without Normalization
Primary key: Suppose Primary key is on FurnitureID.
There is a problem in this table because CategoryType is transitively depended on furniture ID. We can explain it according to the rule discussed above;
- CategoryType depends on Category
- CategoryID depends on FurnitureID
- So we can say that CategoryType depends on FurnitureID. This kind of dependency is a transitive dependency.
Table 2: Furniture
Table 3: Category
Table 2 and 3 demonstrates table conversion into the 3rd Normal form
Primary key: Primary key is on FurnitureID and CategoryID.
Now the table is in 3rd normal form because all the table is in 2nd normal form and there is no transitive dependency.
|Test Your Understandings|
1.If there exist transitive dependency, then the database is in 3rd normal form?
2. Dependency shown below is the transitive dependency? Yes, No
X depends on Y,
Y depends on Z.
Z depends on Y.