Cambridge O Level2210

Databases

Computer Science 2210 Chapter Notes

What this chapter covers

Databases
ShareWhatsAppPost
Databases notes

Unable to load PDF

The notes viewer could not load. Please refresh the page.

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Databases notes as text: skim, search, and jump between subtopics.

~12 min read

In this chapter

  1. 1Database Fundamentals: Tables, Records & Fields
  2. 2Choosing the Right Data Type
  3. 3The Primary Key: Unique Identification
  4. 4Introduction to SQL and SELECT...FROM
  5. 5Filtering Data with the WHERE Clause
  6. 6Organising and Aggregating Data (ORDER BY, SUM, COUNT)

1. Database Fundamentals: Tables, Records & Fields

A database is a structured collection of data. Think of it like a digital filing cabinet. Inside, you have 'tables', which are like individual folders for specific categories of information (e.g., a 'Students' table, a 'Teachers' table). Each table is organised into columns and rows. The columns are called 'fields', representing a single piece of information (e.g., 'FirstName', 'Email'). A complete row, containing all the fields for one single entry (e.g., all the information for one student), is called a 'record'. For your exams, you'll focus on single-table databases.

Key term

Record: A complete set of fields in a table relating to a single item or entity.

Examiner insight

Examiners award marks for clearly distinguishing between the terms 'table', 'record', and 'field'. Using an analogy like a spreadsheet, where the whole sheet is the table, a row is a record, and a column header is a field, can help clarify your understanding.

Worked example 13 marks

A vet's clinic wants to create a database to store information about the pets they treat. The information needed is the pet's name, type of animal (e.g., dog, cat), breed, date of birth, and owner's phone number. Identify the table, the fields, and give an example of one record.

  1. 1
    1. Identify the main entity: The database is about pets, so a suitable table name would be 'Pets'.
  2. 2
    1. Identify the individual pieces of data: These will be the fields. The fields are: PetName, AnimalType, Breed, DateOfBirth, OwnerPhoneNumber.
  3. 3
    1. Create an example record: A record is one complete row of data for a single pet. For example: PetName: 'Buddy', AnimalType: 'Dog', Breed: 'Golden Retriever', DateOfBirth: '12/05/2021', OwnerPhoneNumber: '07700900123'.

Recap

  • A database is a structured way of storing data.
  • A table stores data about a single type of thing, like 'Students' or 'Products'.
  • A field is a single piece of data, like 'FirstName', and forms a column in a table.
  • A record is a collection of all fields for one item, and forms a row in a table.

Quick check

  1. In a table of cars, is 'Colour' a field or a record?1 mark
  2. What is a table in a database used for?1 mark

2. Choosing the Right Data Type

Every field in a database table must be assigned a 'data type'. This tells the database what kind of data to expect in that field, which helps ensure data integrity and allows for correct processing. For example, you can't perform mathematical calculations on a field containing text. Choosing the most appropriate data type is a crucial step in database design.

Key term

Data Type: A classification that specifies which type of value a field can hold and what operations can be performed on it.

Examiner insight

Examiners look for the *most* appropriate data type. For a field like 'HouseNumber', even though it's a number, 'Text' is often a better choice as you don't do maths with it and some house numbers can be '14a'.

Common pitfall

Using the Text data type for numbers that you might need to perform calculations on, like price or quantity. While you can store '10' as text, you cannot mathematically add it to another number.

Worked example 16 marks

A database table stores information about library books. The fields are: Title, Author, Genre, IsFiction, PublicationYear, and Price. Suggest a suitable data type for each field.

  1. 1
    1. Title: This will be a string of characters. Data type: Text (or Alphanumeric).
  2. 2
    1. Author: This will also be a string of characters. Data type: Text.
  3. 3
    1. Genre: This is a category name. Data type: Text.
  4. 4
    1. IsFiction: This is a yes/no question. Data type: Boolean (True/False).
  5. 5
    1. PublicationYear: This is a whole number. Data type: Integer.
  6. 6
    1. Price: This will have decimal places. Data type: Real (or Currency/Decimal).

Recap

  • Text/Alphanumeric stores any combination of letters, numbers, and symbols.
  • Integer stores whole numbers (no decimal places).
  • Real stores numbers with decimal places.
  • Boolean stores one of two values, typically True or False.
  • Date/Time stores dates, times, or both.
  • Choosing the correct data type prevents errors and saves memory.

Quick check

  1. What data type would be best for storing a student's exam percentage, e.g., 87.5%?1 mark
  2. Why is it better to store 'NumberOfChildren' as an Integer rather than Text?1 mark

3. The Primary Key: Unique Identification

How does a database tell the difference between two students both named 'John Smith'? It uses a 'primary key'. A primary key is a special field in a table where the value for each record must be completely unique. This acts as a unique identifier for every single row. A good primary key should not change over time. Often, this is a unique ID number, like a StudentID or OrderID, rather than a name or other piece of personal information which could be duplicated.

Key term

Primary Key: A field in a table that uniquely identifies each record in that table.

Common pitfall

Choosing a field that seems unique but isn't guaranteed to be, such as 'FullName' or 'Postcode'. Many people can share a name, and multiple households can share a postcode.

Fun fact

Many websites create a unique User ID number for you when you sign up. This number is the primary key in their user database and is how they can distinguish your account from millions of others, even if you have a common name.

Worked example 13 marks

A table for a car sales company contains the fields: CarID, Make, Model, Colour, RegistrationPlate, Year. Identify the most appropriate primary key and justify your choice.

  1. 1
    1. Evaluate potential keys: 'Make', 'Model', 'Colour', and 'Year' are not unique. There will be many cars with the same values.
  2. 2
    1. Consider 'RegistrationPlate': This is unique to a car in a country, making it a strong candidate. However, registration plates can sometimes be transferred to other cars.
  3. 3
    1. Consider 'CarID': This is likely a unique number generated by the company for each car they process. It is designed specifically to be a unique identifier.
  4. 4
    1. Justify the choice: The most appropriate primary key is 'CarID'. While 'RegistrationPlate' is also a good candidate, a company-generated ID is guaranteed to be unique within their database and will never change for that specific car record. 'RegistrationPlate' is a good *alternative* key, but 'CarID' is the best primary key.

Recap

  • A primary key uniquely identifies every record in a table.
  • No two records can have the same primary key value.
  • The value in a primary key field should not be null (empty).
  • A good primary key is a unique, non-changing value, like an ID number.
  • Names and dates of birth are usually poor choices for primary keys because they are not guaranteed to be unique.

Quick check

  1. State the main purpose of a primary key.1 mark
  2. Why is a person's email address a better choice for a primary key than their last name?2 marks

4. Introduction to SQL and SELECT...FROM

SQL, which stands for Structured Query Language, is the universal language used to communicate with databases. You use it to ask the database questions, which are called 'queries'. The most fundamental query is used to retrieve data. The `SELECT...FROM` statement allows you to do this. You use `SELECT` to list the fields (columns) you want to see, and `FROM` to specify which table to get them from. To select all fields without listing them individually, you can use the asterisk `*` wildcard.

SELECT Field1, Field2 FROM TableName;

SELECT * FROM TableName;

Key term

SQL (Structured Query Language): The standard programming language used for managing and querying relational databases.

Examiner insight

Examiners check for correct syntax. Ensure you list the fields after `SELECT` and the table after `FROM`. Commas are used to separate multiple field names.

Common pitfall

Getting the field or table names wrong. SQL is very precise; 'FirstName' is different from 'firstname'. Always match the names exactly as they are in the table definition.

Worked example 12 marks

You have a table named 'Employees' with the fields: EmployeeID, FirstName, LastName, Department, Salary. Write a SQL query to retrieve only the first name and last name of all employees.

  1. 1
    1. Identify the fields needed: The question asks for 'FirstName' and 'LastName'.
  2. 2
    1. Identify the table: The data is in the 'Employees' table.
  3. 3
    1. Construct the query using SELECT and FROM: `SELECT FirstName, LastName FROM Employees;`

Worked example 21 mark

