Modeling practice: English requirements to ER diagram

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What is the difference between disjoint and overlapping specialisation? from Lesson 1.2.4
2. When is an entity type weak? from Lesson 1.2.3
3. In Chen notation, what does a double line mean? from Lesson 1.2.2

1 · The Hook

In the exam, ER questions rarely say "here is a 1:N relationship". They say: "Each hostel has a unique name and a warden. Rooms are numbered within each hostel. A student may be allotted at most one room…". You must turn English into a diagram. This lesson gives a fixed procedure and practises it on one complete description.

2 · Predict First

"A department offers many courses; each course is offered by exactly one department." Which words decide the cardinality ratio, and which decide participation?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

"many" and "exactly one" decide the ratio (maximum): 1:N from DEPARTMENT to COURSE. "exactly one" also says every course has a department, so COURSE's participation is total (minimum 1). Nothing says every department must offer a course, so DEPARTMENT's participation is partial.

The six-step procedure

1. Entity types: nouns the organisation keeps several facts about. Check each has a key; if its identifier only makes sense inside another entity ("room 101 of hostel H"), it is weak.
2. Attributes: facts about one entity. Mark keys, composite, multivalued, derived.
3. Relationships: verbs linking entity types ("enrols in", "offers", "is allotted"). Name each one.
4. Cardinality ratios: for each binary relationship ask two questions: "one A can link to how many B?" and "one B can link to how many A?"
5. Participation: "must every A take part?" (words: every, each, must, exactly one, at least one → total; may, can, some → partial).
6. Relationship attributes: facts that depend on the pair (grade, allotted_on). Check whether they could move to the N side.

Phrase table

Worked description: the hostel system

"Each hostel has a unique name and a warden. Rooms are numbered within each hostel and have a capacity. Every student has a unique roll number and a name. A student may be allotted at most one room; a room may hold several students. The date of allotment is recorded. Each student belongs to exactly one department; a department, identified by its name, has a building and many students."

1. Entity types: HOSTEL (key hname), ROOM (weak: "numbered within each hostel"), STUDENT (key roll), DEPARTMENT (key dname).
2. Attributes: HOSTEL(hname, warden); ROOM(room_no partial key, capacity); STUDENT(roll, name); DEPARTMENT(dname, building).
3. Relationships: HAS(HOSTEL, ROOM) identifying; ALLOTTED(STUDENT, ROOM); BELONGS(STUDENT, DEPARTMENT).
4. Ratios: HAS 1:N; ALLOTTED N:1 (student → room: at most one room; room: several students); BELONGS N:1.
5. Participation: ROOM total in HAS (weak); STUDENT partial in ALLOTTED ("may"); STUDENT total in BELONGS ("exactly one"); others partial.
6. allotted_on is an attribute of ALLOTTED; since ALLOTTED is N:1, it could also move to STUDENT.

4 · Visual

ER diagram for the hostel description (only key attributes and the relationship attribute are shown).

5 · Worked Examples

Example 1 Easy

"Each book has a unique ISBN and a title. A member may borrow many books; a book is borrowed by at most one member at a time." Give the ratio and participations of BORROWS(MEMBER, BOOK).

Solution

Ratio: 1:N from MEMBER to BOOK. Participation: MEMBER partial ("may"), BOOK partial (a book may be on the shelf).

Example 2 Medium

"Each patient is treated by at least one doctor, and each doctor treats many patients. The date of each treatment is stored." Give the ratio, participations and where the date goes.

Solution

M:N (patients have one or more doctors; doctors have many patients). PATIENT total ("at least one"), DOCTOR partial (unless stated). treat_date is an attribute of TREATS; because the relationship is M:N it cannot move to an entity.

Example 3 GATE

"Each order is placed by exactly one customer. Order lines are numbered 1, 2, 3 … within each order and record a product and quantity." Identify the weak entity, its owner, its partial key, and the participation of ORDER in PLACED_BY.

Solution

Weak entity ORDER_LINE, owner ORDER, partial key line_no; full key (order_id, line_no). ORDER participates totally in PLACED_BY ("exactly one customer").

8 · Connect the Dots

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Reading constraints

"Every employee works in exactly one department; a department may have no employees." In WORKS_IN(EMPLOYEE, DEPARTMENT):

1. (A) both partial
2. (B) EMPLOYEE total, DEPARTMENT partial
3. (C) EMPLOYEE partial, DEPARTMENT total
4. (D) both total

Q2 · MCQ · 1 mark(s) — Weak entity cue

"Each match is numbered 1, 2, 3 … within each tournament." MATCH is:

1. (A) a subclass of TOURNAMENT
2. (B) a weak entity with owner TOURNAMENT
3. (C) a strong entity with key match_no
4. (D) a multivalued attribute of TOURNAMENT

Q3 · MCQ · 2 mark(s) — Ratio

"A professor can guide many PhD students; each PhD student has one or two guides." GUIDES(PROFESSOR, STUDENT) is:

1. (A) N:1
2. (B) 1:1
3. (C) 1:N
4. (D) M:N

Q4 · MSQ · 2 mark(s) — Relationship attributes

"A student registers for many courses; each course has many students. The registration date is stored." Which are correct?

1. (A) The registration date is an attribute of the relationship.
2. (B) The date can be moved to STUDENT.
3. (C) The relationship is M:N.
4. (D) The date can be moved to COURSE.

Q5 · MCQ · 1 mark(s) — Specialisation cue

"Some employees are engineers, who have an engineering grade." ENGINEER should be:

1. (A) a multivalued attribute
2. (B) a relationship
3. (C) a weak entity of EMPLOYEE
4. (D) a subclass of EMPLOYEE with local attribute grade

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

ER modelling procedurereading cardinality from Englishreading participation from Englishweak-entity cue ("numbered within")subclass cue ("is a kind of")