GATE DA

The entity-relationship (ER) model

The entity-relationship (ER) model describes data as entities, their attributes and the relationships between them, together with cardinality and participation constraints. For GATE you must read an ER diagram correctly in any of its common notations, bound the size of a relationship set from above and below, and convert a diagram into the minimum number of relations with the right keys.

1 previous-year question · 1 solved · 6 practice questions

Entities, attributes and keys

An entity is a distinguishable object. An entity set is a collection of entities of the same type (drawn as a rectangle). Each entity set has attributes (ovals) that describe it. A superkey is a set of attributes whose values identify an entity within its set. A candidate key is a minimal superkey, and the primary key is the candidate key chosen by the designer (its attributes are underlined).

Attribute kind	Meaning	Drawn as	In relations
Simple (atomic)	Cannot be divided further	Oval	One column
Composite	Made of sub-attributes, e.g. address = (city, pin)	Oval with child ovals	Only the leaf sub-attributes become columns
Multivalued	Can hold several values for one entity, e.g. phone	Double oval	Separate relation (entity key + attribute)
Derived	Computed from other attributes, e.g. age from date of birth	Dashed oval	Normally not stored
Key	Unique per entity in the set	Underlined name	Primary key of the relation

EXAM TIP

A candidate key may itself be composite, and an entity set can have several candidate keys. The diagram underlines the one chosen as primary key. A weak entity set has no key of its own. It has only a partial key (discriminator), drawn with a dashed underline.

Relationships, degree, cardinality and participation

A relationship associates entities (diamond). A relationship set is a set of relationship instances, and each instance is identified by the combination of entities it relates, so no two instances relate the same combination, even when the relationship has descriptive attributes such as the grade in an enrolment. The degree is the number of entity-set roles taking part: 1 is recursive (unary), 2 is binary, 3 is ternary. In a recursive relationship the same entity set appears twice under different role names, e.g. an employee supervises an employee.

Cardinality ratio (A to B)	Reading	Arrow notation	Typical placement of the foreign key
1:1	Each A is related to at most one B and each B to at most one A	Arrows to both A and B	On either side; prefer the side with total participation
1:N	Each B is related to at most one A; an A may relate to many B	Arrow to A only	On the N side (the B relation)
M:N	No limit on either side	No arrows	Separate relation for the relationship

Participation is total (double line) when every entity of the set must take part in the relationship, and partial (single line) when some may not. The cardinality ratio limits the maximum number of relationship instances an entity can take part in. Participation fixes the minimum: at least one (total) or zero (partial).

EXAM TIP

Notations differ, so read the diagram's convention first. Arrow notation: an arrow from the relationship to entity set $E$ means each entity on the other side relates to at most one $E$ . 1/N labels: for 1:N from A to B, the '1' is written beside A and the 'N' beside B. (min, max) notation: the pair beside an entity set counts how many relationship instances each entity of that set takes part in. For the same 1:N relationship, B carries $(0, 1)$ or $(1, 1)$ and A carries $(0, N)$ or $(1, N)$ . So the maximum values sit on the opposite sides from the 1/N labels. A minimum of 1 means total participation.

UPPER BOUND ON A RELATIONSHIP SET

∣ R ∣ \leq ∣ E_{1} ∣ \cdot ∣ E_{2} ∣ \dots ∣ E_{n} ∣

For an n-ary relationship set $R$ over entity sets $E_{1}, \dots, E_{n}$ . It holds because no two instances relate the same combination of entities. The bound is reached only when every combination is related.

BOUND FROM AN AT-MOST-ONE SIDE

∣ R ∣ \leq ∣ B ∣ if each b \in B appears in at most one tuple of R

In a binary 1:N relationship from A to B (each B related to at most one A), the count is at most $∣ B ∣$ . With total participation of B it equals $∣ B ∣$ . In a 1:1 relationship both sides are at-most-one, so $∣ R ∣ \leq min (∣ A ∣, ∣ B ∣)$ .

LOWER BOUND FROM TOTAL PARTICIPATION

∣ R ∣ \geq ∣ E_{i} ∣ if E_{i} participates totally in R

