Design

What a database is, including relational databases and flat file databases
What primary and foreign keys are
What data integrity is
What a data dictionary is and be able to produce one
What a search query is and be able to design one

Be able to turn database requirements into a database design
Be able to design a database query

What is a database?

All information needs to be stored in some way that means that it can be easily accessed and quickly. A filing system is ideal for that job
For example, you might organise folders on your computer in a way that makes sense to you.
A more rudimentary example of this is that you might have a cupboard in your house that's organised by what's in each drawer. For example one drawer may contain socks, one might contain t-shirts and one might contain trousers.

Cupboard icons created by justicon - Flaticon

What is a database?

A database is a structured collection of similar information which you can search through.
Before computers became mainstream, data was stored in filing cabinets - some of them were so large, rooms were built just to house them 😱
Today, databases tend to be stored on computer systems and take a fraction of the space that they did when they were stored in filing cabinets.

Database icons created by Awicon - Flaticon

Data vs information

The key difference between information and data is that data is meaningless (for example you might have the number 21 07 24 - but that doesn't mean anything)
But this when we know this is a data we can tell that this means the 21st of July 2024.

Information icons created by Mehwish - Flaticon

Database structure

A file is a collection of structured data on a particular topic. A file is made up of 1 or more records.
A record is a collection of structured data on a particular person or object. A record is made up of 1 or more fields.
- A "record" is the technical name for a database table row and contains the actual data in a table.
- Each record in a table contains the same fields but (usually) has different data in those fields.
A field is an area of a record that contains an individual piece of data.
- A “field” is the technical name for a database table column and is used to denote a specific type of data.
- The “Age” field (column) contains numeric data denoting customer age: Each field on a table holds only one type of data (for example, numerical data describing age)

Medical record icons created by Karyative - Flaticon

Keys

Each record in a database has something unique that identifies it from other records.
Imagine we had two Jamie Balfours. How could you tell you are looking at the right one?
In a database, we use a key called the primary key.

Forename	Surname
Charlee	Goodsman
James	Bond
Andrea	Campbell
Alicia	Coyle
Gavin	Boyle
Steven	Wren
Angela	MacDonald
William	MacKay

StudentID
006
007
008
009
010
011
012
013

Keys

Each record in a database has something unique that identifies it from other records.
Imagine we had two Jamie Balfours. How could you tell you are looking at the right one?
In a database, we use a key called the primary key.

Forename	Surname
Charlee	Goodsman
James	Bond
Andrea	Campbell
Alicia	Coyle
Gavin	Boyle
Steven	Wren
Angela	MacDonald
William	MacKay

StudentID
006
007
008
009
010
011
012
013

Class discussion: primary keys

What uniquely identifies each person in the school?

What uniquely identifies each person sitting National 5 Computing Science?

What uniquely identifies each member of staff working in the school?

Flat file database

A flat file database is a database that is contained in a single table.

StudentID	Forename	Surname	House
006	Charlee	Goodsman	G
007	James	Bond	G
008	Andrea	Campbell	L
009	Alicia	Coyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

Do we have any data replication here?

Guidance Teacher	Guidance Room
Mr Kyle	1.3
Mr Kyle	1.3
Mr Madison	1.8
Mr Madison	1.8
Mr Kyle	1.3
Miss Smith	2.5
Miss Smith	2.5
Mr Madison	1.8

Relational databases

A relational database is a database that is made up of entities (tables), attributes (fields) and relationships. It is made up of multiple entities.

StudentID	Forename	Surname	House
006	Charlee	Goodsman	G
007	James	Bond	G
008	Andrea	Campbell	L
009	Alicia	Coyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

Guidance Teacher	Guidance Room
Mr Kyle	1.3
Mr Kyle	1.3
Mr Madison	1.8
Mr Madison	1.8
Mr Kyle	1.3
Miss Smith	2.5
Miss Smith	2.5
Mr Madison	1.8

Student

GuidanceTeacher

Relational databases

A relational database is a database that is made up of entities (tables), attributes (fields) and relationships. It is made up of multiple entities.

StudentID	Forename	Surname	House
006	Charlee	Goodsman	G
007	James	Bond	G
008	Andrea	Campbell	L
009	Alicia	Coyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

Guidance Teacher	Guidance Room
Mr Kyle	1.3
Mr Kyle	1.3
Mr Madison	1.8
Mr Madison	1.8
Mr Kyle	1.3
Miss Smith	2.5
Miss Smith	2.5
Mr Madison	1.8

Student

GuidanceTeacher

Make sure to name all entities!

Relational databases

Relational databases reduce storage space and wasted storage
They allow for data to be updated quicker - one update to the GuidanceTeacher entity for example would update the information for every record in Student.
As databases get larger, separating the content into different entites makes it easier to work with.

StudentID	Forename	Surname	House
006	Alex	Smith	G
007	James	Bond	G
008	Mark	Adams	L
009	David	Gyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

Guidance Teacher	Guidance Room
Mr Kyle	1.3
Mr Kyle	1.3
Mr Madison	1.8
Mr Madison	1.8
Mr Kyle	1.3
Miss Smith	2.5
Miss Smith	2.5
Mr Madison	1.8

A relational database is a database that is made up of entities, attributes and relationships. It can be made up of multiple tables.

Student

GuidanceTeacher

Database icons created by Eucalyp - Flaticon

entity relationships with tables

Tables only hold attributes for one entity only. But what if we have multiple, related entities?
Entity relationships (ER) are used to describe the structure of a database, showing entities and the relationships between them. At National 5, you will only ever be shown two entities.
In this example we are looking at authors and books:

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

writes

One author writes (infinitely) many books

(or (infinitely) many books are written by one author)

∞

The linking the of tables is made through the foreign key (in Book) to the primary key (in Author).

entity relationships with tables

The linking the of tables is made through the foreign key (in Book) to the primary key (in Author).
A foreign key must always be of the same data type as the field it links to.
A foreign key is represented by an asterisk (*) at the end of it at all times.
A primary key is always underlined.
Showing the relationship using tables like this is called the Information Engineering format.

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

writes

∞

Tables only hold attributes for one entity only.
But what if we have multiple, related entities?

In this example we are looking at authors and books.

One author writes (infinitely) many books

(or (infinitely) many books are written by one author)

Example

A technology company employs contractors to carry out IT work. They create a database that contains information on all contractors including their name, temporary employee number, their hourly rate and their manager. The company create a relational database as a result, with managers being stored separately from employees. Managers have an employee number, their name, a promotion level and their own manager's employee number.

Create an entity relationship to show this.

Contractor icons created by srip - Flaticon

Manager
EmployeeNumber
Name
PromotionLevel
ManagerID

Contractor
TemporaryEmployeeNumber
Name
ManagerID*
HourlyRate

manages

∞

entity relationship diagrams

The entity that contains the foreign key is always on the many side of the diagram and they are linked by a verb.
This format of entity relationship diagram is called the Chen Notation and is far more common both in National 5 and in the industry.

Book

Author

writes

BookISBN

BookTitle

BookAuthor*

BookPublishYear

AuthorID

AuthorName

AuthorDateOfBirth

Relational databases

Looking back at the earlier example, there is currently no way to link these tables.

Guidance Teacher	Guidance Room
Mr Kyle	1.3
Mr Kyle	1.3
Mr Madison	1.8
Mr Madison	1.8
Mr Kyle	1.3
Miss Smith	2.5
Miss Smith	2.5
Mr Madison	1.8

Student

GuidanceTeacher

StudentID	Forename	Surname	House
006	Charlee	Goodsman	G
007	James	Bond	G
008	Andrea	Campbell	L
009	Alicia	Coyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

But if we add in a House attribute to the GuidanceTeacher entity we can link them on that.

Relational databases

StudentID	Forename	Surname	House*
006	Charlee	Goodsman	G
007	James	Bond	G
008	Andrea	Campbell	L
009	Alicia	Coyle	L
010	Gavin	Boyle	G
011	Steven	Wren	T
012	Angela	MacDonald	T
013	William	MacKay	L

House	Guidance Teacher	Guidance Room
G	Mr Kyle	1.3
G	Mr Kyle	1.3
L	Mr Madison	1.8
L	Mr Madison	1.8
G	Mr Kyle	1.3
T	Miss Smith	2.5
T	Miss Smith	2.5
L	Mr Madison	1.8

Student

GuidanceTeacher

Looking back at the previous example, there is currently no way to link these tables.

But if we add in a House attribute to the GuidanceTeacher entity we can link them on that.

Try it yourself

A school has several different clubs such as a video games club, a chess club etc. One teacher manages each club but a teacher can manage multiple clubs (e.g. Mr Balfour runs the video games club and computing club).

Clubs have a unique ID, a name, a room name, and the maximum number of pupils allowed at the club. They also have an indication as to whether computers are used.

Teachers have an employee number, a name, a date of birth and their subject.

Create both types of entity relationship diagram for this database.

Past Paper Questions

A property renovation company requires a relational database to store information about the tasks to be carried out on each room of a house.

The functional requirements for the database are identified:

Store details of each room in the house.
Store details of each task.
Output a list of tasks carried out by one of the following trades people: electrician, builder, plasterer, decorator, carpenter, plumber.
Output a list of tasks completed for a single room.

Complete the entity relationship diagram below by identifying key attributes.

2 marks

2022 Q14 (a)

Attribute types

Much like data types in software, attribute types are used to define what type of data will be entered into each field.
We have five different data types:
- Text - any characters from the keyboard
- Number - a real number of an integer
- Date - a date from the calendar
- Time - a time
- Boolean - true or false, often a checkbox

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

Attribute types

Much like data types in software, attribute types are used to define what type of data will be entered into each field.
We have five different data types:
- Text - any characters from the keyboard
- Number - a real number of an integer
- Date - a date from the calendar
- Time - a time
- Boolean - true or false, often a checkbox

Author
AuthorID	Number
AuthorName	Text
AuthorDateOfBirth	Date

Book
BookISBN	Text
BookTitle	Text
AuthorID*	Number
BookPublishYear	Number

Which field type should be used?

Date

Time

Number

Text

Which attribute type should be used to store the year a person was born?

Boolean

Which field type should be used?

Date

Time

Number

Text

Which attribute type should be used to store a telephone number?

Boolean

Which field type should be used?

Date

Time

Number

Text

Which attribute type should be used to store if a person has been given a vaccine?

Boolean

Validation

Validation on a database ensures that the user inserts the correct information into the database.
Validation is applied to attributes in an entity.
There are four forms of validation required for National 5 Computing Science.

Validation icons created by Vectors Tank - Flaticon

Validation	Description	Example
Presence check	The field is required and data must be provided	Email address is required.
Restricted choice	The data must be selected from a list of valid entries (normally a dropdown box)	Class could be 2L1, 2L2, 2G1, 2G2, 2T1 or 2T2
Length check	The data must be of a specified length	Phone numbers require exactly 11 digits, a validation rule could be added to ensure that phone numbers are exactly 11 digits
Range check	The data must be within the range	A score must be between 0 and 55 marks for a prelim

Which validation is being used?

length

range

presence check

restricted choice

A range check is used here to ensure that the number entered is between 0 and 100 inclusive.

A field in a database is used to store a percentage mark for each pupil called mark.

Which validation is being used?

length

range

presence check

restricted choice

If something is required, it's a presence check.

A database is used to store users' details, including an email address so that they can be contacted. The email address is required.

Past Paper Questions

Balloons are available in a limited number of colours. State the type of validation that should be implemented to achieve this

1 mark

2003 Q12 (a)

The primary school has 14 different class names. For example P1A, P4B, P6/7A.

Describe how validation of this field could be implemented when the
database tables are created.

2 marks

SQP Q16 (d) (i)

Data dictionaries

A data dictionary is created during the design stage to plan the structure of a database.
In this dictionary, we define:
- the name of the entity.
- the attributes including:
  - the name of the attribute,
  - the attribute types associated to them,
  - the size of them,
  - whether the field is used as a primary or foreign key
  - any validation that is required on these fields.

Database icons created by Elzicon - Flaticon

Data dictionaries

Attribute	Key	Type	Size	Required	Validation
BookISBN	PK	Text	13	Yes	Length = 13
BookTitle		Text
AuthorID*	FK	Number	11	Yes
BookPublishYear		Number	4		Between 1900 and 2025

Author

Attribute	Key	Type	Size	Required
AuthorID	PK	Number	11	Yes
AuthorName		Text	30	Yes
AuthorDateOfBirth		Date

Book

Data dictionaries tasks

Create data dictionaries for each of the following:
- A database table is needed that will store customers' IDs (11 characters long), their forename and surname (required), date of birth (optional), their credit amount (in pounds), their email address (required)
- A database table is required to store movie information. It stores a unique ID for the movie which is 100 characters long, the name of the film (required), the director, the producer, the top member of the cast (required). Another table is required to store average ratings and is linked to the movie information table. The ratings table contains the movie ID, the average rating (required) and an AI summary of the review comments.

Referential integrity

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

writes

Referential integrity is a very important part of relational database. Take the following relationship:

AuthorID	AuthorName	AuthorDateOfBirth
001	J.R.R Tolkein	03/01/1892
002	J. K. Rowling	31/07/1965

BookISBN	BookTitle	AuthorID	BookPublishYear
9780747532743	Harry Potter And The Philosopher's Stone	002	1997
0064407667	Book the First: The Bad Beginning	003	1999

Referential integrity

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

writes

Now look at these tables. Who wrote the Book the First: The Bad Beginning?

Author
AuthorID	AuthorName	AuthorDateOfBirth
001	J.R.R Tolkein	03/01/1892
002	J. K. Rowling	31/07/1965

Book
BookISBN	BookTitle	AuthorID	BookPublishYear
978-0007203543	The Fellowship of the Ring	001	1954
978-0747532743	Harry Potter And The Philosopher's Stone	002	1997
978-1405266062	Book the First: The Bad Beginning	003	1999

With referential integrity enforced, this would never happen. As database designers and developers, this is something we should ensure is enabled.

Past Paper Questions

A restaurant stores employee details in a database. Each employee is trained as either a chef, a server, a manager or a cleaner. Sample data from the database is shown below.

(a) Complete the missing attribute and attribute type in the table below.

2 marks

2025 Q14 (a)

Employee
empID	empName	jobTitle	startDate	fullTime	contactNum
117254	Alex Roberts	Manager	21/10/24	✓	07701675815
259631	Rayyan Patel	Chef	05/09/23	✓	07778589526
300193	Charlie King	Server	10/03/25		07789900991
220205	Sam Scott	Cleaner	13/02/25		07811606115
576482	Drew Moore	Cleaner	30/12/24		07705900169
365482	Carter Price	Server	23/01/25		07716168759
419372	Rowan Kelly	Chef	07/07/24	✓	07700148652
895714	Jun Baek	Server	30/04/25		07812612568
681354	Nikan Khan	Manager	06/08/23	✓	07780254369
...	...	...	...	...	...

Attribute	Attribute Type
contactNum
	Boolean

Text

fullTime

Past Paper Questions

A restaurant stores employee details in a database. Each employee is trained as either a chef, a server, a manager or a cleaner. Sample data from the database is shown below.

(b) Errors are made when data is entered for new employees. State one attribute where restricted choice validation could be used to reduce errors.

1 marks

2025 Q14 (b)

Employee
empID	empName	jobTitle	startDate	fullTime	contactNum
117254	Alex Roberts	Manager	21/10/24	✓	07701675815
259631	Rayyan Patel	Chef	05/09/23	✓	07778589526
300193	Charlie King	Server	10/03/25		07789900991
220205	Sam Scott	Cleaner	13/02/25		07811606115
576482	Drew Moore	Cleaner	30/12/24		07705900169
365482	Carter Price	Server	23/01/25		07716168759
419372	Rowan Kelly	Chef	07/07/24	✓	07700148652
895714	Jun Baek	Server	30/04/25		07812612568
681354	Nikan Khan	Manager	06/08/23	✓	07780254369
...	...	...	...	...	...

jobTitle

Query design


Fields
Table
Search Criteria
Sort order

As part of the design process with databases, we also need to design our queries that we will run on the database.
The query needs to state:
- Which attributes (fields) are used
- Which entities (tables) will be used
- The criteria to be searched for
- The sort order

Query design

As part of the design process with databases, we also need to design our queries that we will run on the database.
The query needs to state:
- Which attributes (fields) are used
- Which entities (tables) will be used
- The criteria to be searched for
- The sort order


Fields
Tables
Search Criteria
Sort order

Example 1
Fields	BookISBN
Table	Book
Search Criteria	BookPublishYear > 1945
Sort order	BookPublishYear ASC

Example 1:

Find all books ISBN numbers for books which were published after 1945 and order them by the publish year.

Example 1
Fields	AuthorName, AuthorDateOfBirth
Table	Author
Search Criteria	AuthorDateOfBirth > 1985
Sort order	AuthorName DESC

Example 2:

Find all authors names and dates of birth who we born after 1985 and order them by their name from Z to A

Query design - multiple entities

A query that includes multiple tables such as those shown on the right is done with an equi-join between the tables (more in the Implementation part of the course):


Fields	AuthorID, AuthorName, BookTitle, BookPublishYear
Tables	Author, Book
Search Criteria	Author.AuthorID = Book.AuthorID AND BookPublishYear = 1991
Sort order	AuthorName ASC, BookTitle ASC

Author
AuthorID
AuthorName
AuthorDateOfBirth

Book
BookISBN
BookTitle
AuthorID*
BookPublishYear

Query design - challenges

Create queries for each of the following:
- Find each person whos favourite colour is Orange
- Find each person who was born in the year 2000
- Find each person who likes Goldeneye or Goldfinger
- Sort the database by Surname then Forename in ascending order

Forename	Surname	Date of Birth	Colour	Bond Movie	Food
Karl	Fields	19/06/2001	Orange	Goldeneye	Tacos
Edward	Smith	15/03/2001	Orange	Goldfinger	Pizza
Peter	Skeldon	16/08/2000	Purple	Thunderball	Steak
Andrew	Hay	03/02/2000	Blue	The Living Daylights	Pizza

Data Protection Act 1998

The Data Protection Act 1998 was introduced to protect us - as members of the public - from personal data being misused. At National 5 you need to know four different aspects of the law:
- Prior consent of the data subject; the person who the data is about
- Data is used for limited, specifically stated purposes
- Accuracy of data - ensuring that the date is kept up to date and correct
- Data kept safe and secure

Privacy icons created by Freepik - Flaticon

GDPR

GDPR or the General Data Protection Regulation (2016) came into effect in 2018.
It is a law set by the EU but has since been replaced by the UK GDPR.
It is similar to the Data Protection Act:
- processed lawfully, fairly and in a transparent manner in relation to individuals
- used for the declared purpose only
- limited to the data needed for the declared purpose
- accurate
- not kept for longer than necessary
- held securely

GDPR icons created by Freepik - Flaticon

Past Paper Questions

Customers’ personal details have been stolen.

State one requirement of the UK General Data Protection Regulation (GDPR)
that the company should have implemented to prevent this from happening.

1 mark

2025 Q15 (c)

Past Paper Questions

A property renovation company requires a relational database to store information about the tasks to be carried out on each room of a house.
The functional requirements for the database are identified:

Store details of each room in the house.
Store details of each task.
Output a list of tasks carried out by one of the following trades people: electrician, builder, plasterer, decorator, carpenter, plumber.
Output a list of tasks completed for a single room.

Explain why the General Data Protection Regulations do not apply to the
information that will be stored in this database.

1 mark

2022 Q14 (b)

Today's task

Work through the worksheet on Database Design.
Attempt some past paper questions on the topic.

Plenary

For six of the following words, describe what it means

data dictionary

range check

ER diagram

primary key

presence check

validation

foreign key

query

length check

referential integrity

attribute types

presence check

Presentation Overview

Design

23:53 | 22-04-2026

Keywords

DragonDocs Management

Random selector

Remove after pick

Set a timer for how long?

10:00

5:00

3:00

2:30

2:00

1:00

...

Sections

Binary conversion

Denary to binary conversion

Binary to denary conversion

Use two's complement

Slideshow Outline

Help

The following are key shortcuts for using DragonSlides and reveal.js.

Key combination	Action
→, PAGE DOWN, SPACE	Next slide
←, PAGE UP	Previous slide
SHIFT + →	Last Slide
SHIFT + ←	First slide
B	Whiteboard
W	Widget Board/White Screen
C	Toggle Notes Canvas
DEL	Clears Whiteboard/Notes Canvas
S	Show Presenter Mode
SHIFT + D	Toggle Dark Mode
H	Goes to the Home Slide
META + P	Show Print options window

In order to use the 'Start Remote' feature, you'll need to download JB Slide Controls from the Apple App Store. Once you have this you'll be able to control the slides using the app from you Apple Watch.

Feedback 👍

Accessibility

Apply a filter:

Apply theme

Blue theme

White theme

More effects:

Dark Mode

Bold Text

Slide Zoom

All slideshow files