Using the same 'Employees' table, write a SQL query to retrieve all information for every employee.

  1. 1
    1. Identify the fields needed: The question asks for 'all information'. This is where the `*` wildcard is used.
  2. 2
    1. Identify the table: The data is in the 'Employees' table.
  3. 3
    1. Construct the query: `SELECT * FROM Employees;`

Recap

  • SQL is the language for interacting with databases.
  • `SELECT` is used to specify the columns (fields) you want to retrieve.
  • `FROM` is used to specify the table you are querying.
  • The `*` symbol is a shortcut to select all fields from a table.
  • SQL queries usually end with a semicolon (;).

Quick check

  1. What does the `*` symbol mean in a SQL SELECT statement?1 mark

5. Filtering Data with the WHERE Clause

Often, you don't want to see all the records in a table, but only those that match a specific condition. The `WHERE` clause is used to filter records. It is added after the `FROM` clause and specifies a condition that must be met for a record to be included in the results. You can use comparison operators like `=` (equal to), `>` (greater than), `<` (less than), and `<>` (not equal to). When comparing against text, the value must be enclosed in single quotes, e.g., `WHERE City = 'London'`. You can also combine conditions using `AND` and `OR`.

SELECT Field1 FROM TableName WHERE Condition;

SELECT * FROM TableName WHERE Field2 > 100;

SELECT * FROM TableName WHERE Field3 = 'Value' AND Field4 < 50;

Key term

WHERE clause: A part of a SQL statement that filters records, returning only those that match a specified condition.

Common pitfall

Forgetting to put single quotes around text-based criteria in the WHERE clause, for example writing `WHERE Name = John` instead of `WHERE Name = 'John'`.

Worked example 12 marks

A table named 'Products' has fields: ProductID, ProductName, Category, Price. Write a SQL query to find all products in the 'Electronics' category.

  1. 1
    1. Start with the basic SELECT statement: We want all information, so we use `SELECT * FROM Products`.
  2. 2
    1. Add the filter condition: The condition is that the 'Category' field must be equal to 'Electronics'.
  3. 3
    1. Construct the full query: `SELECT * FROM Products WHERE Category = 'Electronics';` Note the single quotes around 'Electronics'.

Worked example 23 marks

Using the same 'Products' table, write a SQL query to find all products with a 'Price' greater than 50.00 AND that are in the 'Clothing' category.

  1. 1
    1. Start with the SELECT statement: `SELECT * FROM Products`.
  2. 2
    1. Identify the two conditions: `Price > 50.00` and `Category = 'Clothing'`.
  3. 3
    1. Combine the conditions using AND: The query must satisfy both conditions.
  4. 4
    1. Construct the full query: `SELECT * FROM Products WHERE Price > 50.00 AND Category = 'Clothing';`

Recap

  • The `WHERE` clause filters rows (records) based on a condition.
  • Text values in a `WHERE` clause must be inside single quotes (e.g., `'London'`).
  • Numeric values do not need quotes (e.g., `Price > 50`).
  • `AND` requires all connected conditions to be true.
  • `OR` requires at least one of the connected conditions to be true.

Quick check

  1. Which SQL keyword is used to filter records?1 mark
  2. Write a WHERE clause to find all students whose 'Grade' is 9.1 mark

6. Organising and Aggregating Data (ORDER BY, SUM, COUNT)

Beyond just selecting and filtering, SQL can organise and perform calculations on your data. `ORDER BY` is used to sort the results of your query. You can sort in ascending order (from A-Z, or lowest to highest number) using `ASC`, or descending order (Z-A, highest to lowest) using `DESC`. `SUM()` and `COUNT()` are 'aggregate functions' because they operate on a group of records to produce a single result. `SUM(FieldName)` calculates the total of all values in a numeric field. `COUNT(*)` counts the total number of records that match your query, while `COUNT(FieldName)` counts the number of records where that specific field is not empty.

SELECT Field1 FROM TableName ORDER BY Field2 ASC;

SELECT Field1 FROM TableName ORDER BY Field2 DESC;

SELECT SUM(NumericField) FROM TableName;

