Normalization Exercises Hint: adding new entities may be required to complete th

Question

Normalization Exercises

Hint: adding new entities may be required to complete the following normalization exercises.

Exercise 1
Normalize the table below Excercise 2 into First Normal Form (1NF) and explain why. Do not normalize beyond 1NF.

Exercise 2
Normalize the following table into Second Normal Form (2NF) and explain why. Do not normalize beyond 2NF.

Exercise 3

Transform the following Entity (table) into Third Normal Form (3NF) and explain why.

StudentID, StudentName, DOB, Street, City, State, Zip

Student Age Subjects Dan 12 Biology, Math Tim 13 Math Karen 11 Math

Explanation / Answer

Dear Student,

As per the requirement submitted above, kindly find the below solution.

Normalization: Normalization is a process of decomposing of larger and complex data into simple and smaller tables. Data before Normalization can be in the form of a table or it might row data that is data in the text file.

Some of the advantages of Normalization are listed below

Advantages of Normalization:

Exercise 1 :

Answer :

First Normal Form (1NF) : In First Normal Form (1NF) all the columns in the table should be atomic in nature that means each column should contain one value & each row should contain the same number of columns. Repeating groups or duplicate columns are not allowed in first normal form.

Table given in teh excercise 2 has subject column fow which row one is having multiple values so need to normalize that table into first normal form ,below is the schema for the table

Table Name : Student

Schema : (Student , Age , Subject)

Exercise 2 :

Second Normal Form (2NF) :Second Normal Form (2NF) should fullfill requirements of first normal form (1NF) and all the columns in the table should functionally depend upon primary key column in the table. Partially dependencies are not allowed in second normal form

As above table need to normalize into second normal form , also subject is not depends upon student hance here will normalize above table into two tables as Student and Subject table and will add the two columns as StudentID and SubjectID in respective tables.

Table Name : Student

Schema : (Student_ID - Primary Key , StudentName , Age)

Table Name : Subject

Schema : (Subject_ID , SubjectName)

Third Normal Form (3NF):

In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Here above table needs to normalize into third normal form where this table could store the student details and subject details as follows

Table Name : Student_Details

Schema :(Student_Details_ID-Primary key , Student_ID-Foreign key, Subject_ID-Foreign key)

Exercise 3 :

Given table in the question is in first normal form (1NF)

Table schema :StudentID, StudentName, DOB, Street, City, State, Zip

2NF : Here address is not depend on student table hence need to normalize in second normal form (2NF) as Student and Address table as below

Table Name : Student (StudentID-Primary key, StudentName , DOB)

Table Name : Address (AddressID -Primary Key , Street , City , State ,Zip)

Thrid Normal Form (3NF) : Above table in third normal form is as given below

Table Name : StudentDetails (SDID - Primary Key , StudentID-Foreign Key , AddressID-Foreign Key)

Foreign key is used to form a relationship between two tables.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.

Student_Name Age Subject Dan 12 Biology Dan 12 Math Tim 13 Math Karen 11 Math

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.