Simplified IMDB-like database with sample data

imdb_sample.sql

create table people (
  nconst              text primary key,
  primary_name        text not null,
  birth_year          date
);

create table titles (
  tconst           text primary key,
  primary_title    text not null,
  runtime_minutes  smallint
);

create table title_cast (
  tconst        text not null references titles(tconst) on delete cascade,
  nconst        text not null references people(nconst) on delete restrict,
  characters    text[],
  primary key (tconst, nconst)
);

create table genres (
  genre_id serial primary key,
  name     text unique not null
);

create table title_genres (
  tconst   text not null references titles(tconst) on delete cascade,
  genre_id int  not null references genres(genre_id) on delete restrict,
  primary key (tconst, genre_id)
);

INSERT INTO people (nconst, primary_name, birth_year) VALUES
  ('nm0000338','Tim Robbins','1958-01-01'),
  ('nm0000209','Morgan Freeman','1937-01-01'),
  ('nm0000008','Marlon Brando','1924-01-01'),
  ('nm0000199','Al Pacino','1940-01-01'),
  ('nm0000288','Christian Bale','1974-01-01'),
  ('nm0001618','Heath Ledger','1979-01-01');

INSERT INTO titles (tconst, primary_title, runtime_minutes) VALUES
  ('tt0111161','The Shawshank Redemption',142),
  ('tt0068646','The Godfather',175),
  ('tt0468569','The Dark Knight',152);

INSERT INTO genres (name) VALUES
  ('Drama'),
  ('Crime'),
  ('Action');

INSERT INTO title_genres (tconst, genre_id)
SELECT 'tt0111161', g.genre_id FROM genres g WHERE g.name = 'Drama';

INSERT INTO title_genres (tconst, genre_id)
SELECT 'tt0068646', g.genre_id FROM genres g WHERE g.name IN ('Crime','Drama');

INSERT INTO title_genres (tconst, genre_id)
SELECT 'tt0468569', g.genre_id FROM genres g WHERE g.name IN ('Action','Crime','Drama');

INSERT INTO title_cast (tconst, nconst, characters) VALUES
  ('tt0111161','nm0000338', ARRAY['Andy Dufresne']),
  ('tt0111161','nm0000209', ARRAY['Ellis Boyd "Red" Redding']),
  ('tt0068646','nm0000008', ARRAY['Don Vito Corleone']),
  ('tt0068646','nm0000199', ARRAY['Michael Corleone']),
  ('tt0468569','nm0000288', ARRAY['Bruce Wayne','Batman']),
  ('tt0468569','nm0001618', ARRAY['Joker']);