Normalisation and SQL Queries

Normalisation (1NF-3NF) and SQL Queries

Normalisation is the process of organising a relational database to reduce redundancy and prevent update anomalies. SQL (Structured Query Language) is the standard language for querying and manipulating relational databases.

Why Normalise?

Without normalisation, databases suffer from:

• Insertion anomaly: Cannot add data without other unrelated data (e.g., can't add a new course without a student enrolled)
• Deletion anomaly: Deleting data loses other useful information (e.g., deleting the last student on a course loses the course details)
• Update anomaly: Same data stored in multiple places leads to inconsistency when one copy is updated but others are not

First Normal Form (1NF)

A table is in 1NF if:

• All columns contain atomic (indivisible) values — no lists, sets, or repeating groups
• Each row is unique (has a primary key)
• Each column contains values of a single type

Before 1NF (not normalised):

StudentID	Name	Subjects
1	Alice	Maths, Physics
2	Bob	Chemistry

After 1NF:

StudentID	Name	Subject
1	Alice	Maths
1	Alice	Physics
2	Bob	Chemistry

Problem: "Alice" is repeated — redundancy remains.

Second Normal Form (2NF)

A table is in 2NF if:

• It is in 1NF, and
• Every non-key attribute is fully functionally dependent on the whole primary key (no partial dependencies)

This only applies to tables with composite primary keys (keys made of more than one column).

Partial dependency: An attribute depends on only PART of the composite key.

Example: Table with composite key (StudentID, Subject):

StudentID	Subject	Name	Teacher
1	Maths	Alice	Mr Smith
1	Physics	Alice	Dr Jones

• Name depends on StudentID only (partial dependency — violates 2NF)
• Teacher depends on Subject only (partial dependency)

Fix: Split into three tables:

• Students(StudentID, Name)
• Subjects(Subject, Teacher)
• Enrolments(StudentID, Subject) — linking table

Third Normal Form (3NF)

A table is in 3NF if:

• It is in 2NF, and
• No non-key attribute depends on another non-key attribute (no transitive dependencies)

Transitive dependency: A → B → C, where A is the key, B is non-key, C depends on B not A.

Example:

StudentID	Name	TutorID	TutorName
1	Alice	T1	Mr Brown
2	Bob	T1	Mr Brown

StudentID → TutorID → TutorName. TutorName depends on TutorID, not directly on StudentID.

Fix: Split:

• Students(StudentID, Name, TutorID)
• Tutors(TutorID, TutorName)

Summary of Normal Forms

Normal Form	Rule	Eliminates
1NF	Atomic values, unique rows	Repeating groups
2NF	No partial dependencies	Redundancy from composite keys
3NF	No transitive dependencies	Redundancy from non-key dependencies

Memory aid: "The key, the whole key, and nothing but the key" (1NF: depends on A key; 2NF: the WHOLE key; 3NF: NOTHING BUT the key).

SQL: Data Manipulation Language (DML)

SELECT — retrieve data:

SELECT column1, column2 FROM table WHERE condition;
SELECT * FROM students WHERE age > 16;
SELECT name, subject FROM students ORDER BY name ASC;
SELECT DISTINCT subject FROM enrolments;

INSERT — add data:

INSERT INTO students (id, name, age) VALUES (1, 'Alice', 17);

UPDATE — modify data:

UPDATE students SET age = 18 WHERE id = 1;

DELETE — remove data:

DELETE FROM students WHERE id = 1;

SQL: Aggregate Functions

Function	Purpose	Example
COUNT()	Number of rows	SELECT COUNT(*) FROM students
SUM()	Total of a column	SELECT SUM(marks) FROM results
AVG()	Average value	SELECT AVG(marks) FROM results
MAX()	Maximum value	SELECT MAX(marks) FROM results
MIN()	Minimum value	SELECT MIN(marks) FROM results

GROUP BY groups results for aggregation:

SELECT subject, AVG(marks) FROM results GROUP BY subject;

HAVING filters groups (like WHERE but for aggregates):

SELECT subject, AVG(marks) FROM results
GROUP BY subject HAVING AVG(marks) > 60;

SQL: Joins

INNER JOIN — returns rows where there is a match in both tables:

SELECT students.name, enrolments.subject
FROM students
INNER JOIN enrolments ON students.id = enrolments.student_id;

LEFT JOIN — returns all rows from the left table, with NULLs where there is no match:

SELECT students.name, enrolments.subject
FROM students
LEFT JOIN enrolments ON students.id = enrolments.student_id;

Other joins: RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.

SQL: Subqueries

A query inside another query:

SELECT name FROM students
WHERE id IN (SELECT student_id FROM results WHERE marks > 90);

SQL: Data Definition Language (DDL)

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK(age >= 0),
    tutor_id INTEGER,
    FOREIGN KEY (tutor_id) REFERENCES tutors(id)
);

ALTER TABLE students ADD COLUMN email VARCHAR(255);
DROP TABLE students;

Entity-Relationship Diagrams

Relationship	Meaning	Implementation
One-to-one (1:1)	Each A has exactly one B	Foreign key in either table
One-to-many (1:M)	Each A has many Bs	Foreign key in the "many" table
Many-to-many (M:M)	Each A has many Bs and vice versa	Junction/linking table with two foreign keys

Exam Tips

• Walk through normalisation step by step: identify the key, list all dependencies, check for partial (2NF) and transitive (3NF) dependencies
• In SQL questions, always include FROM and use correct JOIN syntax — forgetting the ON clause is a common error
• WHERE filters individual rows; HAVING filters groups (after GROUP BY)
• Know the difference between DDL (CREATE, ALTER, DROP) and DML (SELECT, INSERT, UPDATE, DELETE)
• Primary keys are unique and not null; foreign keys reference another table's primary key
• Practice writing SQL for multi-table queries with JOINs — these are worth the most marks