Relational Data Analysis (RDA)

1. 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).

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 Intuitive 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