Ch 1 · 9 Databases · IGCSE Computer Science 0478
Topic 1 of 1 in Databases — IGCSE Computer Science 0478 — 49 lessons.
Introduction
A school keeps records of every student; a shop keeps records of every product. A database stores such data in an organised way so that it can be searched and summarised quickly with a language called SQL. (Syllabus topic 9.)
Key ideas — Tables, records and fields
A single-table database stores data in a table:
🧑🏫 Teacher's explanation
Imagine you are organizing a school yearbook. If you just threw all the student information into a pile, you'd never find anything. A database table is like a structured spreadsheet designed to keep this information organized and searchable.
📖 Definitions
🔍 Worked example — Easy
A library database has a table called Books. Identify the most suitable data type for the following fields: Title, Price, IsBorrowed, and PublicationYear.
🔍 Worked example — Standard
Given the table Staff, identify the primary key and explain why it is suitable.
🔍 Worked example — Exam-style
Consider a table Sales with fields SaleID, Product, Price, and Quantity. Write an SQL query to find the total sum of Price for all sales where the Product is 'Laptop' and the Quantity is greater than 5.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Identify the most suitable primary key for this table. [1] (b) Suggest a suitable data type for the YearPublished field. [1] (c) Write an SQL statement to display the Title of all books where IsAvailable is TRUE, ordered by Title in ascending order. [3] [5 marks]
Key ideas — Data types
🧑🏫 Teacher's explanation
Imagine you are organizing a massive library. If you throw every book into one giant pile, you can never find anything. To make it useful, you need a system. You create a shelf for 'Fiction', a shelf for 'Science', and within those, you create 'Records' (the individual books) and 'Fields' (the specific details like Title, Author, or ISBN).
📖 Definitions
🔍 Worked example — Easy
Identify the most suitable data type for the following fields in a 'Student' table: 1. StudentID, 2. DateOfBirth, 3. IsEnrolled.
🔍 Worked example — Standard
A database table 'Products' has the following fields: ProductCode, ProductName, UnitPrice, QuantityInStock, ReorderRequired. Suggest the most appropriate data type for each.
🔍 Worked example — Exam-style
Given a table 'Employees' with fields: EmpID (Primary Key), Name, Salary, HireDate, and IsFullTime. Determine the data type for 'Salary' and 'HireDate'.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Identify the most suitable data type for the 'Price' field. [1] (b) Identify the most suitable data type for the 'IsAvailable' field. [1] (c) Suggest a suitable Primary Key for the 'Books' table and justify your choice. [2] [4 marks]
Key ideas — Primary key
A primary key is a field that uniquely identifies each record in the table: no two records can have the same value in it, and it cannot be empty.
🧑🏫 Teacher's explanation
Imagine you are in a massive school assembly with 1,000 students. If the teacher shouts, "Hey, John Smith, come up here!", there might be five different John Smiths in the room. The teacher has no way of knowing exactly which one they want. This is a data collision.
📖 Definitions
🔍 Worked example — Easy
Identify the most suitable primary key for a table containing the following fields: FirstName, LastName, DateOfBirth, PhoneNumber.
🔍 Worked example — Standard
A library database uses BookTitle as a primary key. Explain why this is a poor choice.
🔍 Worked example — Exam-style
A table has 4 records with the following ID values: 101, 102, 103, 104. A new record is added with ID 102. State whether the primary key constraint is violated.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Identify which field should be used as the primary key. [1] (b) Explain why the LastName field is not suitable as a primary key. [2] [3 marks]
Key ideas — SQL: querying the table
Structured query language (SQL) is used to query (search) the data.
🧑🏫 Teacher's explanation
Imagine you are looking at a massive school yearbook. The yearbook is your database, and each page is a record containing information about a single student. If you want to find only the students in 'Year 10' who play 'Football', you don't want to read every single page manually. Instead, you use a magic filter to pull out exactly what you need.
📖 Definitions
🔍 Worked example — Easy
Given a table Products with fields ProductID, ProductName, Price, and StockLevel. Write an SQL query to show the ProductName and Price for all products where the Price is greater than $10.00.
🔍 Worked example — Standard
A table Staff has fields StaffID, Name, Department, and Salary. Write an SQL query to show the Name of all staff in the 'Sales' department, sorted by Name in descending order.
🔍 Worked example — Exam-style
Consider the Sales table with fields SaleID, Amount, and CustomerType. Calculate the total sum of all Amount values where CustomerType is 'VIP' OR 'Gold'.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
Write an SQL query to display the ItemName and Quantity for all items where the Category is 'Electronics' AND the Price is greater than $500. [3 marks]
Worked example
A table Products has fields ProductCode, Name, Price and Stock. Write an SQL query to show the name and price of every product with fewer than 10 in stock, cheapest first.
🔍 Worked example — Easy
A table Students has fields StudentID (Primary Key), FirstName, LastName, and Age. Write an SQL query to show the FirstName and LastName of all students where Age is greater than 16.
🔍 Worked example — Standard
A table Sales has fields SaleID, Product, Amount, and Date. Write an SQL query to show the Product and Amount for all sales where the Product is 'Laptop' OR the Amount is greater than 500, ordered by Amount in descending order.
🔍 Worked example — Exam-style
A table Inventory contains: \n| ItemID | ItemName | Category | Quantity | Price |\n|---|---|---|---|---|\n| 1 | Desk | Furniture | 5 | 150.00 |\n| 2 | Chair | Furniture | 12 | 45.00 |\n| 3 | Lamp | Decor | 3 | 25.00 |\n| 4 | Rug | Decor | 8 | 60.00 |\n\nIdentify the output of the following SQL: \nSELECT ItemName, Price\nFROM Inventory\nWHERE Category = 'Decor' AND Quantity < 10\nORDER BY Price ASC;
⚠️ Common errors
Common mistakes
Practice
Answers: (1) A record is all the data about one item (a row); a field is one item of data stored for every record (a column). (2) (a) text; (b) real; (c) Boolean; (d) date/time. (3) StudentID is unique for every student; two students could have the same first name. (4) Bilal. (5) SELECT COUNT(StudentID) FROM Students WHERE Form = '9A';