Skip to content

Instantly share code, notes, and snippets.

@monapasan
Last active August 29, 2015 14:24
Show Gist options
  • Save monapasan/0afa9d0039e26afac8e3 to your computer and use it in GitHub Desktop.
Save monapasan/0afa9d0039e26afac8e3 to your computer and use it in GitHub Desktop.
DROP TABLE held_in;
DROP TABLE belongs_to;
DROP TABLE tour;
DROP TABLE cities;
DROP TABLE genre;
DROP TABLE country;
DROP TABLE song;
DROP TABLE album;
DROP TABLE band;
CREATE TABLE band (
band_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30),
CONSTRAINT band_name_key1 UNIQUE (name)
);
CREATE TABLE album (
album_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30),
release_date DATE,
band_ID INTEGER,
FOREIGN KEY (band_ID) REFERENCES band ON DELETE CASCADE
);
CREATE TABLE song (
song_ID SERIAL NOT NULL PRIMARY KEY,
title VARCHAR(30),
duration INTEGER,
band_ID INTEGER,
album_ID INTEGER,
FOREIGN KEY (album_ID) REFERENCES album ON DELETE CASCADE,
FOREIGN KEY (band_ID) REFERENCES band ON DELETE CASCADE
);
CREATE TABLE country (
country_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30),
CONSTRAINT name_name_key1 UNIQUE (name)
);
CREATE TABLE genre (
genre_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL
);
CREATE TABLE cities (
city_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30),
country_ID INTEGER,
FOREIGN KEY (country_ID) REFERENCES country
);
CREATE TABLE tour (
tour_ID SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
begin_date DATE,
end_date DATE,
band_ID INTEGER,
FOREIGN KEY (band_ID) REFERENCES band ON DELETE CASCADE
);
CREATE TABLE belongs_to (
genre_ID INTEGER REFERENCES genre ON DELETE CASCADE,
song_ID INTEGER REFERENCES song ON DELETE CASCADE,
PRIMARY KEY (genre_ID, song_ID)
);
CREATE TABLE held_in (
tour_ID INTEGER REFERENCES tour(tour_ID) ON DELETE CASCADE,
city_ID INTEGER REFERENCES cities(city_ID) ON DELETE CASCADE,
PRIMARY KEY (tour_ID, city_ID)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment