Joseph de la Fuente
cs308 Project #1

==============================================
(b)

CREATE TABLE movie (
   mid    number(6),
   mname  varchar2(40),
   genre  varchar2(40),	
   year   number(4),
   descr  varchar2(500),
   primary key (mid),
   constraint yearconstraint 
    check (year >= 1900 and year <= 2004));

CREATE TABLE theater (
   tid     number(6),
   tname   varchar2(40),
   address varchar2(40),
   code    number(5),
   primary key (tid),
   foreign key (code) references zip);

CREATE TABLE schedule (
   mid       number(6),
   tid       number(6),
   time      number(4),
   price     number(4,2),
   num_seats number(3),
   primary key (mid, tid, time),
   foreign key (mid) references movie,
   foreign key (tid) references theater,
   constraint timeconstraint 
    check (time <= 2400),
   constraint priceconstraint
    check (price >= 0),
   constraint seatconstraint 
    check (num_seats > 0));

CREATE TABLE zip (
   code   number(5),
   lat    number(7,5),
   lon    number(7,5),
   primary key (code));

CREATE TABLE customer (
   cid	     number(6),
   cname     varchar2(40),
   card_num  number(16),
   tid       number(6),
   primary key (cid),
   foreign key (tid) references theater,
   constraint cardconstraint
    check (card_num >= 0));

==============================================
(c) and (d)

(1) user queries

Query #1: list all movies by title or descr containing 
specified words(the,boy)

 select mname
 from movie
 where mname like '%The%' or mname like '%the%'
  or descr like '%Boy%' or descr like '%boy%';

MNAME
----------------------------------------
The Bourne Identity
The Gladiator

Query #2: list all movies in a particular genre(action) in a 
local area(11030)
	
 select mname
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = 11030) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < 10
  and m.genre = 'Action';

MNAME
----------------------------------------
Starship Troopers
Starship Troopers
The Gladiator

Query #3: list all theaters and times within 10 miles of a 
given zip code(11030) for at most $10 for a particular movie(Shrek)

 select tname, time
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = 11030) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < 10
  and m.mname = 'Shrek' and s.price <= 10;

TNAME                                          TIME
---------------------------------------- ----------
MegaMall Theatre                               1300
MegaMall Theatre                               1700
MegaMall Theatre                               2100

Query #4: list all theaters and times within 10 miles of a 
given zip code(11030) within a time frame(no later than 6pm) 
for at most $10 for a particular movie(Shrek)

 select tname, time
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = 11030) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
 and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
  + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < 10
 and m.mname = 'Shrek' and s.price <= 10 and s.time <= 1800;

TNAME                                          TIME
---------------------------------------- ----------
MegaMall Theatre                               1300
MegaMall Theatre                               1700

Query #5: list all movies and its theaters within 10 miles of 
a given zip code(11030) within a time frame(no later than 6pm)

 select mname, tname, time
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = 11030) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
 and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
  + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < 10
 and s.time <= 1800;

MNAME
----------------------------------------
TNAME                                          TIME
---------------------------------------- ----------
Starship Troopers
Sunrise Theatre                                1300

Starship Troopers
MegaMall Theatre                               1300

The Gladiator
MegaMall Theatre                               1300

Shrek
MegaMall Theatre                               1300

Shrek
MegaMall Theatre                               1700

(2) user purchase (extra credit)

(3) data entry

insert into movie values 
(1, 'Shrek', 'Animated', 1997, 'about an ogre and his donkey');
insert into movie values 
(2, 'Top Gun', 'Drama', 1988, 'about a boy in a plane');
insert into movie values 
(3, 'The Bourne Identity', 'Action', 2000, 'about a boy who lost his memory');
insert into movie values 
(4, 'The Gladiator', 'Action', 2000, 'about a boy in a dress');
insert into movie values 
(5, 'Starship Troopers', 'Action', 1996, 'about a boy in the future');

insert into zip values (14859, 76.5078, 42.11545);
insert into zip values (14809, 77.4641, 42.36786);
insert into zip values (14822, 77.8356, 42.42257);
insert into zip values (11212, 73.9164, 40.66384);
insert into zip values (11030, 73.6017, 40.75475);

insert into theater values 
(1, 'Loews Theatre', '95 Horace Harding', 14859);
insert into theater values 
(2, 'Sunrise Theatre', 'Massapequa LI', 11030);
insert into theater values 
(3, 'MegaMall Theatre', 'Massapequa LI', 11030);
insert into theater values 
(4, 'SouthMall Theatre', 'Southside', 11212);
insert into theater values 
(5, 'Douglaston Theatre', 'Douglaston', 14809);

insert into schedule values(1, 1, 1800, 10, 15);
insert into schedule values(1, 1, 1530, 10, 1);
insert into schedule values(1, 1, 2030, 10, 3);
insert into schedule values(1, 4, 1200, 8.25, 9);
insert into schedule values(1, 4, 1530, 8.25, 1);
insert into schedule values(2, 5, 2100, 5.50, 25);
insert into schedule values(3, 4, 1200, 8.25, 90);
insert into schedule values(4, 1, 1300, 10, 4);
insert into schedule values(5, 2, 1300, 15, 1);
insert into schedule values(5, 3, 1300, 5.50, 25);
insert into schedule values(4, 3, 1300, 5.50, 1);
insert into schedule values(1, 3, 1300, 5.50, 13);
insert into schedule values(1, 3, 1700, 5.50, 7);
insert into schedule values(1, 3, 2100, 5.50, 100);
insert into schedule values(1, 3, 1900, 5.50, 1);

insert into customer values(1, 'John', 4267091789471910, 2);
insert into customer values(2, 'Smith', 3426709179471088, 5);
insert into customer values(3, 'Blue', 1570775619133423, 3);
insert into customer values(4, 'Alyana', 9436937954609178, 2);
insert into customer values(5, 'Perez', 7267017894719106, 2);

update schedule
set price = 6
where tid = 1 and time = 1530;

==============================================
(e)

(1) views

Views can be defined for the specified user queries created in part(C1) to
prevent the user from having the ability to INSERT or DELETE anything from
the TABLE databases while giving them access to view certain attributes
where these attributes can also unfortunately be UPDATED through the VIEW 
created.

*Query #1*

CREATE VIEW query1view as
select mname, descr
from movie;

select mname
from query1view
where mname like '%The%' and descr like '%boy%';

*Query #2*

CREATE VIEW query2view as
select mname, genre, lat, lon
from movie m, schedule s, theater t, zip z
where m.mid = s.mid and s.tid = t.tid
 and t.code = z.code; 

select m1.mname
from query2view m1,query2view m2
where m1.mname = m2.mname and m1.genre = 'Action'
 and power(( ((69.1*(m2.lat-m1.lat))*(69.1*(m2.lat-m1.lat))) 
  + ((53.0*(m2.lon-m1.lon))*(53.0*(m2.lon-m1.lon))) ), 0.5) < 10;

*Query #3, Query #4, and Query #5, respectively*

CREATE VIEW query3view as
select tname, mname, time, price, lat, lon
from movie m, schedule s, theater t, zip z
where m.mid = s.mid and s.tid = t.tid
 and t.code = z.code; 

select t1.tname, t1.time
from query3view t1, query3view t2
where t1.tname != t2.tname and t1.mname = 'Shrek' and t1.price <= 10
 and power(( ((69.1*(t2.lat-t1.lat))*(69.1*(t2.lat-t1.lat))) 
  + ((53.0*(t2.lon-t1.lon))*(53.0*(t2.lon-t1.lon))) ), 0.5) < 10;

select t1.tname, t1.time
from query3view t1, query3view t2
where t1.tname != t2.tname and t1.mname = 'Shrek' 
 and t1.price <= 10 and t1.time <= 1800
 and power(( ((69.1*(t2.lat-t1.lat))*(69.1*(t2.lat-t1.lat))) 
  + ((53.0*(t2.lon-t1.lon))*(53.0*(t2.lon-t1.lon))) ), 0.5) < 10;

select t1.mname, t1.tname, t1.time
from query3view t1, query3view t2
where t1.tname = t2.tname and t1.mname = t2.mname
 and t1.time = t2.time and t1.time <= 1800
 and power(( ((69.1*(t2.lat-t1.lat))*(69.1*(t2.lat-t1.lat))) 
  + ((53.0*(t2.lon-t1.lon))*(53.0*(t2.lon-t1.lon))) ), 0.5) < 10;

Although a VIEW can be created and used for the user queries, it is more
beneficial to use STORED PROCEDURES because it does not allow the user to
have the ability to INSERT, DELETE, or UPDATE any of the VIEWS or TABLES.

Views can also be defined for the data entry personel to a limited extent.
The views will limit them from completing all attributes in a TABLE,
making them incomplete.  INSERTIONS will be limited most in this process.
UPDATES on the other hand might be the only thing a view will be useful
for since it will only give access to the data entry personal of certain
attributes and not everything.

CREATE VIEW schedulelimited as
select tid, time, price
from schedule;

update schedulelimited
set price = 1.25
where tid = 1 and time = 1530;

(2) stored procedures

*user queries*

CREATE PROCEDURE getquery1
(moviename in varchar, description in varchar, result out varchar)
as

begin
 select mname into result
 from movie
 where mname like moviename
  or descr like description;
end getquery1;
/

CREATE PROCEDURE getquery2
(moviegenre in varchar, zipcode in number, result out varchar)
as

begin
 select mname into result
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = zipcode) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < 10
 and m.genre = moviegenre;
end getquery2;
/

CREATE PROCEDURE getquery3
(moviename in varchar, cost in number, zipcode in number, radius in number, 
 resultname out varchar, resulttime out number)
as

begin
 select tname, time into resultname, resulttime
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = zipcode) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < radius
  and m.mname = moviename and s.price <= cost;
end getquery3;
/

CREATE PROCEDURE getquery4
(moviename in varchar, cost in number, zipcode in number, radius in number, 
 clock in number, resultname out varchar, resulttime out number)
as

begin
 select tname, time into resultname, resulttime
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = zipcode) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < radius
  and m.mname = moviename and s.price <= cost and s.time <= clock;
end getquery4;
/

CREATE PROCEDURE getquery5
(zipcode in number, radius in number, clock in number, 
 resultmovie out varchar, resulttheater out varchar, resulttime out number)
as

begin
 select mname, tname, time into resultmovie, resulttheater, resulttime
 from movie m, theater t, schedule s, zip z1, (select code, lon, lat
                                               from zip
                                               where code = zipcode) z2
 where s.mid = m.mid and s.tid = t.tid and t.code = z1.code
  and power(( ((69.1*(z2.lat-z1.lat))*(69.1*(z2.lat-z1.lat))) 
   + ((53.0*(z2.lon-z1.lon))*(53.0*(z2.lon-z1.lon))) ), 0.5) < radius
  and s.time <= clock;
end getquery5;
/

*data entry*

CREATE PROCEDURE addmovietuple
(mid in number, mname in varchar, genre in varchar, year in number, descr in varchar) 
as

begin
 insert into movie values (mid, mname, genre, year, descr);
end addmovietuple;
/

CREATE PROCEDURE addtheatertuple
(tid in number, tname in varchar, address in varchar, code in number) 
as

begin
 insert into theater values (tid, tname, address, code);
end addtheatertuple;
/

CREATE PROCEDURE addscheduletuple
(mid in number, tid in number, time in number, price in number, num_seats in number) 
as

begin
 insert into schedule values (mid, tid, time, price, num_seats);
end addscheduletuple;
/

CREATE PROCEDURE updatescheduleprice
(movieid in number, theaterid in number, clock in number, cost in number) 
as

begin
 update schedule
 set price = cost
 where mid = movieid and tid = theaterid and time = clock;
end updatescheduleprice;
/