Transcript 534 - Relational and Online Database Management Systems

Unit 4
Normalisation
and
Relational Database
Management Systems
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
Duplication in Flatfile
Members
No
Forename
Surname Address 1 Address 2
Tel No
Video
No
122
Evans
Daniel
7 High St
Aberaeron
01545588999
343
759
Lees
Anna
2 Bridge
St
Lampeter
01570422967
523
122
Evans
Daniel
7 High St
Aberaeron
01545588999
455
Title
Certificate
Category
Cost per
day
Date
loaned
Length
of loan
Date
due
Crash
12
Comedy
£3.00
12/09/05
1 day
13/09/05 £3.00
Emma
12
Drama
£3.00
12/09/05
1 day
13/09/05 £3.00
War of the
Worlds
PG
Sci FI
£3.59
14/09/05
2 days
16/09/05 £3.50
Total
cost
Members
Member number
Foreign Key
Primary Key
Forename
Surname
Loans ID
Loans
Member number
Video number
Primary Key
Address line 1
Address line 2
Telephone number
Date loaned
Length of loan
Date due
Videos
Total cost
Video number
Title
Certificate
Category
Cost per day
Linked data tables in a relational database
Primary Key
Normalisation
Terminology: Primary Key
A primary key is a unique value which allows
each record to be identified

CustomerID
FirstName
LastName
1
Brian
Smith
2
Harry
Adams
3
Joe
Jones
4
Harry
Smith
FirstName or LastName cannot be primary keys as they
contain duplicate and un-unique data. CustomerID
uniquely identifies a row and is therefore suitable
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:
OrderNo
ItemNo
EmployeeNo
CustomerNo
ItemName
Quantity
121
3
4
1024
Nut
4
121
4
4
1024
Bolt
3
122
8
9
176
Washer
6
123
3
6
154
Bolt
5
123
8
6
154
Washer
4
There are no unique fields, so the Primary Key is best suited by
OrderNo and ItemNo taken together
Foreign Key
A Foreign key is a primary key of another table
used to link the tables
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
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
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
Staff
EmployeeID*
LastName
FirstName
CityID
TypeID
Cities
JobTypes
CityID*
TypeID*
City
Salary
DoctorID#
WardID*
Name
Specialism
ContactNo
PatientID#
Name
Illness
Gender
Address
DoctorID*
# Primary Key
WardID #
No of Beds
Type of
Ward
DoctorID*
WardID*
* 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)
,
Advantages of using databases
(Advantages of normalisation)
1. 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
What makes databases secure ?
Hierarchy of passwords
User access rights and privileges
Data is stored separately from programs
so different programs cannot overwrite
data.
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