Database Design

1
Database Design

• CP4 Revision

2
Database Design

• Careful database design is needed to avoid
• Data Redundancy storing the same data item in
  more than one place (waste of storage space)
• Data Inconsistency two versions of the same
  data may be different.

3
Entities

• An entity is a thing about which data is stored
  (Eg Customer, Employee, Stock)
• A relationship is a link between two entities.

4
Relationships

• One-to-One One Pupil has one Network Account.
• One-to-Many One Pupil borrows many LibraryBooks
• Many-to-Many One Teacher teaches many Pupils
  One Pupil is taught by many Teachers.

5
Entity-Relationship Diagrams
One-to-One
One-to-Many
Many-to-Many
6
Key Fields

• Each Entity must have a unique key field the
  primary key.

PatientID Surname Forename Disease WardID
P101 Smith Paul Measles W10
P102 Jones Jane Mumps W12
P103 Wilkins Mary Measles W10
P104 Jackson Tom Tonsillitis W17
PatientID is the primary key field.
An Entity may have foreign keys primary key
fields of other entities. These provide the links
(relationships) between different
entities. WardID is a foreign key.
7
Table Design

• The standard notation for writing down the design
  of a table
• CAPITAL letters for the name of the table
• Underline the key field,
• Italicise the foreign keys.
• Eg.PATIENTS (PatientID, Surname, Forename,
  Disease, WardID)

8
Database Normalisation

• Avoids data duplication
• Avoids data inconsistencies
• ensures the best possible design for a database
  so other applications can use it.

9
Un-Normalised Data
There are a number of repeated fields here
PupilID PupilName DOB ExamID Subject Level Date RoomID RoomName
P99010 Jane Grey 12.03.86 CP101 EN004 AR075 Computing English Art AS GCSE AS 15.05.01 24.05.01 12.06.01 UH UG UG Hall Gym Gym
P99205 Tom Jones 05.11.86 CP101 MA110 PH190 Computing Maths Physics AS AS AS 15.05.01 15.06.01 08.06.01 UH UG 58 Hall Gym Science Lab
P99311 Sam Hill 16.08.86 CP101 EN004 Computing English AS GCSE 15.05.01 24.05.01 UH UG Hall Gym
To change into First Normal Form the repeated
groups of fields must go Split into two linked
tables
10
First Normal Form

• PUPILS ( PupilID, PupilName, DOB, ExamID)
• EXAMS ( ExamID, Subject, Level, Date, RoomID,
  RoomName)
• The key field of the PUPILS table is a combined
  key field.
• The link is made through the foreign key ExamID.

PupilID PupilName DOB ExamID
P99010 Jane Grey 12.03.86 CP101
P99010 Jane Grey 12.03.86 EN004
P99010 Jane Grey 12.03.86 AR075
P99205 Tom Jones 05.11.86 CP101
P99205 Tom Jones 05.11.86 MA110
P99205 Tom Jones 05.11.86 PH190
P99311 Sam Hill 16.08.86 CP101
P99311 Sam Hill 16.08.86 EN004
To get this into Second Normal Form, no field
must be dependant on only part of the key
field. Do this by creating a linking table HINT
You need to do this every time you have a
Many-to-Many relationship)
ExamID Subject Level Date RoomID RoomName
CP101 Computing AS 15.05.01 UH Hall
AR075 Art AS 12.06.01 UG Gym
MA110 Maths AS 15.06.01 UG Gym
PH190 Physics AS 08.06.01 58 Science Lab
EN004 English GCSE 24.05.01 UG Gym
11
Second Normal Form

• PUPILS ( PupilID, PupilName, DOB)
• EXAMS ( ExamID, Subject, Level, Date, RoomID,
  RoomName)
• PUPIL_SITS(PupilID,ExamID)

The Many-to-Many relationship
must be changed to
12
Second Normal Form
PupilID PupilName DOB
P99010 Jane Grey 12.03.86
P99205 Tom Jones 05.11.86
P99311 Sam Hill 16.08.86
PupilID ExamID
P99010 CP101
P99010 EN004
P99010 AR075
P99205 CP101
P99205 MA110
P99205 PH190
P99311 CP101
P99311 EN004
ExamID Subject Level Date RoomID RoomName
CP101 Computing AS 15.05.01 UH Hall
AR075 Art AS 12.06.01 UG Gym
MA110 Maths AS 15.06.01 UG Gym
PH190 Physics AS 08.06.01 58 Science Lab
EN004 English GCSE 24.05.01 UG Gym
To make this database into Third Normal Form,
there must be no non-key dependencies ..so
separate the Rooms into another table
13
Third Normal Form
PupilID PupilName DOB
P99010 Jane Grey 12.03.86
P99205 Tom Jones 05.11.86
P99311 Sam Hill 16.08.86
PupilID ExamID
P99010 CP101
P99010 EN004
P99010 AR075
P99205 CP101
P99205 MA110
P99205 PH190
P99311 CP101
P99311 EN004
ExamID Subject Level Date RoomID
CP101 Computing AS 15.05.01 UH
AR075 Art AS 12.06.01 UG
MA110 Maths AS 15.06.01 UG
PH190 Physics AS 08.06.01 58
EN004 English GCSE 24.05.01 UG
RoomID RoomName
UH Hall
UG Gym
UG Gym
58 Science Lab
UG Gym
PUPILS (PupilID, PupilName, DOB) EXAMS (ExamID,
Subject, Level, Date, RoomID) PUPIL_SITS
(PupilID, ExamID) ROOMS (RoomID, RoomName)