Database Systems

Flat File vs Relational Databases

Flat File Database

A flat file database stores all data in a single table (or file). Every record contains all the data, which often leads to significant data redundancy (the same data stored multiple times).

Example — Flat File:

StudentID	StudentName	CourseID	CourseName	TutorName
S101	Alice	C1	Maths	Mr Smith
S102	Bob	C1	Maths	Mr Smith
S103	Charlie	C2	English	Ms Jones
S101	Alice	C2	English	Ms Jones

Problems with flat files:

• Data redundancy — “Maths”, “Mr Smith” repeated for every student on that course
• Data inconsistency — if Mr Smith changes his name, it must be updated in every row
• Insertion anomaly — cannot add a new course without assigning a student to it
• Deletion anomaly — deleting the last student on a course removes all data about that course
• Update anomaly — changing a course name requires updating multiple rows

Relational Database

A relational database stores data across multiple linked tables, each representing a single entity. Tables are linked through key fields, eliminating redundancy and the anomalies listed above.

Comparison

Feature	Flat File	Relational Database
Structure	Single table	Multiple linked tables
Redundancy	High	Low (data stored once)
Data integrity	Poor (inconsistencies likely)	High (enforced through keys and constraints)
Anomalies	Insertion, deletion, update anomalies	Eliminated through normalisation
Complexity	Simple to set up	More complex structure
Storage	Wastes space	Efficient use of storage
Querying	Limited	Powerful querying with SQL

---

Keys

Keys are fields that serve special purposes in a relational database, used to uniquely identify records and create relationships between tables.

Primary Key

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. Every table must have a primary key.

Rules for primary keys:

• Must be unique — no two records can have the same primary key value
• Must be not null — every record must have a value for the primary key
• Should be stable — the value should not change over time
• Should be minimal — use the fewest fields necessary

Example: StudentID in a Student table — each student has a unique ID.

Foreign Key

A foreign key is a field in one table that references the primary key of another table. Foreign keys create the links (relationships) between tables.

Example: TutorID in the Student table is a foreign key that references TutorID (the primary key) in the Tutor table.

Composite Key

A composite key is a primary key made up of two or more fields combined. Individually, neither field is unique, but together they uniquely identify a record.

Example: In an Enrolment table (junction table), neither StudentID nor CourseID alone is unique (a student takes many courses, and a course has many students). Together, the combination of StudentID + CourseID uniquely identifies each enrolment.

StudentID (FK/PK)	CourseID (FK/PK)	EnrolmentDate
S101	C1	01/09/2025
S101	C2	01/09/2025
S102	C1	01/09/2025

The composite primary key is (StudentID, CourseID).

Secondary Key

A secondary key is a field that is not the primary key but is used frequently for searching or sorting. An index is typically created on secondary keys to speed up queries.

Example: Surname in a Student table — not unique, but frequently searched.

Summary of Key Types

Key Type	Purpose	Example
Primary Key	Uniquely identifies each record in a table	StudentID
Foreign Key	Links one table to another (references a primary key)	TutorID in Student table
Composite Key	A primary key made up of two or more fields	(StudentID, CourseID)
Secondary Key	Used for searching/sorting, indexed for speed	Surname

---

Entity-Relationship Diagrams

An entity-relationship (E-R) diagram is a visual representation of the entities in a database and the relationships between them. ERDs are a key tool in database design.

Components of an ERD

Component	Representation	Description
Entity	Rectangle	A thing about which data is stored (e.g. Student, Course, Order). Represents a table in a database.
Attribute	Listed inside or beside the entity (sometimes in ovals)	A data item stored about an entity (e.g. StudentID, Name, DateOfBirth)
Relationship	Diamond or labelled line between entities	How two entities are related (e.g. “enrols on”, “places”)
Primary Key	Underlined attribute	The attribute that uniquely identifies each record in the entity

Types of Relationships

There are three types of relationships between entities:

One-to-One (1:1)

Each instance of Entity A is associated with exactly one instance of Entity B, and vice versa.

Example: Each Employee has one DrivingLicence, and each DrivingLicence belongs to one Employee.

graph LR
    EMPLOYEE[EMPLOYEE] -- "1 : 1" --- DRIVING_LICENCE[DRIVING_LICENCE]

One-to-Many (1:M)

Each instance of Entity A can be associated with many instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A.

Example: One Tutor teaches many Students, but each Student has only one Tutor.

graph LR
    TUTOR[TUTOR] -- "1 : M" --- STUDENT[STUDENT]

This is the most common relationship type. It is implemented by placing the primary key of the “one” side as a foreign key in the table on the “many” side.

Many-to-Many (M:M)

Each instance of Entity A can be associated with many instances of Entity B, and vice versa.

Example: A Student can enrol on many Courses, and each Course can have many Students.

graph LR
    STUDENT[STUDENT] -- "M : M" --- COURSE[COURSE]

Important: A many-to-many relationship cannot be directly implemented in a relational database. It must be resolved by creating a junction table (also called a linking table or associative entity) that sits between the two tables, converting the M:M relationship into two 1:M relationships.

graph LR
    STUDENT[STUDENT] -- "1 : M" --- ENROLMENT[ENROLMENT]
    COURSE[COURSE] -- "1 : M" --- ENROLMENT

The Enrolment (junction) table typically contains the primary keys of both Student and Course as foreign keys, often combined as a composite primary key.

Database Management System (DBMS)

A Database Management System (DBMS) is the software that sits between the user/application and the physical database. It provides tools for creating, managing, querying, and securing the database. Examples include MySQL, Microsoft Access, Oracle, and PostgreSQL.

Functions of a DBMS

Function	Description
Data Definition Language (DDL)	Define and modify the database structure: create tables, define fields, data types, primary/foreign keys, constraints
Data Manipulation Language (DML)	Insert, update, delete, and query data using SQL
Data Dictionary	Maintains metadata about the database (see below)
Security management	User accounts, passwords, access rights, permissions (read, write, delete per table/field)
Backup and recovery	Tools to create backups and restore the database after failure
Data integrity	Enforces validation rules, referential integrity (foreign keys must reference valid primary keys), and data types
Concurrency control	Manages simultaneous access by multiple users, preventing conflicts using record locking
Query processing	Optimises and executes queries efficiently

Advantages of Using a DBMS

• Data independence — applications do not need to know how data is physically stored; the DBMS handles this
• Reduced redundancy — normalised tables store data once
• Improved data integrity — validation and constraints enforced centrally
• Improved security — fine-grained access control per user, per table
• Concurrent access — multiple users can access the database simultaneously
• Centralised backup — one backup covers all the data

Disadvantages of Using a DBMS

• Cost — DBMS software can be expensive to purchase and maintain
• Complexity — requires trained database administrators (DBAs)
• Performance overhead — the DBMS adds a layer of processing
• Single point of failure — if the DBMS fails, all applications lose access to data

---

Data Dictionary

A data dictionary is a centralised repository of metadata — data about the data. It is maintained automatically by the DBMS and is used internally to manage the database.

Contents of a Data Dictionary

Metadata	Description
Table names	Names of all tables in the database
Field names	Names of all fields in each table
Data types	The data type of each field (e.g., VARCHAR, INTEGER, DATE)
Field sizes	Maximum length or precision of each field
Primary keys	Which field(s) form the primary key for each table
Foreign keys	Which fields are foreign keys and which table/field they reference
Constraints	Validation rules (e.g., NOT NULL, UNIQUE, CHECK)
Default values	Default values for fields when no data is entered
Indexes	Which fields are indexed for faster searching
Access rights	Which users have read/write/delete permissions for each table

Example Data Dictionary Entry

For the Student table:

Field Name	Data Type	Size	Key	Constraints	Default
StudentID	VARCHAR	5	Primary Key	NOT NULL, UNIQUE	—
StudentName	VARCHAR	50	—	NOT NULL	—
Mark	INTEGER	—	—	CHECK (Mark >= 0 AND Mark <= 100)	0
TutorID	VARCHAR	3	Foreign Key (Tutor.TutorID)	NOT NULL	—

Purpose of the Data Dictionary

• Enforces consistency — ensures all applications use the same definitions for fields
• Supports query processing — the DBMS uses it to validate and optimise queries
• Aids database administration — DBAs use it to understand and manage the database structure
• Documents the database — serves as a reference for developers

---

Referential Integrity

Referential integrity is a database concept that ensures relationships between tables remain consistent. It means that a foreign key value must either match an existing primary key value in the referenced table or be null.

Rules

• You cannot add a record with a foreign key value that does not exist as a primary key in the related table
• You cannot delete a record from a parent table if there are records in a child table that reference it (unless cascade delete is enabled)
• You cannot change a primary key value if foreign keys in other tables reference it (unless cascade update is enabled)

Example: If you try to insert a student with TutorID = 'T9' but no tutor with TutorID = 'T9' exists in the Tutor table, the DBMS will reject the insertion.

---

User Views

What is a User View?

A user view (also called a subschema or view) is a tailored representation of the database that shows only the data relevant to a particular user or group of users. Different users of the same database see different subsets of the data.

In a relational database, views are typically implemented using stored queries — a view appears to the user as if it were a table, but its contents are derived from one or more underlying tables.

Advantages of Different User Views

Advantage	Explanation
Security	Users can only see data they are authorised to access. Sensitive fields (e.g. salaries, medical records) can be hidden from users who do not need them
Simplicity	Users see only the data relevant to their role, reducing complexity and the risk of confusion
Data independence	The underlying table structure can change without affecting users’ views, as long as the view is updated accordingly
Consistency	All users see a consistent, up-to-date version of the data — there are no separate copies of data for different departments
Reduced errors	Users cannot accidentally modify data in tables they have no need to access

Example: School Database

User	View
Teacher	Can see student names, class lists, and marks for their own classes only
Admin staff	Can see student names, addresses, and contact details; cannot see academic records
Finance department	Can see fee payment records; cannot see academic or personal details
Headteacher	Can see all data across all departments

The underlying database contains all of this data in shared tables, but each user’s view filters it appropriately.