INFS1200 — Week 4 Notes

ER to Relational Mapping

Module 2, part 2 — continues on from 2026-03-09-the-relational-model-and-integrity-constraints. See relational-mapping-notation for the schema notation used throughout, and er-diagram-notation for the ER/EER diagram notation being mapped from.

Today’s outline

  • The (7+1) steps for mapping an ER/EER diagram to a relational schema
  • Worked example: the Company database
  • Worked example: a Bank database

Why map at all?

The ER model is commonly used for conceptual design (user’s perspective); the relational model is the basis for most commercial DBMSs (storage perspective). Mapping converts a conceptual ER schema into a logical relational schema — input: an ER model; output: relations with primary/foreign key constraints.

The (7+1) steps

  1. Entity mapping
  2. Weak entity mapping
  3. Binary 1:1 relationship mapping
  4. Binary 1:N relationship mapping
  5. Binary M:N relationship mapping
  6. Multivalued attribute mapping
  7. N-ary relationship mapping
  8. (+1, for EER only) Super/subclass mapping

Super/subclass mapping (step “+1”) doesn’t happen at one fixed point — it’s generally done after step 1 or 2, whenever the subclass needs to exist for a later relationship-mapping step to reference it.

Running example — 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), DEPENDENT (weak entity, partial key DepName, Sex, DOB, Relationship) — 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, roles supervisor/supervisee), DEPENDENTSOF (EMPLOYEE 1:N DEPENDENT, identifying), and EMPLOYEE disjointly (d) specialises into SECRETARY (TypingSpeed)/ENGINEER (EngineerType).

Step 1 — Entity mapping

For each (strong) entity type: create a relation, choose a key as the primary key, and include all simple attributes (not composite, derived, or multivalued).

Employee [ssn, fName, mIt, lName, dob, address, sex, salary]

Department [dNumber, dName]

Project [pNo, pName, pLocation]

Name isn’t added directly (it’s composite — its simple components fName/mIt/lName are added instead); Locations and NumberOfEmployees aren’t added to Department yet (multivalued and derived respectively — derived attributes are never stored as columns, since they’re computed on demand).

Step 2 — Weak entity mapping

For each weak entity type: create a relation; its primary key is the combination of its owner’s primary key(s) and its own partial key; include a foreign key back to the owner’s primary key; include all simple attributes.

Dependent [ssn, depName, sex, dob, relationship]
Dependent.ssn references Employee.ssn

Ssn (Employee’s key) is included as part of Dependent’s own primary key, and depName is its partial key — so Dependent’s full key is (ssn, depName).

If a weak entity has more than one owner entity type, its relation includes a foreign key to each owner’s primary key, and its own primary key is the combination of all owner keys plus its partial key.

Question — Weak Entities (Cities/Provinces)

PROVINCE (key name, premier) —1:N IN→ CITY (partial key name, mayor). Resolving the dual use of “name” reasonably, which schema is the most reasonable translation?

A. Cities [name, mayor], Provinces [name, premier]
B. Cities [cName, pName, mayor], Provinces [pName, premier], Cities.cName references Provinces.pName, Cities.pName references Provinces.pName
C. Cities [cName, pName, mayor], Provinces [pName, premier], Cities.pName references Provinces.pName
D. Cities [cName, pName, mayor], In [cName, pName], Provinces [name, premier], Cities.pName references Provinces.name

(C). CITY is a weak entity, so its real key is (pName, cName) — (A) is wrong since it drops pName from Cities’ key entirely. (B) is wrong because cName (the partial key) is not a foreign key — only pName (inherited from the owner) is. (D) is wrong because IN is the identifying relationship for a weak entity — it does not get mapped to its own separate relation (unlike a regular 1:N relationship, which doesn’t need one either, but D invents an unnecessary one here on top of misnaming Provinces’ key column).

Step 3 — Binary 1:1 relationship mapping

