8.

1 Database Concepts
Limitations of the File-Based Approach
In a file-based system, data is stored in one or more separate files (e.g.,
text files, [Link], spreadsheets). Each application program has its own set
of files. This approach has several major limitations:

Limitation Explanation

The same data is repeated in multiple files. For example,

Data
a customer's name and address appear in an orders file,
redundancy
a deliveries file, and an invoices file. This wastes storage
(duplication)
space.

Because of redundancy, updates may not be applied to

Data
all copies. The customer's address might be changed in
inconsistency
one file but not another, leading to contradictory data.

Data is scattered across different files and formats. To

Data isolation combine data (e.g., customer details + orders), you
must write complex custom code.

File structures (e.g., field lengths, data types) are hard-

Data
coded into application programs. Changing the structure
dependence
means modifying every program that uses the file.

If two users try to update the same file at the same time,
Concurrency
one's changes may be lost. File-based systems lack
problems
transaction management.

Security is typically only at the file level, not fine-grained

Security
(e.g., cannot easily allow one user to see only certain
weaknesses
columns).

To find specific data (e.g., "all customers in London who

No built-in
ordered more than 5 items"), you must write a custom
query capability
program.
Limitation Explanation

No standard
There is no automatic way to enforce that a field must
data integrity
be unique, not null, or match a value in another file.
controls

Features of a Relational Database that Address These

Limitations
A relational database organises data into tables (relations) with rows
and columns, linked by common attributes. It overcomes file-based issues
as follows:

Limitatio
How Relational Database Solves It
n

Redundanc Normalisation (especially 3NF) eliminates duplication by

y splitting data into related tables.

Inconsisten Redundancy is reduced, and referential integrity ensures

cy that copies of a fact (e.g., customer ID) match across tables.

Query language (SQL) allows data from multiple tables to

Isolation
be combined easily using JOIN.

Data independence – the logical schema (tables, columns)

Dependenc
is separate from physical storage. Applications query via SQL,
e
not file structures.

Concurrenc Transaction management (ACID properties) ensures

y concurrent updates are isolated and atomic.

GRANT/REVOKE permissions at table/column/row level using

Security
the DBMS.

Ad-hoc SQL provides powerful, flexible querying without

queries programming.
Limitatio
How Relational Database Solves It
n

Constraints (PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL,

Integrity
UNIQUE) are enforced automatically by the DBMS.

Terminology of the Relational Database Model

Synonym(
Term Definition
s)

A real-world object or concept about which

Entity — we store data (e.g., Student, Book, Order).
Represented as a table.

A set of rows and columns that represents

Table Relation
one entity or relationship.

A single entry in a table; a set of related

Record Row, Tuple
attributes about one instance of an entity.

Column, A single property or characteristic of an

Field
Attribute entity (e.g., Name, DateOfBirth).

An attribute (or combination) that uniquely

Primary
— identifies each row in a table. Must be
Key (PK)
unique and NOT NULL. (e.g., StudentID)

Any attribute (or group) that could be

chosen as the primary key – it is unique and
Candidate
— non-null. A table may have several
Key
candidate keys; one becomes PK, others
are alternate keys.

Secondary — A non-unique attribute used for searching or

Key sorting (e.g., LastName). Speeds up queries
 Synonym(
Term Definition
s)

via indexing.

An attribute in one table that refers to the

Foreign
— primary key of another table. It creates a
Key (FK)
relationship between tables.

Relationshi An association between two entities. Types:

—
p one-to-one, one-to-many, many-to-many.

A rule that a foreign key value must either

Referential match an existing primary key in the
—
Integrity referenced table, or be NULL (if allowed).
Prevents "orphan" records.

A database structure (like a hash table or B-

tree) that speeds up data retrieval on
Indexing — selected columns. It trades storage space
and insert/update speed for
faster SELECT queries.

Entity-Relationship (E-R) Diagrams

An E-R diagram is a visual, conceptual design tool that shows entities
and the relationships between them, independent of any specific DBMS.

Symbols (Chen notation – most common in A Level)

Symbol Meaning

Rectangle Entity

Diamond Relationship

Ellipse Attribute (often underlined for primary key)

Symbol Meaning

Line Connects entity to relationship

Relationship Types

E-R
Exam3pl
Type Description Notatio
e
n

One-to- One record in entity A

Person ↔
One relates to at most one record 1---1
Passport
(1:1) in entity B, and vice versa.

One record in entity A

One-to-
relates to many records in Customer →
Many 1---M
entity B, but each B relates Order
(1:M)
to only one A.

Many-
One record in A relates to
to- Student ←→
many in B, and one record in M---N
Many Course
B relates to many in A.
(M:N)

Important: In a physical relational database, many-to-many relationships

cannot be directly implemented. They must be broken into two one-to-
many relationships using an associative table (junction table).

Below is an entity-relationship (ER) diagram for a school

database, following the crow’s foot notation. The example models
students, classes, and the many-to-many relationship Enrols,
which is resolved using an associative entity called Enrolment.

Entities and their attributes

 Student
StudentID (primary key), Name, DateOfBirth
  Class
ClassID (primary key), ClassName, Teacher

 Enrolment (associative entity)

StudentID (foreign key), ClassID (foreign
key), EnrolmentDate, Grade
The composite primary key is (StudentID, ClassID).

Relationship cardinalities

 A Student can enrol in many Classes → one-to-many between

Student and Enrolment.

 A Class can have many Students → one-to-many between Class

and Enrolment.

 Therefore Enrolment links Student and Class in

a many-to-many relationship, which is implemented as two
one-to-many relationships.

ER diagram in text (crow’s foot notation)

+-------------+ +-------------+

| Student | | Class |

+-------------+ +-------------+

| StudentID | | ClassID |

| Name | | ClassName |

| DateOfBirth | | Teacher |

+-------------+ +-------------+

| (1) | (1)

| |

| (M) | (M)

v v

+--------------------------------------------------+

| Enrolment |

+--------------------------------------------------+

| StudentID (FK) |
| ClassID (FK) |

| EnrolmentDate |

| Grade |

+--------------------------------------------------+

Key to symbols

 (1) – one side of the relationship (mandatory participation assumed)

 (M) – many side of the relationship (crow’s foot)

 PK – primary key

 FK – foreign key

Explanation of notation (syllabus style)

 Entity – a real-world object (e.g., Student, Class). Drawn as a

rectangle.

 Attribute – a property of an entity (e.g., Name, DateOfBirth). Listed

inside the rectangle, with PK or FK where appropriate.

 Relationship – an association between entities. Here Student and

Class are connected via the Enrolment entity (many-to-many).

 Cardinality – the number of possible related occurrences.

o One Student → many Enrolments → (1) on Student

side, (M) on Enrolment side.

o One Class → many Enrolments → (1) on Class side, (M) on

Enrolment side.

 Participation – in this diagram every Enrolment must belong to

exactly one Student and one Class (mandatory participation),
indicated by the absence of a circle (optional) on the line

# Normalisation
. Normalisation is the process of organising data to reduce redundancy
and improve integrity. It involves splitting tables into smaller, well-
structured ones.

Why Normalise?

 Eliminate duplicate data

 Avoid update anomalies (insert, update, delete)

 Ensure referential integrity

Three Normal Forms (1NF, 2NF, 3NF)

First Normal Form (1NF)

Rule: A table is in 1NF if:

1. All attributes contain atomic (indivisible) values – no repeating

groups or arrays.

2. Each record is unique (has a primary key).

Violation example:

OrderID Customer Items

101 John "Laptop, Mouse"

Solution: Create separate rows for each item.

OrderID Customer Item

101 John Laptop

101 John Mouse

Second Normal Form (2NF)

Prerequisite: Must be in 1NF.

Rule: No partial dependency – every non-key attribute must depend on

the entire primary key (relevant only for tables with composite primary
keys).

Violation example (composite PK = {StudentID, CourseID}):

StudentI CourseI StudentNa Grad

D D me e

S1 C101 Alice A

Here, StudentName depends only on StudentID, not on CourseID. This is a

partial dependency.
Solution: Split into two tables:

 Student_Course (StudentID, CourseID, Grade) – PK = both columns

 Student (StudentID, StudentName) – PK = StudentID

Third Normal Form (3NF)

Prerequisite: Must be in 2NF.

Rule: No transitive dependency – non-key attributes must depend only

on the primary key, not on other non-key attributes.

Violation example:

StaffI Nam DeptI DeptNa

D e D me

101 Jane D1 Sales

DeptName depends on DeptID, not directly on StaffID. This is a transitive

dependency (StaffID → DeptID → DeptName).

Solution: Move department data to a separate table.

Staff table: (StaffID, Name, DeptID)

Department table: (DeptID, DeptName) – DeptID is PK and FK in Staff.

How to Check if a Table is in 3NF

Ask:

1. Is it in 2NF? (Yes → continue)

2. Are there any non-key attributes that determine other non-key

attributes? If yes → not 3NF.

Producing a Normalised Design (Step-by-Step)

1. Write a list of all attributes from the problem description or

sample data.

2. Identify functional dependencies (e.g., StaffID → Name,

DeptID; DeptID → DeptName).

3. Create a single unnormalised table (including repeating

groups).

4. Convert to 1NF – eliminate repeating groups by creating separate

rows.
 5. Convert to 2NF – remove partial dependencies by splitting tables
where a non-key depends on part of a composite PK.

6. Convert to 3NF – remove transitive dependencies: any attribute

that depends on another non-key attribute moves to a new table.

7. Assign primary keys to each resulting table. Add foreign keys to

maintain relationships.

8.2 Database Management Systems (DBMS)

A Database Management System (DBMS) is the software that sits

between the user/application and the physical data files. Examples:
MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

Features of a DBMS that Address File-Based Issues

Addresses
Feature What it does which
limitation

Data Stores metadata: table names, column

Data
dictionary types, constraints, indexes, users,
dependence,
(system permissions. The DBMS consults it for
isolation
catalog) every operation.

Data Provides tools (like E-R diagrams, DDL) to Redundancy,

modelling define logical structure. inconsistency

The overall logical design (tables,

Logical Data
relationships). Separates programs from
schema dependence
physical storage.

Enforces PK, FK, UNIQUE, CHECK, NOT

Data Inconsistency,
NULL constraints; referential integrity;
integrity integrity
transactions.

Data User authentication; access rights at Security, data

security table/column/row level (GRANT/REVOKE); loss
backup & recovery procedures
 Addresses
Feature What it does which
limitation

(full/differential/log backups).

# Backup Procedures
 Full backup – copy of entire database.

 Incremental backup – only changes since last backup.

 Transaction log – every change recorded; used for point-in-time

recovery.

*Access Rights

Example SQL:

sql

GRANT SELECT, INSERT ON Students TO 'teacher'@'localhost';

REVOKE DELETE ON Students FROM 'assistant'@'localhost';

*Software Tools in a DBMS

Tool Purpose

GUI or command-line environment (e.g., phpMyAdmin, SQL

Developer
Server Management Studio, psql) for writing SQL, designing
interface
tables, managing users.

Subsystem that parses, optimises, and executes SQL queries.

Query
Includes: parser, query optimiser (chooses best index/join order),
processor
execution engine.

How the Query Processor Works (simplified)

1. Parsing – checks syntax and validates table/column names using

the data dictionary.
 2. Optimisation – generates multiple execution plans and estimates
cost (disk I/O, CPU). Chooses cheapest.

3. Execution – runs the plan and returns results.

8.3 Data Definition Language (DDL) and Data

Manipulation Language (DML)
The DBMS provides two sublanguages of SQL:

Language Purpose Commands

DDL (Data Define and modify

CREATE, ALTER, DROP, TRUN
Definition database schema
CATE
Language) (structure)

DML (Data
Query and modify data SELECT, INSERT, UPDATE, DE
Manipulation
inside tables LETE
Language)

Industry standard: Both DDL and DML are standardised as SQL

(Structured Query Language). The syllabus follows ANSI/ISO SQL.

DDL Statements (You must be able to write and understand)

CREATE DATABASE

sql

CREATE DATABASE University;

CREATE TABLE

Basic syntax with data types:

sql

CREATE TABLE Students (

StudentID INTEGER PRIMARY KEY,

FirstName VARCHAR(50), -- Variable length up to 50 characters

LastName VARCHAR(50) NOT NULL,

DateOfBirth DATE,
 IsEnrolled BOOLEAN,

GPA REAL

);

Data types as per syllabus:

Data
Description Example
Type

Fixed-length character string, padded with

CHAR(n) CHAR(4)
spaces. Efficient for codes like 'A001'.

VARCHAR( Variable-length string, max length n. Saves

VARCHAR(255)
n) space.

IsActive
BOOLEAN TRUE or FALSE (or 1/0 in some DBMS)
BOOLEAN

INTEGER Whole number (typically 4 bytes) Age INTEGER

REAL Floating-point number (approximate) Price REAL

DATE Calendar date (YYYY-MM-DD storage) HireDate DATE

StartTime
TIME Time of day (HH:MM:SS)
TIME

ALTER TABLE

Used to change structure after creation.

sql

-- Add a new column

ALTER TABLE Students ADD COLUMN Email VARCHAR(100);

-- Modify a column's data type (not all DBMS allow this easily)

ALTER TABLE Students ALTER COLUMN GPA DECIMAL(3,2);

-- Drop a column

ALTER TABLE Students DROP COLUMN IsEnrolled;

Adding Constraints with ALTER

sql

-- Add a primary key (if not defined at CREATE)

ALTER TABLE Students ADD PRIMARY KEY (StudentID);

-- Add a foreign key

ALTER TABLE Enrolments ADD FOREIGN KEY (StudentID) REFERENCES

Students(StudentID);

Full CREATE TABLE with Constraints (inline and out-of-line)

sql

CREATE TABLE Enrolments (

StudentID INTEGER,

CourseCode CHAR(6),

Grade VARCHAR(2),

PRIMARY KEY (StudentID, CourseCode),

FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

FOREIGN KEY (CourseCode) REFERENCES Courses(CourseCode)

);

DML Statements (Write SQL for up to two tables)

SELECT Queries

sql

-- Basic

SELECT FirstName, LastName FROM Students;

-- WHERE filter

SELECT * FROM Enrolments WHERE Grade = 'A';

-- ORDER BY (default ASC, can use DESC)

SELECT Name, Price FROM Products ORDER BY Price DESC;

-- GROUP BY with aggregate functions

SELECT CourseCode, COUNT(*) AS NumStudents, AVG(GradePoints) AS

AvgGrade

FROM Enrolments

GROUP BY CourseCode;

-- SUM, COUNT, AVG examples

SELECT SUM(Quantity) AS TotalItems, AVG(UnitPrice) AS AvgPrice

FROM OrderItems;

-- INNER JOIN (combine two tables)

SELECT [Link], [Link], [Link]

FROM Students

INNER JOIN Enrolments ON [Link] = [Link];

Data Maintenance (INSERT, UPDATE, DELETE)

sql

-- Insert full row

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth)

VALUES (12345, 'Alice', 'Wu', '2005-03-20');

-- Insert multiple rows

INSERT INTO Courses VALUES

('CS101', 'Computer Science', 4),

('MA101', 'Calculus', 4);

-- Update records

UPDATE Students SET GPA = 3.8 WHERE StudentID = 12345;

-- Delete specific records

DELETE FROM Enrolments WHERE CourseCode = 'CS101' AND Grade = 'F';

-- Delete all rows (but keep table structure)

DELETE FROM OldLogs;

Understanding Given SQL Statements

You must be able to read and explain any SQL code matching the syllabus
subset. For example:

Given:

sql

SELECT DepartmentID, COUNT(*) AS StaffCount

FROM Employees

WHERE Salary > 30000

GROUP BY DepartmentID

ORDER BY StaffCount DESC;

Explanation: For each department (group) where employees earn over

30,000, count how many such employees, show department ID and count,
sorted highest count first.