INFS1200 — Week 3 Notes

The Relational Model and Integrity Constraints

Module 2, part 1. See relational-mapping-notation for the schema notation this lecture (and 2026-03-16-er-to-relational-mapping) uses.

Today’s outline

  • Relational model concepts — relations, attributes, domains, tuples
  • Integrity constraints
  • The transaction concept

Relational model concepts

Introduced by E.F. Codd in 1970. Many DBMS products are based on this model — a sound theoretical foundation with a simple, uniform data structure called a relation. Four basic concepts: relations, attributes, domains, tuples.

Relations

A relation is the main construct for representing data — informally, a set of records, similar to a table with columns and rows. The term table is used interchangeably with relation, but every relation is a table; not every table is a relation — relations have specific properties based on mathematical set theory (e.g. a “pivoted” table with merged header cells, like a quarterly sales report broken down by region/suburb, is a table but not a valid relation).

Domains

A domain D is a set of atomic values — an atomic value is indivisible as far as the relational model is concerned. Each domain has a data type/format: integers, numbers/currency, fixed/variable-length character strings, date/timestamp, a sub-range of a data type (e.g. 1 ≤ grade ≤ 7), or an enumerated type (e.g. Gender ∈ {Male, Female, Other}) — including format-constrained domains like Australian phone numbers (61 + 9 digits) or car registrations (6 alphanumeric characters, no Q).

Attributes

Each attribute A is the name of a role played by some domain D in a relation R. The number of attributes in R is its degree. Same-named attributes across relations don’t necessarily share a domain (Department.id and Employee.id can be different domains despite the shared name id), and differently-named attributes can share a domain (Employee.id and Employee.managerId can both be drawn from the same “employee id” domain, despite the different names).

Tuples

Each tuple t is an ordered list of n values t = <v1, v2, …, vn>, where each value belongs to the corresponding attribute’s domain, or is the special value NULL. t is called an n-tuple.

Relation schema and instance

  • Relation schema: R [A1, A2, …, An] — a relation name R and its list of attributes; n is the degree of the relation. E.g. Employee [id, name, sex, salary, department] is a schema of degree 5.
  • Relation instance: r(R), a set of n-tuples r = {t1, t2, …, tm} — the actual data, at a point in time.

Question — Schema and Instance

Match each characteristic to Schema or Instance: “data in the database”, “specified during database design”, “data describing the data”, “created through data update operations”.

Instance: “data in the database”, “created through data update operations” (inserts/updates/deletes change the instance, not the schema). Schema: “specified during database design”, “data describing the data” (the schema is metadata — it describes the shape data must take, not the data itself).

Ordering of tuples and values

Relations are sets of tuples — mathematically, a set has no implied order. Semantically (e.g. when writing queries), tuple order is irrelevant. Physically, tuples reside on blocks of secondary storage with some ordering, but two instances with the same tuples in a different order are still the same relation. Likewise, an n-tuple is syntactically an ordered list — every tuple in one relation must list values in the same attribute order — but semantically, which order is chosen doesn’t matter, as long as the attribute/value correspondence is maintained consistently.

Integrity constraints

Integrity constraints are rules that enforce the correctness of a database — they must hold on every instance of the schema. Five kinds: domain, key, entity integrity, referential integrity, semantic.

Domain constraint

A domain constraint violation occurs when an attribute’s value doesn’t appear in its corresponding domain — e.g. Employee.id = "LOL" when id is meant to be a 4-digit integer.

Key

A key is a minimal set of attributes that uniquely identifies tuples in a relation — minimal meaning no redundant attributes, not necessarily the smallest possible set. A schema can have more than one key (each a candidate key); the one chosen as the relation’s main key is the primary key (conventionally underlined). A key constraint violation occurs when a tuple is inserted/modified to share a key value with an existing tuple.

Question — Key

Assuming department IDs are unique, which of the following is a key for Department [id, name, manager]: (A) (id), (B) (id, name), (C) (id, manager), (D) all of the above?

(A) (id) — since IDs are already unique on their own, (id) is the minimal key; (id, name) and (id, manager) both contain a redundant extra attribute (adding anything to an already-unique set isn’t minimal).

Question — Key (multiple candidate keys)

Assuming department IDs are unique and the combination of Name and Manager is also unique per department, which of the following is a key(s): (A) (id), (B) (id, name), (C) (name, manager), (D) both A and C?

