Cambridge IGCSE0478

Databases

Computer Science 0478 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.

~10 min read

In this chapter

  1. 1Database Structure: Tables, Fields, and Records
  2. 2Choosing the Right Data Type
  3. 3The Role of the Primary Key
  4. 4Basic SQL Queries: SELECT and FROM
  5. 5Filtering Data with the WHERE Clause
  6. 6Sorting and Aggregating Data

1. Database Structure: Tables, Fields, and Records

A database is an organised collection of data, stored electronically. Think of it like a digital filing cabinet where information is easy to find. The data is structured into tables. A table holds data about a single type of item, like 'Students' or 'Products'. Each table is made up of fields, which are the columns that define what information is stored (e.g., 'FirstName', 'Price'). A complete row of data, containing all the fields for one specific item, is called a record. For example, all the information about one particular student is a single record.

Key term

Record: A single row in a database table, representing a complete set of information about one individual item or entity.

Examiner insight

Marks are often awarded for correctly identifying the number of records and fields in a given table, so practice counting rows (for records) and columns (for fields).

Common pitfall

Confusing a 'field' with 'data'. A field is the column heading (e.g., 'EmailAddress'), whereas the data is the specific value in that column for a given record (e.g., 'student@example.com').

Worked example 13 marks

The table below, named 'Pets', is used by a vet.

PetIDPetNameAnimalTypeAge
P01BuddyDog5
P02WhiskersCat2
P03RockyDog8

Identify:a) The names of all the fields.b) The full record for the pet named 'Whiskers'.c) The total number of records in the table.

  1. 1

    a) The fields are the column headings: PetID, PetName, AnimalType, and Age.

  2. 2

    b) The record for 'Whiskers' is the entire row of data for that pet: {P02, Whiskers, Cat, 2}.

  3. 3

    c) There are three rows of data (excluding the header), so there are 3 records in the table.

Recap

  • A database stores data in one or more tables.
  • A table contains data about a single topic, like 'Customers'.
  • A field is a column in a table that stores a single category of information, like 'Postcode'.
  • A record is a row in a table that contains all the information about one item.

Quick check

  1. If a table has 5 columns and 20 rows of data, how many fields and records does it have?2 marks

2. Choosing the Right Data Type

Every field in a database table must be assigned a data type. The data type tells the database what kind of data to expect in that field, such as text, numbers, or dates. Choosing the correct data type is crucial for ensuring data is stored correctly (data integrity), using memory efficiently, and allowing for correct operations like calculations. For example, you can't perform mathematical addition on a field stored as text. Common data types include Text, Integer, Real, Boolean, and Date/Time.

Key term

Data Type: A classification that specifies which type of value a field can hold, such as integer, text, or Boolean.

Common pitfall

Using the 'Integer' data type for values like phone numbers. While they contain numbers, you don't do maths with them and they can have leading zeros, so 'Text' is a better choice.

Fun fact

The Y2K bug was a massive data type problem! Many old systems stored years as two digits (e.g., 99 for 1999). When 2000 arrived ('00'), computers could interpret it as 1900, causing widespread fears of system failures.

Worked example 14 marks

A school wants to create a database table to store student information. For each piece of information below, suggest a suitable field name and the most appropriate data type:

  • First name
  • Date of birth
  • Is the student a bus user? (yes/no)
  • Number of detentions this year
  1. 1

    First name: Field name could be 'FirstName'. Data type should be Text, as it will store a name which is a string of characters.

  2. 2

    Date of birth: Field name could be 'DateOfBirth'. Data type should be Date/Time to store the date correctly and allow for age calculations.

  3. 3

    Is the student a bus user?: Field name could be 'IsBusUser'. Data type should be Boolean, as it's a simple True/False or Yes/No value.

  4. 4

    Number of detentions: Field name could be 'DetentionCount'. Data type should be Integer, as it will be a whole number used for counting.

Recap

  • Text/Alphanumeric is for letters, numbers, and symbols, like an address.
  • Integer is for whole numbers, like 'StockLevel'.
  • Real (or Decimal/Float) is for numbers with decimal points, like 'Price'.
  • Boolean is for values that can only be True or False.
  • Date/Time is for storing dates, times, or both.

