Create_tables.sql

CREATE TABLE CONTESTANT(
    CODE INT NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 1,INCREMENT BY 1),
    NAME VARCHAR(255),
    BIRTHDATE DATE NOT NULL,
    NOTABILITY  VARCHAR(255),
    HOMETOWN VARCHAR(63),
    PRIMARY KEY(CODE)
);

CREATE TABLE JUDGE(
    CODE INT NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 11,INCREMENT BY 1),
    NAME VARCHAR(255),
    BIRTHDATE DATE NOT NULL,
    NOTABILITY  VARCHAR(255),
    SEASONS INT,
    PRIMARY KEY(CODE)
);

CREATE TABLE PRESENTER(
    CODE INT NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 15,INCREMENT BY 1),
    NAME VARCHAR(255),
    BIRTHDATE DATE NOT NULL,
    NOTABILITY  VARCHAR(255),
    AWARDS VARCHAR(255),
    PRIMARY KEY(CODE)
);

CREATE TABLE EPISODE(
    NUMBER INT NOT NULL,
    THEME VARCHAR(255),
    PRIMARY KEY(NUMBER)
);

CREATE TABLE POINTS(
    ID CHAR(4) NOT NULL,
    CONTESTANTCODE INT
    REFERENCES CONTESTANT(CODE),
    JUDGECODE INT
    REFERENCES JUDGE(CODE),
    EPISODENO INT
    REFERENCES EPISODE(NUMBER),
    SCORE INT,
    PRIMARY KEY(ID)
);

CREATE TABLE PARTICIPATE(
    CONTESTANTCODE INT NOT NULL REFERENCES CONTESTANT(CODE),
    EPISODENO INT NOT NULL REFERENCES EPISODE(NUMBER),
    PRIMARY KEY(CONTESTANTCODE,EPISODENO)
);

CREATE TABLE SONG(
    EPISODENO INT NOT NULL REFERENCES EPISODE(NUMBER),
    CONTESTANTCODE INT NOT NULL REFERENCES CONTESTANT(CODE),
    NAME VARCHAR(255),
    RELEASED INT,
    GENRE VARCHAR(63),
    SINGER VARCHAR(63),
    PRIMARY KEY(EPISODENO,CONTESTANTCODE)
);

CREATE TABLE STYLIST
(
    CERTNO INTEGER NOT NULL PRIMARY KEY,
    NAME   VARCHAR(100),
    TYPE   VARCHAR(100),
    CHECK (TYPE IN ('MAKEUP ARTIST', 'HAIRSTYLIST', 'FASHION STYLIST')),
    CHECK (CERTNO > 0)
);

CREATE TABLE COMPANY
(
    CODE         CHARACTER(5) NOT NULL PRIMARY KEY,
    NAME         VARCHAR(100),
    FOUNDED      DATE,
    HEADQUARTERS VARCHAR(100)
);

CREATE TABLE SINGER
(
    NAME         VARCHAR(100) NOT NULL PRIMARY KEY,
    ACTIVE_YEARS INTEGER,
    BIRTHPLACE   VARCHAR(100),
    CHECK (ACTIVE_YEARS > 0)
);

CREATE TABLE TAKE_PART(
    EPISODENO INT NOT NULL REFERENCES EPISODE(NUMBER),
    CERTNO INT NOT NULL REFERENCES STYLIST(CERTNO),
    PRIMARY KEY(EPISODENO, CERTNO)
);

CREATE TABLE SPONSORS(
    EPISODENO INT NOT NULL REFERENCES EPISODE(NUMBER),
    COMPANYCODE CHARACTER(5) NOT NULL REFERENCES COMPANY(CODE),
    FUNDS INT,
    PRIMARY KEY (EPISODENO, COMPANYCODE)
);