Minimum-table questions are not solved by counting every rectangle and diamond. A relationship may need its own table, may disappear into one entity table, or may let two entity tables combine. Cardinality tells you whether one foreign-key value is enough, while participation tells you whether that value can be missing.
Baseline ER-to-relational mapping rules
Begin with the objects that always need storage:
Each strong entity set normally becomes one table.
Simple attributes become columns. Composite attributes are flattened into their simple components.
Derived attributes need not be stored if they can be computed.
Each multivalued attribute becomes a separate table containing the owner key and one attribute value per row.
A weak entity becomes a table containing the owner's primary key and the weak entity's partial key.
A binary relationship starts as a candidate for a separate table. The cardinality and participation constraints decide whether that relationship table can be absorbed. This logic leads into normalization later, but mapping and normalization are different tasks. The normalization from 1NF to BCNF guide handles the later decomposition questions.
Cardinality and participation decide the count
ER situation | Minimum mapping for two strong entities | Total tables | Reason |
|---|---|---|---|
M:N relationship | Entity tables plus relationship table | 3 | Neither entity row can hold many foreign-key values |
1:N relationship | Put the 1-side key in the N-side table | 2 | Each N-side row has at most one partner on the 1 side |
1:1, not total on both sides | Put a unique foreign key in one entity table | 2 | The relationship disappears, but unmatched entity rows still need their own table |
1:1, total on both sides | Combine both entities and relationship | 1 | Every row has exactly one partner |
Relationship attributes follow the relationship. If a 1:N relationship is absorbed by the N side, its attributes move there. If an M:N relationship keeps a separate table, its attributes stay in that table.
Worked 1:1 mappings
Suppose Employee(Eid, name) and Locker(Lid, room) are connected by Assigned with 1:1 cardinality.
If every employee has exactly one locker and every locker belongs to exactly one employee, participation is total on both sides. Store Eid, name, Lid, room in one combined relation. Either entity key can identify a row, while the other must remain unique. Minimum: 1 table.
Now change only participation. If some employees have no locker, or some lockers are unassigned, the two entity sets still need separate tables. Absorb Assigned into a side by adding the other key as a unique foreign key. If exactly one side has total participation, choose that side to avoid NULL in the foreign key. Minimum: 2 tables. If neither side is total, the foreign key can be nullable and unique, still giving 2 tables.
The distinction is easy to state: 1:1 removes the relationship table; total participation on both sides can also remove one entity table.
Worked 1:N and M:N mappings
Let Department(DeptId) relate to Employee(Eid) through WorksIn, with one department linked to many employees. Put DeptId into Employee as a foreign key. If WorksIn has attribute startDate, put it in Employee too. Minimum: 2 tables. Partial participation of Employee only changes whether DeptId can be NULL, not the count.
Now let Student(Sid) and Course(Cid) connect through M:N Enrols, with attribute grade. Keep Student and Course, then add Enrols(Sid, Cid, grade). A row cannot store a variable-length set of course keys in one atomic foreign-key column. Minimum: 3 tables. Total participation does not reduce this count.
These counts are direct:
two entity tables plus no separate 1:N table:
2 + 0 = 2two entity tables plus one M:N table:
2 + 1 = 3
Weak entities and identifying relationships
Consider strong entity Order(OrderId) and weak entity OrderItem(ItemNo, quantity). OrderItem is identified by owner key OrderId plus partial key ItemNo. The mapping is:
Order(OrderId, ...)OrderItem(OrderId, ItemNo, quantity, ...)
The primary key of OrderItem is (OrderId, ItemNo). Its identifying relationship does not need a third table because OrderId already represents that relationship inside the weak entity table. Minimum: 2 tables.
This count remains 2 whether or not the identifying relationship has a name in the diagram. A separate non-identifying M:N relationship involving OrderItem would still add its own table.
Multivalued attributes add tables
Suppose Employee has multivalued attribute phone. Keep Employee(Eid, ...) and add EmployeePhone(Eid, phone). Each phone value occupies a separate row. One multivalued attribute adds exactly 1 table. Two independent multivalued attributes, such as phone and skill, require two separate tables because combining them would create false phone-skill combinations.
Apply this after the relationship count. The 1:N Department-Employee mapping above takes 2 tables. Add Employee.phone and the verified count becomes 2 + 1 = 3.
Mixed configurations and recursive relationships
If two strong entities have both a 1:N relationship and an M:N relationship, judge them separately. Start with 2 entity tables. Absorb the 1:N relationship into the N side, adding 0. Keep the M:N relationship separately, adding 1. Minimum: `2 + 0 + 1 = 3` tables.
A recursive 1:N relationship, such as Employee reports to Employee, needs only a self-referencing foreign key in Employee. It adds no table. A recursive M:N relationship, such as Course has prerequisite Course when several prerequisites and dependants are allowed, needs a separate two-key table.
How GATE tests minimum-table mapping
The direct question gives a diagram and asks for a count. Indirect questions ask where relationship attributes go, which participation change permits a merge, or which ER fragment matches a relational schema. A useful second pass is the DBMS normalization explained post, which shows what happens after mapping.
About 2,100 published Database Management System questions in the KnowledgeGate bank provide wider practice. Confirm current paper instructions on the official GATE portal of the organising IIT.
The short version
Count strong and weak entity tables first. Add one table for every multivalued attribute and every M:N relationship. Absorb 1:N into the N side. For 1:1, remove the relationship table; combine the two entity tables only when participation is total on both sides.
Draw the mapping beside the ER fragment before giving the number. Then use the GATE Test Series for timed DBMS work, and place this drill in your wider plan through the GATE category page.
