CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the level (15 minutes)

Name which level each of these belongs to. (a) A B+-tree index on a column. (b) The CREATE TABLE statement for orders. (c) A view showing only three of a table's five columns to one application.

Done when: all three are correctly assigned to internal, conceptual or external.

Hint

An index is internal; a CREATE TABLE statement describes the conceptual level; a view is external.

Easy 2: Define both independences in one line each (15 minutes)

Write one sentence defining logical data independence. Write a second, differently worded sentence defining physical data independence.

Done when: each sentence correctly names which level changes and which level stays unaffected.

Hint

Logical independence protects against conceptual-level change; physical independence protects against internal-level change.

Easy 3: Classify a change (15 minutes)

A DBA switches orders from one file organization to another. No SQL anywhere needs to change. State which kind of independence this tests.

Done when: physical data independence is correctly named.

Hint

A file organization change touches only how data sits on disk.

Medium

Medium 1: Break logical data independence on purpose (25 minutes)

Describe a conceptual-level change to customers that would force billing_customers (and the billing application) to change, unlike the loyalty_tier example in this page.

Done when: a change is described that actually removes or renames a column the view depends on.

Hint

Removing or renaming address itself, which the view actually selects, would force the view to change.

Medium 2: Two views, two levels of exposure (25 minutes)

Design two external-level views on orders (order_id, customer_id, placed_on). The shipping team needs only order_id and placed_on. The finance team needs all three columns. Write both as CREATE VIEW statements.

Done when: two CREATE VIEW statements are given, each exposing the correct subset of columns.

Hint

Each view just needs a SELECT listing the required columns from orders.

Medium 3: Why physical independence doesn't help here (25 minutes)

Adding a discount_percent column to orders also changes how much space each row takes on disk. Explain why the column addition itself is still a conceptual-level change, not a physical one.

Done when: the explanation correctly separates the logical addition of a column from its physical storage consequences.

Hint

The column addition is a schema change at the conceptual level. The extra disk space it needs is only a physical-level side effect of that change, not the change itself.

Hard

Hard 1: A migration that preserves both independences (30 minutes)

Describe a database migration, such as splitting customers into customers and customer_addresses. Show how the right views could preserve logical data independence for existing applications. Name what those views would need to do.

Done when: a migration is described along with a compensating view that reconstructs the old external shape from the new tables.

Hint

A view recombining customers and customer_addresses back into the old single-table shape would let existing queries run unmodified.

Hard 2: When physical data independence has limits (35 minutes)

Switching orders to a partitioned table, split across several physical pieces by date, is supposed to be a purely internal-level change. Explain why some real systems still require query changes for it anyway.

Done when: a concrete limitation is named, such as query hints or partition-aware syntax leaking the physical change into application code.

Hint

Some systems require queries to name a partition key or use partition-aware functions to get the performance benefit, which touches application code.

Hard 3: Three levels for a brand-new feature (40 minutes)

A new feature needs orders to track a delivery_status column, but only the logistics team should ever see it. Describe the conceptual-level change needed, and design the external-level view for every other team that should not see this column.

Done when: the conceptual change is stated, and a view is given that correctly excludes delivery_status for other teams.

Hint

Add delivery_status to orders at the conceptual level, then create a view selecting every other column except it for non-logistics teams.