Relational Data Analysis RDA

1
Relational Data Analysis (RDA)

• RDA organises all the systems 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).

2
Relations
Attribute (column name)
Primary key
Foreign key
Tuple (row)
3
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.

4
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.

5
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.

6
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.

7
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

8
WARD
PATIENT Patient no. Patient Surname Patient
Forename Ward No.
WARD Ward No. Ward Name
PATIENT
DRUG
PRESCRIPTION Patient No. Prescription Date Drug
Code Dosage Length of Treatment
DRUG Drug Code Drug Name
PRE- SCRIPTION
9
Logical Data Modelling vs RDA
Logical Data Modelling Top Down Based on analysis
of entities and their inter-relationships Intuitiv
e and subjective Based on and validated against
the processing requirements May produce simple
and inflexible structures Model represented as a
diagram
Relational Data Analysis Bottom-up Based on
analysis of attributes and their
inter-relationships Formal, rigorous and
mathematically based Based on data content of
system inputs and outputs Produces flexible,
complex structures Model represented by groups of
attributes with key attributes identified
(normalised relations)
From SSADM Version 4, M Goodland and C Slater