CREATE TABLE member (mmb_numb NUMBER(4), mmb_fname VARCHAR2(16) CONSTRAINT member_mmb_fname_nn NOT NULL, mmb_lname VARCHAR2(16) CONSTRAINT member_mmb_lname_nn NOT NULL, mmb_addr VARCHAR2(12) CONSTRAINT member_mmb_addr_nn NOT NULL, mmb_city VARCHAR2(10) CONSTRAINT member_mmb_city_nn NOT NULL, join_date DATE CONSTRAINT member_join_date_nn NOT NULL, CONSTRAINT member_mmb_numb_pk PRIMARY KEY (mmb_numb)); CREATE TABLE star (star_numb NUMBER(4), star_fname VARCHAR2(16), star_lname VARCHAR2(16), brth_place VARCHAR2(25), star_born NUMBER(4), star_died NUMBER(4), CONSTRAINT star_star_numb_pk PRIMARY KEY (star_numb)); CREATE TABLE director (dir_numb NUMBER(3), dir_fname VARCHAR2(16) CONSTRAINT director_dir_fname_nn NOT NULL, dir_lname VARCHAR2(16) CONSTRAINT director_dir_lname_nn NOT NULL, dir_born NUMBER(4) CONSTRAINT director_dir_born_nn NOT NULL, dir_died NUMBER(4), CONSTRAINT director_dir_numb_pk PRIMARY KEY (dir_numb)); CREATE TABLE movie CREATE SEQUENCE mmb_numb_sequence START WITH 11; CREATE SEQUENCE star_numb_sequence START WITH 28; CREATE SEQUENCE dir_numb_sequence START WITH 8; CREATE SEQUENCE mv_numb_sequence START WITH 25; CREATE SEQUENCE tp_numb_sequence START WITH 28; CREATE SEQUENCE res_numb_sequence START WITH 1; alter table movie add num_in_stock NUMBER(4); alter table movie add available varchar2(16)); CREATE OR REPLACE TRIGGER insert_member BEFORE INSERT ON member FOR EACH ROW BEGIN SELECT mmb_numb_sequence.NEXTVAL INTO :NEW.mmb_numb FROM dual; END; CREATE OR REPLACE TRIGGER insert_star BEFORE INSERT ON star FOR EACH ROW BEGIN SELECT star_numb_sequence.NEXTVAL INTO :NEW.star_numb FROM dual; END; CREATE OR REPLACE TRIGGER insert_director BEFORE INSERT ON director FOR EACH ROW BEGIN SELECT dir_numb_sequence.NEXTVAL INTO :NEW.dir_numb FROM dual; END; CREATE OR REPLACE TRIGGER insert_movie BEFORE INSERT ON movie FOR EACH ROW BEGIN SELECT mv_numb_sequence.NEXTVAL INTO :NEW.mv_numb FROM dual; END; CREATE OR REPLACE TRIGGER insert_tape BEFORE INSERT ON tape FOR EACH ROW BEGIN SELECT tp_numb_sequence.NEXTVAL INTO :NEW.tp_numb FROM dual; END; alter table movie add num_in_stock NUMBER(4); alter table movie add available varchar2(16)); (mv_numb NUMBER(4), mv_title VARCHAR2(30) CONSTRAINT movie_mv_title_nn NOT NULL, yr_made NUMBER(4) CONSTRAINT movie_yr_made_nn NOT NULL, mv_type VARCHAR2(6), crit NUMBER(1), mpaa VARCHAR2(2), noms NUMBER(1), awrd NUMBER(1), dir_numb NUMBER(3), CONSTRAINT movie_mv_numb_pk PRIMARY KEY (mv_numb), CONSTRAINT movie_dir_numb_fk FOREIGN KEY (dir_numb) REFERENCES director (dir_numb)); CREATE TABLE movstar (mv_numb NUMBER(4), star_numb NUMBER(4), CONSTRAINT movstar_mv_numb_star_numb_pk PRIMARY KEY (mv_numb, star_numb), CONSTRAINT movstar_mv_numb_fk FOREIGN KEY (mv_numb) REFERENCES movie (mv_numb), CONSTRAINT movstar_star_numb_fk FOREIGN KEY (star_numb) REFERENCES star (star_numb)); CREATE TABLE tape (tp_numb NUMBER(4), mv_numb NUMBER(4) CONSTRAINT tape_mv_numb_nn NOT NULL, pur_date DATE CONSTRAINT tape_pur_date_nn NOT NULL, CONSTRAINT tape_tp_numb_pk PRIMARY KEY (tp_numb), CONSTRAINT tape_mv_numb_fk FOREIGN KEY (mv_numb) REFERENCES movie (mv_numb)); CREATE TABLE rental (mmb_numb NUMBER(4), tp_numb NUMBER(4), out_date DATE, rtn_date DATE, rtn_status CHAR(1) DEFAULT 'n' CONSTRAINT rental_rtn_status_cc CHECK ((rtn_status = 'y') OR (rtn_status = 'Y') OR (rtn_status = 'n') OR (rtn_status = 'N')), CONSTRAINT rental_mmb_numb_numb_date_pk PRIMARY KEY (mmb_numb, tp_numb, out_date), --CONSTRAINT rental_mmb_numb_fk FOREIGN KEEY (mmb_numb) REFERENCES member (mmb_numb), CONSTRAINT rental_tp_numb_fk FOREIGN KEY (tp_numb) REFERENCES tape (tp_numb)); CREATE SEQUENCE mmb_numb_sequence START WITH 11; CREATE SEQUENCE star_numb_sequence START WTIH 28; CREATE SEQUENCE dir_numb_sequence START WITH 8; CREATE SEQUENCE mv_numb_sequence START WITH 25; CREATE SEQUENCE tp_numb_sequence START WITH 28; --insert member information into member taable INSERT INTO member VALUES (1, 'Donna', 'Allen', '21 Wilson', 'Carson', TO_DATE('05/25/95', 'MM/DD/YY')); INSERT INTO member VALUES (2, 'Mark', 'Petereon', '215 Raymond', 'Cedar', TO_DATE('02/20/94', 'MM/DD/YY')); INSERT INTO member VALUES (3, 'Mi~uel', 'Sanchez', '47 Chipwood', 'Mantin', TO_DATE('06/14/94', 'MM/DD/YY')); INSERT INTO member VALUES (4, 'Thanh', 'Tran', '108 College', 'Carson' , TO_DATE('07/03/95', 'MM/DD/YY')); INSERT INTO member VALUES (5, 'Terry', 'Roberte', '602 Bridge', 'Hudeon', TO_DATE('11/16/94', 'MM/DD/YY')); INSERT INTO member VALUES (6, 'Greg', 'MacDonald', '19 Oak', 'Carson', TO_DATE('01/29/95', 'MM/DD/YY')); INSERT INTO member VALUES (7, 'Neal', 'Vanderjagt', '12 Bishop', 'Mantin', TO_DATE('08/11/94', 'MM/DD/YY')); INSERT INTO member VALUES (8, 'John', 'Shisppers', '208 Grayton', 'Cedar', TO_DATE('09/02/95', 'MM/DD/YY')); INSERT INTO member VALUES (9, 'Trudy', 'Franklin', '103 Bedford', 'Brook', TO_DATE('12/13/94', 'MM/DD/YY')); INSERT INTO member VALUES (10, 'Shelly', 'Stein', '82 Harcourt', 'Hudson', TO_DATE('05/25/95', 'MM/DD/YY')); --insert directors into director table INSERT INTO director VALUES (1, 'Woody', 'Allen', 1935, null); INSERT INTO director VALUES (2, 'Alfred', 'Hitchcock', 1899, 1980); INSERT INTO director VALUES (3, 'Cecil B.', 'De Mille', 1881, 1959); INSERT INTO director VALUES (4, 'Stanley', 'Kramer', 1913, NULL); INSERT INTO director VALUES (5, 'Stanley', 'Kubrick', 1913, NULL); INSERT INTO director VALUES (6, 'Otto', 'Preming', 1906, NULL); INSERT INTO director VALUES (7, 'John', 'Ford', 1859, 1973); --insert stars into the star table INSERT INTO star VALUES (1, 'Woody', 'Allen', 'New York', 1935, NULL); INSERT INTO star VALUES (2, 'Diane', 'Keaton', 'Los Angeles', 1946, NULL); INSERT INTO star VALUES (3, 'Peter', 'Sellers', 'Southsea, Eng.', 1925, 1980); INSERT INTO star VALUES (4, 'George C.', 'Scott', 'Wlse, Va.', 1927, NULL); INSERT INTO star VALUES (5, 'Malcolm', 'McDowell', 'Leeds, Eng.', 1943, NULL); INSERT INTO star VALUES (6, 'Cary', 'Grant', 'Bristol, Eng.', 1904, 1986); INSERT INTO star VALUES (7, 'Eva Marie', 'Salnt', 'Newark, N.J.', 1929, NULL); INSERT INTO star VALUES (8, 'James', 'Stewart', 'Indiana, Pa.', 1908, NULL); INSERT INTO star VALUES (9, 'Antony', 'Perkins', 'New York', 1932, NULL); INSERT INTO star VALUES (10, 'Janet', 'Leigh', 'Merced, Cal.', 1927, NULL); INSERT INTO star VALUES (11, 'Rod', 'Taylor', 'Sydney, Australia', 1930, NULL); INSERT INTO star VALUES (12, 'Tippi', 'Hedren', 'Lafayette, Minn.', 1935, NULL); INSERT INTO star VALUES (13, 'Victor', 'Mature', 'Louisville, Ky.', 1916, NULL); INSERT INTO star VALUES (14, 'Spencer', 'Tracy', 'Milwaukee', 1900, 1967); INSERT INTO star VALUES (15, 'Katharine', 'Hepburn', 'Hartford', 1909, NULL); INSERT INTO star VALUES (16, 'Reir', 'Dullea', 'Cleveland', 1939, NULL); INSERT INTO star VALUES (17, 'Kim', 'Novak', 'Chicago', 1933, NULL); INSERT INTO star VALUES (18, 'Frank', 'Sinatra', 'Hoboken, N.J.', 1915, 1999); INSERT INTO star VALUES (19, 'Fredric', 'March', 'Racine, Wis.', 1897, 1975); INSERT INTO star VALUES (20, 'Dana', 'Andrews', 'Collins, Miss.', 1912, NULL); INSERT INTO star VALUES (21, 'Charlton', 'Heston', 'Evanston, Ill.', 1923, NULL); INSERT INTO star VALUES (22, 'Maggie', 'McNamara', 'New York', 1928, 1978); INSERT INTO star VALUES (23, 'David', 'Niven', 'Rirriemuir, Scot.', 1910, 1983); INSERT INTO star VALUES (24, 'John', 'Wayne', 'Winterset, Iowa', 1907, 1979); INSERT INTO star VALUES (25, 'Clark', 'Gable', 'Cadiz, O.', 1901, 1960); INSERT INTO star VALUES (26, 'Grace', 'Kelly', 'Philadelphia', 1929, 1982); INSERT INTO star VALUES (27, 'Henry', 'Fonda', 'Grand Island, Neb.', 1905, 1982); -- insert movie records into the movie tabble INSERT INTO movie VALUES (1, 'Annie Hall', 1977, 'COMEDY', 4, 'PG', 5, 4, 1); INSERT INTO movie VALUES (2, 'Dr. Stranglove', 1964, 'COMEDY', 4, 'PG', 4, 0, 5); INSERT INTO movie VALUES (3, 'Clockwork Orange', 1971, 'SCI FI', 4, 'R', 3, 0, 5); INSERT INTO movie VALUES (4, 'North by Northwest', 1959, 'SUSPEN', 4, 'PG', 1, 0, 2); INSERT INTO movie VALUES (5, 'Rope', 1948, 'SUSPEN', 3, 'NR', 0, 0, 2); INSERT INTO movie VALUES (6, 'Psycho', 1960, 'HORROR', 4, 'PG', 3, 0, 2); INSERT INTO movie VALUES (7, 'Interiors', 1978, 'DRAMA', 3, 'PG', 3, 0, 1); INSERT INTO movie VALUES (8, 'The Birds', 1963, 'HORROR', 4, 'NR', 0, 0, 2); INSERT INTO movie VALUES (9, 'Samson and Delilah', 1949, 'RELIGI', 2, 'NR', 1, 0, 3); INSERT INTO movie VALUES (10, 'Guess who''s Coming to Dinner', 1967, 'COMEDY', 3, 'NR', 6, 2, 4); INSERT INTO movie VALUES (11, 'Manhattan', 1979, 'COMEDY', 4, 'R', 2, 0, 1); INSERT INTO movie VALUES (12, 'Vertigo', 1958, 'SUSPEN', 4, 'NR', 0, 0, 2); INSERT INTO movie VALUES (13, 'Judgement at Nuremberg', 1961, 'DRAMA', 3, 'NR', 6, 2, 4); INSERT INTO movie VALUES (14, '2001', 1968, 'SCI FI', 4, 'G', 2, 0, 5); INSERT INTO movie VALUES (15, 'the Man With the Golden Arm', 1955, 'DRAMA', 3, 'NR', 1, 0, 6); INSERT INTO movie VALUES (16, 'Anatomy of a Murder', 1960, 'SUSPEN', 4, 'NR', 4, 0, 6); INSERT INTO movie VALUES (17, 'Inherit the Wind', 1960, 'DRAMA', 4, 'NR', 2, 0, 4); INSERT INTO movie VALUES (18, 'Laura', 1944, 'SUSPEN', 4, 'NR', 3, 1, 6); INSERT INTO movie VALUES (19, 'The Ten Commandments', 1956, 'RELIGI', 3, 'NR', 1, 0, 3); INSERT INTO movie VALUES (20, 'The Moon is Blue', 1953, 'COMEDY', 2, 'NR', 1, 0, 6); INSERT INTO movie VALUES (21, 'Stagecoach', 1939, 'WESTER', 4, 'NR', 3, 1, 7); INSERT INTO movie VALUES (22, 'Rear Window', 1954, 'SUSPEN', 4, 'NR', 1, 0, 2); INSERT INTO movie VALUES (23, 'Mogambo', 1953, 'WESTER', 3, 'NR', 2, 0, 7); INSERT INTO movie VALUES (24, 'Grapes of Wrath', 1940, 'DRAMA', 4, 'NR', 4, 2, 7); -- insert records into movstar table INSERT INTO movstar VALUES ( 1, 1); INSERT INTO movstar VALUES ( 1, 2); INSERT INTO movstar VALUES ( 2, 3); INSERT INTO movstar VALUES ( 2, 4); INSERT INTO movstar VALUES ( 3, 5); INSERT INTO movstar VALUES ( 4, 6); INSERT INTO movstar VALUES ( 4, 7); INSERT INTO movstar VALUES ( 5, 8); INSERT INTO movstar VALUES ( 6, 9); INSERT INTO movstar VALUES ( 6, 10); INSERT INTO movstar VALUES ( 7, 12); INSERT INTO movstar VALUES ( 8, 11); INSERT INTO movstar VALUES ( 8, 12); INSERT INTO movstar VALUES ( 9, 13); INSERT INTO movstar VALUES ( 10, 14); INSERT INTO movstar VALUES ( 10, 15); INSERT INTO movstar VALUES ( 11, 1); INSERT INTO movstar VALUES ( 11, 2); INSERT INTO movstar VALUES ( 12, 8); INSERT INTO movstar VALUES ( 12, 17); INSERT INTO movstar VALUES ( 13, 14); INSERT INTO movstar VALUES ( 14, 16); INSERT INTO movstar VALUES ( 15, 17); INSERT INTO movstar VALUES ( 15, 18); INSERT INTO movstar VALUES ( 16, 8); INSERT INTO movstar VALUES ( 17, 14); INSERT INTO movstar VALUES ( 17, 19); INSERT INTO movstar VALUES ( 18, 20); INSERT INTO movstar VALUES ( 19, 21); INSERT INTO movstar VALUES ( 20, 22); INSERT INTO movstar VALUES ( 20, 23); INSERT INTO movstar VALUES ( 21, 24); INSERT INTO movstar VALUES ( 22, 8); INSERT INTO movstar VALUES ( 22, 26); INSERT INTO movstar VALUES ( 23, 25); INSERT INTO movstar VALUES ( 23, 26); INSERT INTO movstar VALUES ( 24, 27); --insert values into the tape table INSERT INTO tape VALUES ( 1, 1, TO_DATE('04/26/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 2, 2, TO_DATE('04/26/99', 'MM/DD/YY')); INSERT INTO tape VALUES ( 3, 3, TO_DATE('04/26/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 4, 4, TO_DATE('04/28/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 5, 5, TO_DATE('05/12/98', 'MM/DD/YY')); INSERT INTO tape VALUES ( 6, 6, TO_DATE('05/12/98', 'MM/DD/YY')); INSERT INTO tape VALUES ( 7, 7, TO_DATE('05/12/98', 'MM/DD/YY')); INSERT INTO tape VALUES ( 8, 8, TO_DATE('05/12/97', 'MM/DD/YY')); INSERT INTO tape VALUES ( 9, 6, TO_DATE('06/26/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 10, 9, TO_DATE('06/26/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 11, 10, TO_DATE('06/26/02', 'MM/DD/YY')); INSERT INTO tape VALUES ( 12, 11, TO_DATE('07/11/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 13, 12, TO_DATE('08/02/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 14, 6, TO_DATE('08/02/99', 'MM/DD/YY')); INSERT INTO tape VALUES ( 15, 13, TO_DATE('08/25/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 16, 14, TO_DATE('08/25/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 17, 15, TO_DATE('09/07/02', 'MM/DD/YY')); INSERT INTO tape VALUES ( 18, 16, TO_DATE('09/07/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 19, 17, TO_DATE('09/23/99', 'MM/DD/YY')); INSERT INTO tape VALUES ( 20, 14, TO_DATE('10/12/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 21, 18, TO_DATE('11/15/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 22, 19, TO_DATE('11/15/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 23, 20, TO_DATE('12/21/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 24, 21, TO_DATE('12/21/01', 'MM/DD/YY')); INSERT INTO tape VALUES ( 25,22, TO_DATE('12/22/99', 'MM/DD/YY')); INSERT INTO tape VALUES ( 26, 23, TO_DATE('12/22/00', 'MM/DD/YY')); INSERT INTO tape VALUES ( 27, 24, TO_DATE('12/23/00', 'MM/DD/YY')); -- inset records into the rental table INSERT INTO rental VALUES ( 1, 1,TO_DATE('02/01/2001', 'MM/DD/YYYY'), TO_DATE('02/02/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 2, 1,TO_DATE('02/01/2001', 'MM/DD/YYYY'), TO_DATE('02/02/2002', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 4, 2,TO_DATE('02/03/2001', 'MM/DD/YYYY'), TO_DATE('02/06/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 5, 3,TO_DATE('02/04/2001', 'MM/DD/YYYY'), TO_DATE('02/05/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 2, 3,TO_DATE('02/04/2001', 'MM/DD/YYYY'), TO_DATE('02/06/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 8, 6,TO_DATE('02/05/2001', 'MM/DD/YYYY'), TO_DATE('02/07/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 7, 5,TO_DATE('02/10/2001', 'MM/DD/YYYY'), TO_DATE('02/12/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 9, 7,TO_DATE('02/14/2001', 'MM/DD/YYYY'), TO_DATE('02/15/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 6, 9,TO_DATE('02/17/2001', 'MM/DD/YYYY'), TO_DATE('02/19/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 2, 9,TO_DATE('02/17/2001', 'MM/DD/YYYY'), TO_DATE('02/19/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 1, 7,TO_DATE('02/21/2001', 'MM/DD/YYYY'), TO_DATE('02/24/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 4, 6,TO_DATE('02/26/2001', 'MM/DD/YYYY'), TO_DATE('02/28/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 5, 1,TO_DATE('03/01/2001', 'MM/DD/YYYY'), TO_DATE('03/03/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 8, 4,TO_DATE('03/01/2001', 'MM/DD/YYYY'), TO_DATE('03/03/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 9, 1,TO_DATE('03/03/2001', 'MM/DD/YYYY'), TO_DATE('03/05/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 2, 1,TO_DATE('03/05/2001', 'MM/DD/YYYY'), TO_DATE('03/07/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 3, 10,TO_DATE('03/09/2001', 'MM/DD/YYYY'), TO_DATE('03/11/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 2, 10,TO_DATE('03/09/2001', 'MM/DD/YYYY'), TO_DATE('03/11/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 4, 4,TO_DATE('03/15/2001', 'MM/DD/YYYY'), TO_DATE('03/17/2001', 'MM/DD/YYYY'), 'n'); INSERT INTO rental VALUES ( 6, 9,TO_DATE('03/20/2001', 'MM/DD/YYYY'), TO_DATE('03/22/2001', 'MM/DD/YYYY'), 'n'); C