schema.sql

CREATE TABLE ARTIST (
    nickname VARCHAR(16) UNIQUE NOT NULL,
    firstName VARCHAR(20) NOT NULL,
    lastName VARCHAR(20) NOT NULL, 
    e_mail VARCHAR(100) UNIQUE NOT NULL,
    locality VARCHAR(16) NOT NULL, 
    bestInstrument VARCHAR(16) NOT NULL,
    telephoneNumber VARCHAR(16) UNIQUE NOT NULL,
    isCertified BOOLEAN NOT NULL,
    isMaster BOOLEAN NOT NULL,
    pass VARCHAR(128) NOT NULL,
    PRIMARY KEY (nickname)
);

CREATE TABLE GENRE(
    genreName VARCHAR(16) UNIQUE NOT NULL,
    genreDescription VARCHAR(128) NOT NULL,
    origin VARCHAR(256) NOT NULL,
    PRIMARY KEY (genreName)
);


CREATE TABLE INFLUENCE(
    genreName VARCHAR(16) NOT NULL, 
    nickname VARCHAR(16) NOT NULL, 
    PRIMARY KEY (genreName, nickname)
);

CREATE TABLE FOLLOW(
    genreName VARCHAR(16) NOT NULL, 
    nickname VARCHAR(16) NOT NULL, 
    PRIMARY KEY (genreName, nickname)
);

CREATE TABLE DISCUSSION(
    discussionID SERIAL UNIQUE,
    timestampDiscussion TIMESTAMP NOT NULL,
    title VARCHAR(64) NOT NULL,
    average INT NOT NULL,
    nickname VARCHAR(16) NOT NULL,
    discussionText VARCHAR(400) NOT NULL,
    PRIMARY KEY (discussionID)
);

CREATE TABLE COMMENT(
    commentID SERIAL UNIQUE,
    commentText VARCHAR(64) NOT NULL,
    timestampComment TIMESTAMP NOT NULL,
    nickname VARCHAR(16) NOT NULL,
    discussionID INT NOT NULL, 
    PRIMARY KEY(commentID)
);


CREATE TABLE REACTION(
    nickname VARCHAR(16) NOT NULL,
    discussionID INT NOT NULL, 
    vote BOOLEAN, 
    dateAndTime TIMESTAMP NOT NULL,
    PRIMARY KEY (nickname, discussionID)
); 

CREATE TABLE HASHTAG(
    hashtagName VARCHAR(20) UNIQUE NOT NULL,
    PRIMARY KEY (hashtagName)
);

CREATE TABLE POST_REFERENCE(
    discussionID INT NOT NULL, 
    hashtagName VARCHAR(20) NOT NULL, 
    PRIMARY KEY(discussionID, hashtagName)
);

CREATE TABLE CHAT_PARTICIPANT(
    chatID INT NOT NULL,
    entryDate Date NOT NULL,
    exitDate Date,
    nickname VARCHAR(16) NOT NULL, 
    PRIMARY KEY(chatID, nickname)
);

CREATE TABLE CHAT(
    chatID SERIAL UNIQUE,
    chatName VARCHAR(32) NOT NULL,
    PRIMARY KEY(chatID)
);

CREATE TABLE MESSAGE(
    messageID SERIAL UNIQUE,
    chatID INT NOT NULL, 
    nickname VARCHAR(16) NOT NULL, 
    messageText VARCHAR(255) NOT NULL,
    timestampMessage TIMESTAMP NOT NULL,
    PRIMARY KEY(messageID)
);

CREATE TABLE BAND(
    bandName VARCHAR(24) UNIQUE NOT NULL,
    foundationDate DATE NOT NULL,
    PRIMARY KEY(bandName)
);

CREATE TABLE ALBUM(
    albumID SERIAL UNIQUE,
    albumName VARCHAR(10) NOT NULL,
    releaseDate DATE NOT NULL,
    bandName VARCHAR(24),
    nickName VARCHAR(16),
    PRIMARY KEY(albumID)
);

CREATE TABLE SONG(
    songID SERIAL UNIQUE,
    musicTrack INT NOT NULL,
    songName VARCHAR(10) NOT NULL,
    duration INT NOT NULL,
    albumID INT, 
    releaseDate DATE NOT NULL,
    bandName VARCHAR(24),
    nickName VARCHAR(16),
    PRIMARY KEY(songID)
);

CREATE TABLE APPARTAINANCE(
    genreName VARCHAR(16), 
    songID INT NOT NULL, 
    PRIMARY KEY(genreName)
);

CREATE TABLE OBSERVATION(
    hashtagName VARCHAR(20) NOT NULL,
    nickname VARCHAR(16) NOT NULL,
    PRIMARY KEY(hashtagName, nickname)
);

CREATE TABLE BAND_MEMBER(
    nickname VARCHAR(16) NOT NULL,
    bandName VARCHAR(24) NOT NULL,
    entryTimeStamp TIMESTAMP NOT NULL,
    exitTimeStamp TIMESTAMP,
    PRIMARY KEY(nickname, bandName)
);

ALTER TABLE BAND_MEMBER
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE BAND_MEMBER
ADD FOREIGN KEY (bandName) REFERENCES BAND(bandName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;


ALTER TABLE FOLLOW 
ADD FOREIGN KEY (genreName) REFERENCES GENRE(genreName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE FOLLOW 
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE INFLUENCE 
ADD FOREIGN KEY (genreName) REFERENCES GENRE(genreName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE INFLUENCE 
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE COMMENT
ADD FOREIGN KEY (discussionID) REFERENCES DISCUSSION(discussionID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE COMMENT
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE DISCUSSION 
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE REACTION
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE REACTION
ADD FOREIGN KEY (discussionID) REFERENCES DISCUSSION(discussionID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE POST_REFERENCE
ADD FOREIGN KEY (discussionID) REFERENCES DISCUSSION(discussionID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE POST_REFERENCE
ADD FOREIGN KEY (hashtagName) REFERENCES HASHTAG(hashtagName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE OBSERVATION
ADD FOREIGN KEY (hashtagName) REFERENCES HASHTAG(hashtagName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE OBSERVATION
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
 

ALTER TABLE CHAT_PARTICIPANT
ADD FOREIGN KEY (nickname) REFERENCES ARTIST(nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE CHAT_PARTICIPANT
ADD FOREIGN KEY (chatID) REFERENCES CHAT(chatID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE MESSAGE
ADD FOREIGN KEY (chatID, nickname) REFERENCES CHAT_PARTICIPANT(chatID, nickname)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE SONG
ADD FOREIGN KEY (albumID) REFERENCES ALBUM(albumID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE SONG
ADD FOREIGN KEY (bandName) REFERENCES BAND(bandName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE SONG
ADD FOREIGN KEY (nickName) REFERENCES ARTIST(nickName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE ALBUM 
ADD FOREIGN KEY (bandName) REFERENCES BAND(bandName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE ALBUM 
ADD FOREIGN KEY (nickName) REFERENCES ARTIST(nickName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE APPARTAINANCE
ADD FOREIGN KEY (songID) REFERENCES SONG(songID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;
ALTER TABLE APPARTAINANCE
ADD FOREIGN KEY (genreName) REFERENCES GENRE(genreName)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;