Untitled Document

REM
REM
REM-----DROP any existing tables in the Henry Books Database
REM
REM
DROP TABLE author CASCADE CONSTRAINTS;
DROP TABLE book CASCADE CONSTRAINTS;
DROP TABLE branch CASCADE CONSTRAINTS;
DROP TABLE invent CASCADE CONSTRAINTS;
DROP TABLE publisher CASCADE CONSTRAINTS;
DROP TABLE wrote CASCADE CONSTRAINTS;
DROP TABLE tlkpBooktype CASCADE CONSTRAINTS;
DROP TABLE tlkpCity CASCADE CONSTRAINTS;
DROP TABLE tlkpState CASCADE CONSTRAINTS;
REM
REM
REM-----CREATE tables for Henry Books Database
REM
REM

CREATE TABLE author
(
author_number CHAR (2) PRIMARY KEY,
author_last CHAR (20),
author_first CHAR (20)
);

CREATE TABLE branch
(
branch_number CHAR(1) PRIMARY KEY,
branch_name CHAR(20),
branch_location CHAR(20),
number_employees CHAR (2)
);

CREATE TABLE publisher
(
publisher_code CHAR(2) PRIMARY KEY,
publisher_name CHAR(20),
publisher_city CHAR(20),
publisher_state CHAR(2)
);

CREATE TABLE book
(
book_code CHAR (4) PRIMARY KEY,
book_title CHAR (30),
publisher_code CHAR (2)
CONSTRAINT book_pub_code_fk
REFERENCES publisher(publisher_code),
book_type CHAR (3),
book_price NUMBER (7,2),
paperback CHAR (1),
CONSTRAINT book_price_ck CHECK (book_price > 0)
);

CREATE TABLE invent
(
book_code CHAR(4)
CONSTRAINT invent_book_code_fk
REFERENCES book(book_code),
branch_number CHAR(1)
CONSTRAINT invent_branch_number_fk
REFERENCES branch(branch_number),
units_on_hand NUMBER(6,0),
CONSTRAINT invent_pk PRIMARY KEY (book_code, branch_number),
CONSTRAINT units_on_hand_ck CHECK (units_on_hand >= 0)
);

CREATE TABLE wrote
(
book_code CHAR(4),
author_number CHAR(2),
sequence_number NUMBER(3,0),
CONSTRAINT wrote_pk PRIMARY KEY (book_code, author_number)
);

CREATE TABLE tlkpBooktype
(
booktype CHAR(3) PRIMARY KEY
);

CREATE TABLE tlkpCity
(
city CHAR(20) PRIMARY KEY
);

