GATE CS 2017 Set 2 Q17 — ER-model

To determine when the relational table for a relationship $R$ between entity types $A$ and $B$ can be merged with the table for $A$, we analyze the cardinality and participation constraints:

1. Cardinality Constraint (Many-to-One from $A$ to $B$):

   For the relationship $R$ to be merged into the table of $A$, each entity in $A$ must be associated with at most one entity in $B$. This ensures that we can represent the relationship by simply adding the primary key of $B$ as a foreign key in the table of $A$ without violating the First Normal Form (1NF). This corresponds to a many-to-one relationship from $A$ to $B$ (where $A$ is on the "many" side).

2. Participation Constraint (Total Participation of $A$):

   If the participation of $A$ in $R$ is total, every entity in $A$ must be associated with at least one entity in $B$. Combined with the many-to-one constraint, this means every entity in $A$ is associated with exactly one entity in $B$. Consequently, the foreign key column in the merged table for $A$ will never contain NULL values.

   If the participation of $A$ were partial, some entities in $A$ would not be associated with any entity in $B$, leading to NULL values in the foreign key column.

Thus, the table for $R$ can be seamlessly merged with $A$ when the relationship $R$ is many-to-one (from $A$ to $B$) and the participation of $A$ in $R$ is total.