How do you turn a description of a real-world situation into a sound relational database design?
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.
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 take a description of a real situation and design a relational database for it: identify the entities and how they relate, draw an entity-relationship diagram, record the design in a data dictionary, and choose the right keys. This is the design stage of a database project, before any SQL is written.
Entities, attributes and relationships
Analysis starts by reading the problem and identifying the entities (the things data is kept about, such as Student or Course) and their attributes (the facts about each, such as a student's name and date of birth). You then identify how entities relate. The cardinality of a relationship matters: a class has many students but each student is in one class (one-to-many), whereas students and courses are typically many-to-many.
Resolving a many-to-many relationship
A relational database cannot represent a many-to-many relationship with two tables alone, because neither side can hold a single foreign key for the many on the other side. The fix is a link (junction) entity placed between them. It holds a foreign key to each original entity, and any data that belongs to the pairing (such as the date a student enrolled on a course). This converts one many-to-many into two one-to-many relationships, which the model can store.
Entity-relationship diagrams
An ERD is the visual design. Each entity is a box, the relationships are lines between them, and the cardinality (one or many) is marked at each end, often with crow's-foot notation. The ERD is the blueprint that the tables and their keys are built from, and the exam expects you to both draw one from a description and read tables off one.
The data dictionary
The data dictionary is the written record of the design, listing every attribute of every entity with its data type, field size or format, whether it is a key, and any validation (such as a required field or a permitted range). It is the bridge from the ERD to the SQL that creates the tables, and it makes the design unambiguous for whoever implements it.
Try this
Q1. State what is meant by an entity in database design. [1 mark]
- Cue. A thing the database stores data about, which becomes a table.
Q2. Name the kind of entity used to resolve a many-to-many relationship. [1 mark]
- Cue. A link (junction) entity, holding a foreign key to each original entity.
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: ERD4 marksA library lends many books to many members. Identify the entities, the relationship between them, and explain how the design represents this many-to-many relationship.Show worked answer →
Entities: Book and Member (1 mark). The relationship between Book and Member is many-to-many: a member borrows many books and a book is borrowed by many members over time (1 mark). A relational database cannot store a many-to-many relationship directly, so it is resolved by a link (junction) entity, for example Loan, sitting between them (1 mark). Loan holds a foreign key to Book and a foreign key to Member, turning one many-to-many into two one-to-many relationships (1 mark).
AH style: keys3 marksDefine primary key, foreign key and composite key.Show worked answer →
Primary key: an attribute (or set of attributes) that uniquely identifies each record in a table, with no duplicates and no null (1 mark). Foreign key: an attribute in one table that refers to the primary key of another table, creating the link between them (1 mark). Composite key: a primary key made of two or more attributes together, used when no single attribute is unique on its own (1 mark). Markers reward the correct role of each key type.
Related dot points
- 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.
- 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.
- 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.
- Describe and compare iterative (agile) and structured (waterfall) development methodologies, and apply the analysis, design, implementation, testing, documentation, evaluation and maintenance stages of the software development process.
A focused answer to the SQA Advanced Higher Computing Science content on development methodologies, covering iterative agile and structured waterfall approaches and every stage of the software development process from analysis to maintenance.