Show understanding of and use the terminology associated with a relational database model

8.1 Database Concepts

Objective

Show understanding of and use the terminology associated with a relational database model.

Key Terminology

• Database – an organised collection of data stored electronically.
• Relational Database Management System (RDBMS) – software that creates, maintains and provides access to a relational database.
• Table (Relation) – a set of rows (records) and columns (attributes) that stores data about a particular entity.
• Row (Tuple, Record) – a single, complete set of related data items in a table.
• Column (Attribute, Field) – a named data item that holds a particular type of information for all rows.
• Primary Key – a column (or combination) that uniquely identifies each row in a table.
• Foreign Key – a column that creates a link between two tables by referencing the primary key of another table.
• Relationship – the logical connection between tables (one‑to‑one, one‑to‑many, many‑to‑many).
• Normalization – the process of organising tables to reduce redundancy and improve integrity.
• SQL (Structured Query Language) – the standard language used to query and manipulate relational databases.

Structure of a Relational Table

A relational table can be represented as a matrix where each cell holds an atomic value. The formal definition is:

\$\$

R = \{ (a1, a2, \dots , an) \mid ai \in D_i \}

\$\$

where \$R\$ is a relation, \$ai\$ are attribute values, and \$Di\$ are the domains of the attributes.

Example: Student‑Course Database

The following tables illustrate a typical university database.

Common Relationships

1. One‑to‑One (1:1) – each row in Table A relates to at most one row in Table B and vice‑versa.
2. One‑to‑Many (1:N) – a single row in Table A can be related to many rows in Table B, but each row in B relates to only one row in A. The foreign key is placed in the “many” side.
3. Many‑to‑Many (M:N) – rows in Table A can relate to multiple rows in Table B and vice‑versa. Implemented using a junction (associative) table that contains two foreign keys.

Normalization Overview

Normalization is typically described in normal forms. The first three are most relevant for A‑Level:

• First Normal Form (1NF): All attribute values are atomic; no repeating groups.
• Second Normal Form (2NF): In 1NF and every non‑key attribute is fully functionally dependent on the whole primary key.
• Third Normal Form (3NF): In 2NF and no transitive dependencies exist (non‑key attributes depend only on the primary key).

Sample SQL Statements

Below are examples of SQL commands that use the terminology introduced.

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name \cdot ARCHAR(100),
Email \cdot ARCHAR(100)
);
INSERT INTO Students (StudentID, Name, Email)
VALUES (1003, 'Charlie Davis', 'charlie@example.com');
SELECT s.Name, e.CourseCode, e.Semester
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.CourseCode = 'CS101';

Key Points to Remember

• Data is stored in tables; each table represents an entity.
• Primary keys guarantee uniqueness; foreign keys enforce referential integrity.
• Relationships define how tables are linked; choose the correct cardinality.
• Normalization reduces redundancy and improves data integrity.
• SQL is the language for defining, querying, and manipulating relational data.