Relational Databases (AQA GCSE Computer Science): Revision Note

Relational Databases

What is a database?

• A Database is a structured, persistent collection of data

• It allows easy storage, retrieval, and management of information

• Electronic databases offer a number of key benefits:

  • Easier to add, delete, modify and update data

  • Data can be backed up and copied easier

  • Multiple users, from multiple locations, can access the same database at the same time

• There are two main types of database

  • Flat file

  • Relational

Flat file database

• A flat file database is one that stores all data in a single table

• It is simple and easy to understand but causes data redundancy, inefficient storage and is harder to maintain

• Consider this example flat file table of students 

  

  • This table has redundant data - the tutor and form room information repeats, this is inefficient

  • If a tutor changed their name we would need to find all instances of that name and change them all

  • Missing any would mean the table had inconsistent data

Relational database

• A relational database is one that organises data into multiple tables 

• It uses keys to connect related data which reduces data redundancy, makes efficient use of storage and is easier to maintain

• A relational database solves the issues in a flat file database:

  • A new table could be created to store the tutor information and the tutor information in the student table could be moved to the new table.  Then a foreign key in the student table (TutorID) could link a student to their tutor

  

  • Now the name of each tutor and their form room is stored only once

  • This means if they change only one piece of data, the data is updated in the entire database and Inconsistency is avoided

Tables & Records

What is a table?

• A table is a complete set of records about the same subject/topic in a database

cars

• An example of a database table named cars

What is a record?

• A record is complete set of fields on a single entity in a table (row)

cars

• An example of a record in the cars table

Fields & Data Types

What is a field?

• A field is a single piece of data in a table (column)

cars

• An example of a field in the cars table

What is a data type?

• A data type is the type of data that can be held in a field and is defined when designing a table

• Examples of common datatypes are:

  • Numeric - whole/decimal

  • Character/string - text data

  • Date/Time 

  • Boolean - true or false values

• In the car table above, the following datatypes would be used:

  • car_id: numeric

  • make: string

  • model: string

  • colour: string

  • price: numeric 

Primary Keys & Foreign Keys

What is a primary key?

• A primary key is a unique field that can be used to identify a record in a table

• order_id is the primary key for the orders table

• customer_id is the primary key for the customers table

What is a foreign key?

• A foreign key is a field in a table that refers to the primary key in another table.

• A foreign key is used to link tables and create relationships

• In the orders table customer_id is a foreign key - it links an order back to the customer that made the order in the customer table

Key database terminology