Re: Functions/Triggers Newbie help

From: Scotty <sgeba_at_mindspring.com>
Date: Thu, 16 Aug 2001 19:23:29 -0400
Message-ID: <9lhkcn$rn7$1@slb7.atl.mindspring.net>

sorry... here is DB....

Hi, I am really new to Oracle and I am trying to get a few things done that I can't really do... I was wondering if anyone could help with some code... any kinda help would be appreciated, I am just having difficulty with Oracle and I have already been giving things to do that I can't.... attached are my tables and inserted data and what needs to be done, if anyone can help any bit, it would be greatly appreciated.

Create Table checkouts (
user_id number(3),
movie_id number(5),
dout date,
ddue date,
din date,
late char(1),
PRIMARY KEY (user_id, movie_id),
FOREIGN KEY (user_id) REFERENCES members(user_id) );

create table members (

user_id number(3) PRIMARY KEY,
fname varchar2(15),
lname varchar2(20),

address varchar2(50),
state char(2),
zip number(5),
phone number(10),
birthdate date,
joinage number(2) CHECK (age >= 16),
email varchar2(30),
joindate date
);

Create Table movies (
movie_id number(5) PRIMARY KEY,
mname varchar2(60),
mrating varchar2(5) CHECK (mrating != 'NC-17'), mformat varchar2(3)
);

Create Table delinquent (
user_id number(3),
hold char(1),
balancedue number(3,2),
PRIMARY KEY (user_id),
FOREIGN KEY (user_id) REFERENCES members(user_id) );

Create Table memrewards (
user_id number(3),
userrental number(2),
freeno number(2),
PRIMARY KEY (user_id),
FOREIGN KEY (user_id) REFERENCES members(user_id) );

Create Table renthistory (
user_id number(3)
movie_id number(5),
rentdate date,
PRIMARY KEY(user_id),
FOREIGN KEY (user_id) REFERENCES members(user_id), FOREIGN KEY (movie_id) REFERENCES movies(movie_id) );

INSERT INTO movies VALUES (12341, 'MATRIX, THE', 'R', 'DVD');
INSERT INTO movies VALUES (12342, 'BIG LEBOWSKI, THE', 'R', 'DVD');
INSERT INTO movies VALUES (12343, 'OFFICE SPACE', 'R', 'VHS');
INSERT INTO movies VALUES (12344, 'STAR WARS: EPISODE ONE', 'PG-13', 'VHS');
INSERT INTO movies VALUES (12345, 'HISTORY OF THE WORLD PART ONE', 'R',

'VHS');

INSERT INTO movies VALUES (12346, 'SEARCH FOR THE HOLY GRAIL, THE', 'R',
'VHS');

INSERT INTO movies VALUES (12347, 'EVENT HORIZON', 'R', 'DVD');
INSERT INTO movies VALUES (12348, 'FORREST GUMP', 'R', 'DVD');
INSERT INTO movies VALUES (12349, 'SWINGERS', 'R', 'DVD');
INSERT INTO movies VALUES (12340, 'ANIMAL HOUSE', 'R', 'VHS');
INSERT INTO movies VALUES (12351, 'PULP FICITON', 'R', 'DVD');
INSERT INTO movies VALUES (12352, 'RESERVOIR DOGS', 'R', 'DVD');
INSERT INTO movies VALUES (12353, 'CADDYSHACK', 'R', 'VHS');
INSERT INTO movies VALUES (12354, 'AMERICAN PIE', 'R', 'DVD');
INSERT INTO movies VALUES (12355, 'ROAD TRIP', 'R', 'DVD');
INSERT INTO movies VALUES (12356, 'AMERICAN PRESIDENT', 'PG-13', 'DVD');
INSERT INTO movies VALUES (12357, 'DUNE', 'PG-13', 'DVD');
INSERT INTO movies VALUES (12358, 'MAJOR LEAGUE', 'R', 'VHS');
INSERT INTO movies VALUES (12359, 'SPACEBALLS', 'R', 'VHS');
INSERT INTO movies VALUES (12350, 'SIXTH SENSE', 'R', 'DVD');
INSERT INTO movies VALUES (12361, 'CASTAWAY', 'R', 'DVD');
INSERT INTO movies VALUES (12362, 'AMERICAN HISTORY X', 'R', 'DVD');
INSERT INTO movies VALUES (12363, 'AUSTIN POWERS', 'PG-13', 'DVD');
INSERT INTO movies VALUES (12364, 'AUSTIN POWERS II', 'PG-13', 'DVD');
INSERT INTO movies VALUES (12365, 'TOY STORY', 'PG', 'DVD');
INSERT INTO movies VALUES (12366, 'TOY STORY II', 'PG', 'DVD');
INSERT INTO movies VALUES (12367, 'EVER AFTER', 'PG-13', 'DVD');
INSERT INTO movies VALUES (12368, 'BRAVEHEART', 'R', 'DVD');
INSERT INTO movies VALUES (12369, 'Matrix, THE', 'R', 'DVD');
INSERT INTO movies VALUES (12360, 'PATRIOT, THE', 'R', 'DVD');
INSERT INTO movies VALUES (12371, 'PATRIOT, THE', 'R', 'DVD');
INSERT INTO movies VALUES (12372, 'U-571', 'R', 'DVD');
INSERT INTO movies VALUES (12373, 'BRAVEHEART', 'R', 'DVD');

