Features, benefits and limitations of a relational database
Explain the features, benefits and limitations of a relational database
In this lesson
- Why data stored in one flat file leads to update, deletion and insertion anomalies
- The features of a relational database: tables, primary keys, foreign keys, composite keys and relationships
- The benefits of relational databases, from data integrity to concurrency control
- The limitations of relational databases, from rigid schemas to big data
LearnWhy not one big spreadsheet?
A database is an organised collection of data. It is managed by a database management system (DBMS), the software that stores the data, answers queries and controls who can change what.
LabOne spreadsheet or four tables?
A vet clinic's receptionist has three jobs to do. Try each one on the old spreadsheet, then on the new relational database, and compare what happens to the stored data.
| Date | Pet | Species | Owner | Phone | Vet | Room |
|---|---|---|---|---|---|---|
| 2026-09-02 | Max | Dog | Ana Ortiz | 0711 555 0142 | Dr Haas | 2 |
| 2026-09-02 | Luna | Cat | Ana Ortiz | 0711 555 0142 | Dr Haas | 2 |
| 2026-09-20 | Max | Dog | Ana Ortiz | 0711 555 0142 | Dr Okafor | 5 |
| 2026-09-21 | Bella | Rabbit | James Lee | 0711 555 0187 | Dr Okafor | 5 |
| 2026-10-01 | Kiwi | Parrot | Mei Chen | 0711 555 0123 | Dr Haas | 2 |
PK = primary key, FK = foreign key. Do all three jobs in both versions, and try at least one of the extra jobs in the relational database, to complete this activity. Names and numbers are invented.
CheckQuick check
A school keeps one spreadsheet of enrolments. Each row holds a student, a course, the course's teacher and its room. A new course has been planned, but no student has enrolled yet. Which problem does the school have?
LearnFeatures of a relational database
CheckSpot the keys
A library database has the tables Book(BookID, Title, Author), Member(MemberID, Name, Email) and Loan(LoanID, BookID, MemberID, DueDate). What is each field in the Loan table?
- LoanID
- BookID
- MemberID
- DueDate
LearnBenefits of relational databases
LabMany users at once
Step through two agents selling the last seat on a flight, then crash the server in the middle of a ticket purchase. Try each with and without the DBMS's protection.
1. Two agents, one seat left on flight SK 412
2. A crash in the middle of a purchase
Run part 1 to the end with and without concurrency control, and part 2 with a crash, with and without a transaction, to complete this activity.
LearnLimitations of relational databases
CheckBenefit or limitation?
Classify each statement about using a relational database.
- Two cashiers can sell tickets for the same concert at the same time without selling a seat twice
- Recording a new kind of information first needs the table structure to be changed
- A family tree of unknown depth needs a query to be repeated for every generation
- A game's player objects, each holding a list of items, must be converted into rows in several tables
- A new developer can find answers to most SQL problems online
- Planning the tables and links for a hospital system takes an expert several weeks
ExamWorked exam answer
A hospital stores patient records in a relational database. Explain one way the database's security features protect the records.
[2]ReviewMistakes examiners see
A primary key is not just “a unique field”: its value must be unique for every row and never empty, and it is chosen to identify the record.
A foreign key is not a different kind of value: it is the primary key of another table, stored here to make a link.
A deletion anomaly does not mean the row cannot be deleted. It means deleting it loses other facts as well.
When you explain a benefit or limitation, link it to the scenario: say what would go wrong, or go right, for that organisation.
Exam practiceExam-style questions
5 questions · 13 marks. Answer each one first, then mark it yourself against the markscheme. Your answers are saved in this browser only.
A cinema chain has kept all its bookings in one large spreadsheet. Each row holds the customer's name, email address and phone number, the film, the screening time and the seat. The chain is moving to a relational database with the tables Customer, Screening and Booking. Booking has the composite primary key (ScreeningID, SeatNumber) and the foreign key CustomerID.
Explain why CustomerName would not be a suitable primary key for the Customer table.
Explain why (ScreeningID, SeatNumber) is a suitable primary key for the Booking table.
Describe how the foreign key CustomerID links the Booking table to the Customer table.
The marketing team wants the current email address of every customer who booked a screening of one particular film last month. Explain why this is easier to obtain from the relational database than from the old spreadsheet.
The chain also wants to analyse all its past ticket sales together with a constant stream of social-media posts about its films and its film trailer videos. Explain two limitations of a relational database for this.
0 of 5 questions marked. Mark every question to see your total.
Exam-style questions written for Praxis IB in the format of the 2027 specimen papers. They are not IB past-paper questions.