ER Model and ER Diagram: Entities, Keys, Cardinality and Mapping to Tables · ই-আর মডেল ও ই-আর ডায়াগ্রাম: এনটিটি, কি (key), কার্ডিনালিটি ও টেবিলে রূপান্তর

1. What the ER model is and where it fits

Before anyone writes CREATE TABLE, a designer has to describe the real-world data: which things we store facts about, which facts, and how the things are connected. The Entity-Relationship (ER) model, introduced by Peter Chen in 1976, does exactly this with a picture called an ER diagram. It is a conceptual (high-level) model: it says what the data means, not how it is stored.

Database design runs in three steps, and the ER model is the first:

Step	Output	Example
Conceptual design	ER diagram	STUDENT enrols in COURSE (M:N)
Logical design	Relational tables (schema)	STUDENT, COURSE, ENROLLMENT(student_id, course_code, grade)
Physical design	Files, indexes, storage	An index on ENROLLMENT(course_code)

A neighbouring pair exams ask: the schema is the design (table names and columns), which rarely changes; an instance (state) is the data stored at one moment, which changes with every insert or delete.

2. Entities and attributes

• An entity is a real-world object we store data about and can tell apart from others: the student Anik, the course CSE201. It can be physical (a person, a car) or conceptual (a course, a bank account).
• An entity set (entity type) is the collection of similar entities: STUDENT, COURSE. In an MCQ "Which one is an entity?" with options Roll No., Student, Passport No., Department ID, the answer is Student; the others are attributes.
• An attribute is a property of an entity. The set of values it may take is its domain (for example cgpa from 0.00 to 4.00).

Attribute type	Meaning	Example	Chen symbol
Simple (atomic)	Cannot be divided usefully	Roll, Age	Oval
Composite	Made of smaller parts	Name = First + Last; Address = House, Street, City	Oval with sub-ovals
Single-valued	One value per entity	Date of birth	Oval
Multivalued	Several values per entity	Phone numbers, degrees	Double oval
Derived	Computed from other attributes, usually not stored	Age (from date of birth), CGPA (from course GPAs)	Dashed oval
Stored	Actually kept in the database	Date of birth	Oval
Key	Identifies each entity uniquely	Roll, NID	Oval with the name underlined

A NULL value means unknown or not applicable (a person with no middle name); it is not zero and not an empty string.

Grade hangs on the diamond because it belongs to the relationship: it is the grade of one student in one course, not a property of the student or the course alone.

3. Keys: super, candidate, primary, alternate, foreign

Key	Definition	Example in STUDENT(roll, nid, name, dept)
Super key	Any set of attributes that identifies every row uniquely (extra attributes allowed)	{roll}, {roll, name}, {nid, dept}
Candidate key	A minimal super key: no proper subset of it is a super key	{roll}, {nid}
Primary key	The one candidate key chosen as the main identifier; unique and never NULL	roll
Alternate key	A candidate key not chosen as primary	nid
Composite key	A key with two or more attributes	(student_id, course_code) in ENROLLMENT (section 9 adds semester to allow retakes)
Foreign key	Attribute(s) whose value must match the primary key (or another UNIQUE candidate key) of another or the same table, or be NULL	STUDENT.dept → DEPARTMENT.dept_id

So every primary key is a candidate key, and every candidate key is a super key. "Which key is the minimal super key?" → candidate key. "A subset of a super key is a candidate key when?" → when it is itself a super key and no proper subset of it is a super key (it is minimal).

A primary key gives entity integrity (no NULL key); a foreign key gives referential integrity (no reference to a row that does not exist).

Worked example 1: counting super keys. Table Employee(NID, Company_ID, Name, Mobile), where every NID and every mobile number is unique.

1. Candidate keys: {NID} and {Mobile}. Each is unique alone and cannot be made smaller. Company_ID and Name repeat, so neither is a key.
2. A super key is any attribute set that contains NID or Mobile.
3. Sets containing NID: NID is in, the other 3 attributes are free → 2³ = 8.
4. Sets containing Mobile: also 8. Sets containing both were counted twice: NID and Mobile in, 2 free → 2² = 4.
5. Super keys = 8 + 8 − 4 = 12; candidate keys = 2; two example candidate keys: {NID}, {Mobile}.

