A3.1.1 · Database fundamentals
Relational databases: features, benefits and limitations
A relational database is just tables, connected by keys, but that one idea explains almost everything a real database can do, and can't.
Explainer
Think of a school keeping two separate binders: one listing every student, one listing every subject they take. If a student changes their name, someone has to find and fix it in both binders separately, unless the two binders are linked by something as simple as a shared ID number. That link is the entire idea behind a relational database.
This whole topic answers one question, A3's own guiding question: what are the principles, structures and operations that form the basis of database systems? This statement builds the foundation everything else in A3 sits on: what a relational database actually is, before later statements cover schemas, ERDs and normalization.
A relational database holds everything in tables: a grid in which each column is one attribute and each row is one record. Each table describes one entity (a person, a flight, a product). Each row (a tuple or record) is one instance of that entity. Each column (an attribute or field) is one characteristic of it.
A primary key is the field a table uses to tell its rows apart. Pick any value it holds and exactly one row answers to it, which is why no two rows are ever allowed to share one. A foreign key takes that same idea and points it outward: a field in one table copies the primary key value from a row in a completely different table, and that copied value is the connection, the thing that turns two separate tables into one linked structure.
Reading crow's-foot notation
Relationships between tables are drawn as lines with a symbol at each end, never just an arrow. The symbol names how many records on THAT side can take part. The worked example below uses the plain "one" and "many" symbols; the other four are worth recognising too, since ERDs (a later statement) use all six.
One. Exactly one record on this side, no more, no fewer.
OneMany. Any number of records on this side, including several at once.
ManyOne and only one. The stricter version of "one": this record must exist, and there can never be more than one.
One (and only one)Zero or one. This side is optional: a matching record might not exist at all, but if it does, there's only ever one.
Zero or oneOne or many. At least one record must exist on this side, and there can be more.
One or manyZero or many. Fully optional AND unlimited: no matching record is required, but there's no upper limit either.
Zero or manyWorked example
An airline's database: four tables, three relationships
AEROPLANE lists every plane. FLIGHTS lists every flight, and needs to know which plane flew it. PILOTS lists every pilot. PILOTFLIGHT links flights to the pilots who flew them, since one flight can have more than one pilot, and one pilot flies more than one flight.
| Model |
| Manufacturer |
| Engine |
| Departure |
| Destination |
| Name |
Click any PK badge to see which foreign keys reference it. The connectors use real crow's-foot notation: a single bar (⊣) at the "one" end, a crow's foot (❮) at the "many" end.
Click a key badge above to explore the relationships.
PILOTFLIGHT has no primary key of its own, on purpose, since neither FlightID nor PilotID is unique alone. What makes that pair unique, and how it's different from a concatenated key, is worth its own look after the check below.
The four relationship types
One record relates to exactly one in another table. The rarest of the four in real designs, most pairs of tables need more flexibility than this, a passport record tied to exactly one holder is the kind of case where it genuinely fits.
One record relates to several in another table. AEROPLANE → FLIGHTS: one plane flies many flights.
The same relationship, viewed from the other side. FLIGHTS → AEROPLANE: many flights share one plane.
Many records relate to many others. FLIGHTS ↔ PILOTS, solved above with a linking table.
Referential integrity is the rule that keeps this whole structure honest: a foreign key must always match a real, existing primary key somewhere. A FLIGHTS row can't reference a PlaneID that doesn't exist in AEROPLANE. This is what stops insertion, deletion and updates from quietly breaking the relationships between tables.
Check your understanding
What is the relationship between FLIGHTS and PILOTFLIGHT specifically (not FLIGHTS and PILOTS overall)?
Explainer
Composite keys and concatenated keys
PILOTFLIGHT has no primary key of its own, on purpose. FlightID alone repeats (one flight, multiple pilots) and PilotID alone repeats too (one pilot, multiple flights). Together, though, the PAIR (FlightID, PilotID) is always unique, that pair is PILOTFLIGHT's composite key, and this pattern, a "linking table" sitting between FLIGHTS and PILOTS, is the standard way to represent a many-to-many relationship, which a single foreign key can never do on its own.
Check that claim with three real rows. Suppose PILOTFLIGHT actually held this data:
Neither column can be the primary key by itself, both repeat. The PAIR never repeats. That's exactly what makes it a composite key.
Composite key vs concatenated key, they're not the same thing. A composite key keeps the fields separate (PILOTFLIGHT above still stores FlightID and PilotID as two distinct columns). A concatenated key goes a step further: it combines the VALUES from several attributes into one single new field, so a table with a name and a club title, for example, could concatenate them into one field like "Jane Doe-Arts and Crafts" to act as a single primary key column, not two.
Worked from scratch, on a TEACHER table where no single field is unique alone (two teachers could share a name; the same club could have several teachers across years):
Compare that to the composite key above: PILOTFLIGHT keeps two separate columns, FlightID and PilotID, and checks both together. TEACHER's concatenated key has collapsed the two pieces of information into ONE column, there's no second field left to check, the whole identity lives in that single concatenated string.
Check your understanding
PILOTFLIGHT is uniquely identified by the pair (FlightID, PilotID), stored as two separate columns. Is this a composite key or a concatenated key?
Explainer
Benefits and limitations
Every one of these is a real, nameable consequence of the tables-and-keys structure above, not just an abstract claim. The syllabus names ten benefits and six limitations specifically.
Benefits
- Community support: the most widely used database model since the 1970s, so help is easy to find.
- Concurrency control: manages simultaneous users without their changes conflicting.
- Data consistency: each fact is stored in one place, so every user sees the same value.
- Data integrity: the data stays accurate and untampered across its whole lifecycle.
- Data retrieval: SQL queries (SELECT, JOIN, WHERE) retrieve exactly what's needed.
- Reduced data duplication: shared keys link tables instead of repeating full details.
- Reduced redundancy: normalizing into separate tables avoids storing the same fact twice.
- Reliable transaction processing: a transaction runs fully, or not at all, never half-done.
- Scalability: can grow by adding resources (CPU, RAM, storage) to the existing system.
- Security features: user accounts and permissions control exactly who can access what.
Limitations
- "Big data" scalability issues: joins and complex queries slow down as data grows very large.
- Design complexity: tables and relationships need careful planning before anything is built.
- Hierarchical data handling: tree-shaped data (like an org chart) is awkward to store as flat tables.
- Rigid schema: the structure is defined upfront, and changing it later is slow and complicated.
- Object-relational impedance mismatch: OOP concepts (like inheritance, pointers) don't translate cleanly into tables.
- Unstructured data handling: data with no consistent fields across records can't fit a fixed schema at all.
Common mistake
A3.1.1RecurringWhen a question gives a specific scenario and asks for benefits or limitations, markers reported candidates answering with generic, memorised points that never actually connect back to the scenario given. Full marks require tying the benefit or limitation to the specific situation in the question, not just naming it in the abstract.
What a weak answer looks like: "A benefit of a relational database is security features." True, but generic, it never mentions the airline scenario at all.
What it should say: "The airline's database can restrict access so that only authorized ground staff can update FLIGHTS records, preventing an unauthorized user from changing a flight's departure time by mistake." Names the benefit AND applies it to the actual scenario.
Revision questions
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
[2]In the airline database, state which field is the primary key of FLIGHTS and which field in FLIGHTS is a foreign key, giving a reason for each.
Marking points
评分要点
- FlightNo (or equivalent) is the primary key of FLIGHTS: it is unique for every flight and never null.
- AeroplaneID in FLIGHTS is a foreign key: it holds the primary key value of a row in AEROPLANE, linking each flight to the aircraft flying it.
- Reason for the foreign key: it lets one aircraft be referenced by many flights without repeating the aircraft's details.
- FlightNo(或等价字段)是 FLIGHTS 的主键:它对每个航班唯一且永不为空。
- FLIGHTS 中的 AeroplaneID 是外键:它存放 AEROPLANE 某一行的主键值,把每个航班与执飞的飞机关联起来。
- 外键的理由:它让一架飞机可以被多个航班引用,而不必重复飞机的详细信息。
[2]A staff member tries to delete an AEROPLANE record while FLIGHTS still refers to it. Explain what the database should do, and why.
Marking points
评分要点
- The database should refuse the deletion (or cascade it), because referential integrity must be preserved.
- If the AEROPLANE row were removed, the AeroplaneID in the FLIGHTS rows would point to a record that no longer exists.
- That would leave orphaned foreign keys and queries joining the two tables would lose those flights or error.
- 数据库应拒绝该删除(或对其做级联),因为必须保持参照完整性。
- 如果 AEROPLANE 那一行被移除,FLIGHTS 各行中的 AeroplaneID 就会指向一条不再存在的记录。
- 那会留下孤立的外键,连接两表的查询会丢失那些航班或报错。
[6]A veterinary clinic keeps a database of PETS and APPOINTMENTS, where one pet can have many appointments. Discuss one benefit and one limitation of using a relational database for this scenario.
Marking points
评分要点
- Benefit: reduced duplication and enforced integrity. Each pet's details are stored once in PETS and each appointment links to it by PetID, so a pet cannot be booked in twice or misspelled per appointment.
- Benefit: flexible querying with SQL, for example every appointment for one pet, or all pets due a checkup this month, without changing the structure.
- Limitation: the schema is rigid. Adding a new kind of data (a second owner, a vaccination history) means altering tables and possibly existing queries.
- Limitation: joins across PETS and APPOINTMENTS cost processing time, which grows as the data grows.
- Limitation: it fits structured data well but poorly for unstructured items such as scanned X-ray images or free-form clinical notes.
- Judgement: for this clearly structured, relational scenario the benefits outweigh the limitations.
- 优点:减少重复并强制完整性。每只宠物的详细信息在 PETS 中只存一次,每次预约通过 PetID 关联到它,所以一只宠物不会被重复登记或在各预约中拼写不一致。
- 优点:用 SQL 灵活查询,例如某只宠物的所有预约,或本月所有需要体检的宠物,而无需更改结构。
- 局限:模式是刚性的。增加一种新数据(第二位主人、疫苗接种史)意味着修改表,可能还要改现有查询。
- 局限:跨 PETS 和 APPOINTMENTS 的连接消耗处理时间,并随数据增长而增大。
- 局限:它很适合结构化数据,但不适合非结构化内容,如扫描的 X 光片或自由格式的临床记录。
- 判断:对于这个明显结构化、关系型的场景,优点大于局限。
Flashcards
Active recall for this statement's key terms.
Keep this statement's practice with you
Free account: unlock every revision question and flashcard for A3.1.1, and every other IB CS 2027 statement.