1 of 19

Skater (Jan 2018)

Developing the ERD

2 of 19

People make mistakes

Normalisation makes it possible to enter data ONCE and ONCE ONLY
Cutting down on entries cuts down on ERRORS.

There are 3 errors that we look to minimise:

Update errors – changes have to be consistent
Insert errors – new data added to the database must be complete
Delete errors - we must not lose data apart from what we mean to delete

3 of 19

Update Errors – Look for dependent fields

Sort on any of the ID fields and look for dependent fields (Those that are ALWAYS the same for that ID field)
SkaterID

What fields DEPEND on the SkaterID?

X

✓

X

✓

X

4 of 19

Update Errors – Example

Suppose the DOB was wrong and had to be changed to 21/12/2002
How many times would it need to be corrected in this table structure? (Miss some and we have created an anomaly – bad data, it has become inconsistent)

What fields DEPEND on the SkaterID?

X

✓

X

✓

X

5 of 19

Update Errors – Remove duplicates

Before removing keep the link to the rest of the data

If we simply remove all these duplicates then these records no longer relate to the skater

6 of 19

Update Errors – Remove duplicates

Keep the relationship by repeating the ID field

7 of 19

Update Errors – Remove duplicates

Remove the duplicates from the Skater table

The SkaterID is the link between the records

8 of 19

Update Errors – Remove duplicates: Fewer Errors

Now if the DOB needs changing it only has to be done ONCE

9 of 19

Update Errors – Remove duplicates: AGAIN

Sort on a different ID field – the next one is CategoryID
Look for dependent fields
Filters can be useful

✓

10 of 19

Update Errors – Remove duplicates

The CategoryID has to be included in both.

11 of 19

Update Errors – Remove duplicates: MoveID

Sort on MoveID and look for dependent fields

Look at one MoveID value – but do check others to ensure you have found truly dependent fields

12 of 19

Update Errors – Remove duplicates: MoveID

Separate the MoveID fields to their own table, but leave a copy of MoveID to maintain the relationship

13 of 19

Update Errors – Remove duplicates: MoveID

Remove the duplicates from the Move table

14 of 19

Delete Errors

Check that if we delete a record we are not deleting other data as well
QUESTION: If we delete skater 1 are we losing data about something else as well?

15 of 19

Delete Errors

QUESTION: If we delete skater 1 are we losing data about something else as well?

ANSWER: Yes!

If they are the last pupil at this school we will no longer have a school ID of 3 in the database and will no longer know that value is the “Prospect Secondary School”

16 of 19

Delete Errors

SOLUTION: Put the SchoolID and SchoolName in their own table and leave a copy of the SchoolID in the Skater table to maintain the relationship

Now if Skater 1 is deleted we do not lose the school information

17 of 19

Insert Errors – Entering incomplete data

Suppose a move that didn’t exist was entered in a performance, e.g. Move 24.

Without an entry in the Move Table (above) no score could be worked out.

18 of 19

The ERD

19 of 19

Judges – Repeated Group?

At first glance the judges look like a repeating group but they are distinct and non-interchangeable so can be kept in one table.