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 this lecture uses.

Today’s outline

Weak entities
The Enhanced ER (EER) diagram — superclasses/subclasses
Design choices for conceptual modelling

Weak entities

See relationship-constraints#weak-entities for the definition (owner entity, partial key, identifying relationship, total participation). Example: EMPLOYEE —1:N INSURES→ DEPENDANT, where DEPENDANT’s partial key is Pname and its full key is (EMPLOYEE.SSN, DEPENDANT.Pname).

flowchart LR
    EMPLOYEE[EMPLOYEE] ---|"1"| INSURES{INSURES}
    INSURES ===|"N"| DEPENDANT[[DEPENDANT]]
    SSN(["SSN (key)"]) --- EMPLOYEE
    Pname(["Pname (partial key)"]) --- DEPENDANT

Question — Weak Entity (hotel/room)

ROOM (partial key Number, attribute Type) is a weak entity, N:1 IN→ HOTEL (key Address, attribute Service). Which is true: (A) two hotels can share an address, (B) no two rooms share a number, (C) no two hotels have rooms with the same number, (D) no two same-numbered rooms share a type, (E) none of the above?

Solution

(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 (must be unique per hotel); (D) doesn’t follow from any stated constraint.

flowchart LR
    ROOM[[ROOM]] ===|"N"| IN{IN}
    IN ---|"1"| HOTEL[HOTEL]
    Number(["Number (partial key)"]) --- ROOM
    Type(["Type"]) --- ROOM
    Address(["Address (key)"]) --- HOTEL
    Service(["Service"]) --- HOTEL

The EER model — superclasses and subclasses

An entity type is called a class in the EER model. Entities in the same class share the same attributes; a class can be a superclass or subclass — a subclass inherits its superclass’s attributes/ relationships, and can also have its own specific attributes/relationships. Every entity in a subclass is a member of its superclass(es).

Motivating example: a supermarket ITEM (superclass: ProductName, Price) vs a FOOD item (subclass: adds ExpiryDate) — FOOD is just an extension of a regular ITEM.

Specialisation: define subclasses of an entity type based on a more specific distinguishing characteristic (top-down).
Generalisation: abstract away differences between several existing entity types to identify a common superclass (bottom-up).
Subclasses are a specialisation of the superclass; the superclass is a generalisation of the subclasses.

Constraints on specialisation

See er-diagram-notation#subclasses-superclasses-eer for the notation.

Total vs partial: whether every superclass instance must belong to some subclass, or not.
Disjoint (d) vs overlapping (o): whether an instance can belong to at most one subclass, or more than one at once.

Example: EMPLOYEE splits disjointly (d) into SECRETARY/ENGINEER (each with their own attribute — TypingSpeed/EngineerDetails), and separately (still under EMPLOYEE) an overlapping (o) split into DRIVER/PASSPORT-holder isn’t required to be disjoint or total depending on the UoD.

flowchart TD
    EMPLOYEE[EMPLOYEE] --- d(("d"))
    d --- SECRETARY[[SECRETARY]]
    d --- ENGINEER[[ENGINEER]]
    TypingSpeed(["TypingSpeed"]) --- SECRETARY
    EngineerDetails(["EngineerDetails"]) --- ENGINEER

Exercise — University database (UofU)

Students: unique student id, name, address, phone, registered major; visiting students stay for a year. Courses: identified by department + course#, with title and credits. Course sections: unique section# per course/semester, taught by exactly one instructor (no idle instructors); instructors have a unique name and a higher degree recorded. Students enrol in a section and get a mark. A course may have other courses as prerequisites.

Solution

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).

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

Design choices for ER conceptual design

Modelling the same UoD can involve genuine design choices:

Equivalent choices — two ER representations that produce the same resulting database.
Inequivalent choices — the UoD is ambiguous, and different mappings meet the spec but enforce different constraints; note your assumption under the diagram when this happens.

Common choice points:

Attribute vs. (weak) entity type — e.g. an interview’s Details (Department, Date) can be modelled as attributes of the APPLIES relationship, or pulled out into its own weak INTERVIEW entity.
Attribute vs. subclass — e.g. EMPLOYEE.Type as a plain attribute, vs. splitting into SECRETARY/ENGINEER subclasses with their own extra attributes.
Binary vs. n-ary relationships — e.g. SUPPLIER/PROJECT/PART as three binary relationships (CANSUPPLY, USES, SUPPLIES) vs. one ternary SUPPLIES relationship (with a quantity attribute) linking all three at once.
Subclass relationship vs. superclass relationship — e.g. giving WORKSON (a PROJECT relationship) directly to the EMPLOYEE superclass vs. only to specific subclasses like ENGINEER.

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.

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.

Working

External Level → 5, Logical data independence → 3, Conceptual Level → 2, Physical data independence → 4, Internal Level → 1.

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):

Working

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.

Section B — Entities and attributes

