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.