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 | PK | StudentName | |
|---|---|---|
| S01 | Maya | maya01@example.invalid |
| S02 | Leon | leon02@example.invalid |
| S03 | Maya | maya03@example.invalid |
| ClubID | PK | ClubName | Room |
|---|---|---|
| C01 | Robotics | AIR Lab |
| C02 | Game Design | Lab 2 |
| StudentID | PK part, FK | ClubID | PK part, FK | Role |
|---|---|---|
| S01 | C01 | Builder |
| S01 | C02 | Designer |
| S02 | C01 | Programmer |
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.
StudentID | primary key
One student → many memberships
(StudentID, ClubID) | composite primary key
Each column also references its parent table
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.