For each binary 1:1 relationship: choose one participating entity type (prefer the one with total participation); include a foreign key from it back to the other entity’s primary key; include the relationship’s own simple attributes on the chosen side.

MANAGES (EMPLOYEE 1:1 DEPARTMENT, DEPARTMENT has total participation — every department must have a manager) — extend Department, since it’s the side with total participation:

Department [dNumber, dName, mgrSSN, mgrStartDate]
Department.mgrSSN references Employee.ssn

Question — Binary Relationship

S —1:1 R→ T (T has attribute b1). Which schema is a reasonable translation?

A. S [a1, b1], T [b1], S.b1 references T.b1
B. S [a1], T [b1]
C. ST [a1, b1]
D. S [a1], T [b1, a1], T.a1 references S.a1

(A). S should be the side extended with the foreign key, since S has full/total participation in R (shown by the diagram’s 1 cardinality directly against S, no partial-participation gap) — preferred over T for that reason. (B) loses all information about the relationship itself. (C) merges two intentionally-separate entity types into one relation, discarding a conceptual modelling decision. (D) puts the foreign key on the wrong side.

Step 4 — Binary 1:N relationship mapping

For each (non-weak) binary 1:N relationship: identify the entity type on the N side; add a foreign key there, referencing the primary key of the entity type on the 1 side; include the relationship’s simple attributes on the N side.

WORKSFOR (EMPLOYEE N:1 DEPARTMENT), CONTROLS (DEPARTMENT 1:N PROJECT), SUPERVISION (EMPLOYEE recursive, N side = supervisee):

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

Question — Relationship Mapping

A —N R 1→ B —N S 1→ C (with attributes a/d on A, b/e on B, c/f on C). Which of the following appears in your relational schema: (A) A [a, b, d], A.b references B.b; (B) B [b, c, e], B.c references C.c; (C) S [b, c]; (D) all of the above; (E) none of the above?

(B). Each 1:N relationship puts the foreign key on the N side: for R that’s A (referencing B), and for S that’s B (referencing C) — so B [b, c, e], B.c references C.c is exactly right. (A) is wrong: b is the right foreign key column for A, but it shouldn’t be part of A’s primary key — 1:N foreign keys are plain (non-key) attributes on the N side, not treated as part of a composite key like a weak entity’s would be (the answer as written implies b joining A’s key, which the mapping rule doesn’t call for). (C) is wrong — S is a relationship, not an entity type, so 1:N mapping doesn’t create a relation for it at all. The full correct schema: A [a, b, d], A.b references B.b; B [b, c, e], B.c references C.c; C [c, f].

Step 5 — Binary M:N relationship mapping

For each binary M:N relationship: create a new relation; include foreign keys to both participating entity types’ primary keys (their combination becomes the new relation’s primary key); include the relationship’s own simple attributes.

WORKSON (EMPLOYEE M:N PROJECT, attribute Hours):

WorksOn [ssn, pNo, hours]
WorksOn.ssn references Employee.ssn
WorksOn.pNo references Project.pNo

Note: 1:1 and 1:N relationships can also be mapped this same “new relation” way — useful when the relationship is sparse, since it avoids NULL foreign key values that a direct-embedding mapping would otherwise produce for non-participating rows.

Step 6 — Multivalued attribute mapping

For each multivalued attribute: create a new relation; include a foreign key to the owning entity’s primary key; the new relation’s primary key is the combination of that foreign key and the multivalued attribute itself (if the multivalued attribute is composite, include its simple components instead).

DEPARTMENT.Locations:

DeptLocs [dNumber, location]
DeptLocs.dNumber references Department.dNumber

Step 7 — N-ary relationship mapping

For each N-ary relationship (excluding all-1-side cases): create a new relation; include foreign keys to all participating entity types; the foreign keys coming from the many-side entity types form the primary key; include the relationship’s own simple attributes.

(No N-ary relationships in the Company database — WORKSON is binary.)

