IB COMPUTER SCIENCE | A3.1.1 Relational Databases

Lesson objective

Explain tables, keys and relationships, and how relational database design and DBMS mechanisms produce benefits and limitations.

Learn

A3.1.1 | FEATURES, BENEFITS AND LIMITATIONS OF A RELATIONAL DATABASE

01 | ONE STUDENT, SEVERAL CLUBS, ONE CONSISTENT RECORD

A school records which students attend each club. Maya joins robotics and game design, so a simple combined list repeats her name and email twice. When her email changes, someone updates one row but misses the other. Which version should the school trust?

A relational database organises data as relations, commonly represented as tables, and uses keys and constraints to identify records and connect related data. It can store the student once and record her club memberships separately.

A database management system (DBMS) is the software that stores, queries and manages the data. The database is the organised data and structure; the DBMS supplies mechanisms such as queries, permissions and transactions. Not every benefit appears automatically just because a file has rows and columns.

In this lesson we will build a small club example, then explain how its features support the specification’s benefits and where relational design creates trade-offs.

02 | TABLES, ROWS AND COLUMNS

A table describes one kind of data using named columns. A row, often called a record, contains the values for one instance. A column, often called a field in introductory courses, represents an attribute such as StudentName.

A schema defines the structure and rules, including columns, data types and constraints. A student ID and a name have different purposes even when both are stored as text. Values within a column follow its defined type and permitted rules.

Our example uses Students, Clubs and Enrolments. The headings below label key roles explicitly. The two students named Maya are different people; their distinct IDs let us distinguish them without treating a name as unique.

StudentID | PKStudentNameEmail
S01Mayamaya01@example.invalid
S02Leonleon02@example.invalid
S03Mayamaya03@example.invalid
ClubID | PKClubNameRoom
C01RoboticsAIR Lab
C02Game DesignLab 2
StudentID | PK part, FKClubID | PK part, FKRole
S01C01Builder
S01C02Designer
S02C01Programmer

The email addresses are fictional examples. Enrolments records which student belongs to which club and their role in that membership. The key constraints are part of the intended schema, not merely coloured labels.

03 | PRIMARY KEYS: WHICH EXACT ROW?

A primary key is the chosen identifier for a table’s rows. Its value, or combination of values, must be unique and must not be NULL. In Students, StudentID identifies a particular student even when two students share a name.

NULL represents an absent or unknown value under the database’s rules; it is not the number zero or automatically an empty string. A key cannot depend on an unidentified student.

A table can have several possible unique identifiers, called candidate keys when they meet the relevant minimal key conditions. The primary key is the one selected as the main identifier; it is not simply whichever column happens to appear first.

A key guarantees an identification rule, not real-world truth. A database can accept a unique StudentID paired with an incorrectly typed name unless other checking catches the mistake.

04 | FOREIGN KEYS: CONNECT ROWS WITHOUT COPYING EVERY DETAIL

A foreign key constrains values to reference an appropriate key in another table, or sometimes the same table. In Enrolments, StudentID references Students and ClubID references Clubs. This connects the membership to existing rows.

For example, the Enrolments row S01, C02 identifies Maya S01’s membership of Game Design. Her email stays in Students, while the club room stays in Clubs. We follow IDs to obtain those facts rather than repeating every detail in each membership.

Referential integrity means these references obey the defined rules. With the shown non-null key columns and enforced foreign keys, an enrolment for S99 is rejected if no such student exists. A foreign key can reference a suitable unique key, not only a primary key in every possible design.

Deletion policy matters too: removing a referenced student may be restricted, cascade to dependent rows or follow another configured valid action. Do not assume every relational database silently deletes all related data.

05 | COMPOSITE KEYS AND MANY-TO-MANY RELATIONSHIPS

A composite key uses more than one column together. Enrolments has the primary key (StudentID, ClubID), under our rule that a student has at most one membership record per club in this example.

StudentID alone is not unique in Enrolments because S01 joins two clubs. ClubID alone is not unique because C01 has two members. The pair is unique: S01, C01 and S01, C02 are different memberships. A second S01, C01 row would violate the primary key.

Students and clubs have a many-to-many relationship: a student can join several clubs and a club can have several students. The linking table represents it as two one-to-many relationships. One student relates to many Enrolments rows; one club also relates to many Enrolments rows.

This is one primary key made of two columns, not two independent primary keys. If the system later records separate terms or repeat enrolment events, the identifier and business rules would need reconsideration.

STUDENTS

StudentID | primary key

One student → many memberships

ENROLMENTS

(StudentID, ClubID) | composite primary key

Each column also references its parent table

CLUBS

ClubID | primary key

One club → many memberships

06 | FOLLOW THE RELATIONSHIPS TO RETRIEVE A RESULT

