Normalisation

1
Unit 4

• Normalisation
• and
• Relational Database
• Management Systems

2
Computerised Databases
A database is a organised collection of data
items and links between them structured in such a
way that it allows it to be accessed by a number
of different application programs. i.e. related
tables of data which are linked together and many
programs can use the data in the tables Use a
database when you need to store lots of data
3
Duplication in Flatfile

4
Primary Key
Foreign Key
Primary Key
Loans ID
Primary Key

Linked data tables in a relational database
5
Normalisation
6
Terminology Primary Key

• A primary key is a unique value which allows each
  record to be identified

• FirstName or LastName cannot be primary keys as
  they contain duplicate and un-unique data.
  CustomerID uniquely identifies a row and is
  therefore suitable

7

• Sometimes there is no single field appropriate as
  a primary key. In these circumstances, it is
  possible to select two fields which, when taken
  together create a unique value

There are no unique fields, so the Primary Key
is best suited by OrderNo and ItemNo taken
together
8
Foreign Key

• A Foreign key is a primary key of another table
  used to link the tables

9
Second Normal Form (2NF)

• To be in 2NF a table must
• Be in 1NF (obviously)
• Have all non key fields fully functionally
  dependant on the primary key
• In English
• A non key field is one that is not part of the
  primary key
• It means that you need to use the primary key to
  determine the value of the other fields in the
  table
• If you can find the value of other fields without
  using the primary key, you should remove that
  field from the table and place it in a separate
  table

10
Third Normal Form (3NF)

• To be in 3NF a table must
• Fields which do not form part of the primary key
  must always be solely dependent on the primary
  key and not on anything else, such as another non
  key field

11
Third Normal Form (Cont.)

• Remove City from the table and create a new
  cities table
• Remove Salary from the table and create a job
  type table

12
Primary Key Foreign Key
DoctorTable (DoctorID, Name, Specialism, Contact
No, WardID,) WardTable (WardID, No of
Beds,Type of Ward, DoctorID) PatientTable
(PatientID, Name,Adress, Illness, Gender,
DoctorID, WardID) ,
13
Advantages of using databases(Advantages of
normalisation)

• Avoids data duplication
• -data stored once
• -linked by keyfields
• -all data available via relational links in
  keyfields
• 2. Controlled redundancy
• - Minimises data duplication
• 3. Ensures consistency of data
• - of data to all users
• 4. Data independence
• - data stored separately from programs so can
  add new fields because data is independent of
  the applications which use it
• 5. Increased security

14
What makes databases secure ?

• Hierarchy of passwords
• User access rights and privileges
• Data is stored separately from programs so
  different programs cannot overwrite data.

15
Disadvantages

• Complex to set up and maintain needs team of
  programmers to maintain it.
• Database software is large complex expensive and
  requires powerful computers.
• All applications which access the data will be
  affected if database fails. As the DBMS is the
  only access to operational data a system failure
  can have serious consequences