The Story — A Brief Lands on Your Desk
A seasoned designer reads such a brief like a grammar exercise: nouns become entities, verbs become relationships, and describing words become attributes. Do that carefully, add the key and participation constraints, and the diagram almost draws itself.
Rectangle = entity • Ellipse = attribute • Underlined ellipse = key • Diamond = relationship • Single line = partial participation • Double line = total participation • labels 1 / N / M = cardinality.
Step 1 — Find the Entities and Attributes
Scanning the brief for the main "things", four nouns stand out. Each becomes an entity, and the describing words become its attributes.
| Entity | Attributes | Key |
|---|---|---|
| PROFESSOR | Name, Age, Rank, Specialty | none given → add Prof_ID |
| PROJECT | Pno, Sponsor, Start_Date, End_Date, Budget | Pno |
| GRADUATE | UID, Name, Age, Deg_Prog | UID |
| DEPARTMENT | Dno, Dname, Main_Office | Dno |
The brief lists a professor's name, age, rank, and specialty — but none of these is guaranteed unique (two professors can share a name). Every entity needs a primary key, so we introduce a surrogate key Prof_ID (an SSN or staff number would serve the same purpose). We will underline it as the key.
Keys (amber, underlined) uniquely identify each entity. Note the introduced Prof_ID on PROFESSOR.
Step 2 — Turn the Verbs Into Relationships
Each line of the brief that connects two (or three) entities is a relationship. Here is every requirement mapped to its relationship, ready to draw.
| Brief says… | Relationship | Connects | Type |
|---|---|---|---|
| Each project has one PI | Manages | Professor – Project | 1 : N |
| Projects have co-investigators | Works_On | Professor – Project | M : N |
| Grads work on projects, supervised by a prof | Supervises | Professor – Graduate – Project | ternary |
| Each dept has a chairman | Runs | Professor – Department | 1 : 1 |
| Profs work in depts, with a time % | Works_In | Professor – Department | M : N |
Professor and Project are joined by two different relationships — Manages (principal investigator) and Works_On (co-investigators). They mean different things, so they are separate diamonds. The same pair, Professor–Department, also carries two: Runs and Works_In.
Manages — The Principal Investigator (1:N)
"Each project is managed by one professor." So every project points to exactly one PI, while one professor may manage many projects — a 1:N relationship. Because every project must have a PI, the Project side has total participation (double line).
Double line on PROJECT = total participation: no project may exist without a principal investigator.
Works_On — The Co-Investigators (M:N)
"Each project is worked on by one or more professors… professors can work on multiple projects." Many professors, many projects — a classic M:N relationship. "One or more professors" makes the Project side total.
Manages and Works_On look alike but differ in cardinality: a project has exactly one manager, but many co-investigators.
Supervises — The Tricky Ternary Relationship
The relationship Supervises connects Professor, Graduate, and Project. The key constraint: each (Graduate, Project) pair has exactly one supervising professor.
Double line to PROJECT = total: every project has at least one grad working on it (and thus a supervisor). The "1" toward PROFESSOR captures the unique-supervisor key constraint.
Modelling "Graduate–works–Project" and "Professor–supervises–Graduate" as two separate binary relationships loses the link to the project — you could no longer tell which project a professor supervises a student on. The single ternary diamond preserves all three connections together.
Runs — The Department Chairman (1:1)
"Departments have a professor (the chairman) who runs the department." Each department has exactly one chairman, and a professor chairs at most one department — a 1:1 relationship. Every department must have a chairman, so the Department side is total.
Professor participation is partial (most professors are not chairs); Department participation is total (every department is run by someone).
Works_In — With a Relationship Attribute (M:N)
"Professors work in one or more departments, and for each department they work in, a time percentage is associated." This is M:N — but the interesting part is Time_Pct: it belongs to neither Professor nor Department alone, but to the pairing. So it is a descriptive attribute on the relationship.
Double line on PROFESSOR = total participation (every professor works in at least one department). Time_Pct describes this professor in this department.
The Complete ER Diagram
Now assemble every piece. PROFESSOR sits at the centre because it touches everything; the five relationships radiate out to the other three entities. Watch it build up.
Five relationships on four entities: Runs (1:1), Manages (1:N), Works_On (M:N), Works_In (M:N + Time_Pct), and the ternary Supervises.
Key & Participation Constraints at a Glance
The brief asked us to indicate key and participation constraints. Here they are, gathered in one place.
| Relationship | Degree | Cardinality | Total participation | Key constraint |
|---|---|---|---|---|
| Manages | Binary | 1 : N | PROJECT | Each project → 1 professor |
| Works_On | Binary | M : N | PROJECT | — |
| Supervises | Ternary | see note | PROJECT | (Graduate, Project) → 1 professor |
| Runs | Binary | 1 : 1 | DEPARTMENT | Each dept → 1 chairman |
| Works_In | Binary | M : N | PROFESSOR | — (attribute: Time_Pct) |
| Entity | Primary Key |
|---|---|
| PROFESSOR | Prof_ID (introduced) |
| PROJECT | Pno |
| GRADUATE | UID |
| DEPARTMENT | Dno |
Design Decisions & Assumptions
Common Mistakes to Avoid
They connect the same two entities but mean different things and have different cardinalities (1:N vs M:N). Collapsing them loses the "principal investigator" rule entirely.
Placing Time_Pct on PROFESSOR implies one fixed percentage per professor, but the brief says it varies per department. A relationship attribute is the only correct home.
It is tempting to draw PROFESSOR with only the four given attributes. But with no underlined key, the entity cannot be identified — always check that every strong entity has exactly one key.