CREATE TABLE tlkpState
(
statecode CHAR(2) PRIMARY KEY,
statename CHAR(20),
sortorder NUMBER(2,0)
);
REM
REM
REM-----Begin INSERT statements
REM
REM
INSERT INTO branch (branch_number, branch_name, branch_location, number_employees)
VALUES ('1','Henry Downtown','16 Riverview','10');
INSERT INTO branch (branch_number, branch_name, branch_location, number_employees)
VALUES ('2','Henry On The Hill','1289 Bedford','6');
INSERT INTO branch (branch_number, branch_name, branch_location, number_employees)
VALUES ('3','Henry Brentwood','Brentwood Mall','15');
INSERT INTO branch (branch_number, branch_name, branch_location, number_employees)
VALUES ('4','Henry Eastshore','Eastshore Mall','9');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('AH','Arkham House Publ.','Sauk City','WI');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('AP','Arcade Publishing','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('AW','Addison Wesley','Reading','MA');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('BB','Bantam Books','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('BF','Best and Furrow','Boston','MA');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('JT','Jeremy P. Tarcher','Los Angeles','CA');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('MP','McPherson and Co.','Kingston','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('PB','Pocket Books','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('RH','Random House','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('RZ','Rizzoli','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('SB','Schoken Books','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('SI','Signet','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('TH','Thames and Hudson','New York','NY');
INSERT INTO publisher (publisher_code, publisher_name, publisher_city, publisher_state)
VALUES ('WN','W.W. Norton and Co.','New York','NY');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('01','Archer','Jeffrey');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('02','Christie','Agatha');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('03','Clarke','Arthur C.');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('04','Francis','Dick');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('05','Cussler','Clive');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('06','King','Stephen');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('07','Pratt','Philip');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('08','Adamski','Joseph');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('10','Harmon','Willis');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('11','Rheingold','Howard');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('12','Owen','Barbara');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('13','Williams','Peter');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('14','Kafka','Franz');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('15','Novalis',' ');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('16','Lovecraft','H.P.');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('17','Paz','Octavio');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('18','Camus','Albert');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('19','Castleman','Riva');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('20','Zimbardo','Philip');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('21','Gimferrer','Pere');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('22','Southworth','Rod');
INSERT INTO author (author_number, author_last, author_first)
VALUES ('23','Wray','Robert');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('0180','Shyness','BB','PSY','7.65','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('0189','Kane and Abel','PB','FIC','5.55','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('0200','Stranger','BB','FIC','8.75','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('0378','Dunwich Horror and Others','PB','HOR','19.75','N');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('079X','Smokescreen','PB','MYS','4.55','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('0808','Knockdown','PB','MYS','4.75','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('1351','Cujo','SI','HOR','6.65','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('1382','Marcel Duchamp','PB','ART','11.25','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('138X','Death on the Nile','BB','MYS','3.95','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('2226','Ghost from the Grand Banks','BB','SFI','19.95','N');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('2281','Prints of the 20th Century','PB','ART','13.25','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('2766','Prodigal Daughter','PB','FIC','5.45','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('2908','Hymns to the Night','BB','POE','6.75','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('3350','Higher Creativity','PB','PSY','9.75','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('3743','First Among Equals','PB','FIC','3.95','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('3906','Vortex','BB','SUS','5.45','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('5163','Organ','SI','MUS','16.95','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('5790','Database Systems','BF','CS','54.95','N');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('6128','Evil Under the Sun','PB','MYS','4.45','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('6328','Vixen 07','BB','SUS','5.55','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('669X','A Guide to SQL','BF','CS','23.95','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('6908','DOS Essentials','BF','CS','20.50','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('7405','Night Probe','BB','SUS','5.65','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('7443','Carrie','SI','HOR','6.75','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('7559','Risk','PB','MYS','3.95','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('7947','dBASE Programming','BF','CS','39.90','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('8092','Magritte','SI','ART','21.95','N');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('8720','Castle','BB','FIC','12.15','Y');
INSERT INTO book (book_code, book_title, publisher_code, book_type, book_price, paperback)
VALUES ('9611','Amerika','BB','FIC','10.95','Y');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('0180','20','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('0189','01','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('0200','18','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('0378','16','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('079X','04','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('0808','04','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('1351','06','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('1382','17','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('138X','02','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('2226','03','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('2281','19','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('2766','01','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('2908','15','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('3350','10','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('3350','11','2');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('3743','01','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('3906','05','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('5163','12','2');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('5163','13','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('5790','07','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('5790','08','2');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('6128','02','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('6328','05','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('669X','07','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('6908','22','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('7405','05','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('7443','06','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('7559','04','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('7947','07','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('7947','23','2');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('8092','21','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('8720','14','1');
INSERT INTO wrote (book_code, author_number, sequence_number)
VALUES ('9611','14','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('0180','1','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('0189','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('0200','1','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('0200','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('079X','2','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('079X','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('079X','4','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('1351','1','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('1351','2','4');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('1351','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('138X','2','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2226','1','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2226','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2226','4','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2281','4','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2766','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2908','1','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('2908','4','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('3350','1','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('3906','2','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('3906','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('5163','1','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('5790','4','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('6128','2','4');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('6128','3','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('6328','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('669X','1','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('6908','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('7405','3','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('7559','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('7947','2','2');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('8092','3','1');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('8720','1','3');
INSERT INTO invent (book_code, branch_number, units_on_hand)
VALUES ('9611','1','2');
COMMIT;