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
Conceptual Database Design and the ER Model
Module 1, part 1. See er-diagram-notation and relationship-constraints for the reference symbol legend and the cardinality/participation definitions introduced here.
Module outline
Module 1 has five sections. This note covers the first three, and 2026-03-02-weak-entities-the-eer-model-and-design-choices covers the last two:
- Database design overview
- Entities and relationships
- Relationship constraints
- The EER diagram — next lecture
- Design choices — next lecture
The module answers three questions: why conceptual modelling matters for a successful database application; how the Entity-Relationship model is used to do it; and how to evaluate trade-offs between design choices by comparing alternative ER diagrams for the same system.
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”).
See conceptual-database-design, which draws the two-step pipeline and the three-phenomena picture.
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).
The whole notation fits on one diagram, which is the target to work towards. Every symbol on it is defined later in this note or the next:
Figure 1: An example company ER diagram. Everything the module teaches is on it: entity types (purple rectangles), relationship types (magenta diamonds), key attributes (underlined ovals), cardinality ratios (1/N/M on the edges), total participation (double edges — every employee must work for a department, every department must have a manager) and an EER disjoint specialisation (the d circle). The diagram exists to gauge how the system works and how data within it is connected.
Attributes
- Key attribute: every entity type has at least one key (or uniqueness) constraint — its value is distinct for each individual 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 (Fname,Mname,Lname). - Composite key: a combination of simple attributes that together must be unique (e.g. a car’s
Registration=State+Number, alongside a separate simple keyVehicleID) — an entity type can have several candidate keys like this. - Single-valued vs multivalued: the split is stated over simple attributes. 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 (dotted oval) is computed from a related attribute value (e.g.
AgefromBirthDate). - Value sets: the set of values that may be assigned to an attribute (e.g.
employeeAge: integers between 21 and 65;vehicleRegistrationNum: a string of three alphabetic characters followed by three integers) — never shown on the diagram itself. Anullvalue represents an inapplicable (tertiaryDegree), unknown (homePhone) or missing (height) value.
entity-types-and-attributes draws the EMPLOYEE figure with every kind of attribute on it, and the CAR candidate-key figure.
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).
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 the associated entity types. A relationship type may have descriptive attributes, and it may have key attributes as well:
Figure 2: Relationship types carry attributes of their own. WORKSON records a StartDate and an EndDate for each employee/project pairing; StartDate is underlined, so it is a key attribute of the relationship, not merely a descriptive one — a fact the mapping stage has to preserve. The row underneath is the instance-level reading: Paris Lane works on FileZilla.
- Relationship degree = the number of participating entity types: binary (2 entities, e.g.
DEPARTMENT WORKSFOR EMPLOYEE), ternary (3 entities, e.g. a singleSUPPLIESdiamond joiningSUPPLIER,PROJECTandPART), or n-ary (3+) in general. - Roles: each entity type participating in a relationship type plays a particular role, and the role name explains what the relationship means. In
DEPARTMENT WORKSON EMPLOYEE,DEPARTMENTis the Employer andEMPLOYEEis the Worker. - Recursive relationships: the same entity type can participate more than once in one relationship type, under different roles —
EMPLOYEEMANAGESEMPLOYEE, with the two lines labelled Manager and Subordinate. - The relationship set is the collection of all relationships of one relationship type at a point in time. The same relationship set has three representations: the ER fragment (
EMPLOYEEN —WORKSON— MPROJECT), a ball-and-line instance diagram, and a three-column table (WorksOnID,EmployeeID,ProjectID), which is the shape the relationship eventually takes once mapped (er-to-relational-mapping).
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
The composite key comes from the clause that numbers are assigned by departments and different departments may use the same number: Number alone is not unique, so Dept + Number together are the key.
Relationship constraints
Constraints on a relationship type limit the possible combinations of entities that may participate in the corresponding relationship set, and both kinds are determined by the UoD — they are read off the description, not chosen for convenience. There are two:
| Constraint | What it fixes |
|---|---|
| Cardinality constraint | How many relationships of the set one entity can participate in |
| Participation constraint (also called existence dependency) | Whether an entity has to participate at all |
See relationship-constraints for what each ratio and each participation constraint asserts.
Cardinality ratio
A cardinality ratio for a relationship set specifies the number of relationships in the set that an entity can participate in. Take a customer buying items: one customer can purchase many different items, but an individual item (a particular cake) cannot be purchased by several different customers.
Figure 3: The four cardinality ratios as instance diagrams, colour coded: purple 1:1, orange N:1, blue 1:N, green M:N. Each dot is one entity, each line one relationship instance. Which dots are joined is illustrative; the constraint is the shape — a matching (1:1), a fan-in (N:1), a fan-out (1:N), or an unrestricted graph (M:N).
N:1 and 1:N are the same constraint read from opposite ends. The one relationship EMPLOYEE N — WORKSFOR — 1 DEPARTMENT fits both the orange N:1 picture and the blue 1:N picture. Which name you use depends on which entity type you name first, and the course’s notation guide fixes the reading: E1 —1— R —N— E2 is “cardinality ratio 1:N for E1:E2 in R”. The practical rule for reading a diagram is that the number written next to an entity says how many of that entity may take part with one of the other: N beside EMPLOYEE = many employees per department; 1 beside DEPARTMENT = one department per employee.
Three worked examples:
| UoD sentence | Ratio |
|---|---|
“Each employee can work on any number of projects, and each project can have any number of employees working on it” (WORKSON) |
M:N |
“Each department can have any number of employees, but an employee can work for at most one department” (WORKSFOR) |
N:1 (equivalently 1:N the other way) |
“Each department can have at most one manager and each employee can manage at most one department” (MANAGES) |
1:1 |
Participation constraints (existence dependency)
Existence dependency indicates whether the existence of an entity depends on its relationship to another entity. A double line between an entity type and a diamond means total participation; a single line means partial.
- “Every employee must work for a department” —
EMPLOYEEhas total participation inWORKSFOR, soEMPLOYEEis existentially dependent onDEPARTMENTvia theWORKSFORrelationship type. - “Every department must have a manager” —
DEPARTMENThas total participation inMANAGES, soDEPARTMENTis existentially dependent onEMPLOYEEviaMANAGES, whileEMPLOYEE’s participation stays partial (some employees manage nothing, and legally so).
Worked example — EMPLOYEE / DEPARTMENT
Figure 1 above is the diagram. It encodes four sentences:
- Every department must have a manager — total participation of
DEPARTMENTinMANAGES(double line on that edge). - Every employee can manage 0 or more departments — partial participation of
EMPLOYEEinMANAGES, and the1/Nlabels makeMANAGES1:N forEMPLOYEE:DEPARTMENT(a department has at most one manager; an employee may manage several). - Every employee must work for a department — total participation of
EMPLOYEEinWORKSFOR. - Each department can have any number of employees — with
NbesideEMPLOYEEand1besideDEPARTMENT,WORKSFORis N:1.
The 1:1 example in the table above (“each department can have at most one manager and each employee can manage at most one department”) is a different UoD sentence from statement 2: it is drawn 1 — MANAGES — 1, whereas the company diagram is drawn 1 — MANAGES — N. Two sentences, two ratios, same relationship name, so the ratio has to be read off the UoD and not off the verb.
WORKSON (EMPLOYEE M:N PROJECT) completes the picture: each employee can work on any number of projects, and each project can have any number of employees on it.
Question — Relationship constraints
Figure 4: Entity types A, B and C joined by relationship types R and S. A contains a1, a2; B contains b1, b2; C contains c1, c2. Which relationship sets for R and S are possible? The double line into C is the whole difficulty — it constrains the instances, not just the schema.
A. R = {}, S = {}
B. R = {(a1,b1)}, S = {(b2,c2)}
C. R = {(a1,b1), (a1,b2)}, S = {(b1,c1), (b2,c2)}
D. R = {(a1,b2)}, S = {(b1,c2), (b2,c1), (b1,c1)}
(D). Two constraints have to hold at once:
Ris N:1 forA:B, so eachamay appear at most once inR. That kills C, wherea1is paired with bothb1andb2.Chas total participation inS(the double line), so everycmust appear inS. That kills A (nothing appears) and B (c1is missing).Sbeing M:N places no further limit, so D — which lists bothc1andc2and pairs eachaat most once inR— is legal.
Exercise — ABC Banks
Model a bank with branches (unique name, city, budget, rating 1–10), customers (identified by name + phone, address recorded), accounts and loans (unique number, created/maintained by a single branch), where an account is assigned to one or more customers (balance never negative) and a loan is always assigned to a single customer (fixed interest rate, balance never negative).
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
Two clauses in the brief are constraints the notation cannot carry: “its balance can never be negative” is a value-set restriction, and value sets are never shown on an ER diagram — it resurfaces later as a domain constraint (integrity-constraints). “Each account and loan is created and maintained by a single branch” is the participation constraint that makes ACCOUNT and LOAN total in OPENS/GIVES.