Many-to-One example 1

Raw Data

track_raw.csv

Schema

DROP TABLE IF EXISTS track_raw;
DROP TABLE IF EXISTS track;
DROP TABLE IF EXISTS album;

CREATE TABLE album (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(128) UNIQUE
);

CREATE TABLE track (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(128),
    len INTEGER, rating INTEGER, count INTEGER,
    album_id INTEGER REFERENCES album(id) ON DELETE CASCADE,
    UNIQUE(title, album_id)
);

CREATE TABLE track_raw
 (title TEXT, artist TEXT, album TEXT, album_id INTEGER,
  count INTEGER, rating INTEGER, len INTEGER);

Answer Checking SQL

SELECT track.title, album.title
    FROM track
    JOIN album ON track.album_id = album.id;

Steps

-- Copy the CSV to track_raw with no album_id
\\copy track_raw (title, artist, album, count, rating, len) FROM ~/track_raw.csv WITH DELIMITER ',' CSV;

-- Write distinct albums into album table to generate IDs
INSERT INTO album (title) SELECT DISTINCT album FROM track_raw WHERE track_raw.album IS NOT NULL;;

-- Update track_raw table to fill up album_id
UPDATE track_raw SET album_id = (SELECT album.id FROM album WHERE album.title = track_raw.album);

-- Copy track_raw into track table
INSERT INTO track (title, len, rating, count, album_id) SELECT title, len, rating, count, album_id FROM track_raw;

Many-to-One example 2

Raw Data

whc-sites-2018-small.csv

Schema

DROP TABLE IF EXISTS unesco_raw;
DROP TABLE IF EXISTS category;

CREATE TABLE unesco_raw
 (name TEXT, description TEXT, justification TEXT, year INTEGER,
    longitude FLOAT, latitude FLOAT, area_hectares FLOAT,
    category TEXT, category_id INTEGER, state TEXT, state_id INTEGER,
    region TEXT, region_id INTEGER, iso TEXT, iso_id INTEGER);

CREATE TABLE category (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(128) UNIQUE
);

--- ... More tables needed

Copy the CSV to unesco_raw with no category_id, state_id, region_id, and iso_id

\\copy unesco_raw(name,description,justification,year,longitude,latitude,area_hectares,category,state,region,iso) FROM '~/whc-sites-2018-small.csv' WITH DELIMITER ',' CSV HEADER;

Normalize the data in the unesco_raw table by adding the entries to each of the lookup tables (category, etc.) and then adding the foreign key columns to the unesco_raw table. Then make a new table called unesco that removes all of the un-normalized redundant text columns like category.

Answer Checking SQL

SELECT unesco.name, year, category.name, state.name, region.name, iso.name
  FROM unesco
  JOIN category ON unesco.category_id = category.id
  JOIN iso ON unesco.iso_id = iso.id
  JOIN state ON unesco.state_id = state.id
  JOIN region ON unesco.region_id = region.id
  ORDER BY region.name, unesco.name;

Steps