Section 9.3
9 Databases
9.3 The Aims Of Database Normalisation
9.3.1 The Aims Of Database Normalisation
Normalisation ensures that the database is structured in the best possible
way.
To achieve control over data redundancy. There should be no unecessary
duplication of data in different tables.
To ensure data consistency. Where duplication is necessary the data is the
same.
To ensure tables have a flexible structure. E.g. number of classes taken or
books borrowed should not be limited.
To allow data in different tables can be used in complex queries.
A table is in it's first normal form if it contains no repeating
attributes or groups of attributes.
We start off with a repeating attribute Student.
Student(Student number, Student name, Date of birth,
Sex, Class number)
Where the overlining represents a repeating attribute.
To put the table into 1NF the repeating attribute is turned into part of the
primary key.
Student(Student number, Student name, Date of birth,
Sex, Class number)
A table is in the second normal form if it's in the first normal
form AND no column that is not part of the primary key is dependant only a
portion of the primary key.
Consider Student
Student(Student number, Student name, Date of birth,
Sex, Class number)
Student number is uniquely associated with Student name but Class number is
not.
To put the table into second normal form, the primary that the other attributes
are not dependant upon is removed and a linking table created.
This gives
Student(Student number, Student name, Date of birth,
Sex)
Student_Takes(Student number,
Class number)
Class(Class number, Class name,
Lecturer number)
A table is in the third normal form if it is the second normal
form and there are no non-key columns dependant on other non-key columns that
could not act as the primary key.
In short this is the non-key dependence test.
If tutorgp were not a separate entity, then:
Staff(Lecturer no, Lecturer name, Tel no, Tutorgp
name, Tutorgp room)
Tutorgp and Tutorgp room are dependant on each other.
To put the table into 3NF the dependant column is removed from the table and
a new table containing both columns is created.
Staff(Lecturer no, Lecturer name, Tel no, Tutorgp
name)
Tutorgp(Tutorgp name, Tutorgp room)
The third normal form helps prevent unintentional deletion of data.
9.3.5 Foreign Keys
A foreign key is the primary key of one table that appears in another table.
Example
In the Staff table:
Staff(Lecturer no, Lecturer name, Telephone no,
Tutorgp name)
Tutorgp name is a foreign key because it is the key field of Tutorgp.
