A3.1 · Book pp. 168–173
Database fundamentals
Your school records, your bank balance and every online shop rest on one idea: keep data in linked tables.
What you need to know
There is one syllabus point, A3.1.1: explain the features, benefits and limitations of a relational database, for both SL and HL.
🔬 Try this first
Write down three pieces of data your school stores about you, then three about each class. What links you to your classes?
Tables
A database is an organized collection of structured data. A relational database stores it in tables, one for each entity: a person, thing or event you store data about.
| PlaneID | Model | Manufacturer | Engine | Engines |
|---|---|---|---|---|
| A01 | Rockwell Commander 112 | Rockwell | Piston | 1 |
| A02 | Airbus A319 Neo | Airbus | Jet | 2 |
| A03 | Boeing 747-100 | Boeing | Jet | 4 |
| A04 | Boeing 777-8 | Boeing | Jet | 2 |
| A05 | Boeing 747-100 | Boeing | Jet | 4 |
| Row (tuple, record) | One instance, here one aeroplane |
| Column (attribute, field) | One characteristic, here its manufacturer |
| Primary key | A field unique to each record, here PlaneID. Without it, A03 and A05 could not be told apart |
| Foreign key | A field that refers to a primary key in another table |
| Composite key | Two or more attributes that are unique together |
Keys at work
FLIGHTS uses PlaneID as a foreign key. It can repeat there, because one plane makes many flights. PILOTFLIGHT has no single unique field, so FlightID and PilotID together form a composite key.
| FlightID (primary key) | From | To | PlaneID (foreign key) |
|---|---|---|---|
| LG8903 | LUX | OTP | A03 |
| OS864 | CAI | VIE | A04 |
| GB961 | LHR | ZRH | A03 |
Relationships
A relationship links tables through keys, so that data can be divided up and still connected.
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | A record matches at most one in the other table. Rare | A country and its capital city |
| One-to-many (1:m) | A record matches many in the other. The most common | A person with several bank accounts |
| Many-to-one (m:1) | The same relationship, seen from the other side | Many staff work for one company |
| Many-to-many (m:m) | Many records match many others. Needs a linking table | Actors and movies |
💡 Many-to-many
A foreign key holds only one value, so it cannot list many references. A linking table turns one many-to-many into two one-to-many relationships. PILOTFLIGHT does this for flights and pilots.
Referential integrity
A foreign key value must always refer to a record that exists. This stops inconsistent data from being entered.
Transactions
A transaction is a group of actions treated as one unit. It runs in full or not at all.
Check yourself: name each relationship
- Teachers and homerooms, where each teacher has one homeroom. One-to-one.
- Customers and orders. One-to-many.
- Students and clubs. Many-to-many, so it needs a linking table.
Benefits
| Community support | Decades of use, so help is easy to find |
| Concurrency control | Many users can work at once without conflict |
| Data consistency | Everyone sees the same, up-to-date data |
| Data integrity | Rules and validation keep data accurate and complete |
| Data retrieval | SQL gives flexible queries with JOIN and WHERE |
| Reduced duplication and redundancy | Keys link tables, so details are stored only once |
| Reliable transactions | A transaction happens in full or not at all |
| Scalability | Can grow by adding CPU, RAM or storage to a server |
| Security | User accounts, permissions and restricted views |
Limitations
| Big data scaling | Hard to spread across many machines, and joins slow down at scale |
| Design complexity | Tables and relationships need careful planning |
| Hierarchical data | Trees fit tables poorly, and are slow to traverse |
| Rigid schema | Structure is set in advance and hard to change |
| Object-relational mismatch | Objects, references and inheritance do not map cleanly to tables |
| Unstructured data | Records with no common fields cannot fit a schema |
⚠️ Common mistake
General lists of benefits lose marks. Link each point to the scenario in the question, and match the number of marks.
✏️ Book note
The book writes that vertical scaling is cheaper, easier and faster. That is true up to a point, but a single machine has limits. This is why spreading data across many machines (horizontal scaling) matters for big data, and it is what relational databases find hard.
Quick quiz
Exam-style questions
These are written for this site in the style of Paper 1. Answer on paper first, then open the mark points.
-
Define the term “database”. [1]
Mark points
- An organized collection of structured data that can be accessed in different ways [1].
-
Define “primary key” and “foreign key”. [2]
Mark points
- A primary key uniquely identifies a record in a table [1].
- A foreign key is an attribute that refers to the primary key of another table [1].
-
Explain what is meant by referential integrity. [2]
Mark points
- Every foreign key value must match an existing primary key value [1].
- This keeps data consistent during insertions, deletions and updates [1].
-
A school stores which students take which subjects. Explain how to represent this. [3]
Mark points
- It is a many-to-many relationship [1].
- A foreign key can hold only one value, so a linking table is added [1].
- It holds StudentID and SubjectID, forming two one-to-many relationships [1].
-
A music streaming firm is deciding whether to use a relational database for user accounts and billing. Discuss. [4]
Mark points
- Reliable transactions mean that a payment completes fully or not at all [1].
- Integrity rules keep the links between users and payments correct [1].
- Permissions protect personal billing data [1].
- However, scaling could be hard with very large numbers of users, though accounts are structured, so relational suits this data [1].