Question

In: Other

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

3. Normal Forms (a) Briefly describe 1NF, 2NF and 3NHF (5 marks) The following statements are in relation to the second norma

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

Solutions

Expert Solution


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_StudentLast_Name
1Dunne
2Lawlor
3O'Byrne
40Simmons
43Whelan

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

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_LecturerLecturer
444Phelan
333Keegan
322McKeever
117Gordon
557Bourke

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

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_StudentLast_Name
1Dunne
2Lawlor
3O'Byrne
40Simmons
43Whelan

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

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_LecturerLecturer
444Phelan
333Keegan
322McKeever
117Gordon
557Bourke

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

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_StudentID_LecturerGrade
144488
233337
332250
40117100
4355767



Related Solutions

Can a table that is already in 1NF be in 2NF and 3NF without making any...
Can a table that is already in 1NF be in 2NF and 3NF without making any changes? Explain and give one specific example of such table.
Select a Database of your own choice and apply the first three normalization processes. (1NF, 2NF...
Select a Database of your own choice and apply the first three normalization processes. (1NF, 2NF & 3NF) • The minimum number of records in the table must be 10 • Make sure you carry the same table from 1NF to 2NF and 3NF, do not use separate tables from scratch for all the three forms. • Brief explanation of the normalization processes must be specified
Normalize the following table into 1NF, 2NF and 3NF. Salesperson name Sales area Customer Number Customer...
Normalize the following table into 1NF, 2NF and 3NF. Salesperson name Sales area Customer Number Customer Name Warehouse Number Warehouse Location Sales Amount 76458 Hariharan East 17658 Asani 3 Govandi 13545 17645 Madhuram 6 Vasai 10600 17623 Sumit 6 Vasai 9700 76567 Dheerendra North 18190 Dhanram 4 Thane 11560 18173 Toshi 4 Thane 2590 18150 Ganesh 1 Kalyan 8090
Part I Define 1NF 2NF 3NF BCNF State the difference between 3NF and BCNF. Give one...
Part I Define 1NF 2NF 3NF BCNF State the difference between 3NF and BCNF. Give one example schema that violates each of the following 1NF 2NF 3NF Your answer should contain three different schemas, one for each case.
Describe the mechanism of action of the drug ZAVESCA Briefly describe the normal cell biology, the...
Describe the mechanism of action of the drug ZAVESCA Briefly describe the normal cell biology, the pathogenic state and how the drug alters the pathogenic state. Come up with an experimental plan by which you can prove where in the cell the drug acts; what cellular functions it modulates, and the mechanism by which it works.
Demonstrate 1NF Normalization Techniques Procedure Bring the following table structure to first normal form and explain...
Demonstrate 1NF Normalization Techniques Procedure Bring the following table structure to first normal form and explain the errors in the current structure. Your submission can be drawn in Word or hand drawn and attached to your submission. Be sure to write out your explanation of the errors in the normal form. Instructor Name Instructor Phone Instructor Email Course Name Course Number Course Description Student1 Name Student1 Phone Student1 Email Student1 GPA Student2 Name Student2 Phone Student2 Email Student2 GPA …...
list the first three normal forms, and describe in detail the criteria required to satisfy each...
list the first three normal forms, and describe in detail the criteria required to satisfy each one of them. Support your analysis with practical examples.
Briefly describe the different forms of transport across a cell membrane ie; how a molecule moves...
Briefly describe the different forms of transport across a cell membrane ie; how a molecule moves across the membrane, and give examples. What is the sodium-potassium pump and why is it important? What is the difference between primary and secondary active transport? What is a secondary messenger and give an example?
Choose three types of communications and describe each briefly. Which forms of communications do you prefer...
Choose three types of communications and describe each briefly. Which forms of communications do you prefer as a consumer? Why? If you owned a small retail store selling flowers (like an FTD florist), how would you communicate with customers and prospective customers using the e-marketing communications on the Australian government web site.
Briefly explain the significance of "Power to abolish all forms of human poverty and all forms...
Briefly explain the significance of "Power to abolish all forms of human poverty and all forms of human life" in JFK's inaugural speech...? does not have to be from an economic standpoint, more from a sociological but some economic insight would be good too
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT