Entity-Relationship (ER) Model

The ER model, proposed by Peter Chen in 1976, describes data as entities, their attributes and the relationships among them.

Concept	ER-diagram symbol	Example
Strong entity	Rectangle	Student
Weak entity	Double rectangle	Dependent (of Employee)
Attribute	Ellipse	name
Key attribute	Ellipse, underlined	roll_no
Multivalued attribute	Double ellipse	phone_numbers
Derived attribute	Dashed ellipse	age (from DOB)
Composite attribute	Ellipse with sub-ellipses	name → first, last
Relationship	Diamond	Enrolls
Identifying relationship	Double diamond	Has (Employee–Dependent)

ER diagram: Students enroll in Courses (M:N). Underlined = key; double ellipse = multivalued

Cardinality Ratios

1:1: a Person has one Passport
1:N: a Department has many Employees
M:N: Students enroll in many Courses; Courses have many Students

Participation

Total (double line): every entity must participate, e.g. every Loan must belong to a Branch. Partial (single line): participation is optional.

Converting ER to Tables

Each strong entity → a table.
1:N relationship → put the primary key of the "1" side as a foreign key in the "N" side.
M:N relationship → a separate table containing both primary keys.
Multivalued attribute → a separate table.

SQL

-- M:N  Student <-> Course
CREATE TABLE Enrolls (
    roll_no   INT REFERENCES Student(roll_no),
    course_id INT REFERENCES Course(course_id),
    grade     CHAR(2),
    PRIMARY KEY (roll_no, course_id)
);

Track your progress through the DBMS course

← Prev Next →