-- ============================================================
-- Netflix Titles — Normalized Schema
-- ============================================================
-- Splits the flat CSV (director, cast, country, listed_in were
-- comma-separated text fields) into linked tables with proper
-- many-to-many relationships via junction tables.
-- ============================================================
DROP TABLE IF EXISTS title_genres;
DROP TABLE IF EXISTS title_countries;
DROP TABLE IF EXISTS title_cast;
DROP TABLE IF EXISTS title_directors;
DROP TABLE IF EXISTS genres;
DROP TABLE IF EXISTS countries;
DROP TABLE IF EXISTS cast_members;
DROP TABLE IF EXISTS directors;
DROP TABLE IF EXISTS titles;
-- ------------------------------------------------------------
-- Core entity
-- ------------------------------------------------------------
CREATE TABLE titles (
show_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
date_added TEXT, -- original format: DD-Mon-YY
release_year INTEGER,
rating TEXT,
duration TEXT, -- "xx min" or "x Season(s)"
description TEXT,
type TEXT -- 'Movie' or 'TV Show'
);
-- ------------------------------------------------------------
-- Lookup entities (deduplicated from comma-separated text)
-- ------------------------------------------------------------
CREATE TABLE directors (
director_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE cast_members (
person_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE countries (
country_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE genres (
genre_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
-- ------------------------------------------------------------
-- Junction tables (many-to-many)
-- ------------------------------------------------------------
CREATE TABLE title_directors (
show_id INTEGER NOT NULL,
director_id INTEGER NOT NULL,
PRIMARY KEY (show_id, director_id),
FOREIGN KEY (show_id) REFERENCES titles(show_id),
FOREIGN KEY (director_id) REFERENCES directors(director_id)
);
CREATE TABLE title_cast (
show_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
PRIMARY KEY (show_id, person_id),
FOREIGN KEY (show_id) REFERENCES titles(show_id),
FOREIGN KEY (person_id) REFERENCES cast_members(person_id)
);
CREATE TABLE title_countries (
show_id INTEGER NOT NULL,
country_id INTEGER NOT NULL,
PRIMARY KEY (show_id, country_id),
FOREIGN KEY (show_id) REFERENCES titles(show_id),
FOREIGN KEY (country_id) REFERENCES countries(country_id)
);
CREATE TABLE title_genres (
show_id INTEGER NOT NULL,
genre_id INTEGER NOT NULL,
PRIMARY KEY (show_id, genre_id),
FOREIGN KEY (show_id) REFERENCES titles(show_id),
FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);
-- ============================================================
-- Populating from the flat CSV
-- ============================================================
-- The original CSV has one row per title, with director, cast,
-- country, and listed_in as comma-separated text. To populate
-- the normalized tables you need to:
-- 1. Load the flat CSV into a staging table (see
-- netflix_titles_load.sql for that step).
-- 2. Insert distinct values into directors/cast_members/
-- countries/genres by splitting the comma-separated
-- columns (string-splitting syntax is engine-specific —
-- e.g. STRING_TO_ARRAY + UNNEST in PostgreSQL, or a
-- recursive CTE in SQL Server/SQLite).
-- 3. Insert into the junction tables by joining the split
-- values back to their show_id and the new lookup ids.
--
-- Example for PostgreSQL:
--
-- INSERT INTO genres (name)
-- SELECT DISTINCT TRIM(g)
-- FROM netflix_titles_staging, LATERAL UNNEST(STRING_TO_ARRAY(listed_in, ',')) AS g
-- ON CONFLICT (name) DO NOTHING;
--
-- INSERT INTO title_genres (show_id, genre_id)
-- SELECT s.show_id, ge.genre_id
-- FROM netflix_titles_staging s,
-- LATERAL UNNEST(STRING_TO_ARRAY(s.listed_in, ',')) AS g
-- JOIN genres ge ON ge.name = TRIM(g)
-- ON CONFLICT DO NOTHING;
--
-- Repeat the same pattern for director, cast, and country.
Rachel Smith 