Conceptual data models and entity relationship modelling — AQA A-Level Computer Science
A conceptual data model is an abstract representation of real-world data structures and relationships independent of physical storage. In AQA A-Level Computer Science, entity relationship (ER) modelling visually represents entities, attributes, and degree of relationship (1:1, 1:M, M:N), providing the essential foundation for relational database design.
Conceptual data models and entity relationship modelling explained
A data model represents the data a system must store and the relationships between the items of data, usually as an entity relationship diagram.
Read the full explanation
To produce one from given data requirements, first identify the entities, which are the real-world things about which data is held, such as Customer or Order. Next identify the attributes of each entity, choosing a primary key that uniquely identifies each record. Then identify the relationships between entities and their cardinality, for example one customer places many orders, which is written as a one-to-many relationship. Where a many-to-many relationship arises, such as students enrolling on many courses with each course holding many students, a linking entity is usually needed to resolve it into two one-to-many relationships.
Your focus
- Produce a data model from given data requirements for a simple scenario involving multiple entities.
Which command word is this?
Construct a clear visual entity relationship diagram using standard notation (e.g. crow's foot or labelled lines). All entity names must be singular and clearly boxed, with precise cardinality shown on each end.
Conceptual data models and entity relationship modelling exam tips
Topic Overview
Conceptual data modelling is the process of creating an abstract, implementation-independent representation of the data structures and business rules in a given system domain. It allows system architects and database designers to identify real-world objects, their properties, and the logical associations connecting them prior to physical database construction.
Entity Relationship (ER) modelling is the core visual tool used at A-Level to map entities, attributes, and relationships. Mastery of ER modelling ensures that students can analyze complex scenarios, remove many-to-many redundancies via junction entities, and produce normalized relational database schemas ready for SQL implementation.
Key Concepts
- →Entity: Any real-world object, person, event, or concept about which data is collected and stored.
- →Attribute: A distinct property, characteristic, or field describing a particular entity.
- →Entity Identifier (Primary Key): An attribute or minimal set of attributes that uniquely identifies a specific instance of an entity.
- →Cardinality / Degree of Relationship: The numerical relationship between occurrences of entities, classified as One-to-One (1:1), One-to-Many (1:M), or Many-to-Many (M:N).
- →Linking / Junction Entity: An intermediate entity introduced to decompose an unworkable M:N relationship into two valid 1:M relationships, often using a composite primary key.
Marking Points
- Identifies each entity from the scenario as a separate thing about which data is held.
- Assigns appropriate attributes to each entity, including a primary key that uniquely identifies each record.
- Identifies the relationship between each pair of related entities.
- States the cardinality of each relationship correctly, such as one-to-many or many-to-many.
- Resolves any many-to-many relationship by introducing a linking entity.
- Checks that the model covers every stated data requirement without redundant entities.
Examiner Tips
- 💡Work through the requirements sentence by sentence, listing each noun as a possible entity and each verb as a possible relationship.
- 💡Check the final diagram against the original requirements to confirm nothing is missing.
- 💡Label every relationship with its cardinality and every entity with its primary key.
- 💡Always use singular nouns when naming entities (e.g., 'Student', not 'Students') as per standard convention.
- 💡When drawing ER diagrams, always double-check which end of the connector has the single line ('one') and which has the crow's foot ('many').
- 💡Underline primary keys and denote foreign keys clearly with an asterisk (*) or italics when writing out relational database schemas.
Common Mistakes
- Treats an attribute as an entity, for example making Address a separate entity when it is only needed as a field. Correction: make a separate entity only when it has its own attributes or relationships.
- Omits the primary key or chooses an attribute that does not uniquely identify records. Correction: select an attribute that is unique for every record.
- Shows a many-to-many relationship directly without a linking entity. Correction: introduce a linking entity so the relationship becomes two one-to-many relationships.
- Draws relationships without cardinality, so the diagram cannot show how many records are involved. Correction: add a cardinality marker to each relationship.
- Treating individual records or instances as separate entities (e.g. creating an entity for 'TeacherSmith' rather than an entity called 'Teacher' with instances).
- Believing that Many-to-Many (M:N) relationships can be directly represented by placing foreign keys in both original tables without a linking table.
- Confusing an attribute with an entity; if a concept possesses multiple distinct properties of its own, it must be modelled as an entity rather than a single attribute.
Revision Plan
- 1Day 1-2: Master core terminology (entity, attribute, primary key, foreign key, composite key) and practice identifying entities from written briefs.
- 2Day 3-4: Practice drawing and interpreting ER diagrams focusing on 1:1, 1:M, and M:N relationships using standard crow's foot notation.
- 3Day 5-6: Focus exclusively on resolving M:N relationships into two 1:M relationships using linking entities and identifying composite primary keys.
- 4Day 7-8: Complete exam-style questions translating business specifications into formal relational schemas and ER diagrams under timed conditions.
Exam Question Types
- 📋Diagram Completion: Adding missing cardinality markers or intermediary entities to a partially completed ER diagram.
- 📋Scenario Modeling: Reading a case study and extracting all entities, attributes, and degrees of relationship.
- 📋Schema Conversion: Converting an ER diagram into standard textual relational notation showing primary and foreign keys.
- 📋Evaluation / Problem Identification: Analyzing a faulty ER model, identifying design flaws (such as unresolved M:N relationships), and explaining how to rectify them.
Command Word Expectations (AQA)
Construct a clear visual entity relationship diagram using standard notation (e.g. crow's foot or labelled lines). All entity names must be singular and clearly boxed, with precise cardinality shown on each end.
Set out the reasons why a specific modelling decision is made (e.g. why an M:N relationship must be resolved) using technical terms such as data redundancy, referential integrity, and insertion anomalies.
Provide a complete structural solution for a scenario, including all entities, their primary keys, foreign keys, and attributes, expressed in standard relational database notation.
How Students Lose Marks (Examiner Pitfalls)
Step-by-Step Worked Solutions
Question: A GP surgery needs a database to track appointments. A patient can book multiple appointments with various doctors. A doctor sees multiple patients each day. An appointment occurs at a specific date and time, and involves exactly one patient and one doctor. Describe the entity relationship model required, explain how the many-to-many relationship is resolved, and express the final design in standard relational notation.
- 1.Step 1: Identify the primary real-world entities from the scenario: Patient, Doctor, and Appointment.
- 2.Step 2: Determine initial relationships: A Patient books many Appointments, but each Appointment is for one Patient (1:M). A Doctor holds many Appointments, but each Appointment is with one Doctor (1:M). Patient and Doctor have an underlying M:N relationship (one patient sees many doctors; one doctor sees many patients).
- 3.Step 3: Recognize that Appointment acts as the linking entity resolving the M:N relationship between Patient and Doctor into two 1:M relationships: Patient (1) to Appointment (M) and Doctor (1) to Appointment (M).
- 4.Step 4: Define primary keys and foreign keys for each entity to ensure referential integrity.
- 5.Step 5: Write out the entities in standard relational notation, underlining primary keys and marking foreign keys with asterisks or hashes.
Question: A music streaming platform maintains information about Artists, Albums, and Tracks. An artist can release multiple albums. Each album contains multiple tracks. An album is released by only one artist, and a track belongs to only one album. State the degree of relationship between Artist and Album, and between Album and Track. Then, outline the relational schema showing appropriate primary and foreign keys.
- 1.Step 1: Analyze the relationship between Artist and Album. One artist can release many albums, but an album belongs to exactly one artist. This is a 1:M relationship.
- 2.Step 2: Analyze the relationship between Album and Track. One album contains many tracks, but each track belongs to only one album. This is a 1:M relationship.
- 3.Step 3: Assign foreign keys to the 'many' side of each relationship. ArtistID becomes a foreign key in Album; AlbumID becomes a foreign key in Track.
- 4.Step 4: Format using standard AQA entity representation notation with primary keys underlined and foreign keys marked.