Ch 1 · 8 Databases · AS & A Level Computer Science 9618
Topic 1 of 1 in Databases — AS & A Level Computer Science 9618 — 89 lessons.
Introduction
Almost every organisation stores its data in a relational database. This lesson covers why databases replaced separate files, the key terms, how to design tables by normalisation, what a DBMS does, and how to define and query a database with SQL. (Syllabus topic 8: sections 8.1–8.3.)
Key ideas — 8.1 (a) Limitations of a file-based approach
In a file-based system, each department or program keeps its own files.
🧑🏫 Teacher's explanation
Imagine you are organizing a school trip. In a 'file-based approach', the teacher keeps a list of students, the bus company keeps a list of students, and the hotel keeps a list of students. If a student changes their phone number, the teacher updates it, but the bus company and hotel don't know. Now you have three different phone numbers for the same person! This is 'data inconsistency'.
📖 Definitions
🔍 Worked example — Easy
Identify the limitation: A hospital stores patient names in the 'Appointments' file and the 'Billing' file. A patient changes their surname. The 'Appointments' file is updated, but the 'Billing' file is not. What is this limitation called?
🔍 Worked example — Standard
A programmer writes a COBOL program to read a flat text file where the first 10 characters are the 'Name' and the next 5 are 'ID'. If the database administrator decides to change 'Name' to 20 characters, why is this a problem in a file-based system?
🔍 Worked example — Exam-style
Compare a file-based system and a relational database regarding 'Data Integrity'. Which system provides better control over validation and why?
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Explain one disadvantage of using this file-based approach instead of a relational database. [2] (b) Describe how a Relational Database Management System (RDBMS) ensures that an order cannot be created for a customer that does not exist in the 'Customers' table. [2] [4 marks]
Key ideas — 8.1 (b) Terminology
E-R diagrams show entities as boxes and relationships as lines. In crow's-foot notation, a plain end means "one" and a three-pronged end means "many":
🧑🏫 Teacher's explanation
Imagine you are organizing a massive school festival. If you use a single giant spreadsheet (a file-based approach), you'll end up writing the same student names over and over again every time they sign up for a different club. This wastes space and leads to errors—if a student changes their name, you have to find every single row to fix it.
📖 Definitions
🔍 Worked example — Easy
Identify the Primary Key, Candidate Key, and Foreign Key in the following schema: Table: STUDENT (StudentID, NIC, FirstName, LastName) Table: ENROLMENT (EnrolmentID, StudentID, CourseID)
🔍 Worked example — Standard
A database has a 'Customer' table with PK CustomerID and an 'Order' table with PK OrderID and FK CustomerID. If a user tries to delete a record from 'Customer' where CustomerID = 50, but there is a record in 'Order' with CustomerID = 50, what happens due to referential integrity?
🔍 Worked example — Exam-style
Convert the following many-to-many relationship into a relational schema using a junction table: Entities: STUDENT (StudentID, Name) and COURSE (CourseID, Title).
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Explain why a many-to-many relationship exists between BOOK and BORROWER. [2] (b) Describe how a junction table would be used to resolve this relationship. [2] [4 marks]
Key ideas — 8.1 (c) Normalisation
Normalisation is the process of organising data into tables to remove redundancy and avoid problems when inserting, updating or deleting data.
🧑🏫 Teacher's explanation
Imagine you are organizing a massive collection of trading cards. If you just throw them all in one big box, you might write the card's name, the artist's name, and the artist's phone number on every single card. If the artist changes their phone number, you have to find every single card they ever drew and update it manually. If you miss one, your data is inconsistent. This is 'data redundancy'.
📖 Definitions
🔍 Worked example — Easy
A table STUDENT(StudentID, Name, Subject, Grade) contains the following row: (101, "Alice", "Maths, Physics", "A"). Explain why this violates 1NF and how to fix it.
🔍 Worked example — Standard
A table BOOK_SALES(OrderID, BookID, OrderDate, BookTitle) has a composite primary key (OrderID, BookID). Determine if it is in 2NF and why.
🔍 Worked example — Exam-style
A table EMPLOYEE(EmpID, Name, DeptID, DeptName) is in 2NF. The primary key is EmpID. Explain why it is not in 3NF and provide the 3NF decomposition.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
BOOK_AUTHOR(ISBN, Title, AuthorID, AuthorName, AuthorNationality)
Key ideas — 8.2 Database Management Systems (DBMS)
A DBMS is the software that creates, manages and controls access to a database.
🧑🏫 Teacher's explanation
Imagine you are running a massive library. If you just threw every book into a giant pile in the middle of the room, finding a specific book would be impossible, and two people might try to grab the same book at once, causing chaos. This is like a 'file-based approach' where data is just loose files on a disk.
📖 Definitions
🔍 Worked example — Easy
A developer uses a DBMS to create a new table for 'Students'. Identify which DBMS feature is being used when the developer defines the field 'Student_ID' as an INTEGER and 'Name' as a VARCHAR(50).
🔍 Worked example — Standard
A user attempts to delete a record from the 'Orders' table. However, the DBMS prevents this because the 'Customer_ID' in that record is currently linked to an entry in the 'Customers' table. Which specific data integrity concept is being enforced?
🔍 Worked example — Exam-style
A DBMS is managing a database for a bank. Two different tellers attempt to update the balance of the same account at the exact same millisecond. The DBMS locks the record for the first teller until the transaction is complete. Name the feature being used and explain its purpose.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) Define the term 'metadata'. [1] (b) Explain how the DBMS uses a 'data dictionary' to maintain data integrity. [2] [3 marks]
Key ideas — 8.3 (a) DDL, DML and SQL
🧑🏫 Teacher's explanation
Imagine you are building a massive, high-tech library. Before you can put books on shelves, you first need to design the building: how many floors there are, how many shelves each floor has, and what labels go on the aisles. This 'blueprinting' phase is the Data Definition Language (DDL). You aren't touching the books yet; you are defining the structure that will hold them.
📖 Definitions
🔍 Worked example — Easy
Identify whether the following SQL statement is DDL or DML: CREATE TABLE Student (StudentID INTEGER, FirstName VARCHAR(20), LastName VARCHAR(20), PRIMARY KEY (StudentID));
🔍 Worked example — Standard
Given a table Staff with fields StaffID (INTEGER) and Salary (REAL), write a DML statement to retrieve the Salary of the staff member where StaffID is 101.
🔍 Worked example — Exam-style
A database administrator needs to add a new column called Email with a variable character type of length 50 to an existing table named Customer. Write the SQL DDL statement to perform this action.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
(a) State whether the following SQL statement is DDL or DML: ALTER TABLE Product ADD StockLevel INTEGER; [1]
Key ideas — 8.3 (b) SQL DDL
Data types: CHARACTER (fixed length, CHARACTER(4)), VARCHAR(n) (variable length up to n), BOOLEAN, INTEGER, REAL, DATE, TIME.
🧑🏫 Teacher's explanation
Think of a database like a massive, organized filing cabinet system in a school office. If you just threw papers into a drawer, you'd never find anything (this is like a file-based approach). To make it work, you first need to build the cabinet itself—deciding how many drawers there are and what labels go on the folders. This 'building' phase is what we call Data Definition Language (DDL).
📖 Definitions
🔍 Worked example — Easy
Write the SQL DDL statement to create a database named 'Hospital' and a table named 'Doctor' with the following attributes: DoctorID (INTEGER, Primary Key), Name (VARCHAR(50)), and Specialism (VARCHAR(30)).
🔍 Worked example — Standard
A table 'Class' exists with attributes: ClassID (INTEGER, Primary Key) and ClassName (VARCHAR(20)). Write the SQL DDL to add a new attribute called 'RoomNumber' of type VARCHAR(10) to this table.
🔍 Worked example — Exam-style
You have two tables: 'Author' (AuthorID INTEGER PRIMARY KEY, Name VARCHAR(50)) and 'Book' (BookID INTEGER PRIMARY KEY, Title VARCHAR(50), AuthorID INTEGER). Write the SQL DDL to establish a relationship between them by adding a foreign key to the 'Book' table.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
Key ideas — 8.3 (c) SQL DML: queries
Suppose these tables hold the data:
🧑🏫 Teacher's explanation
Think of a database like a massive digital filing cabinet. If you just wanted to find all students taller than 1.6m, you would have to look at every single folder one by one. SQL (Structured Query Language) is like giving a specific, highly efficient command to a robot librarian. Instead of searching manually, you tell the robot exactly which drawer to open, which specific pieces of information to grab, and how to sort them.
📖 Definitions
🔍 Worked example — Easy
Given the Student table, write a SQL query to select the FirstName and StudentID for all students where the Height is exactly 1.55.
🔍 Worked example — Standard
Using the Student table, write a query to find the total number of students (COUNT) and the maximum height (MAX) for each TutorID.
🔍 Worked example — Exam-style
Consider two tables: Product(ProductID, Name, Price) and Sales(SaleID, ProductID, Quantity). Write a query to show the Name of the product and the total Quantity sold for each product, but only for products with a Price greater than 10.00.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
Key ideas — 8.3 (d) SQL DML: data maintenance
Always include a WHERE clause with UPDATE and DELETE: without it, every row is changed or deleted.
🧑🏫 Teacher's explanation
Imagine you are managing a physical filing cabinet for a school. If you want to add a new student, you don't just throw a piece of paper in; you follow a specific protocol to ensure it fits the folder structure. This is what an INSERT statement does. If a student grows taller and you need to update their record, you don't throw the whole folder away and start over; you just erase the old height and write the new one. This is an UPDATE. Finally, if a student leaves the school, you remove their specific folder. This is a DELETE.
📖 Definitions
🔍 Worked example — Easy
You have a table Product with fields ProductID (INTEGER), ProductName (VARCHAR), and Price (REAL). Write an SQL statement to add a new product with ID 10, name 'Laptop', and price 899.99.
🔍 Worked example — Standard
A table Employee has fields EmpID (INTEGER), Name (VARCHAR), and Salary (REAL). Write an SQL statement to increase the salary of the employee with EmpID 505 to 35000.00.
🔍 Worked example — Exam-style
A table Task has fields TaskID (INTEGER), Description (VARCHAR), and Status (VARCHAR). Write an SQL statement to remove all tasks that have a Status of 'Completed'.
⚠️ Common errors
🧠 Quick check 1
🧠 Quick check 2
🎯 Exam question
Write the SQL statement to perform the following maintenance task: Update the price of the car with the registration 'ABC 123' to 15000.00. [2] [2 marks]
Worked example
BOOKING(BookingID, CustomerID, CustomerName, RoomNo, RoomType, Nightly) stores hotel bookings, where each booking is for one room. Explain why the table is not in 3NF and produce a 3NF design.
🔍 Worked example — Easy
Identify the primary key and check if the table STUDENT(StudentID, FirstName, LastName, DateOfBirth) has any transitive dependencies.
🔍 Worked example — Standard
A table PRODUCT(ProductID, ProductName, Category, CategoryDescription) is in 2NF. The primary key is ProductID. Explain why it is NOT in 3NF and suggest the split.
🔍 Worked example — Exam-style
A table EMPLOYEE(EmpID, EmpName, DeptID, DeptName, DeptLocation) is in 2NF. The primary key is EmpID. Convert this to 3NF.
⚠️ Common errors
Common mistakes
Practice
Answers: (1) Any two: data redundancy; inconsistency; data dependency (programs tied to the file structure); difficult to share or query across files; poor security control. (2) Candidate key: an attribute or set of attributes that uniquely identifies each record and could be chosen as the primary key. Referential integrity: every foreign key value must match an existing primary key value in the related table. (3) 2NF (single-field primary key, so no partial dependencies) but not 3NF: ClubName depends on ClubID, a non-key attribute. 3NF: CLUB(ClubID, ClubName), MEMBER(MemberID, Name, ClubID) with ClubID as a foreign key. (4) Any two: table names; field names; data types; field sizes; validation rules; primary/foreign keys; relationships; access rights. (5) CREATE TABLE Club (ClubID CHARACTER(4), ClubName VARCHAR(30), Fee REAL, PRIMARY KEY (ClubID)); (6) SELECT TutorID, COUNT(StudentID) FROM Student WHERE Height > 1.6 GROUP BY TutorID; (result: T01 3). (7) UPDATE Tutor SET Room = 'C10' WHERE TutorID = 'T02';