flowchart TD
STUDENT[STUDENT] ---|"M"| ENROLS_IN{ENROLS_IN}
ENROLS_IN ---|"N"| SECTION[[SECTION]]
SECTION ===|"N"| OFFERING{OFFERING}
OFFERING ---|"1"| COURSE[COURSE]
SECTION ---|"N"| TEACHES{TEACHES}
TEACHES ===|"1"| INSTRUCTOR[INSTRUCTOR]
SID(["SID (key)"]) --- STUDENT
SecNum(["Sec# (partial key)"]) --- SECTION
CourseKey(["Dept+Course# (key)"]) --- COURSE
NameI(["Name (key)"]) --- INSTRUCTOR
INFS1200 — Week 2 Notes
Weak Entities, the EER Model and Design Choices
Module 1, part 2 — continues on from 2026-02-23-conceptual-database-design-and-the-er-model. See er-diagram-notation and relationship-constraints for the reference symbol legend and weak-entity definition.
Outline
- Weak entities (closing out the “Entities and Relationships” section)
- The Enhanced ER (EER) diagram — superclasses/subclasses
- Design choices for conceptual modelling
Weak entities
Entity types that do not have key attributes of their own are called weak entities. Two rows of data show why: an employer insures dependants, and two different employees (SSN 12345 and SSN 22336) each have a dependant named John. Pname on its own cannot identify a dependant; Pname together with the insuring employee’s SSN can.
That gives the four terms:
- A weak entity can be identified uniquely only by considering the primary key of another owner entity and its own partial key, which is underlined with a dotted line.
- The relationship type that relates a weak entity to its owner is the identifying relationship.
- A weak entity and its identifying relationship are represented with double lines.
- The weak entity always has total participation in the identifying relationship — it cannot exist without an owner.
Figure 1: A weak entity and its owner. DEPENDANT has no key of its own: Pname is only a partial key (dotted underline), so its real key is (EMPLOYEE.SSN, DEPENDANT.Pname). INSURES is the identifying relationship and carries its own descriptive attribute Cost. Here the identifying relationship is filled blue and the weak-entity/identifying double lines are crimson; the course’s notation guide draws the same symbols as a magenta diamond and black double lines.
A weak entity can have more than one owner
The identifying relationship does not have to be binary. In this example it is ternary: a CANDIDATE applies to a COMPANY, and the INTERVIEW that results is identified only by both of them together plus its own partial key Details (itself composite — Department + Date).
Figure 2: A weak entity with two owners. INTERVIEW is a weak entity with two owner entity types, so its identifying relationship APPLIES is ternary and its full key is (CANDIDATE.Name, COMPANY.cName, Details). Because INTERVIEW is an entity rather than an attribute, it can be the endpoint of a further relationship — RESULTSIN to JOB OFFER.
This matters downstream: a weak entity with two owners takes a foreign key to each of them, and its primary key is the union of both owner keys and its partial key — see er-to-relational-mapping.
Question — Weak Entity (hotel/room)
Figure 3: ROOM and HOTEL joined by the identifying relationship IN. ROOM is weak: Number carries a dotted underline (partial key), while Address carries a solid one (key). Everything the four options claim turns on telling those two underline styles apart.
Which of the following is true? (A) Two hotels can have the same address. (B) No two rooms can have the same number. (C) No two hotels can have rooms with the same number. (D) No two rooms with the same number can have the same type. (E) None of the above.
(E) None of the above. ROOM’s real key is the composite (HOTEL.Address, ROOM.Number) — a room number is only guaranteed unique within its owning hotel, so (B) and (C) are both false; (A) is false since Address is HOTEL’s key attribute and must therefore be unique per hotel; (D) doesn’t follow from any stated constraint, since nothing at all is asserted about Type.
The EER model — superclasses and subclasses
An entity type is called a class in the EER model. Entities in the same class have the same attributes; a class can be a superclass or a subclass:
- Attributes of a superclass are inherited by the subclasses.
- A subclass can have its own specific attributes.
- A subclass can have its own specific relationships.
- Every entity in a subclass is a member of its superclass(es).
The notation is a ⊂ drawn as an open arc on the connecting line, and the direction of the ⊂ is what says which is which: in E1 ⊂ E2, E1 is the subclass of E2.
Motivating example: a supermarket ITEM has a ProductName and a Price in common with everything else on the shelves; a FOOD item additionally has an ExpiryDate. ITEM is the superclass — it captures the data common to a variety of different objects — and FOOD, an extension of a regular item, is the subclass.
Two ways of arriving at the same picture:
- Specialisation (top-down): define a number of subclasses of an entity type. Each subclass contains a subset of the entities of the superclass, and is defined by a more specific distinguishing characteristic of those entities.
- Generalisation (bottom-up): abstraction — ignore the differences amongst some subclasses and generalise them into a superclass.
In summary: subclasses are a specialisation of the superclass, and the superclass is a generalisation of the subclasses. See specialisation-and-generalisation.
Constraints on specialisation
Two independent choices, and one diagram puts all four combinations together by giving EMPLOYEE two specialisations at once:
Figure 4: Constraints on specialisation. EMPLOYEE carries two independent specialisations: a partial, overlapping one (single line into an o circle — an employee may hold a passport, a driver licence, both, or neither) and a total, disjoint one (double line into a d circle — every employee is either hourly-paid or salaried, and never both). Line style fixes total vs partial; the letter in the circle fixes disjoint vs overlapping. They are orthogonal: any of the four combinations is legal.
| Choice | Drawn as | Asserts |
|---|---|---|
| Total specialisation | Double line from superclass to the circle | Every superclass entity belongs to at least one subclass |
| Partial specialisation | Single line | A superclass entity need not belong to any subclass |
| Disjoint subclass set | d in the circle |
An entity belongs to at most one of the subclasses |
| Overlapping subclass set | o in the circle |
An entity may belong to more than one subclass at once |
The EMPLOYEE → d → SECRETARY/ENGINEER split on the module’s overview diagram (in 2026-02-23-conceptual-database-design-and-the-er-model) is the same apparatus, with the subclasses carrying their own attributes — TypingSpeed on SECRETARY, EngineerDetails on ENGINEER.
Why bother with a subclass at all?
Subclasses let an attribute be stored for some entity instances rather than all of them, which reduces the number of null/empty values. In the event system of Applied class 2, not every SHOW needs a Rating — only ENTERTAINMENT shows do — so putting Rating on the subclass rather than the superclass is a storage-efficiency argument as well as a modelling one. Compare the three reasons a null appears in entity-types-and-attributes.
Exercise — University database (UofU)
Students: unique student id, name, address, phone, registered in a major; visiting students stay for a year. Courses: identified by department + course#, with title and credits. A course may be offered many times, even in the same semester; each offering gets a section# unique for a given course and semester, and is taught by a single instructor. Every instructor is responsible for some section (no idle instructors); instructors have unique names and a higher degree recorded. A student enrols in a course section and gets a mark. A course may have any number of other courses as prerequisites.
STUDENT (key SID, Name, Address, Phone, Major) —M:N ENROLS_IN→ SECTION (attribute Mark); STUDENT has a subclass VISITING_STUDENT (HomeInst, StartDate). SECTION is a weak entity (partial key Sec#, owner COURSE, composite key Sec# + Semester), —N:1 OFFERING→ COURSE (key Dept + Course#, Title, Credits); SECTION —N:1 TEACHES← INSTRUCTOR (key Name, Degree), with total participation on the INSTRUCTOR side (“no idle instructors”). COURSE has a recursive M:N PREREQUISITE relationship with itself (roles higher/requires).
Two clauses do real work. “Section# is unique for a given course and semester” makes SECTION weak with COURSE as its owner — a section number means nothing without the course it belongs to. “There are no idle instructors” is a participation constraint, not a cardinality one: it makes INSTRUCTOR total in TEACHES without changing the N:1 ratio. “Visiting students stay for a year” is the distinguishing characteristic that earns VISITING_STUDENT its subclass.
Design choices for ER conceptual design
When modelling complex organisations a database designer faces design choices, which fall into two categories:
- Equivalent choices — an aspect of the UoD can be represented in an ER diagram by two different methods which are both equivalent, i.e. the resulting database will be the same.
- Inequivalent choices — when a UoD is slightly ambiguous, an aspect of it can be mapped two different ways which both meet the specification but lead to different limitations/constraints being enforced by the database. Note the assumption you made under the diagram.
The four common choice points, each with a worked before/after pair of diagrams in er-design-choices:
- Attribute vs. (weak) entity type — an interview’s
Details(Department,Date) as a key attribute of theAPPLIESrelationship, vs. pulled out into the weakINTERVIEWentity of - Attribute vs. subclass —
EMPLOYEE.Typeas a plain attribute, vs. splitting intoSECRETARY/ENGINEERsubclasses with their own extra attributes. - Subclass relationship vs. superclass relationship — giving
WORKSON(toPROJECT) to theEMPLOYEEsuperclass vs. only toENGINEER. - Binary relationships vs. N-ary relationships —
SUPPLIER/PROJECT/PARTas three binaries (SUPPLIES,CANSUPPLY,USES) vs. one ternarySUPPLIEScarrying aquantity.
Applied Class 1: Intro to DBMS and Basic ERD
Practice for 2026-02-23-conceptual-database-design-and-the-er-model. See er-diagram-notation and relationship-constraints for reference, and dbms-fundamentals for the Section A material.
Purpose: practical experience in using conceptual modelling, such as ER diagrams, to capture important aspects of a system (Universe of Discourse). By the end of the class you should be able to identify key DBMS architecture concepts, identify key components of an ER diagram, create semantically correct ER diagrams, develop a basic ER diagram from a UoD, and evaluate and justify basic design choices.
The solution diagrams here are drawn in TikZ rather than in the Mermaid approximation described at the bottom of er-diagram-notation, so that the mandatory Chen symbols render literally: a solid underline for a key attribute, a dashed oval for a derived attribute, a double-outlined oval for a multivalued attribute, and a double line into a diamond for total participation. The shapes are unfilled (black on white) rather than in the purple-entity / magenta-diamond / grey-oval house fills.
Section A — DBMS architecture concepts
Question 1 — Three-schema architecture matching
Match each concept to its description: (1) describes the physical storage structure, (2) describes the whole database’s structure for a community of users, (3) change the conceptual schema without changing external views/ applications, (4) modify the physical schema without changing the logical schema, (5) provides access to particular parts of the database to users.
Enter the numbers matching External Level, Logical data independence, Conceptual Level, Physical data independence, Internal Level, in that order, comma-separated:
External Level → 5, Logical data independence → 3, Conceptual Level → 2, Physical data independence → 4, Internal Level → 1.
The two easy-to-swap pairs are the ones to check. Levels describe structure; independence properties describe what you are allowed to change without disturbing something else. And logical independence is about the conceptual↔︎external boundary, physical independence about the internal↔︎conceptual one — the three-schema figure in dbms-fundamentals brackets each property onto exactly the mapping it rides.
Question 2 — Typical DBMS functions
Select all options that are a typical function of a DBMS: (1) providing secure access, (2) enforcing integrity constraints, (3) normalising the relational schema, (4) handling concurrent access, (5) recommending changes to database design.
Enter the correct option numbers in ascending order, comma-separated (e.g. 1,2,3):
1, 2, 4 — providing secure access, enforcing integrity constraints, and handling concurrent access are all typical DBMS functions. Normalising a schema and recommending design changes are design-time human decisions, not something the DBMS itself does.
The rule that separates them: a DBMS enforces the design it is handed, it does not choose it. Every option phrased as a design decision is a distractor.
Section B — Entities and attributes
You draw an ER diagram for one entity from a RESTAURANT example. The RESTAURANT database keeps track of a restaurant’s chefs, dishes and other information.
Question 1 — RESTAURANT: CHEF entity
The system stores each chef’s name (which consists of their first name, middle name and last name), which is unique. Their date of birth and age are also recorded, along with any specialisations that they have.
Four different attribute kinds appear in one small diagram, which is the point of the question:
Nameis both composite (it splits intoFirstName,MiddleName,LastName) and the key — “which is unique” attaches to the whole name, so the underline goes on the parentNameoval, not on the three components.DoBis a plain simple attribute.Ageis derived — it is computable fromDoB, so it is not stored independently.Specialisationsis multivalued — “any specialisations”, plural, with no fixed number.
Figure 5: The CHEF entity. Underline = key, dashed oval = derived, double outline = multivalued; the three name components hang off the composite Name oval rather than off CHEF directly. The derived Age has a dashed oval but a solid connecting line here; the course notation dashes the connecting line too.
Section C — Basic relationships
Create an ER (or EER) diagram for each Universe of Discourse below. When choosing a cardinality or participation constraint, justify the choice from the UoD. It is worth doing the same in the assignments. Where the UoD is silent on some aspect, make a reasonable decision and write it down as an assumption beneath the diagram rather than leaving it implicit.
The justification tables in the workings below match each constraint in the diagram back to the sentence in the UoD that motivates it.
Question 1 — Olympics database
An athlete is identified by a unique athleteID and their name, age, sex and representative country are also stored. Each event is given a unique eventID and the name of the event and category are also recorded. For venues, a unique ID is stored, along with the venue’s name and its address.
Each athlete participates in at least one event and their final placement for each event is also recorded. Each event is held at one venue. Some venues are backups and hence not every venue stored in the system will be used to host an event.
Three entity types, each with its own key; one M:N relationship carrying an attribute; one N:1 relationship.
Figure 6: The Olympics ER diagram. Double lines mark total participation, and there are three of them: ATHLETE and EVENT are both total in PARTICIPATES, and EVENT is total in HOSTS. VENUE’s single line into HOSTS is the one partial participation, and it is what encodes the backup venues.
Reading the constraints back off the diagram, each against its sentence in the UoD:
| Constraint | Justification from the UoD |
|---|---|
PARTICIPATES is M:N |
An athlete may enter several events, and an event has many athletes |
Placement sits on the relationship, not on either entity |
A placement belongs to an athlete in an event, not to the athlete alone |
ATHLETE total in PARTICIPATES |
“Each athlete participates in at least one event” |
EVENT total in PARTICIPATES |
Implied — an event with no athletes is not an event being run |
HOSTS is N:1 from EVENT to VENUE |
“Each event is held at one venue”; a venue may host several |
EVENT total in HOSTS |
Every event is held somewhere |
VENUE partial in HOSTS |
“Some venues are backups… not every venue will be used to host an event” |
Question 2 — University transcripts database
The university keeps track of each student’s name, student number and social security number. The department each student majors in is also recorded. If the student has a minor, then that department is also recorded. Both social security number and student number have unique values for each student. Students can also take courses and for each course they take a grade is recorded.
Each department is described by a name, department code and office number. Both name and code have unique values for each department. A department may manage some courses.
Each course has a course name, course number, description and offering department. The value of course number is unique for each course.
The interesting parts here are the two entity types that each have two candidate keys and the two relationships between the same pair of entity types.
Figure 7: The university-transcripts ER diagram. STUDENT and DEPARTMENT each carry two underlined (candidate-key) attributes. MAJORS and MINORS are two separate N:1 relationships between the same pair of entity types, and only MAJORS gets the double line — every student majors somewhere, but a minor is optional. COURSE is likewise total in OFFERS, since every course has an offering department.
What each decision rests on:
| Decision | Justification from the UoD |
|---|---|
StudentNo and SSN both underlined |
“Both social security number and student number have unique values for each student” — two candidate keys, drawn as two separate underlined ovals |
Name and Code both underlined on DEPARTMENT |
“Both name and code have unique values for each department” |
TAKES is M:N with attribute Grade |
A student takes many courses and a course has many students; the grade belongs to the pair |
Separate MAJORS and MINORS relationships |
They are two different facts about a student, both N:1 to DEPARTMENT — one relationship with a “kind” attribute would lose the constraint that a student has at most one major |
STUDENT total in MAJORS, partial in MINORS |
“The department each student majors in is also recorded” (unconditional) versus “if the student has a minor” (conditional) |
COURSE total in OFFERS |
Every course has an “offering department” listed among its attributes in the UoD |
DEPARTMENT partial in OFFERS |
“A department may manage some courses” |
Section D — Analysis & application
Uses the same Olympics UoD and diagram as Section C question 1 (Figure 6).
Question 1 — Correct statements
Select the correct statements: (1) two different venues may share an address, (2) EVENT has a ternary relationship, (3) ATHLETE is an entity while Age is an attribute, (4) an ATHLETE cannot participate at the same EVENT more than once, (5) all VENUEs must host an EVENT.
Enter the correct option numbers in ascending order, comma-separated:
1, 3, 4.
- True.
Addressisn’t underlined onVENUE— onlyIDis — so nothing in the diagram stops two venues sharing an address. - False.
EVENTtakes part inPARTICIPATESandHOSTS, and both are binary. A ternary relationship would be one diamond with three entity types on it. - True by definition:
ATHLETEis drawn as a rectangle,Ageas an oval hanging off it. - True. A relationship instance in the ER model is a pair, so the same (athlete, event) pair can occur at most once in
PARTICIPATES— which is also whyPlacementcan sit on the relationship at all. Recording repeat entries would need a different design. - False —
VENUE’s participation inHOSTSis drawn with a single line, i.e. partial, matching “some venues are backups”.
Question 2 — Total participation, semantically
What does the total participation on both sides of the PARTICIPATES relationship mean semantically? Does this align with the specification in the UoD?
Total participation from ATHLETE and EVENT to the PARTICIPATES relation signifies each athlete in the system participates in at least one event and each event has at least one athlete participating in it. This is in line with the specification presented in the UoD.
The ATHLETE half is stated outright (“each athlete participates in at least one event”). The EVENT half is the one to be able to defend: an Olympic event with no athletes entered is not an event that is being run, so requiring at least one participant per event is the reasonable reading — and under the Section C instruction, exactly the sort of decision you would note as an assumption if challenged on it.
Question 3 — Redraw VENUE with two candidate keys
Redraw the VENUE entity so that a venue can be identified by either its ID or the combination of its name and address.
VENUE gets two underlined ovals, one per candidate key. The second is a composite key — a parent oval named Key with Name and Address hanging off it, so the underline lands on the composite as a whole rather than on either component separately.
Figure 8: VENUE with two candidate keys. Two underlined ovals, not one: ID alone identifies a venue, and so does the composite Name+Address. Underlining Name and Address individually would say something different and wrong — that each is a key on its own.
This redraw contradicts Section D question 1 statement (1): once Name+Address is a key, two venues can no longer share both — though they could still share an address alone.
Section E — Additional resources
Recommended as revision practice for the assessment.
Question 1 — RESTAURANT: DISH entity
The restaurant serves many dishes, each of which has a unique name on the menu. Each dish also has a unique number and associated description, which consists of a brief history of the dish, the number of calories in the dish, and the price. The preparation time for each dish is also recorded, along with its ingredients.
Two candidate keys again — Name (“a unique name on the menu”) and Number (“a unique number”) — each drawn as its own underlined oval. Description is a composite attribute splitting into History, Calories and Price. PreparationTime is simple, and Ingredients is multivalued.
Figure 9: The DISH entity. Only Ingredients carries the double outline of a multivalued attribute; History is an ordinary component of the composite Description.
Compare with the CHEF answer in Section B: same restaurant UoD, same four attribute kinds to distinguish, but here the two keys are two separate simple attributes rather than one composite one, and there is no derived attribute to spot.
Case Study 1: Dirt Road Driving
Group case study applying the ER/EER model in 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices to a real-world brief. See er-diagram-notation and relationship-constraints for reference.
Section A — EER diagram
Dirt Road Driving is a rural rideshare company, gathered from correspondence with the company’s Director of Innovation:
- Users: unique
id, fullName,DOB. A user can register any number of emergency contacts (Nameunique per user,Email,Phone). - Staff: unique
staffID, fullName(first/middle/last),DOB,Phone(s). Split intoAdministration(DeskNumber) andDriver(Licence) — a staff member can be both at once (overlapping). - Vehicles:
VIN,Make,Model. Disjointly splits into4WD(RideHeight,WheelType) and2WD(FrontWheelDrive). - Trip: a user, a vehicle, and a driver (never permanently assigned to one vehicle — recorded per trip), plus a
BookingTimeand start/end timestamps (used to deriveFare); a trip can record multiple stopLocations. After a trip, the user separately rates the driver and the vehicle (integer out of 10, stored asRATESrelationships) — a repeat rating updates the existing one rather than creating a new one.
The two specialisations
STAFF (key StaffID, composite Name = FirstName/MiddleName/ LastName, DOB, multivalued Phone) specialises overlappingly (o) into DRIVER (Licence) and ADMINISTRATION (DeskNumber) — overlapping because the brief says a staff member can be both at once. VEHICLES (key VIN, Make, Model) specialises disjointly (d) into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive) — disjoint because a vehicle is one or the other. Both are drawn below in the notation of er-diagram-notation and specialisation-and-generalisation:
Figure 10: The two specialisations in EER notation: entity types are solid purple rectangles with white ALL-CAPS names, attributes are grey ellipses (key underlined, multivalued double-outlined), and a subclass is joined to the d/o circle by a line carrying the ⊂ arc, whose opening faces the superclass. The brief does not say whether every staff member must be a driver or an administrator, nor whether every vehicle must be a 4WD or a 2WD, so the line from each superclass down to its circle is drawn single (partial specialisation); a double line there would assert total specialisation, and the brief does not settle it.
Note the box shapes. A subclass is an ordinary single-line rectangle — what makes it a subclass is the ⊂ arc on the line into the d/o circle, not the shape of its box. The double-line rectangle means weak entity and nothing else, so in this answer the only double box is EMERGENCYCONTACTS below. Drawing DRIVER, ADMINISTRATION, 4WD or 2WD with a double border would assert the opposite of what they are: a subclass inherits its superclass’s key, whereas a weak entity has no key of its own at all.
The relationships
USER (key ID, Name, DOB) owns the weak entity type EMERGENCYCONTACTS (partial key Name, plus Email and Phone) through the identifying relationship HAS — 1 on the USER side, N on the contact side, and a double line on the contact side because a contact cannot exist without the user it belongs to. Name is only a partial key (unique per user, not globally), which is exactly why the mapping in week4-tutorial-case-study-3-dirt-road-driving gives the resulting relation the composite key (userID, name).
TRIP is a single ternary relationship over USER, DRIVER and VEHICLES carrying BookingTime, StartTime, EndTime, multivalued StopLocation and derived Fare — a driver is not permanently assigned to a vehicle, so the vehicle belongs to the trip rather than to the driver. Ratings are two separate RATES relationships, USER–DRIVER and USER–VEHICLES, each M:N with its own Number (out of 10); because the rating is an attribute of the relationship instance, re-rating updates the one existing tuple instead of adding a second one, which is what the brief asks for.
Figure 11: The relationship half of the answer. EMERGENCYCONTACTS is the one weak entity type — double-line rectangle, identifying (double) HAS diamond, double participation line, dash-underlined partial key Name. DRIVER and VEHICLES appear here as plain boxes; their attributes and their place in the class hierarchy are in Figure 10. Two conventions here go beyond what the brief or the notation settles: (1) the dashed underline is defined for a weak entity’s partial key, so using it on the relationship attribute BookingTime is an extension, recording that BookingTime is what distinguishes two trips with the same user, driver and vehicle — the fact the Week 4 mapping’s composite key relies on; (2) the brief does not fix the ternary cardinality ratio, so all three TRIP edges are marked N for many on every side.
Section B — Critical thinking
Propose a change to the EER diagram that would help the company make a new data-informed decision not currently possible.
This is open-ended — no single correct answer. Consider what a rural rideshare operator might want to analyse that today’s schema can’t answer (e.g. tracking incident reports per trip, recording each vehicle’s service/ maintenance history, or logging cancelled vs. completed trips separately) and how you’d extend the entities/relationships above to capture it.
Reference material
DBMS Fundamentals
What a database is, what a Database Management System is layered into, and what it is (and isn’t) responsible for. Introduced in 2026-02-23-conceptual-database-design-and-the-er-model and drilled in week2-tutorial-applied-class-1-intro-to-dbms-and-basic-erd.
Data, information, and where a database sits
Four definitions the course builds on top of each other:
| Term | Definition |
|---|---|
| Data | Facts and statistics collected together for reference or analysis, typically digitised |
| Information | Data put into meaningful and useful context and communicated to recipients who use it to make decisions |
| System | A set of components that interact to accomplish some purpose |
| Information system | A system that manages information |
An information system is built from five blocks — inputs, processes, outputs, databases and technology. This course is about the database block, which is where the data actually lives.
A database is a collection of related data or known facts that:
- represents some aspect of the real world — the mini-world, or Universe of Discourse (UoD);
- is a logically coherent collection of data with some inherent meaning;
- is designed, built and populated with data for a specific purpose, for an intended group of users and some preconceived applications;
- can be of any size and complexity;
- may be maintained manually or computerised.
The third point is the one that does the work later: a database is designed for a purpose, which is why design begins by fixing the UoD rather than by listing tables. See conceptual-database-design.
Data management — capturing, storing, retrieving, analysing, presenting and interpreting (large amounts of) data — is the skill the whole course is in service of. Three application sectors depend on it:
| Sector | Examples |
|---|---|
| Commerce | Cash management, pricing, employee records, purchasing, reordering, trend analysis |
| Government | Law enforcement, election commission, taxation, legal systems, transport and utilities, city councils |
| Science and engineering | Health and medicine, space exploration, geography (GIS), architecture, military and defence, telecommunications |
Banking is an example of an application area where data plays a central role.
What a DBMS is
A database management system (DBMS) is a software system that facilitates the processes of defining, constructing, manipulating and sharing databases among various users and applications. Those four verbs are the standard summary answer to “what is a DBMS”:
| Process | What it covers |
|---|---|
| Defining | Specifying the types, structures and constraints for the data — sql-ddl |
| Constructing | Storing the data on some storage medium controlled by the DBMS |
| Manipulating | Querying to retrieve specific data, updating to reflect changes in the mini-world, and generating reports — sql-select-statement, sql-dml |
| Sharing | Allowing multiple users and programs to access the database simultaneously |
The four components of a database system
The DBMS is only one piece. A database system has four:
| Component | What it is |
|---|---|
| The stored database | The collection of related information itself |
| The DBMS | The software that defines, constructs and manipulates the database |
| The applications | The programs, in specific languages, that manipulate the database |
| The users | People who use the system, either through the DBMS interface or through application programs |
Figure 12: The database system. Users reach the stored database only through one of two front doors — an application program or an interactive query — and both go through the DBMS. Nothing touches the stored database directly.
Users are further split into database administrators, database designers, end users and application programmers. They reach the database through different interfaces by design: query languages for casual end users, programming-language interfaces for application programmers, and forms and commands for parametric users. GUIs, web-enabled interfaces, natural-language interfaces and speech I/O all sit on top of those. A parametric user is someone who runs the same canned transaction over and over.
Getting from the world to a stored database
Conceptual database design is two steps. Keep them separate before any diagram gets drawn:
- Identify the Universe of Discourse. The database will not model everything in the world, only some mini-world — see conceptual-database-design.
- Convert the UoD to a data model that a database can capture.
One example previews the whole arc of the course. An ER diagram has EMPLOYEE (attributes ID, Name, Salary) joined N:1 to DEPARTMENT (attributes Number, Name) through a WORKS FOR relationship. It maps to this relation:
Employee
| ID | Name | Salary | Department |
|---|---|---|---|
| 175 | Paris Lane | 60,000 | 2 |
| 467 | Anna Lee | 70,000 | 1 |
| 1023 | Ben Cho | 70,000 | 4 |
That one mapping is Modules 1 and 2 of this course: the conceptual model on top (entity-types-and-attributes, relationship-types) and the relational model beneath it (relational-model), joined by the mapping rules in er-to-relational-mapping. The WORKS FOR relationship has become the Department column of Employee, not a table of its own. Recognising that column as a foreign key is Module 2’s job (foreign-keys), and the reason a 1:N relationship collapses into a column is in er-to-relational-mapping.
Redundancy: the problem a designed database solves
Redundancy occurs when one fact is stored in more than one place. Take employee records split across two relations:
| ID | Name | Level |
|---|---|---|
| 1 | Paris | Developer |
| 2 | Anna | Manager |
| 3 | Ben | Manager |
| ID | Name | Salary |
|---|---|---|
| 1 | Paris | 60,000 |
| 2 | Anna | 70,000 |
| 3 | Ben | 70,000 |
Two separate facts are stored redundantly here. The ID/name pair is stored twice, once per relation. And assuming an employee’s salary corresponds directly to the level they hold, the fact “Manager earns 70,000” is stored once per manager rather than once — which is why Anna and Ben both carry 70,000 in their own row. Three costs follow:
- duplication of effort,
- wastage of storage space, and
- inconsistent data — the copies drift apart.
The design goal is therefore to minimise redundancy, not merely to store everything somewhere. The systematic machinery for doing that is functional-dependencies and normal-forms; the concrete failure modes are catalogued in update-anomalies. The other thing a database buys you over a pile of files is the ability to represent complex relationships among the data, which is what er-to-relational-mapping turns an ER diagram into.
Typical functions of a DBMS
| Function | What it means | Where it lands in this course |
|---|---|---|
| Data integrity maintenance | Defining and enforcing integrity constraints — restrictions placed on the data, based on the semantics (meaning) of the data | integrity-constraints |
| Query processing | A language simple enough to run a query and get data back in seconds, yet powerful enough to manage millions of tuples without crashing | sql-select-statement |
| Security management | Measures for securing the database against a variety of threats | database-access-control, sql-injection |
| Concurrency control | Control protocols for concurrent access to data files that guarantee serialisability | Deferred to later INFS courses |
| Backup and recovery | Recovering from hardware and software failures via the backup and recovery sub-system | Deferred to later INFS courses |
Integrity constraints are semantic — they come from what the data means, not from how it is stored. Examples: every employee must have a unique code; employee codes must be 4-digit integers; an employee must work in an existing department; an employee cannot work in multiple departments; an employee’s salary should not exceed their supervisor’s salary. Each of those is a constraint the DBMS can be told to police.
Figure 13: What the two boxes in Figure 12 decompose into, and which module of the course covers each piece: the functions of the DBMS, and what goes into designing and modelling the stored database. Everything greyed as future INFS courses is deferred to those courses.
The recovery example is the one worth remembering. A program transferring $200 from account A to account B is interrupted by a system failure partway through, so the deposit is never recorded in B. The DBMS restores the database to the state prior to the update and restarts the update program, rather than leaving the money in neither account. Concurrency control solves the neighbouring problem: thousands of people editing the same file while each assumes they are the only person using it. Serialisability is the guarantee the control protocols provide: whatever interleaving actually occurred, the result matches some order of running those accesses one at a time.
What a DBMS does — and doesn’t
The dividing line is a single rule: a DBMS enforces the design it is handed, it does not choose the design.
Enforcing integrity constraints, providing secure access, handling concurrent access, and backing up and recovering the database are all DBMS functions. Designing a database is not — and neither is normalising the relational schema (normal-forms) or recommending changes to the database design. Those are design-time decisions made by a human database designer, and no amount of running the database will make them happen by itself. In a list of candidate “DBMS functions”, the design-flavoured option is the odd one out every time.
The three-schema architecture
A DBMS describes the same data at three separate levels, so that each one can change without disturbing the others:
| Level | Describes |
|---|---|
| External level | Provides access to particular parts of the database to particular users, as one or more external views |
| Conceptual level | Describes the structure of the whole database for a community of users, as the conceptual schema |
| Internal level | Describes the physical storage structure of the database, as the internal schema |
The levels are joined by two named mappings: the external/conceptual mapping from each external view down to the conceptual schema, and the conceptual/internal mapping from the conceptual schema down to the internal schema and the stored database beneath it. The mappings are what make data independence possible — a change at one level is absorbed by re-writing the mapping rather than by rewriting the level above.
Figure 14: The three-schema architecture, with the two data-independence properties bracketed onto the level pairs they insulate. The colours — green external views, black/yellow conceptual schema, navy internal schema, gold cylinders — are the textbook’s, not INFS1200 notation; the course’s own ER house style is purple/magenta. Each independence property spans exactly one mapping: logical data independence rides the external/conceptual mapping, physical data independence rides the conceptual/internal one.
The external level is per-audience: a user or application sees the slice of the database relevant to it, not the whole thing, and SQL’s views (see sql-views) are the concrete mechanism for carving out that slice. The conceptual level is the one an ER diagram maps onto (see conceptual-database-design and relational-model), and the internal level — file organisation, hashing, indexing — is explicitly out of scope for this course.
Data independence
Data independence is the ability to change the schema at one level of a database system without having to change the schema at the next higher level. The three-schema architecture exists to deliver it in two flavours:
| Kind | What it buys you |
|---|---|
| Logical data independence | The ability to change the conceptual schema without changing the external views or the applications built on them |
| Physical data independence | The ability to modify the physical schema without changing the conceptual schema |
The course treats conceptual schema and logical schema as two names for the same middle level — physical data independence is stated interchangeably as “modify the physical schema without making changes to the logical schema”. So the two independence properties bracket that one level: logical data independence insulates the layer above it, physical data independence insulates it from the layer below.
Two claims that look right and aren’t: logical data independence is not about moving where data is physically stored (that is physical data independence), and the external level provides access to parts of the database, not the ability to change the schema of its relations. Both are distractors in multiple-choice questions. The correct statement is that physical data independence lets you modify the physical schema without changing the logical schema. The same matching is drilled in week2-tutorial-applied-class-1-intro-to-dbms-and-basic-erd.
Reading
Chapters 1 and 2 of Elmasri & Navathe, Fundamentals of Database Systems (the course textbook, available online via the UQ Library). The module that follows this one is Module 1, conceptual data modelling and ER diagrams — 2026-02-23-conceptual-database-design-and-the-er-model.
Entity Types and Attributes
The two smallest building blocks of an ER diagram: the kinds of thing a database records, and the facts it records about them. Introduced in 2026-02-23-conceptual-database-design-and-the-er-model and practised in week2-tutorial-applied-class-1-intro-to-dbms-and-basic-erd; see er-diagram-notation for the symbols and the naming style guide.
Entity, entity type, entity set
Three words that are easy to blur together, and are worth keeping apart:
| Term | Meaning |
|---|---|
| Entity | A physical or conceptual object with data associated with it — one particular student, one particular chef |
| Entity type | The format for the data that needs to be recorded to represent a particular entity — described by its name and its attributes. Drawn as a rectangle with attribute ovals attached |
| Entity set | The collection of all entities of one entity type in the database at any point in time |
The entity type is the schema-level thing (it doesn’t change as data comes and goes); the entity set is the instance-level thing (it changes with every insert and delete). The entity set is what becomes a table once the diagram is mapped — see er-to-relational-mapping.
Which attributes an entity type carries is decided by the UoD, not by the real-world object: an R&D organisation records an employee’s Degree and Field of Study where a retail organisation records Sales Experience instead. See conceptual-database-design.
Kinds of attribute
Attributes are classified along several independent axes — an attribute can be composite and multivalued, key and composite, and so on:
| Kind | Meaning | Example |
|---|---|---|
| Simple | Can’t be meaningfully split further | Email, DoB |
| Composite | Divides into smaller parts which are simple attributes with independent meaning | Name → Fname, Mname, Lname; Address → Number, Sname, Suburb, Pcode |
| Single-valued | Holds at most one value per entity | DoB |
| Multivalued | Can hold more than one value per entity (double-lined oval) | Degree — one person can hold several; Specialisations, Ingredients, Locations |
| Stored | Physically recorded | BirthDate |
| Derived | Computed from another stored attribute (dashed oval) | Age from BirthDate; Number_of_employees on a department |
| Key | Value is unique across the whole entity set (name underlined) | StudentID, Ssn |
A single EMPLOYEE entity type can carry every kind at once:
Figure 15: Every kind of attribute on one EMPLOYEE entity type. ID and SSN are two independent key attributes (solid underline). Name is composite — it fans out into Fname/Mname/Lname, and the parent oval itself is unmarked. Degree is multivalued (double outline): one person can hold several degrees. Age is derived from BirthDate, so it gets a dashed outline and a dashed connecting line, and it is never stored.
The single/multivalued split is stated over simple attributes — a composite attribute’s multiplicity is really a property of its components. The distinction between composite and multivalued is the one that catches people out: Name splitting into first/middle/last is composite (one name made of parts), whereas Phone on a student is multivalued (several independent phone numbers). Both attach several ovals to the diagram, but they mean different things and map differently.
Key attributes and candidate keys
Every entity type has at least one key constraint (equivalently, a uniqueness constraint), carried by a key attribute whose value is distinct for each individual entity in the entity set. Two properties matter:
- Multiple keys are possible. An entity type can have several independent candidate keys, each underlined in its own oval — the
EMPLOYEEabove carries bothIDandSSN. Elsewhere in the course: aDISHidentified by either itsNameor itsNumber, aVENUEby either itsIDor the combination ofNameandAddress, aVACCINEby either itsIDorBrand+Name. Any claim that an entity is “only identifiable by X” is false as soon as a second key oval is on the diagram. - A key must hold for every possible extension of the entity type. It isn’t enough that the current data happens to have no duplicates; the key must be a rule about every state the entity set could ever reach.
A composite key is a combination of simple attributes that together must be unique — a car’s Registration = State + Number, alongside a separate simple key VehicleID. On the diagram this is drawn as one underlined parent oval that splits into its component ovals, which is how you can tell “these two attributes are jointly a key” apart from “these are two separate keys”:
Figure 16: A CAR entity type with two candidate keys side by side: the composite Registration (underlined parent oval, splitting into State and Number — neither of which is unique alone) and the simple VehicleID. Year, Model and Make are ordinary attributes. Which of the two candidate keys is chosen as the primary key is deferred to the mapping stage, and changes the resulting schema — see er-to-relational-mapping.
The single exception to “every entity type has a key” is the weak entity, which has no key attribute of its own and is identified by its owner’s key plus its own partial key — see relationship-constraints. See also relational-keys for the same idea stated over relations rather than entity types.
Value sets and nulls
An attribute’s value set specifies the set of values that may be assigned to that attribute of an entity — employeeAge: integers between 21 and 65; vehicleRegistrationNum: a string of three alphabetic characters followed by three integers. Value sets are never displayed on the ER diagram itself — they resurface later as the domains of the relational-model, enforced as the domain constraint among the integrity-constraints.
A particular entity may not have an applicable value for an attribute, and a null value is used to represent that. Three reasons a null appears are worth keeping apart, because they are different facts about the world:
| Reason | Example |
|---|---|
| Not applicable | tertiaryDegree for a person with no university education |
| Not known whether it exists | homePhone |
| Missing — exists, but not recorded | height |
Nulls are the reason an attribute being “optional” is a real modelling decision rather than an afterthought; see sql-nulls for how they behave once queried.
Why the attribute kind matters later
The classification isn’t decorative — each kind maps differently:
- A multivalued attribute becomes its own relation, keyed on the owner’s key plus the value itself:
Staff’s multivaluedPhonebecomesStaffPhone [id, phone]. It has to — a relational column holds one atomic value (relational-model). Leavingphoneout of that key would cap each staff member at one phone number, defeating the point of making it multivalued in the first place. - A derived attribute is computed on demand, so it gets no column of its own: a trip’s
Fareis derived from its start and end times and simply doesn’t appear in the mappedTriprelation. - A composite attribute is flattened into its simple components (
Name→firstName,middleName,lastName).
All three are worked through in week4-tutorial-case-study-3-dirt-road-driving; the mapping rules themselves live in er-to-relational-mapping.
ER Design Choices
When modelling complex organisations, a database designer faces design choices: two people modelling the same Universe of Discourse will not draw the same diagram, and that isn’t necessarily a sign either of them is wrong. Introduced in 2026-03-02-weak-entities-the-eer-model-and-design-choices.
Equivalent vs inequivalent choices
- Equivalent choices — an aspect of the UoD can be represented in an ER diagram by two different methods which are both equivalent, i.e. the resulting database will be the same. Genuinely a matter of taste; pick either.
- Inequivalent choices — the UoD is slightly ambiguous, and an aspect of it can be mapped two different ways which both meet the specification of the UoD but lead to different limitations/constraints being enforced by the database. Here the choice has consequences, and the right move is to pick one and note the assumption under the diagram.
That second case is the reason a diagram is rarely a complete answer on its own. The medical-clinic model in week3-tutorial-applied-class-2-entity-relationship-diagrams carries the explicit assumptions “every patient in the DB has made an appointment” and “every clinic has at least one doctor” — neither is stated in the brief, both change the participation constraints, so both get written down rather than smuggled into the drawing.
The four common choice points
| Choice | The question | Worked example |
|---|---|---|
| Attribute vs (weak) entity type | Is this a fact about something, or a thing in its own right? | An interview’s Details (Department, Date) as a key attribute of the APPLIES relationship, vs. a separate weak INTERVIEW entity |
| Attribute vs subclass | Is this a value, or a kind? | EMPLOYEE.Type as a plain attribute, vs. splitting into SECRETARY / ENGINEER subclasses |
| Subclass relationship vs superclass relationship | Does this relationship apply to everyone, or only to one kind? | Giving WORKSON (to PROJECT) to the EMPLOYEE superclass, vs. only to ENGINEER |
| Binary relationships vs n-ary relationships | Does the association hold between pairs, or genuinely among all three at once? | SUPPLIER / PROJECT / PART as three binaries (SUPPLIES, CANSUPPLY, USES) vs. one ternary SUPPLIES carrying a quantity |
Each row is a real fork, not a stylistic one:
- Attribute vs entity type. Pulling something out into its own entity type gives it identity — it can then be referred to, counted, and given relationships of its own. The cinema model in the same applied class makes exactly this call about a
VIEWING: it can be read as a weak entity owned byCUSTOMER+MOVIEwithTimestampas its partial key, or as an M:N relationship carryingTimestampas a key attribute. Both fit the brief; they differ in whether a viewing is a thing or a link. (interpreting-er-diagrams shows the reading side of the same call: without a distinguishing attribute, a plain M:N pair can only be recorded once.) - Attribute vs subclass. A subclass earns its place when the subtypes have different attributes or relationships —
SECRETARYrecording aTypingSpeedthat means nothing for an engineer. If the only difference is the label itself, a plain attribute says the same thing with less diagram. Applied class 2 gives the efficiency half of the argument explicitly: subclasses let a data attribute be stored for some entity instances rather than all of them, and so reduce the number of null/empty values — not everySHOWneeds aRating, onlyENTERTAINMENTshows do. See specialisation-and-generalisation. - Binary vs n-ary. Three binary relationships and one ternary relationship are not interchangeable: the binaries record that a supplier can supply a part and that a project uses a part, while the ternary records the specific fact that this supplier supplied this part to this project. Splitting a genuinely three-way fact into pairs loses information; forcing a ternary where only pairwise facts exist invents it. See relationship-types.
- Subclass vs superclass relationship. Attaching a relationship higher up the hierarchy widens what the schema will accept. That looks harmless on a diagram and is not harmless afterwards — see below.
Worked example: attribute vs weak entity type
A candidate applies to a company, and each application involves an interview with a department on a date. Both diagrams below satisfy that description. The world does not choose between them.
Figure 17: Attribute vs. (weak) entity type: two models of the same description. Left: the interview details hang off the APPLIES relationship as a composite key attribute Details (solid underline) made of Department and Date. Right: the interview becomes a thing — a weak entity owned by both CANDIDATE and COMPANY, so APPLIES becomes an identifying relationship (double diamond, double line into the weak entity) and Details demotes from key attribute to composite partial key (dotted underline). Nothing else on the diagram moves.
The two are alternatives, and the choice between them looks cosmetic. It stops looking cosmetic the moment the UoD says an interview results in a job offer: an attribute cannot be the endpoint of a relationship, so only the weak entity can carry a RESULTSIN link to a JOBOFFER entity. That extended diagram is drawn in 2026-03-02-weak-entities-the-eer-model-and-design-choices.
Worked example: binary vs n-ary relationships
Suppliers, projects and parts. Modelled as three binary relationships — a supplier supplies a project, a supplier can supply a part, a project uses a part:
flowchart TD
SUPPLIER[SUPPLIER] ---|"M"| SUPPLIES{SUPPLIES}
SUPPLIES ---|"P"| PROJECT[PROJECT]
SUPPLIER ---|"M"| CANSUPPLY{CANSUPPLY}
CANSUPPLY ---|"N"| PART[PART]
PROJECT ---|"P"| USES{USES}
USES ---|"N"| PART
sName(["sName (key)"]) --- SUPPLIER
projName(["projName (key)"]) --- PROJECT
partNo(["partNo (key)"]) --- PART
Modelled as one ternary relationship, with all three lines running into a single diamond that can then carry a quantity:
flowchart TD
SUPPLIER[SUPPLIER] ---|"M"| SUPPLIES{SUPPLIES}
SUPPLIES ---|"P"| PROJECT[PROJECT]
SUPPLIES ---|"N"| PART[PART]
SUPPLIES --- quantity(["quantity"])
sName(["sName (key)"]) --- SUPPLIER
projName(["projName (key)"]) --- PROJECT
partNo(["partNo (key)"]) --- PART
The quantity is the tell. It is a fact about a supplier-project-part triple, so there is nowhere to put it in the binary model — hanging it on SUPPLIES there would record how much a supplier sent a project without saying of what. This is the inequivalent case: the two diagrams do not produce the same database.
Worked example: subclass vs superclass relationship
EMPLOYEE specialises disjointly into SECRETARY and ENGINEER, and somebody works on projects. Which box does WORKSON attach to?
Figure 18: Subclass relationship vs. superclass relationship, reduced to the fragment where the two options differ. Option 1 attaches WORKSON to the subclass ENGINEER; Option 2 attaches it to the superclass EMPLOYEE. The rest of the company diagram (MANAGES, WORKSFOR, CONTROLS and DEPARTMENT) is the same under both options and is left out so the one difference is visible.
This is the clearest inequivalent choice of the four. Option 1 says only engineers work on projects — secretaries cannot be recorded on one at all. Option 2 says anyone may. Both satisfy a brief that only ever mentions engineers working on projects, but they enforce different things: Option 2 will happily accept a secretary on a project, and Option 1 will refuse an engineer’s promotion out of the subclass without also dropping their project assignments. Attaching a relationship higher up the hierarchy always widens what the schema will accept.
Why the choice matters after the diagram
A conceptual choice becomes a constraint the DBMS can or cannot enforce once the diagram is mapped. The clearest example is the third mistake in week4-tutorial-case-study-3-dirt-road-driving: mapping a trip’s driver as a reference to Staff rather than to Driver lets an admin-only staff member be recorded as a trip’s driver, because the schema no longer knows that a driver is a driver. The diagram-level decision about which class a relationship attaches to is the same decision as the schema-level decision about which relation the foreign key points at.
This is also why an assumption noted under a diagram is worth marks and worth memory: it’s the record of a constraint that the notation alone couldn’t distinguish. Once the schema exists, refining it further is a different exercise with different tools — see functional-dependencies and normal-forms for redundancy-driven refinement, and conceptual-database-design for where in the pipeline each one sits.
ER Diagram Notation
Reference legend for the symbols used across every ER/EER diagram in this course — introduced across 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices.
The notation below is the mandatory standard: it is what the lectures’ own notation guide specifies, and it is what assignments and the exam are marked against. (The separately published INFS1200/7900 EER & ER Notation Guide handout only standardises naming — see the style guide at the bottom of this note — and explicitly defers the symbols themselves to the lecture material.)
Core symbols
| Symbol | Meaning |
|---|---|
| Rectangle | Entity type |
| Double-line rectangle | Weak entity type (no key attribute of its own) |
| Oval | Attribute |
| Oval, name underlined | Key attribute |
| Oval, name underlined with a dotted line | Partial key attribute (weak entity’s own distinguishing attribute) |
| Double-line oval | Multivalued attribute (can hold more than one value) |
| Dashed oval, attached by a dashed line | Derived attribute (computed from another stored attribute, e.g. Age from BirthDate) |
| Diamond | Relationship type |
| Double-line diamond | Identifying relationship type (connects a weak entity to its owner entity) |
- Composite attribute: an attribute with its own sub-attributes attached (e.g.
Namesplitting intoFirstName/MiddleName/LastName) — the parent attribute oval isn’t underlined/dashed/doubled itself unless it’s also a key/derived/multivalued attribute. - Value sets (the domain of legal values for an attribute, e.g. “integers 21-65”) are never shown on the diagram itself.
Relationship constraints
| Symbol | Meaning |
|---|---|
1, N, M on the lines into a diamond |
Cardinality ratio — E1 —1— R —N— E2 reads as ratio 1:N for E1:E2 in R |
| Double line between an entity type and a diamond | Total participation of that entity type in that relationship |
| Single line | Partial participation |
See relationship-constraints for what each ratio and each participation constraint actually asserts.
Subclasses / superclasses (EER)
A subclass is drawn as an ordinary rectangle. What makes it a subclass is the ⊂ symbol on the line joining it to its superclass, not the shape of its box — a subclass box is a plain single-line rectangle, never the double-line rectangle that means weak entity. The two are entirely different ideas (a subclass inherits its superclass’s key; a weak entity has no key of its own at all) and the notation keeps them apart.
| Symbol | Meaning |
|---|---|
⊂ on the line between two entity types |
The entity type on the closed (narrow) side of the ⊂ is a subclass of the one on the open (wide) side — read it as the set-inclusion symbol, subclass ⊂ superclass |
A circle joining several subclass lines to one superclass, each line carrying its own ⊂ opening towards the circle |
Several entity types are all subclasses of the same superclass |
d inside that circle |
Disjoint specialisation — an entity instance can belong to at most one of the subclasses |
o inside that circle |
Overlapping specialisation — an entity instance can belong to more than one subclass at once |
| Single line from superclass to the subclass circle | Partial specialisation — not every superclass instance needs to belong to a subclass |
| Double line from superclass to the subclass circle | Total specialisation — every superclass instance must belong to at least one subclass |
Naming conventions (INFS1200/7900 style guide)
Course-specific naming standard for entities/relationships/attributes on ER and EER diagrams (distinct from — and in addition to — the mandatory notation above):
- Entity names: capitalised, no spaces, ideally one word (e.g.
STUDENT,POLICEOFFICER— notPoliceOfficer). - Relationship names: capitalised, no spaces, ideally one word, preferably a verb (e.g.
CREATES,ACTSIN— notLeadActor). - Attribute names: UpperCamelCase — first letter of each word capitalised, no spaces, acronym letters stay capitalised (e.g.
ComputerIP,DateOfBirth— notDate of Birth).
How diagrams are drawn in these notes
Diagrams in this course’s notes are drawn as Mermaid flowchart graphs (renders in both PDF and HTML), using shapes chosen to match the Chen notation above as closely as Mermaid’s flowchart shapes allow:
| Mermaid shape | Used for |
|---|---|
[Entity] (rectangle) |
Entity type — and subclasses, which are ordinary rectangles |
[[WeakEntity]] (subroutine, double vertical bars) |
Weak entity type, and nothing else |
{Relationship} (diamond/rhombus) |
Relationship type |
([Attribute]) (stadium) |
Attribute |
((d)) / ((o)) (small circle) |
Disjoint/overlapping specialisation marker |
The [[X]] shape is reserved for weak entity types. Drawing an EER subclass with it is a notation error, not a stylistic one: it renders the double-line box that the official notation uses for a weak entity, and so asserts the opposite of what a subclass is. Subclasses are plain [X] rectangles; their subclass-ness is carried by the edge into the ((d)) or ((o)) circle, exactly as the ⊂ carries it in the hand-drawn notation.
Since Mermaid can’t reliably underline/dash node text across renderers, attribute qualifiers are written as a plain-text suffix instead: (key), (partial key), (derived), (multi). Composite attributes are drawn as a parent attribute node connected to its component attribute nodes. Cardinality is a text label on the edge ("1", "N", "M"); total participation/identifying relationships use a thick edge (===/==>), partial participation uses a thin edge (---/-->).
Any diagram with more than ~6-8 nodes needs an explicit %%| fig-width: cell option (in inches) or it runs off the right edge of the PDF page - mermaid sizes itself from the diagram’s own SVG bounding box, and there’s no project-wide default that reaches it (a document/project fig-width only applies to executed-code-cell figures, e.g. matplotlib). 5.5 fits this vault’s PDF page width (scrartcl, DIV=11, letter) comfortably:
```{mermaid}
%%| fig-width: 5.5
flowchart TD
...
```ER to Relational Mapping
The (7+1)-step algorithm that converts a conceptual ER/EER diagram into a logical relational schema — input: an ER model; output: relations with primary and foreign key constraints. Introduced in 2026-03-16-er-to-relational-mapping and practised in week5-tutorial-applied-class-4-entity-relationship-mapping and week4-tutorial-case-study-3-dirt-road-driving. See relational-mapping-notation for the notation and naming style used throughout, er-diagram-notation for the diagram being mapped from, and er-mapping-pitfalls for the mistakes to check for afterwards.
Why map at all
The ER model is used for conceptual design — the user’s perspective, good for talking to stakeholders about entities and relationships. The relational model is what commercial DBMSs actually implement — the storage perspective. Mapping is the bridge: every construct that the ER model can draw has to be re-expressed using only relations, primary keys and foreign-keys, because those are the only tools the relational model has (see relational-model).
The steps at a glance
| # | Construct | Produces |
|---|---|---|
| 1 | Strong entity type | A new relation |
| 2 | Weak entity type | A new relation, keyed on owner’s key + partial key |
| 3 | Binary 1:1 relationship | A foreign key on one side |
| 4 | Binary 1:N relationship | A foreign key on the N side |
| 5 | Binary M:N relationship | A new relation |
| 6 | Multivalued attribute | A new relation |
| 7 | N-ary relationship | A new relation — except 1:1:1 and N:1:1, which take foreign keys instead |
| +1 | Subclass (EER) | A new relation per subclass |
Only steps 1, 2, 5, 6, 7 and +1 create relations; steps 3 and 4 only add foreign keys to relations that already exist, and step 7 does the same in its two excluded cases. Working in order matters, because every later step needs the primary keys that earlier steps established.
The running example below is the Company database: EMPLOYEE (key Ssn, composite Name → Fname/Mit/Lname, Sex, Salary, Address, DOB), DEPARTMENT (keys Dnumber/Dname, derived NumberOfEmployees, multivalued Locations), PROJECT (keys Pno/Pname, Plocation), and the weak entity DEPENDENT (partial key DepName), with relationships WORKSFOR (EMPLOYEE N:1 DEPARTMENT), MANAGES (EMPLOYEE 1:1 DEPARTMENT, attribute StartDate), CONTROLS (DEPARTMENT 1:N PROJECT), WORKSON (EMPLOYEE M:N PROJECT, attribute Hours), SUPERVISION (EMPLOYEE recursive 1:N), DEPENDENTSOF (identifying), and EMPLOYEE disjointly specialising into SECRETARY/ENGINEER.
Step 1 — Strong entity types
For each strong (non-weak) entity type: create a relation, choose one of its keys as the primary key, and include all simple attributes.
Employee [ssn, fName, mIt, lName, dob, address, sex, salary]
Department [dNumber, dName]
Project [pNo, pName, pLocation]
Three attribute kinds are deliberately not copied across as columns:
- Composite attributes contribute their simple components instead —
Namenever appears,fName/mIt/lNamedo. - Derived attributes are never stored at all —
NumberOfEmployeesis computed on demand, so storing it would only create an opportunity for it to go stale. - Multivalued attributes can’t be stored in an atomic column and are deferred to step 6 —
Locationsis missing fromDepartmentfor now.
Where an entity has several keys (DEPARTMENT has both Dnumber and Dname), one becomes the primary key and the others are simply ordinary attributes in the relational schema — see relational-keys.
Step 2 — Weak entity types
For each weak entity type: create a relation whose primary key is the owner’s primary key plus the weak entity’s partial key; add a foreign key from the inherited part back to the owner; include all simple attributes.
Dependent [ssn, depName, sex, dob, relationship]
Dependent.ssn references Employee.ssn
Dependent’s full key is (ssn, depName): ssn is Employee’s key inherited through the identifying relationship, depName is the partial key (see relationship-constraints). Note what does not happen — the identifying relationship DEPENDENTSOF gets no relation of its own; it is entirely represented by the inherited key.
If a weak entity has more than one owner, the rule generalises rather than changes: include as foreign keys the primary key attributes of every owner entity type, and make the primary key the combination of all of them plus the partial key. For a weak W (partial key a1) owned by both E1 (key b1) and E2 (key c1):
E1 [b1]
E2 [c1]
W [a1, b1, c1]
W.b1 references E1.b1
W.c1 references E2.c1
All three columns are underlined as one key; both inherited keys are foreign keys in their own right.
When the owner’s own key is composite, the foreign key is composite too. In the Bank example, BRANCH is a weak entity owned by BANK (branchNo is only unique within a bank):
Bank [code, name, hoAddr]
Branch [bankCode, branchNo, addr]
Branch.bankCode references Bank.code
Step 3 — Binary 1:1 relationships
For each binary 1:1 relationship: pick one participating entity type, add a foreign key there referencing the other’s primary key, and put the relationship’s own simple attributes on that same chosen side.
Preference is given to entity types with total participation (relationship-constraints) — it is part of the rule, not a stylistic tip. In the generic case, with T the totally-participating side (double line) and c1 the relationship’s own attribute:
S [a1, a2]
T [b1, b2, a1, c1]
T.a1 references S.a1
If every department must have a manager but not every employee manages, then putting the foreign key on Department gives a value in every row, whereas putting it on Employee would leave it null for almost everyone (sql-nulls). MANAGES is drawn with the double line on the DEPARTMENT side, so DEPARTMENT is the chosen side:
Department [dNumber, dName, mgrSSN, mgrStartDate]
Department.mgrSSN references Employee.ssn
mgrStartDate is MANAGES’s own attribute, and travels with the foreign key. The foreign key is a plain attribute here, not part of Department’s key.
Both landed columns are renamed on arrival — Employee.ssn becomes mgrSSN, StartDate becomes mgrStartDate — and the same happens in step 4 (Dnumber → dNumber in Employee, Ssn → superSSN for SUPERVISION). Renaming an inherited attribute to say what role it plays in its new relation is expected, and is the standard fix when the imported name would collide with one already there.
Step 4 — Binary 1:N relationships
For each binary 1:N relationship that isn’t an identifying relationship: find the entity type on the N side, add a foreign key there referencing the primary key of the entity type on the 1 side, and put the relationship’s own simple attributes on the N side too.
The direction is forced, not chosen: one row on the N side relates to exactly one row on the 1 side, so a single foreign key column can hold it. The other direction would need many values in one cell.
Employee [ssn, fName, mIt, lName, dob, address, sex, salary, dNumber, superSSN]
Employee.dNumber references Department.dNumber
Employee.superSSN references Employee.ssn
Project [pNo, pName, pLocation, dNumber]
Project.dNumber references Department.dNumber
SUPERVISION is recursive, and is handled exactly like any other 1:N relationship — the N-side role (supervisee) carries a foreign key back to the 1-side role (supervisor) in the same relation.
Step 5 — Binary M:N relationships
For each binary M:N relationship: create a new relation; give it foreign keys to both participants’ primary keys; the combination of those foreign keys is its primary key; add the relationship’s own simple attributes.
WorksOn [ssn, pNo, hours]
WorksOn.ssn references Employee.ssn
WorksOn.pNo references Project.pNo
A new relation is unavoidable here: neither side can hold a single foreign key value, since both sides relate to many rows of the other.
One wrinkle the Company example doesn’t show: the relationship may bring a key attribute of its own, and if it does, that attribute joins the primary key rather than sitting outside it. For an M:N R between S and T carrying an underlined c1 and a plain c2:
S [a1, a2]
T [b1, b2]
R [a1, b1, c1, c2]
R.a1 references S.a1
R.b1 references T.b1
R’s key is (a1, b1, c1) — the pair of foreign keys plus c1. This is how a relationship that can repeat between the same two entities (the same employee working on the same project in several different years, say) gets modelled without a duplicate-key clash.
1:1 and 1:N relationships may also be mapped this way, and it is worth doing when the relationship is sparse — a separate relation stores only the rows that actually participate, instead of leaving a null foreign key in every row that doesn’t. In the standard implementation the foreign key column is mostly nulls, while the M:N implementation holds one short relation of just the participating pairs, and no nulls at all.
Step 6 — Multivalued attributes
For each multivalued attribute: create a new relation containing a foreign key to the owning entity’s primary key plus the attribute itself; the primary key is the combination of the two. If the multivalued attribute is composite, include its simple components instead of the attribute itself.
DeptLocs [dNumber, location]
DeptLocs.dNumber references Department.dNumber
Keying on both columns is what allows one department to have many locations while forbidding the same location twice. If the owning relation’s key is itself composite, the foreign key here is composite — in the cinema example the snack relation carries all four columns of Viewing’s key:
ViewingSnack [name, id, date, time, snack]
ViewingSnack.{name, id, date, time} references Viewing.{name, id, date, time}
Step 7 — N-ary relationships
For each N-ary relationship type — excluding the 1:1:1 and N:1:1 cases, which are handled separately below: create a new relation; give it foreign keys to the primary key of every participating entity type; the foreign keys from the participants with many cardinality form the primary key; the relationship’s own attributes may also contribute to that key; add its remaining simple attributes.
One relation body covers every non-excluded case, and only the underline moves. For a ternary R over E1, E2 and E3, with own attributes d1 and d2:
E1 [a1]
E2 [b1]
E3 [c1]
R [a1, b1, c1, d1, d2]
R.a1 references E1.a1
R.b1 references E2.b1
R.c1 references E3.c1
| Cardinalities | Primary key of R |
|---|---|
N:M:P — every participant many |
All three foreign keys, (a1, b1, c1) |
N:M:1 |
The two many-side foreign keys (a1, b1); c1 is a plain attribute |
N:M:1 with d1 an underlined attribute of R |
(a1, b1, d1) — the relationship’s own key attribute joins them |
Every participant is always represented, whatever its cardinality: a 1-side foreign key is present but outside the key. For a ternary K over H (role N), A (role M) and P (role 1), with own attribute l, that gives
K [b, i, q, l]
K.b references A.b
K.i references H.i
K.q references P.q
with only b and i underlined. The Company database contains no N-ary relationship — WORKSON is binary.
The excluded cases — 1:1:1 and N:1:1
These get no new relation at all. With at most one participant on a many side, there is no combination of foreign keys that would make a key, and the relationship can be absorbed exactly as in steps 3 and 4: one participating relation takes a foreign key to each of the others, plus the relationship’s own attributes.
- N:1:1 — the entity type on the
Nside is the one that takes them, and the choice is forced, since only its rows relate to exactly one row of each other participant:
E1 [a1, b1, c1, d1]
E1.b1 references E2.b1
E1.c1 references E3.c1
E2 [b1]
E3 [c1]
- 1:1:1 — nothing forces the direction, so choose a participant the same way step 3 does, preferring total participation, and put both foreign keys and the relationship’s attributes there. The schema looks identical to the N:1:1 one above; only the reason for picking
E1differs.
The giveaway that a question is testing this exclusion is a ternary diamond with at most one N on its edges: count the many sides before reaching for a new relation, because with fewer than two of them there is nothing to key it on.
Step +1 — Subclasses (EER)
For each subclass (specialisation-and-generalisation): create a relation whose primary key is the superclass’s primary key; add a foreign key from it back to the superclass relation; include the subclass’s own simple attributes. The same rule works for every combination of total/partial and disjoint/overlapping specialisation.
Secretary [ssn, typingSpeed]
Secretary.ssn references Employee.ssn
Engineer [ssn, engineerType]
Engineer.ssn references Employee.ssn
A subclass with no attributes of its own still gets a relation — it is the row’s membership in that subclass that is being recorded.
This step has no fixed position in the sequence. It is done straight after step 1 or 2 (whichever created the superclass), specifically so the subclass relations exist by the time a later relationship step needs to reference one — and relationships drawn to a subclass must reference the subclass relation, not the superclass.
Final schema — Company database
Employee [ssn, fName, mIt, lName, dob, address, sex, salary, dNumber, superSSN]
Employee.dNumber references Department.dNumber
Employee.superSSN references Employee.ssn
Department [dNumber, dName, mgrSSN, mgrStartDate]
Department.mgrSSN references Employee.ssn
Project [pNo, pName, pLocation, dNumber]
Project.dNumber references Department.dNumber
Dependent [ssn, depName, sex, dob, relationship]
Dependent.ssn references Employee.ssn
Secretary [ssn, typingSpeed]
Secretary.ssn references Employee.ssn
Engineer [ssn, engineerType]
Engineer.ssn references Employee.ssn
WorksOn [ssn, pNo, hours]
WorksOn.ssn references Employee.ssn
WorksOn.pNo references Project.pNo
DeptLocs [dNumber, location]
DeptLocs.dNumber references Department.dNumber
Eight relations: the three strong entity types became three, the weak entity one, the two subclasses two, the M:N relationship one, and the multivalued attribute one — while all four of the 1:1 and 1:N relationships (MANAGES, WORKSFOR, CONTROLS, SUPERVISION) became nothing more than foreign key columns on relations that already existed.
Second worked example — Bank database
The other complete run-through is the bank: a BANK (unique Code, Name, HOAddr) has several branches; a BANK-BRANCH has a BranchNo unique only within its bank, so it is weak. One branch has many ACCOUNTs (ACNo, Type, Balance) and many LOANs (LoanNo, Type, Amount), and every account and loan has one or more holders drawn from CUSTOMER (SSN, Name, Addr) — two M:N relationships, AHOLDER and LHOLDER.
Bank [code, name, hoAddr]
Branch [bankCode, branchNo, addr]
Branch.bankCode references Bank.code
Account [acNo, type, balance, bankCode, branchNo]
Account.{bankCode, branchNo} references Branch.{bankCode, branchNo}
Loan [loanNo, type, amount, bankCode, branchNo]
Loan.{bankCode, branchNo} references Branch.{bankCode, branchNo}
Customer [ssn, name, address]
AccountHolder [acNo, ssn]
AccountHolder.acNo references Account.acNo
AccountHolder.ssn references Customer.ssn
LoanHolder [loanNo, ssn]
LoanHolder.loanNo references Loan.loanNo
LoanHolder.ssn references Customer.ssn
Two things are worth reading off this schema. First, a weak entity’s composite key propagates: because Branch is keyed on (bankCode, branchNo), every 1:N foreign key drawn from Branch is composite too. Second, it propagates as a plain column — Account has its own key acNo, so bankCode and branchNo sit outside Account’s primary key, exactly as step 4 requires. AccountHolder and LoanHolder, by contrast, have no key of their own and are keyed on their pair of foreign keys.
The result is a logical schema, ready to be implemented with sql-ddl and checked for redundancy with normal-forms. Whether each inherited foreign key is part of its relation’s primary key — the question that decides most of the answers above — is tabulated in foreign-keys.
Interpreting ER Diagrams
Drawing a diagram and reading one are different skills, and the applied classes test the second at least as hard as the first: given an ER/EER diagram and a plain-English claim about the world, decide whether the diagram actually says it. Practised in the statement-evaluation questions in week2-tutorial-applied-class-1-intro-to-dbms-and-basic-erd and week3-tutorial-applied-class-2-entity-relationship-diagrams, and in the questions in 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices. See er-diagram-notation for what each symbol is.
The checklist
| If the claim is about… | Check | Trap |
|---|---|---|
| How many X per Y | The cardinality ratio, from the far side | Reading 1:N in the wrong direction |
| Whether every X does something | Participation (double vs single line) | Assuming total participation because the relationship exists |
| Uniqueness of an attribute | Whether that attribute is underlined | Treating a non-key attribute as if it were unique |
| “Only identifiable by…” | Whether a second key oval exists | Missing a second candidate key |
| A weak entity’s identity | Owner’s key + partial key | Treating the partial key as a full key |
| An instance being two kinds at once | d vs o on the specialisation circle |
Reading two separate circles as one |
| A relationship’s degree | Count the lines into the diamond | Calling a pair of binaries “ternary” |
| Whether a given relationship set is possible | Every cardinality ratio and every participation constraint, tuple by tuple | Checking the ratios and forgetting the double lines |
Cardinality: read it from the far side
A 1:N relationship constrains both ends, and the interesting constraint is usually the one on the “many” side. HEALTHWORKER —1:N OVERSEEN→ HEALTH means one health worker can oversee many participants, and therefore each participant has at most one overseeing health worker. “A participant can be overseen by several healthcare workers” is incorrect against that diagram — a claim that reads naturally in English and is contradicted by the diagram in the direction most people don’t check. See relationship-constraints.
An M:N relationship with no distinguishing attribute also says something: each pair appears at most once. An athlete/event pair in a plain PARTICIPATES relationship cannot be recorded twice, so “an athlete cannot participate at the same event more than once” is correct. Making repeats possible requires a distinguishing key attribute (a Timestamp, say) or a weak entity — see er-design-choices.
Participation: absence of a double line is a fact
Total participation (double line) says every instance must participate; partial participation (single line) says it need not. Both are claims, and the partial one is easy to skip over:
- The recursive
WITHrelationship onVACCINEis partial on both sides, so “each vaccine must have a second dose or a preceding dose” is incorrect — the relationship allows a two-dose program, it doesn’t require one. OVERSEENhas total participation on theHEALTHside, so “every participant in the HEALTH subclass is overseen by a health worker” is correct.- Some venues are backups and never host an event, which is exactly why
VENUE’s participation inHOSTSis partial — “all venues must host an event” is incorrect.
Keys: what isn’t underlined can repeat
If an attribute isn’t marked as a key, the diagram is asserting nothing about its uniqueness — so two different venues may share an Address when only ID is underlined. Conversely, HOTEL.Address being the key attribute is what makes “two hotels can share an address” false.
Claims of the form “X can only be identified by Y” need the whole attribute list checked, because entity types routinely have more than one candidate key: VACCINE has both ID and the composite Brand + Name, so “a vaccine can only be identified by the combination of its brand and name” is incorrect. See entity-types-and-attributes.
Weak entities: the partial key is not the key
A weak entity’s partial key is unique only within its owner, so any claim that treats it as globally unique is false. The hotel/room question is the canonical case: ROOM’s real key is (HOTEL.Address, ROOM.Number), so neither “no two rooms share a number” nor “no two hotels have rooms with the same number” holds — room numbers repeat freely across hotels, which is the entire reason ROOM is weak. The same logic makes “a BATCH is uniquely identified by only its number” incorrect, and makes an emergency contact named “Mum” registrable by two different users.
The hotel/room question is also a reminder that “none of the above” is a live option: with HOTEL.Address as the key and ROOM.Number as a partial key, every uniqueness claim on offer — two hotels sharing an address, no two rooms sharing a number, no two hotels having rooms with the same number — is false at once.
Checking a candidate relationship set
A second question format hands you a small diagram plus several candidate relationship sets written out as tables of tuples, and asks which one is possible. It is mechanical once you know the two things to check:
| Notation on the diagram | What it forbids in the table |
|---|---|
A 1 on E’s side of R |
An entity of E appearing in more than one row of R |
An N or M on E’s side of R |
Nothing — that side is unconstrained |
| A double line between E and R | An entity of E appearing in no row of R |
Worked instance. Take A —N— R —1— B —N— S ═M═ C, with entity sets {a₁, a₂}, {b₁, b₂} and {c₁, c₂}. The 1 on B’s side of R means each a may appear at most once in R; the double line on C’s side of S means every c must appear somewhere in S; M:N on S constrains nothing else. So:
R = ∅,S = ∅fails —c₁andc₂are missing fromS.R = {(a₁,b₁)},S = {(b₂,c₂)}fails —c₁is still missing.R = {(a₁,b₁), (a₁,b₂)}fails —a₁appears twice, violating the1.R = {(a₁,b₂)},S = {(b₁,c₂), (b₂,c₁), (b₁,c₁)}is possible — noarepeats and bothcs appear.
Note that an empty relationship set is perfectly legal wherever participation is partial; it is only the double line that forces rows to exist. That is the half of the check people skip.
Subclasses: d and o are per circle
An overlapping (o) specialisation permits an instance to be in several subclasses at once, so “a participant can be in both the HEALTH subclass and the AGE subclass” is correct when those two hang off an o circle. A disjoint (d) specialisation forbids it. The per-circle part matters: a superclass with two separate specialisation circles constrains each one independently, and membership in a subclass under one circle says nothing about membership under the other. See specialisation-and-generalisation.
Degree: count the lines
“EVENT has a ternary relationship” is incorrect when EVENT’s relationships are PARTICIPATES (to ATHLETE) and HOSTS (to VENUE) — two separate binary relationships, not one three-way one. An entity type participating in several relationships is not the same as participating in a high-degree relationship. See relationship-types.
Relationship Constraints
Constraints on a relationship type limit the possible combinations of entities that may participate in its relationship set — determined by the Universe of Discourse (UoD), not chosen arbitrarily. There are exactly two kinds, cardinality constraints and participation constraints. Introduced in 2026-02-23-conceptual-database-design-and-the-er-model; see er-diagram-notation for the full symbol legend.
Cardinality ratio
A cardinality ratio for a relationship set specifies the number of relationships in the set that an entity can participate in. The intuition is a customer buying items: one particular customer can purchase many different items, however an individual item — this particular cake — cannot be purchased by several different customers.
| Ratio | Name | Meaning |
|---|---|---|
| 1:1 | One-to-one | Both entities can participate in only one relationship instance |
| N:1 | Many-to-one | One entity can participate in many relationship instances, the other in only one |
| 1:N | One-to-many | The same constraint, viewed from the other side |
| M:N | Many-to-many | Both entities can participate in many different relationship instances |
Three worked readings, all on the same two entity types, are worth memorising as a set because the English is nearly identical each time and only the ratio changes:
| Statement of the UoD | Ratio |
|---|---|
| “Each employee can work on any number of projects, and each project can have any number of employees working on it” | EMPLOYEE —N— WORKSON —M— PROJECT (M:N) |
| “Each department can have any number of employees, but an employee can work for at most one department” | EMPLOYEE —N— WORKSFOR —1— DEPARTMENT (N:1) |
| “Each department can have at most one manager and each employee can manage at most one department” | EMPLOYEE —1— MANAGES —1— DEPARTMENT (1:1) |
Note where the N sits in the second row: on the EMPLOYEE side, because it is employees that are many. Reading the label on the wrong end is the standard error — see interpreting-er-diagrams.
Participation constraint (existence dependency)
The course’s own name for the participation constraint is existence dependency: it indicates whether the existence of an entity depends on its relationship to another entity — i.e. whether every instance of the entity type must participate in at least one relationship instance.
- Total participation (double line in the diagram) — every entity instance must participate. “Every employee must work for a department” makes
EMPLOYEEtotally participate inWORKSFOR, and the phrasing to use is thatEMPLOYEEis existentially dependent onDEPARTMENTvia theWORKSFORrelationship type. - Partial participation (single line) — an entity instance is not required to participate. “Every employee can manage 0 or more departments” makes
EMPLOYEE’s participation inMANAGESpartial.
The two ends are constrained independently, and MANAGES is the example that shows it: EMPLOYEE participates partially (most employees manage nothing) while DEPARTMENT participates totally (every department must have a manager), so DEPARTMENT is existentially dependent on EMPLOYEE via MANAGES and not the other way round.
Why the participation constraint is worth getting right
It is not just documentation: it decides where a foreign key ends up. The Module 2 mapping rule for a binary 1:1 relationship says to pick one of the two participating entity types and give it the foreign key, and that preference is given to entity types with total participation. MANAGES is exactly that case — because DEPARTMENT totally participates, the mapping extends Department with mgrSSN (plus the relationship’s own startDate, renamed mgrStartDate) rather than putting a department reference in Employee, which would be null for almost every employee. The mirror-image answer fails for precisely this reason: given S ═1═ R ─1─ T, putting the foreign key in T is wrong, because S has full participation and is preferred over T. See er-to-relational-mapping for the rule in full.
Weak entities
An entity type with no key attribute of its own is a weak entity — it can be identified uniquely only by considering the primary key of another owner entity together with its own partial key (underlined with a dotted line). The relationship type that relates a weak entity to its owner is the identifying relationship, and the weak entity always has total participation in it: it cannot exist without its owner. Both the weak entity box and the identifying relationship diamond are drawn with double lines.
An example is an EMPLOYEE (key SSN) who INSURES a DEPENDANT (partial key Pname, plus Age), 1:N, with Cost as a descriptive attribute on the identifying relationship. Two different employees can each insure a dependant named John, which is precisely why Pname cannot be a key on its own — the identity of a dependant is (SSN, Pname).
Provinces and cities have the same shape, and it is the compact example to memorise because all three notations — cardinality, participation and partial key — are on it at once:
Figure 19: A weak entity in full notation. CITY is weak (double box) with the partial key name (dash-underlined); IN is the identifying relationship (double diamond); the CITY–IN edge is doubled for CITY’s total participation, and the ratio is 1:N, so one province holds many cities. PROVINCE’s own name is a full key (solid underline). Entity boxes are purple, relationship diamonds magenta and attribute ovals grey.
The two names do different jobs. PROVINCE.name identifies a province outright; CITY.name identifies a city only within a province, so the mapped Cities relation needs a pName column as part of its primary key and as a foreign key to Provinces — the mapping is Cities [cName, pName, mayor] with Cities.pName references Provinces.pName, and the wrong mappings are the ones that drop pName from the key, make cName a foreign key, or turn the identifying relationship IN into a relation of its own. See er-to-relational-mapping for the mapping rule that produces this.
A weak entity can have two (or more) owner entity types, in which case the identifying relationship is n-ary rather than binary. The generic version shows how the constraints are placed in that case — many weak entities per combination of owners, one owner of each kind per weak entity:
Figure 20: A weak entity with two owner entity types. The identifying relationship R is ternary: N on the weak side, 1 towards each owner, with the double edge marking W‘s total participation. W’s identity is its partial key a1 plus both owners’ keys, which is why the mapped relation is W [a1, b1, c1] with b1 and c1 as foreign keys.
INTERVIEW is the worked case: it is owned by both CANDIDATE and COMPANY, so the identifying relationship APPLIES is ternary, and INTERVIEW’s composite partial key Details (Department, Date) only distinguishes interviews within one candidate/company pair. INTERVIEW is a full entity type despite being weak, so it can carry relationships of its own — it RESULTSIN a JOB OFFER. See er-design-choices for the decision that produced it and interpreting-er-diagrams for the claims it makes false.
Specialisation and Generalisation
The Enhanced ER (EER) model’s one big addition to plain ER: the ability to say that one entity type is a kind of another. Introduced in 2026-03-02-weak-entities-the-eer-model-and-design-choices; see er-diagram-notation for the notation.
Classes, superclasses and subclasses
In the EER model an entity type is called a class, and entities in the same class share the same attributes. A class can be a superclass or a subclass of another:
- Attributes of a superclass are inherited by its subclasses, and a subclass can add its own specific attributes and its own specific relationships on top.
- Every entity in a subclass is also a member of its superclass(es). The subclass never holds entities the superclass doesn’t.
The motivating example is a supermarket ITEM (ProductName, Price) versus a FOOD item, which is just an ITEM that additionally records an ExpiryDate. Modelling FOOD as a subclass says exactly that — it is an extension of a regular item, not a separate thing that happens to look similar.
Two directions, one relationship
Specialisation and generalisation describe the same superclass/ subclass structure discovered from opposite ends:
| Direction | Starting point | Move | |
|---|---|---|---|
| Specialisation | Top-down | One existing entity type | Define a number of subclasses of it, each containing a subset of the superclass’s entities, on the basis of a more specific distinguishing characteristic |
| Generalisation | Bottom-up | Several existing entity types | Abstraction — ignore the differences amongst the subclasses and generalise them into a common superclass |
The two are duals, not alternatives: the subclasses are a specialisation of the superclass, and the superclass is a generalisation of the subclasses. Which word you use just records how you arrived at the diagram.
Constraints on a specialisation
Two independent constraints apply to every specialisation, and both come from the UoD rather than from preference. They are stated over different things: a specialisation may be total or partial, while the subclass sets may be overlapping or disjoint.
| Constraint | Applies to | Options | Question it answers |
|---|---|---|---|
| Completeness | The specialisation | Total (double line) vs partial (single line) | Must every superclass entity belong to some subclass? |
| Disjointness | The subclass sets | Disjoint (d) vs overlapping (o) |
Can one superclass entity belong to more than one subclass at once? |
Worked readings from the tutorials:
DOCTORspecialises disjointly intoGP/SPECIALIST— “each doctor is either a GP or a specialist, never both”.PARTICIPANTspecialises into overlappingHEALTH/AGEsubclasses — a participant can legitimately be in both at once.STAFFspecialises into overlappingDRIVER/ADMINin week2-tutorial-case-study-1-dirt-road-driving, because a staff member at a small rideshare company can be both at once. Getting this one wrong as disjoint would silently forbid a perfectly ordinary real employee.
flowchart TD
STAFF[STAFF] --- o1(("o"))
o1 --- DRIVER[DRIVER]
o1 --- ADMIN[ADMIN]
Licence(["Licence"]) --- DRIVER
DeskNumber(["DeskNumber"]) --- ADMIN
One superclass, several independent specialisations
A superclass can be specialised more than once, along different characteristics, each with its own circle and its own independent pair of constraints. The canonical case is EMPLOYEE carrying two specialisations side by side:
| Specialisation | Subclasses | Disjointness | Completeness |
|---|---|---|---|
| Documents held | PASSPORT / DRIVERLICENSE |
Overlapping (o) |
Partial (single line) |
| How they are paid | HOURLY / SALARY |
Disjoint (d) |
Total (double line) |
All four boxes are ordinary subclasses of EMPLOYEE — none of them is a category or a union type, and each is drawn as a plain rectangle. The o on the left circle is what permits an employee to hold a passport and a driver licence; the single line into it permits one to hold neither. The d on the right circle forbids an employee from being both hourly and salaried, and the double line into it requires every employee to be one or the other. The two circles say nothing about each other: an hourly employee is neither more nor less likely to hold a passport.
flowchart TD
EMPLOYEE[EMPLOYEE] --- o1(("o"))
EMPLOYEE === d1(("d"))
o1 --- PASSPORT[PASSPORT]
o1 --- DRIVERLICENSE[DRIVERLICENSE]
d1 --- HOURLY[HOURLY]
d1 --- SALARY[SALARY]
The same structure turns up elsewhere: GAME in week1-lab-practical-1-course-tools splits disjointly into SINGLEPLAYER / MULTIPLAYER (how it’s played) and, separately, disjointly into WINNABLE / NON-WIN (whether it can be won) — four subclasses, but two unrelated specialisations. EMPLOYEE is also specialised disjointly into SECRETARY / ENGINEER (with TypingSpeed and EngineerDetails respectively) in the running company diagram, which is a third, separate characteristic again.
Reading two circles as one is a common misreading: subclasses under different circles say nothing about each other, so disjointness in one specialisation places no restriction on membership in the other. See interpreting-er-diagrams for the statement-evaluation version of this trap.
Subclasses carry their own relationships
Because a subclass is a full entity type in its own right, relationships can attach to it directly, and this is often the whole point of creating it. SPECIALIST (not DOCTOR) is what RUNS a SPECIALIST CLINIC, and HEALTHWORKER oversees HEALTH participants specifically, not every PARTICIPANT. Attaching a relationship at the subclass level rather than the superclass level is itself a design decision — see er-design-choices.
What happens on mapping
Each subclass becomes its own relation whose primary key is the superclass’s key, plus a foreign key back to the superclass — Driver [id, licence] with Driver.id references Staff.id. The subclass relation holds only its own specific attributes: repeating the superclass’s attributes there (e.g. copying make/model from Vehicles onto 4WD) is a mapping error, since it wastes space and lets the two copies drift apart. See step +1 of er-to-relational-mapping, er-mapping-pitfalls for the rest of the subclass mistakes, and week4-tutorial-case-study-3-dirt-road-driving.