Movie Theatre Database

Project Info/Table_Code.sql

Drop table User_table cascade constraints; create table User_table( --- changed table name because 'user' isn't accepted by the sql dev user_id int, user_name varchar(10), user_email varchar(30), user_address varchar(50), user_password varchar(30), primary key(user_id) ); Insert into User_table values( 1 , 'Isayas' , 'isayas1@is420.com' , '231 Spooner Street' , 'ph03b3'); Insert into User_table values( 2 , 'Jimmy' , 'jimmy1@is420.com' , '1742 Evergreen Terrace' , 'ch4ndl3r'); Insert into User_table values( 3 , 'Quentin' , 'quentin1@is420.com' , 'Apartment 5A, 129 West 81st Street' , 'm0nic4'); Insert into User_table values( 4 , 'Sullivan' , 'sullivan1@is420.com' , '81882 Gerard Street' , 'j03y'); Insert into User_table values( 5 , 'Trevor' , 'trevor1@is420.com' , '64222 Clinton Way' , 'r4ch3l'); Drop table Cinema cascade constraints; create table Cinema ( cinema_id int, cinema_name varchar(30), cinema_address varchar(30), cinema_phone varchar(12), cinema_auditoriums int, primary key(cinema_id) ); Insert into Cinema values ( 1 , 'Cinema 21' , '123 Maple Lane' , '240-123-7410' , 4000 ); Insert into Cinema values ( 2 , 'AMC' , '456 Birch Avenue' , '401-456-9630' , 2000 ); Insert into Cinema values ( 3 , 'Ziegfeld Theatre' , '789 Oak Drive' , '443-789-4561' , 400 ); Drop table Company cascade constraints; create table Company ( company_id int, company_name varchar(30), company_child_discount number(2,2), --- may vary company_senior_discount number(2,2), --- may vary primary key(company_id) ); Insert into Company values ( 1 , 'AMC', 0.8 , 0.9); Insert into Company values ( 2 , 'Cinema 21', 0.6 , 0.8); Insert into Company values ( 3 , 'Cinema 21', 0.65 , 0.75); Insert into Company values ( 4 , 'Ziegfeld Theatre', 0.6 , 0.7); Drop table Auditorium cascade constraints; create table Auditorium ( auditorium_id int, Cinema_id int, auditorium_type varchar(10), capacity int, primary key(Auditorium_id), foreign key(cinema_id) references Cinema (cinema_id) ); Insert into Auditorium values ( 1 , 1 , 'regular' , 100 ); Insert into Auditorium values ( 2 , 1 , '3D' , 200 ); Insert into Auditorium values ( 3 , 1 , 'IMAX' , 300 ); Insert into Auditorium values ( 4 , 3 , 'regular' , 50 ); Insert into Auditorium values ( 5 , 2 , 'regular' , 100 ); Drop table Movie cascade constraints; Create table Movie ( movie_id int, movie_title varchar(100), movie_release_date date, movie_rating varchar(5), avg_imdb_review number(4,2), movie_length varchar(10), Primary key (movie_id) ); Insert into Movie values ( 1 , 'Alita: Battle Angel' , to_date('02-14-2019','mm-dd-yyyy') , 'PG-13' , 77.00 , '2 h 02 min' ); Insert into Movie values ( 2 , 'Lego Movie 2' , to_date('02-08-2019','mm-dd-yyyy') , 'PG', 80.00 , '1 h 47 min' ); Insert into Movie values ( 3 , 'Cold Pursuit' , to_date('02-08-2019','mm-dd-yyyy') , 'R', 64.50 , '1 h 59 min' ); Insert into Movie values ( 4 , 'Glass' , to_date('01-18-2019','mm-dd-yyyy') , 'R', 55.5 , '2h 09min' ); Insert into Movie values ( 5 , 'Green Book' , to_date('11-16-2018','mm-dd-yyyy') , 'PG-13', 85.5 , '2 h 10 min' ); Drop table Show_times cascade constraints; Create table Show_times ( movie_id int, auditorium_id int, cinema_id int, show_start_time timestamp, show_end_time timestamp, format varchar(7), full_show varchar(1), foreign key (movie_id) references Movie(movie_id), foreign key (auditorium_id) references Auditorium(auditorium_id), foreign key (cinema_id) references Cinema(cinema_id) ); Insert into Show_times values ( 1 , 2 , 1 , to_date('03-05-2019 14:00:00', 'mm-dd-yyyy hh24:mi:ss') , to_date('03-05-2019 16:00:00', 'mm-dd-yyyy hh24:mi:ss') , 'regular' , 'N' ); Insert into Show_times values ( 2 , 5 , 2 , to_date('03-05-2019 12:30:00', 'mm-dd-yyyy hh24:mi:ss') , to_date('03-05-2019 13:50:00', 'mm-dd-yyyy hh24:mi:ss') , '3D' , 'Y' ); Insert into Show_times values (2 , 3 , 1 , to_date('03-05-2019 11:00:00', 'mm-dd-yyyy hh24:mi:ss') , to_date('03-05-2019 13:00:00', 'mm-dd-yyyy hh24:mi:ss') , 'regular' , 'N' ); Insert into Show_times values ( 5 , 1 , 1 , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss') , to_date('03-05-2019 19:45:00', 'mm-dd-yyyy hh24:mi:ss') , 'regular' , 'Y' ); Drop table Seating cascade constraints; Create table Seating ( seat_id int, auditorium_id int, cinema_id int, seat_availability varchar(1), primary key (seat_id), foreign key (auditorium_id) references Auditorium(auditorium_id), Foreign key(cinema_id) references Cinema(cinema_id) ); Insert into Seating values ( 1 , 1 , 1, 'Y'); Insert into Seating values ( 2 , 2 , 2, 'Y'); Insert into Seating values ( 3 , 1 , 3, 'Y'); Insert into Seating values ( 4 , 3 , 1, 'Y'); Insert into seating values (5, 2, 2, 'Y'); Drop table Transactions cascade constraints; Create table Transactions ( transaction_id int, user_id int, purchase_time timestamp, purchase_quantity int, purchase_status int, purchase_amount number(6,2), primary key (transaction_id), foreign key (user_id) references User_table(user_id) ); Insert into Transactions values ( 1 , 1 , to_date(' 02-28-2019 ',' mm-dd-yyyy '), 2 , 1 , 40.50 ); Insert into Transactions values ( 2 , 2 , to_date(' 02-28-2019 ',' mm-dd-yyyy '), 2 , 0 , 50.10 ); Insert into Transactions values ( 3 , 3 , to_date(' 02-28-2019 ',' mm-dd-yyyy '), 1 , 2 , 78.30 ); Insert into Transactions values ( 4 , 4 , to_date(' 02-28-2019 ',' mm-dd-yyyy '), 1 , 1 , 80.50 ); drop table discount cascade constraints; create table discount( disc_id int, disc_rate int, primary key(disc_id) ); insert into discount values(1, .30); insert into discount values(2, .25); Drop table Ticket cascade constraints; Create table Ticket ( ticket_id int, ticket_type varchar(6), Base_price int, cinema_id int, auditorium_id int, seat_id int, primary key (ticket_id), foreign key (cinema_id) references Cinema(cinema_id), foreign key (auditorium_id) references Auditorium(auditorium_id), foreign key (seat_id) references Seating(seat_id) ); Insert into Ticket values ( 1 , 'adult' , 10, 1 , 1 , 1 ); Insert into Ticket values ( 2 , 'adult' , 10, 1 , 1 , 2 ); Insert into Ticket values ( 3 , 'child' , 10, 1 , 2 , 3 ); Insert into Ticket values ( 4 , 'adult' , 10, 2 , 3 , 4); Insert into Ticket values ( 5 , 'senior' , 10, 2 , 3 , 5); Drop table Payment cascade constraints; Create table Payment ( payment_id int, transaction_id int, payment_type int, payment_method int, last_four_digits varchar(4), payment_amount number(6,2), payment_time timestamp, primary key (payment_id), foreign key (transaction_id) references Transactions(transaction_id) ); Insert into Payment values ( 1 , 1 , 1 , 1 , '1234', 25.50 , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss') ); Insert into Payment values ( 2 , 2 , 1 , 2 , '5678' , 36.15 , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss')); Insert into Payment values ( 3 , 3 , 1 , 3 , '2468' , 40.50 , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss')); Drop table Reviews cascade constraints; Create table Reviews ( review_id int, user_id int, cinema_id int, score int, review_text varchar(2000), review_time timestamp, primary key (review_id), foreign key (user_id) references User_table(user_id), foreign key (cinema_id) references Cinema(cinema_id) ); Insert into Reviews values ( 1 , 1 , 1 , 3 , 'Popcorn was too salty!' , to_date ('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss')); Insert into Reviews values ( 2 , 2 , 2 , 3 , 'They did not have Mike and Ikes.' , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss') ); Insert into Reviews values ( 3 , 3 , 3 , 5 , 'Great place and friendly staff' , to_date('03-05-2019 17:00:00', 'mm-dd-yyyy hh24:mi:ss') );