The Entity-Relationship (ER) model is a conceptual data model used to design databases visually before implementation. It represents the real world as entities (objects), their attributes (properties), and relationships between them. ER diagrams are translated into relational tables using systematic mapping rules. The ER model is the starting point for every database design — whether for a hospital, an e-commerce platform, or a banking application. Critical GATE topic appearing in almost every year.
Real-life analogy: The architect blueprint
An architect draws a blueprint before constructing a building. The ER diagram is the blueprint of a database. Entities are rooms, attributes are room dimensions, and relationships are doors connecting rooms. You design on paper first — catching flaws cheaply before writing a single SQL statement.
Core ER components
| Component | Symbol | Example | Description |
|---|---|---|---|
| Strong Entity | Rectangle | Student, Course, Employee | A real-world object with independent existence |
| Weak Entity | Double rectangle | Dependent, OrderItem | Exists only if related strong entity exists — has no key of its own |
| Key Attribute | Underlined oval | StudentID, SSN | Uniquely identifies an entity instance |
| Multi-valued Attr | Double oval | PhoneNumbers, Skills | Can have multiple values for one entity |
| Derived Attr | Dashed oval | Age (from DOB) | Computed from other attributes — not stored |
| Composite Attr | Oval with sub-ovals | FullName = First + Last | Made up of multiple sub-attributes |
| Relationship | Diamond | Enrolls, Works_In | Association between two or more entities |
Translating ER diagram to relational tables
-- ER: Student(StudentID PK, Name, DOB)
-- ER: Course(CourseID PK, Title, Credits)
-- ER: Enrolls_In = M:N relationship with attribute Grade
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DOB DATE
);
CREATE TABLE Course (
CourseID VARCHAR(10) PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Credits INT CHECK (Credits BETWEEN 1 AND 6)
);
-- M:N relationship becomes a junction table
CREATE TABLE Enrolls_In (
StudentID INT REFERENCES Student(StudentID),
CourseID VARCHAR(10) REFERENCES Course(CourseID),
Grade CHAR(2),
EnrollDate DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (StudentID, CourseID) -- Composite PK
);Cardinality and participation constraints
| Cardinality | Meaning | Example |
|---|---|---|
| 1:1 | One entity relates to exactly one other | Employee MANAGES Department |
| 1:N | One entity relates to many others | Department HAS_MANY Employees |
| M:N | Many entities relate to many others | Student ENROLLS_IN Course |
Total participation (double line) means every instance must participate. Partial participation (single line) means participation is optional. In SQL, total participation on a FK side means NOT NULL.
- Strong entity: Becomes a table. Key attribute becomes PRIMARY KEY.
- Weak entity: PK = owner PK + partial key. Has FK to owner.
- 1:1 relationship: Add FK to either side (prefer total-participation side).
- 1:N relationship: FK goes on the N-side (many) table.
- M:N relationship: Creates a separate junction table with composite PK.
- Multi-valued attribute: Separate table with entity PK + the attribute.
- Derived attribute: NOT stored — computed via SQL when needed.
Practice questions (GATE-style)
- Student has StudentID, Name, PhoneNumbers (multi-valued), DOB, Age (derived). How many tables result from correct ER mapping? (Answer: 2 tables — Student(StudentID, Name, DOB) and StudentPhone(StudentID, PhoneNumber). Age is derived so not stored.)
- Weak entity Dependent has partial key DepName, owner Employee has PK EmpID. What is the PK of Dependent table? (Answer: (EmpID, DepName) — composite PK using owner PK + partial key.)
- In M:N between Student and Course with relationship attribute Grade, where does Grade go? (Answer: In the junction table Enrolls_In(StudentID, CourseID, Grade).)
- Total participation on a FK side translates to SQL as: (Answer: NOT NULL constraint on the foreign key column.)
- Employee MANAGES Department is 1:1. Where should the FK go? (Answer: On the side with total participation, or merge into one table if both sides are total participation.)
On LumiChats
When you describe a system to LumiChats and ask it to design a database schema, it generates ER diagrams and SQL CREATE TABLE statements following ER-to-relational mapping rules. Try: 'Design a database for a hospital with patients, doctors, and appointments.'
Try it free