8.1 Database Concepts

---

Limitations of File-Based Approach

Definition: A file-based approach is where data is stored in separate files, often in different formats, with each application having its own files.

Characteristics of File-Based Systems

• Data duplication: Same data stored in multiple files
• Program-data dependence: File structure embedded in application code
• Limited data sharing: Files owned by specific applications
• Inconsistent formats: Different applications store data differently
• No central control: Each application manages its own data

Key Limitations

1. Data Redundancy and Duplication

Problem: Same data repeated across multiple files.

Example:

Student Records File:     Enrollment File:
StudentID: 12345          StudentID: 12345
Name: John Smith          Name: John Smith
Address: 10 High St       Course: CS101
Phone: 555-1234           Date: 01/09/2024

Consequences:

• Wasted storage space
• Inconsistencies when data updated in one place but not others
• Increased data entry effort
• Higher maintenance costs

2. Data Inconsistency

Problem: Different versions of same data exist.

Example:

File 1: John Smith, 10 High Street
File 2: J. Smith, 10 High St
File 3: John Smith, 10 High Street, London

Which is correct? Impossible to know without checking source.

3. Program-Data Dependence

Problem: File structure is hard-coded in programs.

COBOL Example:

FILE SECTION.
FD STUDENT-FILE.
01 STUDENT-RECORD.
   05 STUDENT-ID    PIC 9(5).
   05 STUDENT-NAME  PIC X(30).
   05 STUDENT-ADDR  PIC X(50).

If structure changes (e.g., adding phone number):

• All programs accessing file must be modified
• Recompilation required
• High maintenance cost
• Risk of introducing bugs

4. Limited Data Sharing

Problem: Files owned by specific applications; other apps can’t easily access.

Consequences:

• Same data recreated for different applications
• No concurrent access control
• Difficulty integrating data across organisation
• “Siloed” information

5. Poor Data Integrity Control

Problem: No central enforcement of data rules.

Issues:

• Invalid data can be entered (e.g., age 200)
• Missing data not prevented
• Referential integrity not maintained
• No validation rules enforced

6. Security Issues

Problem: Each application manages its own security.

Consequences:

• Inconsistent security policies
• Hard to manage user permissions centrally
• Audit trail difficult to maintain
• Increased vulnerability

7. No Atomic Operations (Transactions)

Problem: Cannot group operations that must all succeed or all fail.

Example: Transfer money

• Step 1: Deduct from account A
• Step 2: Add to account B

If system crashes after Step 1: Money disappears!

---

Relational Database Features Addressing File-Based Limitations

1. Eliminating Redundancy

Feature: Centralised data storage with controlled duplication.

How it addresses limitation:

• Each fact stored once
• Relationships link data rather than duplicating
• Updates in one place automatically reflected everywhere

Example:

Instead of storing student name in enrollment records:
Student Table:                    Enrollment Table:
StudentID | Name                  StudentID | CourseID
12345     | John Smith            12345     | CS101

2. Ensuring Consistency

Feature: Data integrity rules enforced by DBMS.

How it addresses limitation:

• Validation rules at database level
• Constraints ensure data quality
• All applications see consistent data

3. Data Independence

Feature: Separation of data structure from applications.

How it addresses limitation:

• Logical schema defines structure
• Physical storage managed by DBMS
• Applications access data via queries
• Structure can change without breaking apps (with views)

4. Data Sharing

Feature: Multiple users and applications can access data concurrently.

How it addresses limitation:

• Central database accessible to all authorised users
• Concurrency control prevents conflicts
• Transaction management ensures consistency

5. Data Integrity

Feature: Central enforcement of data rules.

How it addresses limitation:

• Constraints (primary key, foreign key, check)
• Data types ensure correct format
• Referential integrity maintains relationships

6. Security

Feature: Centralised security management.

How it addresses limitation:

• User authentication
• Access rights (GRANT/REVOKE)
• Views restrict data access
• Audit trails

7. Transaction Support

Feature: ACID properties ensure reliable operations.

ACID:

