Databases and SQL: Question 4
Syllabus 8.2
The community sports centre from earlier questions no longer accesses its COACH and
SESSIONS tables as raw files. Instead, all access now goes through a Database Management
System (DBMS), which manages the data on behalf of every member of staff and every program
that needs it.
(a) State what is meant by a data dictionary, and describe one piece of information about
the CoachID field that it would store. [2]
(b) Describe the purpose of the DBMS's query processor when a receptionist runs an SQL
SELECT statement to list all of today's sessions. [2]
(c) State the purpose of the DBMS's developer interface, and give one example of a task a developer might use it for when maintaining this database. [2]
(d) Other than the use of passwords, state one feature the DBMS provides to protect the centre's data, and explain what it protects against. [2]
Show worked solution Hide worked solution
Worked solution
Part (a): The data dictionary
A data dictionary is metadata held by the DBMS. That is, data about the database’s structure, rather than the coach and session data itself.
For the CoachID field, the data dictionary would store information such as: its data type (INTEGER), that it is the primary key of COACH, and/or that it must not be left blank. (Any one correct piece of metadata about CoachID is sufficient.)
[2 marks]: [1] for correctly describing a data dictionary as metadata about the database’s structure (not the data itself), [1] for a correct, specific piece of information it would hold about CoachID.
Part (b): The query processor
When the receptionist runs a SELECT statement, the query processor is the part of the DBMS that handles it: it interprets the SQL statement and checks it is valid, works out an efficient way to carry it out, retrieves the matching rows from wherever the data is actually stored, and returns them as a result set. Without the receptionist needing to know how or where the data is physically stored.
[2 marks]: [1] for describing the query processor as interpreting/validating the SQL statement, [1] for describing it as retrieving the matching data and returning a result.
Part (c): The developer interface
The developer interface is a software tool provided by the DBMS that lets a developer work directly with the database’s structure (for example, defining tables, keys and relationships) rather than simply entering or viewing everyday data as a receptionist would.
An example use: a developer might use the developer interface to create a brand-new table in the database, or to alter the structure of an existing table (such as SESSIONS) by adding a new field to it.
[2 marks]: [1] for correctly describing the developer interface’s purpose (working with the database’s structure, not its everyday data), [1] for a valid, specific example use.
Part (d): Data security beyond passwords
One feature is backup procedures: the DBMS regularly saves a copy of the database, so that if data is lost (for example because of a hardware failure, a corrupted file, or a member of staff accidentally deleting records) it can be restored from the most recent backup rather than being lost permanently.
(An equally valid answer is access rights: the DBMS can restrict which individuals or groups of users are allowed to read, add, change or delete particular data, protecting against unauthorised or accidental changes by staff who should not have that level of access.)
[2 marks]: [1] for naming a genuine DBMS data-security feature other than passwords, [1] for correctly explaining what specific risk it protects against.
Final answers
- (a) Data dictionary: metadata describing the database’s structure, e.g. that
CoachIDisINTEGERand the primary key ofCOACH. - (b) Query processor: interprets and validates the SQL, retrieves the matching data, returns the result set.
- (c) Developer interface: used to define/maintain the database’s structure, e.g. creating or altering a table.
- (d) Backup procedures (or access rights): protect against data loss (or unauthorised/accidental changes).