(D) Both A and C. (id) is still minimal and unique on its own; now (name, manager) is also minimal and unique, making it a second candidate key. (id, name) is still not minimal since (id) alone already suffices.

Entity integrity constraint

An entity integrity constraint violation occurs when a tuple is inserted/modified such that (any part of) its primary key is NULL — for a composite primary key, no part of it can be null.

Foreign keys

A foreign key is a set of attributes in one relation that links it to another relation’s primary key. Formally: let FK be attributes in R1 and PK the primary key of R2. FK in R1 is a foreign key referencing PK in R2 if FK/PK share a domain, and for every tuple t1 in R1, either t1[FK] is NULL, or some tuple t2 in R2 has t1[FK] = t2[PK].

Department [id, name, manager]
Department.manager references Employee.id

Employee [id, name, sex, salary, department]
Employee.department references Department.id

Self-referencing relations are also possible — a table can reference itself, e.g. Employee.managerId references Employee.id. Composite foreign keys are possible too, referencing a multi-attribute primary key:

Student [sid, name]

Course [cid, department]

Enrolment [sid, cid, department, grade]
Enrolment.sid references Student.sid
Enrolment.{cid, department} references Course.{cid, department}

Referential integrity constraint

A referential integrity constraint violation occurs when a foreign key value doesn’t match any existing primary key value in the referenced relation (and isn’t NULL) — e.g. inserting an Employee tuple with department = 5 when no Department with id = 5 exists.

Semantic (business-rule) constraint

Semantic constraints are generally defined by the business/organisation (not derivable from the schema’s structure alone) — e.g. “an employee’s salary must not exceed their supervisor’s”, or “an employee can work at most 56 hours across all projects”. Often implemented via a constraint specification language (SQL triggers/assertions).

Question — Integrity Constraints (password)

Opening an online bank account, you enter your usual password “password123”, but get: “your password must contain at least one capital letter and a number”. What kind of constraint is this?

Domain constraint — the domain of “password” is defined as strings matching a particular format (≥1 capital, ≥1 digit); “password123” simply isn’t a member of that domain.

Question — Integrity Constraints (which is violated?)

Given Department [id, name, manager] and Employee [id, name, salary, department], with Department rows (1, Marketing, 4671), (2, Development, 1751), and Employee rows (1751, Paris Lane, 60000, 2), (4671, Anna Lee, 70000, 1), (2670, Grace Mills, 50000, 2), (2034, Jack Smith, 40000, 1) — which constraint is violated by each of the following?

  1. Inserting (2670, James Smith, 40000, 1) into Employee.
  2. Inserting (2644, James, Smith, 1) into Employee (note: only 4 values given for a 4-attribute relation, but shifted).
  3. Inserting (2644, James Smith, 40000, 3) into Employee.
  4. Deleting (2, Development, 1751) from Department.
  5. Updating (2, Development, 1751) to (2, Development, 2034) in Department.
  1. Key constraint — id = 2670 already exists in Employee.
  2. Domain constraint — the shift means salary receives a non-numeric value ("Smith"), which isn’t a member of salary’s domain.
  3. Referential integrity constraint — no Department with id = 3 exists.
  4. Referential integrity constraint — Employee rows with department = 2 still exist (Grace Mills), so deleting Department 2 would leave a dangling foreign key.
  5. None of the above (by the stated constraints) — Employee with id = 2034 doesn’t actually work in Department 1, but there’s no declared constraint stopping a department’s manager from being an employee of a different department; this would only be a semantic constraint if the business rule “a manager must work in the department they manage” were explicitly specified.

Constraints and operations

Enforcing integrity constraints keeps the database consistent — insert, modify, and delete operations must not leave the database in an inconsistent state; a DBMS should reject any update that would violate integrity.

  • Insertion/modification can violate any of the five constraint types (domain, key, entity integrity, referential integrity, semantic).
  • Deletion can only violate referential or semantic constraints. A referential integrity violation on delete can be handled by rejecting the delete, cascading it (deleting dependent rows too), or setting the referencing value to a default/NULL.

The transaction concept

A transaction is an executing program that includes database operations (reads, inserts, deletes, updates). At the end of a transaction, the database must be left in a valid/consistent state satisfying all constraints — but constraint violations are allowed at intermediate steps within the transaction.

Example: given Department [id, name, manager] and Employee [id, name, sex, salary, department], with the business rules “every department must have ≥1 employee” and “every employee must work for a department” — neither a new department (no employees yet) nor a new employee (no department yet) can be inserted alone without violating one of these rules. A transaction that inserts both the new department and its first employee together resolves this: the intermediate state (after just one insert) is inconsistent, but the final state (after both inserts) is valid.

Applied Class 2: Entity Relationship Diagrams

Practice for 2026-03-02-weak-entities-the-eer-model-and-design-choices. See er-diagram-notation and relationship-constraints for reference.

Section A — ERD components

Given a COMPANY ER diagram (EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT, plus the relationships MANAGES/WORKS_FOR/CONTROLS/ WORKS_ON/SUPERVISION/DEPENDENTSOF), find an example of each ERD component.

Component Example
Strong entity EMPLOYEE, DEPARTMENT, PROJECT
Weak entity DEPENDENT
(Candidate) key EMPLOYEE.Ssn; DEPARTMENT.Name/Number; PROJECT.Name/Number; DEPENDENT.{Ssn, Name}
Partial key DEPENDENT.Name
Composite attribute EMPLOYEE.Name (→ FirstName, MiddleInitial, LastName)
Derived attribute DEPARTMENT.Number_of_employees
Multivalued attribute DEPARTMENT.Locations
1-1 relationship MANAGES
1-N relationship CONTROLS, WORKS_FOR, SUPERVISION
M-N relationship WORKS_ON
Identifying relationship DEPENDENTSOF 

flowchart TD
    EMPLOYEE[EMPLOYEE] ---|"1 supervisor"| SUPERVISION{SUPERVISION}
    SUPERVISION ---|"N supervisee"| EMPLOYEE
    EMPLOYEE ===|"N"| WORKS_FOR{WORKS_FOR}
    WORKS_FOR ---|"1"| DEPARTMENT[DEPARTMENT]
    EMPLOYEE ---|"1"| MANAGES{MANAGES}
    MANAGES ===|"1"| DEPARTMENT
    DEPARTMENT ---|"1"| CONTROLS{CONTROLS}
    CONTROLS ---|"N"| PROJECT[PROJECT]
    EMPLOYEE ---|"M"| WORKS_ON{WORKS_ON}
    WORKS_ON ---|"N"| PROJECT
    EMPLOYEE ---|"1"| DEPENDENTSOF{DEPENDENTSOF}
    DEPENDENTSOF ===|"N"| DEPENDENT[[DEPENDENT]]

    Ssn(["Ssn (key)"]) --- EMPLOYEE
    NameE(["Name"]) --- EMPLOYEE
    Salary(["Salary"]) --- EMPLOYEE

    DeptKey(["Number/Name (key)"]) --- DEPARTMENT
    NumEmp(["Number_of_employees (derived)"]) --- DEPARTMENT
    Locations(["Locations (multi)"]) --- DEPARTMENT

    ProjKey(["Number/Name (key)"]) --- PROJECT

    DepName(["Name (partial key)"]) --- DEPENDENT

Section B — ERD modelling assumptions

Uses a health/vaccination EER diagram: HEALTHWORKER —1:N OVERSEEN→ HEALTH (subclass of PARTICIPANT, alongside an overlapping AGE subclass); VACCINE (ID, composite Key = Brand+Name) has a recursive WITH relationship (roles First/Second, both partial) and —1:N HAS→ BATCH (weak entity, partial key Number).

Evaluate each statement as Correct or Incorrect.

B1 — Each vaccine must have a second dose or a preceding dose.

B2 — A participant can be overseen by several healthcare workers.

B3 — Every participant in the HEALTH subclass is overseen by a health worker during vaccination.

B4 — A vaccine can only be uniquely identified by the combination of its Brand and Name.

B5 — A participant can be in both the HEALTH subclass and the AGE subclass.

B6 — A BATCH is uniquely identified by only its number.

  • B1 Incorrect — the recursive WITH relationship lets a vaccine be recorded as a first or second dose, but participation from both sides is partial: a vaccine doesn’t have to be part of a two-shot program.
  • B2 Incorrect — OVERSEEN is 1:N from HEALTHWORKER to HEALTH: one healthcare worker can oversee many participants, but each participant has at most one overseeing healthcare worker.
  • B3 Correct — OVERSEEN has total participation from the HEALTH side.
  • B4 Incorrect — VACCINE has two candidate keys: the composite Key (Brand+Name), or its ID.
  • B5 Correct — HEALTH and AGE are overlapping subclasses of PARTICIPANT — an instance can be a member of both at once.
  • B6 Incorrect — BATCH is a weak entity; its real key is Batch Number combined with its owner VACCINE’s key (either ID or Key).

flowchart TD
    PARTICIPANT[PARTICIPANT] --- o1(("o"))
    o1 --- HEALTH[[HEALTH]]
    o1 --- AGE[[AGE]]
    HEALTHWORKER[HEALTHWORKER] ---|"1"| OVERSEEN{OVERSEEN}
    OVERSEEN ===|"N"| HEALTH

    VACCINE[VACCINE] ---|"First"| WITH{WITH}
    WITH ---|"Second"| VACCINE
    VACCINE ---|"1"| HAS0{HAS}
    HAS0 ===|"N"| BATCH[[BATCH]]

    IDv(["ID (key)"]) --- VACCINE
    VKey(["Key (key)"]) --- VACCINE
    Brand(["Brand"]) --- VKey
    VName(["Name"]) --- VKey
    Number(["Number (partial key)"]) --- BATCH

Section C — Conceptual modelling

Question 1 — Cinema

Movies (unique Name, Description, RunningTime); attendants (unique StaffID, Name, Phone); customers (unique ID, Name, Email); viewings record a Timestamp (unique per customer+movie) and any Snacks purchased; exactly one attendant oversees each viewing.

MOVIE —1:N HAS→ VIEWING (Timestamp, multivalued Snack) —1:N OVERSEES←ATTENDANT; CUSTOMER —1:N HAS→ VIEWING. VIEWING’s key is the composite of its owning CUSTOMER+MOVIE+Timestamp (it’s a weak entity in the fuller reading of the UoD, or an M:N relationship with a Timestamp key attribute, depending on the design choice made).

flowchart TD
    MOVIE[MOVIE] ---|"1"| HAS1{HAS}
    HAS1 ---|"N"| VIEWING[[VIEWING]]
    CUSTOMER1[CUSTOMER] ---|"1"| HAS2{HAS}
    HAS2 ---|"N"| VIEWING
    VIEWING ---|"N"| OVERSEES{OVERSEES}
    OVERSEES ---|"1"| ATTENDANT[ATTENDANT]

    NameM(["Name (key)"]) --- MOVIE
    Description(["Description"]) --- MOVIE
    RunningTime(["RunningTime"]) --- MOVIE
    Timestamp(["Timestamp (partial key)"]) --- VIEWING
    Snack(["Snack (multi)"]) --- VIEWING
    StaffID(["StaffID (key)"]) --- ATTENDANT
    IDc(["ID (key)"]) --- CUSTOMER1

Question 2 — Medical clinic

Patients (PatientID) and doctors (LicenceNo); each doctor is either a GP or a specialist (never both), specialists also record their SpecialisationArea; doctors work in clinics (RegistrationNo), one clinic can have several doctors; some clinics are specialist clinics, each run by exactly one specialist doctor; patients make appointments (Fee) to consult doctors.

PATIENT (PatientID) —M:N CONSULTS (Fee)→ DOCTOR (LicenceNo), which disjointly (d) specialises into GP/SPECIALIST (SpecialisationArea). DOCTOR —M:N WORKSIN→ CLINIC (RegistrationNo), which has a subclass SPECIALIST CLINIC —1:N RUNS←SPECIALIST.

Assumptions: every patient in the DB has made an appointment; every clinic has at least one doctor.

flowchart TD
    PATIENT[PATIENT] ---|"M"| CONSULTS{CONSULTS}
    CONSULTS ---|"N"| DOCTOR[DOCTOR]
    DOCTOR --- d(("d"))
    d --- GP[[GP]]
    d --- SPECIALIST[[SPECIALIST]]
    DOCTOR ---|"M"| WORKSIN{WORKSIN}
    WORKSIN ---|"N"| CLINIC[CLINIC]
    CLINIC --- SPECIALISTCLINIC[["SPECIALIST CLINIC"]]
    SPECIALIST ---|"1"| RUNS{RUNS}
    RUNS ---|"N"| SPECIALISTCLINIC

    PatientID(["PatientID (key)"]) --- PATIENT
    LicenceNo(["LicenceNo (key)"]) --- DOCTOR
    Fee(["Fee"]) --- CONSULTS
    SpecArea(["SpecialisationArea"]) --- SPECIALIST
    RegNo(["RegistrationNo (key)"]) --- CLINIC

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