• Atomicity: All or nothing
• Consistency: Data remains valid
• Isolation: Concurrent transactions don’t interfere
• Durability: Committed data persists

---

Relational Database Terminology

Core Concepts

Term	Definition	Analogy
Entity	Real-world object or concept about which data is stored	Student, Course, Book
Table	Collection of related data organised in rows and columns	Spreadsheet
Record / Row / Tuple	Single instance of an entity; horizontal row in a table	One student’s data
Field / Column / Attribute	Single piece of information about an entity	Student name, date of birth

Keys

Term	Definition	Example
Primary Key	Uniquely identifies each record in a table	StudentID (unique for each student)
Candidate Key	Any column that could be the primary key	StudentID, Email, National Insurance No.
Secondary Key	Indexed non-unique field for searching	Department, Course name
Foreign Key	References primary key in another table	CourseID in Student table (links to Course table)

Relationships

Relationship	Description	Example
One-to-One (1:1)	Each record in Table A relates to exactly one in Table B	One student has one locker
One-to-Many (1:M)	One record in Table A relates to many in Table B	One department has many students
Many-to-Many (M:N)	Many records in Table A relate to many in Table B	Students can take many courses; courses have many students

Integrity Concepts

Term	Definition
Referential Integrity	Ensures foreign key values always reference existing primary keys
Entity Integrity	Primary key cannot be null (no duplicate, no missing)
Domain Integrity	All values in a column must be of the same type and meet constraints

Indexing

Definition: Data structure that improves speed of data retrieval operations.

Characteristics:

• Like book index (quick lookup)
• Speeds up SELECT queries
• Slows down INSERT/UPDATE/DELETE
• Can be created on any column(s)
• Usually implemented as B-tree or hash table

Example:

CREATE INDEX idx_student_name ON Students (LastName);

---

Entity-Relationship (E-R) Diagrams

Purpose

Document database design visually showing:

• Entities (tables)
• Attributes (fields)
• Relationships between entities

E-R Diagram Notation

Entities: Represented as rectangles

┌─────────────┐
│   Student   │
└─────────────┘

Attributes: Represented as ovals connected to entity

        ┌─────────────┐
        │ StudentID   │
        └─────────────┘
               │
        ┌──────┴──────┐
        │   Student   │
        └──────┬──────┘
               │
        ┌──────┴──────┐
        │    Name     │
        └─────────────┘

Relationships: Represented as diamonds

┌─────────────┐       ┌─────────┐       ┌─────────────┐
│   Student   │───────│ Enrolls │───────│   Course    │
└─────────────┘       └─────────┘       └─────────────┘

Relationship Cardinality

One-to-One (1:1):

┌─────────────┐         ┌─────────────┐
│   Student   │1───────1│   Locker    │
└─────────────┘         └─────────────┘

One-to-Many (1:M):

┌─────────────┐         ┌─────────────┐
│ Department  │1───────M│   Student   │
└─────────────┘         └─────────────┘

Many-to-Many (M:N):

┌─────────────┐         ┌─────────────┐
│   Student   │M───────N│   Course    │
└─────────────┘         └─────────────┘

Example E-R Diagram

┌─────────────┐                  ┌─────────────┐
│   Student   │                  │   Course    │
├─────────────┤                  ├─────────────┤
│ StudentID   │                  │ CourseID    │
│ Name        │                  │ Title       │
│ Email       │                  │ Credits     │
│ DeptID      │───┐              └─────────────┘
└─────────────┘   │                     │
                  │                     │
                  │  ┌─────────────┐     │
                  └──│             │────┘
              ┌──────│ Enrollment  │──────┐
              │      └─────────────┘      │
              │            │               │
         ┌────┴────┐  ┌────┴────┐     ┌───┴────┐
         │ Grade   │  │ Semester│     │ Year   │
         └─────────┘  └─────────┘     └────────┘

---

Normalisation

Purpose of Normalisation

Definition: Process of organising data to reduce redundancy and improve integrity.

Goals:

