Designing an ER Diagram for a University Research Database
Press Next → or use ← → arrow keys
The Story — A Brief Lands on Your Desk
| Symbol | Means | Symbol | Means |
|---|---|---|---|
| ▭ Rectangle | Entity | ◇ Diamond | Relationship |
| ⬭ Ellipse | Attribute | — Single line | Partial participation |
| ⬭ Underlined | Key attribute | ═ Double line | Total participation |
| 1 / N / M labels | Cardinality on the edges | ||
Find the Entities & Their Attributes
PROFESSOR has no naturally unique attribute (names and ranks repeat). So we introduce a surrogate key Prof_ID — without an underlined key, the entity simply cannot be identified.
Turn the Verbs Into Relationships
| Brief says… | Relationship | Entities | Cardinality |
|---|---|---|---|
| Each project has one principal investigator | Manages | Professor–Project | 1:N |
| Projects have multiple co-investigators | Works_On | Professor–Project | M:N |
| Grads work on projects under supervision | Supervises | Professor–Graduate–Project | Ternary |
| Each department has a chairman | Runs | Professor–Department | 1:1 |
| Professors work in departments (with time %) | Works_In | Professor–Department | M:N |
Professor and Project are joined by two separate diamonds — Manages (the PI) and Works_On (co-investigators) — because they are distinct roles with different cardinalities.
Manages — One Principal Investigator (1:N)
Each project is managed by exactly one professor; one professor may manage many projects. The project side has total participation — every project must have a principal investigator.
Works_On — Many Co-Investigators (M:N)
A project is worked on by one or more professors, and professors work on many projects — pure M:N. This is the second diamond between the same pair: Works_On (co-investigators) sits alongside Manages (the PI), because collapsing them would erase the principal-investigator rule.
Supervises — The Ternary Relationship
"A grad works on Robotics under Prof. Rao, and on AI under Prof. Mehta." Supervision depends on the combination of student and project. Splitting it into two binary relationships would lose which project the professor supervises the student on — so it must be a single ternary diamond.
Runs — Department Chairman (1:1)
Each department has exactly one chairman, and a professor chairs at most one department — 1:1. But participation differs: department is total (every dept must have a chairman), professor is partial (most professors chair nothing).
Works_In — M:N With a Relationship Attribute
A professor split across two departments has a different time percentage in each — it varies per professor–department pairing. So Time_Pct hangs off the diamond, never off PROFESSOR or DEPARTMENT alone.
The Complete ER Diagram
Four entities, five relationships. PROFESSOR connects to all others; PROJECT and DEPARTMENT are the leaf nodes. Manages and Works_On run in parallel to PROJECT; Runs and Works_In in parallel to DEPARTMENT; and the purple ternary Supervises ties Professor, Graduate and Project together.
Key & Participation Constraints
| Relationship | Degree | Cardinality | Total on | Key constraint |
|---|---|---|---|---|
| Manages | Binary | 1:N | PROJECT | each project → 1 professor |
| Works_On | Binary | M:N | PROJECT | — |
| Supervises | Ternary | Ternary | PROJECT | (Graduate, Project) → 1 professor |
| Runs | Binary | 1:1 | DEPARTMENT | each dept → 1 chairman |
| Works_In | Binary | M:N | PROFESSOR | attribute: Time_Pct |
Design Decisions & Assumptions
Three Common Mistakes to Avoid
Every mistake here comes from ignoring what a fact depends on — a role, a pairing, or an identity. Model the dependency, not just the words.
The Resulting Relational Schema
Applying the mapping rules: entities → tables, 1:N → FK, M:N & ternary → junction tables.
-- Entities
PROFESSOR (Prof_ID PK, Name, Age, Rank, Specialty)
PROJECT (Pno PK, Sponsor, Start_Date, End_Date, Budget, PI_Prof_ID FK) -- Manages 1:N
GRADUATE (UID PK, Name, Age, Deg_Prog)
DEPARTMENT (Dno PK, Dname, Main_Office, Chair_Prof_ID FK UNIQUE) -- Runs 1:1
-- Relationship tables
WORKS_ON (Prof_ID FK, Pno FK) -- M:N · PK=(Prof_ID,Pno)
WORKS_IN (Prof_ID FK, Dno FK, Time_Pct) -- M:N + attribute
SUPERVISES (Prof_ID FK, UID FK, Pno FK) -- ternary · PK=(UID,Pno)SUPERVISES uses (UID, Pno) as its primary key — encoding the rule that each (graduate, project) pair has exactly one supervising professor. The 1:N and 1:1 links needed no new table, just a foreign key.
Golden Rules for Designing From a Brief — 1 to 3
Golden Rules for Designing From a Brief — 4 to 6
Good ER design is disciplined reading. Find the nouns, name the verbs, ask what each fact depends on, and mark what's mandatory — the diagram falls out of the brief.
From Brief to Blueprint
You've taken an unstructured brief all the way to a precise ER diagram — with a surrogate key, a ternary relationship, two roles between the same pair, and a relationship attribute — then converted it to tables. This is exactly the workflow real database design follows.
Nouns are entities, verbs are relationships, adjectives are attributes — and always ask what a fact depends on before deciding where it lives.
🎓 End of tutorial · Press ← to review, or click Restart