What is Functional Dependency?

Functional dependency is the constraint between two set of attributes where if we have one attribute (Key Attribute) we can be able to determine the other attributes. This constraint describes the relation between the attributes. If the first set X of attribute can be used to determine the other attributes, then the other set Y attribute is said to be functional dependent on X. if Y is a subset of X, then there exist a trivial dependency.

In the table above, we can see that Trainer ID can be used as a key attribute to find all the other attributes. So our first functional dependency is shown below.

Trainer_ID à Trainer_Name, Room_No,Room_Seats,Class_Session,Class_Name,Class_Day,School

But since we can see that Trainer_ID and Trainer_Name can be merged in one table to eliminate anomalies and the remaining attributes can be identified by use of Room_NO as a key attribute, then we can obtain the following two functional dependencies.

Trainer_ID à Trainer_Name

In the figure below, Room_NO is used as a key attribute where the other attributes a functional dependent on the Room_NO key attribute. We can obtain the following functional dependency diagram.

Room_NO à Room_Seats, Class_Session, Class_Name , Class_Day, School

Up to this level, there still exist anomalies in the table data. We can obtain room table data where Room_NO is functional dependent on Room_Seats

Room_NO à Room_Seats

At this level the anomalies are almost eliminated, the remaining task is to normalise the class table. Class_Name can be used to determine the other non- key attributes although we need to make a class session table by introducing a unique key identifier for the class session identifications.

Therefore we have two tables, class school table and class sessions table where Class_Name is a foreign key in a class school table. Below are the functional diagrams for the two mentioned tables:

Class_Name à School

In the below Diagram, class_Name is a primary Key in Class_School table a foreign key. The diagram shows Class_Session functional dependency diagram.

Class_Name -à Class_Session, Class_Day

In Summary, these are identified dependency data tables from the above diagram.

General table data

Trainer_ID à Trainer_Name, Room_No,Room_Seats,Class_Session,Class_Name,Class_Day,School

Trainer Table

Trainer_ID à Trainer_Name

Room table

Room_NO à Room_Seats

Class school table

Class_Name à School

Class session table

Class_Name -à Class_Session, Class_Day

The table data above is normalized and all anomalies are eliminated.

For our table to be in 1NF, there must not be repeating data groups. To ensure our table is in 1NF, we will identify the most considerable key attributes that can form a group of attributes with no cases of repeated data. Then we later eliminate the data duplicates as shown in the following tables.

Normalization

Table 1: Trainer table

In the table below, we can see that the two attributes Trainer_ID and Trainer_Name are forming one table data by the name Trainer table where Trainer_ID is the key attribute and can be used to identify the non-key attribute Trainer_Name. Although our table has data duplicates, we can use the key attribute to identify non key attribute.

 Remaining table:

Table 2: Room table

After the trainer table extraction, our remaining table data has a lot of anomalies brought in by the repeating groups. This repeating groups makes us to identify another unique data group which has no anomalies. The data group is made up of Room_No and Room_Seats which is named to be Room table as shown below. At least now we can use Room_NO attribute as a key identifier to obtain the Room_Seats Capacity.

Remaining table: 

Table 3: class session table

Up to this level, our tables are not clear and we can see that the repeating groups still exist. This leaves us with another task of trying to solve the anomaly between the class school data and the class session data. But we already know that any class session must be held In a class and every school contains the classes. So, we will have Class_School table data and a Class_Session table data where there exists no repeating groups.

Below is Class_Session table data with a class_Name as a key but as foreign key as well.

Final table: Class_School table

The final table is shown below.

Class school table identifies where each class belong to which school. The table data qualifies to be in 1NF.

For our tables to be in 2NF, there must not exist partial dependencies and the table data should be in 1NF. I.e. every non-key attribute should be dependent on the key attribute.

Since we have already identified our first tables which are in 1NF, then we need to eliminate partial dependences for our tables to be in 2NF. Below are the steps for normalising our tables:

Table 1: Trainer table

 
The table has repeated data rows which is considered as duplicates since the Trainer_ID is the same and the Trainer_Name is the same as well. Below is a clear picture of the sample of existing duplicates which brings in partial dependency. For instance, Trainer_ID 102 has three occurrences and identifies the same person by the name George Edwards. Since retrieving the data of ID 102 can be tedious, then we merge the three rows into one data row.

1NF

When the data rows are merged into non duplicate row, the following data table is obtained and our table qualifies to be in 2NF since the partial dependencies no longer exist.

Table 2: Room table

The table need to be eliminated data duplicates so as it can be in 2NF.

Room table has existing data duplicates. This data duplicates need to be eliminated and be made into one data row to avoid partial dependency. For instance, a good case of duplicate is the data identified by the key ID Room_NO 53 which has two occurrences and identifies only one data row. If these data duplicates are eliminated, we merge then into a single data row. Since our table is in 1NF and has no partial dependency, the table qualifies to be in 2NF.

Table 3: Class session table

For our class session table data to be in 2NF, we must avoid partial dependences. But since our table data seems to contain duplicates which is false, we need to introduce an auto increment key identifier so as our table data has every data identified to its class. Then we must uphold the Class_Name into this data table as a foreign key so as every data row is unique as shown below. Our table now qualifies to be in 2NF.

Final table: class school table

In this table data, we only need to eliminate the data row duplicates so as we obtain unique data rows. Class_Name will be the key identifier of this table data and that moment our table qualifies to be in 2NF as shown below:

For our tables to be in 3NF, the tables has to be in 2NF and non-prime attribute has to be dependent on the key attributes.

First table: Trainer table

The table above is in 3NF since it is in 2NF and every non key attribute is dependent on the key attribute. Since every data row is unique, then our table data is in 3NF state.

Table 2: Room table

The table is in 2NF and non-key attribute depends on key attribute. There is no case of redundant data rows. Therefore the table qualifies to be in 3NF.

Table 3: Class session table

Our table is already in 2NF and every non key attribute depends on the key attribute class session ID, then the table is in 3NF since every data row is uniquely identified.

Table 4: class school table

The table above has no case of partial dependency and non-key attribute is dependent on key attribute and there exist data row unique identification. The table is 2NF and therefore qualifies to be in 3NF.

The figure below ERD with relations and cardinalities:

ERD

Reletional schema

References 

Watt, A. (n.d.). Database Design:Chapter 11 Functional Dependencies. Retrieved April 20, 2017, from https://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/

Beynon-Davies, Paul (2004). Database Systems. Basingstoke, UK: Palgrave: Houndmills. ISBN 1403916012.