Question 1 — RESTAURANT: CHEF entity

Each chef’s name (first/middle/last) is unique; DoB and age are recorded, along with any specialisations.

Working

CHEF entity, composite key Name (→ FirstName, MiddleName, LastName, underlined as the key), simple attribute DoB, derived attribute Age (dashed oval, computed from DoB), and multivalued attribute Specialisations (double-lined oval).

flowchart TD
    Name(["Name (key)"]) --- CHEF[CHEF]
    FirstName(["FirstName"]) --- Name
    MiddleName(["MiddleName"]) --- Name
    LastName(["LastName"]) --- Name
    DoB(["DoB"]) --- CHEF
    Age(["Age (derived)"]) --- CHEF
    Specialisations(["Specialisations (multi)"]) --- CHEF

Section C — Basic relationships

Question 1 — Olympics database

Athletes: unique athleteID, Name, Age, Sex, Country. Events: unique eventID, Name, Category. Venues: unique ID, Name, Address. Each athlete participates in ≥1 event with a recorded Placement; each event is held at exactly one venue; some venues are backups and never host an event.

Working

ATHLETE (key ID) —M:N PARTICIPATES (attribute Placement)→ EVENT (key ID), total participation on the ATHLETE side. EVENT —N:1 HOSTS→ VENUE (key ID), with partial participation on the VENUE side (“some venues… never used to host an event”).

flowchart LR
    ATHLETE[ATHLETE] ===|"M"| PARTICIPATES{PARTICIPATES}
    PARTICIPATES ---|"N"| EVENT[EVENT]
    EVENT ---|"N"| HOSTS{HOSTS}
    HOSTS ---|"1"| VENUE[VENUE]
    IDa(["ID (key)"]) --- ATHLETE
    IDe(["ID (key)"]) --- EVENT
    IDv(["ID (key)"]) --- VENUE
    Placement(["Placement"]) --- PARTICIPATES

Section D — Analysis & application

Uses the same Olympics UoD/diagram as Section C.

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:

Working

1, 3, 4. (1) Address isn’t a key of VENUE here, so two venues could share one. (2) EVENT’s relationships (PARTICIPATES, HOSTS) are both binary, not ternary. (3) correct by definition. (4) the diagram shows a plain (non-recursive, non-attributed-for-repeats) M:N relationship — an athlete/event pair only appears once. (5) false — some venues are backups and never host an event (partial participation).

Question 2 — Total participation, semantically

What does total participation on both sides of PARTICIPATES mean? Does it align with the UoD?

Working

Total participation from both ATHLETE and EVENT means every athlete participates in at least one event, and every event has at least one participating athlete — consistent with the UoD (“each athlete participates in at least one event”).

Question 3 — Redraw VENUE with two candidate keys

Redraw VENUE so it can be identified by either its ID, or the combination of its Name and Address.

Working

VENUE gets two separate key ovals: ID (underlined), and a composite key Key (also underlined) that itself splits into Name + Address — two independent candidate keys for the one entity type.

flowchart TD
    ID(["ID (key)"]) --- VENUE[VENUE]
    Key(["Key (key)"]) --- VENUE
    Name(["Name"]) --- Key
    Address(["Address"]) --- Key

Section E — Additional resources (not covered in the applied class)

Question 1 — RESTAURANT: DISH entity

The restaurant serves many dishes, each with a unique name on the menu. Each dish also has a unique number and a description (history, calories, price), a preparation time, and ingredients.

Working

DISH entity: key attributes Name and (separately) Number — two candidate keys. Composite attribute Description (→ derived-looking History, Calories, Price — History shown multivalued in the source diagram). Simple attribute PreparationTime, and multivalued attribute Ingredients.

flowchart TD
    Name(["Name (key)"]) --- DISH[DISH]
    Number(["Number (key)"]) --- DISH
    Description(["Description"]) --- DISH
    History(["History (multi)"]) --- Description
    Calories(["Calories"]) --- Description
    Price(["Price"]) --- Description
    PreparationTime(["PreparationTime"]) --- DISH
    Ingredients(["Ingredients (multi)"]) --- DISH

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, full Name, DOB. A user can register any number of emergency contacts (Name unique per user, Email, Phone).
Staff: unique staffID, full Name (first/middle/last), DOB, Phone(s). Split into Administration (DeskNumber) and Driver (Licence) — a staff member can be both at once (overlapping).
Vehicles: VIN, Make, Model. Disjointly splits into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).
Trip: a user, a vehicle, and a driver (never permanently assigned to one vehicle — recorded per trip), plus a BookingTime and start/end timestamps (used to derive Fare); a trip can record multiple stop Locations. After a trip, the user separately rates the driver and the vehicle (integer out of 10, stored as RATES relationships) — a repeat rating updates the existing one rather than creating a new one.

Working

