WikiStart/RelationalDesign: kreiranje.sql

-- Базирано на стабилна трансформација

-- поставување на патека односно шема во која ќе се извршуваат командите
set search_path = project;

DROP TABLE IF EXISTS student CASCADE;
DROP TABLE IF EXISTS task CASCADE;
DROP TABLE IF EXISTS feedback CASCADE;
DROP TABLE IF EXISTS school_year CASCADE;
DROP TABLE IF EXISTS term CASCADE;
DROP TABLE IF EXISTS subject_at_term CASCADE;
DROP TABLE IF EXISTS calendar_event CASCADE;
DROP TABLE IF EXISTS repeating_event CASCADE;
DROP TABLE IF EXISTS non_repeating_event CASCADE;
DROP TABLE IF EXISTS e_mail CASCADE;
DROP TABLE IF EXISTS reminder CASCADE;
DROP TABLE IF EXISTS place CASCADE;
DROP TABLE IF EXISTS dashboard CASCADE;
DROP TABLE IF EXISTS notes CASCADE;
DROP TABLE IF EXISTS professor CASCADE;
DROP TABLE IF EXISTS subject_book CASCADE;
DROP TABLE IF EXISTS author CASCADE;
DROP TABLE IF EXISTS chapter CASCADE;

DROP TABLE IF EXISTS has_taken_a_subject CASCADE;
DROP TABLE IF EXISTS holds_subject CASCADE;
DROP TABLE IF EXISTS holds_event CASCADE;
DROP TABLE IF EXISTS happens_at_place CASCADE;
DROP TABLE IF EXISTS recommended_book CASCADE;
DROP TABLE IF EXISTS recommended_chapter CASCADE;
DROP TABLE IF EXISTS has_consultations CASCADE;
DROP TABLE IF EXISTS year_has_subject CASCADE;
DROP TABLE IF EXISTS dashboard_event CASCADE;
DROP TABLE IF EXISTS subject_event CASCADE;
DROP TABLE IF EXISTS creates_task CASCADE;
DROP TABLE IF EXISTS student_event CASCADE;
DROP TABLE IF EXISTS student_task CASCADE;
DROP TABLE IF EXISTS gives_feedback CASCADE;
DROP TABLE IF EXISTS dashboard_task CASCADE;
DROP TABLE IF EXISTS book_author CASCADE;

-- отстранување на постојните домени

drop domain if exists string_long;
drop domain if exists string_short;
drop domain if exists string_real_short;

-- отстранување на шемата

drop schema if exists project;

-- креирање на шемата

create schema project;

-- поставување на патека односно шема во која ќе се извршуваат командите

set search_path = project;

-- креирање потребните домени - нови типови податоци

create domain string_long AS character varying(1000);
create domain string_short AS character varying(255);
create domain string_real_short AS character varying(20);

-- трансформација на множествата ентитети во табели

CREATE TABLE student (
    student_id SERIAL PRIMARY KEY,
    username string_short NOT NULL,
    s_first_name string_short NOT NULL,
    s_last_name string_short,
    s_password string_short NOT null
);


CREATE TABLE task (
    task_id SERIAL PRIMARY KEY,
    task_name string_short not null,
    task_description string_long,
    task_priority INT not null,
    is_done BOOLEAN not null default true
);

CREATE TABLE feedback (
    fb_id SERIAL PRIMARY KEY,
    fb_date DATE not null,
    fb_title string_short,
    fb_description string_long,
    fb_rating INT not null
);

CREATE TABLE school_year (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    PRIMARY KEY (student_id, starting_year, finishing_year),
    CONSTRAINT fk_school_year_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);



CREATE TABLE term (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_start_date DATE,
    term_end_date DATE,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type),
    CONSTRAINT fk_term_school_year FOREIGN KEY (student_id, starting_year, finishing_year) REFERENCES school_year(student_id, starting_year, finishing_year)
);