A query asks the DBMS for data or performs an operation under defined rules. A join can combine rows using a matching condition. To list Maya S01’s clubs, match her Enrolments rows with Clubs on ClubID.

The result can show ClubName, Room and Role without storing a second copy of those club details in every enrolment. Matching by StudentName would confuse the two Mayas; matching by StudentID identifies the intended student.

Choose a student to see the derived result. S03 is a valid student with no enrolments in the displayed data. An empty result therefore does not prove that the student record is missing.

07 | REDUCED DUPLICATION AND REDUNDANCY

A combined list might repeat a student’s email once per club and a club’s room once per student. Repeating the same fact in several places creates redundancy: information stored more than is necessary for the intended design.

If Robotics changes room, updating only some copies creates inconsistent versions. Storing the room once in Clubs reduces that update problem. Good relational design and later normalization help determine which facts belong together.

Not all repeated values are mistakes. S01 appearing in two Enrolments rows records two genuine relationships. The foreign-key values are useful references, not redundant copies of the student’s whole profile.

Reduced duplication is therefore a design benefit, not a guarantee that every relational database is already well structured. Some systems deliberately add selected redundancy for performance, accepting extra consistency work.

08 | CONSISTENCY AND INTEGRITY: RULES WITH A LIMIT

Data consistency concerns compatible, non-contradictory states or versions under the relevant rules. Data integrity concerns the validity and reliability of data and relationships. The terms overlap in practice, but neither means “all stored facts must be true”.

Constraints can enforce identifiers, references, mandatory fields, ranges and other stated rules. A primary key prevents duplicate identifiers; a foreign key prevents the invalid references specified by its definition; a check constraint can require a permitted value.

For example, S99 cannot become a membership reference in our constrained model because it has no parent student. But the DBMS cannot discover that a valid email was typed for the wrong real person merely from that format. Good input handling, verification and appropriate business rules remain necessary.

Try proposed enrolments below. The activity evaluates only the shown key/reference rules and does not change the tables or validate the person’s real-world eligibility.

09 | RELIABLE TRANSACTIONS: COMPLETE ONE LOGICAL UNIT

A transaction groups related operations into one logical unit. Suppose booking a trip place must reduce remaining capacity and create a booking. Keeping only the capacity change while losing the booking would leave an inconsistent result.

A suitable transactional DBMS can commit the successful unit or roll back its changes if it cannot complete. Atomicity is the all-or-nothing property. Durability means committed changes persist under the system’s stated guarantees.

The familiar ACID properties also include consistency with defined rules and isolation between concurrent work. Exact guarantees depend on the DBMS, transaction/isolation settings, application use and operational environment. A product name alone does not correct an incorrectly designed booking process.

Advance through a small transaction with either success or failure. The display separates provisional transaction changes from the committed model state. It assumes the whole booking operation is correctly grouped and no concurrent booking occurs.

10 | CONCURRENCY CONTROL: TWO PEOPLE, ONE LAST PLACE

Concurrency means multiple users or operations overlap in time. If two administrators both read “one place left” and independently approve a booking before either checks the other’s update, two bookings can be accepted for one place.

Concurrency control coordinates overlapping work using mechanisms such as locking, row versions, suitable isolation and conflict checks. A correctly designed booking transaction can ensure that only one succeeds under the capacity rule; another may wait, see no capacity or need to retry.

This supports consistency without making every user work entirely alone. However, waiting, conflicts, deadlocks or retries can affect performance and application complexity. The exact behaviour depends on the chosen mechanisms.

Transactions alone do not automatically fix every business race. The application must use appropriate checks and isolation so the capacity decision remains valid when changes commit.

11 | DATA RETRIEVAL: ASK DIFFERENT QUESTIONS

Structured tables and relationships support queries such as “which students attend Robotics?”, “which clubs use AIR Lab?” and “how many members are in each club?”. SQL is widely used to select, join, filter and aggregate relational data.

An index is an additional access structure that can help suitable lookups and joins. It does not mean every query becomes instant: data volume, query design, selectivity, storage and resources all matter. Indexes also require storage and maintenance on changes.

Separating facts can make retrieval flexible because different related tables can be combined for different questions. The trade-off is that joins may require planning and processing compared with reading a prebuilt report.

A database query retrieves the result under its logic and permissions. Asking for the wrong ID or using an incorrect join can still produce an inappropriate result, even if the DBMS follows the query correctly.

12 | SCALABILITY AND COMMUNITY SUPPORT

Scalability is the ability to handle growth while maintaining acceptable service. Relational systems can support substantial data and many users through indexing, suitable resources, replication, partitioning and other supported techniques. These are engineering choices, not a promise of unlimited capacity.

