Practice database creation

Fast track: In this field, you will create the database for Los Angeles Police Department, based on the physical model. You will also read about different options of a column like it can be NULL or not, PRIMARY KEY, FOREIGN KEY.
If you are on the fast track, read and execute the script in the Solution section to build up the database for you.

Exercise 1: Build up tables and relations in your empty playground database based on the Physical model you created during Practice database design.

Hints:

  • Define types.
  • DuckDB doesn't have an auto-incrementing column type. Instead, give the primary key type UUID with a default of uuid(), so a fresh random id is generated whenever a row is inserted without specifying one — the same approach used for person_id in the crime database you've been querying.
  • Use foreign keys to enforce relations between columns.
  • Define NULL/NOT NULL for the columns.

  • Many different solutions exist.

  • Use shell.duckdb.org's table list to check you achieved what you wanted. Table creation or any modification on the structure cannot be undone, but you can drop a table and recreate it anytime, if you always save your work into an sql file.

Solution 
CREATE TABLE person(
id UUID PRIMARY KEY DEFAULT uuid(),
first_name TEXT NULL,
last_name TEXT NULL,
date_of_birth DATE NULL);

CREATE TABLE sample(
id UUID PRIMARY KEY DEFAULT uuid(),
place_collected TEXT NULL,
time_collected DATETIME NULL,
person_id UUID NULL,
CONSTRAINT fk_sample_person FOREIGN KEY (person_id) REFERENCES person(id));

CREATE TABLE locus(
id UUID PRIMARY KEY DEFAULT uuid(),
name TEXT NOT NULL,
CONSTRAINT locus_name_unique UNIQUE (name));

CREATE TABLE peak(
id UUID PRIMARY KEY DEFAULT uuid(),
locus_id UUID NOT NULL,
sample_id UUID NOT NULL,
value INT NOT NULL,
CONSTRAINT fk_peak_locus FOREIGN KEY (locus_id) REFERENCES locus(id),
CONSTRAINT fk_peak_sample FOREIGN KEY (sample_id) REFERENCES sample(id));

Exercise 2: Draw a diagram of your tables.

Hints:

  • Use DuckDB's web shell — the same tool you used to connect to the crime database — to inspect the tables you just created.
  • If your client doesn't generate an entity-relationship diagram automatically, export the schema and use a separate diagramming tool.