CREATE TABLE PRICE (
PRICE_CODE INTEGER NOT NULL UNIQUE,
PRICE_DESCRIPTION CHAR(15) NOT NULL,
PRICE_RENTFEE VARCHAR(3) NOT NULL,
PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL,
PRIMARY KEY (PRICE_CODE));

CREATE TABLE MOVIE (
MOVIE_NUM INTEGER NOT NULL UNIQUE,
MOVIE_TITLE VARCHAR(35) NOT NULL,
MOVIE_YEAR INTEGER NOT NULL,
MOVIE_COST NUMBER NOT NULL,
MOVIE_GENRE CHAR(6) NOT NULL,
PRICE_CODE INTEGER,
PRIMARY KEY (MOVIE_NUM),
FOREIGN KEY (PRICE_CODE) references PRICE(PRICE_CODE)
);

CREATE TABLE VIDEO (
VID_NUM INTEGER NOT NULL UNIQUE,
VID_INDATE DATE NOT NULL,
MOVIE_NUM INTEGER NOT NULL,
PRIMARY KEY (VID_NUM),
FOREIGN KEY (MOVIE_NUM) references MOVIE(MOVIE_NUM)
);

CREATE TABLE DETAILRENTAL (
RENT_NUM INTEGER NOT NULL,
VID_NUM INTEGER NOT NULL,
DETAIL_FEE NUMBER NOT NULL,
DETAIL_DUEDATE DATE NOT NULL,
DETAIL_RETURNDATE DATE,
DETAIL_DAILYLATEFEE NUMBER NOT NULL,
PRIMARY KEY (RENT_NUM, VID_NUM),
FOREIGN KEY (RENT_NUM) references RENTAL(RENT_NUM),
FOREIGN KEY (VID_NUM) references VIDEO(VID_NUM),
CONSTRAINT VID_UI1 UNIQUE (RENT_NUM, VID_NUM)
);

CREATE TABLE RENTAL (
RENT_NUM INTEGER NOT NULL UNIQUE,
RENT_DATE DATE NOT NULL,
MEM_NUM INTEGER NOT NULL,
PRIMARY KEY (RENT_NUM),
FOREIGN KEY (MEM_NUM) references MEMBERSHIP(MEM_NUM)
);

CREATE TABLE MEMBERSHIP (
MEM_NUM INTEGER NOT NULL UNIQUE,
MEM_FNAME VARCHAR(15) NOT NULL,
MEM_LNAME VARCHAR(15) NOT NULL,
MEM_STREET VARCHAR(35) NOT NULL,
MEM_CITY CHAR(15) NOT NULL,
MEM_STATE CHAR(2) NOT NULL,
MEM_ZIP VARCHAR(5) NOT NULL,
MEM_BALANCE NUMBER,
PRIMARY KEY (MEM_NUM)
);