ER Model in DBMS – Entity Relationship Diagram, Relational Model & Schema Conversion
What You Will Learn
- Entity types, entity sets, attributes, and keys
- Relationships: degree, cardinality, participation constraints
- Weak entities and identifying relationships
- Extended ER features: generalisation, specialisation, aggregation
- Relational model: tuples, domains, schema, integrity constraints
- ER diagram to relational schema conversion rules
- GATE PYQs with full solutions
GATE Weightage: 1–2 marks. Focus on conversion rules, weak entities, and cardinality constraints.
1. ER Model Basics
The Entity-Relationship (ER) model is a high-level conceptual data model used to design databases before implementation. It describes data in terms of entities, attributes, and relationships.
Entity and Entity Set
- Entity: A real-world object or concept distinguishable from others (e.g., student with ID 101)
- Entity Type: A category/template defining the structure (e.g., STUDENT) — drawn as a rectangle
- Entity Set: Collection of all entities of the same type at a point in time — the actual table rows
Keys
| Key Type | Definition |
|---|---|
| Super Key | Any set of attributes that uniquely identifies a tuple |
| Candidate Key | Minimal super key (no proper subset is a super key) |
| Primary Key | One chosen candidate key; underlined in ER diagram |
| Alternate Key | Candidate keys not chosen as primary key |
| Partial Key | Attribute that partially identifies a weak entity; dashed underline |
2. Attribute Types
| Attribute Type | Description | ER Symbol |
|---|---|---|
| Simple (Atomic) | Cannot be divided further (e.g., Age) | Oval |
| Composite | Can be split into sub-attributes (e.g., Name → First, Last) | Oval with sub-ovals |
| Multi-valued | Can have multiple values (e.g., PhoneNumbers) | Double oval |
| Derived | Computed from other attributes (e.g., Age from DOB) | Dashed oval |
| Stored | The base attribute from which derived ones are calculated | Oval |
| Null | Attribute that may have no value for some entities | — |
| Complex | Combination of composite + multi-valued | — |
3. Relationships & Constraints
A relationship type is an association among entities. Drawn as a diamond in ER diagrams.
Degree of Relationship
- Unary (recursive): Entity related to itself (e.g., EMPLOYEE manages EMPLOYEE)
- Binary: Two entity types (most common)
- Ternary: Three entity types involved
Cardinality Ratios
| Type | Meaning | Example |
|---|---|---|
| 1:1 | One entity in A associated with at most one in B | EMPLOYEE manages DEPARTMENT |
| 1:N | One entity in A associated with many in B | DEPARTMENT has many EMPLOYEES |
| M:N | Many entities in A associated with many in B | STUDENT enrolls in COURSE |
Participation Constraints
- Total participation (double line): Every entity must participate in at least one relationship instance. Translates to NOT NULL foreign key.
- Partial participation (single line): Some entities may not participate. Foreign key can be NULL.
Relationship Attributes
Relationships can have their own attributes. Example: EMPLOYEE works-on PROJECT with HoursPerWeek — this attribute belongs to the relationship, not either entity. In M:N relationships, relationship attributes move to the new relationship table.
4. Weak Entities
A weak entity cannot be uniquely identified by its own attributes alone. It depends on a strong (owner) entity through an identifying relationship.
| Concept | Symbol | Example |
|---|---|---|
| Weak entity type | Double rectangle | DEPENDENT |
| Identifying relationship | Double diamond | HAS-DEPENDENT |
| Partial key (discriminator) | Dashed underline | DependentName |
| Owner entity | Regular rectangle | EMPLOYEE |
Relational schema: DEPENDENT(SSN_EMP, DependentName, Age, Relationship)
Primary key = (SSN_EMP, DependentName); SSN_EMP is FK referencing EMPLOYEE.
5. Extended ER Features
Specialisation
Top-down process: a superclass entity is divided into specialised subclasses based on distinguishing characteristics. Example: PERSON → EMPLOYEE, CUSTOMER. Subclass inherits all superclass attributes and can have additional attributes.
Generalisation
Bottom-up process: multiple entity types are combined into a higher-level superclass based on common attributes. The reverse of specialisation. Example: ENGINEER, MANAGER → EMPLOYEE.
Constraints on Specialisation/Generalisation
- Disjoint (d): An entity can belong to only ONE subclass
- Overlapping (o): An entity can belong to multiple subclasses
- Total: Every superclass entity must belong to at least one subclass
- Partial: Some superclass entities may not belong to any subclass
Aggregation
Treats a relationship as a higher-level entity for use in another relationship. Used when a relationship itself needs to participate in another relationship. Example: EMPLOYEE works-on PROJECT (a relationship) is managed by MANAGER — the works-on relationship is aggregated into an entity-like box.
6. Relational Model
The relational model organises data into relations (tables). Each relation has a schema (structure) and an instance (current data).
Terminology
| Formal Term | Common Term | Meaning |
|---|---|---|
| Relation | Table | Set of tuples with same attributes |
| Tuple | Row / Record | One data entry |
| Attribute | Column / Field | Named property of entities |
| Domain | Data type | Set of allowed values for attribute |
| Degree | Arity | Number of attributes in a relation |
| Cardinality | — | Number of tuples in a relation |
| Schema | Table definition | Relation name + attribute names |
Integrity Constraints
- Domain constraint: Each value must be from the attribute’s domain
- Key constraint: Primary key values must be unique across all tuples
- Entity integrity: Primary key attributes cannot be NULL
- Referential integrity: Foreign key must match a primary key value in referenced relation, or be NULL
7. ER to Relational Schema Conversion
Converting an ER diagram to relational schema is a high-frequency GATE topic. Follow these rules systematically:
| ER Component | Relational Schema Rule |
|---|---|
| Strong entity type | Create a table with all simple attributes; PK is the entity’s key |
| Composite attribute | Include only the leaf (simple) sub-attributes, not the composite itself |
| Multi-valued attribute | Create a new table: entity PK + multi-valued attribute; PK = both columns |
| Derived attribute | Do NOT include in schema (computed when needed) |
| Weak entity type | Table with partial key + owner’s PK as FK; combined PK |
| 1:1 relationship | Add FK to either side; prefer the total-participation entity |
| 1:N relationship | Add FK on the N side (FK references 1-side PK) |
| M:N relationship | New relation table with both PKs as FKs; combined PK |
| Relationship attribute | Include in relationship table (for M:N) or on FK side (for 1:N) |
STUDENT(Sid, Name)
COURSE(Cid, Title)
ENROLLS(Sid, Cid, Grade) — Sid FK→STUDENT, Cid FK→COURSE
Example — EMPLOYEE (Eid, Name) works-in DEPARTMENT (Did, DName) (1:N):
EMPLOYEE(Eid, Name, Did) — Did FK→DEPARTMENT
DEPARTMENT(Did, DName)
Strong entities: one table each
Weak entities: one table each (merged with identifying relationship)
1:1 relationships: can merge into one of the entity tables (no new table needed)
1:N relationships: no new table needed (FK on N side)
M:N relationships: always need a new table
8. GATE Examples
GATE 2015: Consider an ER model where a STUDENT entity has a multi-valued attribute PhoneNumbers. When converted to relational model, how many tables are needed?
View Solution
Multi-valued attributes require a separate table.
Table 1: STUDENT(Sid, Name, …)
Table 2: STUDENT_PHONE(Sid, Phone) — Sid FK→STUDENT
Answer: 2 tables
GATE 2017: A relation R has 5 attributes. The only candidate key is {A, B}. How many super keys does R have?
View Solution
Super keys are all supersets of the candidate key {A, B}.
Remaining attributes = 5 – 2 = 3. Each can be included or not.
Number of super keys = 2³ = 8
GATE 2019: In an ER diagram, entity set E1 has total participation in relationship R and entity set E2 has partial participation. The cardinality is 1:N (E1 side = N). How many tables are needed minimum?
View Solution
1:N relationship: Add FK on the N side (E1). No new table needed.
Tables: E1 table (with FK to E2) + E2 table = 2 tables minimum
Total participation of E1 means the FK in E1 must be NOT NULL.
9. Common Mistakes
- Including derived attributes in schema: Derived attributes (like Age from DOB) should NOT be included in relational tables. They are computed on demand.
- Forgetting to split composite attributes: Only the leaf-level simple sub-attributes go into the table, not the composite attribute name itself. Name → {FirstName, LastName}; the column “Name” doesn’t exist.
- 1:1 relationship — FK placement: For 1:1, add FK to the entity with total participation (that entity cannot exist without the relationship, so the FK should be on its side and will be NOT NULL).
- Weak entity table: The weak entity table’s primary key is (owner PK + partial key). The owner PK alone is not the PK of the weak entity table.
- Confusing cardinality with degree: Degree = number of entity types in a relationship (binary = 2). Cardinality = 1:1, 1:N, M:N ratio between participating entities.
10. Frequently Asked Questions
What is the difference between entity and entity type in ER model?
An entity type is the schema/template (e.g., STUDENT with attributes Sid, Name, Age). An entity is one specific instance (e.g., the student with Sid=101). The entity set is all current instances. This parallels class vs. object in OOP — entity type is the class, entity is an object.
What is a weak entity in DBMS and how is it represented?
A weak entity lacks a key attribute of its own — it depends on an owner entity for identification. It has a partial key (discriminator) unique only within one owner entity’s scope. Represented by a double rectangle (weak entity), double diamond (identifying relationship), dashed underline (partial key). Its table’s PK = owner’s PK + partial key, with a CASCADE delete constraint.
How do you convert an ER diagram to a relational schema?
Rules: Strong entity → table. Weak entity → table with owner PK + partial key. 1:1 → FK on total-participation side. 1:N → FK on N-side. M:N → new table with both PKs. Multi-valued attribute → separate table. Derived attribute → omit. Composite attribute → use leaf sub-attributes only.
What is the difference between total and partial participation in ER model?
Total participation (double line) means every entity must be in at least one relationship instance — translates to NOT NULL constraint on the FK. Partial participation (single line) means some entities may not be involved — FK can be NULL. Example: every EMPLOYEE must work in a DEPARTMENT (total) but some DEPARTMENTS may have no manager assigned (partial).
What are the constraints of the relational model?
Domain constraint: values must come from the attribute’s domain. Key constraint: primary key is unique across all tuples. Entity integrity: PK attributes cannot be NULL. Referential integrity: FK values must exist in the referenced relation’s PK or be NULL. These four constraints ensure relational database consistency and are enforced by the DBMS automatically.