CREATE TABLE album $($

id SERIAL,

title VARCHAR$(128)$ UNIQUE,

PRIMARY KEY $($id$)$

;

CREATE TABLE track $($

id SERIAL,

title VARCHAR$(128),$

len INTEGER, rating INTEGER, count INTEGER,

album$\_$id INTEGER REFERENCES album$($id$)$ ON DELETE CASCADE,

UNIQUE$($title$,$ album$\_$id$),$

PRIMARY KEY$($id$)$

;

DROP TABLE IF EXISTS track$\_$raw;

CREATE TABLE track$\_$raw

$($title TEXT, artist TEXT, album TEXT, album$\_$id INTEGER,

count INTEGER, rating INTEGER, len INTEGER$)$;

We will ignore the artist field for this assignment and focus on the many$-$to$-$one relationship between tracks and albums.

If you run the program multiple times in testing or with different files, make sure to empty out the data before each run.

Load this CSV data file into the track$\_$raw table using the lcopy command. Then write SQL commands to insert all of the distinct albums into the album table $($creating their primary keys$)$ and then set the album$\_$id in the track$\_$raw table using an SQL query like:

UPDATE track$\_$raw SET album$\_$id $=($SELECT

album.id FROM album. WHERE album.title $=$ track$\_$raw.album$)$;

Then use a INSERT $...$ SELECT statement to copy the corresponding data from the track$\_$raw table to the track table, effectively dropping the artist and album text fields.

grade this assignment, the auto$-$grader will run a query like this on your database and look for the data it expects to see: