Multiple choiceQuestion 1 · Recall
Loading…
By the end you can describe the features of a relational database that fix the limitations of the file-based approach, and explain the role of the database management system (DBMS).
A relational database stores data in a set of tables. Each table holds one kind of thing: students, courses, loans. Tables are linked by keys, which are values that identify a row or refer to a row in another table.
Instead of repeating a student's address in every file that needs it, the address is stored once, in the student table. Other tables refer to the student by an ID.
The data and the programs are separated by a database management system (DBMS). Programs ask the DBMS for data; they never read the storage directly. That single change is what makes the rest of the fixes possible.
Relational database
A collection of data organised as a set of tables (relations) that are linked by shared key fields, where each table stores one type of entity and each row is uniquely identified by a primary key.
Exam questions often ask how a relational database addresses the limitations you met in the previous lesson. Learn the pairs.
| File-based limitation | Relational database feature that addresses it |
|---|---|
| Data redundancy | Each fact is stored once, in one table. Other tables refer to it by key. |
| Data inconsistency | There is only one copy to update, so all programs see the same value. |
| Program-data dependence | Data independence: programs use field names, and the DBMS handles physical storage. |
| Concurrent access conflicts | The DBMS controls simultaneous access with locking and transactions. |
| Weak security | The DBMS grants access rights to users, tables, and even particular fields or views. |
| Integrity problems | Integrity constraints (such as uniqueness and referential integrity) are enforced centrally. |
| No ad hoc queries | Users can write queries in SQL without writing a new program. |
| Slow searches on large data | Indexes let the DBMS find rows without scanning the whole table. |
| Recovery after a failure | The DBMS keeps logs and performs backup and recovery. |
A database does not remove every problem by magic. It gives one central place where each rule can be enforced, instead of hoping every program behaves.
Worked example: The school office again
The school replaces its three files with a database containing Student(StudentID, Forename, Surname, Address) and Loan(StudentID, BookID, DueDate). A student moves house. Explain why the database handles this better than the three separate files did.
The DBMS is another name for the data.
The DBMS is the software that manages the data: it stores, retrieves, protects and controls access to it. The database is the organised collection of data itself.
Tables are related just because they are in the same database.
Tables are related when a key in one table refers to a key in another. Two tables sitting side by side with no key link are independent.
A relational database never contains duplicate values.
It avoids unnecessary duplication of facts. The same value can legitimately appear in several rows, for example a StudentID in many loan rows, because those rows each describe a different loan by that student.