Relational Databases
A relational database organises data into tables (relations) that are linked together through keys. This structure eliminates data redundancy and makes querying efficient.
Learning Objectives
- 11.4.1.1 Describe relational databases and their purpose
- 11.4.1.2 Use terms: attribute, object, index, record, tuple
- 11.4.1.4 Define data types
- 11.4.1.3 Explain primary, composite, and foreign keys
Conceptual Anchor
The Spreadsheet Evolved
Imagine a spreadsheet where each sheet is a table, each row is a record, and each column is a field. Now imagine the sheets are linked — a student sheet connects to a grades sheet via student ID. That's a relational database: multiple linked tables working together.
Rules & Theory
Database Terminology
| Term | Also Called | Meaning | Example |
|---|---|---|---|
| Table (Relation) | Entity | A collection of related data | Students, Courses |
| Record (Row) | Tuple | A single entry in the table | One student's data |
| Field (Column) | Attribute | A single category of data | Name, Age, Email |
| Object | — | Any defined structure in the database | Table, query, form |
| Index | — | A data structure that speeds up searching | Index on StudentID |
Example Table: Students
Students Table:
┌──────────┬──────────┬─────┬──────────────────┬────────┐
│ StudentID│ Name │ Age │ Email │ Class │
├──────────┼──────────┼─────┼──────────────────┼────────┤
│ S001 │ Ali │ 16 │ ali@nis.edu.kz │ 11A │
│ S002 │ Dana │ 16 │ dana@nis.edu.kz │ 11B │
│ S003 │ Arman │ 17 │ arman@nis.edu.kz │ 11A │
└──────────┴──────────┴─────┴──────────────────┴────────┘
↑ Primary Key ↑ Attributes (fields)
Each row = one record (tuple)Types of Keys
| Key Type | Definition | Example |
|---|---|---|
| Primary Key (PK) | Uniquely identifies each record. Cannot be NULL or duplicate. | StudentID |
| Foreign Key (FK) | A field in one table that links to the PK of another table. | StudentID in Grades table |
| Composite Key | A PK made from two or more fields combined. | (StudentID + CourseID) |
| Candidate Key | Any field that could serve as a PK. | Email (unique per student) |
How Keys Link Tables
Students Table: Grades Table:
┌──────────┬────────┐ ┌──────────┬──────────┬───────┐
│ StudentID│ Name │ │ GradeID │ StudentID│ Mark │
├──────────┼────────┤ ├──────────┼──────────┼───────┤
│ S001 │ Ali │◄───────────│ G001 │ S001 │ 85 │
│ S002 │ Dana │◄───────────│ G002 │ S002 │ 92 │
│ S003 │ Arman │ │ G003 │ S001 │ 78 │
└──────────┴────────┘ └──────────┴──────────┴───────┘
PK (Primary Key) FK (Foreign Key)
Links to Students.StudentIDCommon Data Types
| Data Type | Description | Example Values |
|---|---|---|
| INTEGER | Whole numbers | 1, 42, -7 |
| REAL / FLOAT | Decimal numbers | 3.14, 92.5 |
| TEXT / VARCHAR | Strings of characters | "Ali", "ali@nis.edu.kz" |
| BOOLEAN | True/False | TRUE, FALSE |
| DATE | Calendar date | 2025-02-19 |
| CHAR(n) | Fixed-length string | "S001" (CHAR(4)) |
Why Relational?
The word "relational" comes from mathematical relations (tables). The key advantage is data integrity — by linking tables through keys, you avoid storing the same data in multiple places, reducing errors and saving storage.
Common Pitfalls
Using Name as Primary Key
Names are NOT unique — two students can have the same name. Always use an ID field (e.g., StudentID) as the primary key.
Confusing Primary and Foreign Keys
A primary key uniquely identifies records in its OWN table. A foreign key is a reference to a primary key in ANOTHER table.
Tasks
Define: table, record, field, primary key, foreign key, composite key.
Design a database for a school library with tables: Books, Members, Loans. Identify the PK and FK for each table.
A student proposes using "Email" as the primary key for a Users table. Discuss 2 advantages and 2 disadvantages of this choice.
Self-Check Quiz
Q1: What is a primary key?
Q2: What is the difference between a record and a field?
Q3: When would you use a composite key?