ER diagrams
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)"Design the database for a library, a shop or a college" is a favourite interview task. The interviewer watches your order: entities first, then relationships with their cardinalities, then tables and keys.
Entities and attributes (simple, composite, multivalued, derived)
An entity is a thing you store data about, such as a student or a company. An entity set is all entities of one kind, drawn as a rectangle; its attributes are ovals, and the key attribute is underlined.
A placement cell stores students and their details. Some details are single values, some are lists, and some can be worked out from others. The program shows one derived attribute computed instead of stored.
Example 1 · A derived attribute is computed, not stored
from dataclasses import dataclassfrom datetime import date
@dataclassclass Student: roll_no: str # key attribute name: tuple[str, str] # composite attribute: first + last dob: date phones: list[str] # multivalued attribute
def age_on(self, day): # derived attribute: computed, never stored had_birthday = (day.month, day.day) >= (self.dob.month, self.dob.day) return day.year - self.dob.year - (not had_birthday)
s = Student("21CS045", ("Priya", "Sharma"), date(2003, 8, 15), ["98450 11111", "080 2345 6789"])drive_day = date(2025, 9, 1)
print("Key attribute:", s.roll_no)print("Composite name:", " + ".join(s.name))print("Multivalued phones:", len(s.phones), "values")print("Derived age on drive day:", s.age_on(drive_day))Key attribute: 21CS045 Composite name: Priya + Sharma Multivalued phones: 2 values Derived age on drive day: 22
What this code does
- The composite name has two parts, the multivalued phone list has several values, and age is derived from the date of birth whenever it is needed.
- Storing age would make it wrong a year later; storing date_of_birth and computing age keeps it always correct. The drive date is fixed so the output never changes.
Attributes come in four kinds that interviewers ask by name. Each kind changes how it becomes a column later:
- Simple (atomic): cannot be split, such as roll_no or cgpa.
- Composite: made of parts, such as name (first, last) or address (street, city, pin).
- Multivalued (double oval): can hold several values, such as phone numbers or skills.
- Derived (dashed oval): computed from others, such as age from date_of_birth; usually not stored. Revise: Entities and ER diagrams (opens in a new tab).
Predict the output
Which kind of attribute?
Read the entity, then pick the answer you would say aloud.
Round 1 of 3
A student can give several phone numbers. Which attribute is multivalued?
Relationships and cardinality (1:1, 1:N, M:N)
Cardinality says how many entities on one side of a relationship can be linked to one entity on the other side: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N).
A relationship connects entities and is drawn as a diamond: a student applies to a drive. Cardinality says how many entities on one side relate to one on the other.
Company ──1────conducts────N── DriveStudent ──M────applies_to───N── Drive (attribute: status)Student ──1────has──────────1── LockerExample 2 · Reading cardinality from real rules
1. Each student has exactly one college ID card; each card belongs to one student.2. A hostel room houses up to three students; each student lives in one room.3. A student registers for many electives; each elective has many students.4. A mentor guides many students; each student has one mentor.5. A company can visit many colleges; a college invites many companies.1. Student - IDCard 1:1 2. Room - Student 1:N (one room, many students) 3. Student - Elective M:N 4. Mentor - Student 1:N 5. Company - College M:N
What this code does
- Ask the question twice, once from each side: "one student has how many cards?" and "one card belongs to how many students?".
- "Up to three" in rule 2 is still the "many" side; cardinality notation only distinguishes one from many.
One-to-one: a student has one hostel locker. One-to-many: one company conducts many drives, and each drive belongs to one company. Many-to-many: a student applies to many drives, and a drive receives many students. A relationship can also carry its own attributes, such as the application date or status.
Find the bug
Find the wrong mapping line
Each snippet maps a relationship to tables. Tap the wrong line.
One department has many professors (1:N).
Round 1 of 3Weak entities and participation
A weak entity cannot be identified by its own attributes alone. It depends on an owner (strong) entity through an identifying relationship and is drawn as a double rectangle.
A fee instalment numbered 1, 2, 3 means nothing without the student who pays it. That makes it a weak entity. Participation says whether every entity must take part in a relationship.
[[FeeInstalment]] ==pays_by== [Student]FeeInstalment key = (roll_no, instalment_no) -- owner's key + partial keyExample 3 · A weak entity and its key
Drive (strong): drive_id, company_id, drive_dateRound (weak): round_no, round_type, round_dateIdentifying relationship: Drive --has--> Round (total participation of Round)Sample rows of Round: round_no 1, Aptitude (drive D101) round_no 2, Technical (drive D101) round_no 1, Aptitude (drive D102)round_no alone repeats (1 appears in D101 and D102) -> not a key. Partial key: round_no (unique within one drive). Full key of Round: (drive_id, round_no). Every round belongs to some drive -> total participation (double line).
What this code does
- A round has no identity outside its drive, which is exactly what makes it weak.
- Its table will borrow drive_id from the owner and combine it with round_no for the primary key.
The weak entity's partial key is unique only within one owner. Total participation (double line) means every entity takes part: every drive belongs to a company. Partial participation (single line) means some may not: not every student applies to a drive. A weak entity always has total participation in its identifying relationship.
Converting an ER diagram to tables
A junction table turns a many-to-many relationship into two one-to-many ones. It holds the primary keys of both entities as foreign keys, and those columns together form its primary key.
Turning a diagram into tables follows fixed rules, which is why interviewers like it: there is one right answer. The SQL shows the M:N rule, the one asked most often.
Example 4 · Mapping an M:N relationship to a junction table
Runs in: MySQL 8
CREATE TABLE student (roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(60));CREATE TABLE drive (drive_id INT PRIMARY KEY, company VARCHAR(60));CREATE TABLE application ( -- M:N: student applies_to drive roll_no VARCHAR(10), drive_id INT, status VARCHAR(20), -- attribute of the relationship PRIMARY KEY (roll_no, drive_id), FOREIGN KEY (roll_no) REFERENCES student(roll_no), FOREIGN KEY (drive_id) REFERENCES drive(drive_id));INSERT INTO student VALUES ('21CS045', 'Priya'), ('21CS046', 'Rahul');INSERT INTO drive VALUES (101, 'Infosys'), (102, 'Zoho');INSERT INTO application VALUES ('21CS045', 101, 'Selected'), ('21CS045', 102, 'Applied'), ('21CS046', 101, 'Rejected');SELECT s.name, d.company, a.statusFROM application a JOIN student s ON s.roll_no = a.roll_no JOIN drive d ON d.drive_id = a.drive_idORDER BY s.name, d.company+-------+---------+----------+ | name | company | status | +-------+---------+----------+ | Priya | Infosys | Selected | | Priya | Zoho | Applied | | Rahul | Infosys | Rejected | +-------+---------+----------+
What this code does
- Neither student nor drive can hold the other's key (each side has many), so the relationship becomes its own table with both keys.
- The relationship attribute
statuslives in the junction table, because it belongs to one student's application to one drive.
Apply the rules entity by entity, then relationship by relationship:
- Strong entity → table with its simple attributes; composite attributes become their parts; derived ones are usually left out.
- Multivalued attribute → separate table (owner key, value), with both as the primary key.
- 1:N → put the "one" side's key as a foreign key in the "many" side's table. 1:1 → foreign key on either side (prefer the total-participation side), with UNIQUE.
- M:N → a new junction table with both keys as a composite primary key, plus the relationship's attributes. Weak entity → table with owner key + partial key as the primary key. Revise: Many-to-many join tables (opens in a new tab) and One-to-one and one-to-many (opens in a new tab).
Order of execution
Diagram to tables
Tap the mapping steps in the order you would do them.
Worked example: a college placement system
The picture shows the core of the placement system as an ER diagram and as tables. The example below adds the skills and the interview rounds.
Example 5 · The placement system: ER to six tables
Entities: Student(roll_no, name(first, last), dob, cgpa, {skills}) Company(company_id, name, website) Drive(drive_id, drive_date, ctc_lpa)Weak entity: Round(round_no, round_type) owned by DriveRelationships: Company 1:N conducts Drive (Drive total participation) Student M:N applies_to Drive (attribute: status)student(roll_no PK, first_name, last_name, dob, cgpa) -- age derived, not stored student_skill(roll_no FK, skill, PK(roll_no, skill)) -- multivalued company(company_id PK, name, website) drive(drive_id PK, company_id FK NOT NULL, drive_date, ctc_lpa) -- 1:N, total round(drive_id FK, round_no, round_type, PK(drive_id, round_no)) -- weak application(roll_no FK, drive_id FK, status, PK(roll_no, drive_id)) -- M:N
What this code does
- Each line applies one mapping rule: composite split into parts, multivalued to its own table, 1:N as a foreign key, weak entity with the owner's key, M:N as a junction table.
company_id NOT NULLin drive records total participation: a drive cannot exist without its company.
The rules: a company conducts many drives; a student applies to many drives and each drive receives many applications, each with a status; a student can list several skills; each drive has rounds numbered 1, 2, 3.
The rules give entities Student, Company and Drive, a weak entity Round (owned by Drive), a multivalued attribute skills, a 1:N relationship conducts and an M:N relationship applies_to with an attribute status.
Next step
Check your understanding
12 quick questions