Quick check

  1. What data type would you use for a field storing the price of an item, like £9.99?1 mark
  2. Why is 'Boolean' a good data type for a field called 'IsActive'?1 mark

3. The Role of the Primary Key

Imagine a school with two students named 'Jane Smith'. How do you tell them apart in a database? This is where the primary key comes in. A primary key is a special field in a table chosen to uniquely identify each and every record. Every value in the primary key column must be unique, and it cannot be left blank (it cannot be NULL). Often, a special ID field is created for this purpose, like a 'StudentID' or 'ProductID', as this guarantees uniqueness where a name or date of birth might not.

Key term

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

Examiner insight

When asked to justify your choice of primary key, always use the phrase 'it is a unique identifier for each record'.

Common pitfall

Choosing a field that could have duplicates as a primary key. For example, 'LastName' is a poor choice because multiple people can have the same last name.

Worked example 12 marks

A library database has a 'Books' table with the fields: ISBN, Title, Author, Genre, PublicationYear. Which field is the most suitable choice for the primary key? Justify your answer.

  1. 1

    The most suitable field for the primary key is 'ISBN'.

  2. 2

    Justification: An ISBN (International Standard Book Number) is a unique code assigned to every published book. Therefore, it will be different for every single record in the 'Books' table, fulfilling the requirement of a primary key to be a unique identifier.

Recap

  • A primary key uniquely identifies every record in a table.
  • Primary key values cannot be repeated within the table.
  • A primary key field cannot be left empty (it cannot be NULL).
  • Fields like 'FirstName' or 'City' are poor choices for primary keys as they can contain duplicates.

Quick check

  1. State the two main properties of a primary key field.2 marks

4. Basic SQL Queries: SELECT and FROM

SQL, or Structured Query Language, is the standard language used to communicate with databases. To get data out of a database, we write a 'query'. The most basic query uses the `SELECT` and `FROM` statements. `SELECT` tells the database which fields (columns) you want to see. `FROM` tells it which table to look in. To select all fields from a table without listing them one by one, you can use the asterisk `*` wildcard character.

SELECT FieldName1, FieldName2 FROM TableName;

SELECT * FROM TableName;

Key term

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

Examiner insight

Examiners check for exact syntax. Field and table names are often case-sensitive, so copy them exactly as they appear in the question.

Common pitfall

Forgetting the comma between multiple field names (e.g., `SELECT ProductName Price`) or putting a comma after the last field name before `FROM`.

Worked example 11 mark

Using a 'Products' table with fields `ProductID`, `ProductName`, `Category`, `Price`, write an SQL query to show only the names of all products.

  1. 1

    SELECT ProductName FROM Products;

Worked example 22 marks

Using the same 'Products' table, write an SQL query to show the product name and price for all items.

  1. 1

    SELECT ProductName, Price FROM Products;

Worked example 31 mark

Using the 'Products' table, write an SQL query to show all information about every product.

  1. 1

    SELECT * FROM Products;

Recap

  • SQL stands for Structured Query Language.
  • `SELECT` specifies the fields you want to retrieve.
  • `FROM` specifies the table you are querying.
  • Use a comma to separate multiple field names in a `SELECT` list.
  • Use `*` as a shortcut to select all fields from a table.

Quick check

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

5. Filtering Data with the WHERE Clause

Often, you don't want to see every record in a table. The `WHERE` clause allows you to filter your results and retrieve only the records that match a specific condition. It always comes after the `FROM` clause. You can use comparison operators like `=` (equal to), `>` (greater than), `<` (less than), and `<>` (not equal to). Remember that text values in a condition must be enclosed in single quotes, like `'London'`, while numbers do not need quotes.

SELECT field(s) FROM TableName WHERE condition;

Key term

WHERE clause: An SQL clause used to filter records, extracting only those that fulfill a specified condition.

Common pitfall

Forgetting to put single quotes around text values in the WHERE clause, for example writing `WHERE Category = Electronics` instead of the correct `WHERE Category = 'Electronics'`.

Worked example 12 marks

From the `Products` table (`ProductID`, `ProductName`, `Category`, `Price`), write an SQL query to find the names of all products that are in the 'Games' category.

  1. 1

    SELECT ProductName FROM Products WHERE Category = 'Games';

Worked example 22 marks