INSERT INTO members VALUES (111, 'JOHN', 'JACOBSON', '123 ANY ST', 'PA', 11128, 2154445421, TO_DATE('31-JAN-1971', 'DD-MON-YYYY'),,
'JJACOBSON_at_AOL.COM', TO_DATE('12-JUN-1994', 'DD-MON-YYYY'));

INSERT INTO members VALUES (112, 'BRIAN', 'MASTERS', '14 BROAD ST', 'PA',
12114, 2154751452, TO_DATE('30-APR-1964', 'DD-MON-YYYY'),,

'BMASTERS_at_YAHOO.COM', TO_DATE('24-MAR-1999', 'DD-MON-YYYY'));
INSERT INTO members VALUES (113, 'MARY', 'LAMB', '545 LITTLE ST', 'PA', 34574, 2157984564, TO_DATE('23-DEC-1952', 'DD-MON-YYYY'),,
'MLAMBY_at_EARTHLINK.COM', TO_DATE('13-DEC-2000', 'DD-MON-YYYY'));
INSERT INTO members VALUES (114, 'HELEN', 'CARTER', '2745 MARKET ST', 'PA', 11141, 2154789454, TO_DATE('19-AUG-1982', 'DD-MON-YYYY'),, 'HELENC_at_ATT.COM', TO_DATE('02-JAN-2001', 'DD-MON-YYYY'));
INSERT INTO members VALUES (115, 'TOM', 'JONES', '3492 RACE ST', 'PA', 19124, 2154875459, TO_DATE('09-NOV-1984', 'DD-MON-YYYY'),,
'TJONES_at_COVAD.NET', TO_DATE('15-FEB-1999', 'DD-MON-YYYY'));
INSERT INTO members VALUES (116, 'BILL', 'LUMBERG', '213 WALNUT LN', 'PA', 10911, 6105478745, TO_DATE('27-SEP-1979', 'DD-MON-YYYY'),, 'BIGBIL_at_AOL.COM', TO_DATE('27-SEP-1998', 'DD-MON-YYYY'));
INSERT INTO members VALUES (117, 'MICHEAL', 'BOLTON', '6454 RIDGE AVE',
'PA', 19111, 2154796541, TO_DATE('01-MAR-1980', 'DD-MON-YYYY'),,
'MBOLTONX_at_YAHOO.COM', TO_DATE('01-NOV-1998', 'DD-MON-YYYY'));
INSERT INTO members VALUES (118, 'JAMES', 'JOYCE', '6321 HENRY AVE', 'PA', 19111, 2153546987, TO_DATE('15-FEB-1961', 'DD-MON-YYYY'),,
'IRISHJOYCE_at_EXCITE.COM', TO_DATE('09-JUL-1997', 'DD-MON-YYYY'));
INSERT INTO members VALUES (119, 'AMANDA', 'BOYSE', '2111 COTTMAN AVE',
'PA', 19111, 2157896545, TO_DATE('21-JUN-1969', 'DD-MON-YYYY'),,
'BOYS4ME_at_AOL.COM', TO_DATE('13-NOV-1999', 'DD-MON-YYYY'));
INSERT INTO members VALUES (120, 'JULIUS', 'HENRAVE', '3241 BRIDGE ST',
'PA', 19111, 2678759874, TO_DATE('12-JUL-1977', 'DD-MON-YYYY'),,
'JHENRY_at_AOL.COM', TO_DATE('20-DEC-1997', 'DD-MON-YYYY'));
What I am working on:

-create checkout function (3 days to rent), member checks out movie, goes into checkout table, as well as populates checked out and return dates -if late in checkouts then member isn't allowed to rent... put in delinquent table.

-clear from delinquent after paid
-if age < 17 no rent R
-e-mail when late (if can't do, then just a message saying so and so is

late.)

-create balance due function $1 for every day late
-insert movie insertion trigger (LD no longer accepted, BETA either)
-use birthdate to calculate age at join
-member rewards (add one to userrental in memrewards table when one is

rented, if at 10 issue free movie... mark a 1 in freeno) -member history (history of all rentals... userid, movieid) -new customer... free intro rental (put in memrewards table with a 1 in freeno)
-on rental check memrewards... take 1 off of freeno when free rental is used -on delete of member check that no other info is in any other tables. Received on Thu Aug 16 2001 - 18:23:29 CDT