Question

Database Design Using Entity-Relationship Diagrams.

Checkpoint 11.1

5. While mapping a binary 1:N relationship when the N side has full participation, why do we include the key of the 1 side of the table in the N side of the table? What would be wrong if we included the key of the N side of the table in the 1 side of the table?

Answer 1

When we create a table for a 1:N relationship, then we need to add the primary key of 1 side of the relationship in the N side of the table as the foreign key. This is because 1 side will have only one row per entry but the N side of the relationship can have multiple rows for each 1 side entry.

For example, consider a relation of Teacher-Subject. This is a 1:N relationship as a Teacher may teach multiple Subjects. So, in this, the primary key of Teacher table will be added to the Subjects table as its foreign key.

If we put the Subject foreign key values in the Teacher table then we would need a method to add multiple subjects in a single row of Teacher table, perhaps using something like an array or multi-valued data structure or a nested table. These complex attributes may not be supported by many systems and therefore we do not want to create such problems.