A-Level Computer Science / Unit 8: Relational Databases and SQL

8.2.1 How a DBMS Organises and Protects Data

🔒 Lesson slides are available to signed-in users. Sign in

8.2.1 How a DBMS Organises and Protects Data

A database management system (DBMS) is the software layer that controls how a database is organised, accessed and protected. It applies one shared structure and one set of rules instead of leaving each application to manage separate files.

By the end of this section, you should be able to:

  • Distinguish between a database and the DBMS that manages it.
  • Explain how central data management addresses weaknesses of separate files.
  • Describe the purpose of data modelling and a logical schema.
  • Explain how user views can be separated from logical and physical storage details.
  • Describe how a DBMS supports data integrity.
  • Explain how individual and group access rights protect data.
  • Describe the purpose of database backup and recovery procedures.
Scope note: The data dictionary, developer interface and query processor are studied in 8.2.2 Data Dictionaries and DBMS Tools.

What Does a DBMS Do?

A database stores organised data. A DBMS is the software used to define that organisation, enforce rules, control access and coordinate operations on the stored data.

Database

The organised data

Tables, tuples, attributes, keys and relationships.

DBMS

The management software

Controls structure, access, updates, integrity, security and recovery.

DBMS: software that manages a database and provides controlled ways to define, store, retrieve, modify and protect its data.

This lesson uses an original city bicycle-sharing service. Its central database stores bicycles, docking stations, journeys, customers and maintenance work. Rider support, technicians and analysts all use the same data, but they do not need the same views or permissions.

Common mistake: The DBMS is not another name for the database. The database contains the data; the DBMS is the software controlling it.

Central Data Management

In a file-based system, different programs may keep their own copies of customer, bicycle or journey data. A DBMS allows those applications to work with one centrally managed database.

File-based difficulty DBMS response Benefit
Separate applications store duplicate facts. Applications share centrally managed tables. Less unnecessary redundancy and fewer conflicting copies.
Each program contains its own rules. Constraints are defined and enforced centrally. The same integrity rules apply to every application.
A file format change can break dependent programs. The DBMS separates applications from physical storage details. Storage can change with less effect on user programs.
Sharing data safely is difficult. The DBMS controls concurrent access and permissions. Several users can work with the same database in a controlled way.
Recovery procedures differ between applications. Backup and recovery are managed for the database as a whole. Data can be restored using a consistent procedure.
Exam tip: Do not merely list a DBMS feature. Link it to the file-based problem it addresses and explain the resulting benefit.

Data Modelling and the Logical Schema

Before implementation, a designer models the data that the organisation needs. The model identifies entities, attributes, keys, relationships and rules. The DBMS then maintains a logical schema that describes the implemented logical organisation.

Schema: a description of the structure and rules of a database.
Logical schema: the overall logical design, including tables, attributes, data types, keys, relationships and constraints, without describing the exact physical storage arrangement.
Station StationID · StationName · Capacity
Bicycle BikeID · Status · StationIDFK
Journey JourneyID · CustomerIDFK · BikeIDFK · StartTime · EndTime
MaintenanceJob JobID · BikeIDFK · TechnicianIDFK · JobStatus

The logical schema gives every application a consistent understanding of the data. For example, every program uses the same definition of BikeID and the same relationship between Bicycle and Station.

Common mistake: A logical schema is not a screenshot of the data. It describes the structure and constraints, not the current tuple values.

Separating User Views, Logical Design and Storage

A DBMS can separate what users see from the complete logical design and from the way bytes are stored. This separation is often represented using three levels.

Level What it describes Bicycle-sharing example
External A view provided for one user, group or application. Technicians see faults and repair jobs but not payment information.
Conceptual / logical The complete logical schema for the database. All tables, relationships and integrity constraints.
Internal The physical organisation of stored data. How the DBMS arranges records and storage pages.

This separation supports data independence. An application can request logical data without knowing its physical location. The DBMS translates the request into the necessary storage operations.

Exam tip: A user view is a selected presentation of the database. It is not a separate copy of all the data.

Protecting Data Integrity

Data integrity means that stored data remains accurate, valid and consistent. A DBMS protects integrity by applying the same rules regardless of which application submits an update.

Integrity control Rule enforced Example
Data type and domain constraints Values must be suitable for an attribute. Station capacity must be a positive integer.
Entity integrity Every tuple has a unique, non-null primary key. Two bicycles cannot share the same BikeID.
Referential integrity A foreign-key value must reference an existing primary-key value. A maintenance job cannot refer to a bicycle that does not exist.
Business-rule constraint Organisation-specific conditions are checked. A completed journey must have an EndTime.
Transaction control A multi-step operation is completed consistently or reversed. A bicycle transfer must not remove a bike from one station without adding it to another.
Transaction: a related group of database operations treated as one logical unit of work.

