Implementation

To implement relational databases to match a design with referential integrity
To implement SQL queries including create, read, update and delete queries
To implement an equi-join in SQL
How to read and understand SQL queries

Be able to create a relational database with referential integrity
Be able to explain the importance of referential integrity
Be able to create and read SQL statements

Database setup

When you have created your database, click on View at the top and switch to Design View.

Now enter your field names and set the field types on the right.

Database Setup - Setting up validation

Length check

Presence check

Range check

Note - Number data types cannot have a field size - they are determined based on their data type (e.g. Integer or Double)

Database setup - Setting up validation - restricted choice

Select the Lookup Wizard as the field type.

Database setup - Primary keys

You will often need to change the primary key.
To do this, right click the grey area to the left of the field and select Primary Key to make it the primary key of the table.

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!

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 (infinitly) many books

(or (infinitly) 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 (infinitly) many books

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

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

Database setup - relationships

With relational databases, you need to be able to create the relationship yourself.

Database setup - relationships

With relational databases, you need to be able to create the relationship yourself.
To do this, make sure you close all tables in the relationships and then click on Database Tools then Relationships
Then just drag from the foreign or primary to the opposite table. At National 5 you only need to create a relationship with two tables!
Ensure you enforce referential integrity!

Referential integrity

With relational databases, you need to be able to create the relationship yourself.
To do this, make sure you close all tables in the relationships and then click on Database Tools then Relationships
Then just drag from the foreign or primary to the opposite table. At National 5 you only need to create a relationship with two tables!
Ensure you enforce referential integrity!

Referential integrity ensures that any foreign key in one table must match an existing primary key in another table. If it does not, the integrity of the database is breached.

Database icons created by Eucalyp - Flaticon

Searching and sorting

Searching and sorting a database are the main reasons why database are used.
We call this querying the database.

Query icons created by Freepik - Flaticon

Running queries

We can easily create queries in access by stating the fields we want back (or all of them with the *)
It will run the query and join both tables that the relationship has already established.

Create and populate with fake information the following databases from these tables (and in any validation required):

- Books table (ISBN, Title, Author, and Genre [Restricted Choice])
- Loans table (LoanID, ISBN, PupilName, ReturnDate)
- Teams (TeamID, TeamName, AgeGroup, Coach)
- Players (PlayerID, TeamID, PlayerName, Position)
- Products (ProductID, ProductName, Price, Category [Restricted Choice])
- Order (OrderID, ProductID, CustomerName, Quantity)

SQL

SQL or Structured Query Language is a declarative, non-procedural programming language.
This means that you can't create things like loops or selection in it.
SQL is made up of commands specific to databases that allow the user to query the database.
It is designed to be simple and highly descriptive to the point where it reads almost like English.

Sql icons created by Smashicons - Flaticon

Why do we need SQL?

Around 90-95% of all production databases run on a headless server - that is a server without a display.
These servers are connected to remotely and commands are run on them.
Since we can't use a graphical interface with them, SQL commands are used through typing a command.
Additionally, programming languages such as PHP, which provide a backend to the web, must be able to access databases and to be able to do this they need to be able to run queries on the database simply with code.

Sql icons created by Smashicons - Flaticon

What does SQL do?

SQL carries out the four main functions of a database via command line, known as CRUD:
- Create - INSERT INTO
- Read - SELECT
- Update - UPDATE
- Delete - DELETE FROM

Sql icons created by Smashicons - Flaticon

Our database

CPUs
CPUID 🔑	CPUName	TOPS	ChipPrice	Developer
1	Ryzen 9 AI HX 370	80	350.00	2
2	Core Ultra 9 14900K	51	450.00	1
3	M3 Max	38	600.00	5
4	Snapdragon X	45	350.00	3
5	Ryzen 7 7840U	16	280.00	2
6	Core Ultra 7 155H	11	320.00	1

Developer
ID🔑	DeveloperName	Country	CEO
1	Intel	USA	Pat Gelsinger
2	AMD	USA	Lisa Su
3	Qualcomm	China	Cristiano Amon
4	Samsung	South Korea	Kyung Kye-hyun
5	Apple	USA	Tim Cook
6	IBM	Taiwan	Arvind Krishna

INSERT queries

An INSERT query is the create part of CRUD.
It is the simplest of the queries and the second most commonly executed of them.
An INSERT query allows us to enter data into a database.

Create icons created by Muhammad Atif - Flaticon

INSERT INTO CPUs VALUES (7, "Ryzen 9 7950X3D", 12, 420, 2);

reate

Our database now

Since we have inserted something into our database, the database has now changed:

CPUs
CPUID 🔑	CPUName	TOPS	ChipPrice	Developer
1	Ryzen 9 AI HX 370	80	350.00	2
2	Core Ultra 9 14900K	51	450.00	1
3	M3 Max	38	600.00	5
4	Snapdragon X	45	350.00	3
5	Ryzen 7 7840U	16	280.00	2
6	Core Ultra 7 155H	11	320.00	1

AMD Ryzen 9 7950X3D

420.00

reate

SELECT queries

An SELECT query is the read part of CRUD.
It is the most commonly used query but sometimes considered the most complex.
An SELECT query allows us to read data out of the database. We can sort the output of the data.

Database icons created by pojok d - Flaticon

ead

SELECT queries

The structure of a SELECT query is:

SELECT CPUName, ChipPrice
FROM CPUs
ORDER BY CPUName;

ead

SELECT fieldname1, fieldname2, fieldname3
FROM table1, table2
WHERE condition
ORDER BY field1 ASC/DESC, field2 ASC/DESC

SELECT *
FROM CPUs
WHERE TOPS > 25;

SELECT queries

SELECT *
FROM CPUs
WHERE TOPS > 25
ORDER BY ChipPrice ASC, CPUName ASC;

ead

CPUs
CPUID 🔑	CPUName	TOPS	ChipPrice	Developer
1	Ryzen 9 AI HX 370	80	350.00	2
4	Snapdragon X	45	350.00	3
2	Core Ultra 9 14900K	51	450.00	1
3	M3 Max	38	600.00	5

Equi-join

ead

An equi-join allows data from multiple linked tables in a database to be queried together.
For this to work, there must be a foreign key and primary key that match.

CPUs
Developer
2
1
5
3
2
1

Developer
ID🔑
1
2
3
4
5
6

Foreign key

Primary key

Equi-join

ead

In SQL, to make an equi-join we need to use the condition in the WHERE part of our query:

CPUs
Developer
2
1
5
3
2
1

Developer
ID🔑
1
2
3
4
5
6

Foreign key

Primary key

SELECT *
FROM table1, table2
WHERE table1.foreignKey = table2.primaryKey
ORDER BY table1.foreignKey

Equi-join

SELECT CPUID, CPUName, TOPS, DeveloperName, Country
FROM CPUs, Developer
WHERE CPUs.Developer = Developer.ID AND TOPS > 25
ORDER BY ChipPrice ASC, CPUName ASC;

ead

CPUs
CPUID 🔑	CPUName	TOPS
1	Ryzen 9 AI HX 370	80
4	Snapdragon X	45
2	Core Ultra 9 14900K	51
3	M3 Max	38

Developer
DeveloperName	Country
AMD	USA
Qualcomm	China
Intel	USA
Apple	USA

Update

UPDATE CPUs
SET ChipPrice = 400.00, ChipName = "Core Ultra 9 14900K (used)"
WHERE CPUID = 2

pdate

Changing the contents of a database is done with UPDATE command.
In this example, we're discounting a product and changing it's name to reflect it.

Update icons created by Freepik - Flaticon

oUR DATABASE NOW

pdate

CPUs
CPUID 🔑	CPUName	TOPS	ChipPrice	Developer
1	Ryzen 9 AI HX 370	80	350.00	2
2	Core Ultra 9 14900K (used)	51	400.00	1
3	M3 Max	38	600.00	5
4	Snapdragon X	45	350.00	3
5	Ryzen 7 7840U	16	280.00	2
6	Core Ultra 7 155H	11	320.00	1
7	AMD Ryzen 9 7950X3D	12	420.00	2

Delete

elete

The final SQL command you need to know about and the last part of the CRUD paradigm is DELETE.
As you can imagine DELETE removes something from the database:

DELETE FROM CPUs
WHERE CPUID = 4

DELETE FROM CPUs
WHERE ChipPrice > 350.00

Past Paper Questions

A new driver is to be added.

INSERT INTO Driver (driverNum, forename, surname, wins, points, teamID) VALUES (99, "Thomas", "Webb", 0, 0, "PC81")

When this SQL statement is run, an error message is displayed stating that the record cannot be added.

Explain why an error message is displayed.

1 mark

2025 16 (d)

Team
teamID	teamName	titlesWon
RR32	Rapid Racers	7
SS21	Swift Streaks	3
TT16	Turbo Titans	2
VR12	Velocity Vipers	4
ER54	Elite Racers	1
PV81	Prime Speed	5

Driver
driverNum	forename	surname	wins	points	teamID
1	Shannon	Kelly	5	125	ER54
3	Ezri	Wuzik	2	50	PV81
4	Jackie	Price	2	50	RR32
11	Kai	West	4	100	SS21
14	Charlie	Wilkinson	1	25	TT16
18	Moss	Gray	0	0	VR12

Past Paper Questions

The relational database is implemented. Sample data from both tables is
shown.

The database is used to identify all customer names and addresses that have 5 or more windows and are allocated to staff member Fatima Khan.

Produce an SQL statement that would produce this output.

2023 14 (b) (i)

House
houseID	customerName	address	directDebit	windows	cost	staffID
BON4523	Claire Banks	168 Ferry Place	Yes	6	13	1613
DAL8001	Stephen Jones	7F Millbank	No	3	7	1752
EDI3189	Ian Collins	15 Robin Ave	Yes	8	12	1613
...	...	...	...	...	...	...

Staff
staffID	staffName	contactTel	hoursPerWeek
2160	Daniel Buchan	07721454187	35
1613	Jessie Tait	07378565190	15
1752	Nick Haig	07745176623	27.5
1617	Dagmara Dyner	07178251143	35
...	...	...	...

Now that you have covered SQL and Database Design, you can work through the practical tasks. By covering both of these topics, you have covered most of the content for databases.
Work through some of the past papers on Achieve when you have finished.

Presentation Overview

Implementation

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