The Entity-Relationship Model
Conceptual Modeling, ER Diagrams, and Extended ER Features
100% Exam Frequency
ER/EER diagram questions appear in every exam. The most common scenario is Hospital Management System. Be prepared to draw complete diagrams and map them to relational schema.
1. Introduction to ER Model
What is the ER Model?
The Entity-Relationship (ER) Model is a high-level conceptual data model used to describe the structure of a database. It models an enterprise as a collection of entities and relationships among entities.
The ER model was developed by Peter Chen in 1976 and is widely used for database design. The main components are:
Entities
Objects in the real world that are distinguishable from other objects. Examples: person, company, event, place.
Relationships
Associations among entities. Shows how entities are related to each other.
Attributes
Properties that describe entities. Examples: name, address, salary.
2. Entities and Entity Sets
Entity
An entity is an object that exists in the real world and is distinguishable from other objects. It can be a physical object (person, car) or a conceptual object (course, job).
Entity Set
An entity set is a set of entities of the same type that share the same properties (attributes). Examples: set of all students, set of all courses.
Strong Entity
An entity that has a primary key and can exist independently. It does not depend on any other entity for its identification.
Represented by a single rectangle
Weak Entity
An entity that cannot be uniquely identified by its own attributes alone. It depends on a "strong entity" (owner) for its existence.
Represented by a double rectangle
Example: Entity Sets
- EMPLOYEE entity set: All employees in a company
- DEPARTMENT entity set: All departments in the organization
- PROJECT entity set: All projects being worked on
- DEPENDENT entity set: Dependents of employees (weak entity)
3. Attributes
What are Attributes?
Attributes are descriptive properties possessed by all members of an entity set. Each attribute has a domain (set of permitted values).
Types of Attributes
Simple (Atomic) Attribute
Cannot be divided into smaller parts. E.g., Age, SSN
Composite Attribute
Can be divided into smaller sub-parts. E.g., Address → Street, City, Zip
Multivalued Attribute
Can have multiple values. E.g., Phone numbers, Email addresses
Derived Attribute
Value can be computed from other attributes. E.g., Age from DOB
Key Attribute
Uniquely identifies an entity. Shown with underline.
Stored Attribute
Directly stored in database, not computed.
Example: Instructor Entity with Attributes
4. Keys
Super Key
A set of one or more attributes whose values uniquely determine each entity. May contain extra attributes.
For INSTRUCTOR: {ID}, {ID, name}, {ID, salary} are all super keys
Candidate Key
A minimal super key - no proper subset of it is a super key. Also called a "minimal super key".
For CAR: {State, Reg#} and {SerialNo} are candidate keys
Primary Key
One candidate key chosen by the database designer to uniquely identify entities. Shown with underline in ER diagrams.
Partial Key (Discriminator)
An attribute that partially identifies a weak entity when combined with the owner entity's primary key.
5. Relationships and Relationship Sets
Relationship
A relationship is an association among several entities. It shows how entities are related to each other.
Relationship Set
A relationship set is a set of relationships of the same type. It is a mathematical relation among entities.
Example
44553 (Peltier) advisor 22222 (Einstein)
Here, advisor is a relationship between a student entity and an instructor entity.
Degree of Relationship
Unary (Recursive)
Degree 1 - Involves only one entity set. Example: Employee manages Employee
Binary
Degree 2 - Involves two entity sets. Most common type. Example: Student takes Course
Ternary
Degree 3 - Involves three entity sets. Example: Student works on Project under Instructor
Relationship Attributes
Relationships can also have attributes. For example, the advisor relationship might have a date attribute indicating when the student started with the advisor.
6. Cardinality Constraints (Mapping Cardinality)
Mapping Cardinality
Expresses the number of entities to which another entity can be associated via a relationship. Most useful for binary relationships.
One-to-One (1:1)
An entity in A is associated with at most one entity in B, and vice versa.
Example: One department has one manager, and one manager manages only one department.
One-to-Many (1:N)
An entity in A is associated with many entities in B, but an entity in B is associated with at most one entity in A.
Example: One department has many employees, but each employee belongs to only one department.
Many-to-One (N:1)
Many entities in A are associated with one entity in B. Reverse of one-to-many.
Example: Many students have one advisor.
Many-to-Many (M:N)
An entity in A can be associated with many entities in B and vice versa.
Example: Students enroll in many courses, and each course has many students.
7. Participation Constraints
Total Participation
Every entity in the entity set participates in at least one relationship. Represented by a double line.
Example: Every section must have an associated course (total participation of section in sec_course).
Partial Participation
Some entities may not participate in any relationship. Represented by a single line.
Example: Not every instructor needs to be an advisor (partial participation of instructor in advisor).
Participation Constraint Example
Double line indicates total participation - every section must be associated with a course
8. Weak Entity Sets
Weak Entity
An entity set that does not have a primary key is called a weak entity set. Its existence depends on a strong entity (also called the identifying/owner entity).
Characteristics
- No primary key of its own
- Depends on a strong (owner) entity
- Has a partial key (discriminator)
- Represented by double rectangle
- Connected via identifying relationship (double diamond)
- Always has total participation in identifying relationship
Example: DEPENDENT
- Owner entity: EMPLOYEE
- Weak entity: DEPENDENT
- Partial key: Dependent_name
- Primary key: (ESSN, Dependent_name)
- A dependent cannot exist without an employee
Weak Entity Representation
9. Extended ER (EER) Features
Exam Tip
EER features like Specialization and Generalization are explicitly requested in recent exams. Make sure to understand the constraints (disjoint/overlapping, total/partial).
The Extended ER model adds additional semantic concepts to the basic ER model:
- Specialization - Top-down process of defining subclasses
- Generalization - Bottom-up process of combining entity types
- Aggregation - Treating relationship as higher-level entity
- Category (Union Type) - Subset of union of entity types
10. Specialization
Specialization (Top-Down)
The process of defining a set of subclasses of an entity type based on some distinguishing characteristic. Subclasses inherit all attributes from the superclass.
Example
EMPLOYEE can be specialized into:
- ENGINEER (with attribute: Eng_type)
- SECRETARY (with attribute: Typing_speed)
- TECHNICIAN (with attribute: Tech_grade)
Each subclass inherits Emp_name, Address, Salary from EMPLOYEE.
Constraints on Specialization
Disjointness Constraint
- Disjoint (d): An entity can belong to only ONE subclass
- Overlapping (o): An entity can belong to MULTIPLE subclasses
Disjoint: Employee is either Engineer OR Secretary (not both)
Overlapping: Person can be both Student AND Employee
Completeness Constraint
- Total (double line): Every superclass entity MUST belong to at least one subclass
- Partial (single line): Some superclass entities may NOT belong to any subclass
Total: Every person must be either Student or Employee
Partial: Some employees may not be engineers, secretaries, or technicians
Specialization with ISA Triangle
'd' in triangle indicates disjoint constraint
11. Generalization
Generalization (Bottom-Up)
The process of combining multiple entity types that share common features into a higher-level superclass. It's the reverse of specialization.
Example
CAR and TRUCK can be generalized into VEHICLE:
- Common attributes: Vehicle_no, Average_kilometer, No_of_wheels
- CAR-specific: No_of_doors
- TRUCK-specific: Capacity
Specialization vs Generalization
Both are inverses of each other and are represented the same way in ER diagrams using the ISA triangle. The difference is in the design approach:
- Specialization: Start with general entity → define specific subclasses
- Generalization: Start with specific entities → abstract common features into superclass
12. Aggregation
Aggregation
A technique that allows treating a relationship as an abstract entity. It's used when we need to express a relationship between a relationship and an entity.
Example: Project Guide Evaluation
Consider: Students work on projects under the guidance of instructors (proj_guide relationship). Now we want to record evaluations of students by guides.
- We can't directly create a relationship between a relationship (proj_guide) and an entity (evaluation)
- Solution: Aggregate proj_guide into an abstract entity, then create relationship with evaluation
Aggregation Representation
13. ER to Relational Mapping
Exam Tip
Questions often ask to "Draw an ER diagram AND Map it to Relational Model". Learn the mapping algorithm step by step.
Mapping Algorithm
Strong Entity Mapping
Create a relation for each strong entity. Include all simple attributes. Primary key of entity becomes primary key of relation.
Weak Entity Mapping
Create relation with all attributes. Include owner's primary key as foreign key. Primary key = Owner's PK + Partial key.
Binary 1:1 Relationship Mapping
Add foreign key to one of the entities (preferably the one with total participation). Include relationship attributes.
Binary 1:N Relationship Mapping
Add the primary key of the "one" side as foreign key to the "many" side relation.
Binary M:N Relationship Mapping
Create a new relation with primary keys of both entities as foreign keys. Their combination forms the primary key. Include relationship attributes.
Multivalued Attribute Mapping
Create a new relation containing the attribute and the primary key of the parent entity as foreign key.
N-ary Relationship Mapping
Create a new relation with primary keys of all participating entities as foreign keys, plus relationship attributes.
Specialization/Generalization Mapping
Option A: Create relation for superclass and each subclass (subclass has superclass PK as FK)
Option B: Create relations only for subclasses with all inherited + own attributes
14. ER Diagram Examples
Hospital Management System
This is the most frequently asked scenario. Key entities and relationships:
Entities
- PATIENT: Patient_ID (PK), Name, DOB, Address, Phone
- DOCTOR: Doctor_ID (PK), Name, Specialization, Phone
- DEPARTMENT: Dept_ID (PK), Dept_Name, Location
- ROOM: Room_No (PK), Room_Type, Charges
- NURSE: Nurse_ID (PK), Name, Qualification
- TREATMENT: (Weak entity) Treatment_Date, Description
Relationships
- admits (Patient - Room): M:1
- treats (Doctor - Patient): M:N with Treatment as relationship entity
- works_in (Doctor - Department): M:1
- assigned_to (Nurse - Department): M:1
- attends (Nurse - Patient): M:N
Specialization
PATIENT can be specialized into:
- INPATIENT: Admission_Date, Discharge_Date, Room_No
- OUTPATIENT: Visit_Date, Time
Library Management System
Entities
- BOOK: ISBN (PK), Title, Author, Publisher, Year
- MEMBER: Member_ID (PK), Name, Address, Phone
- LIBRARIAN: Lib_ID (PK), Name, Contact
- COPY: (Weak) Copy_No, Status
Relationships
- borrows (Member - Copy): M:N with Date, Due_Date
- has (Book - Copy): 1:N (identifying)
- manages (Librarian - Book): M:N
Company Database
Entities
- EMPLOYEE: SSN (PK), Name, Address, Salary
- DEPARTMENT: Dnumber (PK), Dname, Locations (multivalued)
- PROJECT: Pnumber (PK), Pname, Location
- DEPENDENT: (Weak) Name, Relationship
Relationships
- works_for (Employee - Department): M:1
- manages (Employee - Department): 1:1
- works_on (Employee - Project): M:N with Hours
- controls (Department - Project): 1:N
- supervision (Employee - Employee): 1:N
- has_dependents (Employee - Dependent): 1:N (identifying)
15. Practice Questions
Draw an Extended E-R diagram for Hospital Management System and Map it to Relational Model. [10 marks]
▼Entities:
- PATIENT (Patient_ID, Name, DOB, Address, Phone) - Strong
- DOCTOR (Doctor_ID, Name, Specialization, Phone) - Strong
- DEPARTMENT (Dept_ID, Name, Location) - Strong
- NURSE (Nurse_ID, Name, Qualification) - Strong
- ROOM (Room_No, Type, Charges) - Strong
Relationships:
- treats (Doctor-Patient): M:N with Date, Prescription
- admits (Patient-Room): M:1 with Admission_date
- works_in (Doctor-Department): M:1
- assigned_to (Nurse-Department): M:1
EER Features - Specialization:
PATIENT specialized into INPATIENT and OUTPATIENT (disjoint, total)
Relational Schema:
DOCTOR(Doctor_ID, Name, Specialization, Phone, Dept_ID)
DEPARTMENT(Dept_ID, Name, Location)
NURSE(Nurse_ID, Name, Qualification, Dept_ID)
ROOM(Room_No, Type, Charges)
TREATMENT(Doctor_ID, Patient_ID, Date, Prescription)
ADMISSION(Patient_ID, Room_No, Admission_date)
Describe weak entity. Provide an example of weak entity and strong entity. [5 marks]
▼Weak Entity:
- An entity that cannot be uniquely identified by its own attributes alone
- Depends on a strong (owner) entity for its existence
- Has a partial key (discriminator) that partially identifies it
- Primary key = Owner's PK + Partial key
- Represented by double rectangle in ER diagram
- Connected via identifying relationship (double diamond)
Example:
- Strong Entity: EMPLOYEE with attributes SSN (PK), Name, Address
- Weak Entity: DEPENDENT with partial key Dependent_name, Relationship
- DEPENDENT cannot exist without EMPLOYEE
- Primary key of DEPENDENT: (ESSN, Dependent_name)
Discuss Generalization, Specialization, and Aggregation with suitable examples. [10 marks]
▼1. Specialization (Top-Down):
- Process of defining subclasses from a superclass
- Based on distinguishing characteristics
- Subclasses inherit attributes from superclass
- Represented by ISA triangle
Example: EMPLOYEE → ENGINEER, SECRETARY, TECHNICIAN
Constraints:
- Disjoint (d): Entity belongs to only one subclass
- Overlapping (o): Entity can belong to multiple subclasses
- Total: Every superclass entity must be in some subclass
- Partial: Some entities may not belong to any subclass
2. Generalization (Bottom-Up):
- Process of combining entity types into superclass
- Identifies common features among entities
- Reverse of specialization
Example: CAR + TRUCK → VEHICLE (common: Vehicle_no, Wheels)
3. Aggregation:
- Treats a relationship as a higher-level entity
- Used when relationship participates in another relationship
- Eliminates redundancy
Example: proj_guide relationship (Student-Project-Instructor) aggregated to relate with EVALUATION entity
Explain ER to Relational Mapping Algorithm. [5 marks]
▼Step 1 - Strong Entity: Create relation with all simple attributes. Entity PK → Relation PK.
Step 2 - Weak Entity: Create relation with all attributes + owner's PK as FK. PK = Owner's PK + Partial key.
Step 3 - Binary 1:1: Add FK to one relation (preferably total participation side).
Step 4 - Binary 1:N: Add PK of "one" side as FK to "many" side.
Step 5 - Binary M:N: Create new relation with both PKs as FKs. Composite PK from both.
Step 6 - Multivalued: Create new relation with attribute + parent's PK as FK.
Step 7 - N-ary: Create relation with all participating entity PKs as FKs.
Step 8 - Specialization: Option A (tables for super + sub) or Option B (only sub with inherited attributes).