Step “+1” — Super/subclass mapping (EER)

Works for any combination of total/partial and disjoint/overlapping subclasses. For each subclass: create a relation; its primary key is the superclass’s primary key; include a foreign key back to the superclass’s relation; include the subclass’s own simple attributes.

EMPLOYEE disjointly specialises into SECRETARY/ENGINEER:

Secretary [ssn, typingSpeed]
Secretary.ssn references Employee.ssn

Engineer [ssn, engineerType]
Engineer.ssn references Employee.ssn

This step doesn’t happen at one fixed position in the 7-step sequence — it’s usually done right after step 1 or 2 (whichever applies to the superclass), specifically so the subclass relations already exist by the time any later relationship-mapping step needs to reference them.

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

Worked example — Bank database

A bank (unique code, name, hoAddr) has branches (branchNo unique per-bank, addr). A branch has accounts (acNo, type, balance, ≥1 account holders) and loans (loanNo, type, amount, ≥1 loan holders). Customers (ssn, name, address) can hold accounts and/or loans.

Working through the same 7 steps:

After step 1 (entity mapping):

Bank [code, name, hoAddr]

Account [acNo, type, balance]

Loan [loanNo, type, amount]

Customer [ssn, name, address]

After step 2 (BRANCH is a weak entity, owned by BANK):

Branch [bankCode, branchNo, addr]
Branch.bankCode references Bank.code

After step 4 (BRANCH 1:N ACCOUNT/LOAN — foreign keys added to the N side; note the FK is composite here, since Branch’s own key is composite):

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}

After step 5 (ACCOUNT/LOAN M:N CUSTOMER — new relations):

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

Final schema:

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

Applied Class 3: Relational Integrity

Practice for 2026-03-09-the-relational-model-and-integrity-constraints. See relational-mapping-notation for reference.

Section A — Recap and preview

Question 1 — Matching relational concepts

Match each concept to its description: (1) any minimal set of attributes that can uniquely identify tuples in a relation, (2) defines the structure of a relation, specified during database design, (3) a candidate key chosen to identify tuples in a relation, (4) data in a relation, modified via create/update/delete operations.

Candidate key → 1, Relational Schema → 2, Primary Key → 3, Relational Instance → 4.

Question 2 — Electoral roll schema

Given:

Region [prefix, suffix]

Member [enrolmentId, passportNum, regionPrefix, regionSuffix]
Member.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

Candidate [enrolmentId, name, regionPrefix, regionSuffix]
Candidate.enrolmentId references Member.enrolmentId
Candidate.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

List all examples of each component: candidate key, primary key, composite primary key, foreign key.

  • Candidate key: Member.enrolmentId, Member.passportNum, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Primary key: Member.enrolmentId, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Composite primary key: Region.{prefix, suffix}.
  • Foreign key: Member.{regionPrefix, regionSuffix}, Candidate.{regionPrefix, regionSuffix}, Candidate.enrolmentId.

Question 3 — Schema statements

Select all true statements: (1) a schema is the metadata, or data describing the data, (2) a schema is specified during database design, (3) a schema is created during data updates and changes frequently, (4) a schema is the data in the database at a particular time.

Enter the correct option numbers in ascending order, comma-separated:

1, 2. A schema is metadata specified at design time — it doesn’t change with every data update (that’s the instance), and it isn’t itself “the data” (statements 3 and 4 both describe the instance, not the schema).

Question 4 — Key statements

Select all true statements: (1) a key is a unique identifier of a tuple in the relation, (2) the key constraint implies no two tuples can share the same values for all attributes, (3) a key cannot have multiple attributes, (4) an entity may have more than one primary key, (5) a primary key is a candidate key chosen as the main key for a relation.

Enter the correct option numbers in ascending order, comma-separated:

