COMP9120 · Database Management Systems
Conceptual Modelling (ER/EER)
Conceptual modelling is the second step of database design in COMP9120 Database Management Systems at the University of Sydney: it turns a written description of what a business needs to store into a precise Entity–Relationship (ER) diagram, then into relational tables. This chapter walks through Chen’s ER model and its Enhanced (EER) extensions — entities, relationships, cardinality, weak entities, IsA and aggregation — and the fixed recipe for mapping a diagram to a schema, exactly as the unit assesses it in Assignment 1 and the final exam.
What this chapter covers
- 01Where conceptual (ER) modelling sits in the design pipeline: requirements to logical schema
- 02Entities, attributes and the key family (superkey, candidate key, primary key)
- 03The eight core EER symbols and when to use each attribute type
- 04Relationships: degree (unary/binary/ternary), role names and relationship attributes
- 05Mapping cardinality 1:1, 1:N and N:M — and the many-to-one direction trap
- 06Key and participation constraints (arrow, thick line) and (min..max) annotations
- 07Weak entities: identifying owner, discriminator and the owner-PK-plus-discriminator key
- 08Generalisation/specialisation (IsA) with disjoint/overlap and total/partial constraints
- 09Aggregation: treating a relationship as an entity so it can participate in another
- 10The five ER-to-relations mapping rules used in the exam's opening question
Read cardinality and participation, then map an ER model to relations
- +2Cardinalities: WorksOn is N:M (a project has many employees and an employee works on many projects). Manages is 1:N from Employee to Project (one employee manages many projects, but each project has exactly one manager).
- +1Participation: 'every project is managed by exactly one employee' means Project participates totally in Manages, with (min..max) = (1..1) on the Project side; the Employee side is (0..*), partial.
- +2Map the 1:N Manages by posting the '1' side (Employee) primary key as a foreign key on the 'N' side (Project), and make manager_id NOT NULL to enforce the total participation.
- +1Map the N:M WorksOn as a separate table with foreign keys to both sides and the relationship attribute hours; its primary key is the pair (emp_id, proj_id).
Key terms
- Entity set
- A collection of similar real-world objects we store data about, drawn as a rectangle; its instances are individual entities.
- Candidate key
- A minimal superkey — a set of attributes that uniquely identifies an entity such that no proper subset also does. The chosen candidate key becomes the underlined primary key.
- Mapping cardinality
- A limit on how many entities on one side of a relationship an entity on the other side may link to: 1:1, 1:N, N:1 or N:M. 'Many-to-one from A to B' means each A relates to at most one B.
- Participation constraint
- Whether every entity of a set must take part in a relationship: total (at least one instance, drawn as a thick or double line) or partial (may take part in none).
- Weak entity
- An entity with no key of its own (double rectangle). It borrows identity from an identifying owner via an identifying relationship, always participates totally, and its key is the owner's primary key plus its own discriminator.
- Discriminator
- The partial key of a weak entity (drawn with a dashed underline) that distinguishes its instances within one owner, e.g. a dock number within a station.
- IsA (generalisation/specialisation)
- An EER relationship (triangle) where a subclass is a kind of a superclass: attributes(subclass) contains attributes(superclass) and entity-set(subclass) is a subset of entity-set(superclass). The subclass inherits all attributes and relationships.
- Aggregation
- Treating a whole relationship as an abstract entity (a dashed box) so it can itself participate in another relationship — a relationship between relationships.
Conceptual Modelling (ER/EER) FAQ
Is ER modelling actually on the COMP9120 final exam, or just in the assignment?
Both. Conceptual modelling is Week 2 content, so it is assessed in Assignment 1 (a group task worth 13% that builds an ER model and derives a PostgreSQL schema), it is within the scope of the in-class Quiz (24%, covering Weeks 1 to 9), and the final exam (50%, paper-based, 2 hours, closed book) opens with an ER-to-relations plus data-integrity question. Confirm the exact marks and weightings on Canvas.
What is the difference between a weak entity and a plain entity with a foreign key?
A weak entity has no primary key of its own — its identity is only meaningful relative to an owner, so its key is the owner's primary key plus a local discriminator, and it must participate totally in the identifying relationship. A normal entity has its own key and can reference others through optional foreign keys without depending on them for identity.
Can AI help me with conceptual modelling in COMP9120?
Yes, as a study aid. Sia is an AI tutor that explains concepts step by step — for example it can walk you through why a many-to-many relationship needs its own table, or check that you have carried an owner's key into a weak entity. It does not hand back finished assignment or exam answers and cannot promise a grade; the value is understanding the mapping rules well enough to apply them yourself under exam conditions.
Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.
Exam move
Practise the two-move loop until it is automatic: first draw the ER diagram from the words, then translate it to relations with the five fixed rules. Drill the constraint vocabulary (arrow = key constraint, thick line = total participation) and the direction trap, because a misread 'many-to-one' flips which side gets the foreign key. Rehearse full ER-to-relations questions by hand and time yourself: the final exam is 2 hours and the opening ER-and-integrity question in the example papers is worth around 30 marks, so aim to finish it in about 25 minutes and keep the rest of the paper in reserve.