SELECT COUNT(*) FROM TableName;

SELECT COUNT(Field1) FROM TableName WHERE Condition;

Key term

Aggregate Function: A function in SQL that performs a calculation on a set of values and returns a single summary value.

Examiner insight

Examiners frequently test the combination of aggregate functions with a WHERE clause. For example, 'Count the number of students who passed', which requires both `COUNT(*)` and `WHERE Score >= 40`.

Common pitfall

Confusing SUM and COUNT. SUM adds the values (e.g., total price of all products), while COUNT counts the items (e.g., how many products there are).

Worked example 12 marks

From a 'Students' table with fields 'Name' and 'Score', write a query to list all student names and their scores, from the highest score to the lowest.

  1. 1
    1. Select the required fields: `SELECT Name, Score FROM Students`.
  2. 2
    1. Add the sorting clause: We need to order by the 'Score' field.
  3. 3
    1. Specify the direction: The order should be from highest to lowest, which is descending.
  4. 4
    1. Construct the full query: `SELECT Name, Score FROM Students ORDER BY Score DESC;`

Worked example 22 marks

An 'Orders' table contains an 'OrderTotal' field. Write a query to find the total revenue from all orders.

  1. 1
    1. Identify the function needed: To get a total, use the `SUM()` function.
  2. 2
    1. Identify the field to sum: The field is 'OrderTotal'.
  3. 3
    1. Identify the table: The table is 'Orders'.
  4. 4
    1. Construct the query: `SELECT SUM(OrderTotal) FROM Orders;`

Worked example 32 marks

From a 'Members' table, write a query to find out how many members live in the city of 'Manchester'. The relevant field is 'City'.

  1. 1
    1. Identify the function needed: We need to count records, so use `COUNT()`.
  2. 2
    1. Identify the filter condition: We only want to count members `WHERE City = 'Manchester'`.
  3. 3
    1. Construct the query: `SELECT COUNT(*) FROM Members WHERE City = 'Manchester';`

Recap

  • `ORDER BY` sorts the query results.
  • `ASC` sorts in ascending order (the default), `DESC` sorts in descending order.
  • `SUM()` adds up all the values in a specified numeric column.
  • `COUNT(*)` counts the total number of records returned by a query.
  • Aggregate functions like SUM and COUNT return a single value.

Quick check

  1. What is the difference between `SUM(Quantity)` and `COUNT(Quantity)`?2 marks
  2. Which keyword would you add to a query to sort results alphabetically from Z to A?1 mark

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. Describe the difference between a field, a record, and a table in a database.3 marks
  2. A school stores student data in a table called 'Student'. The fields are StudentID, FirstName, LastName, DateOfBirth, FormGroup, and HasLocker. Identify the most appropriate primary key and justify your choice.2 marks
  3. For the 'Student' table in the previous question, state the most appropriate data type for the 'DateOfBirth' and 'HasLocker' fields.2 marks
  4. A table named 'Films' contains the fields: FilmID, Title, Director, Genre, RunningTime (in minutes). Write a SQL query to select only the Title and Director for all films.2 marks
  5. Using the 'Films' table, write a SQL query to find all films with a 'Genre' of 'Sci-Fi'.2 marks
  6. Write a SQL query to find all films from the 'Films' table with a 'RunningTime' of more than 120 minutes, and display the results ordered by Title alphabetically.3 marks
  7. Explain the purpose of a primary key in a database table.2 marks
  8. A table named 'Sales' contains the fields: SaleID, ProductID, Quantity, SalePrice. Write a SQL query to calculate the total number of sales recorded in the table.2 marks
  9. Using the 'Sales' table, write a SQL query to calculate the total revenue, which is the sum of the 'SalePrice' for all sales of the product with 'ProductID' 501.3 marks
  10. A student writes the following SQL query: `SELECT Name FROM Students WHERE Score = '100'`. The 'Score' field is an Integer data type. Identify the error in the query and write the corrected version.2 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Number systemsTypes and methods of data transmissionComputer architectureTypes of software and interruptsThe internet and the world wide webAutomated systems