DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).
E-R Model
Modelling the real world as entities and the relationships between them — before a single table is written.
🌍 Where this lives: the E-R diagram is the one artefact that survives every technology change in a project. Teams argue about frameworks and databases, but "a student enrols in many courses; a course has one department" is true regardless. That is why every ORM makes you declare it — Django's ForeignKey and ManyToManyField, Rails' belongs_to and has_many, Prisma's relation fields are all E-R relationships expressed in code. Draw the diagram wrong and the schema encodes the mistake permanently, because fixing a cardinality error later means migrating live data. Search "database design mistakes cardinality".
The three building blocks
1. ENTITY — a distinguishable thing about which we store data
Student, Course, Department, Book, Invoice
ENTITY TYPE the definition (Student)
ENTITY SET the collection of current instances
{Ram, Sita, Hari}
Drawn as a RECTANGLE.
2. ATTRIBUTE — a property of an entity
roll, name, marks, date_of_birth
Drawn as an ELLIPSE, connected to its entity.
The KEY attribute is UNDERLINED.
3. RELATIONSHIP — an association among entities
Student ENROLS IN Course
Department OFFERS Course
Drawn as a DIAMOND between the participating entities.
NOTATION SUMMARY (Chen notation, which exams use):
┌──────────┐ rectangle entity type
╔══════════╗ double rect WEAK entity type
◇ diamond relationship
◈ double diam identifying relationship
○ ellipse attribute
(○) double ell MULTIVALUED attribute
◌ dashed dashed ell DERIVED attribute
___ underline KEY attribute
--- dashed under partial key (weak entity)
── single line PARTIAL participation
══ double line TOTAL participation
Attribute types
SIMPLE (atomic) cannot be divided further
roll, marks, age
COMPOSITE made of sub-parts, each meaningful
name → (first_name, middle, last)
address → (street, city, district, pin)
Drawn as an ellipse with child ellipses.
SINGLE-VALUED exactly one value per entity
date_of_birth, roll
MULTIVALUED several values per entity
phone_numbers, email_addresses, skills
Drawn as a DOUBLE ellipse.
→ in the relational model this ALWAYS
becomes a separate table (see below)
DERIVED computed from other attributes,
not stored
age from date_of_birth
total from unit_price × quantity
Drawn as a DASHED ellipse.
STORED actually kept on disk (the opposite of
derived)
KEY uniquely identifies an entity
roll for Student
Drawn UNDERLINED.
NULL permitted when a value is unknown or
inapplicable — not a type, but a state
every attribute may or may not allow.
WHY MULTIVALUED ATTRIBUTES MUST BECOME TABLES:
A relation in first normal form requires every attribute to
be atomic. So `phone` holding {9841xxxxxx, 9851xxxxxx}
cannot be one column. It becomes:
student_phone(roll, phone) with (roll, phone) as the key
That single rule — multivalued attribute → new table — is
the most common conversion step in the exam.
Cardinality and participation
CARDINALITY RATIO — how many entities may participate
1:1 ONE to ONE
Employee MANAGES Department
one employee manages at most one department, and
one department has at most one manager
1:N ONE to MANY
Department EMPLOYS Employee
one department has many employees; one employee
belongs to one department
M:N MANY to MANY
Student ENROLS IN Course
many students per course, many courses per student
PARTICIPATION CONSTRAINT — must it participate?
TOTAL (mandatory) every entity MUST take part
drawn as a DOUBLE line
→ becomes NOT NULL on the foreign key
PARTIAL (optional) an entity MAY take part
drawn as a SINGLE line
→ the foreign key allows NULL
"Every employee must belong to a department"
→ Employee's participation in WORKS_FOR is TOTAL
→ employee.dept_code is NOT NULL
"A department may have no employees"
→ Department's participation is PARTIAL
STRUCTURAL CONSTRAINT = cardinality + participation together.
It is the pair that determines the schema, which is why exam
questions always ask for both.
MIN-MAX (alternative) NOTATION:
Employee (1,1) ─── WORKS_FOR ─── (0,N) Department
↑ min,max ↑ min,max
min = 0 → partial; min = 1 → total
This notation is more precise and increasingly preferred.
Converting E-R to tables — the complete rules
RULE 1 — STRONG ENTITY → its own table
the key attribute becomes the PRIMARY KEY
Student(roll, name, dob) → student(roll PK, name, dob)
RULE 2 — COMPOSITE ATTRIBUTE → flatten into components
name(first, middle, last) → first_name, middle_name,
last_name
RULE 3 — MULTIVALUED ATTRIBUTE → a NEW table
Student.phone (multivalued) →
student_phone(roll FK, phone)
PRIMARY KEY (roll, phone)
RULE 4 — DERIVED ATTRIBUTE → do NOT store it
compute it in a query or a view:
AGE(dob) as a generated column or view expression
RULE 5 — 1:1 RELATIONSHIP → a foreign key on EITHER side
put it on the side with TOTAL participation, and add UNIQUE
department(code PK, ..., mgr_id UNIQUE REFERENCES
employee(id))
Both a UNIQUE and a FK are required — the UNIQUE is what
makes it 1:1 rather than 1:N.
RULE 6 — 1:N RELATIONSHIP → foreign key on the "N" side
employee(id PK, name, dept_code REFERENCES
department(code))
NEVER create a separate table for a 1:N relationship. That
is the single most common mistake in exam answers.
RULE 7 — M:N RELATIONSHIP → a NEW JUNCTION table
enrolment(roll FK, course_code FK, semester, grade)
PRIMARY KEY (roll, course_code)
Relationship ATTRIBUTES (semester, grade) live here — there
is nowhere else they can go.
RULE 8 — WEAK ENTITY → a table whose PK is
(owner's PK + partial key), with ON DELETE CASCADE
RULE 9 — MULTIWAY (ternary) RELATIONSHIP → one table with a
foreign key to each participant, PK = all three
er_to_sql.sql
-- ===== The E-R design being converted =====
-- Department(code, name) strong entity
-- Student(roll, name, dob, {phone}, /age/) strong, with a
-- multivalued and a derived attribute
-- Course(code, title, credits) strong entity
-- Department OFFERS Course 1:N, total on Course
-- Student ENROLS IN Course M:N, attrs semester+grade
-- Professor ADVISES Student 1:N, partial both sides-- RULE 1: strong entities
CREATE TABLE department (
code CHAR(6) PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE professor (
id INTEGER PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_code CHAR(6) NOT NULL REFERENCES department(code)
);
-- RULE 2: composite name flattened
-- RULE 4: age is DERIVED, so it is NOT a stored column
CREATE TABLE student (
roll INTEGER PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
middle_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
-- RULE 7 (1:N, ADVISES): FK on the N side, NULLable
-- because participation is PARTIAL
advisor_id INTEGER REFERENCES professor(id)
);
-- RULE 3: the multivalued phone attribute becomes a tableCREATE TABLE student_phone (
roll INTEGER NOT NULL REFERENCES student(roll)
ON DELETE CASCADE,
phone VARCHAR(15) NOT NULL,
PRIMARY KEY (roll, phone)
);-- RULE 6: OFFERS is 1:N, so the FK goes on Course.
-- Total participation on Course -> NOT NULL.
CREATE TABLE course (
code CHAR(8) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
credits SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 6),
dept_code CHAR(6) NOT NULL REFERENCES department(code)
);
-- RULE 7: M:N becomes a junction table, and the
-- RELATIONSHIP ATTRIBUTES live here
CREATE TABLE enrolment (
roll INTEGER NOT NULL REFERENCES student(roll)
ON DELETE CASCADE,
course_code CHAR(8) NOT NULL REFERENCES course(code),
semester SMALLINT NOT NULL CHECK (semester BETWEEN 1 AND 8),
grade CHAR(2),
PRIMARY KEY (roll, course_code, semester)
);
-- RULE 4: the derived attribute as a VIEW, computed not stored
CREATE VIEW v_student AS
SELECT roll,
first_name || COALESCE(' ' || middle_name, '')
|| ' ' || last_name AS full_name,
dob,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)) AS age
FROM student;
-- RULE 5 example: 1:1 MANAGES needs UNIQUE, not just FK
ALTER TABLE department
ADD COLUMN head_id INTEGER UNIQUE REFERENCES professor(id);
-- UNIQUE is what enforces 1:1. Without it this is 1:N —
-- one professor could head many departments.
Three conversion decisions worth defending in an exam:
1. WHY enrolment's PK includes semester.
With PK (roll, course_code) a student could never repeat
a failed course. Adding semester allows a retake while
still preventing a duplicate enrolment in the SAME
semester. The choice of primary key encodes a business
rule.
2. WHY advisor_id IS NULLABLE but course.dept_code IS NOT.
Participation. A student may have no advisor yet
(partial), so NULL is allowed. Every course MUST belong
to a department (total), so NOT NULL. That is how the
double-line/single-line distinction becomes SQL.
3. WHY head_id NEEDS BOTH FK AND UNIQUE.
The foreign key says "this must be a real professor".
The UNIQUE says "no professor appears twice", which is
what makes the relationship 1:1 rather than 1:N. Omitting
UNIQUE is a silent cardinality error — the schema permits
data the model forbids.
ON DELETE CASCADE on student_phone and enrolment is
deliberate: a phone number or enrolment has no meaning
without its student, so deleting the student should remove
them. Compare course.dept_code, which has NO cascade —
deleting a department with courses should FAIL, not silently
destroy the courses.
Point 3 is the one most often missed. A foreign key alone always produces a 1:N relationship, because nothing stops the same value appearing in many rows. Turning it into 1:1 requires UNIQUE. If an exam asks you to convert a 1:1 relationship and you write only a foreign key, the schema does not match the model.
Worked example — a ternary relationship
Some relationships genuinely involve THREE entities and
cannot be decomposed into three binary ones.
SUPPLIER supplies PART to PROJECT
Drawn as one diamond with three lines:
SUPPLIER ──┐
├── ◇ SUPPLIES ── PROJECT
PART ──────┘
WHY NOT THREE BINARY RELATIONSHIPS: because
Supplier–Part (this supplier can supply this part)
Part–Project (this project needs this part)
Supplier–Project (this supplier serves this project)
together do NOT capture "supplier S supplies part P TO
project J". The three pairwise facts can all be true while
that specific triple is false.
CONVERSION — one table, FK to each, PK = all three:
CREATE TABLE supplies (
supplier_id INTEGER NOT NULL REFERENCES supplier(id),
part_no INTEGER NOT NULL REFERENCES part(no),
project_id INTEGER NOT NULL REFERENCES project(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL,
PRIMARY KEY (supplier_id, part_no, project_id)
);
Verifying it holds the right information:
supplier | part | project | qty | price
---------+-------+---------+-----+-------
S1 | bolt | metro | 500 | 12.50
S1 | bolt | bridge | 800 | 12.00 ← same S,P other J
S2 | bolt | metro | 300 | 13.00 ← same P,J other S
S1 | nut | metro | 500 | 4.75
Row 1 and 2 differ only in project; rows 1 and 3 only in
supplier. Three separate binary tables could not distinguish
these, and the PRICE differing per triple proves the fact is
genuinely three-way.
total for the metro project
= 500×12.50 + 300×13.00 + 500×4.75
= 6250 + 3900 + 2375
= 12,525
🌍 Go further: the E-R model has an extended form — EER — that adds specialisation and generalisation (an is-a hierarchy), aggregation, and categories. That is where inheritance in databases lives, and there are three standard ways to map it to tables: single-table with a discriminator, one table per class, or one table per concrete class. Every ORM implements at least two of them, and choosing wrong causes either sparse NULL-filled tables or expensive joins. Search "table per hierarchy vs table per type" and "EER specialization generalization".
💡 Exam angle: know the Chen notation symbols cold — rectangle, diamond, ellipse, double versions for weak/multivalued, dashed for derived, underline for key, double line for total participation. Classify the attribute types (simple, composite, multivalued, derived, key). The highest-value skill is E-R to relational conversion: 1:N puts the FK on the N side, M:N needs a junction table, a multivalued attribute becomes its own table, and 1:1 needs FK plus UNIQUE. Expect to be given a scenario and asked to draw the diagram and produce the schema.
Syllabus points
Entities, relationships, relationship types
Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.
Related topics in Data Models, Normalization, and SQL