Revision notes for AQA A Level Computer Science Conceptual data models and entity relationship modelling. Open the guide for explanations and worked examples. Written against the AQA A Level Computer Science specification, so the content matches what's examinable rather than general Computer Science background.
Conceptual data models and entity relationship modelling
What you'll learn
- How to turn data requirements into a conceptual data model.
- How to identify entities, attributes and entity identifiers.
- How to decide relationships and cardinalities between multiple entities.
- How to represent a model using an entity relationship diagram and entity descriptions.
The starting point: data requirements
A database design usually begins with data requirements: statements describing what data a system must store and the rules that connect that data.
For example, a school system might need to store students, courses and which students are enrolled on which courses.
At this stage, you are not writing SQL or choosing data types. You are working out what the data means.
Definition
Conceptual data model
A data model is an abstract description of the data needed by a system. A conceptual data model describes the important real-world things, their properties and their relationships, without worrying about exactly how they will be implemented in a particular database system.
A good conceptual model helps you avoid missing important data, storing the same fact repeatedly, or making relationships impossible to query later.
Entities, attributes and identifiers
The first skill is to separate the “things” the system stores data about from the “facts” stored about those things.
Definition
Entity, attribute and entity identifier
An entity is a type of thing about which data is stored, such as STUDENT, BOOK or ORDER. An entity instance is one specific example of that entity, such as one particular student. An attribute is a named item of data about an entity. An entity identifier is the attribute, or combination of attributes, that uniquely identifies each entity instance.
For example:
STUDENT(StudentID, Forename, Surname, DateOfBirth)
Here, STUDENT is the entity. StudentID, Forename, Surname and DateOfBirth are attributes. The underlined StudentID is the entity identifier.
An identifier must be unique for each instance. Sometimes one attribute is enough, such as StudentID. Sometimes the identifier is composite, meaning it uses more than one attribute together.
Example
Selecting identifiers and attributes
A music shop stores each physical instrument for sale. For every instrument, it records SerialNumber, Model, Colour and Price. The serial number is unique to each physical instrument.
- Treat INSTRUMENT as an entity because the system stores many separate instrument records, and each record represents a real thing the shop sells.
- Compare possible identifiers: Model, Colour and Price can all be repeated by many instruments, but SerialNumber is unique to one physical instrument.
- Write the entity description as INSTRUMENT(SerialNumber, Model, Colour, Price), with SerialNumber underlined as the entity identifier.
Common Mistake
Using names as identifiers
Names are usually poor entity identifiers. Two students can have the same name, and a person’s name can change. Prefer a stable unique identifier such as StudentID, MemberID or CustomerNumber if the scenario gives one.
Relationships and cardinality
Entities rarely exist in isolation. A model also needs to show how entities are connected.
Definition
Relationship and cardinality
A relationship is an association between entities. Cardinality describes how many instances of one entity can be associated with instances of another entity, such as one-to-one, one-to-many or many-to-many.
To decide cardinality, ask the question in both directions:
- For one instance of entity A, how many instances of entity B can be linked?
- For one instance of entity B, how many instances of entity A can be linked?
The three common relationship cardinalities are shown below.
A many-to-many relationship often needs special care. An associative entity, also called a linking entity, is an entity created to represent a relationship, especially when that relationship has its own attributes.
Example
Deciding cardinality
An online shop stores customers, orders and products. A customer can place many orders. Each order is placed by one customer. Each order can contain many products, and each product can appear in many different orders. The shop records the quantity of each product in each order.
- For CUSTOMER and ORDER, one customer can place many orders, but one order belongs to one customer, so the relationship is one-to-many.
- For ORDER and PRODUCT, one order can contain many products, and one product can appear in many orders, so the direct relationship is many-to-many.
- Quantity is not just a fact about PRODUCT, because the same product can have different quantities in different orders. It is also not just a fact about ORDER, because one order contains several products.
- Introduce an associative entity such as ORDERLINE to represent each product line within an order, storing attributes such as Quantity.
Producing an entity relationship diagram
An entity relationship diagram, often shortened to ERD, is a diagram that represents a data model.
A typical ERD shows:
- entities as labelled boxes;
- attributes inside the entity boxes, or separately in entity descriptions;
- relationships as lines between entities;
- cardinality labels such as “one” and “many”.
Different textbooks use slightly different ERD notation, so the exact symbols may vary. In an exam, the important point is that your entities, relationships and cardinalities are clear.
Tip
Keep the diagram and descriptions consistent
If your ERD contains an entity, it should also appear in your entity descriptions. If your entity description uses an identifier, make sure the same identifier is shown or implied in the diagram.
Here is a typical ERD and matching entity descriptions for a course enrolment model. The many-to-many idea “students enrol on courses” is represented using the associative entity ENROLMENT, because the enrolment itself has an EnrolDate.
The entity description format expected for this topic is:
EntityName(IdentifierAttribute, Attribute2, Attribute3, ...)
For example:
COURSE(CourseID, Title)
If more than one attribute forms the identifier, underline each of those attributes:
ENROLMENT(StudentID, CourseID, EnrolDate)
That composite identifier assumes a student can enrol on a particular course only once. If the requirements said students could retake the same course, you would need a different identifier, such as EnrolmentID or an AttemptNumber as part of the composite identifier.
Common Mistake
Putting relationship data on the wrong entity
EnrolDate is not really a fact about a STUDENT alone or a COURSE alone. It is a fact about a particular student’s enrolment on a particular course, so it belongs on ENROLMENT.
Producing a data model from requirements
When you are given a scenario, work from meaning first. A useful process is:
- Identify candidate entities: things the system stores data about.
- Assign attributes to the entity they describe.
- Choose entity identifiers that are unique and stable.
- Identify relationships between entities.
- Decide the cardinality of each relationship.
- Check the model against the original requirements.
Example
Building a library data model
A library stores members, books and physical copies of books. A book can have several copies. A member can borrow copies. Each borrowing records the date borrowed and the due date. A copy can be borrowed many times over its life, but each loan record is for one member and one copy.
- Choose MEMBER, BOOK and COPY as entities because the system stores separate data about each of these. Also choose LOAN as an entity because a borrowing event has its own attributes and many loan records will exist over time.
- Assign likely attributes and identifiers: MEMBER needs MemberID, Name and Email; BOOK needs ISBN, Title and Author; COPY needs CopyID and ShelfLocation; LOAN needs LoanID, DateBorrowed and DueDate.
- Work out relationships: one BOOK can have many COPY records, but each COPY is of one BOOK. One MEMBER can have many LOAN records, but each LOAN belongs to one MEMBER. One COPY can appear in many LOAN records over time, but each LOAN is for one COPY.
- Write the entity descriptions as MEMBER(MemberID, Name, Email); BOOK(ISBN, Title, Author); COPY(CopyID, ISBN, ShelfLocation); LOAN(LoanID, MemberID, CopyID, DateBorrowed, DueDate).
- Check the model against the scenario: if the same member borrows the same copy twice on different dates, there can be two separate LOAN records, so the model supports the library’s history of borrowing.
Checking the quality of your model
Before you finish, test the model carefully:
- Can each entity have many instances?
- Does every attribute describe exactly one instance of its entity?
- Is each identifier unique for the entity it identifies?
- Are relationship attributes placed on the correct entity?
- Does every relationship’s cardinality match the wording of the scenario?
- Have you avoided repeated attributes such as Book1, Book2, Book3?
Exam technique
In the exam
- Start by writing entity descriptions, underlining the identifier attribute or attributes for each entity.
- For every relationship, test both directions: “one A can link to how many B?” and “one B can link to how many A?”
- If a many-to-many relationship has its own data, introduce an associative entity and connect it with two one-to-many relationships.
Self review
Check yourself
- Why is StudentName usually a weaker entity identifier than StudentID?
- In a cinema booking system, what entities might you create for customers, screenings and booked seats?
- If a relationship has an attribute such as DateJoined or Quantity, what does that suggest about your data model?