deifneDatabase.sql

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)
);