Every entity of $E_{i}$ appears in at least one instance, and each instance contains exactly one entity of $E_{i}$ .

BINARY M:N WITH TOTAL PARTICIPATION ON BOTH SIDES

max (∣ A ∣, ∣ B ∣) \leq ∣ R ∣ \leq ∣ A ∣ \cdot ∣ B ∣

Both bounds can be reached: the lower one by covering every entity with as few pairs as possible, the upper one by relating every pair.

Weak entities and identifying relationships

A weak entity set has no key of its own and exists only in relation to an owner (identifying) entity set. It is drawn as a double rectangle, linked to its owner by an identifying relationship (double diamond). The identifying relationship is many-to-one from the weak entity set to its owner: each weak entity is related to exactly one owner, and the weak entity set participates totally. Within one owner, its entities are told apart by the partial key.

KEY OF A WEAK ENTITY'S RELATION

PK (W) = PK (owner) \cup {partial key of W}

The owner's key is copied in as a foreign key, normally declared ON DELETE CASCADE so that deleting an owner removes its weak entities. No separate relation is made for the identifying relationship.

EXAM TIP

The partial key alone is not unique across the whole weak entity set. For example, section number 1 can exist under every course, so uniqueness needs the owner's key as well. If the owner is itself a weak entity set, its whole primary key (which already includes its own owner's key) is copied in.

Specialisation, generalisation and aggregation

Specialisation is top-down: an entity set is split into sub-classes that have extra attributes or relationships (ISA triangle). Generalisation is the bottom-up reverse: several entity sets with common attributes are merged into a super-class. Sub-class entities inherit the super-class's attributes, key and relationships.

Constraint	Options	Meaning
Disjointness	Disjoint / overlapping	Whether an entity may belong to at most one sub-class or to several
Completeness	Total / partial	Whether every super-class entity must belong to some sub-class

One relation for the super-class and one per sub-class holding the super-class key plus the sub-class attributes. This works for every constraint combination.
One relation per sub-class holding all attributes, including inherited ones. It needs a total specialisation, because otherwise super-class-only entities would be lost. With overlapping sub-classes it repeats the inherited attributes in each sub-class an entity belongs to.
One single relation holding all attributes. For disjoint sub-classes, add one type attribute; for overlapping sub-classes, add one boolean flag per sub-class. Attributes that do not apply are NULL.

Aggregation treats a relationship set, together with the entity sets it relates, as a higher-level entity so that it can take part in another relationship. For example, (EMPLOYEE works on PROJECT) as a whole is monitored by a MANAGER. When mapped, the relation for the outer relationship holds the primary key of the aggregated relationship's relation, plus the key of the other participant.

Converting an ER diagram to relations

Strong entity set: one relation. Its columns are the simple attributes and the leaves of composite attributes; multivalued and derived attributes are left out. Its primary key is the entity set's key.
Multivalued attribute: one extra relation with the entity's key and the attribute. Its primary key is both together.
Weak entity set: one relation with the owner's key, the partial key and its own attributes.
Binary 1:N: add the key of the 1 side, with the relationship's attributes, as a foreign key in the N side's relation. No new relation. If the N side's participation is partial, the foreign key may be NULL.
Binary 1:1: merge into one side by a foreign key, preferably the side with total participation. If both sides participate totally, the two entity sets can be merged into a single relation, whose primary key is either entity's key; the other becomes a candidate key.
Binary M:N: a new relation holding both entity keys and the relationship's attributes. Its primary key is the union of the two keys.
Recursive (unary): a 1:N recursive relationship adds a foreign key to the same relation, e.g. manager_id in Employee. An M:N recursive relationship needs a new relation holding the key twice under two role names.
Ternary or higher: a new relation holding every participant's key. The primary key is chosen from the cardinalities (see the tip below).

Relationship	Primary key of its relation (if mapped separately)
Binary M:N	$PK (A) \cup PK (B)$
Binary 1:N from A to B	$PK (B)$ , the key of the N side
Binary 1:1	Either $PK (A)$ or $PK (B)$
n-ary with no 'one' side	Union of all participants' keys

KEY OF AN M:N RELATIONSHIP RELATION

PK (R) = PK (A) \cup PK (B)

