INFS1200 — Week 1 Notes

Conceptual Database Design and the ER Model

Module 1, part 1. See er-diagram-notation and relationship-constraints for the reference symbol legend and cardinality/participation definitions this lecture introduces.

Today’s outline

  • Conceptual database design
  • Entities and relationships
  • Relationship constraints

Conceptual database design

  • Step 1: identify the “Universe of Discourse” (UoD) — the database models some “mini-world”/UoD, not everything in the world.
  • Step 2: convert the UoD into a data model that a database can capture.
  • A model is never perfect — three categories of phenomena around any conceptual schema: common phenomena (captured in the model, e.g. “every employee works for a department”), phenomena not captured (e.g. “some employees go out for dinner on Fridays” — irrelevant, left out), and phenomena not true in the world (e.g. an over-general assumption like “every secretary can type”).

The ER model

The Entity-Relationship (ER) model provides a graphical representation of data entities — it helps define a project’s scope/requirements for clients and businesses. An entity is a physical or conceptual object with data (attributes) associated with it; the same real-world entity can have different attributes recorded depending on the system’s requirements (e.g. an R&D organisation records an employee’s Degree/Field of Study, a retail organisation records Sales Experience instead).

An entity type provides the format (name + attributes) for recording a particular kind of entity — drawn as a rectangle, with attribute ovals attached. The entity set is the collection of all entities of one entity type in the database at a point in time (maps to a table).

Attributes

  • Key attribute: every entity type has at least one — its value is unique for each entity in the entity set (name underlined). Multiple keys are possible, and a key must hold for every possible extension of the entity type.
  • Composite vs simple: a composite attribute (e.g. Name) can be split into simple attributes with independent meaning (FirstName, MiddleName, LastName).
  • Composite key: a combination of simple attributes that together must be unique (e.g. a car’s Registration = State + Number, alongside a separate simple key VehicleID) — an entity type can have several candidate keys like this.
  • Single-valued vs multivalued: a multivalued attribute (double-lined oval) can hold more than one value per entity (e.g. Degree — one person can hold multiple degrees).
  • Stored vs derived: a derived attribute (dashed oval) is computed from another stored attribute (e.g. Age derived from BirthDate).
  • Value sets: the set of legal values for an attribute (e.g. employeeAge: integers 21-65) — never shown on the diagram itself. A null value represents an inapplicable, unknown, or missing value.

In-class exercise — Student entity

Every student has a unique id, name (title/first/middle initial/last), email, address (number/street/suburb/postcode), and one or more phone numbers. Draw an ER diagram.

STUDENT entity type with key attribute ID; composite attribute Name (→ Title, Fname, Initial, Lname); simple attribute Email; composite attribute Address (→ Number, Sname, Suburb, Pcode); and multivalued attribute Phone (double-lined oval, since a student can have more than one phone number).

flowchart TD
    ID(["ID (key)"]) --- STUDENT[STUDENT]
    Name(["Name"]) --- STUDENT
    Title(["Title"]) --- Name
    Fname(["Fname"]) --- Name
    Initial(["Initial"]) --- Name
    Lname(["Lname"]) --- Name
    Email(["Email"]) --- STUDENT
    Address(["Address"]) --- STUDENT
    Number(["Number"]) --- Address
    Sname(["Sname"]) --- Address
    Suburb(["Suburb"]) --- Address
    Pcode(["Pcode"]) --- Address
    Phone(["Phone (multi)"]) --- STUDENT

Relationships

  • A relationship is an association among two or more entities (e.g. “Paris Lane works on the project FileZilla”). A relationship type defines it — drawn as a diamond connected to its entity types, and may have its own descriptive/key attributes.
  • Relationship degree = number of participating entity types: binary (2, e.g. EMPLOYEE WORKSFOR DEPARTMENT), ternary (3, e.g. SUPPLIER SUPPLIES PART via a PROJECT), or n-ary (3+) in general.
  • Roles: each participating entity type plays a named role in the relationship, explaining what the relationship means (e.g. DEPARTMENT is the Employer, EMPLOYEE is the Worker in WORKSON).
  • Recursive relationships: the same entity type can participate more than once in one relationship type, under different roles (e.g. EMPLOYEE MANAGES EMPLOYEE, with Manager/Subordinate roles).
  • The relationship set is the collection of all relationship instances of one relationship type at a point in time.

Question — Entities and Relationships (courses/students)

Store info about students, courses, courses taken, and grades. Courses have a number/department/title (numbers assigned per-department, so different departments may reuse a number); students have a unique student ID and name; students enrol in courses and receive a grade.

COURSES (composite key Key = Dept + Number, plus Title) ↔︎ ENROLMENTS (attribute Grade) ↔︎ STUDENTS (key ID, Name) — an M:N relationship, since a student can enrol in many courses and a course has many enrolled students.

flowchart LR
    ID(["ID (key)"]) --- STUDENTS[STUDENTS]
    NameS(["Name"]) --- STUDENTS
    STUDENTS ---|"N"| ENROLMENTS{ENROLMENTS}
    ENROLMENTS ---|"M"| COURSES[COURSES]
    Grade(["Grade"]) --- ENROLMENTS
    CourseKey(["Key (key)"]) --- COURSES
    Dept(["Dept"]) --- CourseKey
    Number(["Number"]) --- CourseKey
    Title(["Title"]) --- COURSES

Relationship constraints

See relationship-constraints for the cardinality ratio (1:1/1:N/M:N) and participation constraint (total/partial) definitions.

