CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the direction (15 minutes)

State whether starting from a general "Vehicle" entity and splitting it into "Car" and "Truck" is specialization or generalization.

Done when: specialization is correctly named.

Hint

Splitting a general entity into specific ones is specialization.

Easy 2: Name the direction, reversed (15 minutes)

State whether starting from already-separate "Car" and "Truck" entities, then noticing they share attributes and introducing "Vehicle," is specialization or generalization.

Done when: generalization is correctly named.

Hint

Pulling shared attributes up from already-separate entities is generalization.

Easy 3: Spot a candidate for aggregation (15 minutes)

Explain why "Customer places Order," together with a further fact "flagged by FraudTeam," is a candidate for aggregation.

Done when: the explanation correctly notes the flag depends on the whole placing-event, not on Customer or Order alone.

Hint

The fraud flag is about one specific order placed by one specific customer together, not a fact of either entity in isolation.

Medium

Medium 1: Build a specialization hierarchy (25 minutes)

Model a Payment superclass specializing into CardPayment (with a last_four_digits attribute) and CashPayment (with no extra attribute), sharing amount and paid_on.

Done when: a correctly structured ISA hierarchy with the right shared and subclass-specific attributes is given.

Hint

Payment (amount, paid_on) ISA CardPayment (last_four_digits), ISA CashPayment ().

Medium 2: Decide if specialization is worth it (25 minutes)

Decide whether Book and Audiobook, if they actually share every single attribute with no exceptions, are worth modeling as a specialization hierarchy at all.

Done when: the decision correctly leans toward not splitting them, since specialization only earns its complexity when subtypes genuinely differ.

Hint

With no differing attributes at all, there is nothing for specialization to add, so a single flat entity is simpler and equally correct.

Medium 3: Identify the aggregated entity (25 minutes)

For "Student enrolls in Course," and a further fact "graded by Instructor" that depends on the specific enrollment, name what gets aggregated.

Done when: the Student-enrolls-Course relationship is correctly identified as the entity being aggregated.

Hint

The Student-enrolls-Course relationship itself is the aggregated entity that "graded by Instructor" connects to.

Hard

Hard 1: Translating specialization to tables, previewed (30 minutes)

Without yet using the next module's formal rules, propose how Account, CustomerAccount and StaffAccount might become actual tables, and name the challenge of keeping shared attributes in sync.

Done when: a plausible table design (such as one table per subclass, or a single table with a type column) is proposed, with the sync challenge named for whichever approach involves more than one table.

Hint

One common approach stores shared attributes in accounts and subclass-specific attributes in separate customer_accounts/staff_accounts tables linked by a shared ID, risking the two staying in sync; another stores everything in one table with a type column and nullable subclass-specific columns.

Hard 2: When generalization reveals a modeling mistake (35 minutes)

Explain how discovering, during generalization, that two "separate" entities actually share nearly every attribute could reveal that they should have been one entity all along, not two subtypes of a new one.

Done when: the explanation correctly notes that near-total attribute overlap, with no meaningful subtype-specific attributes, suggests merging rather than generalizing.

Hint

If every attribute overlaps with no real subtype-specific difference, the two "subtypes" were likely always the same entity, and no hierarchy was actually needed.

Hard 3: Aggregation versus a simple extra foreign key (40 minutes)

Explain why "reviewed by SupportAgent" cannot simply be modeled as a foreign key added directly onto the Order table, instead of using aggregation on the Customer-places-Order relationship.

Done when: the explanation correctly notes that if the fact genuinely depends on the Customer-Order pairing rather than the Order alone, flattening it onto Order loses that distinction, though it also correctly recognizes that in many real cases a direct foreign key on Order would be simpler and just as correct.

Hint

A fact truly about the pairing, not about the order alone, only stays precisely modeled through aggregation; in practice, many teams accept the simplification of a direct foreign key on Order when the distinction rarely matters.