• Eliminate redundant data
• Ensure data dependencies make sense
• Avoid update anomalies
• Simplify data maintenance

Types of Anomalies

Anomaly	Description	Example
Insert	Cannot insert data because other data missing	Can’t add new course without a student
Update	Must update same data in multiple places	Changing course name requires updating many records
Delete	Deleting data removes unintended information	Deleting a student removes course information

Normal Forms

First Normal Form (1NF)

Requirements:

1. Each column contains atomic (indivisible) values
2. No repeating groups or arrays
3. Each record uniquely identifiable (primary key)

Example – NOT in 1NF:

StudentID | Name      | Courses
12345     | John Smith| CS101, CS201, CS301

Problem: Courses column contains multiple values (non-atomic).

Convert to 1NF:

StudentID | Name      | Course
12345     | John Smith| CS101
12345     | John Smith| CS201
12345     | John Smith| CS301

Now in 1NF ✓ but has redundancy (Name repeated).

Second Normal Form (2NF)

Requirements:

1. Must be in 1NF
2. All non-key attributes must depend on entire primary key (no partial dependencies)

Partial dependency: When attribute depends on only part of a composite key.

Example – NOT in 2NF:
Table: Enrollment (StudentID, CourseID, StudentName, CourseTitle, Grade)

Composite primary key: (StudentID, CourseID)

StudentName depends on StudentID (part of key only) → PARTIAL DEPENDENCY
CourseTitle depends on CourseID (part of key only) → PARTIAL DEPENDENCY
Grade depends on both StudentID and CourseID → FULL DEPENDENCY

Problems:

• StudentName repeated for each course student takes (redundancy)
• Update anomaly: Changing student name requires updating multiple records
• Insert anomaly: Can’t add new course without a student enrolled

Convert to 2NF – Split into tables:

Student table:

StudentID (PK) | StudentName
12345          | John Smith

Course table:

CourseID (PK) | CourseTitle
CS101         | Programming
CS201         | Data Structures

Enrollment table:

StudentID (FK) | CourseID (FK) | Grade
12345          | CS101         | A
12345          | CS201         | B

Now in 2NF ✓ – Each non-key attribute depends on full primary key.

Third Normal Form (3NF)

Requirements:

1. Must be in 2NF
2. No transitive dependencies (non-key attributes depending on other non-key attributes)

Transitive dependency: When A → B and B → C, so A → C indirectly.

Example – NOT in 3NF:
Student table:

StudentID (PK) | StudentName | DeptID | DeptName | DeptPhone
12345          | John Smith  | CS     | Computing| 555-1234
12346          | Jane Doe    | CS     | Computing| 555-1234
12347          | Bob Wilson  | MATH   | Maths    | 555-5678

Dependencies:

• StudentID → DeptID
• DeptID → DeptName, DeptPhone
• Therefore StudentID → DeptName, DeptPhone (transitive)

Problems:

• Department details repeated for every student in that department
• Update anomaly: Changing department phone requires updating all students
• Insert anomaly: Can’t add new department without students
• Delete anomaly: Deleting last student loses department info

Convert to 3NF – Split into tables:

Student table:

StudentID (PK) | StudentName | DeptID (FK)
12345          | John Smith  | CS
12346          | Jane Doe    | CS
12347          | Bob Wilson  | MATH

Department table:

DeptID (PK) | DeptName | DeptPhone
CS          | Computing| 555-1234
MATH        | Maths    | 555-5678

Now in 3NF ✓ – No transitive dependencies.

Normalisation Summary

Normal Form	Rule	How to Achieve
1NF	Atomic values; no repeating groups	Separate multi-valued attributes into rows
2NF	No partial dependencies	Split tables with composite keys
3NF	No transitive dependencies	Split tables where non-key depends on non-key

---

8.2 Database Management Systems (DBMS)

Features of a DBMS

What is a DBMS?

Definition: Software that manages databases, providing centralised control, security, and data management.

Key Features

1. Data Dictionary

Definition: Central repository of metadata (data about data).

Contents:

