Outcome 2: Creating a Relational Database Design

Table of Contents

1. Data Analysis and Normalisation
2. Keys
3. The Normalisation Process
4. The Normalisation Process
5. Activity 8: Normalisation Case Study
6. Multiple and Nested Repeating Groups
7. Activity 9
8. Activity 10
9. Constructing the Data Model
10. Degrees of Relationship (Cardinality)
    1. One-to-one (1:1)
    2. One-to-Many (1:M)
    3. Many-to-Many (M:N)
    4. Optional Relationships
    5. Recursive Relationships
    6. Constructing the Data Model
    7. More Symbols
    8. The Basic Data Model
    9. Relationships
    10. Degrees of Relationship
    11. Activity 11
    12. Validating the Data Model
    13. Outcome Assessments 1 and 2

Recursive Relationships

In the examples above, a relationship has always been between occurrences in two different entities. However, it is possible for the same entity to participate in the relationship. This is termed a recursive relationship.

Let us take the example of an employee who is also a manager. But a manager is also an employee, whose details will be held in the employee entity. To implement this a foreign key of the employee's manager number would be held in each employee record.

Employee entity

Employee no

Employee surname

Employee forename

Employee DOB

Employee NI number

Manager no * (this is the employee no of the employee's manager)

Graphically, this can be shown as:

This is commonly know as a 'pig's ear'.

Another, more complicated example (don't worry, you will not be assessed on this) is showing the relationship between a person and their parents. This can be represented graphically by:

Next: Constructing the Data Model