drop index idx_rentaldate;
drop index idx_movieid;
drop table rental_copy;
drop table movie;
drop table rental;
drop sequence rentalID_seq;


create table movie(
movieID integer primary key,
title varchar2(50),
director varchar2(50),
releaseDate date,
leadActor varchar2(50));

create table rental_copy (
inventoryID integer,
movieID integer,
mediaFormat varchar2 (20),
primary key (inventoryID),
foreign key (movieID) references movie(movieID));

create table rental (
rentalID integer not null primary key,
inventoryID integer not null,
rentaldate date not null,
customerID integer not null,
returnDate date,
check (rentaldate < returndate),
foreign key (inventoryid) references rental_copy(inventoryid)
);

insert into movie (movieID, title, director, releasedate, leadactor) values (1,'Star Wars', 'George Lucas', '25-May-77', 'Harrison Ford');
insert into movie (movieID, title, director, releasedate, leadactor) values (2,'The Empire Strikes Back', 'George Lucas', '21-May-80', 'Harrison Ford');
insert into movie (movieID, title, director, releasedate, leadactor) values (3,'Star Wars', 'George Lucas', '25-May-83', 'Harrison Ford');

insert into rental_copy (inventoryID, movieID, mediaFormat) values (1, 1, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (2, 1, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (3, 1, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (4, 1, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (5, 1, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (6, 1, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (7, 1, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (8, 1, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (9, 2, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (10, 2, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (11, 2, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (12, 2, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (13, 2, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (14, 2, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (15, 2, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (16, 2, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (17, 3, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (18, 3, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (19, 3, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (20, 3, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (21, 3, 'DVD');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (22, 3, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (23, 3, 'Blu-Ray');
insert into rental_copy (inventoryID, movieID, mediaFormat) values (24, 3, 'Blu-Ray');