SchemaWright

Fundamentals

The key the business insists on renaming

Difficulty 2/5Free

Logistics tracks shipments leaving warehouses.

Every warehouse has a short code that humans use everywhere, such as SP01, RJ02. It is printed on labels, typed into spreadsheets, and read out on the phone. It is genuinely unique, and it is genuinely the thing people mean when they say "which warehouse".

It is also not stable. Operations reorganises regions and renames codes, and they will not stop doing that because a database found it inconvenient.

What has to hold:

  • Two warehouses can never share a code.
  • A shipment always belongs to a warehouse that exists.
  • When a code is renamed, no shipment is lost and none is left pointing at the old code. After the rename, looking up the warehouse's shipments by its new code finds all of them.
  • A shipment's tracking number is unique across the whole company.

warehouses already exists, and its code is the primary key.

The one thing that is fixed

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

  • warehouses

    code

  • shipments

    idwarehouse_codetracking_number

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql
CREATE TABLE warehouses (
  code TEXT PRIMARY KEY,
  city TEXT NOT NULL
);

INSERT INTO warehouses (code, city) VALUES ('SP01', 'Sao Paulo'), ('RJ02', 'Rio de Janeiro');
PostgreSQL DDL: tables, constraints, indexes
schema.sql

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

6 tests
  • ····A shipment leaves a warehouse
  • ····A shipment cannot leave a warehouse that does not exist
  • ····Two shipments cannot share a tracking number
  • ····Operations renames SP01 to SP10
  • ····Both shipments followed the warehouse to its new code
  • ····No shipment is left pointing at a code that vanished