USER (key ID, Name, DOB) —1:N (total on the weak-entity side) HAS→ EMERGENCYCONTACTS (weak entity, partial key Name, Email, Phone). STAFF (key ID, Name, DOB, multivalued Phone) overlappingly (o) specialises into DRIVER (Licence) and ADMIN (DeskNumber). VEHICLES (key VIN, Make, Model) disjointly (d) specialises into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).

USER —M:N TRIP←DRIVER, and VEHICLES —M:N TRIP (a trip is identified by USER+DRIVER+VEHICLES+BookingTime, with StartTime, EndTime, multivalued StopLocation, and derived Fare). Separately, USER —M:N RATES (Number, out of 10)→ DRIVER, and USER —M:N RATES→ VEHICLES.

flowchart TD
    USER[USER] ---|"1"| HAS{HAS}
    HAS ===|"N"| EMERGENCYCONTACTS[[EMERGENCYCONTACTS]]

    STAFF[STAFF] --- o1(("o"))
    o1 --- DRIVER[[DRIVER]]
    o1 --- ADMIN[[ADMIN]]

    VEHICLES[VEHICLES] --- d1(("d"))
    d1 --- FourWD[["4WD"]]
    d1 --- TwoWD[["2WD"]]

    USER ---|"N"| TRIP{TRIP}
    DRIVER ---|"N"| TRIP
    VEHICLES ---|"N"| TRIP

    USER ---|"M"| RATESD{RATES}
    RATESD ---|"N"| DRIVER
    USER ---|"M"| RATESV{RATES}
    RATESV ---|"N"| VEHICLES

    IDu(["ID (key)"]) --- USER
    NameEC(["Name (partial key)"]) --- EMERGENCYCONTACTS
    IDs(["ID (key)"]) --- STAFF
    Licence(["Licence"]) --- DRIVER
    DeskNumber(["DeskNumber"]) --- ADMIN
    VIN(["VIN (key)"]) --- VEHICLES
    BookingTime(["BookingTime (partial key)"]) --- TRIP
    Fare(["Fare (derived)"]) --- TRIP

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

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.

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-line oval	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. Name splitting into FirstName/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

See relationship-constraints for cardinality ratio (1:1, 1:N, M:N) and participation (total/partial) notation.

Subclasses / superclasses (EER)

Symbol	Meaning
`⊂` between two entity types	The entity type on the narrow side is a subclass of the one on the wide side
A circle joining several subclasses to one superclass	Several entity types are all subclasses of the same superclass
`d` inside that circle	Disjoint specialization — an entity instance can belong to at most one of the subclasses
`o` inside that circle	Overlapping specialization — an entity instance can belong to more than one subclass at once
Single line from superclass to the subclass circle	Partial specialization — not every superclass instance needs to belong to a subclass
Double line from superclass to the subclass circle	Total specialization — 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 — not PoliceOfficer).
Relationship names: capitalised, no spaces, ideally one word, preferably a verb (e.g. CREATES, ACTSIN — not LeadActor).
Attribute names: UpperCamelCase — first letter of each word capitalised, no spaces, acronym letters stay capitalised (e.g. ComputerIP, DateOfBirth — not Date 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
`[[WeakEntity]]` (subroutine, double vertical bars)	Weak entity type
`{Relationship}` (diamond/rhombus)	Relationship type
`([Attribute])` (stadium)	Attribute
`((d))` / `((o))` (small circle)	Disjoint/overlapping specialisation marker

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
    ...
```

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. Introduced in 2026-02-23-conceptual-database-design-and-the-er-model; see er-diagram-notation for the full symbol legend.

Cardinality ratio

Specifies the number of relationship instances an entity can participate in.

Ratio	Meaning
1:1	Both entities can participate in only one relationship instance
1:N	One entity can participate in many relationship instances (the “1” side, only one of the “N” side per instance)
N:1	Same relationship, viewed from the other side
M:N	Both entities can participate in many different relationship instances

Example: EMPLOYEE WORKSFOR DEPARTMENT — “each department can have any number of employees, but an employee can work for at most one department” is N:1 (N on the EMPLOYEE side, 1 on the DEPARTMENT side).

Participation constraint (existence dependency)

Indicates whether an entity’s existence 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. E.g. “every employee must work for a department” — EMPLOYEE has total participation in WORKSFOR.
Partial participation (single line) — an entity instance is not required to participate. E.g. “every employee can manage 0 or more departments” — EMPLOYEE has partial participation in MANAGES.

Weak entities

An entity type with no key attribute of its own is a weak entity — it can only be identified uniquely by combining the primary key of its owner entity with its own partial key (underlined with a dotted line). The relationship linking a weak entity to its owner is the identifying relationship, and the weak entity always has total participation in it (it can’t exist without its owner). Both the weak entity box and the identifying relationship diamond are drawn with double lines. A weak entity can have two (or more) owner entity types, in which case the identifying relationship is n-ary rather than binary.