Suppose bicycle BK-604 is moved from station ST-18 to ST-27. If the system fails after removing the old station reference but before storing the new one, the DBMS should reverse the incomplete operation or recover it safely. The database must not be left in an impossible intermediate state.

Common mistake: Validation alone cannot guarantee that data is correct. It checks whether values satisfy rules; it cannot prove that a plausible value reflects reality.

Protecting Data with Access Rights

After a user has been authenticated, the DBMS can apply permissions to an individual account or to a role representing a group of users. This allows the principle of least privilege: users receive only the access needed for their work.

Role Data visible Allowed actions Restricted actions
Rider-support agent Customer account and journey summaries Read; correct contact details Cannot view full payment records or delete journeys
Maintenance technician Bicycle status and assigned repair jobs Read; update job status Cannot view customer personal data
Planning analyst Anonymised journey and station-use data Read and analyse Cannot change operational records
Database administrator Database structure and authorised administration data Manage accounts, roles, backup and schema settings Access should still be controlled and audited
Access rights: permissions defining which data a user or group may access and which operations, such as read, insert, update or delete, they may perform.

Views and access rights work together. A view can expose only selected attributes and tuples, while permissions determine what actions the user may perform through that view.

Exam tip: “Password” alone is not a complete DBMS-security answer. Explain how access rights restrict the data and operations available after authentication.

Backup and Recovery Procedures

Integrity controls cannot prevent every failure. Hardware faults, accidental deletion, software errors and other incidents can still damage or remove data. A backup is a separate recoverable copy that can be used to restore the database.

Procedure Why it matters
Take backups on a planned schedule. Limits how much recent work could be lost.
Keep copies separate from the live database. A single incident is less likely to destroy both live data and backups.
Protect backup files with suitable security. Backups may contain the same sensitive data as the live database.
Check that backups complete successfully. A failed or incomplete backup may be unusable.
Test the restoration process. A backup is valuable only if the organisation can recover from it.
Document who can start a restore. Prevents unauthorised or accidental replacement of live data.
Common mistake: A backup is not simply another live copy that is always modified at the same time. If corruption is copied immediately, both versions may be damaged.

Interactive: DBMS Control Centre

Explore how the DBMS separates the system into layers, grants permissions and protects a multi-step update. The widget retains the architecture-explorer purpose of the original page and extends it with security and recovery simulations.

Follow a request through the DBMS

Select a layer to see what it controls.

User and application views

Rider support · Maintenance · Planning

Logical schema

Tables, data types, keys, relationships and constraints.

Integrity checks

Validate the operation before stored data is changed.

Physical storage

The DBMS locates and updates the required stored records.

Common Mistakes and Misconceptions

  • Using “database” and “DBMS” as though they mean the same thing.
  • Describing a logical schema as the physical position of data on a disk.
  • Assuming a user view contains a separate copy of the full database.
  • Listing access rights without explaining which data or operations are restricted.
  • Claiming validation proves that entered data is factually correct.
  • Forgetting that primary-key and foreign-key constraints protect integrity.
  • Discussing backups without explaining how restoration would be controlled and tested.
  • Including detailed data-dictionary or query-processor material that belongs in 8.2.2.

Practice

Original practice tasks

  1. Explain the difference between a database and a DBMS.
  2. A library has separate files for loans, members and overdue notices. Explain three ways a DBMS could improve its data management.
  3. Define logical schema and give two elements it can contain.
  4. Explain how separating a user view from physical storage reduces program–data dependency.
  5. A repair record contains a BikeID that does not exist in Bicycle. Name the integrity rule that should prevent this and explain how it works.
  6. Compare suitable access rights for a customer-service user and a maintenance technician.
  7. Explain why a multi-step database transaction should be completed entirely or reversed.
  8. Design four points for a reliable backup procedure for a medical-appointment database.

Review

DBMS responsibility What it achieves
Central data management Applications share one controlled source of data and rules.
Data modelling and logical schema Defines the database structure independently of physical storage.
User views Present suitable subsets of data to users and programs.
Integrity controls Enforce valid keys, references, domains and consistent transactions.
Access rights Restrict which data and operations are available to individuals or groups.
Backup and recovery Allow the database to be restored after loss, corruption or failure.
Final exam tip: Structure an answer as feature → operation → benefit. For example: “Role-based access rights restrict technicians to maintenance data, preventing them from viewing customer payment details.”