DROP TABLE CUSTOMERS CASCADE CONSTRAINT;
DROP TABLE DISTRIBUTOR CASCADE CONSTRAINT;
DROP TABLE RENTAL ORDER CASCADE CONSTRAINT;
DROP TABLE MOVIE CASCADE CONSTRAINT;
DROP TABLE STORE CASCADE CONSTRAINT;
DROP TABLE ACTOR CASCADE CONSTRAINT;
DROP TABLE DIRECTOR CASCADE CONSTRAINT;
DROP TABLE AWARD CASCADE CONSTRAINT;
DROP TABLE CATALOG CASCADE CONSTRAINT;

CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER(10) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
ADDRESS VARCHAR2(30),
CITY VARCHAR2(10),
CUSTOMER_STATE VARCHAR2(10),
PHONE  NUMBER(10)
);

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER
      PRIMARY KEY (CUSTOMER_ID);
CREATE TABLE DISTRIBUTOR(
DISTRIBUTOR_ID NUMBER(10) NOT NULL,
DISTRIBUTOR_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
ADDRESS VARCHAR2(30) NOT NULL,
CITY VARCHAR2(10) NOT NULL,
DISTRIBUTOR_STATE VARCHAR2(10) NOT NULL,
PHONE  NUMBER(10)
);

ALTER TABLE DISTRIBUTOR ADD CONSTRAINT PK_DISTRIBUTOR
      PRIMARY KEY (DISTRIBUTOR_ID);

CREATE TABLE MOVIE_TYPE(
MOVIE_TYPE_ID NUMBER(20) NOT NULL,
MOVIE_TYPE VARCHAR2(10) NOT NULL
);

ALTER TABLE MOVIE_TYPE ADD CONSTRAINT PK_MOVIE_TYPE
      PRIMARY KEY (MOVIE_TYPE_ID);
CREATE TABLE ACTOR(
ACTOR_ID NUMBER(20) NOT NULL,
ACTOR_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL
);

ALTER TABLE ACTOR ADD CONSTRAINT PK_ACTOR
      PRIMARY KEY (ACTOR_ID);

CREATE TABLE MOVIE(
MOVIE_ID NUMBER(20) NOT NULL,
MOVIE_TYPE_ID NUMBER(20) NOT NULL,
DISTRIBUTOR_ID NUMBER(20)NOT NULL,
MOVIE_TITLE VARCHAR2(10) NOT NULL,
RATING NUMBER(7),
Running_Length NUMBER(5) NOT NULL,
Year_Released NUMBER(4) NOT NULL
);

ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE
      PRIMARY KEY (MOVIE_ID);

ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_MOVIE_TYPE
      FOREIGN KEY (MOVIE_TYPE_ID) REFERENCES MOVIE_TYPE (MOVIE_TYPE_ID);

ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_DISTRIBUTOR
      FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES DISTRIBUTOR (DISTRIBUTOR_ID);

CREATE TABLE ACADEMY_AWARDS(
Awards_ID NUMBER(20) NOT NULL,
Movie_ID NUMBER(20)NOT NULL,
Category_Awards VARCHAR2(20) NOT NULL,
Year_Awards NUMBER(10) NOT NULL
);

ALTER TABLE ACADEMY_AWARDS ADD CONSTRAINT PK_ACADEMY_AWARDS
      PRIMARY KEY (Awards_ID);

ALTER TABLE ACADEMY_AWARDS ADD CONSTRAINT FK_ACADEMY_AWARDS_MOVIE
       FOREIGN KEY (Movie_ID) REFERENCES MOVIE (Movie_ID);


CREATE TABLE DIRECTOR(
Director_ID NUMBER(20) NOT NULL,
DIRECTOR_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL
);

ALTER TABLE DIRECTOR ADD CONSTRAINT PK_DIRECTOR
      PRIMARY KEY (Director_ID);