The short rule: if a relation of n attributes has exactly one candidate key and that key is a single attribute, it has 2ⁿ⁻¹ super keys. For R(E, F, G, H) whose only key is E that is 2³ = 8. With more than one candidate key, use inclusion–exclusion as above.

4. Relationships: degree, cardinality and participation

A relationship links entities (Anik enrols in CSE201); a relationship set links entity sets and is drawn as a diamond.

• Degree = the number of entity sets taking part: unary or recursive (EMPLOYEE supervises EMPLOYEE; role names such as supervisor and subordinate tell the two sides apart), binary (STUDENT enrols in COURSE, the usual case), ternary (SUPPLIER supplies PART to PROJECT).
• Cardinality ratio (mapping cardinality) = the maximum number of partners:

Cardinality	Meaning	Example
One-to-one (1:1)	Each A with at most one B, each B with at most one A	DEPARTMENT is headed by one INSTRUCTOR
One-to-many (1:N)	One A with many B, each B with at most one A	One DEPARTMENT has many STUDENTs
Many-to-one (N:1)	The same as 1:N, read from the other side	Many STUDENTs belong to one DEPARTMENT
Many-to-many (M:N)	Each A with many B and each B with many A	STUDENTs enrol in many COURSEs, a course has many students

• Participation = the minimum: total (every entity must take part, drawn as a double line; also called existence dependency) or partial (some may not, single line). Every LOAN must belong to a CUSTOMER (total), but not every CUSTOMER has a loan (partial).
• (min, max) notation puts both on the line: STUDENT in BELONGS_TO is (1, 1), DEPARTMENT is (0, N). min = 0 means partial, min ≥ 1 means total. Careful: (min, max) is written next to the entity it describes, so its placement is the reverse of the 1 / N labels of the cardinality-ratio notation (the student side carries (1, 1) although the department side carries the 1).
• Some books call the minimum the modality (optional = 0, mandatory = 1). With two sides and two choices each there are 2 × 2 = 4 possible sets of minimum cardinalities.

5. Weak entities

A weak entity has no key of its own; it is identified only together with its owner (identifying, strong) entity.

• Example: DEPENDENT of an INSTRUCTOR. Two instructors can each have a dependent named Ayesha, so dep_name alone is not unique, but within one instructor it is. dep_name is the partial key (discriminator), drawn with a dashed underline.
• Its key = owner's primary key + partial key: (instructor_id, dep_name).
• It is linked to the owner by an identifying relationship (double diamond) and always has total participation in it (a dependent cannot exist without the instructor). The weak entity itself is a double rectangle.

Point	Strong entity	Weak entity
Own primary key	Yes	No, only a partial key
Identified by	Its own key	Owner's key + partial key
Symbol	Rectangle	Double rectangle
Relationship that identifies it	None needed	Double diamond (identifying relationship), total participation

6. Generalization, specialization and aggregation (EER)

The Enhanced ER (EER) model adds class hierarchies:

• Specialization (top-down): split a superclass into subclasses. EMPLOYEE → TEACHER, OFFICER.
• Generalization (bottom-up): combine entity sets with common attributes into a superclass. CAR + TRUCK → VEHICLE.
• Subclasses are joined by an IS-A relationship and inherit all attributes and relationships of the superclass.
• Two constraints: disjoint (an entity is in at most one subclass, marked d) or overlapping (o); total (every superclass entity is in some subclass, double line) or partial.
• Aggregation treats a relationship as an entity so it can take part in another relationship (a MANAGER monitors a whole EMPLOYEE–WORKS_ON–PROJECT assignment).

To map a hierarchy to tables: one table for the superclass plus one per subclass keyed by the superclass key (works in every case); or subclass tables only (only if total and disjoint); or one table with a type column (disjoint), such as VEHICLE(RegNo, Price, VType, Seats, Tonnage).

7. ER diagram notations side by side

