ER Model in DBMS: Cardinality, Diagrams and Mapping | Part 2

DBMS Part 2: ER model aur database design. Part 1 mein relational model, primary aur foreign key samjhe the. Ab tables banane se pehle real-world requirements ko diagram mein kaise badalte hain, woh dekhenge. Yeh general CSE notes hain, kisi ek university ka official syllabus nahi.

1. ER model: proper definition aur use

Definition: Entity-Relationship (ER) model ek conceptual data model hai jo application ke objects (entities), unke properties (attributes) aur objects ke beech associations (relationships) ko represent karta hai. Iska diagram database ki requirements ko table schema se pehle clearly dikhata hai.

Simple Hinglish: College ke system mein Student aur Course alag cheezein hain; student kisi course mein enroll hota hai. ER diagram pehle yeh story banata hai. Uske baad SQL tables decide hote hain. Diagram khud database nahi hota.

2. Entity, attribute, relationship aur key

Entity: identifiable real-world object, jaise ek particular student. Entity set: same type ke objects ka group, jaise sab students. Attribute: entity ki property, jaise student_id, name. Relationship: entities ke beech association, jaise Student enrolls in Course.

Concept	Student-Course example	Design decision
Entity	Student 101	Students table
Attribute	student_id, name	Columns
Relationship	101 enrolls in DBMS	Enrollment link
Key	student_id = 101	Unique identity

Key attribute: entity ko uniquely identify karta hai; name akela reliable key nahi, kyunki do students ka same name ho sakta hai. Composite attribute: address ko street/city mein tod sakte hain. Multivalued attribute: multiple phone numbers; relational design mein separate rows/table useful ho sakti hain. Derived attribute: birth_date se age nikali ja sakti hai; age ko alag store karne par stale hone ka risk hai.

3. Cardinality aur participation ko kaise padhein?

Cardinality: ek entity instance doosri side ke kitne instances se relate ho sakta hai. 1:1 (person-passport, assuming current single passport), 1:N (department-many students), M:N (many students-many courses) common patterns hain. Participation: relationship mandatory hai ya optional. Agar har enrollment ka student hona zaroori hai, Enrollment side ki Student relationship mein total participation hai; lekin ek naya student abhi kisi course mein enrolled na ho sakta hai.

Worked case: Student 101 DBMS aur OS leta hai; Student 102 DBMS leta hai. Ek student ke kai courses aur ek course ke kai students, isliye Student-Course M:N. Isko ek hi Courses column mein "DBMS, OS" daalna weak design hai. Enrollment table mein (101, DBMS), (101, OS), (102, DBMS) teen rows rakho.

4. ER diagram ko relational tables mein badalna

Mapping: regular entity set ke liye table, key ke liye primary key. 1:N mein many-side table par one-side ki foreign key. M:N ke liye junction table banao, jisme dono sides ki foreign keys hoti hain. Enrollment mein student_id aur course_id ka combined primary key repeat enrollment rok sakta hai, jab tak business rule ek hi course ko multiple terms mein repeat allow na kare. Agar repeat allowed hai to term bhi key mein aayega ya separate enrollment_id use hoga.

CREATE TABLE Students (student_id INTEGER PRIMARY KEY, name VARCHAR(80) NOT NULL);
CREATE TABLE Courses (course_id INTEGER PRIMARY KEY, title VARCHAR(80) NOT NULL);
CREATE TABLE Enrollment (
  student_id INTEGER REFERENCES Students(student_id),
  course_id INTEGER REFERENCES Courses(course_id),
  PRIMARY KEY (student_id, course_id)
);

Yeh SQL PostgreSQL-style example hai. Enrollment ke foreign keys nonexistent student/course IDs ko reject karne mein help karte hain. Participation jaise "har student kam se kam ek course le" ko sirf simple FK se enforce nahi kar sakte; application/transaction rule bhi chahiye.

5. Weak entity aur common mistakes

Weak entity: jiski identity owner ke bina complete nahi hoti. Example: OrderItem ka line_no har order mein repeat ho sakta hai, isliye (order_id, line_no) milkar identify karte hain. Har entity ko weak mat bolo sirf isliye ki usmein FK hai. Mistakes: relationship ko attribute bana dena, M:N ko single FK samajhna, name ko guaranteed unique maan lena, aur cardinality ko bina business rule ke guess karna.

6. Diagram read karna: maximum links aur minimum links

Notation books mein different ho sakti hai. Chen notation mein rectangle entity set, diamond relationship, ellipse attribute, double rectangle weak entity aur double ellipse multivalued attribute dikhata hai. Crow's-foot aur min-max diagrams alag symbols use karte hain. Legend padho; ek notation ke symbol ko doosre ka rule mat maan lo.

Conceptual story: Student ↔ enrolls in ↔ Course
Student: 0..many courses
Course: 0..many students
↓ M:N relationship ko junction relation mein map karo
Students [1] ← [many] Enrollment [many] → [1] Courses

Each Enrollment row: exactly one existing Student + exactly one existing Course.
Each Student/Course: zero or more Enrollment rows in this example.

Yeh text diagram business-rule summary hai, full Chen ER diagram nahi. Many-side par optional participation ka matlab Enrollment.student_id NULL hona nahi: ek Student ke liye koi Enrollment row na hona optional participation represent karta hai. Existing Enrollment row ko valid Student aur Course dono chahiye.

Aspect	Question	Example	SQL consequence
Maximum cardinality	At most one ya many?	Student many courses le sakta hai	M:N needs junction table
Minimum participation	Zero allowed ya at least one?	New Student may have zero enrollments	Student may exist without link rows
Enrollment existence	Can a link lack its endpoints?	No, each link has one Student and one Course	NOT NULL plus both FKs
Pair uniqueness	Same student-course pair repeat?	No in this simplified model	Composite PK(student_id,course_id)

7. Mapping rules: 1:1, 1:N, M:N aur weak entity

ER pattern	Relational mapping	Important constraint	Trap
Regular entity	One table; scalar attributes as columns	Choose suitable primary key	Name is not automatically unique
1:N Department-Student	department_id FK in Students, the many side	NOT NULL if every Student needs a Department	This does not force every Department to have a Student
1:1 Person-Passport	FK on a suitable side, or merged table when requirements fit	UNIQUE on FK; NOT NULL for mandatory link on that row	Plain FK alone allows many rows to reference one Person
M:N Student-Course	Enrollment junction table	Both FKs; pair key or key including term	Putting course IDs in a comma-separated column loses relation structure
Multivalued phone	StudentPhone(student_id,phone)	Suitable pair key plus Student FK	phone1, phone2, phone3 creates a fixed arbitrary limit
Weak OrderItem	OrderItem(order_id,line_no,...)	Owner FK plus partial key: PK(order_id,line_no)	line_no alone is not globally unique

1:1 mapping ka single correct FK side har case mein same nahi hota. Optionality, which object owns the lifecycle, and how nulls should behave matter. FK+UNIQUE enforces at most one relationship per referenced entity. Every parent having a child is a separate minimum-participation requirement; simple FK does not enforce that reverse direction.

8. Worked Enrollment design, data aur rejected rows

Neeche complete setup apne empty practice database mein run karo. Displayed setup/query aur four failure tests SQLite 3 par foreign_keys=ON ke saath executed hain. Explicit NOT NULL keeps endpoint rules clear across engines; PostgreSQL also makes every primary-key column not null. SQLite setup needs PRAGMA foreign_keys=ON in each test connection. Error messages engine-specific hain; neeche expected rule bataya hai.

CREATE TABLE Students (student_id INTEGER PRIMARY KEY, name VARCHAR(80) NOT NULL);
CREATE TABLE Courses (course_id INTEGER PRIMARY KEY, title VARCHAR(80) NOT NULL);
CREATE TABLE Enrollment (
  student_id INTEGER NOT NULL REFERENCES Students(student_id),
  course_id INTEGER NOT NULL REFERENCES Courses(course_id),
  PRIMARY KEY (student_id, course_id)
);
INSERT INTO Students VALUES (101,'Asha'),(102,'Ravi'),(103,'Meera');
INSERT INTO Courses VALUES (10,'DBMS'),(20,'OS');
INSERT INTO Enrollment VALUES (101,10),(101,20),(102,10);

SQLite users first run PRAGMA foreign_keys=ON;. Existing PostgreSQL-style CREATE example above remains valid for its stated engine.

SELECT s.name, c.title
FROM Enrollment e
JOIN Students s ON s.student_id=e.student_id
JOIN Courses c ON c.course_id=e.course_id
ORDER BY s.student_id, c.course_id;

name	title
Asha	DBMS
Asha	OS
Ravi	DBMS

