create.sql

-- 1
CREATE TABLE MEMBER (
  MEMBER_ID       NUMBER(10),
  MEMBER_EMAIL    VARCHAR(50),
  MEMBER_PASSWORD VARCHAR(256),
  MEMBER_NAME     VARCHAR(50),
  MEMBER_AGE      NUMBER(10),
  MEMBER_TYPE     NUMBER(10),
  PRIMARY KEY (MEMBER_ID),
  UNIQUE (MEMBER_EMAIL)
);
/

CREATE SEQUENCE MEMBER_SEQUENCE START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER MEMBER_TRIGGER
BEFORE INSERT ON MEMBER
FOR EACH ROW
WHEN (new.MEMBER_ID IS NULL)
  BEGIN
    SELECT MEMBER_SEQUENCE.NEXTVAL
    INTO :new.MEMBER_ID
    FROM dual;
  END;
/

CREATE OR REPLACE PROCEDURE ADD_MEMBER(m_email IN VARCHAR, m_password IN VARCHAR, m_name IN VARCHAR, m_age IN INT,
                                       m_type  IN INT)
IS
  BEGIN
    INSERT INTO MEMBER (MEMBER_EMAIL, MEMBER_PASSWORD, MEMBER_NAME, MEMBER_AGE, MEMBER_TYPE)
    VALUES (m_email, m_password, m_name, m_age, m_type);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_MEMBER(m_id  IN INT, m_email IN VARCHAR, m_password IN VARCHAR, m_name IN VARCHAR,
                                          m_age IN INT, m_type IN INT)
IS
  BEGIN
    UPDATE MEMBER
    SET MEMBER_EMAIL  = m_email,
      MEMBER_PASSWORD = m_password,
      MEMBER_NAME     = m_name,
      MEMBER_AGE      = m_age
    WHERE MEMBER_ID = m_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_MEMBER(m_id IN INT)
IS
  BEGIN
    DELETE FROM MEMBER
    WHERE MEMBER_ID = m_id;
  END;
/




-- 2
CREATE TABLE MOVIE (
  MOVIE_ID           NUMBER(10) NOT NULL,
  MOVIE_TITLE        VARCHAR(100),
  MOVIE_RELEASE_DATE DATE,
  MOVIE_LENGTH       NUMBER(10),
  PRIMARY KEY (MOVIE_ID)
);
/

CREATE SEQUENCE MOVIE_SEQUENCE START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER MOVIE_TRIGGER
BEFORE INSERT ON MOVIE
FOR EACH ROW
WHEN (new.MOVIE_ID IS NULL)
  BEGIN
    SELECT MOVIE_SEQUENCE.NEXTVAL
    INTO :new.MOVIE_ID
    FROM dual;
  END;
/

CREATE PROCEDURE ADD_MOVIE(m_title IN VARCHAR, m_release_date IN DATE, m_length IN INT)
IS
  BEGIN
    INSERT INTO MOVIE (MOVIE_TITLE, MOVIE_RELEASE_DATE, MOVIE_LENGTH)
    VALUES (m_title, m_release_date, m_length);
    COMMIT;
  END;
/

CREATE PROCEDURE UPDATE_MOVIE(m_id IN INT, m_title IN VARCHAR, m_release_date IN DATE, m_length IN INT)
IS
  BEGIN
    UPDATE MOVIE
    SET MOVIE_TITLE      = m_title,
      MOVIE_RELEASE_DATE = m_release_date,
      MOVIE_LENGTH       = m_length
    WHERE MOVIE_ID = m_id;
    COMMIT;
  END;
/

CREATE PROCEDURE DELETE_MOVIE(m_id IN INT)
IS
  BEGIN
    DELETE FROM MOVIE
    WHERE MOVIE_ID = m_id;
  END;
/