Question 1 — Bubble tea shop

BUBBLETEA (unique Name, Description, Ingredients, Price); customers sign up with a BubbleID, Name, Phone, Email; each order (BUYS) is timestamped with a PrepTime; customers can friend other customers.

BUBBLETEA —N:M BUYS (PrepTime, key Timestamp)→ CUSTOMER (BubbleID); CUSTOMER has a recursive M:N FRIENDS relationship with itself (roles Requestor/Requestee).

flowchart LR
    BUBBLETEA[BUBBLETEA] ---|"N"| BUYS{BUYS}
    BUYS ---|"M"| CUSTOMER2[CUSTOMER]
    CUSTOMER2 ---|"Requestor"| FRIENDS{FRIENDS}
    FRIENDS ---|"Requestee"| CUSTOMER2

    NameB(["Name (key)"]) --- BUBBLETEA
    Description2(["Description"]) --- BUBBLETEA
    Ingredients(["Ingredients"]) --- BUBBLETEA
    Price(["Price"]) --- BUBBLETEA
    Timestamp2(["Timestamp (partial key)"]) --- BUYS
    PrepTime(["PrepTime"]) --- BUYS
    BubbleID(["BubbleID (key)"]) --- CUSTOMER2

Question 2 — Bank

Bank (composite key Code+Name, Address) has branches (Number unique per-bank, Address); a branch manages accounts and loans; every account has Type, Balance, unique Number, controlled by ≥1 customers; every loan has unique Number, Type, Amount, connected to ≥1 customers; every loan/account belongs to exactly one branch; customers have Name, Address, Phone, unique ID.

BANK (key Key = Code+Name) —1:N OPERATES→ BRANCH (weak entity, partial key Number) —1:N OFFERS→ LOAN (Number, Type, Amount); BRANCH —1:N OFFERS→ ACCOUNT (Number, Balance, Type). LOAN —M:N HAS→ CUSTOMER (ID, Name, Address, Phone); ACCOUNT —M:N HAS→ CUSTOMER, both with total participation on the LOAN/ACCOUNT side (“must be associated with… at least one customer”).

flowchart TD
    BANK[BANK] ---|"1"| OPERATES{OPERATES}
    OPERATES ===|"N"| BRANCH[[BRANCH]]
    BRANCH ---|"1"| OFFERS1{OFFERS}
    OFFERS1 ---|"N"| LOAN[LOAN]
    BRANCH ---|"1"| OFFERS2{OFFERS}
    OFFERS2 ---|"N"| ACCOUNT[ACCOUNT]
    LOAN ===|"M"| HAS3{HAS}
    HAS3 ---|"N"| CUSTOMER3[CUSTOMER]
    ACCOUNT ===|"M"| HAS4{HAS}
    HAS4 ---|"N"| CUSTOMER3

    BankKey(["Key (key)"]) --- BANK
    Code(["Code"]) --- BankKey
    BName(["Name"]) --- BankKey
    BranchNum(["Number (partial key)"]) --- BRANCH
    LoanNum(["Number (key)"]) --- LOAN
    AcctNum(["Number (key)"]) --- ACCOUNT
    IDc2(["ID (key)"]) --- CUSTOMER3

Case Study 2: Dirt Road Driving

Group case study applying integrity constraints from 2026-03-09-the-relational-model-and-integrity-constraints to Dirt Road Driving’s internal payroll system. See relational-mapping-notation for reference.

Section A — The schema

Dirt Road Driving’s payroll backend, built by an external developer:

Employee [id, firstName, lastName, role]

Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id

TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name

Company policy (a semantic/business-rule constraint, not enforceable by the schema’s structure alone): administration staff cannot be project leaders.

Question 1 — Identify a key and a foreign key

Any candidate key works, e.g. Employee.id, Project.name, or TimeLog.{employeeID, projectName, date} (composite). Any foreign key works, e.g. Project.projectLeader, TimeLog.employeeID, or TimeLog.projectName.

Section B — Integrity constraint violations

