Database — Complete Study Notes
DATABASE
Complete Study Notes — All Topics Organised
How Everything Connects
The whole chapter is about one thing: how to store data properly so it doesn't cause problems. Each
topic builds on the previous one.
Step Topic What it does
1 Flat file vs Relational DB Understand WHY we need a
proper database
2 ER Diagram PLAN the tables before building
3 Keys (PK, FK, Candidate) Define how tables identify and
link rows
4 Normalisation (1NF, 2NF, 3NF) Remove redundancy and errors
5 SQL (DDL + DML) Build and use the database
6 DBMS Tools Manage the database with
forms, reports, queries
1. Data Storage: Flat File vs Relational Database
Flat File Approach
Data is stored in one or more separate computer files.
4 Limitations of Flat Files
• Data redundancy — same data is repeated in more than one file
• Data dependency — changes to data means changes to every program that accesses it
• Lack of data integrity — entries that should be the same can be different in different
places
• Lack of data privacy — all users have access to all data in a single flat file
Relational Database
A way of structuring information in tables, rows and columns.
5 Benefits over Flat File
• Reduced data redundancy
• Reduced data dependency
A-Level / IGCSE Computer Science
Database — Complete Study Notes
• Improved data integrity
• Improved data privacy
• Program-data independence
2. Keys — The Identity System of a Database
Think of keys as the ID system. Without them, you can't link tables or find specific rows.
Key Definitions
Term Definition
Entity The concept or object in the system we want to
model and store information about. Becomes a
table.
Attribute A data item, represented as a field (column)
within a table.
Tuple / Record One complete row of data in a table.
Primary Key (PK) A unique identifier for each tuple. No two rows
share the same value. Cannot be NULL.
Foreign Key (FK) A field in one table that links to a primary key in
another table — creates the relationship.
Candidate Key Any attribute (or set of attributes) that could
uniquely identify a row — it COULD be the
primary key.
Secondary Key A candidate key that was NOT chosen as the
primary key.
Candidate Key — Explained
A candidate key is ANY column (or group of columns) that COULD be the primary key. You pick
one to become the actual primary key. The rest become secondary keys.
Example — Student Table
For a student, several things could uniquely identify them:
• Roll No — unique for every student
• CNIC (national ID) — unique for every person
• Passport Number — unique for every person
A-Level / IGCSE Computer Science
Database — Complete Study Notes
All three are candidate keys. If you choose Roll No as the primary key, then CNIC and Passport
Number become secondary keys.
Two Rules a Candidate Key Must Satisfy
• Uniqueness — it must identify exactly one row. No two rows can share the same value.
• Minimality — you can't remove any attribute from it and still have it be unique.
Memory trick: if (StudentID + Name) works but StudentID alone also works, then (StudentID + Name)
is NOT minimal — StudentID alone is the real candidate key.
Referential Integrity
Tables must not try to reference data that does not exist.
• A primary key cannot be deleted unless all dependent records are already deleted
(cascading delete)
• A primary key cannot be updated unless all dependent records are already updated
(cascading update)
• Every foreign key value must have a matching value in the corresponding primary key
• Foreign keys must be the same data type as the primary key they reference
3. Normalisation — 1NF, 2NF, 3NF
The goal is to remove redundancy (repeated data) and anomalies (errors when inserting, updating or
deleting). Each stage builds on the previous one.
Memory trick: "Each piece of data should depend on the key, the WHOLE key, and nothing but
the key."
1NF — First Normal Form
Rule
• Each cell must have ONE value only (atomic values)
• No repeating groups of attributes
• No duplicate rows
Example — Breaks 1NF
Name Courses
A-Level / IGCSE Computer Science
Database — Complete Study Notes
Taha Math, English
Sara Science
"Math, English" is two values in one cell — not atomic!
Fixed — Now in 1NF
Name Course
Taha Math
Taha English
Sara Science
Ask yourself: Is there more than one value in any single cell? If yes, it fails 1NF.
2NF — Second Normal Form
Rule
•Must already be in 1NF
•No partial dependency — every non-key attribute must depend on the ENTIRE primary
key, not just part of it
NOTE: This only matters when the primary key is made of two or more columns (a composite
key).
What is partial dependency?
When a non-key attribute depends on only PART of the composite primary key.
Example — Score Table (from notes)
Primary Key = (student_id + subject_id)
score_id student_id subject_id marks teacher
1 S1 Math 85 Mr Khan
2 S2 Math 90 Mr Khan
3 S1 English 78 Ms Ali
Problem: 'teacher' only depends on subject_id, NOT on the full (student_id + subject_id) key.
This is a partial dependency. Mr Khan is repeated every time a student takes Maths.
Fix: Move 'teacher' to a separate Subject table where subject_id is the primary key.
A-Level / IGCSE Computer Science
Database — Complete Study Notes
Ask yourself: Is there a composite (multi-column) primary key? Does any non-key column depend on
only PART of it? If yes, it fails 2NF.
3NF — Third Normal Form
Rule
• Must already be in 1NF and 2NF
• No transitive dependency — no non-key attribute should depend on another non-key
attribute
What is transitive dependency?
When Column C depends on Column B, which depends on Column A (the key). C is not directly
depending on the key — it goes through B. Example: StudentID → DeptID → DeptName
Example
StudentID (PK) DeptID DeptName
S1 D1 Science
S2 D1 Science
S3 D2 Arts
Problem: DeptName depends on DeptID, not directly on StudentID. 'Science' is repeated twice
— transitive dependency!
Fix: Split into two tables:
• Student(StudentID, DeptID)
• Department(DeptID, DeptName)
Ask yourself: Does any non-key column depend on another non-key column? If yes, it fails 3NF.
Normalisation Summary Table
Form What it removes Key question to ask
1NF Multi-values in a cell, duplicate Is every cell atomic (one value
rows only)?
2NF Partial dependency on Does every column need the
composite key FULL key?
A-Level / IGCSE Computer Science
Database — Complete Study Notes
3NF Dependency between non-key Does any column depend on a
columns non-key column?
Real Exam Example — SOFTWARE_PURCHASED (from notes)
SOFTWARE_PURCHASED(SoftwareName, SoftwareDescription, CustomerID, LicenceType,
LicenceCost, RenewalDate)
Why it fails 3NF:
• SoftwareDescription only depends on SoftwareName — partial dependency (fails 2NF)
• LicenceCost depends on LicenceType — transitive dependency (fails 3NF)
4. Entity Relationship (ER) Diagrams
An ER diagram is the PLAN you draw BEFORE building the database. It shows what entities (tables)
exist and how they relate to each other.
The 4 Types of Relationships
Relationship Notation Example
One to One (1:1) A ——— B Employee — Office (one
employee has one office)
One to Many (1:M) A ———< B Teacher — Students (one
teacher has many students)
Many to One (M:1) A >——— B Students — Teacher (many
students have one teacher)
Many to Many (M:M) A >———< B Customers — Products (many
customers buy many products)
NOTE: Many-to-Many relationships need a linking table (e.g. CustomerProduct) to be
implemented in a relational database.
How to Read the Arrow Notation
The arrow with a fork (< or >) points to the 'many' side. The plain line is the 'one' side.
• A-NURSE >——— A-WARD = Many nurses belong to one ward = Many to One
• B-NURSE >———< B-WARD = Many nurses in many wards = Many to Many
A-Level / IGCSE Computer Science
Database — Complete Study Notes
Schema Levels
Schema Level What it describes
External Schema The individual user's view of the database — they
only see what they need
Conceptual Schema Describes the views which users of the database
might have
Logical Schema Describes how relationships will be implemented
in the logic structure
Physical / Internal Schema Describes how the data will be stored on the
physical media (hard drive)
5. SQL — Structured Query Language
SQL has two parts: DDL (building the structure) and DML (working with the data).
DDL — Data Definition Language
Used to BUILD the database structure.
1. Create a database
CREATE DATABASE Students;
2. Create a table
CREATE TABLE Employee_Data (
EmployeeID VARCHAR(7),
FirstName VARCHAR,
LastName VARCHAR,
DateOfBirth DATE,
Gender VARCHAR(6),
DeptNumber VARCHAR(2),
PRIMARY KEY (EmployeeID) NOT NULL
);
3. Add a new field to existing table
ALTER TABLE Student
ADD Telephone VARCHAR;
A-Level / IGCSE Computer Science
Database — Complete Study Notes
DML — Data Manipulation Language
Used to WORK WITH the data inside the tables.
INSERT — add a new row
INSERT INTO Room
VALUES (5, 'Double');
UPDATE — change existing data
UPDATE B_Nurse
SET FamilyName = 'Chi'
WHERE NurseID = '076';
SELECT — retrieve/display data
SELECT NurseID, FamilyName
FROM B_Nurse
WHERE Specialism = 'THEATRE';
SELECT with multiple conditions
SELECT LessonDate, LessonTime
FROM Lesson
WHERE InstructorID = 'Ins01'
AND LessonDate > '22/11/2023';
DELETE — remove a row
DELETE FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';
INNER JOIN — Combining Two Tables
Use INNER JOIN when you need data from two tables at once. It only returns rows that have a match
in BOTH tables.
SELECT ActorID
FROM Film_Actor
INNER JOIN Film_Fact
ON Film_Fact.FilmID = Film_Actor.FilmID
WHERE Film_Fact.FilmTitle = 'Cinderella';
A-Level / IGCSE Computer Science
Database — Complete Study Notes
Aggregate Functions
Function What it does Example
COUNT() Counts the number of rows SELECT COUNT(ProductID)
FROM Products;
AVG() Calculates the average value SELECT AVG(Price) FROM
Products;
SUM() Adds up all values SELECT SUM(Price) FROM
Products;
GROUP BY Groups results by a column SELECT CustomerID, COUNT(*)
FROM Orders GROUP BY
CustomerID;
ORDER BY ASC Sorts results ascending (A-Z, 0- ORDER BY Cost ASC
9)
ORDER BY DESC Sorts results descending (Z-A, 9- ORDER BY Cost DESC
0)
Complex query example (from notes)
SELECT CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate
FROM Licence
WHERE ExpiryDate <= '31/12/2019'
GROUP BY CustomerID
ORDER BY Cost ASC;
6. DBMS Tools
A DBMS (Database Management System) provides tools so designers and users can interact with the
database — without needing to know how data is physically stored.
Developer Interface
What a database designer uses it for:
• Create user-friendly features — e.g. forms to enter new bookings
• Create outputs — e.g. a report of bookings on a given date
• Create interactive features — e.g. buttons or menus
Tasks performed by the developer interface
• Create a table — using DDL commands (CREATE TABLE)
• Design a form — with text boxes, dropdowns, radio buttons, date pickers
A-Level / IGCSE Computer Science
Database — Complete Study Notes
• Design a report — formatted printable output with headers, totals, averages
• Set up buttons and menus — that trigger queries when clicked
• Set user permissions — control who can read, write or delete data
Query Processor
• Creates SQL queries to search for and retrieve data
• Searches for data that meets set criteria — e.g. all bookings for next week
• Performs calculations on extracted data — e.g. number of empty rooms
• Organises and displays the results
Data Dictionary
Stores all information ABOUT the database — not the actual data, but the structure.
• Field names and data types
• Which fields are primary keys, foreign keys
• Constraints (NOT NULL, UNIQUE etc.)
7. Quick Revision Cheatsheet
Read this the night before your exam.
All Key Definitions
Term Definition
Entity A thing to store data about — becomes a table
Attribute A data item about an entity — becomes a column
Tuple One row in a table
Primary Key Unique identifier for each row — cannot be NULL
Foreign Key Links one table to another table's primary key
Candidate Key Any attribute that COULD be the primary key
Secondary Key A candidate key that was not chosen as primary
key
Referential Integrity Foreign key values must match an existing
primary key
Normalisation Process of removing redundancy from a database
design
A-Level / IGCSE Computer Science
Database — Complete Study Notes
Partial dependency Non-key depends on only PART of composite key
— breaks 2NF
Transitive dependency Non-key depends on another non-key — breaks
3NF
DDL Data Definition Language — used to create and
modify structure (CREATE, ALTER)
DML Data Manipulation Language — used to work with
data (SELECT, INSERT, UPDATE, DELETE)
SQL Quick Reference
Action SQL Command
Create database CREATE DATABASE name;
Create table CREATE TABLE name (cols, PRIMARY KEY(col)
NOT NULL);
Add column ALTER TABLE name ADD column datatype;
Insert row INSERT INTO table VALUES (v1, v2, ...);
Update row UPDATE table SET col=val WHERE condition;
Select rows SELECT col FROM table WHERE condition;
Delete rows DELETE FROM table WHERE condition;
Join tables SELECT col FROM t1 INNER JOIN t2 ON [Link] =
[Link];
Count rows SELECT COUNT(col) FROM table;
Average value SELECT AVG(col) FROM table;
Sum values SELECT SUM(col) FROM table;
Sort ascending ORDER BY col ASC
Sort descending ORDER BY col DESC
Group results GROUP BY col
Normalisation Decision Tree
Use this step-by-step to solve any normalisation question:
• Step 1: Does any cell have multiple values? YES → Fix for 1NF: split into separate rows
• Step 2: Is there a composite (multi-col) PK? Does any non-key column depend on only
part of it? YES → Fix for 2NF: move that column to its own table
• Step 3: Does any non-key column depend on another non-key column? YES → Fix for
3NF: move that group to its own table
A-Level / IGCSE Computer Science