Transcript Relational Data Analysis (RDA)

Relational Data Analysis (RDA)
RDA organises all the system’s data items into
a set of well NORMALISED relations.
These should avoid:
1. Unnecessary duplication of data items
in different relations (i.e. no redundant
data).
2. Problems with modifying, inserting and
deleting data (update anomalies).
RJP/RDA 1 /93
Relations
Attribute
(column name)
Primary
key
Foreign
key
Tuple
(row)
P a tie n t T a b le
W a rd T a b le
RJP/RDA 2 /93
Some Relational Rules
• Rows
1. In any table there must be no duplicate rows. Thus each
row must be uniquely identifiable (by its primary key).
2. The order in which the rows appear must not be
significant.
• Columns
1. The order of the columns must not be significant.
2. There must be only ONE value associated with each
row/column intersection.
• Domains
A domain is the pool of all possible values from which the
actual values appearing in the columns are drawn.
RJP/RDA 3 /93
Stages of Normalisation
•
•
•
•
Unnormalised form
First normal form
Second normal form
Third normal form
The rules of normalisation were developed by
Ted Codd and have a formal mathematical
basis (relational algebra) which is outside the
scope of this treatment.
RJP/RDA 4 /93
Steps
• UNF to 1NF
Remove any repeating groups of data items
into separate relations (including the primary key).
• 1NF to 2NF
Represent the data in 2NF by removing any data
items that only depend on part of the key.
• 2NF to 3NF
Represent the data in 3NF by removing any data
items not directly dependent on the key.
RJP/RDA 5 /93
Tests for TNF Relations
• Test 1
Given a value for the key(s) of a TNF relation, is there just
one possible value for for each data item in that relation?
• Test 2*
Is each data item in a TNF relation directly and wholly
dependent on the keys of that relation?
*The ‘Relational Oath’
“I swear to be dependent on the key, the whole key and
nothing but the key, so help me Codd.”
RJP/RDA 6 /93
Conversion of RELATIONS to a
Logical Data Model
• Each relation is shown as an entity
• Compound key relations are ‘owned’ by relations
that have part of the compound key as their primary key
• Relations that have foreign keys are owned by relations
that have that foreign key as their primary key
RJP/RDA 7 /93
PATIENT
WARD
Patient no.
Patient Surname
Patient Forename
Ward No.
Ward No.
Ward Name
PRESCRIPTION
DRUG
Patient No.
Prescription Date
Drug Code
Dosage
Length of Treatment
Drug Code
Drug Name
RJP/RDA 8 /93
WARD
PATIENT
DRUG
PRESCRIPTION
Logical Data Modelling vs RDA
Logical Data Modelling
Relational Data Analysis
Top Down
Bottom-up
Based on analysis of entities
and their inter-relationships
Based on analysis of attributes
and their inter-relationships
Intuitive and subjective
Formal, rigorous and
mathematically based
Based on and validated
against the processing
requirements
Based on data content of
system inputs and outputs
May produce simple and
inflexible structures
Produces flexible, complex
structures
Model represented as a
diagram
Model represented by groups of
attributes with key attributes
identified (normalised relations)
From SSADM Version 4, M Goodland and C Slater
RJP/RDA 9 /93