George David.
0030528
Project1
12/3/04
(a)
ER schema on back.
(b)
CREATE TABLE movies (
mid number,
title varchar2(20),
genre varchar2(20),
release_yr number,
description varchar2(40),
primary key (mid));
CREATE TABLE zip_codes (
theater_zipcode number(5),
longitude number,
latitude number,
primary key (theater_zipcode));
CREATE TABLE theater (
tid number,
theater_name varchar2(20),
theater_street varchar2(20),
theater_city varchar2(20),
theater_zipcode number(5),
primary key (tid),
foreign key (theater_zipcode) references zip_codes);
CREATE TABLE schedule (
sid number,
mid number,
tid number,
showtime number(4),
ticket_price number CONSTRAINT price_positive check(ticket_price >= 0),
seats_taken number CONSTRAINT seats_positive check(seats_taken >= 0),
primary key (sid),
foreign key (mid) references movies,
foreign key (tid) references theater);
CREATE TABLE customers (
custid number,
first_name char(20),
last_name char(20),
credit_card number,
price_per_seat number,
number_of_seats number CONSTRAINT seats_ordered check(number_of_seats >= 0),
sid number,
primary key (custid),
foreign key (sid) references schedule);
(c).1
(d)
(i)ALL MOVIES WITH TITLE CONTAINING TEXT "RELOADED" AND DESCRIPTION WITH "OF"
SELECT title FROM movies
WHERE UPPER(title) LIKE '%RELOADED%' OR UPPER(description) LIKE '%OF%'
/
:OUTPUT:
TITLE
----------------------------------------
Matrix Reloaded
Alexander
(ii)ALL GENRE:ACTION MOVIES PLAYING IN ZIP:10004
SELECT title
FROM movies m, theater t, schedule s
WHERE theater_zipcode=10004 AND UPPER(genre) LIKE '%ACTION%'
AND m.mid=s.mid AND t.tid=s.tid
GROUP BY title
/
:OUTPUT:
TITLE
----------------------------------------
Alexander
(iii)ALL THEATERS PLAYING ALEXANDER IN A 10 MILE RADIUS OF ZIP:10004
SELECT theater_name, s.showtime
FROM movies m, theater t, schedule s, zip_codes z1,
(SELECT theater_zipcode,longitude,latitude
FROM zip_codes
WHERE theater_zipcode = 10004) z2
WHERE UPPER(m.title) LIKE '%ALEXANDER%' AND m.mid=s.mid
AND t.tid=s.tid AND t.theater_zipcode=z1.theater_zipcode
AND power(( ((69.1*(z2.latitude-z1.latitude))*(69.1*(z2.latitude-z1.latitude)))
+ ((53.0*(z2.longitude-z1.longitude))*
(53.0*(z2.longitude-z1.longitude))) ), 0.5) < 10
GROUP BY theater_name,s.showtime
/
:OUTPUT:
THEATER_NAME SHOWTIME
-------------------- ----------
Clearview 1400
(iv)ALL MOVIES IN A 10 MILE RADIUS OF 10004 WITH TICKETS LESS THAN 10 DOLLARS
SELECT m.title, t.theater_name,s.showtime, s.ticket_price
FROM movies m, theater t, schedule s, zip_codes z1,
(SELECT theater_zipcode,longitude,latitude
FROM zip_codes
WHERE theater_zipcode = 10004) z2
WHERE s.ticket_price < 10 AND m.mid=s.mid AND t.tid=s.tid
AND t.theater_zipcode=z1.theater_zipcode
AND power(( ((69.1*(z2.latitude-z1.latitude))*(69.1*(z2.latitude-z1.latitude)))
+ ((53.0*(z2.longitude-z1.longitude))*(53.0*(z2.longitude-z1.longitude))) ), 0.5) < 10
GROUP BY m.title, t.theater_name,s.showtime, s.ticket_price
/
:OUTPUT:
TITLE THEATER_NAME SHOWTIME TICKET_PRICE
-------------------- -------------------- ---------- ------------
Darkness National Amusement 1700 9
Alexander Clearview 1400 5.25
National Treasure United Artists 1700 5.25
National Treasure United Artists 2000 9
(c).2
CREATE TRIGGER INS_CUSTOMER
BEFORE
INSERT ON CUSTOMERS
BEGIN
SELECT s.seats_taken, c.number_of_seats
FROM schedule s, customers c
WHERE s.sid = c.sid;
IF(seats_taken + number_of_seats > 10) THEN
raise_application_error(-10,'NUMBER OF SEATS NOT AVAILABLE');
ELSE UPDATE schedule SET seats_taken=seats_taken+number_of_seats
WHERE s.sid = c.sid;
END IF;
END;
(c).3
INSERT INTO zip_codes VALUES (10001, 73.9963, 40.75042);
INSERT INTO zip_codes VALUES (10002, 73.9864, 40.71875);
INSERT INTO zip_codes VALUES (10003, 73.9885, 40.73022);
INSERT INTO zip_codes VALUES (10004, 74.0252, 40.69635);
INSERT INTO zip_codes VALUES (14889, 76.5850, 42.21022);
INSERT INTO theater VALUES (1, 'United Artists', 'Jay St', 'Brooklyn', 10001);
INSERT INTO theater VALUES (2, 'National Amusement', '112th st', 'Jamaica', 10002);
INSERT INTO theater VALUES (3, 'Stadium 12', '7th ave', 'Brooklyn', 10003);
INSERT INTO theater VALUES (4, 'Clearview', 'Broadway', 'New York', 10004);
INSERT INTO theater VALUES (5, 'Someplace', 'Somewhere', 'Van Etten', 14889);
INSERT INTO movies VALUES (1, 'National Treasure', 'Adventure', 2004, 'Try to find hidden treasure');
INSERT INTO movies VALUES (2, 'Matrix Reloaded', 'Action', 2003, 'Final part of trilogy');
INSERT INTO movies VALUES (3, 'Alexander', 'Action', 2004, 'Story of alexander the great');
INSERT INTO movies VALUES (4, 'Darkness', 'Horror', 2004, 'Something about a haunted house');
INSERT INTO schedule VALUES (1, 1, 1, 1700, 5.25, 0);
INSERT INTO schedule VALUES (2, 1, 1, 2000, 9.00, 0);
INSERT INTO schedule VALUES (3, 1, 2, 1800, 10.00, 0);
INSERT INTO schedule VALUES (4, 2, 3, 1930, 10.25, 0);
INSERT INTO schedule VALUES (5, 3, 4, 1400, 5.25, 0);
INSERT INTO schedule VALUES (6, 3, 5, 1645, 8.25, 0);
INSERT INTO schedule VALUES (7, 4, 2, 1700, 9.00, 0);
INSERT INTO schedule VALUES (8, 2, 5, 1800, 9.00, 0);
INSERT INTO schedule VALUES (9, 4, 5, 1200, 5.25, 0);
INSERT INTO customers VALUES (1, 'George', 'David', 12345678, 5.25, 4, 1);
UPDATE schedule
SET ticket_price=12.00
WHERE sid=8;
SQL> SELECT * FROM schedule;
SID MID TID SHOWTIME TICKET_PRICE SEATS_TAKEN
------- ---------- ---------- ---------- ------------ -----------
1 1 1 1700 5.25 0
2 1 1 2000 9 0
3 1 2 1800 10 0
4 2 3 1930 10.25 0
5 3 4 1400 5.25 0
6 3 5 1645 8.25 0
7 4 2 1700 9 0
8 2 5 1800 12 0
9 4 5 1200 5.25 0
(d). this part is below each of the SQL commands in part (c).1
(e).1
Creating views here are not to benefical since alot of the data needs to be filled in. If a view is created
the remaining data in that table will be left empty, and that could lead to problems. The best solution is to
use stored procedures so that the data can be manipulated based on variables that the user chooses.
The only benefit to the views would be to prevent certain data from being seen, but in this case the only people
who would be able to see the data are the data entry people, and they would most likely need access to all the fields
of the tables, except maybe the customer table. And if they needed to access the customer table, they most likely wouldnt
need to see the credit card info.
CREATE VIEW SECURE_CUSTOMER AS
SELECT custid, first_name, last_name, sid
FROM customers;
Besides protecting data from other database users, procedures and functions are more benefical.
(e).2
SEARCH FOR A MOVIE NAME
CREATE OR REPLACE PROCEDURE SEARCH_MOVIE(moviename in varchar, result out varchar) as
BEGIN
SELECT title INTO result
FROM movies
WHERE title LIKE moviename;
END SEARCH_MOVIE;
/
SEARCH BY GENRE
CREATE OR REPLACE PROCEDURE SEARCH_GENRE(genre in varchar, result out varchar) as
BEGIN
SELECT title INTO result
FROM movies m, theater t, schedule s
WHERE genre LIKE genre
AND m.mid=s.mid AND t.tid=s.tid
GROUP BY title;
END SEARCH_GENRE;
/
ALL THEATERS PLAYING ?MOVIE? IN A ? MILE RADIUS OF ZIP:?
CREATE OR REPLACE PROCEDURE SEARCH_MOVIES_BY_ZIP(movie in varchar, zip in number, distance in number,
result out varchar, timings out number) as
BEGIN
SELECT theater_name into result, s.showtime into timings
FROM movies m, theater t, schedule s, zip_codes z1,
(SELECT theater_zipcode,longitude,latitude
FROM zip_codes
WHERE theater_zipcode = zip) z2
WHERE m.title LIKE movie AND m.mid=s.mid
AND t.tid=s.tid AND t.theater_zipcode=z1.theater_zipcode
AND power(( ((69.1*(z2.latitude-z1.latitude))*(69.1*(z2.latitude-z1.latitude)))
+ ((53.0*(z2.longitude-z1.longitude))*
(53.0*(z2.longitude-z1.longitude))) ), 0.5) < distance
GROUP BY theater_name,s.showtime;
END SEARCH_MOVIES_BY_ZIP;
/
ADDING MOVIE
CREATE OR REPLACE PROCEDURE ADD_MOVIE(mid in number, title in varchar, genre in varchar,
yr in number, descr in varchar) as
begin
insert into movies values (mid, title, genre, yr, descr);
end ADD_MOVIE;
/
DELETE MOVIE
CREATE OR REPLACE PROCEDURE DELETE_MOVIE(movieid in number) as
begin
delete FROM movies
where mid in (movieid);
end DELETE_MOVIE;
/
ADDING THEATER
CREATE OR REPLACE PROCEDURE ADD_THEATER(tid in number, theater_name in varchar, theater_street in varchar,
theater_city in varchar, theater_zipcode in number) as
begin
insert into movies values (tid, theater_name, theater_street, theater_city, theater_zipcode);
end ADD_THEATER;
/
DELETE THEATER
CREATE OR REPLACE PROCEDURE DELETE_THEATER(theaterid in number) as
begin
delete FROM theater
where tid in (theaterid);
end DELETE_THEATER;
/
ADDING SCHEDULE
CREATE OR REPLACE PROCEDURE ADD_SCHEDULE(sid in number, mid in number, tid in number,
showtime in number, ticketprice in number) as
begin
insert into schedule values (sid, mid, tid, showtime, ticketprice, 0);
end ADD_SCHEDULE;
/
DELETE SCHEDULE
CREATE OR REPLACE PROCEDURE DELETE_SCHEDULE(scheduleid in number) as
begin
delete FROM schedule
where sid in (scheduleid);
end DELETE_SCHEDULE;
/
UPDATE SCHEDULE
CREATE OR REPLACE PROCEDURE ADD_SCHEDULE(sid in number, mid in number, tid in number,
showtime in number, ticketprice in number, seats_taken in number) as
begin
update schedule s
set s.mid=mid, s.tid=tid, s.showtime=showtime, s.ticket_price=ticketprice, s.seats_taken=seats_taken
where s.sid=sid;
end ADD_SCHEDULE;
/