How do you prove a database returns the right data and keeps its data consistent?
Test SQL queries against expected results, maintain referential integrity, and evaluate a database solution for fitness for purpose and data integrity.
A focused answer to the SQA Advanced Higher Computing Science content on database testing and evaluation, covering testing SQL queries against expected results, referential integrity, entity integrity and evaluating a database for fitness for purpose.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this dot point is asking
The SQA wants you to test a database the way you test a program: against expected results decided in advance. You also need to know the integrity rules that keep a relational database consistent, especially referential integrity, and to evaluate a finished database against its requirements.
Testing SQL queries
A query is tested like any other code: you work out the correct output by hand from a known set of data, then run the query and compare. A query that "looks right" but is never checked against a predetermined expected result proves nothing. Cover ordinary cases, boundary cases (such as a value on the edge of a range) and exceptional cases (such as a missing or null value), so you confirm the query both includes the right rows and excludes the wrong ones.
Entity integrity
Entity integrity is the rule that the primary key of every table is unique and never null, so each record can always be identified and never duplicated. A database that allowed a null or repeated primary key could not reliably tell its records apart.
Referential integrity
Referential integrity keeps the links between tables valid: every foreign-key value must either match an existing primary-key value in the referenced table or be null. It prevents orphaned records, where a row points at a parent that does not exist. The database management system enforces it: it blocks inserting a child row with a non-existent foreign key, and blocks (or cascades) deleting a parent row that still has children.
Evaluating a database
Evaluation judges the finished database against its requirements.
- Fitness for purpose. Does it store all the data the specification requires and answer every query the user needs, with the right tables and relationships?
- Data integrity. Are entity and referential integrity enforced, and is validation (data types, required fields, permitted ranges) applied so invalid data cannot enter?
- Efficiency and maintainability. Is the design normalised so there is no redundancy, making updates safe and the structure easy to extend?
Try this
Q1. State the rule that referential integrity enforces. [1 mark]
- Cue. Every foreign-key value must match an existing primary-key value, or be null.
Q2. Name the integrity rule that requires every primary key to be unique and never null. [1 mark]
- Cue. Entity integrity.
Exam-style practice questions
Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
AH style: referential integrity3 marksExplain what referential integrity is and give one example of an action it prevents.Show worked answer →
Referential integrity is the rule that every foreign-key value must match an existing primary-key value in the referenced table, or be null (1 mark). It keeps the links between tables valid so there are no orphaned records (1 mark). Example of a prevented action: inserting a Sale with a customerID that does not exist in the Customer table, or deleting a Customer who still has Sales referring to them (1 mark). Either example earns the mark.
AH style: testing a query3 marksDescribe how you would test that an SQL query returning customers from Perth is correct.Show worked answer →
Decide the expected result in advance from the data: list, by hand, exactly which records should be returned for town = 'Perth' (1 mark). Run the query and compare the actual rows returned against that expected list (1 mark). Also test boundary and exceptional situations, for example a town spelled with different case, or a customer with no town, to confirm the query includes and excludes the right rows (1 mark). Markers want a predetermined expected result compared against the actual output.
Related dot points
- Implement and query a relational database in SQL, using data definition (CREATE, ALTER) and data manipulation (SELECT, INSERT, UPDATE, DELETE) with joins, aggregate functions, GROUP BY and HAVING, subqueries, wildcards and computed columns.
A focused answer to the SQA Advanced Higher Computing Science content on SQL, covering data definition and data manipulation, multi-table joins, aggregate functions with GROUP BY and HAVING, subqueries, wildcards and computed columns.
- Normalise data from unnormalised form to first, second and third normal form, using functional dependency and primary, foreign and composite keys to remove redundancy and anomalies.
A focused answer to the SQA Advanced Higher Computing Science content on normalisation, covering unnormalised form, first, second and third normal form, functional dependency, and how normalisation removes redundancy and update anomalies.
- Analyse data requirements and design a relational database using entities, attributes and relationships, entity-relationship diagrams, a data dictionary, and primary, foreign and composite keys.
A focused answer to the SQA Advanced Higher Computing Science content on database analysis and design, covering entities, attributes and relationships, entity-relationship diagrams, the data dictionary, and primary, foreign and composite keys.
- Plan and carry out testing using normal, extreme and exceptional data and dry runs with trace tables, and evaluate software for fitness for purpose, robustness, efficiency and maintainability.
A focused answer to the SQA Advanced Higher Computing Science content on testing and evaluation, covering normal, extreme and exceptional test data, dry runs and trace tables, and evaluating software for fitness for purpose, robustness, efficiency and maintainability.