Sample data: Employee has (1919, Diluen, Smith, Developer), (2014, Daniel, Johnson, Administration), (2019, Annie, Fang, Developer), (2020, Russell, Turner, Manager). Project has ("Website Setup", ..., 12000, 2019), ("2020 Marketing", ..., 40000, 2020). TimeLog has 5 rows, including (1919, "Website Setup", 2/1/2020, 5, true) and (2020, "2020 Marketing", 2/1/2020, 5, true), among others — no row with employeeID = 1919 and projectName = "2020 Marketing". For each operation, does it violate an integrity constraint?

Operation 1 — Update ("Website Setup", ..., 12000, 2019) to ("Website Setup", ..., 20000, 1919) in Project (changes funding and projectLeader; 1919 is a Developer).

Operation 2 — Insert (2014, "Rebecca", "Zhang", "Administration") into Employee.

Operation 3 — Update (2020, "2020 Marketing", 2/1/2020, 5, true) to (1919, "Overall Marketing", 2/1/2020, 5, true) in TimeLog.

Operation 4 — Insert (NULL, "Test", "Test", "Test") into Employee.

Operation 5 — Delete (2014, "Daniel", "Johnson", "Administration") from Employee.

Operation 6 — Insert ("Talent Recruitment Initiative", ..., 10000, 2014) into Project (2014 is Administration).

  • Op 1 No — a well-formed update; funding/projectLeader values both change to valid new values (1919 is a real, existing employee), and nothing here conflicts with a key/entity/referential/semantic rule.
  • Op 2 Yes — Key constraint. id = 2014 already exists in Employee (id is its primary key), so this insert isn’t unique.
  • Op 3 Yes — Referential integrity. TimeLog.projectName references Project.name, but no Project named "Overall Marketing" exists — the foreign key value doesn’t resolve.
  • Op 4 Yes — Entity integrity. Employee.id is the primary key, and it’s NULL here — no part of a primary key may ever be NULL.
  • Op 5 Yes — Referential integrity. TimeLog.employeeID (foreign key to Employee.id) has a row referencing employeeID = 2014 — deleting that Employee row would leave it dangling.
  • Op 6 Yes — Semantic (user-defined) constraint. projectLeader = 2014 is an Administration staff member, violating the business rule that administration staff cannot be project leaders — this isn’t something the schema’s structure alone captures (no foreign key/domain/key rule is broken), it’s an organisation-specific policy.

Question 2 — Original examples

Give an original example (not copied from above) of an operation that would cause (a) a domain constraint violation, (b) a referential integrity constraint violation.

  1. Domain constraint: inserting ("A string is not a number", "Joe", "Blog", "Administration") into Employee — id is meant to be numeric, not a string. (b) Referential integrity: deleting (2020, "Russell", "Turner", "Manager") from Employee while a Project row still has projectLeader = 2020 — the deletion would leave that foreign key dangling.

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

Relational Mapping Notation

Reference legend + course style guide for writing relational schemas — introduced across 2026-03-09-the-relational-model-and-integrity-constraints and 2026-03-16-er-to-relational-mapping.

Schema notation

  • Relation [attr1, attr2, ...] — a relation schema; the (primary) key attribute(s) are underlined.
  • A composite primary key gets a single continuous underline spanning all of its attributes, e.g. Enrolment [studentId, courseCode, sem, year].
  • Relation.fk references OtherRelation.pk — a foreign key constraint, listed directly under the relation it belongs to.
  • Relation.{fk1, fk2} references OtherRelation.{pk1, pk2} — a composite foreign key referencing a composite primary key.

Naming convention (INFS1200/7900 style guide)

  • Table names: UpperCamelCase (first letter of each word capitalised, no spaces) — e.g. Flight, not FLIGHT.
  • Attribute names: lowerCamelCase (first letter of each word from the second word onwards capitalised) — e.g. departureTime, not Departure Time. Acronym attribute names stay entirely lowercase (e.g. eta, not ETA).
  • A space separates the table name from the opening bracket: Flight [planeNumber, ...], not Flight[planeNumber, ...].

Layout convention

A table’s foreign key constraint lines go directly underneath that table’s own definition, with a blank line before the next table starts — not all grouped together under a separate “Foreign Keys:” heading at the end:

Employee [ssn, firstName, lastName, dob, manager]
Employee.manager references Employee.ssn

Dependant [ssn, name, dob]
Dependant.ssn references Employee.ssn

DependantPhoneNumber [ssn, name, phoneNumber]
DependantPhoneNumber.{ssn, name} references Dependant.{ssn, name}

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.