Skip to topic
    ← Back to course topics

    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

    1. 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
    1. 1Day 1-2: Master core terminology (entity, attribute, primary key, foreign key, composite key) and practice identifying entities from written briefs.
    2. 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.
    3. 3Day 5-6: Focus exclusively on resolving M:N relationships into two 1:M relationships using linking entities and identifying composite primary keys.
    4. 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)
    Draw

    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.

    Explain

    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.

    Design

    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)
    Pitfall: Failing to resolve many-to-many (M:N) relationships into two one-to-many (1:M) relationships using an intermediate linking entity.
    ❌ Weak Answer (Loses Marks):The student draws a direct line between Student and Course with crow's feet on both ends and states that a student takes many courses and a course has many students.
    Example improved answer:Direct many-to-many relationships cannot be directly implemented in a relational database. An intermediate linking entity such as Enrolment must be introduced, transforming the M:N relationship into two 1:M relationships: Student (1) to Enrolment (M), and Course (1) to Enrolment (M). The Enrolment entity typically uses a composite primary key consisting of StudentID and CourseID.
    Examiner Tip: Whenever an exam scenario describes a many-to-many relationship, immediately create an associative entity. Ensure the 'crow's foot' or 'many' symbol points toward the linking entity from both original entities.
    Pitfall: Placing the foreign key on the incorrect side of a one-to-many relationship.
    ❌ Weak Answer (Loses Marks):The primary key of the 'Many' table is posted into the 'One' table as a foreign key.
    Example improved answer:In a one-to-many (1:M) relationship, the foreign key must always reside in the entity on the 'many' side of the relationship to reference the primary key of the entity on the 'one' side, preventing repeating groups and data redundancy.
    Examiner Tip: Use the rule of thumb: 'The Many holds the Foreign Key'. For example, in Customer (1) to Order (M), CustomerID must be placed inside the Order entity.
    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. 1.Step 1: Identify the primary real-world entities from the scenario: Patient, Doctor, and Appointment.
    2. 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. 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. 4.Step 4: Define primary keys and foreign keys for each entity to ensure referential integrity.
    5. 5.Step 5: Write out the entities in standard relational notation, underlining primary keys and marking foreign keys with asterisks or hashes.
    Final Answer: Patient(PatientID, FirstName, LastName, DateOfBirth) Doctor(DoctorID, FirstName, LastName, Specialism) Appointment(AppointmentID, AppointmentDate, AppointmentTime, PatientID*, DoctorID*) Where PatientID and DoctorID in Appointment are foreign keys referencing Patient and Doctor respectively.

    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. 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. 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. 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. 4.Step 4: Format using standard AQA entity representation notation with primary keys underlined and foreign keys marked.
    Final Answer: Relationships: Artist to Album: 1:M (One-to-Many) Album to Track: 1:M (One-to-Many) Relational Schema: Artist(ArtistID, ArtistName, Bio) Album(AlbumID, Title, ReleaseYear, ArtistID*) Track(TrackID, Title, DurationSeconds, TrackNumber, AlbumID*)
    Active Recall Memory Test
    Why can relational databases not directly implement a many-to-many (M:N) relationship?
    Key Fact: Because doing so would require repeating groups or storing multiple values in a single foreign key field, violating first normal form (1NF) and causing data redundancy.
    Where must the foreign key always be placed in a one-to-many (1:M) relationship?
    Key Fact: The foreign key must always be placed within the entity on the 'many' side of the relationship.
    What is a composite primary key and when is it typically used in ER modelling?
    Key Fact: A primary key composed of two or more attributes combined; it is typically used in a linking entity created to resolve an M:N relationship.
    What is the difference between an entity and an attribute?
    Key Fact: An entity is an independent object or concept about which data is held, whereas an attribute is a specific characteristic or property that describes an entity.
    Frequently Asked Questions
    Why is an entity-relationship diagram called a conceptual model?
    It is called a conceptual model because it represents data requirements at a high, abstract level based purely on real-world business logic. It does not consider physical implementation constraints such as database software, storage media, data types, or indexing strategies. This allows designers and stakeholders to agree on data structure without getting bogged down in implementation details.
    How do I know whether something is an entity or an attribute?
    Ask yourself whether the item has its own independent properties or if it merely describes something else. For instance, 'Address' might seem like an attribute of 'Student', but if your system needs to track multiple residents, building types, and council tax bands for each address, 'Address' becomes an entity. If it only holds a single string value to describe the student, it remains an attribute.
    Does a linking entity always have to use a composite primary key?
    No, it does not. While a linking entity commonly uses a composite key made up of the primary keys of the two related entities (e.g. StudentID + CourseID), it is also entirely acceptable to assign it its own surrogate primary key (e.g. EnrolmentID). A surrogate key is often preferred if the linking entity itself has child entities or requires multiple identical combinations over time.
    Which ER diagram notation should I use in AQA A-Level Computer Science exams?
    AQA accepts standard crow's foot notation as well as traditional single/double-headed arrow or textually annotated lines (e.g., 1 and M, or 1 and ∞). Crow's foot notation (a single line for 'one' and a three-pronged fork for 'many') is the industry standard and the most unambiguous format to use in your exam scripts.
    What is the difference between cardinality and degree of relationship?
    In A-Level exam contexts, 'degree of relationship' refers to the numerical association between entities: one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). 'Cardinality' technically refers to the specific minimum and maximum number of occurrences of one entity that can be related to a single occurrence of another (e.g. exactly 1, 0 or more, 1 or more). In many exam questions, these terms are used interchangeably.