Mature database ecosystems can provide documentation, drivers, administration tools, learning resources and community discussion. Community support can reduce development difficulty and help teams solve known problems. Its breadth and quality vary with the product and community.

Support is not a property guaranteed by the mathematics of tables. An abandoned product may have poor support, and a forum answer is not a tested recovery plan. Teams still need appropriate expertise, maintained software and dependable operations.

Likewise, a relational DBMS on a small device and a distributed commercial platform have different capabilities. Explain the benefit in the intended environment instead of assuming every relational system has the same scaling features.

13 | SECURITY FEATURES: CONTROL WHO CAN DO WHAT

DBMS security features can include account authentication, roles and privileges, views, auditing and supported encryption. These can separate ordinary reading from privileged changes and help administrators apply common policy.

For example, a club teacher might read membership names while only authorised staff can change student contact details. A view can present a defined result or selected columns, but the complete permission arrangement must prevent unintended alternative access.

Security depends on correct configuration and application behaviour. A shared overprivileged account, exposed backup or unsafe SQL construction can defeat intended restrictions. A relational table is not automatically protected just because it has a primary key.

Reliable operation also needs protected backups, restore tests, monitoring and patching. A committed transaction does not replace a backup, and encryption does not fix every authorisation error.

14 | LIMITATION: DESIGN COMPLEXITY AND A RIGID SCHEMA

Choosing entities, attributes, keys, relationships and constraints takes care. Poor choices can create repeated facts, confusing joins or rules that fail to represent the real system. Designing the schema is therefore a cost as well as a way to improve data quality.

A relational schema supplies a predictable contract, but changing requirements can require a schema migration: a planned change to structure and data, with application updates and testing. Adding a mandatory field to a large existing table is not just typing a new heading.

“Rigid schema” means structure is defined and changes need coordination; it does not mean the schema can never change. Stability helps consistent processing, while rapidly changing data shapes can make that stability less convenient.

If memberships must later record several terms or separate attendance events, the current one-row-per-student-per-club rule may no longer fit. The team must redesign the identifier and relationships rather than force different facts into misleading duplicate rows.

15 | LIMITATION: HIERARCHICAL DATA

Hierarchical data has parent-child levels, such as folders containing subfolders or an organisation with managers and reporting lines. A flat relational table can store a parent reference, including a foreign key back to the same table.

The difficulty appears when an application frequently needs all descendants, an entire nested structure or complex path queries. These can require recursive queries, repeated joins or additional modelling strategies rather than a single straightforward row lookup.

Modern relational systems may support recursive queries and specialised features, so “relational databases cannot store hierarchies” is incorrect. The limitation is the modelling and query effort for the particular workload.

A folder tree can be a useful comparison: storing one row per folder is possible, while repeatedly retrieving an arbitrarily deep tree may be less natural than the application’s nested view of it.

16 | LIMITATION: UNSTRUCTURED AND VARIED DATA

Images, recordings, free-form documents and rapidly varying event details do not always fit naturally into a fixed set of scalar columns. A relational system may store binary content or references to external files, while storing structured metadata in tables.

Many modern products also support JSON or other richer types. JSON is usually described as semi-structured, not a synonym for every kind of unstructured content. Support for storing it does not make every query, relationship or validation rule equally simple.

The key limitation is fit: if most operations involve whole varied documents or media analysis, a different or combined storage design may be more appropriate. Introducing hundreds of optional columns or storing everything as one uninterpreted field can weaken the benefits of structured querying.

Avoid an absolute claim that relational databases cannot hold pictures or JSON. Explain the mismatch between fixed relational structure and the application’s data shapes and retrieval needs.

17 | LIMITATION: OBJECT-RELATIONAL IMPEDANCE MISMATCH

An object-oriented program may represent a Student object containing a list of Club objects and behaviour. The database represents rows, columns and explicit relationships. Moving between those representations requires decisions about identity, references, nested collections and changes.

This difference is called object-relational impedance mismatch. For example, loading one Student object with its clubs may require several related rows or joins, while saving a changed object graph may require several database operations.

An object-relational mapper (ORM) can automate parts of this translation. It can reduce routine code, but developers still need to understand generated queries, transaction boundaries and update behaviour. Automatically loading many small related queries can create performance surprises.

This is a limitation of fit and abstraction, not proof that object-oriented programs cannot use relational databases. The implementation must bridge the representations carefully.

18 | LIMITATION: BIG DATA AND DISTRIBUTED SCALING

At very large volumes, high update rates or geographically distributed workloads, joins, global constraints, replication and transactions can become expensive to coordinate. Splitting data across machines may make some operations more complex because related records are no longer local.

A partition divides data into sections; sharding commonly places sections across different servers. A cross-shard join or transaction can require extra communication and coordination. Some systems support this well, but it is not free.

