Slides badge

Relational databases

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).
  • Understand what a relational database is
  • Understand what relationships between tables in a database are
  • Understand the benefits of relational databases
  • I can explain the term relational database
  • I can discuss the pros and cons of relational databases

Flat file database vs relational database

  • A flat file database has only one table.
  • A relational database has multiple tables linked together.
  • We have looked at flat file databases so far.

Relational databases

  • A relational database is a collection of tables containing data, which are related to each other through common items.
  • This provides maximum flexibility and is the most efficient database design.
  • Logical links between tables are called relationships.
Database icons created by Eucalyp - Flaticon

A flat file database

  • Customer ID
  • First Name
  • Surname
  • Address
  • Telephone Number
  • Item
  • Quantity
  • Cost
  • CustomerID

A relational database

The relationship (the foreign key)

  • Customer
    • Customer ID
    • First Name
    • Surname
    • Address
    • Telephone Number
  • Order
    • Order ID
    • Item
    • Quantity
    • Cost
    • CustomerID

What are the benefits of separating data?

  • Reduced duplication – by having the customers details separate from an order we don’t have to repeat all the information on each order, saving on database storage
  • Simplicity – it’s far easier to create an order record if we design it this way, we only need to add four fields instead of eight
  • Updating – if the customer’s details change, only one record is changed (the one in the Customer table) and not every single order.

Keys

  • Each record in a database has a unique identifying field. We call this the primary key.
  • When we create a relational database, the primary key is really important.
  • Both tables should have a primary key.
  • One table should link to another table through the foreign key.
  • If you have more than one field that could be a primary key, you probably need to split the table into two or more tables.
Key icons created by Smashicons - Flaticon

Keys

  • Each record in a database has a unique identifying field. We call this the primary key.
  • When we create a relational database, the primary key is really important.
  • Both tables should have a primary key.
  • One table should link to another table through the foreign key.
  • If you have more than one field that could be a primary key, you probably need to split the table into two or more tables.
  • Customer
    • Customer ID
    • First Name
    • Surname
    • Address
    • Telephone Number
  • Order
    • Order ID
    • Item
    • Quantity
    • Cost
    • CustomerID
  • Customer
    • Customer ID
  • Order
    • Order ID
    • Item
    • Quantity
    • Cost
    • CustomerID
  • Order
    • Order ID
    • Item
    • Quantity
    • Cost
    • CustomerID
Key icons created by Smashicons - Flaticon

Keys - WHICH field IS THE PRIMARY KEY?

PupilName

SQANumber

DateOfBirth

ClassID

PupilName SQANumber DateOfBirth ClassID
John Smith 314124 13/07/08 3
Andrew Andrews 732322 12/07/09 4
Adam Adamson 321654 14/07/09 3
Peter Peterson 423422 11/07/09 4

Keys - WHICH field IS THE primary KEY?

Subject

ClassID

Teacher

Room

ClassID Subject Teacher Room
1 Computing Mr Computer P8
2 Business Mr Entreprenuer L3
3 Maths Mrs Maths L4
4 English Mrs Lit L5

Keys - WHICH field IS THE foreign KEY?

Subject

ClassID

Teacher

Room

ClassID Subject Teacher Room
1 Computing Mr Computer P8
2 Business Mr Entreprenuer L3
3 Maths Mrs Maths L4
4 English Mrs Lit L5
PupilName SQANumber DateOfBirth ClassID
John Smith 314124 13/07/08 3
Andrew Andrews 732322 12/07/09 4
Adam Adamson 321654 14/07/09 3
Peter Peterson 423422 11/07/09 4

PupilName

SQANumber

DateOfBirth

Understanding relationships

  • A relational database simply wouldn’t be relational without relationships.
  • A relationship is a logical connection between one table and another. The connection is made between a field in one table and a field in another table.

Understanding relationships

  • One branch of a bank will contain many customer accounts.
  • By relating the BranchNumber field of the Branch table to the BrunchNumber field in the Accounts table, you are creating a “One-to-Many” relationship between the two tables.
  • There are three different types of relationship you can define between tables – One-to-Many, One-to-One, and Many-to-Many.

One-to-Many relationship

  • A One-to-Many relationship occurs when one table, known as the parent table, has many matching records in another table, known as the child table. In the example before, the Branch parent has many matching records in the Account child table.
  • One-to-Many relationships link ONE record in the first table to MANY records in the second table – this is the most common table relationship.
  • One teacher teaches many pupils.

One-to-Many relationship

One-to-One relationship

  • A One-to-One relationship occurs when there is a direct match between a record in one table and a record in another.
  • For example, are two tables in the following examples - a customer table and an address table. The business logic behind the relationship states that a customer may only have one address at any time.
  • Every single record in the “Customer” table therefore directly matches one record in the “Address” table.
  • One-to-One relationships link ONE record in the first table to exactly ONE record in the second table.

One-to-One relationship

Many-To-Many relationship

  • A Many-to-Many relationship occurs when one table can have many possible matches with records of another table, and vice versa. To model this relationship in Access, you must create a third table, known as a junction table, and reduce the Many-to-Many relationship between two tables into two One-to-Many relationships – using the junction table as the intermediary:
  • Many-to-Many relationships link MANY records in the first table to MANY records in the second table.
  • A junction table keeps track of related records in two other tables.

Foreign keys

  • When you relate two tables you are making a link between a primary key and a foreign key.
  • A foreign key is the field in a table that exactly matches the primary key field of another table.
  • For example, to relate the “Cars” table with another table called “CarSales”, containing records of all cars sold in a particular dealership, then you could include a “Vehicle Identification Number”  field in the “CarSales” table as the foreign key. By relating the two VIN fields you are linking the information in both tables in a logical manner.

Keys

Primary keys

  • A primary key is assigned to a field which is used to uniquely identify a particular piece of information on the database so that you can find a particular record in a file as quickly as possible.
  • E.g. The Scottish Qualifications Authority will have a database of all the students who are sitting National or Higher exams. The primary key field in the database would be the candidate number as  each number can only belong to one person - therefore it is unique to them.

Creating relationships

Creating relationships

Ensure that you Enforce Referential Integrity

 

You'll learn more about this at National 5 level.

Creating relationships

Creating relationships

  • Create a database file.
  • Create two tables:
    • Products [ProductID,  ProductDescription, ProductPrice, ProductManufacturer]
    • Sales [SaleID, ProductID, SaleQuantity, Purchaser]
  • Populate the tables with some made up arbitray data.
  • Create the relationship between them
Download the Vet database. Add in a relationship between the Vet and Pet and the Pet and the Pet and Customer.
 
Complete the search tasks in the worksheet.

Task

Presentation Overview
Close
JB
Relational databases
© 2020 - 2026 J Balfour
05:16 | 15-02-2026
Join Live Session
Go Live
Start Remote
Save Progress
Slideshow Outline
Presenter Mode
Bullet Only Mode
Generate Quiz
Generate Lesson Plan
Widget Screen
Canvas Controls
Fullscreen
Random Selector
Timer
Volume Meter
Binary Converter
Python Editor
Show Knox 90
Provide Feedback
Help
!
Keywords
    DragonDocs Management
    Random selector
    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
      Feedback 👍
      Accessibility

      Apply a filter:

      Apply theme

      Blue theme
      White theme

      More effects:

      Dark Mode
      Bold Text
      Slide Zoom
      ×
      Loading
      All slideshow files