Applies to a binary many-to-many relationship. Each key column is also a foreign key.

MINIMUM NUMBER OF TABLES (DEFAULT COUNT)

# tables = # entity sets (strong and weak) + # multivalued attributes + # M:N and n-ary relationship sets

Counts recursive M:N relationship sets too. This is the count after merging every 1:1 and 1:N relationship (identifying relationships included) into an entity's relation. Each 1:1 relationship with total participation on both sides reduces the count by one more. Assumes no specialisation or aggregation.

EXAM TIP

For a ternary relationship over X, Y and Z: if each combination of two participants relates to at most one entity of the third (say each $(x, y)$ relates to at most one $z$ ), the third is left out of the key, giving ${x, y}$ . If a second such constraint also holds (each $(x, z)$ to at most one $y$ ), both ${x, y}$ and ${x, z}$ are candidate keys and either may be the primary key. If no such constraint holds, the key is ${x, y, z}$ . To avoid ambiguity, arrow notation normally allows at most one arrow out of a non-binary relationship.

Worked examples

WORKED EXAMPLE

An ER diagram has entity sets DOCTOR(did, name, phones) with phones multivalued, WARD(wid, wname), PATIENT(pid, pname) and CLINIC(cid, city). Relationships: WORKS_IN is 1:N from WARD to DOCTOR (each doctor in exactly one ward); TREATS is M:N between DOCTOR and PATIENT with attribute fee; HOSTS is 1:1 between CLINIC and WARD, with total participation on both sides. What is the minimum number of relations?

Solution

Entity sets: DOCTOR, WARD, PATIENT and CLINIC give 4 relations.
The multivalued attribute phones needs DOCTOR_PHONE(did, phone): 5 relations.
WORKS_IN is 1:N, so wid goes into DOCTOR as a foreign key: no new relation.
TREATS is M:N, so TREATS(did, pid, fee) is new: 6 relations.
HOSTS is 1:1 and total on both sides, so CLINIC and WARD merge into one relation (wid, wname, cid, city). Take wid as its primary key; cid becomes a candidate key, and DOCTOR.wid references the merged relation. Count: 6 - 1 = 5 relations.

Answer: 5 relations

WORKED EXAMPLE

COURSE(cid, title) owns a weak entity set SECTION with partial key secno and attribute room. Give the relation for SECTION and its primary key.

Solution

The identifying relationship is many-to-one from SECTION to COURSE with total participation of SECTION, so it creates no relation.
SECTION(cid, secno, room), where cid is a foreign key to COURSE.
The primary key is {cid, secno}: the owner's key together with the partial key.

Answer: SECTION(cid, secno, room) with primary key {cid, secno}

PREVIOUS-YEAR QUESTIONS

1 question from the DA papers

Try each question first, then open the answer. We placed each question under a topic ourselves; the exam paper does not label topics.

See these papers in full: GATE DA 2026

GATE 2026 DA Q.61

GATE 2026 · DAQ.61 · 2 marks · NAT

Consider an ER model with the entities E1(A11, A12, A13) and E2(A21, A22, A23), where A11, A12, A13 are the attributes of E1, and A21, A22, A23 are the attributes of E2. Let A22 be a multi-valued attribute. A11 and A21 are the primary keys of E1 and E2, respectively.

Let R12 be a many-to-many relationship between E1 and E2. Participation of both E1 and E2 in R12 is total.

The minimum number of relations required to convert the ER model into relational model (assuming there is no other functional dependency) where each relation is in third normal form (3NF) is ______. (Answer in integer)

Answer and solution

GATE 2026 DA Q.61: answer and solution

See the question in the paper

Tap to open full size

GATE 2026 DA Q.61: answer and solution

PRACTICE

6 questions to test yourself

PRACTICE 11 mark · MCQ

Entity sets P(p1, p2) and Q(q1, q2) have keys p1 and q1. A binary relationship R between them is one-to-many from P to Q (each Q entity is related to at most one P entity), with total participation of Q, and R has no attributes. What is the minimum number of relations needed to represent this diagram?

(A)
1
(B)
2
(C)
3
(D)
4

Answer and solution

Answer: (B)