Relational systems can handle large-scale workloads, including specialised distributed relational designs. The limitation is not “a relational database is always small”; it is that scaling the required operations under the required guarantees can add cost and complexity.

Choose using workload evidence: structured consistent transactions may favour a relational approach, while some huge varied event streams or graph/document-heavy tasks may benefit from other or combined systems. Every alternative has trade-offs too.

19 | CHOOSE THE EXPLANATION THAT FITS

Each situation asks for a mechanism, benefit or limitation. Choose the statement that connects the feature to its consequence without turning a possible benefit into an automatic guarantee.

20 | EXPLAIN FEATURE → MECHANISM → CONSEQUENCE

For explain, do more than list “keys, secure, fast”. Identify the feature or mechanism, state how it works and link it to the outcome using because, therefore or resulting in.

For a benefit: “A student’s email is stored once in Students while enrolments reference the student ID. Therefore a change normally updates one stored fact rather than several repeated copies, reducing the risk of conflicting versions.”

For a limitation: “The application uses nested objects while the database stores linked rows. The program must map between those representations, which adds translation work and can create inefficient queries if the mapping is poorly designed.”

A balanced answer should recognise that keys, constraints, transactions, tools and good design enable the benefits, while schema fit, query costs and operational demands limit them. The next section develops schemas and relational design in detail.

MATCH THE DATABASE IDEA TO ITS ROLE

Terminology

Terminology

Relational database

Data organised in relations commonly represented as tables with defined structures and constraints.

DBMS

Software managing database storage, queries, rules and supported services.

Table

A structured relation represented by rows and named columns.

Row / record

Values representing one instance in a table.

Column / attribute

A named property with defined type and rules.

Schema

The defined data structure and related rules.

Primary key

The selected unique, non-null identifier, potentially comprising several columns.

Foreign key

A constraint requiring relevant values to reference a suitable key under its rules.

Composite key

A key made from a combination of more than one column.

Candidate key

A minimal set of attributes able to uniquely identify rows under the relevant model.

NULL

An absent or unknown value under database rules, distinct from zero.

Relationship

An association represented between relevant data instances.

Linking table

A table recording associations, such as memberships connecting students and clubs.

Referential integrity

Reference values obey their declared foreign-key rules.

Redundancy

More stored representation of a fact than is needed for the intended design.

Data consistency

Compatible non-contradictory data states or versions under the relevant rules.

Data integrity

Validity and reliability of data and relationships under defined requirements.

Query

A request for data or a database operation under specified logic.

Join

An operation combining related rows according to a matching condition.

Index

An additional access structure that can speed suitable retrieval at storage/update cost.

Transaction

A logical unit of database work with supported completion/recovery rules.

Atomicity

The all-or-nothing property of a transaction.

Concurrency control

Coordination of overlapping operations to preserve required guarantees.

Scalability

Supporting growth in data or workload with acceptable service.

Schema migration

A coordinated change to database structure and often existing data/applications.

Hierarchical data

Data organised in parent-child levels.

Unstructured data

Content not naturally described by a fixed conventional tabular field structure.

Object-relational impedance mismatch

Differences between object-oriented representations and relational data structures.

ORM

Software mapping between program objects and database representations.

Sharding

Distributing data sections across servers, with coordination implications for some operations.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which are relational features?
2. Which describe the example keys?
3. Which proposed rows violate shown rules?
4. Which explain reduced redundancy?
5. Which are transaction benefits and limits?
6. Which support retrieval and scale?
7. Which are realistic security statements?
8. Which describe schema/hierarchy limitations?
9. Which describe data-model fit?
10. Which are balanced scaling/support claims?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Explain why primary keys are useful in the club example.

2. Explain the role of foreign keys.

3. Explain why the Enrolments primary key is composite.

4. Explain how relational design reduces inconsistent duplication.

5. Explain how a join retrieves a student’s clubs.

6. Explain transaction reliability using the trip booking.

7. Explain why concurrency control is needed.

8. Explain a benefit and limitation of indexes and SQL retrieval.

9. Explain security and community-support benefits with limits.

10. Explain rigid schema and design complexity.

11. Explain hierarchy, unstructured-data and object-mapping limitations.

12. Explain why big-data scalability is a conditional limitation.

EXPLAIN IT | A SCHOOL CLUB SYSTEM

Explain how Students, Clubs and Enrolments represent memberships. Identify the primary, foreign and composite keys, then connect the design to reduced duplication, consistency and retrieval. Explain why transactions, concurrency control and permissions are also needed, and discuss two relevant limitations if requirements or scale change.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.1.1 Relational Databases is coming soon. Complete the matching task and written questions in your exercise book.