The shape of the lesson
By the end of the lesson, learners can
- What a DBMS provides: Each feature of a DBMS answers one of the problems of the file-based approach.
- Integrity and security: Integrity keeps the data correct; security keeps it safe from people who should not see or change it, and from loss.
- The developer interface and the query processor: Two software tools inside the DBMS are named in the syllabus.
- Data dictionary: metadata, not the data.
- Integrity: validation, unique keys, referential integrity. Security: passwords, access rights, backups.
- Access rights: per user or group: none, read-only, read and write.
- Developer interface builds; query processor runs queries.
One picture every idea returns to
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.
Forty-five minutes, phase by phase
| Time | Phase | What happens in the room | Grouping |
|---|---|---|---|
| 0 to 5 min | Hook: predict | Open with the question in the title, Software that guards the data.. Nobody reads or writes the tables of a large database directly. Put the figure on the board: it is the one picture every idea in the lesson returns to. | Whole class, then pairs |
| 5 to 13 min | Teach and check: What a DBMS provides | Each feature of a DBMS answers one of the problems of the file-based approach. Model the worked example A data dictionary entry on the board, one line at a time. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Integrity and security | Integrity keeps the data correct; security keeps it safe from people who should not see or change it, and from loss. Model the worked example Setting access rights on the board, one line at a time. Your turn: Explain why a backup alone is not enough: what else must a backup procedure include? | Pairs |
| 21 to 30 min | Teach and check: The developer interface and the query processor | Two software tools inside the DBMS are named in the syllabus. Model the worked example Following a query on the board, one line at a time. | Corners |
| 30 to 36 min | Check | The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong. | Individual, then whole class |
| 36 to 43 min | Plenary | The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording. | Individual, random call |
| 43 to 45 min | Exit and homework | Set the worksheet below (6 questions, 18 marks). Learners self-assess against the objectives before they leave. | Individual |
Protect the parts that carry the learning
The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.
Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.
Cut first if the build overruns: the last check in The developer interface and the query processor, never the check or the plenary.
In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.
Corners
Put one answer or viewpoint in each corner of the room. Students move to the corner they agree with, justify the choice to a partner there, then hear one voice from each corner before the reveal.
The full structure, with its variations and the other twelve, is on the cooperative-learning page.
What to head off, and how
| Trap learners fall into | Teaching move that pre-empts it |
|---|---|
| The data dictionary holds data about the data (metadata), not the data itself. "It stores all the records" is wrong. | Return to What a DBMS provides: ask a pair to state the correct version and show it on the lab before moving on. |
| 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. |
Database management systems: worksheet
6 questions, 18 marks. Written in the style of the papers; no past paper question is reproduced.
State three items that would be stored in the data dictionary of a DBMS.
A hospital uses a DBMS to store patient records. Explain how the DBMS can be used to keep the data secure.
Describe the purpose of the query processor in a DBMS.
Describe how a developer uses the developer interface of a DBMS.
A small business keeps customer data in spreadsheet files on several computers. Explain two ways in which moving to a DBMS would address problems with this approach.
Distinguish between data integrity and data security, giving an example of each for a school database.
Answer key and marking notes
- Table names and field (attribute) names.
- Data types and field sizes.
- Primary and foreign keys, relationships, validation rules or access rights (any one).
- Each user logs in with a username and password.
- Access rights are set for each group: e.g. doctors read and write medical records, receptionists read-only access to contact details only.
- Regular scheduled backups are made of the database.
- Backups are stored off-site or in the cloud and the restore is tested.
- It takes a query (e.g. in SQL), checks it is valid and that the user has permission.
- It works out how to run it efficiently, executes it and returns the results.
- To create tables, choose data types and set primary keys.
- To define relationships between tables.
- To build queries, forms for data entry and reports for output without low-level programming.
- Data is held once in a shared database, so there is less redundancy and no inconsistent copies.
- Integrity: validation rules and referential integrity are enforced by the DBMS on every change.
- Security: access rights can be set for each employee, rather than access to a whole file.
- Any two, each identified (1) and explained (1).
- Integrity is about the data being correct and consistent, e.g. a range check stops a mark of 150 out of 100 being entered.
- Security is about protecting data from unauthorised access or loss, e.g. students have read-only access to the timetable.
6 multiple-choice questions on this lesson
From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.
Which of these would be held in the data dictionary of the library database?
- AThe data type and size of the Surname field in MEMBER
- BThe surname of every member of the library
- CA backup copy of the MEMBER table taken last night
- DThe results of the most recent query on LOAN
What is meant by the logical schema of a database?
- AThe overall structure of the database: its tables, attributes, keys and relationships, independent of how it is stored
- BThe physical location of each record on the storage device and the file it is held in
- CThe set of usernames and access rights given to each group of users
- DThe sequence of steps the query processor follows when it runs a query
At a dental surgery, receptionists must be able to look up patients' appointment times but must not be able to change any patient's treatment notes. Which DBMS feature is used to achieve this?
- AAccess rights set for the receptionist group
- BReferential integrity between the tables
- CA validation rule on the treatment notes field
- DRegular backup of the treatment notes table
A teacher tries to enter a test mark of 150 for a test marked out of 100, and the DBMS refuses it. Which DBMS feature has done this?
- AData integrity, using a range check stored with the field
- BData security, using the teacher's access rights
- CReferential integrity, using a foreign key
- DThe developer interface, using a data entry form design
Which backup procedure best protects a company's database against both a hardware failure and a fire at its office?
- AA full backup each week and an incremental backup each night, stored off-site, with the restore tested regularly
- BA full backup each night, copied to a second hard disk in the same server as the database
- CA full backup made by any user who thinks the data has changed a lot, kept in the office safe
- DA copy of the data dictionary made each night and stored in the cloud
A student with read-only access to BOOK submits an SQL statement that tries to delete records from LOAN. Which statement describes what happens?
- AThe query processor checks the student's access rights, held by the DBMS, and refuses to run the statement
- BThe developer interface rewrites the statement as a SELECT so it can run safely
- CThe statement runs, but referential integrity restores the deleted loans afterwards
- DThe data dictionary deletes the records, because it holds the definition of LOAN
Quiz key
The data dictionary holds metadata: table and field names, data types and sizes, keys, validation rules, relationships and access rights. The surnames themselves are data stored in the MEMBER table, not metadata. Backups are made by the backup procedure, and query results are returned to the user rather than stored in the dictionary.
The logical schema describes the structure as developers and users see it, with no reference to physical storage. Where records are stored on disk is a physical description. User names and rights are security settings, and the steps followed to run a query are the work of the query processor.
Access rights let the administrator give the receptionist group read access to appointments and no write access to treatment notes. Referential integrity checks that foreign keys match primary keys. A validation rule checks that a value is sensible, not who entered it, and backups protect against loss, not unauthorised changes.
Rejecting a value that is outside the allowed range keeps the data correct, so it is data integrity through a validation (range) rule held in the data dictionary. The teacher is allowed to enter marks, so access rights are not the issue. No foreign key is involved, and the developer interface is used to build the database, not to check each entry as it is made.
Scheduled full and incremental backups kept away from the building survive both a disk failure and a fire, and testing the restore proves the data can be recovered. A second disk in the same server is lost in the same fire. Backups made only when someone remembers are irregular, and the data dictionary alone holds the structure, not the records.
The query processor checks the syntax and that the user has the rights the statement needs; with no write access to LOAN, the deletion is refused. The developer interface is for building tables, forms and reports, not for changing a user's queries. Referential integrity does not undo deletions, and the data dictionary only stores metadata; it does not carry out queries.
Slides for this lesson
A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.