From the `Products` table, write an SQL query to find the names and prices of all products that have a price of more than 50.00.

  1. 1

    SELECT ProductName, Price FROM Products WHERE Price > 50.00;

Recap

  • The `WHERE` clause filters records based on a condition.
  • It is placed after the `FROM` clause in an SQL query.
  • Comparison operators like `=`, `>`, `<`, `<>` are used to build conditions.
  • Text values in a `WHERE` clause must be inside single quotes (e.g., `'Jumper'`).
  • Numeric values do not need quotes (e.g., `Price > 50`).

Quick check

  1. Write the SQL to find all details for products with a price of exactly 19.99 from a `Products` table.2 marks

6. Sorting and Aggregating Data

SQL can do more than just retrieve data; it can organize and calculate it. The `ORDER BY` clause is used to sort your results. You can sort by any field, in ascending order (`ASC`) or descending order (`DESC`). If you don't specify, `ASC` is the default. Aggregate functions perform a calculation on a set of rows and return a single summary value. The two you need to know are `COUNT()`, which counts the number of records, and `SUM()`, which calculates the total of a numeric field.

SELECT ... FROM ... ORDER BY field ASC|DESC;

SELECT COUNT(*) FROM TableName;

SELECT SUM(numeric_field) FROM TableName;

Key term

Aggregate Function: A function, such as SUM or COUNT, that performs a calculation on a set of values and returns a single summary value.

Examiner insight

When a question asks for the 'highest' or 'lowest' of something, it's a clue that you need to use `ORDER BY ... DESC` or `ORDER BY ... ASC`.

Common pitfall

Confusing `SUM` and `COUNT`. `SUM` adds up the values in a column (e.g., total price of all items), while `COUNT` counts how many rows there are (e.g., how many items exist).

Worked example 12 marks

From a `Products` table, list the names and prices of all products, from most expensive to least expensive.

  1. 1

    SELECT ProductName, Price FROM Products ORDER BY Price DESC;

Worked example 22 marks

From a `Students` table, write a query to find out how many students are in 'Tutor Group 11B'.

  1. 1

    SELECT COUNT(*) FROM Students WHERE TutorGroup = '11B';

Worked example 31 mark

From a `Products` table with a `StockLevel` field, calculate the total number of items in stock across all products.

  1. 1

    SELECT SUM(StockLevel) FROM Products;

Recap

  • Use `ORDER BY FieldName DESC` to sort from highest to lowest.
  • Use `ORDER BY FieldName ASC` or just `ORDER BY FieldName` to sort from lowest to highest.
  • `COUNT(*)` counts the total number of records returned by a query.
  • `SUM(FieldName)` adds up all the values in a specified numeric field.
  • Aggregate functions like `SUM` and `COUNT` return a single value, not a list of records.

Quick check

  1. Which SQL keyword is used to sort results in descending order?1 mark
  2. What is the difference between `SUM(Stock)` and `COUNT(Stock)` on a products table?2 marks

End-of-chapter exercise

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

  1. A table named `Employees` has the following fields: `EmployeeID`, `FirstName`, `LastName`, `Department`, `Salary`, `StartDate`. How many fields does this table have?1 mark
  2. For the `Employees` table described above, what would be the most appropriate data type for the `Salary` field, which stores values like 45000.50?1 mark
  3. Explain why `LastName` is not a suitable choice for a primary key in the `Employees` table.2 marks
  4. Using the `Employees` table, write an SQL query to select the `FirstName`, `LastName`, and `Department` for all employees.2 marks
  5. Using the `Employees` table, write an SQL query to find all information about employees who work in the 'Sales' department.2 marks
  6. Using the `Employees` table, write an SQL query to find the `FirstName` and `Salary` of all employees who earn more than 50000.2 marks
  7. Write an SQL query to display the `FirstName`, `LastName`, and `Salary` of all employees in the 'IT' department, sorted by salary from highest to lowest.3 marks
  8. Write an SQL query to count the total number of employees in the `Employees` table.2 marks
  9. A new 'CarPark' table is needed to track employee parking. It needs to store a unique permit number, the employee's ID it is assigned to, the car's registration plate, and whether the permit is currently active. Identify suitable field names, data types, and a primary key for this table.4 marks
  10. Using the `Employees` table, write an SQL query to calculate the total annual salary bill for the entire company by adding up all values in the `Salary` field.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