Notes › DBMS
ER Model and Relational Schema Design
Before creating tables, designers draw an Entity–Relationship (ER) diagram: a picture of the real-world things the database stores and how they relate. It is then converted into tables.
Building blocks
| Concept | Meaning | Drawn as | Example |
|---|---|---|---|
| Entity | a real-world thing | rectangle | Student, Course |
| Entity set | all entities of one type | rectangle | all students |
| Attribute | a property | oval | name, email |
| Relationship | an association between entities | diamond | Student enrolls in Course |
| Weak entity | can't be identified by its own attributes | double rectangle | Dependent of an Employee |
Types of attributes
| Type | Meaning | Example | Drawn as |
|---|---|---|---|
| Simple | can't be divided | age | oval |
| Composite | made of parts | name → first, last | oval with sub-ovals |
| Single-valued | one value | date of birth | oval |
| Multi-valued | many values | phone numbers | double oval |
| Derived | computed from others | age from date of birth | dashed oval |
| Key | uniquely identifies the entity | roll number | underlined |
Cardinality (mapping constraints)
How many entities on one side can relate to how many on the other:
| Cardinality | Example |
|---|---|
| 1 : 1 | Person — has — Passport |
| 1 : N | Department — has — Employees |
| M : N | Student — enrolls in — Course |
Participation
- Total participation (double line): every entity must take part. Every Employee must work in a Department.
- Partial participation (single line): some may not. Not every Employee manages a Department.
Weak entities
A weak entity has no primary key of its own; it is identified by its owner (strong) entity + a partial key (discriminator), through an identifying relationship (double diamond). It always has total participation.
Example: Dependent(name, age) of an Employee — two employees can both have a child named "Riya", so the key is (emp_id, dependent_name).
Extended ER features
- Generalization (bottom-up): combine
CarandBikeintoVehicle. - Specialization (top-down): split
EmployeeintoEngineerandManager. - Inheritance: subclasses get all attributes of the superclass.
- Constraints: disjoint (an entity belongs to one subclass) vs overlapping; total vs partial.
- Aggregation: treat a relationship as an entity so it can take part in another relationship (e.g. Employee works on Project is monitored by a Manager).
Converting ER → tables (the exam favourite)
| ER element | Becomes |
|---|---|
| Strong entity | a table; key attribute = primary key |
| Composite attribute | one column per part (first_name, last_name) |
| Multi-valued attribute | a separate table (entity_key, value) |
| Derived attribute | usually not stored (compute it) |
| Weak entity | table with owner's key + partial key as primary key; FK to owner (ON DELETE CASCADE) |
| 1 : 1 relationship | FK on either side (prefer the side with total participation), or merge tables |
| 1 : N relationship | FK on the N side (Employee gets dept_id) |
| M : N relationship | a new junction table with both keys as a composite primary key + relationship attributes |
| Specialization | one table per subclass with the superclass key, or one table with a type column |
Worked example — a college
Student(roll_no, name, phone*) * = multi-valued
Course(course_id, title, credits)
Department(dept_id, name)
Student —M:N— Course (attribute: grade)
Department —1:N— Course
Tables:
CREATE TABLE department (dept_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE course (
course_id INT PRIMARY KEY,
title VARCHAR(100),
credits INT,
dept_id INT REFERENCES department(dept_id) -- 1:N → FK on the N side
);
CREATE TABLE student (roll_no INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE student_phone ( -- multi-valued attribute
roll_no INT REFERENCES student(roll_no),
phone VARCHAR(15),
PRIMARY KEY (roll_no, phone)
);
CREATE TABLE enrollment ( -- M:N → junction table
roll_no INT REFERENCES student(roll_no),
course_id INT REFERENCES course(course_id),
grade CHAR(2),
PRIMARY KEY (roll_no, course_id)
);
Minimum number of tables (GATE-style)
- 1:N or 1:1 with no attributes → 2 tables (FK goes into one side).
- M:N → 3 tables.
- 1:1 with total participation on both sides → can be merged into 1 table.
Common design mistakes
- Storing a comma-separated list in one column (breaks 1NF — use a separate table).
- Putting the FK on the wrong side of a 1:N relationship.
- Forgetting the junction table for M:N.
- Repeating data (department name in every employee row) → normalise it (Keys and Normalization).
Interview one-liners
- Weak entity = no own key; identified by owner key + partial key.
- M:N always needs a separate table.
- Multi-valued attributes become their own table.
- Cardinality says how many; participation says must or may.
- Related: SQL Essentials and Joins.