Question

3. Normal Forms 

(a) Briefly describe 1NF, 2NF and 3NHF 

(b) The following statements are in relation to the second normal form (2NF). Which ones are true? (

1) The table also has to be in INF 

2) The table also has to be in 3NF 

3) The table is only allowed to have atomic values in its cells 

4) The primary key is not allowed to consist of multiple attributes. 

5) Every non-key attribute depends on the complete primary key 

6) Every non-key attribute is only allowed to be dependent on the primary key but not on another non-key attribute 

7) The table is not allowed to have a foreign key attribute 

8) If a table is in INF and does not have a multi-part primary keys, then i is automatically also in 2NF. 

9) If a table is in 2NF and does not have a multi-part primary key, then it is automatically also in 3NF 1

0) The primary key is not allowed to be NULL in any row of a table

 (c) Given the following table:

(i) Briefly describe which normal form the table in 3(c) is in? 

(ii) Convert the table in 3(c) nto Third Normal Form (3NF) and justify your decisions

Answer 1

Question a :

Normalization :

• Normalization is a process of defragmenting or decomposing large and complex table into simple and smaller form.

• Normalization helps to reduce redundant data,Redundant means duplicate data.

• Normalization helps to avoid inconsistent data.Inconsistent means incorrect data.

• After normalization related data will be stored in the related table.

First Normal Form (1NF) :

• First Normal Form (1NF) says table all the columns should be automic in nature.

• In 1nf duplicate columns and multivalued columns are not allowed.

• First NF says all rows should have equal number of columns.

Second Normal Form (2NF) :

• Second Normal Form (2NF) says table should be in the first normal form.

• All non key columns in the table should depends upon primary key column.

• In 2NF partially dependency are not allowed.

Third Normal Form (3NF) :

• Third Normal Form says table should be in the second normal form.

• All non key columns in the table should depends upon non key column in the table.

• In 3NF transitive dependency are not allowed.

********************************

Question b :

• 1) The table also has to be in 1NF.

• 5) Every non-key attribute depends on the complete primary key.

• 6) Every non-key attribute is only allowed to be dependent on the primary key,but not on another non-key attribute.

• 10) The primary key is not allowed to be null in any row of the table.

*************************************

Question c) i:

• Table is in the First Normal Form (1NF) because all columns are automic in nature.

• No duplicate columns and multivalued columns exists in the table.

********************************

Question c) ii :

Below is the normalization process.

First Normal Form (1NF) :Table is in the 1NF because all columns are in automic in nature.No duplicate columns and multivalued columns exists in the table.

Second Normal Form (2NF) :

• Here above table needs to normalize into second normal form to remove partial dependencies.

• Here need to identify new tables like

• Student :some of the columns like Last_Name are depends upon ID_Student hence all the student details needs to store in the table student.

• Lecture :Here some of the columns like Lecturer are depends upon ID_Lecturer.

Below are tables in 2NF.

1.Table Name :Student

Description :This table stores student id and last name.

Schema :Student (ID_Student,Last_Name)

FD :ID_Student Last_Name

Below is the table data

ID_Student	Last_Name
1	Dunne
2	Lawlor
3	O'Byrne
40	Simmons
43	Whelan

***************************

2.Table Name :Lecturer

Description :This table stores lecturer details such as ID_Lecturer and Lecturer.

Schema :Lecturer (ID_Lecturer,Lecturer)

FD :ID_Lecturer Lecturer

ID_Lecturer	Lecturer
444	Phelan
333	Keegan
322	McKeever
117	Gordon
557	Bourke

***********************

Third Normal Form (3NF) :

Here above table needs to normalize into 3NF to remove transitive dependencies.Also primary key of one table is used as foreign key in another table.

Below are tables in 3NF.

1.Table Name :Student

Description :This table stores student id and last name.

Schema :Student (ID_Student,Last_Name)

FD :ID_Student Last_Name

Below is the table data

ID_Student	Last_Name
1	Dunne
2	Lawlor
3	O'Byrne
40	Simmons
43	Whelan

***************************

2.Table Name :Lecturer

Description :This table stores lecturer details such as ID_Lecturer and Lecturer.

Schema :Lecturer (ID_Lecturer,Lecturer)

FD :ID_Lecturer Lecturer

Below is the table data

ID_Lecturer	Lecturer
444	Phelan
333	Keegan
322	McKeever
117	Gordon
557	Bourke

****************************

3.Table Name :StudentGrade

Description :This table stores student along with lecturer and grade

Schema :StudentGrade (ID_Student,ID_Lecturer,Grade)

FD :ID_Student,ID_Lecturer Grade

Below is the table data

ID_Student	ID_Lecturer	Grade
1	444	88
2	333	37
3	322	50
40	117	100
43	557	67