Database Concepts

This lesson covers the fundamental concepts of databases as required by the OCR A-Level Computer Science (H446) specification, section 1.3. You need to understand the difference between flat files and relational databases, key types, and entity-relationship diagrams.

Why Use a Database?

A database is an organised collection of structured data stored electronically. Databases are essential because:

They provide persistent storage that survives program termination.
They allow multiple users to access data simultaneously.
They support efficient querying -- finding specific data quickly.
They enforce data integrity -- ensuring data is accurate and consistent.
They provide security -- controlling who can access or modify data.

Flat Files vs Relational Databases

Flat Files

A flat file is a single table of data stored as a text file (e.g., CSV or TSV). All data is in one file with no relationships between different types of data.

Example flat file (students.csv):

StudentID,Name,Subject,TeacherName,TeacherEmail
001,Alice,Maths,Mr Smith,smith@school.com
002,Bob,Maths,Mr Smith,smith@school.com
003,Charlie,English,Ms Jones,jones@school.com
004,Alice,English,Ms Jones,jones@school.com

Problems with Flat Files

Problem	Explanation
Data redundancy	The same data is stored multiple times (e.g., Mr Smith's email appears twice).
Data inconsistency	If Mr Smith's email changes, it must be updated in every row -- easy to miss one.
Insert anomaly	Cannot add a new teacher without a student being enrolled.
Delete anomaly	Deleting the last student in a subject removes the teacher's data too.
Update anomaly	Updating data in one place but not another creates inconsistency.

Relational Databases

A relational database organises data into multiple related tables (also called relations). Each table stores data about one type of entity, and tables are linked through keys.

Students table:

StudentID	Name
001	Alice
002	Bob
003	Charlie

Subjects table:

SubjectID	SubjectName	TeacherID
S1	Maths	T1
S2	English	T2

Teachers table:

TeacherID	TeacherName	TeacherEmail
T1	Mr Smith	smith@school.com
T2	Ms Jones	jones@school.com

Exam Tip: When comparing flat files and relational databases, always explain the specific problems (redundancy, anomalies) that flat files cause and how relational databases solve them through normalisation and relationships.

Database Terminology

Term	Definition
Table (Relation)	A collection of related data organised into rows and columns.
Record (Row/Tuple)	A single entry in a table, representing one instance of an entity.
Field (Column/Attribute)	A single piece of data within a record (e.g., Name, Age).
Entity	A real-world object or concept represented by a table (e.g., Student, Teacher).
Attribute	A property of an entity, represented by a column.
Schema	The structure of the database -- tables, fields, data types, and relationships.

Keys

Keys are fields (or combinations of fields) that uniquely identify records and establish relationships between tables.

Primary Key

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

Unique -- no two records can have the same primary key value.
Not null -- every record must have a primary key value.
Stable -- the value should not change over time.

Examples: StudentID, OrderNumber, ISBN.

Foreign Key

A foreign key is a field in one table that references the primary key of another table. It creates a link (relationship) between the two tables.

Students table              Enrolments table
+-----------+------+        +-----------+-----------+
| StudentID | Name |        | StudentID | SubjectID |
+-----------+------+        +-----------+-----------+
| 001       | Alice|        | 001       | S1        |
| 002       | Bob  |        | 001       | S2        |
+-----------+------+        | 002       | S1        |
                            +-----------+-----------+

In the Enrolments table, StudentID is a foreign key referencing the Students table, and SubjectID is a foreign key referencing the Subjects table.

Composite Key

A composite key (or compound key) is a primary key made up of two or more fields together. Neither field alone is unique, but the combination is.

In the Enrolments table above, the composite key is (StudentID, SubjectID) -- neither field alone uniquely identifies a row, but together they do.

Key Type	Definition
Primary key	Uniquely identifies each record in a table.
Foreign key	A field referencing the primary key of another table.
Composite key	A primary key made up of two or more fields.
Candidate key	Any field (or combination) that could serve as a primary key.
Secondary key	A field used for indexing and searching, but not as the primary key.

Entity-Relationship Diagrams (ERDs)

An Entity-Relationship Diagram is a visual representation of the entities in a database and the relationships between them.

Relationship Types

Type	Description	Example
One-to-One (1:1)	One record in table A relates to exactly one record in table B.	Person -- Passport
One-to-Many (1:M)	One record in table A relates to many records in table B.	Teacher -- Students
Many-to-Many (M:M)	Many records in A relate to many in B.	Students -- Subjects

Resolving Many-to-Many Relationships

A many-to-many relationship cannot be directly implemented in a relational database. It must be resolved using a junction table (also called a linking table or bridge table).

Students ---< Enrolments >--- Subjects

The Enrolments table contains foreign keys from both Students and Subjects, creating two one-to-many relationships.

ERD Notation

[Student] 1----M [Enrolment] M----1 [Subject]
   |                                    |
   PK: StudentID                   PK: SubjectID
   Name                            SubjectName
   DOB                             TeacherID (FK)

Exam Tip: OCR exam questions frequently ask you to draw or interpret ERDs. Always show entities as boxes, include primary keys and foreign keys, and label the relationship types (1:1, 1:M, M:M). When you see a M:M relationship, explain that it must be resolved with a junction table.

Data Types

Fields in a database have specific data types that determine what values they can store:

Data Type	Description	Example
INTEGER	Whole numbers	StudentID: 001
REAL/FLOAT	Decimal numbers	Price: 29.99
VARCHAR(n)	Variable-length text up to n characters	Name: "Alice"
CHAR(n)	Fixed-length text of exactly n characters	PostCode: "SW1A 1AA"
BOOLEAN	True or False	IsActive: True
DATE	Calendar date	DOB: 2007-03-15
TEXT	Long text with no fixed limit	Description

Summary

Concept	Definition
Flat file	Single-table data store with redundancy and anomaly problems.
Relational database	Multiple related tables linked by keys.
Primary key	Uniquely identifies each record in a table.
Foreign key	References the primary key of another table.
Composite key	Primary key made of two or more fields.
ERD	Visual diagram showing entities and their relationships.
1:1, 1:M, M:M	Relationship types between entities.
Junction table	Resolves a many-to-many relationship.

Exam Tip: Be prepared to design a database schema for a given scenario. Identify the entities, their attributes, the keys, and the relationships. Draw an ERD and explain why a relational database is better than a flat file for the scenario.