Questions: ER Model: Weak Entities and Specialization Hierarchies

Question 1

A database models course sections, where each section is identified by a number (1, 2, 3) unique only within a specific course. How should this be modeled?

• A. Section as a regular entity with section number as its primary key
• B. Section as a weak entity with section number as its partial key and Course as its identifying owner
• C. Section as a weak entity with course ID as its primary key
• D. Section as an attribute of the Course entity

Answer: (B)

Section numbers are only unique within a course — 'Section 2' of CS101 differs from 'Section 2' of MATH201. This is the classic weak entity pattern: Section cannot be identified by its own attributes alone. Its partial key (section number) combined with the owner's primary key (course ID) forms its full identity. Option A is wrong because numbers collide across courses. Option C is wrong because it misidentifies which key belongs to the weak entity. Option D loses the ability to record section-specific data like enrollment or instructor.

Question 2

A university models Person with subclasses Student and Faculty. Queries almost always access only Student-specific attributes for one subclass at a time. Which ISA mapping strategy minimizes joins for these queries?

• A. Separate tables for Person, Student, and Faculty — Student references Person via foreign key
• B. One table per subclass only, with shared Person attributes duplicated in each
• C. A single combined table with a type discriminator and nullable columns for subclass attributes
• D. All three strategies produce equivalent join costs for this query pattern

Answer: (B)

When queries focus on one subclass at a time, having a separate Student table avoids the overhead of scanning a large combined table. The single-table approach (C) avoids joins but wastes space on nulls and weakens constraints. The subclass-only approach (B) avoids joins but duplicates shared attributes and makes cross-subclass queries expensive. Option A (separate tables) requires a join to reconstruct a full person record but is efficient when queries target subclass-specific attributes.

Question 3

When a weak entity's identifying strong entity is deleted, the weak entities it owns should also be deleted.

• T. True
• F. False

Answer: True

This follows directly from the semantics of weak entities. A weak entity depends on its identifying owner for its very existence and identity — an apartment record in a demolished building has no meaning. In SQL, this is implemented with ON DELETE CASCADE on the foreign key linking the weak entity to its owner. Allowing the strong entity to be deleted while leaving weak entities behind would create orphaned records with broken identity, violating the dependency that motivated the weak entity model.

Question 4

In an ISA hierarchy, 'total' specialization means that nearly every subclass entity is expected to belong to nearly every superclass.

• T. True
• F. False

Answer: False

This conflates two independent dimensions of ISA constraints. 'Total' specialization means every superclass entity must belong to at least one subclass — every Person must be either a Student or Faculty (or both, if overlapping). It says nothing about subclass entities and superclasses. 'Disjoint' vs 'overlapping' governs whether an entity can belong to multiple subclasses simultaneously. Total/partial controls coverage of the superclass; disjoint/overlapping controls overlap among subclasses. These are orthogonal properties.

Question 5

Why does a weak entity use a partial key rather than its own full primary key?

Model answer: A weak entity lacks attributes that can uniquely identify its instances across all contexts — its partial key is only unique within the scope of its identifying owner. Apartment '3B' is not globally unique, but 'Building 7, Apartment 3B' is. The full primary key in a relational schema must therefore be a composite of its partial key and the owner's primary key. This composite key also encodes the real-world dependency: the apartment's identity is inherently tied to the building.

Assigning a surrogate key (like a sequential ID) would technically avoid collisions but would hide the natural dependency relationship and make the schema harder to understand. The partial key concept captures a genuine ontological fact: these entities exist and have meaning only relative to their owners.