• Table names and structures
• Column names, data types, constraints
• Indexes defined
• User permissions
• Relationships between tables
• Views, stored procedures

Purpose:

• Self-documenting system
• Enforces consistency
• Used by query optimiser
• Central control of schema

2. Data Modelling

Definition: Tools to define database structure.

Features:

• Create/modify tables
• Define relationships
• Set constraints
• Visual design tools

3. Logical Schema

Definition: Overall logical structure of the database.

Levels of abstraction:

External Schema (User Views)
        ↓
Logical Schema (Overall Structure)
        ↓
Internal Schema (Physical Storage)

Benefits:

• Data independence
• Multiple views for different users
• Hide complexity

4. Data Integrity

Features ensuring data correctness:

Integrity Type	DBMS Feature
Entity integrity	Primary key enforcement (no nulls, no duplicates)
Referential integrity	Foreign key constraints prevent orphan records
Domain integrity	Data types, CHECK constraints
User-defined integrity	Rules, triggers

5. Data Security

Features:

Feature	Description
Authentication	Verify user identity (username/password)
Access rights	GRANT/REVOKE permissions per user/group
Views	Restrict data visible to users
Encryption	Encrypt stored data
Audit trails	Log who accessed what and when

Access rights example:

GRANT SELECT, INSERT ON Students TO Teacher
GRANT SELECT ON Students TO Student
REVOKE DELETE ON Students FROM Teacher

6. Backup and Recovery

Features:

• Full database backups
• Incremental backups
• Point-in-time recovery
• Transaction logs
• Replication (copies for redundancy)

Purpose:

• Protect against data loss
• Recover from corruption
• Disaster recovery
• Business continuity

7. Concurrency Control

Features:

• Locking mechanisms
• Transaction isolation
• Deadlock detection/resolution

Purpose:

• Prevent lost updates
• Ensure consistent views
• Allow multiple users simultaneously

8. Query Processing

Features:

• Query optimiser (finds fastest way to execute)
• Execution engine
• Result set management

---

DBMS Software Tools

Developer Interface

Purpose: Tools for database developers to create and manage database objects.

Features:

Graphical interface:

• Visual table designer
• Query builder (drag-and-drop)
• Diagram viewer
• Form/report designers

Command-line interface:

• SQL execution
• Script running
• Administration commands

Examples:

• phpMyAdmin (web-based MySQL)
• SQL Server Management Studio
• Oracle SQL Developer
• pgAdmin (PostgreSQL)
• MySQL Workbench

Typical developer tasks:

• Create/modify tables
• Write and test queries
• Create views, stored procedures
• Manage indexes
• Debug performance issues

Query Processor

Purpose: Executes SQL queries and returns results.

How it works:

SQL Query
    ↓
[Parser] - Checks syntax, builds parse tree
    ↓
[Query Optimiser] - Determines most efficient execution plan
    ↓
[Execution Engine] - Executes plan, accesses data
    ↓
[Result Set] - Returns data to user

Optimiser considerations:

• Available indexes
• Table sizes
• Join methods (nested loop, hash join, merge join)
• Data distribution statistics
• System resources

Example optimisation:

Query: SELECT * FROM Students WHERE LastName = 'Smith'

Without index: Full table scan (slow)
With index: Index lookup + single row access (fast)

Optimiser chooses faster path if index exists.

---

8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

Structured Query Language (SQL)

Definition: Standard language for relational database management.

Two main parts:

Language	Purpose	Commands
DDL	Define/modify structure	CREATE, ALTER, DROP
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE

---

Data Definition Language (DDL)

CREATE DATABASE

Purpose: Creates a new database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE School;

CREATE TABLE

Purpose: Creates a new table with specified columns.

Syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Data Types:

Type	Description	Example
CHAR(n)	Fixed-length string (n characters)	CHAR(10)
VARCHAR(n)	Variable-length string (max n)	VARCHAR(50)
BOOLEAN	True/False	BOOLEAN
INTEGER	Whole number	INTEGER
REAL	Floating-point number	REAL
DATE	Date (YYYY-MM-DD)	DATE
TIME	Time (HH:MM:SS)	TIME