Worked example — EMPLOYEE/DEPARTMENT:

  • WORKSFOR: N:1 — “each department can have any number of employees, but an employee can work for at most one department”, and EMPLOYEE has total participation (“every employee must work for a department”).
  • MANAGES: 1:1 — “each department can have at most one manager and each employee can manage at most one department”; DEPARTMENT has total participation (“every department must have a manager”) while EMPLOYEE’s participation is partial (“every employee can manage 0 or more departments”).
  • WORKSON (EMPLOYEE/PROJECT): M:N — “each employee can work on any number of projects, and each project can have any number of employees working on it”.

flowchart LR
    EMPLOYEE1[EMPLOYEE] ===|"N"| WORKSFOR{WORKSFOR}
    WORKSFOR ---|"1"| DEPARTMENT[DEPARTMENT]
    EMPLOYEE1 ---|"1"| MANAGES{MANAGES}
    MANAGES ===|"1"| DEPARTMENT


flowchart LR
    EMPLOYEE2[EMPLOYEE] ---|"M"| WORKSON{WORKSON}
    WORKSON ---|"N"| PROJECT[PROJECT]

Exercise — ABC Banks

Model a bank with branches (unique name, city, budget, rating), customers (name + phone, address), accounts and loans (unique number, created/ maintained by a single branch), where an account is assigned to ≥1 customers and a loan is assigned to a single customer.

BRANCH (Name, City, Budget, Rating) —1:N OPENS→ ACCOUNT (ID, Balance); BRANCH —1:N GIVES→ LOAN (ID, Rate, Balance); CUSTOMER (ID, Name, Phone, Address) —M:N OWNS→ ACCOUNT; CUSTOMER —1:N TAKES→ LOAN.

flowchart TD
    BRANCH[BRANCH] ---|"1"| OPENS{OPENS}
    OPENS ---|"N"| ACCOUNT[ACCOUNT]
    BRANCH ---|"1"| GIVES{GIVES}
    GIVES ---|"N"| LOAN[LOAN]
    CUSTOMER[CUSTOMER] ---|"M"| OWNS{OWNS}
    OWNS ---|"N"| ACCOUNT
    CUSTOMER ---|"1"| TAKES{TAKES}
    TAKES ---|"N"| LOAN

    NameBr(["Name (key)"]) --- BRANCH
    City(["City"]) --- BRANCH
    Budget(["Budget"]) --- BRANCH
    Rating(["Rating"]) --- BRANCH

    IDa(["ID (key)"]) --- ACCOUNT
    BalanceA(["Balance"]) --- ACCOUNT

    IDl(["ID (key)"]) --- LOAN
    Rate(["Rate"]) --- LOAN
    BalanceL(["Balance"]) --- LOAN

    IDc(["ID (key)"]) --- CUSTOMER
    NameC(["Name"]) --- CUSTOMER
    Phone(["Phone"]) --- CUSTOMER
    AddressC(["Address"]) --- CUSTOMER

Practical 1: Course Tools

Introduces the two tools used throughout the course: diagrams.net (draw.io) for building ER diagrams, and Gradescope for submitting assessment. This practical itself is not assessable — it’s onboarding practice before Assignment 1.

Task 1 — Recreate the “Always a Gamer” diagram in draw.io

UoD: a fictional gaming company’s GAME entity (Name, Year, Language, Description, Console) specialises two separate, disjoint (d) ways:

  • How it’s played: SINGLEPLAYER (Characters) or MULTIPLAYER (MinPlayers, MaxPlayers).
  • Whether it can be won: WINNABLE, or NON-WIN (Purpose).

NON-WIN games also have a recursive SEQUEL relationship to other NON-WIN games (roles PrequelTo/SequelTo, 1:1).

flowchart TD
    GAME[GAME] --- d1(("d"))
    d1 --- SINGLEPLAYER[[SINGLEPLAYER]]
    d1 --- MULTIPLAYER[[MULTIPLAYER]]
    GAME --- d2(("d"))
    d2 --- WINNABLE[[WINNABLE]]
    d2 --- NONWIN[["NON-WIN"]]
    NONWIN ---|"1 PrequelTo"| SEQUEL{SEQUEL}
    SEQUEL ---|"1 SequelTo"| NONWIN

    Name(["Name"]) --- GAME
    Year(["Year"]) --- GAME
    Language(["Language"]) --- GAME
    Description(["Description"]) --- GAME
    Console(["Console"]) --- GAME
    Characters(["Characters"]) --- SINGLEPLAYER
    MinPlayers(["MinPlayers"]) --- MULTIPLAYER
    MaxPlayers(["MaxPlayers"]) --- MULTIPLAYER
    Purpose(["Purpose"]) --- NONWIN

Recreate this EER diagram yourself in draw.io — see the course’s linked draw.io usage guide for the mechanics (shapes, connecting entities to a relationship diamond, exporting).

Task 2 — Using the ER diagram

Question 1 — Identifying entities

Which entity type is the superclass that every other entity type in this diagram specialises from (directly or indirectly)?

GAME is the superclass. Its two disjoint specialisations are SINGLEPLAYER/MULTIPLAYER (one d-circle) and WINNABLE/NON-WIN (a separate d-circle) — four subclasses total, all ultimately specialising GAME.

Task 2.2 — Exporting the diagram

Export the finished diagram from draw.io as an image and insert it into the practical’s Gradescope submission template — no fixed “right answer” here, just confirms the export step works.

Task 3 — Submitting via Gradescope

Submit via the Gradescope link on the course Blackboard site. Not assessable — purely to confirm your Gradescope account is set up correctly ahead of Assignment 1.

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.