Back 

Normalising a database questions and answers

Questions

Q1. What does normalising a database achieve?
Q2. What is meant by UNF or 0NF?
Q3. Write down exactly what you have to do to go from UNF to 1NF.
Q4. What is actually achieved when you put a database in 1NF?
Q5. Write down exactly what you have to do to go from 1NF to 2NF.
Q6. What is actually achieved when you put a database in 2NF?
Q7. Write down exactly what you have to do to go from 2NF to 3NF.
Q8. What is actually achieved when you put a database in 3NF?
Q9. What is the difference between a simple primary key and a compound primary key?
Q10. Showing the normalisation steps, normalise a student-course database. A typical record is shown below.

STUDENT RECORD

Student ID: 1232223
Surname: Patel
Initial: A
DofB: 09/06/1996

CourseNo     CourseName      LecturerID      LecturerSurname      LecturerInitial
435                 Fractal Maths           23                        Cooper                            A
200                 Mechanics               18                        Smith                                F
191                 Chaos Theory          23                        Cooper                            A
31                   Random Theory       11                       Mistry                               D

Answers

Q1. Normalisation aims to create a database that has two key characteristics. Redundant data is minimised and the chance of making data in the database inconsistent is minimised.
Q2. UNF (0NF) is the unnormalised form of a database.
Q3. To go from UNF to 1NF, you copy over any repeating groups into the 1NF column. Once you have done this, you copy across the attribute that acts as the primary key from the non-repeating group in the UNF column and add it to the repeating group in the 1NF column. The repeating group in 1NF now has a ‘compound’ primary key, a primary key made up of more than one attribute. Finally, you copy across the data items that remain in UNF to 1NF, into its own group.
Q4. A table in 1NF contains no ‘repeating groups’.
Q5. To go from 1NF to 2NF: if any table in 1NF has a simple primary key (made up of only one attribute) then it is automatically in 2NF and can be copied across to the 2NF column. The next step is to identify those non-key attributes (i.e. attributes that are NOT part of a compound primary key) which are related to only PART of the compound key.
You then copy the part of the primary key that those non-key attributes depend upon from 1NF into the new table you just created in 2NF. Then you underline this attribute to show that it is the primary key of this new group. Finally, you copy across any attributes left over from the old group from 1NF into 2NF, into their own table.
Q6. A database is in 2NF if it is in 1NF and all the non-key attributes in a table depend entirely upon the primary key.
Q7. To go from 2NF to 3NF: Any table that is in 2NF and has zero or one non-key attributes, are automatically in 3NF. They can be moved across without further thought. Going from 2NF to 3NF concerns only non-key attributes. You are looking for any non-key attribute that is directly related and dependent upon another non-key attribute. You move these into their own table. You then copy across the dependent non-key attribute to the new table in 3NF and make it the primary key.
Q8. A database is in 3NF if it is in 2NF and each table has no non-key attributes that depend upon other non-key attributes.
Q9. A simply primary key is made up of just one attribute whereas a compound primary key is made up of more than one attribute.
Q10. The normalised database is as follows:

Back