How do we analyse the requirements for a database and design its tables, fields and validation?
Database analysis and design: identifying end-user and functional requirements, designing tables with fields and appropriate field types, and choosing primary and foreign keys and validation.
An SQA National 5 Computing Science answer on database analysis and design, covering how to identify end-user and functional requirements, design tables with fields and suitable field types, choose a primary key and a foreign key to link tables, and add validation such as presence, restricted choice, field length and range checks.
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 key area is asking
The SQA wants you to analyse what a database must do, then design its structure: tables made of fields with suitable field types, a primary key for each table, foreign keys to link tables, and validation to keep the data accurate.
Analysis: what the database must do
As with software analysis, getting the requirements right first means the design solves the real problem. For a lending library, the end users (staff) need to record members and loans and find out who has borrowed what; the functional requirements then include storing member and loan data and linking each loan to a member.
Design: tables, fields and field types
Designing the tables means listing the fields each table needs and choosing the field type for every one. A member's name is text, their date of birth is a date, the number of items borrowed is a number, and whether their membership is active is a Boolean.
Keys: primary and foreign
The distinction is reliably examined. The primary key guarantees each record can be told apart; the foreign key is how a record in one table points to a record in another. In a two-table design, the primary key lives in the "one" table and the foreign key lives in the "many" table that refers to it.
Validation: keeping data accurate
Validation does not guarantee the data is true, but it stops obviously wrong data getting in - an empty required field, a house that is not one of the four options, a phone number that is too long, or an age outside a sensible range. Each check should match the field it protects.
Why analysis and design matter
A well-designed database stores each fact once, in the right table, with the right type and the right links, and rejects bad data at entry. Poor design leads to duplicated data, broken links and errors that are hard to fix later. As with software, the time spent on analysis and design here pays off when the database is built and queried with SQL.
How this key area is examined
Questions ask you to identify requirements, design tables and fields with suitable field types, explain primary and foreign keys, or choose and justify a validation check. Keep the keys straight (primary identifies, foreign links), match each field type to its data, and pair each validation check with the rule it enforces. Worked design questions reward a clear table-by-table answer that names the keys and the link.
For the official course specification
The SQA publishes the full National 5 Computing Science course specification, specimen question papers and coursework tasks at sqa.org.uk. Always revise from the current specification and SQA past papers, because question style and terminology are board-specific.
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.
SQA N5 style4 marksA database has two linked tables, Member and Loan. Explain the difference between a primary key and a foreign key, using these tables as an example.Show worked answer →
Two marks for the primary key idea and two for the foreign key idea.
A primary key is a field (or attribute) that uniquely identifies each record in a table; no two records share the same value and it cannot be empty. For example, memberID is the primary key of the Member table.
A foreign key is a field in one table that refers to the primary key of another table, creating a link between them. For example, the Loan table stores memberID as a foreign key, so each loan is linked to one member in the Member table.
Markers reward "uniquely identifies a record" for the primary key and "refers to the primary key of another table to link them" for the foreign key, with a sensible example.
SQA N5 style3 marksFor a field that stores a pupil's house from the choices North, South, East and West, state and justify a suitable field type and one validation check.Show worked answer →
Marks for the field type, the validation and a justification.
Field type: text (string), because the house names are words.
Validation: a restricted choice (lookup) check that only allows North, South, East or West.
Justification: this stops invalid or misspelled entries such as "Norht", keeping the data accurate. A presence check could also be added so the field cannot be left empty.
Markers reward a sensible type, a validation that suits a fixed set of options (restricted choice), and a reason linked to keeping data valid.
Related dot points
- SQL implementation: searching and sorting with SELECT, FROM, WHERE and ORDER BY, and changing data with INSERT, UPDATE and DELETE, then testing and evaluating the database.
An SQA National 5 Computing Science answer on implementing a database in SQL, covering how to search and sort records using SELECT, FROM, WHERE and ORDER BY, how to add, change and remove data with INSERT, UPDATE and DELETE, and how to test and evaluate that the database meets its requirements.
- Data types and structures: variables holding character, string, numeric (integer and real) and Boolean values, and the 1-D array as a structure for holding many values of the same type under one name.
An SQA National 5 Computing Science answer on data types and structures, covering the variable types of character, string, integer, real and Boolean, when each is chosen, and how a one-dimensional array stores many values of the same type under a single name accessed by an index.
- Analysis: identifying the purpose, scope and boundaries of a problem and writing functional requirements in terms of inputs, processes and outputs.
An SQA National 5 Computing Science answer on the analysis stage of software development, covering how to identify the purpose, scope and boundaries of a problem and how to write functional requirements in terms of the inputs, processes and outputs a finished program must provide.
- The iterative software development process: analysis, design, implementation, testing, documentation and evaluation, and why the process is iterative rather than strictly linear.
An SQA National 5 Computing Science answer on the software development process, covering the six stages of analysis, design, implementation, testing, documentation and evaluation, what is produced at each stage, and why the process is iterative so that developers loop back to earlier stages when problems are found.
- Security risks and precautions: common threats to a computer system, the precautions of encryption, passwords and biometrics, and the legal protection of the Computer Misuse Act.
An SQA National 5 Computing Science answer on security risks and precautions, covering common threats to a computer system such as viruses and hacking, the precautions of encryption, strong passwords and biometrics, and how the Computer Misuse Act makes unauthorised access to computer systems illegal.