CREATE TABLE MOVIE_DIRECTOR(
Direction_ID NUMBER(20) NOT NULL,
Director_ID NUMBER(20) NOT NULL,
Movie_ID NUMBER(20)NOT NULL
);

ALTER TABLE MOVIE_DIRECTOR ADD CONSTRAINT PK_MOVIE_DIRECTOR
      PRIMARY KEY (Direction_ID);

ALTER TABLE MOVIE_DIRECTOR ADD CONSTRAINT FK_MOVIE_DIRECTOR_DIRECTOR
      FOREIGN KEY (Director_ID) REFERENCES DIRECTOR (Director_ID);

ALTER TABLE MOVIE_DIRECTOR ADD CONSTRAINT FK_MOVIE_DIRECTOR_MOVIE
     FOREIGN KEY (Movie_ID) REFERENCES MOVIE (Movie_ID);

CREATE TABLE MOVIE_COPY(
Copy_ID NUMBER(20) NOT NULL,
Movie_ID NUMBER(20)NOT NULL,
Format_type VARCHAR2(10) NOT NULL,
Amount_charge NUMBER(10) NOT NULL
);

ALTER TABLE MOVIE_COPY ADD CONSTRAINT PK_MOVIE_COPY
      PRIMARY KEY (Copy_ID);

ALTER TABLE MOVIE_COPY ADD CONSTRAINT FK_MOVIE_COPY_MOVIE
       FOREIGN KEY (Movie_ID) REFERENCES MOVIE (Movie_ID);

CREATE TABLE STORE(
STORE_ID NUMBER(20) NOT NULL,
Store_Name VARCHAR2(30) NOT NULL,
Store_Address VARCHAR2(30) NOT NULL,
CITY VARCHAR2(20) NOT NULL,
STORE_STATE VARCHAR2(20) NOT NULL,
PHONE  NUMBER(10) NOT NULL
);

ALTER TABLE STORE_M ADD CONSTRAINT PK_STORE_M
      PRIMARY KEY (STORE_M_ID);

CREATE TABLE RENTAL(
RENTAL_ID NUMBER(20) NOT NULL,
CUSTOMER_ID NUMBER(20) NOT NULL,
Copy_ID NUMBER(20)  NOT NULL,
Rental_Date DATE NOT NULL,
Return_Date DATE
);

ALTER TABLE RENTAL ADD CONSTRAINT PK_RENTAL
      PRIMARY KEY (Rental_ID);

ALTER TABLE RENTAL ADD CONSTRAINT FK_RENTAL_ID_CUSTOMER
      FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE RENTAL_RECORD ADD CONSTRAINT FK_RENTAL_RECORD_MOVIE_COPY
      FOREIGN KEY (Copy_ID) REFERENCES MOVIE_COPY (Copy_ID);


CREATE TABLE CATALOG(
Catalog_ID NUMBER(20) NOT NULL,
STORE_ID NUMBER(20) NOT NULL,
DISTRIBUTOR_ID NUMBER(20) NOT NULL,
Copy_ID NUMBER(20)  NOT NULL,
Catalog_Num NUMBER(20) NOT NULL
);

ALTER TABLE CATALOG ADD CONSTRAINT PK_ELECTRONIC_CATALOG
      PRIMARY KEY (Catalog_ID);

ALTER TABLE CATALOG ADD CONSTRAINT FK_ELECTRONIC_CATALOG_STORE
      FOREIGN KEY (STORE_ID) REFERENCES STORE (STORE_ID);

ALTER TABLE CATALOG ADD CONSTRAINT
 FK_CATALOG_DISTRIBUTOR
 FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES DISTRIBUTOR (DISTRIBUTOR_ID);

  ALTER TABLE CATALOG ADD CONSTRAINT
  FK_CATALOG_MOVIE_COPY
  FOREIGN KEY (Copy_ID) REFERENCES MOVIE_COPY (Copy_ID);