How do you model the data for a real problem as linked tables before building a database?
Analysing a database problem and designing the data model: entity-relationship diagrams, entities and attributes, relationships and cardinality, the data dictionary, and primary, foreign and compound keys.
An SQA Higher Computing Science answer on database analysis and design, covering entity-relationship diagrams, entities and attributes, relationships and cardinality, the data dictionary, and primary, foreign and compound 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 key area is asking
The SQA wants you to analyse a database problem and design the data model: drawing entity-relationship diagrams, identifying entities and attributes, stating relationships and their cardinality, writing a data dictionary, and using primary, foreign and compound keys. This is the design stage before any SQL is written.
Why analyse and design first
A database built without a design becomes a tangle of duplicated, inconsistent data. Analysis identifies what data the system must hold and what it must do with it (the end-user and functional requirements), and design turns that into a clean structure of linked tables. Getting the model right prevents redundancy and the update problems it causes.
Entities and attributes
So a Member entity might have attributes memberID, name, address and joinDate. Choosing the right entities and attributes from a problem description is the heart of the analysis.
Relationships and cardinality
Entities are linked by relationships, and the cardinality states how many records of one entity relate to how many of the other.
Entity-relationship diagrams
An entity-relationship (ER) diagram shows the entities (as boxes) and the relationships (as lines) between them, labelling the cardinality. A common notation uses a crow's foot at the "many" end of a relationship:
Member ---< Loan >--- Book
Here one Member has many Loans (crow's foot at Loan), and each Book can appear in many Loans. An entity-occurrence diagram shows the same relationship using actual example records, which helps confirm the cardinality is right.
Keys
For a Customer and Order database: customerID is the primary key of Customer; customerID in the Order table is a foreign key linking each order to its customer. In a link table joining Students and Courses, the primary key might be the compound key (studentID, courseID), unique only when combined.
The data dictionary
A data dictionary documents the design of each table precisely: for every attribute it records the name, the data type, the size or format, whether it is a key (primary or foreign), whether it is required, and any validation (such as a permitted range). It is the blueprint the database is built from and the reference for anyone maintaining it.
Examples in context
Every relational database, from a school's records to an online shop, is built this way. Online stores model Customers, Products and Orders, resolving the many-to-many between Orders and Products with an "order line" link table holding a compound key - exactly the pattern above. Designers draw ER diagrams before writing any SQL, and a good data dictionary is what lets a new developer understand a database months later. This design stage feeds directly into the SQL key areas, where these tables are queried and updated.
Try this
Q1. State what an entity becomes in the finished database. [1 mark]
- Cue. A table.
Q2. State the cardinality of "one customer places many orders, each order by one customer". [1 mark]
- Cue. One-to-many.
Q3. State what a foreign key does. [2 marks]
- Cue. It is an attribute in one table that refers to the primary key of another table, creating the link between them.
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 Higher (style)4 marksA library lends books to members. One member can borrow many books over time, but each loan is of one book to one member. Draw the entity-relationship diagram and state the cardinality of the relationship.Show worked answer →
The entities are Member and Loan (and Book). Focusing on Member and Loan:
Member ---< Loan (a "crow's foot" at the Loan end)
The relationship is one-to-many: one Member can have many Loans, but each Loan belongs to exactly one Member. The "one" side is Member; the "many" side is Loan.
To implement it, the primary key of Member (for example memberID) appears as a foreign key in the Loan table, linking each loan to its member.
Markers reward identifying the entities, the one-to-many cardinality (one member to many loans), and recognising that the member's primary key becomes a foreign key in the loans table.
SQA Higher (style)3 marksExplain the difference between a primary key and a foreign key, and give an example of each in a database of customers and their orders.Show worked answer →
A primary key is an attribute (or combination) that uniquely identifies each record in a table, so no two records share the same value and it is never empty. For example, customerID is the primary key of the Customer table.
A foreign key is an attribute in one table that refers to the primary key of another table, creating the link between them. For example, customerID in the Order table is a foreign key referring to customerID in the Customer table, recording which customer placed each order.
Markers reward primary key = unique identifier of a record in its own table, foreign key = an attribute referring to another table's primary key to form a link, with a correct example of each.
Related dot points
- Referential integrity and entity integrity between linked tables, and validation of data entered into a database (presence, restricted choice and field length checks).
An SQA Higher Computing Science answer on database integrity and validation, covering entity integrity, referential integrity between linked tables, and validation checks such as presence, restricted choice and field length.
- Querying a relational database with SQL SELECT, including WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.
An SQA Higher Computing Science answer on querying a relational database with SQL, covering SELECT with WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.
- Changing the data in a relational database with SQL INSERT, UPDATE and DELETE on prepopulated linked tables, and the implications of these operations.
An SQA Higher Computing Science answer on changing data in a relational database, covering the SQL INSERT, UPDATE and DELETE operations on prepopulated linked tables and the implications of each.
- Data types and structures: variables of simple types, 1-D arrays, records, and parallel arrays or arrays of records, with string operations.
An SQA Higher Computing Science answer on data types and structures, covering simple variable types, 1-D arrays, records, parallel arrays and arrays of records, plus string operations such as concatenation.