-- 3
CREATE TABLE POSTER (
  POSTER_ID   NUMBER(10) NOT NULL,
  POSTER_PATH VARCHAR(255),
  PRIMARY KEY (POSTER_ID),
  FOREIGN KEY (POSTER_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_POSTER(p_id IN INT, p_path IN VARCHAR)
IS
  BEGIN
    INSERT INTO POSTER (POSTER_ID, POSTER_PATH)
    VALUES (p_id, p_path);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_POSTER(p_id IN INT, p_path IN VARCHAR)
IS
  BEGIN
    UPDATE POSTER
    SET POSTER_PATH = p_path
    WHERE POSTER_ID = p_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_POSTER(p_id IN INT)
IS
  BEGIN
    DELETE FROM POSTER
    WHERE POSTER_ID = p_id;
  END;
/




-- 4
CREATE TABLE STORYLINE (
  STORYLINE_ID      NUMBER(10) NOT NULL,
  STORYLINE_CONTEXT VARCHAR(255),
  PRIMARY KEY (STORYLINE_ID),
  FOREIGN KEY (STORYLINE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_STORYLINE(s_id IN INT, s_context IN VARCHAR)
IS
  BEGIN
    INSERT INTO STORYLINE (STORYLINE_ID, STORYLINE_CONTEXT)
    VALUES (s_id, s_context);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_STORYLINE(s_id IN INT, s_context IN VARCHAR)
IS
  BEGIN
    UPDATE STORYLINE
    SET STORYLINE_CONTEXT = s_context
    WHERE STORYLINE_ID = s_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_STORYLINE(s_id IN INT)
IS
  BEGIN
    DELETE FROM STORYLINE
    WHERE STORYLINE_ID = s_id;
  END;
/




-- 5
CREATE TABLE AGELIMIT (
  AGELIMIT_ID NUMBER(10) NOT NULL,
  AGELIMIT    NUMBER(10),
  PRIMARY KEY (AGELIMIT_ID),
  FOREIGN KEY (AGELIMIT_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_AGELIMIT(a_id IN INT, a_limit IN INT)
IS
  BEGIN
    INSERT INTO AGELIMIT (AGELIMIT_ID, AGELIMIT)
    VALUES (a_id, a_limit);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_AGELIMIT(a_id IN INT, a_limit IN INT)
IS
  BEGIN
    UPDATE AGELIMIT
    SET AGELIMIT = a_limit
    WHERE AGELIMIT_ID = a_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_AGELIMIT(a_id IN INT)
IS
  BEGIN
    DELETE FROM AGELIMIT
    WHERE AGELIMIT_ID = a_id;
  END;
/




-- 6
CREATE TABLE GENRE (
  GENRE_ID      NUMBER(10) NOT NULL,
  GENRE_CONTEXT VARCHAR(255),
  PRIMARY KEY (GENRE_ID),
  FOREIGN KEY (GENRE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_GENRE(g_id IN INT, g_context IN VARCHAR)
IS
  BEGIN
    INSERT INTO GENRE (GENRE_ID, GENRE_CONTEXT)
    VALUES (g_id, g_context);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_GENRE(g_id IN INT, g_context IN VARCHAR)
IS
  BEGIN
    UPDATE GENRE
    SET GENRE_CONTEXT = g_context
    WHERE GENRE_ID = g_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_GENRE(g_id IN INT)
IS
  BEGIN
    DELETE FROM GENRE
    WHERE GENRE_ID = g_id;
  END;
/




-- 7
CREATE TABLE LANGUAGE (
  LANGUAGE_ID NUMBER(10) NOT NULL,
  LANGUAGE    VARCHAR(255),
  PRIMARY KEY (LANGUAGE_ID),
  FOREIGN KEY (LANGUAGE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_LANGUAGE(l_id IN INT, lang IN VARCHAR)
IS
  BEGIN
    INSERT INTO LANGUAGE (LANGUAGE_ID, LANGUAGE)
    VALUES (l_id, lang);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_LANGUAGE(l_id IN INT, lang IN VARCHAR)
IS
  BEGIN
    UPDATE LANGUAGE
    SET LANGUAGE = lang
    WHERE LANGUAGE_ID = l_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_LANGUAGE(l_id IN INT)
IS
  BEGIN
    DELETE FROM LANGUAGE
    WHERE LANGUAGE_ID = l_id;
  END;
/




-- 8
CREATE TABLE ACTOR (
  ACTOR_ID       NUMBER(10) NOT NULL,
  ACTOR_NAME     VARCHAR(50),
  ACTOR_SURNAME  VARCHAR(50),
  ACTOR_AGE      NUMBER(10),
  ACTOR_HOMETOWN VARCHAR(50),
  PRIMARY KEY (ACTOR_ID)
);
/

CREATE SEQUENCE ACTOR_SEQUENCE START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER ACTOR_TRIGGER
BEFORE INSERT ON ACTOR
FOR EACH ROW
WHEN (new.ACTOR_ID IS NULL)
  BEGIN
    SELECT ACTOR_SEQUENCE.NEXTVAL
    INTO :new.ACTOR_ID
    FROM dual;
  END;
/

CREATE OR REPLACE PROCEDURE ADD_ACTOR(a_name IN VARCHAR, a_surname IN VARCHAR, a_age IN INT, a_hometown IN VARCHAR)
IS
  BEGIN
    INSERT INTO ACTOR (ACTOR_NAME, ACTOR_SURNAME, ACTOR_AGE, ACTOR_HOMETOWN)
    VALUES (a_name, a_surname, a_age, a_hometown);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_ACTOR(a_id       IN INT, a_name IN VARCHAR, a_surname IN VARCHAR, a_age IN INT,
                                         a_hometown IN VARCHAR)
IS
  BEGIN
    UPDATE ACTOR
    SET ACTOR_NAME   = a_name,
      ACTOR_SURNAME  = a_surname,
      ACTOR_AGE      = a_age,
      ACTOR_HOMETOWN = a_hometown
    WHERE ACTOR_ID = a_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_ACTOR(a_id IN INT)
IS
  BEGIN
    DELETE FROM ACTOR
    WHERE ACTOR_ID = a_id;
  END;
/




-- 9
CREATE TABLE DIRECTOR (
  DIRECTOR_ID       NUMBER(10) NOT NULL,
  DIRECTOR_NAME     VARCHAR(50),
  DIRECTOR_SURNAME  VARCHAR(50),
  DIRECTOR_AGE      NUMBER(10),
  DIRECTOR_HOMETOWN VARCHAR(50),
  PRIMARY KEY (DIRECTOR_ID)
);
/

CREATE SEQUENCE DIRECTOR_SEQUENCE START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER DIRECTOR_TRIGGER
BEFORE INSERT ON DIRECTOR
FOR EACH ROW
WHEN (new.DIRECTOR_ID IS NULL)
  BEGIN
    SELECT DIRECTOR_SEQUENCE.NEXTVAL
    INTO :new.DIRECTOR_ID
    FROM dual;
  END;
/

CREATE OR REPLACE PROCEDURE ADD_DIRECTOR(d_name IN VARCHAR, d_surname IN VARCHAR, d_age IN INT, d_hometown IN VARCHAR)
IS
  BEGIN
    INSERT INTO DIRECTOR (DIRECTOR_NAME, DIRECTOR_SURNAME, DIRECTOR_AGE, DIRECTOR_HOMETOWN)
    VALUES (d_name, d_surname, d_age, d_hometown);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_DIRECTOR(d_id       IN INT, d_name IN VARCHAR, d_surname IN VARCHAR, d_age IN INT,
                                            d_hometown IN VARCHAR)
IS
  BEGIN
    UPDATE DIRECTOR
    SET DIRECTOR_NAME   = d_name,
      DIRECTOR_SURNAME  = d_surname,
      DIRECTOR_AGE      = d_age,
      DIRECTOR_HOMETOWN = d_hometown
    WHERE DIRECTOR_ID = d_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_DIRECTOR(d_id IN INT)
IS
  BEGIN
    DELETE FROM DIRECTOR
    WHERE DIRECTOR_ID = d_id;
  END;
/




--10
CREATE TABLE MOVIE_ACTOR (
  MOVIE_ID NUMBER(10) NOT NULL,
  ACTOR_ID NUMBER(10) NOT NULL,
  PRIMARY KEY (MOVIE_ID, ACTOR_ID),
  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE,
  FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR (ACTOR_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_MOVIE_ACTOR(m_id IN INT, a_id IN INT)
IS
  BEGIN
    INSERT INTO MOVIE_ACTOR (MOVIE_ID, ACTOR_ID)
    VALUES (m_id, a_id);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_MOVIE_ACTOR(m_id IN INT, a_id IN INT)
IS
  BEGIN
    DELETE FROM MOVIE_ACTOR
    WHERE MOVIE_ID = m_id AND ACTOR_ID = a_id;
  END;
/




--11
CREATE TABLE MOVIE_DIRECTOR (
  MOVIE_ID    NUMBER(10) NOT NULL,
  DIRECTOR_ID NUMBER(10) NOT NULL,
  PRIMARY KEY (MOVIE_ID, DIRECTOR_ID),
  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE,
  FOREIGN KEY (DIRECTOR_ID) REFERENCES DIRECTOR (DIRECTOR_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_MOVIE_DIRECTOR(m_id IN INT, d_id IN INT)
IS
  BEGIN
    INSERT INTO MOVIE_DIRECTOR (MOVIE_ID, DIRECTOR_ID)
    VALUES (m_id, d_id);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_MOVIE_DIRECTOR(m_id IN INT, d_id IN INT)
IS
  BEGIN
    DELETE FROM MOVIE_DIRECTOR
    WHERE MOVIE_ID = m_id AND DIRECTOR_ID = d_id;
  END;
/




--12
CREATE TABLE MOVIE_COMMENT (
  MOVIE_ID      NUMBER(10) NOT NULL,
  MEMBER_ID     NUMBER(10) NOT NULL,
  MOVIE_COMMENT VARCHAR(255),
  PRIMARY KEY (MOVIE_ID, MEMBER_ID),
  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_MOVIE_COMMENT(mov_id IN INT, mem_id IN INT, m_comment IN VARCHAR)
IS
  BEGIN
    INSERT INTO MOVIE_COMMENT (MOVIE_ID, MEMBER_ID, MOVIE_COMMENT)
    VALUES (mov_id, mem_id, m_comment);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_MOVIE_COMMENT(mov_id IN INT, mem_id IN INT, m_comment IN VARCHAR)
IS
  BEGIN
    UPDATE MOVIE_COMMENT
    SET MOVIE_COMMENT = m_comment
    WHERE MOVIE_ID = mov_id
          AND MEMBER_ID = mem_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_MOVIE_COMMENT(mov_id IN INT, mem_id IN INT)
IS
  BEGIN
    DELETE FROM MOVIE_COMMENT
    WHERE MOVIE_ID = mov_id AND MEMBER_ID = mem_id;
  END;
/




--13
CREATE TABLE ACTOR_COMMENT (
  ACTOR_ID      NUMBER(10) NOT NULL,
  MEMBER_ID     NUMBER(10) NOT NULL,
  ACTOR_COMMENT VARCHAR(255),
  PRIMARY KEY (ACTOR_ID, MEMBER_ID),
  FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR (ACTOR_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_ACTOR_COMMENT(a_id IN INT, m_id IN INT, a_comment IN VARCHAR)
IS
  BEGIN
    INSERT INTO ACTOR_COMMENT (ACTOR_ID, MEMBER_ID, ACTOR_COMMENT)
    VALUES (a_id, m_id, a_comment);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_ACTOR_COMMENT(a_id IN INT, m_id IN INT, a_comment IN VARCHAR)
IS
  BEGIN
    UPDATE ACTOR_COMMENT
    SET ACTOR_COMMENT = a_comment
    WHERE ACTOR_ID = a_id
          AND MEMBER_ID = m_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_ACTOR_COMMENT(a_id IN INT, m_id IN INT)
IS
  BEGIN
    DELETE FROM ACTOR_COMMENT
    WHERE ACTOR_ID = a_id AND MEMBER_ID = m_id;
  END;
/




--13
CREATE TABLE DIRECTOR_COMMENT (
  DIRECTOR_ID      NUMBER(10) NOT NULL,
  MEMBER_ID        NUMBER(10) NOT NULL,
  DIRECTOR_COMMENT VARCHAR(255),
  PRIMARY KEY (DIRECTOR_ID, MEMBER_ID),
  FOREIGN KEY (DIRECTOR_ID) REFERENCES DIRECTOR (DIRECTOR_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_DIRECTOR_COMMENT(d_id IN INT, m_id IN INT, d_comment IN VARCHAR)
IS
  BEGIN
    INSERT INTO DIRECTOR_COMMENT (DIRECTOR_ID, MEMBER_ID, DIRECTOR_COMMENT)
    VALUES (d_id, m_id, d_comment);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_DIRECTOR_COMMENT(d_id IN INT, m_id IN INT, d_comment IN VARCHAR)
IS
  BEGIN
    UPDATE DIRECTOR_COMMENT
    SET DIRECTOR_COMMENT = d_comment
    WHERE DIRECTOR_ID = d_id
          AND MEMBER_ID = m_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_DIRECTOR_COMMENT(d_id IN INT, m_id IN INT)
IS
  BEGIN
    DELETE FROM DIRECTOR_COMMENT
    WHERE DIRECTOR_ID = d_id AND MEMBER_ID = m_id;
  END;
/




--15
CREATE TABLE MOVIE_RATING (
  MOVIE_ID     NUMBER(10) NOT NULL,
  MEMBER_ID    NUMBER(10) NOT NULL,
  MOVIE_RATING NUMBER(10),
  PRIMARY KEY (MOVIE_ID, MEMBER_ID),
  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE (MOVIE_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_MOVIE_RATING(mov_id IN INT, mem_id IN INT, m_rating IN INT)
IS
  BEGIN
    INSERT INTO MOVIE_RATING (MOVIE_ID, MEMBER_ID, MOVIE_RATING)
    VALUES (mov_id, mem_id, m_rating);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_MOVIE_RATING(mov_id IN INT, mem_id IN INT, m_rating IN INT)
IS
  BEGIN
    UPDATE MOVIE_RATING
    SET MOVIE_RATING = m_rating
    WHERE MOVIE_ID = mov_id
          AND MEMBER_ID = mem_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_MOVIE_RATING(mov_id IN INT, mem_id IN INT)
IS
  BEGIN
    DELETE FROM MOVIE_RATING
    WHERE MOVIE_ID = mov_id AND MEMBER_ID = mem_id;
  END;
/




--16
CREATE TABLE ACTOR_RATING (
  ACTOR_ID     NUMBER(10) NOT NULL,
  MEMBER_ID    NUMBER(10) NOT NULL,
  ACTOR_RATING NUMBER(10),
  PRIMARY KEY (ACTOR_ID, MEMBER_ID),
  FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR (ACTOR_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_ACTOR_RATING(a_id IN INT, m_id IN INT, a_rating IN INT)
IS
  BEGIN
    INSERT INTO ACTOR_RATING (ACTOR_ID, MEMBER_ID, ACTOR_RATING)
    VALUES (a_id, m_id, a_rating);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_ACTOR_RATING(a_id IN INT, m_id IN INT, a_rating IN INT)
IS
  BEGIN
    UPDATE ACTOR_RATING
    SET ACTOR_RATING = a_rating
    WHERE ACTOR_ID = a_id
          AND MEMBER_ID = m_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_ACTOR_RATING(a_id IN INT, m_id IN INT)
IS
  BEGIN
    DELETE FROM ACTOR_RATING
    WHERE ACTOR_ID = a_id AND MEMBER_ID = m_id;
  END;
/




--17
CREATE TABLE DIRECTOR_RATING (
  DIRECTOR_ID     NUMBER(10) NOT NULL,
  MEMBER_ID       NUMBER(10) NOT NULL,
  DIRECTOR_RATING NUMBER(10),
  PRIMARY KEY (DIRECTOR_ID, MEMBER_ID),
  FOREIGN KEY (DIRECTOR_ID) REFERENCES DIRECTOR (DIRECTOR_ID) ON DELETE CASCADE,
  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID) ON DELETE CASCADE
);
/

CREATE OR REPLACE PROCEDURE ADD_DIRECTOR_RATING(d_id IN INT, m_id IN INT, d_rating IN INT)
IS
  BEGIN
    INSERT INTO DIRECTOR_RATING (DIRECTOR_ID, MEMBER_ID, DIRECTOR_RATING)
    VALUES (d_id, m_id, d_rating);
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE UPDATE_DIRECTOR_RATING(d_id IN INT, m_id IN INT, d_rating IN INT)
IS
  BEGIN
    UPDATE DIRECTOR_RATING
    SET DIRECTOR_RATING = d_rating
    WHERE DIRECTOR_ID = d_id
          AND MEMBER_ID = m_id;
    COMMIT;
  END;
/

CREATE OR REPLACE PROCEDURE DELETE_DIRECTOR_RATING(d_id IN INT, m_id IN INT)
IS
  BEGIN
    DELETE FROM DIRECTOR_RATING
    WHERE DIRECTOR_ID = d_id AND MEMBER_ID = m_id;
  END;
/




-- 18
CREATE TABLE LOGS (
  LOG_ID      NUMBER(10),
  LOG_CONTEXT VARCHAR(255),
  PRIMARY KEY (LOG_ID)
)
/

CREATE SEQUENCE LOGS_SEQUENCE START WITH 1 INCREMENT BY 1;
/

CREATE OR REPLACE TRIGGER LOGS_TRIGGER
BEFORE INSERT ON LOGS
FOR EACH ROW
WHEN (new.LOG_ID IS NULL)
  BEGIN
    SELECT LOGS_SEQUENCE.NEXTVAL
    INTO :new.LOG_ID
    FROM dual;
  END;
/

CREATE OR REPLACE PROCEDURE ADD_LOG(l_context IN VARCHAR)
IS
  BEGIN
    INSERT INTO LOGS (LOG_CONTEXT)
    VALUES (l_context);
    COMMIT;
  END;
/