This chapter completes the important advanced ideas of the ER Model. You will learn every major key used around ER and relational design, all important mapping constraints, cardinality and participation rules, min-max notation, role names, generalization, specialization, inheritance, disjoint and overlapping constraints, total and partial specialization, and aggregation. Everything is connected through simple college and company examples so you can understand the logic instead of memorising disconnected definitions.
What Are We Actually Learning Here?
Basic ER Model tells us what things exist, what details they have and how they are connected. This advanced chapter answers the next questions: How do we identify each entity uniquely? How many entities can participate in a relationship? Is participation compulsory? Can one entity type be divided into specialised subtypes? Can several lower-level entities be combined into one higher-level idea? And how can a relationship itself participate in another relationship?
Master shortcut: K-M-G-A
K = Keys · M = Mapping Constraints · G = Generalization · A = AggregationThink: “Know My Group Architecture.” First identify records, then relationship rules, then hierarchy, then higher-level relationship modelling.
This chapter assumes you already understand entity, attribute, relationship, cardinality and basic ER notation. If not, first read ER Model in DBMS: Entity, Attribute, Relationship & ER Diagram Notation.
Interactive Advanced ER Model Visual Lab
Use the buttons below to switch between the four ideas. The goal is to create a picture in your mind before learning the technical definitions.
Keys in ER Model and Database Design
A key is not just an exam definition. Its job is to answer one practical question:
Shortcut #1: A key answers “Which exact record?”
If you say “student,” there may be 5,000 students. If you say student_id = S1042, the system can point to one exact student.
1. Super Key
A super key is any set of one or more attributes that can uniquely identify an entity instance or table row.
Suppose STUDENT has attributes:
student_id, email, name, branch
If both student_id and email are unique, then examples of super keys may include:
{student_id}, {email}, {student_id, name}, {email, branch}, {student_id, email}.
Notice that a super key can contain extra unnecessary attributes. That is why every candidate key is a super key, but not every super key is a candidate key.
2. Candidate Key
A candidate key is a minimal super key. It uniquely identifies a record, and if you remove any attribute from it, it no longer works as a unique identifier.
In the same example, if both student_id and email are individually unique, both can be candidate keys.
Shortcut #2: Candidate = Super Key minus extra baggage
Candidate Key = Minimal Super Key3. Primary Key
A primary key is the candidate key selected as the main identifier for the entity or relation.
If student_id and email are both candidate keys, the designer may choose student_id as the primary key because it is short, stable and controlled by the institution.
4. Alternate Key
A candidate key that is not selected as the primary key is called an alternate key.
If student_id is the primary key and email is another candidate key, then email is an alternate key.
5. Composite Key
A composite key is a key made from more than one attribute.
Example: in an ENROLLMENT relation, the pair (student_id, course_id) may uniquely identify one student's enrollment in one course. Neither attribute alone is enough.
6. Partial Key / Discriminator
A partial key is used with a weak entity. It distinguishes weak entities that belong to the same owner, but it may not be globally unique by itself.
Suppose EMPLOYEE owns DEPENDENT. A dependent may use dependent_name as a partial key. Two different employees can both have a dependent named “Riya,” so dependent_name alone is not globally unique. But (employee_id, dependent_name) can identify the dependent.
7. Foreign Key
A foreign key belongs mainly to the relational model rather than the conceptual ER model. It is an attribute or set of attributes in one relation that refers to a candidate key, commonly the primary key, in another relation.
Example: if STUDENT belongs to DEPARTMENT, the relational Students table may contain department_id as a foreign key referring to the Departments table.
Shortcut #3: S-C-P-A-C-P-F
Super → Candidate → Primary → Alternate → Composite → Partial → ForeignUnderstand the flow instead of memorising only names: first all unique choices, then minimal choices, then chosen key, unused candidate, multi-column key, weak-entity key and finally relational reference key.
Super Key vs Candidate Key vs Primary Key
| Point | Super Key | Candidate Key | Primary Key |
|---|---|---|---|
| Unique identification | Yes | Yes | Yes |
| Minimal? | Not necessary | Yes | Yes, because it is chosen from candidate keys |
| How many can exist? | Many | One or more | One selected primary key per relation |
| Main purpose | All possible unique identifying attribute sets | Best minimal unique choices | Main chosen identifier |
| Example | {student_id, name} | {student_id} | student_id |
Shortcut #4: S → C → P means Filter, Filter, Choose
Super keys are all unique possibilities. Filter out extra attributes to get candidate keys. Choose one candidate key to get the primary key.
What Are Mapping Constraints in ER Model?
Mapping constraints are rules that limit or describe how entities can participate in relationships. The most important are cardinality ratio and participation constraints. Min-max notation gives an even more precise form of these rules.
Cardinality Ratio
How many entities on one side can be connected to how many entities on the other side?
Participation Constraint
Must every entity participate, or is participation optional for some entities?
Min-Max Constraint
Shows the minimum and maximum number of relationship instances allowed.
Role Constraint / Role Name
Clarifies the role played by an entity, especially in recursive relationships.
Shortcut #5: Mapping constraints answer “How many?” and “Must?”
Cardinality = How many? · Participation = Must?Cardinality Ratio: 1:1, 1:N, N:1 and M:N
1:1 Example
In a simplified model, one PERSON has at most one PASSPORT and one PASSPORT belongs to one PERSON.
1:N Example
One DEPARTMENT has many STUDENTS, while each STUDENT belongs to one DEPARTMENT.
N:1 Example
Many STUDENTS belong to one DEPARTMENT. This is the same relationship viewed from the opposite direction.
M:N Example
Many STUDENTS enroll in many COURSES. A student can take several courses, and a course can contain many students.
Shortcut #6: Ask from both sides
Never guess cardinality from one sentence. Ask:
One A can connect to how many B?
One B can connect to how many A?
This two-question method almost always exposes the correct ratio.
Total Participation vs Partial Participation
Participation tells whether an entity is required to take part in a relationship.
Total Participation
Every entity in the entity set must participate in at least one relationship instance.
Common Chen notation: double line.
Example: every DEPENDENT must be connected to an EMPLOYEE.
Partial Participation
Some entities may participate and some may not.
Common Chen notation: single line.
Example: some TEACHERS may manage a DEPARTMENT, while many do not.
Shortcut #7: Must = Total, May = Partial
If the business rule says every, must or required, think total participation. If it says may, can or some, think partial participation.
Min-Max Notation: (0,1), (1,1), (0,N), (1,N)
Min-max notation combines participation and cardinality in one pair.
Example: suppose every STUDENT must belong to exactly one DEPARTMENT. On the STUDENT side, the DEPARTMENT relationship may be represented as (1,1). Suppose a DEPARTMENT can initially have zero students or later many students. On that side it can be (0,N).
Shortcut #8: (minimum, maximum)
First number = Must? · Second number = How many at most?0 means optional. 1 as minimum means compulsory. N as maximum means many.
Role Names and Recursive Relationships
Sometimes one entity type participates in the same relationship more than once. Then role names tell us what each participation means.
Example: EMPLOYEE SUPERVISES EMPLOYEE.
Without role names, the diagram can become confusing because both ends are the same entity type.
What Is Generalization in ER Model?
Generalization is a bottom-up process in which two or more lower-level entity types that share common features are combined into one higher-level entity type.
Suppose a college separately identifies STUDENT and TEACHER. Both have common attributes such as name, email and phone. Instead of repeating these common features, we can create a higher-level entity called PERSON.
PERSON becomes the superclass or supertype. STUDENT and TEACHER become subclasses or subtypes.
The subtype inherits common attributes and relationships from the supertype. It can also have its own special attributes.
Example:
PERSON: person_id, name, email, phone
STUDENT: branch, semester
TEACHER: designation, salary
Shortcut #9: Generalization = Many specific → One common
STUDENT + TEACHER → PERSONThink bottom-up: start with separate lower-level entity types and move upward to a common parent.
What Is Specialization?
Specialization is a top-down process in which one higher-level entity type is divided into lower-level subtypes based on distinguishing characteristics.
Example: start with EMPLOYEE. Depending on role, EMPLOYEE can be specialised into FACULTY, ADMIN_STAFF and TECHNICAL_STAFF.
Shortcut #10: Generalization goes UP, Specialization goes DOWN
Generalization: children → parent · Specialization: parent → childrenGeneralization vs Specialization
| Point | Generalization | Specialization |
|---|---|---|
| Direction | Bottom-up | Top-down |
| Starts with | Several lower-level entity types | One higher-level entity type |
| Goal | Find common parent | Create meaningful subtypes |
| Example | STUDENT + TEACHER → PERSON | EMPLOYEE → FACULTY + ADMIN |
| Inheritance | Subtypes inherit from the generalized supertype | Created subtypes inherit from the original supertype |
Inheritance in Generalization and Specialization
A subtype inherits the attributes and relationships of its supertype.
If PERSON has person_id, name and email, then STUDENT does not need to conceptually redefine all of them. STUDENT inherits those common features and adds subtype-specific attributes such as branch and semester.
Likewise, if PERSON participates in a relationship such as LIVES_AT with ADDRESS, a subtype such as STUDENT can also inherit that relationship unless the model defines special restrictions.
Shortcut #11: Child gets Parent + Extra
Subtype = Inherited common features + subtype-specific featuresDisjoint vs Overlapping Constraints
Disjoint Constraint
An entity from the supertype can belong to at most one subtype in that specialization.
Example: if an ACCOUNT is classified strictly as either SAVINGS_ACCOUNT or CURRENT_ACCOUNT, one account cannot be both.
Overlapping Constraint
An entity from the supertype may belong to more than one subtype at the same time.
Example: a PERSON may be both STUDENT and EMPLOYEE in a university.
Shortcut #12: Disjoint = one box only, Overlap = multiple boxes allowed
If membership can happen in more than one subtype simultaneously, think overlap.
Total vs Partial Specialization
Total Specialization
Every entity in the supertype must belong to at least one subtype.
Example: if every ACCOUNT must be either SAVINGS_ACCOUNT or CURRENT_ACCOUNT, specialization is total.
Partial Specialization
Some supertype entities may belong to no subtype.
Example: some EMPLOYEES may be neither MANAGER nor ENGINEER.
Shortcut #13: Total = everyone enters a child group
Partial means the parent can contain entities that stay only in the parent and do not belong to any listed subtype.
Attribute-Defined vs User-Defined Specialization
A specialization can sometimes be decided by an attribute value.
Example: EMPLOYEE has attribute job_type. If job_type = 'Faculty', the employee enters FACULTY subtype. If job_type = 'Admin', the employee enters ADMIN subtype. This is an attribute-defined or predicate-defined approach.
In other designs, subtype membership may be assigned by explicit business rules or user decisions rather than one simple attribute. This can be called user-defined specialization.
What Is Aggregation in ER Model?
Aggregation is an abstraction in which a relationship set, together with its participating entity sets, is treated as a higher-level entity-like unit so that it can participate in another relationship.
This definition sounds difficult until you see the problem.
Suppose EMPLOYEE works on PROJECT. That is already a relationship:
EMPLOYEE — WORKS_ON — PROJECT
Now suppose a MANAGER monitors this specific work assignment. The manager is not simply monitoring the employee alone and not simply monitoring the project alone. The manager is monitoring the employee working on that project.
So we treat the whole EMPLOYEE–WORKS_ON–PROJECT combination as one higher-level unit. Then MANAGER can have a MONITORS relationship with that aggregated unit.
Shortcut #14: Aggregation = Relationship becomes a “thing”
If another relationship needs to connect to an existing relationship, think aggregation.
Entity + Relationship + Entity → Higher-level unitWhy Do We Need Aggregation?
Aggregation is useful when the meaning depends on a relationship instance as a whole.
In the manager example, MONITORS refers to a specific assignment of an employee to a project. If we only connect MANAGER separately to EMPLOYEE and PROJECT, we may lose the meaning that the manager is monitoring that exact work assignment.
Could a ternary relationship sometimes model similar information? Yes, depending on the requirements. But aggregation is specifically used when we want to treat an existing relationship as a higher-level object for another relationship. The right model depends on the exact semantics of the problem.
Shortcut #15: Ask “Is the second relationship about a pair's connection?”
If yes, aggregation may be the clearest modelling tool.
Generalization vs Aggregation
| Point | Generalization | Aggregation |
|---|---|---|
| Main purpose | Create a higher-level supertype from similar lower-level entity types. | Treat a relationship and its entities as a higher-level unit. |
| Works mainly with | Entity hierarchy | Relationship abstraction |
| Core idea | “These entities are kinds of the same parent.” | “This whole relationship needs to participate in another relationship.” |
| Example | STUDENT + TEACHER → PERSON | MANAGER monitors EMPLOYEE working on PROJECT |
| Memory word | Hierarchy | Relationship-as-object |
One Full Example Using Keys, Constraints, Generalization and Aggregation
Suppose a university is designing a project management database.
This example shows why these topics are taught together: keys solve identification, mapping constraints solve relationship rules, specialization/generalization solve hierarchy, and aggregation solves relationship-about-relationship situations.
How These ER Concepts Affect Relational Tables
The ER model is conceptual, but these decisions directly affect relational design.
That is why a correct ER model saves work later. A wrong cardinality or key decision can create unnecessary duplication or incorrect table relationships.
The earlier DBMS vs File System chapter explains why central rules and relationships matter. The DBMS Architecture chapter explains where database components sit. This chapter explains the deeper rules inside the conceptual data model.
Common Mistakes Students Make
Fast Method for ER Model Questions
Shortcut #16: E-K-R-C-P-W-H-A
Entities → Keys → Relationships → Cardinality → Participation → Weak → Hierarchy → AggregationThis sequence is useful for long 8-mark or 10-mark ER questions.
How to Write This Topic in Semester Exams
One focused definition
If asked about one term, write definition + one example. Do not dump all ER concepts into a 2-mark answer.
Definition + types + example
For keys, compare super/candidate/primary. For mapping constraints, explain cardinality and participation with one diagram.
Full conceptual answer
Add diagrams, hierarchy constraints, differences, examples and practical interpretation. For aggregation, always draw the higher-level relationship picture.
For better presentation, use the guide on writing high-scoring answers in university semester exams. For unit-wise revision, use the previous-year-paper priority method.
Practice Questions With Answers
1. Why is every candidate key a super key but not every super key a candidate key?
A candidate key is a minimal super key. A super key can contain extra attributes and still remain unique. Therefore all candidate keys satisfy the super-key condition, but only minimal super keys qualify as candidate keys.
2. What is the difference between cardinality and participation?
Cardinality tells how many entities can be related, such as 1:N or M:N. Participation tells whether every entity must participate or participation is optional for some entities.
3. What does (1,N) mean in min-max notation?
It means at least one relationship instance is required and multiple relationship instances are allowed.
4. What is generalization?
Generalization is a bottom-up abstraction in which similar lower-level entity types are combined into a common higher-level supertype.
5. What is specialization?
Specialization is a top-down process in which one higher-level entity type is divided into meaningful lower-level subtypes.
6. What is the difference between disjoint and overlapping specialization?
In disjoint specialization, one supertype entity can belong to at most one subtype in that specialization. In overlapping specialization, one entity may belong to multiple subtypes simultaneously.
7. What is aggregation in ER Model?
Aggregation treats a relationship together with its participating entity sets as a higher-level unit so that it can participate in another relationship.
8. Why is partial key different from primary key?
A partial key may only distinguish weak entities belonging to the same owner and is not necessarily globally unique. A primary key uniquely identifies a row in the relation.
9. Can total specialization also be overlapping?
Yes. Completeness and disjointness are separate constraints. Total means every supertype entity must belong to at least one subtype; overlapping means an entity may belong to more than one subtype.
10. When should aggregation be considered?
Consider aggregation when another relationship needs to refer to an existing relationship instance as a whole, not merely to one of its participating entities.
One-Minute Revision Sheet
Frequently Asked Questions
What are mapping constraints in ER Model?
Mapping constraints describe or limit how entities participate in relationships. The main ideas are cardinality ratio, participation constraints and min-max constraints.
What is the difference between primary key and candidate key?
A candidate key is any minimal unique identifier. The primary key is the candidate key chosen as the main identifier.
What is generalization in ER Model?
Generalization combines similar lower-level entity types into one higher-level supertype based on common features.
What is specialization in ER Model?
Specialization divides a higher-level entity type into lower-level subtypes based on distinguishing characteristics.
What is aggregation in ER Model with example?
Aggregation treats a relationship as a higher-level unit. Example: MANAGER monitors an EMPLOYEE working on a PROJECT, so EMPLOYEE-WORKS_ON-PROJECT can be aggregated and related to MANAGER through MONITORS.
What should I study after advanced ER Model?
Study the relational model, ER-to-relational mapping, integrity constraints, relational algebra, SQL, functional dependencies and normalization.
What Should You Learn Next?
Move from conceptual model to relational model
You now know how to model entities, keys, relationships, constraints, hierarchy and aggregation. The next logical chapter is Relational Model in DBMS: relation, tuple, attribute, domain, schema, instance, degree, cardinality, relational keys and integrity constraints.
After that, learn ER-to-relational mapping, relational algebra, SQL, functional dependency and normalization. For video revision after reading, you can use the DBMS YouTube study guide as a secondary resource.
