imZ6DKME

1DROP TABLE IF EXISTS tracks;
2DROP TABLE IF EXISTS labels;
3DROP TABLE IF EXISTS genres;
4DROP TABLE IF EXISTS albums;
5DROP TABLE IF EXISTS artists;
6
7/* Part 1 - Create the empty tables 
8    After cleaning out any previous tables, especially for testing,
9    we create empty tables. Given that "serial" populates unique IDS
10    for every serial generated, we probably don't need to distinguish
11    as ARTISTID, ALBUMID, etc. but I did anyway.
12*/
13
14CREATE TABLE artists (
15	ARTISTID SERIAL PRIMARY KEY,
16	name text,
17    type text,
18	person bool,
19	beginyear numeric(4,0),
20	endyear numeric(4,0) CHECK(endyear - beginyear >= 0)
21	);
22
23CREATE TABLE albums ( 
24	ALBUMID serial PRIMARY KEY,
25	title text NOT NULL,
26    genre text NOT NULL,
27	album_year numeric(4,0) CHECK(album_year > 0),
28    -- Now we can delete an artist -> will delete the album
29	ARTISTID int NOT NULL REFERENCES artists(ARTISTID) ON DELETE CASCADE
30	);
31
32CREATE TABLE genres (
33    genre text NOT NULL PRIMARY KEY
34);
35
36CREATE TABLE labels ( 
37	LABELID serial PRIMARY KEY,
38	name text NOT NULL UNIQUE,
39	location text
40	);
41
42CREATE TABLE tracks ( 
43	track_name text NOT NULL,
44	number text UNIQUE,
45	ALBUMID int NOT NULL REFERENCES albums(ALBUMID) ON DELETE CASCADE,
46	PRIMARY KEY (ALBUMID, track_name)
47	);
48
49/* Part 2 - Populate the tables 
50    Filling the tables up using data from the project description.
51*/
52
53INSERT INTO artists(name, type)
54SELECT distinct(artist_name), artist_type
55FROM project7
56WHERE artist_name IS NOT NULL;
57
58/* 
59    This will pull all members of type person and add them to artists,
60    
61*/
62
63INSERT INTO artists(name, type)
64SELECT distinct(member_name), 'Person'
65FROM project7 p7
66WHERE member_name IS NOT NULL AND NOT EXISTS(SELECT artist_name FROM artists WHERE p7.member_name = artists.name);