Meera (103) exists in Students but has no Enrollment row, so this inner join excludes her. Optional participation is allowed by this schema. It is not a lost or invalid Student record.

Attempt, run separately	Expected	Rule
(101,10), already present	Rejected	Composite primary key duplicate
(999,10)	Rejected	Student 999 missing, FK
(101,99)	Rejected	Course 99 missing, FK
(NULL,10)	Rejected	student_id NOT NULL

INSERT INTO Enrollment VALUES (101,10);
INSERT INTO Enrollment VALUES (999,10);
INSERT INTO Enrollment VALUES (101,99);
INSERT INTO Enrollment VALUES (NULL,10);

Run each INSERT separately because tools/transactions handle a failed statement differently. The purpose is to verify the constraint, not continue a production transaction after an error.

9. Relationship attributes, degree aur repeat enrollment

Relationship attribute: grade belongs to a particular Student-Course enrollment, not to Student alone or Course alone. Store it on Enrollment. If students can repeat a course in different terms, pair(student_id,course_id) is no longer enough: use (student_id,course_id,term) under that uniqueness rule, or an enrollment_id plus a UNIQUE constraint for the intended business identity.

Degree: a unary/recursive relationship involves one entity type in different roles (Employee supervises Employee); binary involves two (Student-Course); ternary involves three (Supplier supplies Part to Project). A ternary relationship is not always equivalent to three binary links: separate links can imply combinations that never occurred. Model the real fact before choosing tables.

Recursive role names: supervisor and subordinate are roles of Employee, not necessarily two independent entity types. A nullable supervisor_id FK can represent an employee with no supervisor; reject self-supervision or cycles with additional rules if the organization forbids them. A foreign key alone does not prevent cycles.

Weak vs merely dependent: a row can require another row through an FK without being a classic weak entity whose identity needs the owner's key. In this lesson OrderItem uses owner-key plus line_no, so line_no is a partial key. Do not label every FK-bearing table a weak entity.

10. Exam practice with design decisions

1. Department-Student is 1:N. Where is FK? On Students, the many side. NOT NULL if every Student must have a Department.
2. One Person can have at most one current Passport. Is FK enough? No. Add UNIQUE to the Person FK in Passport to enforce at most one Passport per Person. Make the assumption explicit; real passport history needs a different model.
3. Every Course must have at least one Student. Do Enrollment FKs enforce it? No. They verify existing links, not that each Course has a link. Use a transaction/application rule or appropriate database mechanism.
4. Order100 line1 and Order200 line1: duplicate? No under PK(order_id,line_no). (100,1) and (200,1) are distinct identities; repeating (100,1) is a duplicate.
5. Where does enrollment_date go? Enrollment, because it describes that association. If repeated terms are separate enrollments, key choice must reflect them.
6. Student has three phone numbers. One comma-separated field? Prefer separate StudentPhone rows in this relational design. Key/format rules depend on the actual phone business rules.
7. Cardinality vs participation? Maximum multiplicity vs whether zero participation is allowed. Some books combine minimum and maximum in the word cardinality; min-max notation makes both explicit.
8. Is every ER-generated schema automatically BCNF? No. ER modeling helps, but check functional dependencies and normalization separately.

Practice questions: short answers

1. ER model kya hai? Entities, attributes aur relationships ka conceptual representation, jo schema design se pehle requirements model karta hai.
2. Student-Course M:N ko kaise map karenge? Students, Courses aur Enrollment tables; Enrollment mein dono foreign keys aur suitable primary key.
3. Cardinality vs participation? Cardinality batati hai maximum mapping 1 ya many; participation batati hai relationship mandatory hai ya optional.
4. Weak entity ka example? OrderItem, jiska line_no sirf ek order ke andar unique hai; (order_id, line_no) identity de sakta hai.

Five-minute revision

ER = conceptual blueprint. Entity object hai, attribute property, relationship link. Key identity deti hai. 1:N mein many side FK; M:N mein junction table. Cardinality kitne links, participation link zaroori hai ya nahi. Design ko hamesha real business rules se check karo.

Sources: University of Cape Town: Entity-Relationship modelling and mapping; MIT OCW: Schema Design and ER modeling; PostgreSQL: constraints and foreign keys. Next: SQL queries and joins.

DBMS Exam Notes series: Previous: Part 1: Relational Model | DBMS series and hub | Next: Part 3: SQL Queries