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

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.
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
Explain the difference between 2NF and 3 NF relations
Explain the difference between 2NF and 3 NF relations
Briefly describe the specific characteristics of the normal curve. Conceptually, where does it come from? What...
Briefly describe the specific characteristics of the normal curve. Conceptually, where does it come from? What human behavior, trait, or characteristic can you think of that is normally distributed? Can you think of one that isn't? Remember you cannot use categorical (nominal or ordinal) level measurements for this example.
There are three forms to the Efficient Market Hypothesis. Please describe each of the forms and...
There are three forms to the Efficient Market Hypothesis. Please describe each of the forms and what they assume. Based upon your reading of the text and your own knowledge and research, do you believe there is any element of truth to any of these hypothesis? In your opinion, does this hypothesis add to our general understanding of how markets work, or is it too outdated to be of any value? Please provide some support for your opinion.
Explain at least the first three normal forms of the normalization process.
Explain at least the first three normal forms of the normalization process.
A population forms a normal distribution with a mean of µ = 120 and a standard...
A population forms a normal distribution with a mean of µ = 120 and a standard deviation of σ = 14. If two scores were selected from this population, how much distance would you expect, on average, between the second score and the population mean? A sample of n = 20 scores from this population has a mean of M = 90, do you think this sample is relative typical or extreme to the population? Explain.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT