Relational Databases (AQA GCSE Computer Science)

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

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

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 

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