Each entity set gives one relation: P and Q.
R is 1:N, so the key p1 is added to Q as a foreign key. R needs no relation of its own.
P cannot be merged into Q, because one P entity may relate to many Q entities.
Total: 2 relations.

PRACTICE 22 marks · NAT

An ER diagram has: entity set STUDENT(sid, name, phone, address), where sid is the key, phone is multivalued and address is composite with sub-attributes city and pin; entity set COURSE(cid, title) with key cid; a weak entity set SECTION with partial key secno, owned by COURSE through an identifying relationship; and an M:N relationship ENROLS between STUDENT and COURSE with attribute grade. Each entity set, multivalued attribute and M:N relationship is mapped separately, and 1:N relationships are merged. What is the minimum number of relations?

Answer and solution

Answer: $5$

STUDENT(sid, name, city, pin), with the composite address flattened: 1 relation.
Multivalued phone gives STUDENT_PHONE(sid, phone): 2 relations.
COURSE(cid, title): 3 relations.
SECTION(cid, secno): 4 relations. The identifying relationship adds none.
ENROLS(sid, cid, grade): 5 relations.

PRACTICE 32 marks · MSQ

Which of the following statements about the ER model and its conversion to relations are TRUE?

(A)
For a binary M:N relationship, the relation created for it has the union of the two entity keys as its primary key.
(B)
A weak entity set must participate totally in its identifying relationship.
(C)
The partial key of a weak entity set is unique across all entities of that set.
(D)
A multivalued attribute can be kept as a single column of the entity's relation without violating first normal form.

Answer and solution

Answer: (A), (B)

A is true: in an M:N relationship neither key alone identifies an instance, but the pair of entity keys does.
B is true: every weak entity must be related to an owner, because it cannot be identified without one.
C is false: the partial key is unique only among the weak entities of one owner.
D is false: a multivalued column would hold several values in one cell, so the relation would not be in 1NF. It needs a separate relation.

PRACTICE 41 mark · NAT

Entity set A has 6 entities and entity set B has 4 entities. A binary relationship R between them is such that each entity of B takes part in at most one tuple of R, while an A entity may take part in any number of tuples. What is the maximum possible number of tuples in R?

Answer and solution

Answer: $4$

Each tuple of R contains exactly one B entity, and each B entity appears in at most one tuple.
So $∣ R ∣ \leq ∣ B ∣ = 4$ .
The bound is reached when every B entity is related to some A entity.

PRACTICE 52 marks · MCQ

A ternary relationship R relates entity sets X, Y and Z with primary keys x, y and z. For every pair (x, y) there is at most one related z. For a fixed (x, z) there may be many related y, and for a fixed (y, z) there may be many related x. Which attribute set is the primary key of the relation representing R?

(A)
{x}
(B)
{x, y}
(C)
{x, y, z}
(D)
{z}

Answer and solution

Answer: (B)

Each pair (x, y) determines at most one z, so {x, y} identifies a tuple of R and z is not needed in the key.
{x, z} and {y, z} do not identify a tuple, because a fixed (x, z) can occur with many y and a fixed (y, z) with many x.
No single attribute identifies a tuple, since each can occur with many partners.
So {x, y} is the only candidate key. {x, y, z} is a superkey but not minimal.

PRACTICE 61 mark · NAT

Entity set A has 5 entities and entity set B has 3 entities. A binary M:N relationship R between them has total participation of both A and B. What is the minimum possible number of tuples in R?

Answer and solution

Answer: $5$

Total participation of A: each of the 5 A entities appears in at least one tuple, and each tuple has exactly one A entity, so $∣ R ∣ \geq 5$ .
Total participation of B gives only $∣ R ∣ \geq 3$ , so the binding bound is $max (5, 3) = 5$ .
5 is reachable: (a1, b1), (a2, b2), (a3, b3), (a4, b1), (a5, b1) cover every entity of both sets.
Minimum = 5.

OFFICIAL GATE 2027 SYLLABUS WORDING

Copied from the GATE 2027 syllabus; see all 2027 syllabi.

GATE DA

ER-model

Preparing in Kolkata? IAE Academy's course for GATE DA.