1, 5. (2) is a weaker, different notion (that’s just basic set semantics — no two tuples are ever fully identical in a relation regardless of keys — not what makes something a key specifically). (3) is false — composite keys exist (e.g. Region.{prefix, suffix} above). (4) is false — an entity can have several candidate keys, but only one of them is chosen as the primary key.

Section B — Integrity constraints in practice

A company database: Employee [ssn, fName, mInit, lName, bDate, address, sex, salary, superSsn, dNo], Department [dName, dNumber, mgrSsn, mgrStartDate], Project [pName, pNumber, pLocation, dNum], WorksOn [essn, pNo, hours], Dependent [essn, depName, sex, bDate, relationship]. Two extra business rules: an employee’s salary cannot exceed their supervisor’s, and a department cannot have more than three active projects at a time. For each operation below, does it violate an integrity constraint (assume any earlier operations in this list have not been applied)?

B1 — Insert ('Robert', 'F', 'Scott', 943775543, '1942-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', 50000, 888665555, 1) into Employee (943775543 and 888665555 are both not currently Employee SSNs other than as noted; 888665555 belongs to the company president, whose salary far exceeds 50000).

B2 — Insert ('ProductA', 'A', 'Bellaire', 2) into Project (no Department with dNumber = 2 exists).

B3 — Insert ('Production', 4, 943775543, '1988-10-01') into Department (a Department with dNumber = 4 already exists; no Employee with ssn = 943775543 exists).

B4 — Insert (677678989, null, 40.0) into WorksOn (no Employee with ssn = 677678989 exists).

B5 — Insert ('Grace', 'G', 'Chan', 31203126, '1962-06-21', '2312 Anchor Rd, Bellaire, TX', 'F', 58000, 888665555, 1) into Employee (supervisor 888665555 earns less than 58000).

B6 — Delete the WorksOn tuples with essn = 333445555.

B7 — Delete the Employee tuple with ssn = 987654321.

B8 — Modify mgrSsn/mgrStartDate of the Department tuple with dNumber = 5 to 123456789/'1988-10-01' (123456789 is an existing Employee SSN).

B9 — Modify superSsn of the Employee tuple with ssn = 999887777 to 943775543 (no Employee with ssn = 943775543 exists).

B10 — Insert ('ProductSuperDuperSecret', 86, 'Washington', 5) into Project (department 5 already has three active projects).

  • B1 No — this is a well-formed, non-conflicting insert: the new ssn doesn’t already exist (no key violation), the supervisor ssn does exist (no referential violation), and the salary doesn’t exceed the supervisor’s (no semantic violation).
  • B2 Yes — Referential integrity (dNum = 2 doesn’t exist in Department) and domain constraint (pNumber should be an integer, not the string 'A').
  • B3 Yes — Key constraint (dNumber = 4 already exists) and referential integrity (mgrSsn = 943775543 doesn’t exist in Employee).
  • B4 Yes — Entity integrity (pNo, part of WorksOn’s primary key, is NULL) and referential integrity (essn = 677678989 doesn’t exist in Employee).
  • B5 Yes — Semantic constraint (this employee’s salary exceeds their supervisor’s).
  • B6 No — nothing else references WorksOn rows as a foreign key target, and no business rule requires an employee to have work assignments, so deleting them is unconstrained.
  • B7 Yes — Referential integrity (WorksOn, Dependent, Department, and Employee itself all have tuples referencing this ssn, which would be left dangling).
  • B8 No — 123456789 is a valid existing employee, dNumber = 5 already exists (this is an update, not a duplicate insert), and no stated business rule constrains who can be a department’s manager.
  • B9 Yes — Referential integrity (the new superSsn value doesn’t match any existing Employee.ssn).
  • B10 Yes — Semantic constraint (exceeds the “max 3 active projects per department” business rule).

Case Study 3: Dirt Road Driving

Group case study applying ER-to-relational mapping from 2026-03-16-er-to-relational-mapping to a revised Dirt Road Driving EER diagram (the EER diagram itself is week2-tutorial-case-study-1-dirt-road-driving’s, refined per that case study’s open-ended Section B). See relational-mapping-notation for reference.

Section A — Full relational mapping

Elaine’s team maps the revised EER diagram (User, Staff disjointly specialising into Driver/Admin, Vehicles disjointly specialising into 4WD/2WD, weak entities EmergencyContact and TripStop, the ternary-ish Trip, and the UserRatesDriver/ UserRatesVehicle relationships) to a full relational schema.

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

Driver [id, licence]
Driver.id references Staff.id

Admin [id, deskNumber]
Admin.id references Staff.id

4WD [vin, rideHeight, wheelType]
4WD.vin references Vehicles.vin

2WD [vin, frontWheelDrive]
2WD.vin references Vehicles.vin

EmergencyContact [userID, name, email, phone]
EmergencyContact.userID references User.id

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Driver.id
Trip.vin references Vehicles.vin

TripStop [userID, driverID, vin, bookingTime, location]
TripStop.{userID, driverID, vin, bookingTime} references Trip.{userID, driverID, vin, bookingTime}

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

UserRatesVehicle [userID, vin, rating]
UserRatesVehicle.userID references User.id
UserRatesVehicle.vin references Vehicles.vin

Key points:

  • Driver/Admin and 4WD/2WD are standard disjoint-subclass mappings — each subclass’s primary key is its superclass’s key, plus a foreign key back to the superclass.
  • EmergencyContact is a weak entity owned by User — its real key is (userID, name) (owner’s key + its own partial key name), not just name alone (a name is only guaranteed unique per user).
  • StaffPhone maps Staff’s multivalued Phone attribute — its primary key is (id, phone) (the multivalued attribute joins the owner’s key to form the key, per the multivalued-attribute mapping rule), which makes sense of why phone isn’t shown as a single extra column on Staff itself.
  • Trip is identified by the combination of userID, driverID, vin, and bookingTime — a trip is only unique per that combination (the same user/driver/vehicle triple could recur across different bookings).
  • TripStop is a weak entity too, owned by Trip — it needs Trip’s entire composite key as a foreign key, plus its own partial key location.
  • UserRatesDriver/UserRatesVehicle are both M:N relationships (each gets its own new relation, combining both sides’ foreign keys as its primary key, plus the relationship’s own rating attribute).

Section B — Spot the mistakes

A junior admin partially mapped the same diagram:

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

EmergencyContact [name, userID, email, phone]
EmergencyContact.userID references User.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Staff.id
Trip.vin references Vehicles.vin

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

Driver [id, licence]
Driver.id references Staff.id

4WD [vin, make, model, rideHeight, wheelType]
4WD.vin references Vehicles.vin

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Find 5 mistakes (ignore “missing tables” — some relations from Section A are simply left out here, and that’s not one of the 5).

  1. EmergencyContact’s primary key is missing userID. As a weak entity, its key must be (userID, name), not name alone — otherwise two different users couldn’t each have an emergency contact with the same name (e.g. two users both listing a contact named “Mum”).
  2. Trip’s primary key is missing bookingTime. Without it, a user couldn’t take two separate trips with the same driver and vehicle — bookingTime is exactly what distinguishes repeat trips between the same pair.
  3. Trip.driverID incorrectly references Staff.id instead of Driver.id. This would let an Admin-only staff ID (someone who isn’t a Driver at all) be entered as a trip’s driver, corrupting the data and undermining any audit that assumes driverID always names an actual driver.
  4. 4WD redundantly repeats make/model. These are already stored on Vehicles (the superclass) — duplicating them wastes space and risks the two copies going out of sync.
  5. StaffPhone’s primary key is missing phone. As the mapping of a multivalued attribute, the key must be (id, phone) — without phone in the key, each staff member could only ever have one phone number on file, defeating the purpose of the multivalued attribute allowing several.

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}