When mapping a supertype subtype relationship from an entity relationship model to a relational database schema, which of the following is generally true about the assignment of primary keys?

Introduction / Context:
This question deals with mapping entity relationship models that include supertype subtype relationships into relational database schemas. Supertypes represent general entities, while subtypes represent specialized categories that share the same identity. Knowing how primary keys are assigned and linked is important for maintaining consistency and enforcing constraints in the relational model.

Given Data / Assumptions:

- There is a supertype entity and one or more subtype entities.- We need to choose the correct rule for primary key assignment when mapping to tables.- Options mention various ways of connecting or not connecting keys.

Concept / Approach:
In common mapping strategies, the supertype is represented by a table that contains attributes common to all subtypes. Each subtype is mapped to its own table that contains subtype specific attributes. The primary key of each subtype table is the same as the primary key of the supertype, and in the subtype table that key also acts as a foreign key referencing the supertype table. This preserves the one to one relationship between a supertype row and any corresponding subtype row.

Step-by-Step Solution:
Step 1: Recall that supertypes and subtypes share the same identity, so they must share the same primary key value for a given instance.Step 2: The supertype table stores the core attributes and defines the primary key.Step 3: Each subtype table stores additional attributes and uses the same value as its primary key, referencing the supertype row.Step 4: This implies that the supertype primary key is copied into each subtype table as both primary key and foreign key.Step 5: Option A describes exactly this mapping pattern.Step 6: Options B, C, D, and E either reverse the relationship or remove it entirely, which contradicts standard mapping practice.

Verification / Alternative check:
Formal mapping rules in data modeling literature describe that for supertype subtype hierarchies, designers can use one of several approaches. In the common approach where each subtype gets its own table, the supertype key is used as the primary key of the subtype table, ensuring referential integrity and one to one correspondence. Practical database designs follow this pattern to ensure consistent identification. This validates option A as correct.

Why Other Options Are Wrong:
Option B suggests giving the subtype key to the supertype, which reverses the logical hierarchy. Options C and D claim that there is no link or no key relationship, which would make it impossible to trace which subtype rows belong to which supertype row. Option E suggests completely different primary keys for each subtype, which would break the shared identity concept and complicate joins and integrity checks.

Common Pitfalls:
A common mistake is to assign independent surrogate keys to subtypes without maintaining a referential link to the supertype, leading to inconsistent or duplicate identity information. Another pitfall is misunderstanding that a subtype must always have its own distinct key rather than sharing the supertype key. Recognizing that subtype rows represent the same real world entity as the supertype row helps clarify the correct mapping strategy.

Final Answer:
The correct mapping is that the supertype primary key is assigned as the primary key of each subtype and also acts as a foreign key back to the supertype.