Please send questions to
st10@humboldt.edu .
-- hw3_setup.sql
-- last modified: 3-3-03
-- table movie_category holds this store's
-- chosen movie categories for shelving movies
drop table movie_category cascade constraints;
create table movie_category
(category_code char(3),
category_name varchar2(10) not null,
primary key (category_code)
);
-- insert initial set of categories
insert into movie_category
values
('100', 'Comedy');
insert into movie_category
values
('200', 'Action');
insert into movie_category
values
('300', 'Drama');
insert into movie_category
values
('400', 'Classic');
insert into movie_category
values
('500', 'Family');
-- table renter keeps track of all renters
-- who have either rented a video or who have
-- indicated a wish to rent a video sometime
drop table renter cascade constraints;
create table renter
(renter_num char(4),
renter_lname varchar2(15) not null,
renter_fname varchar2(15),
renter_phone char(8),
renter_credit_rtg decimal(2,1) check(renter_credit_rtg
between 0.0 and 5.0),
renter_fave_cat char(3),
primary key (renter_num),
foreign key (renter_fave_cat) references
movie_category(category_code)
);
-- insert initial set of renters
insert into renter
values
('1111', 'Alpha', 'Ann', '111-1111', 4.9, '100');
insert into renter
values
('2222', 'Beta', 'Bob', '222-2222', 1.2, '200');
insert into renter
values
('3333', 'Carlos','Ann', '333-3333', 3.8, '300');
insert into renter
values
('4444', 'Delta','David','222-2222', 3.4, '400');
insert into renter(renter_num, renter_lname, renter_fname, renter_phone,
renter_credit_rtg)
values
('5555', 'Beta', 'Edie', '555-5555', 5.0);
insert into renter
values
('6666', 'Epsilon', 'Emma', '666-6666', 2.1, '400');
insert into renter
values
('7777', 'Feta', 'Frank', '777-7777', 0.0, '500');
insert into renter
values
('8888', 'Greta', 'Gail', '888-8888', 4.4, '200');
insert into renter
values
('9999', 'Harpo', 'Harry', '999-9999', 3.3, '100');
-- table movie holds information about movies
-- themselves (such movies may then have video
-- instances in various formats, etc.)
drop table movie cascade constraints;
create table movie
(movie_num char(5),
movie_title varchar2(40) not null,
movie_director_lname varchar2(30),
movie_yr_released char(4) default '2002',
movie_rating varchar2(5) check(movie_rating in ('G', 'PG',
'PG-13', 'R', 'A')),
category_code char(3),
primary key (movie_num),
foreign key (category_code) references movie_category
);
-- insert initial set of movies
insert into movie
values
('10001', 'Gone with the Wind', 'Fleming', '1939', 'G', '400');
-- for those of you who read closely: note how I get a single quote
-- within a string: you type two single-quote characters in a row,
-- as you see in the movie title below:
insert into movie
values
('10002', 'It''s a Wonderful Life', 'Capra', '1946', 'G', '400');
insert into movie
values
('10003', 'The Muppet Movie', 'Frawley', '1979', 'G', '500');
insert into movie
values
('20001', 'Indiana Jones and the Temple of Doom', 'Spielberg', '1984', 'PG',
'200');
insert into movie
values
('20002', 'Indiana Jones and the Last Crusade', 'Spielberg', '1989', 'PG-13',
'200');
insert into movie
values
('30001', 'Babe', 'Noonan', '1995', 'G', '500');
insert into movie
values
('30002', 'Toy Story', 'Lasseter', '1995', 'G', '500');
-- I know these are wrong, I don't have my movie reference on me... 8-)
insert into movie
values
('30003', 'Toy Story 2', 'Lasseter', '1999', 'G', '500');
insert into movie
values
('20003', 'Star Wars', 'Lucas', '1977', 'PG', '200');
insert into movie
values
('20004', 'The Empire Strikes Back', 'Lucas', '1980', 'PG', '200');
insert into movie
values
('20005', 'Return of the Jedi', 'Lucas', '1984', 'PG', '200');
insert into movie
values
('20006', 'The Phantom Menace', 'Lucas', '1999', 'PG-13', '200');
insert into movie
values
('40001', 'One Flew Over the Cuckoo''s Nest', 'Mazursky', '1970', 'R', '300');
insert into movie
values
('40002', 'Midnight Cowboy', 'Lewis', '1969', 'A', '300');
insert into movie
values
('40003', 'Clockwork Orange', 'Smith', '1971', 'A', '300');
insert into movie
values
('10004', 'All About Eve', 'Jones', '1938', 'G', '400');
insert into movie
values
('10005', 'Philadelphia Story', 'Murphy', '1939', 'G', '400');
insert into movie
values
('40004', 'Fatal Attraction', 'Osgood', '1991', 'R', '300');
insert into movie
values
('20007', 'The Matrix', 'Smitty', '1999', 'R', '200');
insert into movie
values
('10006', 'Blues Brothers', 'Landis', '1980', 'PG', '100');
-- table video keeps track of videos that
-- have ever been rented or have been available
-- for rental
drop table video cascade constraints;
create table video
(vid_id char(6),
vid_format varchar2(4) not null,
vid_purchase_date date,
vid_rental_price decimal(3,2),
movie_num char(5),
primary key (vid_id),
foreign key (movie_num) references movie
);
-- insert initial set of videos
insert into video
values
('120011','VHS','11-JAN-1998', 2.99, '10001');
insert into video
values
('130012','DVD','22-FEB-1999', 4.99, '10001');
insert into video
values
('120021','VHS','03-MAR-1997', 1.99, '10002');
insert into video
values
('130022', 'DVD', '04-MAR-2000', 5.99, '10002');
insert into video
values
('130031','DVD','22-FEB-1999', 4.99, '10003');
insert into video
values
('130061', 'DVD', '15-JAN-1998', 1.99, '10006');
insert into video
values
('210011','Beta','05-MAY-1994', 0.99, '20001');
insert into video
values
('230012', 'DVD', '01-Jan-1998', 3.99, '20001');
insert into video
values
('220013', 'VHS', '05-Apr-1996', 2.19, '20001');
insert into video
values
('220021', 'VHS', '05-Apr-1996', 2.19, '20002');
insert into video
values
('320011', 'VHS', '05-Apr-1996', 1.99, '30001');
insert into video
values
('330012', 'DVD', '08-May-1998', 3.99, '30001');
insert into video
values
('320021', 'VHS', '06-May-1996', 1.99, '30002');
insert into video
values
('320022', 'VHS', '06-May-1996', 1.99, '30002');
insert into video
values
('330023', 'DVD', '07-Jun-1997', 4.99, '30002');
insert into video
values
('320031', 'VHS', '01-Dec-1999', 2.99, '30003');
insert into video
values
('320032', 'VHS', '01-Dec-1999', 2.99, '30003');
insert into video
values
('330033', 'DVD', '05-May-2000', 5.99, '30003');
insert into video
values
('330034', 'DVD', '05-May-2000', 5.99, '30003');
insert into video
values
('210031', 'Beta', '05-May-1994', 0.99, '20003');
insert into video
values
('220032', 'VHS', '15-Jul-1996', 1.99, '20003');
insert into video
values
('230033', 'DVD', '17-Aug-1998', 3.99, '20003');
insert into video
values
('210041', 'Beta', '05-May-1994', 1.29, '20004');
insert into video
values
('220042', 'VHS', '15-Jul-1996', 1.99, '20004');
insert into video
values
('230043', 'DVD', '17-Aug-1998', 3.99, '20004');
insert into video
values
('220051', 'VHS', '15-Jul-1996', 1.99, '20005');
insert into video
values
('230052', 'DVD', '17-Aug-1998', 3.99, '20005');
insert into video
values
('220061', 'VHS', '01-Dec-1999', 2.99, '20006');
insert into video
values
('220062', 'VHS', '01-Dec-1999', 2.99, '20006');
insert into video
values
('220063', 'VHS', '01-Dec-1999', 2.99, '20006');
insert into video
values
('230064', 'DVD', '01-Dec-1999', 5.99, '20006');
insert into video
values
('420011', 'VHS', '04-Oct-1998', 2.19, '40001');
insert into video
values
('420021', 'VHS', '04-Oct-1998', 2.19, '40002');
insert into video
values
('420031', 'VHS', '04-Oct-1998', 2.19, '40003');
insert into video
values
('120041', 'VHS', '04-Oct-1998', 1.99, '10004');
insert into video
values
('120051', 'VHS', '04-Oct-1998', 1.99, '10005');
insert into video
values
('410041', 'Beta', '05-May-1994', 1.29, '40004');
insert into video
values
('420042', 'VHS', '05-May-1994', 2.19, '40004');
insert into video
values
('220071', 'VHS', '15-Mar-2000', 2.99, '20007');
insert into video
values
('220072', 'VHS', '15-Mar-2000', 2.99, '20007');
insert into video
values
('230073', 'DVD', '15-Mar-2000', 5.99, '20007');
insert into video
values
('230074', 'DVD', '15-Mar-2000', 5.99, '20007');
-- table rental notes which renters have
-- rented which videos
-- (why rental_num, now? because we are now going to change
-- the scenario to allow renters to rent the same video more
-- than once...)
drop table rental cascade constraints;
create table rental
(rental_num char(7),
renter_num char(4),
vid_id char(6),
date_out date not null,
date_due date not null,
date_returned date,
primary key (rental_num),
foreign key (renter_num) references renter,
foreign key (vid_id) references video
);
-- insert initial set of rentals
insert into rental
values
('0000001', '5555','120011', '21-SEP-2002', '23-SEP-2002', '22-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000002', '2222','130031', '19-SEP-2002', '22-SEP-2002');
insert into rental
values
('0000003', '2222','130012', '03-OCT-2002', '08-OCT-2002', '04-OCT-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000004', '3333','130012', '21-SEP-2002', '22-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000005', '3333','210011', '08-AUG-2002', '11-AUG-2002');
insert into rental
values
('0000007', '3333','120011', '15-SEP-2002', '18-SEP-2002', '21-SEP-2002');
insert into rental
values
('0000008', '1111','130022', '04-OCT-2001', '07-OCT-2002', '14-OCT-2002');
insert into rental
values
('0000009', '6666', '130012', '13-SEP-2002', '16-SEP-2002', '16-SEP-2002');
insert into rental
values
('0000010', '7777', '120021', '14-SEP-2002', '17-SEP-2002', '17-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000011', '8888', '230012', '05-OCT-2002', '10-OCT-2002');
insert into rental
values
('0000012', '9999', '210031', '10-OCT-2002', '13-OCT-2002', '13-OCT-2002');
insert into rental
values
('0000013', '9999', '210041', '10-OCT-2002', '13-OCT-2002', '13-OCT-2002');
insert into rental
values
('0000014', '9999', '230033', '10-OCT-2002', '13-OCT-2002', '13-OCT-2002');
insert into rental
values
('0000015', '3333', '420011', '01-OCT-2002', '04-OCT-2002', '03-OCT-2002');
insert into rental
values
('0000016', '2222', '330023', '11-SEP-2002', '16-SEP-2002', '15-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000017', '5555', '220013', '22-SEP-2002', '25-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000018', '1111', '220062', '21-SEP-2002', '26-SEP-2002');
insert into rental
values
('0000019', '3333', '320032', '06-OCT-2002', '09-OCT-2002', '07-OCT-2002');
insert into rental
values
('0000020', '7777', '130031', '29-AUG-2002', '01-SEP-2002', '02-SEP-2002');
insert into rental(rental_num, renter_num, vid_id, date_out, date_due)
values
('0000021', '6666', '210011', '15-SEP-2002', '17-SEP-2002');
insert into rental
values
('0000022', '5555', '210031', '16-SEP-2002', '18-SEP-2002', '17-SEP-2002');
insert into rental
values
('0000023', '4444', '120011', '16-SEP-2002', '18-SEP-2002', '21-SEP-2002');
insert into rental
values
('0000024', '7777', '130031', '08-SEP-2002', '10-SEP-2002', '09-SEP-2002');
-- end of hw4_setup.sql