CREATE TABLE subject_at_term (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id SERIAL,
    subject_name string_short not null,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_subject_at_term_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type) REFERENCES term(student_id, starting_year, finishing_year, term_type)
);

CREATE TABLE calendar_event (
    calendar_event_id SERIAL,
    event_name string_short not null,
    event_type VARCHAR(50) not null,
    event_description string_long,
    event_start_time TIME not null,
    event_end_time TIME not null,
    PRIMARY KEY(calendar_event_id)
);

CREATE TABLE repeating_event (
    calendar_event_id INT,
    event_week_day string_real_short not null ,
    repeats_every_n_weeks INT not null,
    starting_date DATE not null,
    ending_date DATE,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_repeating_event_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id)
);

CREATE TABLE non_repeating_event (
    calendar_event_id INT,
    event_date DATE not null,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_non_repeating_event_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id)
);

CREATE TABLE e_mail (
    student_id INT,
    e_mail_id SERIAL,
    e_mail_address string_short not null,
    primary key (student_id, e_mail_id),
    CONSTRAINT fk_email_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

CREATE TABLE reminder (
    calendar_event_id INT,
    r_id SERIAL,
    minutes_before_event INT not null,
    r_flag BOOLEAN not null default true,
    PRIMARY KEY(calendar_event_id, r_id),
    CONSTRAINT fk_reminder_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id)
);

CREATE TABLE place (
    place_id SERIAL PRIMARY KEY,
    building_name string_short,
    room_name string_short not null
);


CREATE TABLE dashboard (
    student_id INT,
    dashboard_id SERIAL,
    dashboard_date DATE not null, 
    UNIQUE (dashboard_id),
    PRIMARY KEY(student_id, dashboard_id),
    CONSTRAINT fk_dashboard_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

CREATE TABLE notes (
    student_id INT,
    dashboard_id INT,
    note_id SERIAL,
    notes_date DATE not null,
    note_text string_long not null,
    PRIMARY KEY(student_id, dashboard_id, note_id),
    CONSTRAINT fk_notes_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id)
);

CREATE TABLE professor (
    professor_id SERIAL,
    p_first_name string_short not null,
    p_last_name string_short not null,
    p_role string_real_short,
    p_email string_short,
    PRIMARY KEY(professor_id)
);

CREATE TABLE subject_book (
    book_id SERIAL,
    book_title string_short not null,
    b_year_of_publishment INT,
    book_edition string_real_short not null,
    PRIMARY KEY(book_id)
);

CREATE TABLE author (
    author_id SERIAL PRIMARY KEY,
    author_name string_short not null,
    a_short_biography string_long
);



CREATE TABLE chapter (
    book_id INT,
    chapter_number INT,
    chapter_title string_short,
    PRIMARY KEY (book_id, chapter_number),
    CONSTRAINT fk_chapter_subject_book FOREIGN KEY (book_id) REFERENCES subject_book(book_id)
);

-- трансформација на множествата релации меѓу ентитети во табели

CREATE TABLE has_taken_a_subject (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    student_id_ref INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_taken_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_taken_student FOREIGN KEY (student_id_ref) REFERENCES student(student_id)
);

CREATE TABLE holds_subject (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    professor_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id, professor_id),
    CONSTRAINT fk_holds_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_holds_professor FOREIGN KEY (professor_id) REFERENCES professor(professor_id)
);

CREATE TABLE holds_event (
    calendar_event_id INT,
    professor_id INT,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_holds_event_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_holds_event_professor FOREIGN KEY (professor_id) REFERENCES professor(professor_id)
);

CREATE TABLE happens_at_place (
    calendar_event_id INT,
    place_id INT,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_happens_at_place_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_happens_at_place_place FOREIGN KEY (place_id) REFERENCES place(place_id)
);



CREATE TABLE dashboard_task (
    task_id INT,
    student_id INT,
    dashboard_id INT,
    PRIMARY KEY (task_id),
    CONSTRAINT fk_dashboard_task_task FOREIGN KEY (task_id) REFERENCES task(task_id),
    CONSTRAINT fk_dashboard_task_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id)
);

CREATE TABLE gives_feedback (
    fb_id INT,
    student_id INT,
    PRIMARY KEY (fb_id),
    CONSTRAINT fk_gives_feedback_feedback FOREIGN KEY (fb_id) REFERENCES feedback(fb_id),
    CONSTRAINT fk_gives_feedback_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

CREATE TABLE recommended_book(
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    book_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id, book_id),
    CONSTRAINT fk_recommended_book_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_recommended_book_subject_book FOREIGN KEY (book_id) REFERENCES subject_book(book_id)
    
);

CREATE TABLE recommended_chapter (
    calendar_event_id INT,
    book_id INT,
    chapter_number INT,
    PRIMARY KEY (calendar_event_id, book_id, chapter_number),
    CONSTRAINT fk_recommended_chapter_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_recommended_chapter_chapter FOREIGN KEY (book_id, chapter_number) REFERENCES chapter(book_id, chapter_number)
);

CREATE TABLE has_consultations (
    calendar_event_id INT,
    professor_id INT,
        book_id INT,
    chapter_number INT,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_has_consultations_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_has_consultations_professor FOREIGN KEY (professor_id) REFERENCES professor(professor_id),
    CONSTRAINT fk_has_chapter FOREIGN KEY (book_id, chapter_number) REFERENCES chapter(book_id, chapter_number)
);

CREATE TABLE year_has_subject (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    student_id_ref INT,
    starting_year_ref INT,
    finishing_year_ref INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_year_has_subject_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_year_has_subject_school_year FOREIGN KEY (student_id_ref, starting_year_ref, finishing_year_ref) REFERENCES school_year(student_id, starting_year, finishing_year)
);



CREATE TABLE dashboard_event (
    student_id INT,
    dashboard_id INT,
    calendar_event_id INT,
    PRIMARY KEY (student_id, dashboard_id, calendar_event_id),
    CONSTRAINT fk_dashboard_event_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id),
    CONSTRAINT fk_dashboard_event_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id)
);

CREATE TABLE subject_event (
    calendar_event_id INT,
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_subject_event_calendar_event FOREIGN KEY (calendar_event_id) REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_subject_event_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id)
);

CREATE TABLE creates_task (
    task_id INT,
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type string_real_short,
    term_subject_id INT,
    PRIMARY KEY (task_id),
    CONSTRAINT fk_creates_task_task FOREIGN KEY (task_id) REFERENCES task(task_id),
    CONSTRAINT fk_creates_task_subject_at_term FOREIGN KEY (student_id, starting_year, finishing_year, term_type, term_subject_id) REFERENCES subject_at_term(student_id, starting_year, finishing_year, term_type, term_subject_id)
);
CREATE TABLE student_event (
    calendar_event_id INT,
    student_id INT,
    PRIMARY KEY (calendar_event_id),
    CONSTRAINT fk_student_event_calendar_event
        FOREIGN KEY (calendar_event_id)
        REFERENCES calendar_event(calendar_event_id),
    CONSTRAINT fk_student_event_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

CREATE TABLE student_task (
    task_id INT,
    student_id INT,
    PRIMARY KEY (task_id),
    CONSTRAINT fk_student_task_task
        FOREIGN KEY (task_id)
        REFERENCES task(task_id),
    CONSTRAINT fk_student_task_student  FOREIGN KEY (student_id)  REFERENCES student(student_id)
);

CREATE TABLE book_author (
    author_id INT,
    book_id INT,
    PRIMARY KEY (author_id, book_id),
    CONSTRAINT fk_book_author_author FOREIGN key (author_id) REFERENCES author(author_id),
    CONSTRAINT fk_book_author_book  FOREIGN KEY (book_id) REFERENCES subject_book(book_id)
);