Concept	Chen notation	Crow's foot notation
Entity	Rectangle	Box with the attribute list inside
Weak entity	Double rectangle	Box (key shown as owner key + partial key)
Relationship	Diamond	A line with a label
Identifying relationship	Double diamond	Solid line
Attribute	Oval (key underlined)	Row inside the box, marked PK / FK
Multivalued / derived attribute	Double oval / dashed oval	Usually moved to its own box / left out
Cardinality 1 / many	1, N or M written on the line	One bar / a fork (crow's foot)
Total / partial participation	Double / single line	Bar (mandatory) / circle (optional) at the line end

In exams draw Chen notation unless told otherwise, and always write the cardinality on every relationship line.

8. Mapping an ER diagram to tables

ER construct	Rule
Strong entity	One table; simple attributes become columns; the key becomes the primary key
Composite attribute	Store only its parts (first_name, last_name), not the whole
Multivalued attribute	A separate table (owner key, value); both together form the primary key
Derived attribute	Not stored (computed when needed)
Weak entity	A table with owner key + partial key as the primary key; the owner key is also a foreign key
1:1 relationship	Foreign key on either side, preferably the side with total participation (add UNIQUE)
1:N relationship	Foreign key on the N side, pointing to the 1 side; no new table
M:N relationship	A new table (junction or bridge table) with both keys as foreign keys and together as primary key, plus the relationship's own attributes
Unary 1:N (supervises)	A foreign key to the same table (manager_id)
n-ary (ternary)	A new table with the keys of all participating entities

Worked example 2: how many tables? E1 and E2 have simple single-valued attributes. R1 between them is one-to-many, R2 is many-to-many, and neither has attributes.

1. E1 → 1 table, E2 → 1 table: 2 so far.
2. R1 (1:N) adds no table: put E1's key as a foreign key in E2's table (the N side).
3. R2 (M:N) needs its own table (E1 key, E2 key): total 3.

The same reasoning answers "two entities, one relationship, both partial: minimum and maximum tables?": 1:1 or 1:N → 2 tables, M:N → 3, so min 2, max 3. (They cannot be merged into one table when participation is partial, because an unmatched entity would leave a NULL in the key.)

9. Full worked example: a university database

The story. A university has departments (id, name, building). Each student (id, name made of first and last name, date of birth, one or more phone numbers) belongs to exactly one department; age is worked out from the date of birth. Each instructor (id, name, salary) works in one department, and each department is headed by exactly one instructor. Each course (code, title, credits) is offered by one department. A student enrols in many courses and a course has many students; each enrolment records the semester and the grade. An instructor may advise many students; a student has at most one advisor. For benefits, the university records each instructor's dependents (name, relation).

Step 1: entities. DEPARTMENT, STUDENT, INSTRUCTOR, COURSE (strong) and DEPENDENT (weak: a dependent is named only within its instructor). "Enrolment" is an action between student and course, so it is a relationship, not an entity.

Step 2: attributes. Name is composite, phone is multivalued, age is derived, grade and semester belong to the enrolment.

Step 3: relationships with cardinality and participation.

Relationship	Between	Cardinality	Participation
belongs_to	DEPARTMENT – STUDENT	1:N	Student total
works_in	DEPARTMENT – INSTRUCTOR	1:N	Instructor total
heads	INSTRUCTOR – DEPARTMENT	1:1	Department total, instructor partial
offers	DEPARTMENT – COURSE	1:N	Course total
enrols	STUDENT – COURSE	M:N	Both partial
advises	INSTRUCTOR – STUDENT	1:N	Both partial
supports (identifying)	INSTRUCTOR – DEPENDENT	1:N	Dependent total

Step 4: the diagram (crow's foot: || exactly one, o| zero or one, o{ zero or more, |{ one or more; a solid line is an identifying relationship, a dashed line a non-identifying one).

Step 5: map to tables (primary keys in bold, FK = foreign key).

Table	Columns	Rule used
DEPARTMENT	dept_id, dept_name, building, head_id (FK → INSTRUCTOR, UNIQUE, NOT NULL)	Strong entity; 1:1 heads with FK on the total side
INSTRUCTOR	instructor_id, name, salary, dept_id (FK, NOT NULL)	Strong entity; 1:N works_in
STUDENT	student_id, first_name, last_name, dob, dept_id (FK, NOT NULL), advisor_id (FK, may be NULL)	Composite split; 1:N belongs_to and advises; age not stored
STUDENT_PHONE	student_id, phone	Multivalued attribute
COURSE	course_code, title, credits, dept_id (FK, NOT NULL)	Strong entity; 1:N offers
ENROLLMENT	student_id, course_code, semester, grade	M:N junction table with the relationship's attributes
DEPENDENT	instructor_id, dep_name, relation	Weak entity: owner key + partial key

Result: 7 tables. Notice where the foreign keys went: every 1:N put a column on the N side (STUDENT.dept_id, INSTRUCTOR.dept_id, COURSE.dept_id, STUDENT.advisor_id), and only the M:N relationship and the multivalued attribute created extra tables. Semester is in ENROLLMENT's key so a student can retake a course in a later semester. Each total participation becomes a NOT NULL foreign key. Because DEPARTMENT and INSTRUCTOR reference each other, the first department and its head cannot be inserted one after the other with both foreign keys checked at once; declare one of them DEFERRABLE INITIALLY DEFERRED so it is checked at COMMIT (or add that constraint after the first rows exist). Built in SQLite with head_id deferred, the schema accepts a department and its head inserted in one transaction; it rejects a department with no head and an instructor, course or student with no department (NOT NULL), one instructor heading two departments (UNIQUE head_id), and a second dependent named Ayesha for the same instructor (but allows one for another instructor).

10. The next step: normalization

After mapping, check the tables with normalization, which removes redundancy and update, insert and delete anomalies using functional dependencies (A → B: a value of A fixes the value of B).

Normal form	Condition
1NF	Every cell holds one atomic value; no repeating groups (a "Python, CSS, JS" cell breaks it)
2NF	1NF, and no non-prime attribute (one not in any candidate key) depends on only part of a candidate key
3NF	2NF, and no non-prime attribute is transitively dependent on a candidate key; equivalently, for every non-trivial X → A, X is a super key or A is prime
BCNF	For every non-trivial dependency X → Y, X is a super key

BCNF drops the "or A is prime" escape, so every BCNF relation is in 3NF but not the reverse. Beyond BCNF come 4NF (every non-trivial multivalued dependency has a super key on its left) and 5NF (every join dependency is implied by the candidate keys).

Example: STUDENT(roll, name, dept_id, building), with roll → name, roll → dept_id and dept_id → building. Its only candidate key is roll, so it is in 2NF (a one-attribute key has no part for anything to depend on), but it breaks 3NF: in dept_id → building, dept_id is not a super key and building is not prime, so roll → dept_id → building is a transitive dependency (every student of a department repeats its building). Splitting it into STUDENT(roll, name, dept_id) and DEPARTMENT(dept_id, building) fixes it, and the join on dept_id gives back exactly the original rows. A careful ER design usually lands close to 3NF already; normalization catches what the diagram missed.

11. Tricky exam cases

• Degree of a relation vs degree of a relationship. The degree of a relation (table) is its number of attributes (columns), and its cardinality is its number of rows (tuples). The degree of a relationship is the number of entity sets in it (binary = 2).
• Many-to-one with total participation. If R is many-to-one from E1 to E2 and E1 participates totally, then every entity in E1 is associated with exactly one entity in E2 (total gives at least one, many-to-one gives at most one).
• M:N becomes a table. In an ER diagram, a many-to-many relationship corresponds to a table in the database; a 1:N does not.
• Where the foreign key goes. Always on the "many" side. Putting dept_id in STUDENT works; putting a student list in DEPARTMENT breaks 1NF.
• Multivalued vs composite. Phones (several values of one kind) is multivalued; name (one value split into parts) is composite. The 1NF rule converts a composite attribute into its individual parts.
• Super key vs UNIQUE key. A super key is a design concept (any identifying attribute set); UNIQUE is an SQL constraint that may allow NULL, while a PRIMARY KEY may not.
• Weak entity identification. A weak entity is identified by owner key + partial key, never by the partial key alone, and its participation in the identifying relationship is always total.