Transcript Normalisation-Basics

Normalisation
Unit 6: Databases
Just to recap

What is an Entity

What is an Attribute?
Just to recap



So, for example:
An entity in a video store database
would be ‘Customer’
What would the Attributes be?
Attributes







First Name
Surname
Address
Telephone Number
Age
Gender
E-mail
Introduction


This week we will be going on to
standard database notation and
Normalisation
In your assignment your plan will
need to be in both tabular format
and in standardised database
notation
Lesson Objectives

By the end of this lesson, you
should be able to:


Design a database in standard
database notation
Define the three forms of normalisation
Standard database notation

Using the video store as an
example:
Standard database notation

In a video store database, the tables can
be described as follows
• CUSTOMER (CustomerID, Firstname, Surname,
Address, TelephoneNumber, Email, Age, Gender)
• VIDEO (VideoID, VideoTitle, Rating, FilmGenre,
DateReleased, LengthOfFilm, RentalPrice)
• VIDEORENTALS (VideoRentalID, CustomerID, VideoID,
DateRented, DateReturned)
Primary Keys

The underlined field types are known as Primary
Keys
• This is a field that is used to uniquely define a
particular record or line in a table.
• Since text fields e.g. surname can be repeated,
primary keys are nearly always numeric fields.

They can include:

Membership number, product number, employee
number, catalogue number, account number.
Primary and Foreign Key
Customer
Table
Customer ID
First Name
Surname
Address
Telephone
number
E-mail
Age
Gender
Video Table
Video ID
Video title
Rating
Film Genre
Date Released
Length of film
Rental price
Video
Rentals
Table
Video_Rental
ID
Customer ID
Video ID
Date Rented
Date Returned
Your Task

For the two databases you have
designed:


Draw the tables as shown in the
previous slide
Identify possible Primary Keys
Foreign Keys



This is a field in one table which is also
the primary key of another table.
Foreign keys are used to establish a
relationship between the main table and
other subsidiary tables.
Looking at the video tables, what would
be the foreign keys?
Foreign Keys
Customer
Table
Video
Table
Customer
ID
Video ID
First Name
Rating
Surname
Address
Telephone
number
E-mail
Age
Gender
Video title
Film Genre
Date
Released
Length of
film
Rental
price
Video
Rentals
Table
Video_Rent
al ID
Customer
ID
Video ID
Date Rented
Date
Returned
Your Task

On the tables you have finished for
your databases, identify the foreign
keys
Normalisation



The process of refining the structure of a
database to minimise redundancy and
improve integrity is called normalisation.
When a database has been normalised it
is said to be in normal form.
There are three normal forms:

First, second and third
First Normal Form



A database is in first normal form if there
are no repeated fields.
That means that there must only be one
field for each item of data you want to
store.
A library database, for example, is not in
first normal form because it has the field’s
book1, book2, book3, book4, book5, and
book6, which all store the same
information, i.e. the name of a book.
First Normal Form

First normal form not only facilitates
searching, but is also more space
efficient as there wouldn't be 5
empty fields being stored if
borrowers only had one book.
Second Normal Form

A database is said to be in second
normal form if it:



is already in first normal form
has no fields that aren't dependent on
the whole of the key
That is to say that all fields are
dependent on the whole of the key
(where there is a compound key).
Second Normal Form

For example, a merits database:









Name
Tutor group
Teacher
Subject
Date
Reason
Head of year
Tutor
There are no repeated fields, so it's in first normal
form. If you made name the key field, however,
you could see there would be fields that aren't
dependent on the key.
Second Normal Form


Subject, for example, is related to the
teacher, not to the student. This
database, therefore, is not in second
normal form.
Even if you were to make a compound
key to include the date and the teacher
giving the merit, it still wouldn't be in
second normal before because subject
wouldn't be dependent on the whole of
the key.
Third Normal Form

A database is in third normal form if it:



is already in second normal form
it has no non-key dependencies
By non-key dependencies, we mean that
there are no fields that are dependent on
other fields that are not part of the key.
Third Normal Form

For example:

In the version of merits database
previously, where ‘Name’ was the key,
the ‘subject’ was related to the
‘teacher’, and not to the student that is a non-key dependency.
Real Life



But forget all that! It still sounds a bit complicated
to me, and I've been developing relational
databases (including professionally) for more than
4 years.
For your assignment, don't try to start at first
normal form, and then go to second normal form,
and then to third.
Once you've got the hang of it, you'll knock out
databases in third normal form without even
thinking about it. The key thing is to think about
what your entities (or tables) are going to be - if
you pick the right ones it'll normalise itself.
The Normalisation Oath



Well, don't forget it all, however, because you
may be asked about normalisation in an exam.
Here's a little tip to remember the three stages we call it the Normalisation Oath:
Each attribute is dependent on the key, the whole
key, and nothing but the key!
So, to get to third normal form, your nonrepeating fields (first normal form) need to be
dependent on the whole of the key (second
normal form), and nothing other than the key
(third normal form). It works for me!
So,


By choosing the correct field names
and tables, your database should
normalise itself
Normalisation improves searches
and queries