DROP TABLE PRICE;
DROP TABLE MOVIE;
DROP TABLE VIDEO;
DROP TABLE MEMBERSHIP;
DROP TABLE RENTAL;
DROP TABLE DETAILRENTAL;


CREATE TABLE PRICE (
PRICE_CODE		NUMBER(2,0)  PRIMARY KEY,
PRICE_DESCRIPTION	VARCHAR(20)  NOT NULL  ,
PRICE_RENTFEE		NUMBER(5,2) CHECK (PRICE_RENTFEE >= 0),
PRICE_DAILYLATEFEE	NUMBER(5,2) CHECK (PRICE_DAILYLATEFEE >= 0)
);

CREATE TABLE MOVIE (
MOVIE_NUM	NUMBER(8,0)  PRIMARY KEY,
MOVIE_TITLE	VARCHAR(75) NOT NULL,
MOVIE_YEAR	NUMBER(4,0)  CHECK (MOVIE_YEAR > 1900),
MOIVE_COST	NUMBER(5,2),
MOVIE_GENRE	VARCHAR(50),
PRICE_CODE	NUMBER(2,0) CONSTRAINT MOVIE_PRICE_CODE_FK REFERENCES PRICE
);

CREATE TABLE VIDEO (
VID_NUM		NUMBER(8,0) PRIMARY KEY,
VID_INDATE		DATE,
MOVIE_NUM	NUMBER(8,0) CONSTRAINT VIDEO_MOVIE_NUM_FK REFERENCES MOVIE
);

CREATE TABLE MEMBERSHIP (
MEM_NUM		NUMBER(8,0) PRIMARY KEY,
MEM_FNAME	VARCHAR(30) NOT NULL,
MEM_LNAME	VARCHAR(30) NOT NULL,
MEM_STREET	VARCHAR(120),
MEM_CITY		VARCHAR(50),
MEM_STATE	CHAR(2),
MEM_ZIP		CHAR(5),
MEM_BALANCE	NUMBER(10,2)
);

CREATE TABLE RENTAL (
RENT_NUM	NUMBER(8,0) PRIMARY KEY,
RENT_DATE	DATE DEFAULT SYSDATE,
MEM_NUM	NUMBER(8,0) CONSTRAINT RENTAL_MEM_NUM_FK REFERENCES MEMBERSHIP
);

 
CREATE TABLE DETAILRENTAL (
RENT_NUM			NUMBER(8,0), 
VID_NUM			NUMBER(8,0), 
DETAIL_FEE			NUMBER(5,2),
DETAIL_DUEDATE		DATE,
DETAIL_RETURNDATE	DATE,
DETAIL_DAILYLATEFEE	NUMBER(5,2),
CONSTRAINT DETAIL_RENT_VID_PK PRIMARY KEY (RENT_NUM, VID_NUM)
/*CONSTRAINT DETAIL_RENT_NUM_FK REFERENCES RENTAL*/
/*CONSTRAINT DETAIL_VID_NUM_FK REFERENCES VIDEO,*/
);