Nobody reads or writes the tables of a large database directly. Every request goes through a database management system, which knows the structure, checks the rules, decides who may see what, and keeps backups so that nothing is lost.
A database management system (DBMS) is the software that sits between users and the data. It provides data management, including a data dictionary (data about the data: tables, fields, data types, keys, validation rules and relationships), data modelling tools, a logical schema (the overall structure of the database, independent of how it is stored), data integrity (validation and referential integrity) and data security (backup procedures and access rights for individuals and groups). Developers build the database through the developer interface, and every query is checked and run by the query processor.
Fig. 1: every request to the database passes through the DBMS
Section 01
What a DBMS provides.
Each feature of a DBMS answers one of the problems of the file-based approach. Learn them as pairs: the feature, and the problem it solves.
DBMS feature
What it is
File-based problem addressed
Data dictionary
A store of metadata: table and field names, data types, field sizes, primary and foreign keys, validation rules, relationships, users and access rights
Data dependency: programs ask the DBMS, not the file layout
Data modelling
Tools to design and view the structure, such as E-R diagrams and table relationships
No overall view of the data held in separate files
Logical schema
The overall logical structure: tables, attributes, keys and relationships, independent of the physical storage
Programs tied to how and where files are stored
Data integrity
Validation rules, unique primary keys and referential integrity enforced on every change
Inconsistent or invalid data in different files
Data security
Usernames and passwords, access rights for individuals and groups, and backup procedures
All-or-nothing access to whole files; data lost if a file is damaged
Worked example A data dictionary entry
Give the data dictionary entries the DBMS might hold for the field MemberID in the library database.
Table: MEMBER. Field name: MemberID.
Data type: VARCHAR(4); format: the letter M followed by two digits.
Key: primary key of MEMBER; also used as a foreign key in LOAN, so referential integrity applies.
Validation: presence check, format check; must be unique.
Answer: Name, table, data type and size, key status, relationships and validation rules
Examiner trap
The data dictionary holds data about the data (metadata), not the data itself. "It stores all the records" is wrong.
Section 02
Integrity and security.
Integrity keeps the data correct; security keeps it safe from people who should not see or change it, and from loss. Mark schemes want specific procedures, not "the DBMS keeps data secure".
Data integrity: validation rules (range, type, format, presence) are checked on entry; primary key values must be unique; referential integrity stops orphan foreign keys.
Access rights: each user or group of users is given only the rights they need: no access, read-only, or read and write, set per table (or even per field). Users log in with a username and password.
Backup procedures: the DBMS makes regular, scheduled backups (for example a full backup weekly and incremental backups daily), stored off-site or in the cloud, and the restore process is tested.
Worked example Setting access rights
In the school library database, suggest suitable access rights for (a) students, (b) the librarian, (c) the database administrator.
(a) Students: read-only access to BOOK, to search the catalogue; no access to other members' records.
(b) Librarian: read and write access to MEMBER, BOOK and LOAN, to record loans and returns; no right to change the table structure.
(c) Database administrator: full rights, including changing the structure and setting other users' rights.
Answer: Each group gets only the access it needs
Your turn
Explain why a backup alone is not enough: what else must a backup procedure include?
Backups must be made regularly (to a schedule), stored away from the original (off-site or cloud) so one disaster cannot destroy both, and the restore must be tested so the data can actually be recovered
Stage 1 · Learn
Check what the sim just showed you
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Quick check+10 XP
What is held in a data dictionary?
Quick check+10 XP
Which feature gives a student read-only access to the BOOK table?
Quick check+10 XP
What does the query processor do?
Quick check+10 XP
What is the logical schema?
Section 03
The developer interface and the query processor.
Two software tools inside the DBMS are named in the syllabus. You need to know what each one is for and how it is used in practice.
Developer interface: lets a developer create and change the database without low-level programming: design tables and set data types and keys, define relationships, build queries (often by example, in a grid), and design forms for data entry and reports for output.
Query processor: takes a query, written in SQL or built in the developer interface, and checks its syntax and that the user has the access rights it needs. It uses the data dictionary to find the tables and fields, works out an efficient way to run the query (for example using an index), runs it and returns the results.
Worked example Following a query
A librarian runs a query to list all loans not yet returned. Describe what the query processor does.
It receives the SQL query and checks that it is valid.
It checks that the librarian has read access to the LOAN table.
It finds the table and fields in the data dictionary and plans an efficient search.
It runs the query and returns the matching records to the librarian.
Answer: Check, plan, execute, return
Examiner trap
Do not confuse the two tools. The developer interface is used to build the database and its forms, queries and reports; the query processor runs queries.
Stage 2 · Exam
Exam-style questions
Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.
Finish the four checks above to unlock the exam questions
Exam style+20 XP
How does a DBMS reduce data dependency?
Exam style+20 XP
Which is part of a good backup procedure?
Exam style+20 XP
A developer builds a data entry form in a DBMS without writing any code. Which tool is used?
Exam style+20 XP
Which is an example of the DBMS maintaining data integrity?
Skill unlocked
Database management systems, mastered.
This skill is now recorded on your profile. Keep the chain going.