Constraints:

Constraint	Description
PRIMARY KEY	Unique identifier for each row
FOREIGN KEY	References another table’s primary key
NOT NULL	Column cannot be empty
UNIQUE	All values in column must be different
DEFAULT	Default value if none provided
CHECK	Validates data meets condition

Example – Create tables:

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DateOfBirth DATE,
    Enrolled BOOLEAN DEFAULT TRUE
);

CREATE TABLE Course (
    CourseID VARCHAR(10) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Credits INTEGER CHECK (Credits > 0 AND Credits <= 10)
);

CREATE TABLE Enrollment (
    StudentID INTEGER,
    CourseID VARCHAR(10),
    Grade CHAR(2),
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

ALTER TABLE

Purpose: Modifies existing table structure.

Syntax:

ALTER TABLE table_name action;

Examples:

Add column:

ALTER TABLE Student ADD Phone VARCHAR(15);

Modify column:

ALTER TABLE Student ALTER COLUMN Email SET NOT NULL;

Drop column:

ALTER TABLE Student DROP COLUMN Phone;

Add primary key:

ALTER TABLE Student ADD PRIMARY KEY (StudentID);

Add foreign key:

ALTER TABLE Enrollment 
ADD FOREIGN KEY (StudentID) REFERENCES Student(StudentID);

DROP TABLE

Purpose: Removes table from database.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE TempData;

---

Data Manipulation Language (DML)

SELECT

Purpose: Retrieves data from database.

Basic SELECT:

SELECT column1, column2 FROM table;
SELECT * FROM table;  -- all columns

Example:

SELECT FirstName, LastName FROM Student;

WHERE clause (filtering):

SELECT * FROM Student WHERE LastName = 'Smith';
SELECT * FROM Enrollment WHERE Grade = 'A';
SELECT * FROM Student WHERE DateOfBirth > '2000-01-01';

ORDER BY (sorting):

SELECT * FROM Student ORDER BY LastName;
SELECT * FROM Student ORDER BY LastName DESC;  -- descending
SELECT * FROM Enrollment ORDER BY Grade, StudentID;

GROUP BY (aggregation):

SELECT CourseID, COUNT(*) AS StudentCount
FROM Enrollment
GROUP BY CourseID;

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY DepartmentID;

Aggregate functions:

Function	Description	Example
COUNT()	Number of rows	COUNT(*)
SUM()	Sum of values	SUM(Salary)
AVG()	Average value	AVG(Grade)
MIN()	Minimum value	MIN(Age)
MAX()	Maximum value	MAX(Salary)

INNER JOIN (combining tables):

SELECT Student.FirstName, Student.LastName, Course.Title
FROM Student
INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID
INNER JOIN Course ON Enrollment.CourseID = Course.CourseID;

Complete query example:

SELECT 
    Student.FirstName,
    Student.LastName,
    Course.Title,
    Enrollment.Grade,
    YEAR(Enrollment.EnrollmentDate) AS Year
FROM Student
INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID
INNER JOIN Course ON Enrollment.CourseID = Course.CourseID
WHERE Course.Department = 'CS'
    AND Enrollment.Grade IN ('A', 'B')
ORDER BY Student.LastName, Course.Title;

INSERT INTO

Purpose: Adds new records to table.

Syntax:

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

Examples:

INSERT INTO Student (StudentID, FirstName, LastName, Email)
VALUES (12345, 'John', 'Smith', 'john.smith@email.com');

INSERT INTO Course (CourseID, Title, Credits)
VALUES ('CS101', 'Introduction to Programming', 3);

-- Insert with default values
INSERT INTO Enrollment (StudentID, CourseID)
VALUES (12345, 'CS101');  -- Grade NULL, EnrollmentDate default

UPDATE

Purpose: Modifies existing records.

Syntax:

UPDATE table
SET column1 = value1, column2 = value2
WHERE condition;

Examples:

-- Update single record
UPDATE Student
SET Email = 'john.new@email.com'
WHERE StudentID = 12345;

-- Update multiple records
UPDATE Enrollment
SET Grade = 'A+'
WHERE CourseID = 'CS101' AND Grade = 'A';

-- Update all records (be careful!)
UPDATE Employee
SET Salary = Salary * 1.05;  -- 5% raise for everyone

DELETE FROM

Purpose: Removes records from table.

Syntax:

DELETE FROM table WHERE condition;

Examples:

-- Delete specific record
DELETE FROM Student WHERE StudentID = 99999;

-- Delete multiple records
DELETE FROM Enrollment WHERE Grade IS NULL;

-- Delete all records (be very careful!)
DELETE FROM TempTable;

Note: DELETE removes rows but keeps table structure. DROP TABLE removes table entirely.

---

SQL Examples Summary

Creating Database Structure

CREATE DATABASE SchoolDB;

USE SchoolDB;

CREATE TABLE Department (
    DeptID CHAR(4) PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL,
    Budget REAL
);

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DeptID CHAR(4),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Course (
    CourseID CHAR(6) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Credits INTEGER CHECK (Credits BETWEEN 1 AND 6),
    DeptID CHAR(4),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Enrollment (
    StudentID INTEGER,
    CourseID CHAR(6),
    Grade CHAR(2),
    Semester CHAR(10),
    Year INTEGER,
    PRIMARY KEY (StudentID, CourseID, Semester, Year),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Sample Queries

-- 1. List all students in Computer Science
SELECT FirstName, LastName, Email
FROM Student
WHERE DeptID = 'CS';

-- 2. Count students per department
SELECT DeptID, COUNT(*) AS StudentCount
FROM Student
GROUP BY DeptID;

-- 3. Average grade per course
SELECT CourseID, AVG(
    CASE Grade
        WHEN 'A' THEN 4
        WHEN 'B' THEN 3
        WHEN 'C' THEN 2
        WHEN 'D' THEN 1
        ELSE 0
    END) AS AvgGradePoint
FROM Enrollment
GROUP BY CourseID;

-- 4. Students taking 'CS101'
SELECT s.FirstName, s.LastName, e.Grade
FROM Student s
INNER JOIN Enrollment e ON s.StudentID = e.StudentID
WHERE e.CourseID = 'CS101';

-- 5. Add new student
INSERT INTO Student (StudentID, FirstName, LastName, Email, DeptID)
VALUES (54321, 'Jane', 'Doe', 'jane.doe@email.com', 'CS');

-- 6. Update email
UPDATE Student
SET Email = 'jane.new@email.com'
WHERE StudentID = 54321;

-- 7. Delete old enrollment records
DELETE FROM Enrollment
WHERE Year < 2020;

---

Summary Checklist for Assessment Objectives

AO1 (Knowledge) – You should be able to:

• ✓ List limitations of file-based approach
• ✓ Describe relational database terminology (entity, tuple, attribute, keys, relationships)
• ✓ Explain normalisation and normal forms (1NF, 2NF, 3NF)
• ✓ Describe DBMS features (data dictionary, security, integrity)
• ✓ Define DDL and DML
• ✓ List SQL data types
• ✓ Recall SQL command syntax

AO2 (Application) – You should be able to:

• ✓ Identify file-based limitations in given scenarios
• ✓ Draw E-R diagrams from descriptions
• ✓ Apply normalisation to given data
• ✓ Explain why tables are/aren’t in 3NF
• ✓ Write SQL statements (CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE)
• ✓ Use WHERE, ORDER BY, GROUP BY, JOIN
• ✓ Use aggregate functions (COUNT, SUM, AVG)

AO3 (Design/Evaluation) – You should be able to:

• ✓ Design normalised database from requirements
• ✓ Evaluate database designs for normalisation
• ✓ Compare file-based vs database approaches
• ✓ Design appropriate SQL queries for problems
• ✓ Assess DBMS feature suitability for scenarios
• ✓ Justify database design decisions

---