WikiStart/RelationalDesign: kreiranje_old1.sql

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 term_has_subject CASCADE;
DROP TABLE IF EXISTS calendar_task 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 student_dashboard CASCADE;
DROP TABLE IF EXISTS studied_in_school_year CASCADE;
DROP TABLE IF EXISTS dashboard_notes CASCADE;
DROP TABLE IF EXISTS book_modules CASCADE;
DROP TABLE IF EXISTS contains_terms CASCADE;
DROP TABLE IF EXISTS has_e_mail 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 book_author CASCADE;
DROP TABLE IF EXISTS gives_feedback CASCADE;
DROP TABLE IF EXISTS event_reminder CASCADE;
DROP TABLE IF EXISTS dashboard_task CASCADE;

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    username VARCHAR(255),
    s_first_name VARCHAR(255),
    s_last_name VARCHAR(255),
    s_password VARCHAR(255)
);

CREATE TABLE task (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(255),
    task_description TEXT,
    task_priority INT,
    is_done BOOLEAN
);

CREATE TABLE feedback (
    fb_id INT PRIMARY KEY,
    fb_date DATE,
    fb_title VARCHAR(255),
    fb_description TEXT,
    fb_rating INT
);

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 VARCHAR(50),
    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 VARCHAR(50),
    term_subject_id INT,
    subject_name VARCHAR(255),
    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 INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_type VARCHAR(50),
    event_description TEXT,
    event_start_time TIME,
    event_end_time TIME
);

CREATE TABLE repeating_event (
    calendar_event_id INT,
    event_week_day VARCHAR(20),
    repeats_every_n_weeks INT,
    starting_date DATE,
    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,
    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 INT PRIMARY KEY,
    e_mail_address VARCHAR(255),
    CONSTRAINT fk_email_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

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

CREATE TABLE place (
    place_id INT PRIMARY KEY,
    building_name VARCHAR(255),
    room_name VARCHAR(255)
);


CREATE TABLE dashboard (
    student_id INT,
    dashboard_id INT,
    dashboard_date DATE,
    PRIMARY KEY (dashboard_id), 
    UNIQUE (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 INT PRIMARY KEY,
    notes_date DATE,
    note_text TEXT,
    CONSTRAINT fk_notes_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id)
);

CREATE TABLE professor (
    professor_id INT PRIMARY KEY,
    p_first_name VARCHAR(255),
    p_last_name VARCHAR(255),
    p_role VARCHAR(50),
    p_email VARCHAR(255)
);

CREATE TABLE subject_book (
    book_id INT PRIMARY KEY,
    book_title VARCHAR(255),
    b_year_of_publishment INT,
    book_edition VARCHAR(50)
);

CREATE TABLE author (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255),
    a_short_biography TEXT
);

CREATE TABLE chapter (
    book_id INT,
    chapter_number INT,
    chapter_title VARCHAR(255),
    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 VARCHAR(50),
    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 VARCHAR(50),
    term_subject_id INT,
    professor_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_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 term_has_subject (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type VARCHAR(50),
    term_subject_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_term_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)
);

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 VARCHAR(50),
    term_subject_id INT,
    book_id INT,
    fb_id INT, -- Added fb_id here
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_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),
    CONSTRAINT fk_recommended_book_feedback FOREIGN KEY (fb_id) REFERENCES feedback(fb_id) -- Corrected the reference here
);

CREATE TABLE recommended_chapter (
    calendar_event_id INT,
    book_id INT,
    chapter_number INT,
    PRIMARY KEY (calendar_event_id),
    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,
    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)
);

CREATE TABLE year_has_subject (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type VARCHAR(50),
    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 student_dashboard (
    student_id INT,
    dashboard_id INT,
    PRIMARY KEY (student_id, dashboard_id),
    CONSTRAINT fk_student_dashboard_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id)
);

CREATE TABLE studied_in_school_year (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type VARCHAR(50),
    term_subject_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_studied_in_school_year_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 dashboard_notes (
    student_id INT,
    dashboard_id INT,
    PRIMARY KEY (student_id, dashboard_id),
    CONSTRAINT fk_dashboard_notes_dashboard FOREIGN KEY (student_id, dashboard_id) REFERENCES dashboard(student_id, dashboard_id)
);

CREATE TABLE book_modules (
    book_id INT,
    chapter_number INT,
    PRIMARY KEY (book_id, chapter_number),
    CONSTRAINT fk_book_modules_subject_book FOREIGN KEY (book_id) REFERENCES subject_book(book_id)
);

CREATE TABLE contains_terms (
    student_id INT,
    starting_year INT,
    finishing_year INT,
    term_type VARCHAR(50),
    term_subject_id INT,
    PRIMARY KEY (student_id, starting_year, finishing_year, term_type, term_subject_id),
    CONSTRAINT fk_contains_terms_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 has_e_mail (
    student_id INT,
    e_mail_id INT,
    PRIMARY KEY (student_id, e_mail_id),
    CONSTRAINT fk_has_e_mail_student FOREIGN KEY (student_id) REFERENCES student(student_id)
);

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 VARCHAR(50),
    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 VARCHAR(50),
    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, student_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, student_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)
);

CREATE TABLE event_reminder (
    calendar_event_id INT,
    r_id INT,
    PRIMARY KEY (calendar_event_id, r_id),
    CONSTRAINT fk_event_reminder_calendar_event
        FOREIGN KEY (calendar_event_id)
        REFERENCES calendar_event(calendar_event_id)
);