Functional Dependency And Normalization In Database Design

What is Functional Dependency?

Trainer_ID Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Trainer_Name	Room_No	Room_Seats	Class_Session Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Class_Name	Class_Day	School
101	Alana Fredrick	54A	36	9:00	ITC114	Mon	Computing
102	George Edwards	22	28	9:00	ITC333	Mon	Computing
103	Lilly Nelly	59	38	12:30	BUS350	Tues	Business
104	Eddy Brown	53	30	12:30	ITC114	Thurs	Computing
102	George Edwards	59	38	4:00	ITC504	Fri	Computing
103	Lilly Nelly	18	24	12:30	BUS360	Wedn	Business
102	George Edwards	53	30	9:00	ITC201	Wedn	Computing
105	Sam Wang	22	28	4:00	ITC556	Tues	Computing
106	Yifeng Chong	58	38	9:00	ITC556	Thurs	Computing

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.

Trainer_ID	Trainer_Name	Room_No	Room_Seats	Class_Session	Class_Name	Class_Day	School
101	Alana Fredrick	54A	36	9:00	ITC114	Mon	Computing
102	George Edwards	22	28	9:00	ITC333	Mon	Computing
103	Lilly Nelly	59	38	12:30	BUS350	Tues	Business
104	Eddy Brown	53	30	12:30	ITC114	Thurs	Computing
102	George Edwards	59	38	4:00	ITC504	Fri	Computing
103	Lilly Nelly	18	24	12:30	BUS360	Wedn	Business
102	George Edwards	53	30	9:00	ITC201	Wedn	Computing
105	Sam Wang	22	28	4:00	ITC556	Tues	Computing
106	Yifeng Chong	58	38	9:00	ITC556	Thurs	Computing

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.

Trainer_ID	Trainer_Name
101	Alana Fredrick
102	George Edwards
103	Lilly Nelly
104	Eddy Brown
102	George Edwards
103	Lilly Nelly
102	George Edwards
105	Sam Wang
106	Yifeng Chong

 Remaining table:

Room_No	Room_Seats	Class_Session	Class_Name	Class_Day	School
54A	36	9:00	ITC114	Mon	Computing
22	28	9:00	ITC333	Mon	Computing
59	38	12:30	BUS350	Tues	Business
53	30	12:30	ITC114	Thurs	Computing
59	38	4:00	ITC504	Fri	Computing
18	24	12:30	BUS360	Wedn	Business
53	30	9:00	ITC201	Wedn	Computing
22	28	4:00	ITC556	Tues	Computing
58	38	9:00	ITC556	Thurs	Computing

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.

Room_No	Room_Seats
54A	36
22	28
59	38
53	30
59	38
18	24
53	30
22	28
58	38

Remaining table: 

Class_Session	Class_Name	Class_Day	School
9:00	ITC114	Mon	Computing
9:00	ITC333	Mon	Computing
12:30	BUS350	Tues	Business
12:30	ITC114	Thurs	Computing
4:00	ITC504	Fri	Computing
12:30	BUS360	Wedn	Business
9:00	ITC201	Wedn	Computing
4:00	ITC556	Tues	Computing
9:00	ITC556	Thurs	Computing

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.

Class_Name	Class_Session	Class_Day
ITC114	9:00	Mon
ITC333	9:00	Mon
BUS350	12:30	Tues
ITC114	12:30	Thurs
ITC504	4:00	Fri
BUS360	12:30	Wedn
ITC201	9:00	Wedn
ITC556	4:00	Tues
ITC556	9:00	Thurs

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.

Class_Name	School
ITC114	Computing
ITC333	Computing
BUS350	Business
ITC114	Computing
ITC504	Computing
BUS360	Business
ITC201	Computing
ITC556	Computing
ITC556	Computing

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

Trainer_ID	Trainer_Name
101	Alana Fredrick
102	George Edwards
103	Lilly Nelly
104	Eddy Brown
102	George Edwards
103	Lilly Nelly
102	George Edwards
105	Sam Wang
106	Yifeng Chong

 
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

Trainer_ID	Trainer_Name
102	George Edwards
102	George Edwards
102	George Edwards

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.

Trainer_ID	Trainer_Name
101	Alana Fredrick
102	George Edwards
103	Lilly Nelly
104	Eddy Brown
105	Sam Wang
106	Yifeng Chong

Table 2: Room table

Room_No	Room_Seats
54A	36
22	28
59	38
53	30
59	38
18	24
53	30
22	28
58	38

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.

Room_No	Room_Seats
54A	36
22	28
59	38
53	30
18	24
58	38

Table 3: Class session table

Class_Session	Class_Day
9:00	Mon
9:00	Mon
12:30	Tues
12:30	Thurs
4:00	Fri
12:30	Wedn
9:00	Wedn
4:00	Tues
9:00	Thurs

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.

classSessionID	Class_Name	Class_Session	Class_Day
1	ITC114	9:00	Mon
2	ITC333	9:00	Mon
3	BUS350	12:30	Tues
4	ITC114	12:30	Thurs
5	ITC504	4:00	Fri
6	BUS360	12:30	Wedn
7	ITC201	9:00	Wedn
8	ITC556	4:00	Tues
9	ITC556	9:00	Thurs

Final table: class school table

Class_Name	School
ITC114	Computing
ITC333	Computing
BUS350	Business
ITC114	Computing
ITC504	Computing
BUS360	Business
ITC201	Computing
ITC556	Computing
ITC556	Computing

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:

Class_Name	School
ITC114	Computing
ITC333	Computing
BUS350	Business
ITC504	Computing
BUS360	Business
ITC201	Computing
ITC556	Computing

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

Trainer_ID	Trainer_Name
101	Alana Fredrick
102	George Edwards
103	Lilly Nelly
104	Eddy Brown
105	Sam Wang
106	Yifeng Chong

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

Room_No	Room_Seats
54A	36
22	28
59	38
53	30
18	24
58	38

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

classSessionID	Class_Session	Class_Day
1	9:00	Mon
2	9:00	Mon
3	12:30	Tues
4	12:30	Thurs
5	4:00	Fri
6	12:30	Wedn
7	9:00	Wedn
8	4:00	Tues
9	9:00	Thurs

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

Class_Name	School
ITC114	Computing
ITC333	Computing
BUS350	Business
ITC504	Computing
BUS360	Business
ITC201	Computing
ITC556	Computing

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.