Database Management Systems (DBMS)
1. The Problem: A File-Based Approach
Before database management systems, each application kept its own files. The consequences are the standard justification for using a DBMS, and are examined directly.
Data redundancy — the same data is stored in several files. A student's address might be
held separately by the library system, the finance system and the registration system.
Data inconsistency — a consequence of redundancy. If the address is updated in one file
and not the others, the system now holds contradictory data and there is no way to tell which is correct.
Data dependence — the file structure is written into each program. Adding one field means
editing and recompiling every program that reads the file.
Lack of security and integrity control — access is all-or-nothing at file level, so
individual fields cannot be protected, and each program must enforce its own validation.
2. What a DBMS Is
A database management system is software that sits between the users and the stored data. Applications never touch the data files directly; every request passes through the DBMS.
The central benefit is program–data independence. The DBMS holds the definition of the
data separately from the programs that use it, so the structure can change without rewriting the applications.
3. Features of a DBMS
Three features are named by the syllabus. Learn what each one is and what it does.
(a) The data dictionary
The data dictionary is data about the data — metadata. It is itself stored in the database and describes its structure.
| The data dictionary holds |
|---|
| Table names and the fields in each table |
| The data type and size of every field |
| Primary and foreign keys, and the relationships between tables |
| Validation rules applied to each field |
| Access rights — which users may see or change what |
| Indexes that exist to speed up queries |
Because the structure is defined once in the data dictionary rather than inside each program, a change
made there applies everywhere at once. This is the mechanism that delivers program–data independence.
(b) The developer interface
The developer interface is the set of tools a database developer uses to build and maintain the database, rather than to query it day to day.
- Create and modify tables, fields and relationships
- Write and test SQL directly
- Define validation rules and set access rights
- Design forms and reports for end users
- Monitor performance and create indexes
(c) The query processor
The query processor receives a query, checks it and executes it. Its work has three stages:
| Stage | What happens |
|---|---|
| Parsing | The query is checked for correct syntax, and field and table names are verified against the data dictionary |
| Optimisation | The most efficient way to obtain the result is chosen — for example which index to use, and the best order in which to apply conditions |
| Execution | The plan is carried out, the data is retrieved and the result is returned to the user |
Optimisation is the key idea. SQL states what is wanted, not how to get it.
The query processor decides the method, which is why the same query can run efficiently even as the data grows or
new indexes are added — without the query being rewritten.
4. How a DBMS Solves the File-Based Problems
| Problem | How the DBMS addresses it |
|---|---|
| Redundancy | Data is stored once and shared; normalisation removes repetition |
| Inconsistency | With one copy of each item, an update is immediately correct for every user |
| Data dependence | Structure lives in the data dictionary, not in program code |
| Security | Access rights are set per user, per table and per field |
| Integrity | Validation rules are defined centrally and enforced on every update |
| Concurrent access | Record locking prevents two users altering the same record simultaneously |
5. Other Facilities
Backup and recovery — automatic backups plus a transaction log, so the database can be
restored to a consistent state after a failure.
Access rights and authentication — user accounts with defined privileges, so a clerk may
read records that only a manager may amend.
Record locking — while one user updates a record, others are prevented from writing to it,
avoiding the lost-update problem.
Views — each user or application is presented only with the fields it needs, which serves
both security and simplicity.
6. Exam Focus
Do not confuse the data dictionary with the data. The dictionary holds the description
of the database — field names, types, validation, access rights. It does not hold the records themselves.
Redundancy and inconsistency are different. Redundancy is storing the same data more than once;
inconsistency is the resulting disagreement between copies. Redundancy is the cause, inconsistency the
effect. Both are needed for full marks when explaining the limitations of a file-based approach.
Name the query processor's stages. Answers asking what it does should mention parsing,
optimisation and execution — and should identify optimisation as choosing the most efficient method.
Quick self-check
- State four limitations of a file-based approach.
- Define the data dictionary and list four things it stores.
- Explain what is meant by program–data independence, and how a DBMS provides it.
- Name and describe the three stages of query processing.
- Explain how a DBMS prevents two users corrupting the same record.
- Explain the difference between data redundancy and data inconsistency.