8 Databases and SQL
Model related information with keys and tables, explain DBMS functions, and read and write the syllabus SQL subset.
Mapped to Cambridge International AS & A Level Computer Science 9618, 2027–2029 Version 2. These original lessons and self-checks are independent study support; they are not Cambridge papers, official marks or grades. The course does not yet provide a timed four-paper simulation or a locked-down Paper 4 programming environment.
Before you begin
- Use the 2027–2029 syllabus for your exam year. AS candidates study Sections 1–12; A Level candidates study Sections 1–20. Paper 4 uses a programming language chosen from Python, Java or Visual Basic in console mode.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
What is the main purpose of a primary key?
What you will learn
- Explain the key representations, structures or processes in this syllabus section
- Apply the method to a new computer-science example
- Justify a design choice and check its limits
Relational design reduces duplicated data
A file-based system can duplicate facts across files, making updates inconsistent. A relational database organises data into tables. A record (row/tuple) contains fields (columns/attributes); a primary key uniquely identifies a row, and a foreign key refers to a key in another table.
A DBMS controls database definition, storage, queries, access and maintenance. It can enforce integrity rules and manage concurrent access, backups and permissions. It does not automatically make a poor design correct.
DDL defines structures; DML operates on data
SQL is commonly used to communicate with relational DBMSs. DDL creates or alters structures, such as CREATE DATABASE, CREATE TABLE and ALTER TABLE. Data types include CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE and TIME.
DML retrieves and changes records. SELECT chooses fields and rows; INSERT adds rows; UPDATE changes selected rows; DELETE removes selected rows. A WHERE condition limits affected rows, so check it before changing data.
Pause and explain
Which statement changes existing row values?
Worked example
A Learner table has fields LearnerID, Name and ClassID. Which field is a suitable primary key and what role could ClassID have?
Show the worked solution
- A primary key must identify each learner record uniquely.
- LearnerID is intended to be unique for each learner.
- ClassID can refer to the key in a Class table, acting as a foreign key.
Answer LearnerID is the primary key; ClassID can be a foreign key to Class.
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
A database stores each student's class name repeatedly in every subject file. Explain one problem and how a relational design can reduce it.
Give me a hint
Think about what happens when a repeated fact changes.
Compare my reasoning
- Repeated class names can become inconsistent after an update.
- Store each class once in a Class table with a unique ClassID.
- Reference ClassID from student or enrolment records.
A separate Class table reduces duplication; a foreign key links each record to the class.
Look for these in your work
- Update anomaly identified
- Class entity separated
- Relationship explained
Write SQL to create a Learner table with LearnerID as an integer primary key and Name as VARCHAR(40).
Give me a hint
Use the field definitions and a table-level or inline key declaration.
Compare my reasoning
- Use CREATE TABLE Learner.
- Define LearnerID as INTEGER and Name as VARCHAR(40).
- Declare LearnerID as the primary key.
CREATE TABLE Learner (LearnerID INTEGER PRIMARY KEY, Name VARCHAR(40));
Look for these in your work
- DDL command correct
- Data types included
- Primary key declared
A school wants to correct the class for learner 204, whose ClassID should become 8. State the UPDATE query and explain why its WHERE condition matters.
Give me a hint
Target the single learner record using its primary key.
Compare my reasoning
- Start with UPDATE Learner SET ClassID = 8.
- Add WHERE LearnerID = 204.
- Without the WHERE condition, every row could receive ClassID 8.
UPDATE Learner SET ClassID = 8 WHERE LearnerID = 204; the key condition limits the change to one learner.
Look for these in your work
- Target table correct
- Value set
- Row limit explained
Common mistakes
- Using a person's name as a guaranteed unique key.
- Confusing DDL changes to structure with DML changes to records.
- Writing UPDATE or DELETE without a sufficiently specific WHERE condition.
What can you explain now?
Which SQL language category defines a table structure?
Compare with the explanation
DDL
DDL defines or changes structures; DML queries and changes table data.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
How do primary and foreign keys support relational integrity, and how do DDL and DML differ?
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.