Database Management Systems (DBMS)

A Database Management System (DBMS) is software that is used to create, manage and control access to a database. The crucial point examiners want is the distinction:

• the database is the stored data;
• the DBMS is the program that looks after that data.

The DBMS sits between the users/applications and the database, handling all storage, retrieval, integrity and security so that individual programs don't each have to manage the data themselves.

Why we moved on from the file-based approach. Older systems gave each program/department its own separate files. That caused recurring problems:

File-based problem	What goes wrong	How the DBMS fixes it
Data redundancy	the same item stored in many files	data stored once centrally
Data inconsistency	one copy updated, others not	update once → all consistent
Poor security	security applied file-by-file	central access rights + backups
Hard to share	files locked to one program	many users share one database

Almost every "why use a DBMS?" question is answered by one of these rows: name the problem, then say how the DBMS removes it.

Part of how a DBMS manages data is by maintaining a data dictionary.

A data dictionary is a store of metadata — data about the data — that describes the structure of the database, not the records themselves.

Typical contents of a data dictionary:

• the names of the tables and the names of the fields in each;
• the data type of each field (and its length);
• the validation rules applied to each field;
• the relationships (keys) between tables;
• indexes and access rights.

Notice that every item describes the design of the database. A customer's actual name or address is data, not metadata — it would never appear in the data dictionary.

The DBMS uses the data dictionary internally too: for example, when you run a query it checks the dictionary to confirm a field exists and is the right type.

Before a database is built, the data is modelled and a logical schema is produced.

Data modelling is the process of analysing the data requirements and designing how the data will be organised. It identifies:

• the entities (the things data is held about, e.g. Student, Course);
• the attributes of each entity (e.g. a Student has a Name, DateOfBirth);
• the relationships between entities (e.g. a Student is enrolled on a Course).

The logical schema is the result of that design: the logical structure of the whole database — its tables, fields and relationships — described independently of how the data is physically stored on disk.

The word logical is doing the heavy lifting. The logical schema says what tables exist and how they relate; it deliberately ignores where the bytes sit on the storage device (that is the physical/internal level, a separate concern). Separating the two means the physical storage can be changed without altering the logical design that programs depend on.

Data integrity means the data is accurate, complete and consistent. A DBMS provides several mechanisms to maintain it:

• Validation rules. The DBMS enforces rules on fields so only sensible data can be entered — e.g. a date must be a valid date, a quantity must be within a range, a required field cannot be left blank.
• Enforcing relationships (referential integrity). Because tables are linked, the DBMS can prevent a record from referring to data that does not exist (e.g. an order for a customer who is not in the customer table).
• Removing redundancy. Because each item is stored once, the same fact cannot be duplicated and then become inconsistent — fixing it in one place fixes it everywhere.

These are exactly the failings of the file-based approach (§1) turned into features: where separate files allowed the same address to disagree across copies, the DBMS keeps a single, consistent version.

Data security protects data against loss and against unauthorised access or alteration. The two features named in the syllabus are backup procedures and access rights.

Backup procedures. The DBMS regularly copies the database and stores the copy separately (often off-site or on different media). If the live data is corrupted, deleted or destroyed — by hardware failure, malware, accident or disaster — it can be restored from the backup. Taking backups on a schedule limits how much data could be lost between backups. Backups are about availability/recovery.

Access rights (permissions). These define what each user, or group of users, is allowed to do with the data — for example read-only, read/write, or no access. By granting appropriate rights, the DBMS ensures each role sees and changes only the data it needs, preventing unauthorised viewing or editing.

• Rights can be set for an individual or for a group of users, which is efficient when many users share a role.
• Example (a hospital): doctors get read/write to medical records; receptionists get read-only to appointments and no access to clinical notes.

Keep the two distinct: access rights = authorisation (who may do what); backups = recovery (getting data back after loss).

A DBMS provides software tools to build and use a database. The two you must know are the developer interface and the query processor.

Developer interface. This is the set of tools a designer/programmer uses to build and maintain the database. With it they:

• create and modify tables (fields, data types, relationships);
• design the forms users enter data through;
• create queries and design reports.

It is used mainly at design/build time, by developers.

Query processor. This tool handles queries submitted to the database (for example SQL statements). Its job is to:

1. interpret/check the query,
2. optimise the most efficient way to carry it out,
3. execute it against the data, and
4. return the results to the user or application.

It runs whenever a query is issued — i.e. during everyday use, by anyone (user or program) that queries the data.

	Developer interface	Query processor
Purpose	build/maintain the database	interpret, optimise & run a query
Who uses it	designers/programmers	any user/application running a query
When	design/build time	run time (everyday use)

The trap is to swap them: the developer interface builds the database, the query processor runs queries against it.