CREATE TABLE AUTHOR (AUTHOR_NUM DECIMAL(2,0) PRIMARY KEY, AUTHOR_LAST CHAR(12),

Question

CREATE TABLE AUTHOR (AUTHOR_NUM DECIMAL(2,0) PRIMARY KEY, AUTHOR_LAST CHAR(12), AUTHOR_FIRST CHAR(10) );
CREATE TABLE BOOK (BOOK_CODE CHAR(4) PRIMARY KEY, TITLE CHAR(40), PUBLISHER_CODE CHAR(3), TYPE CHAR(3), PRICE DECIMAL(4,2), PAPERBACK CHAR(1) );
CREATE TABLE BRANCH (BRANCH_NUM DECIMAL(2,0) PRIMARY KEY, BRANCH_NAME CHAR(50), BRANCH_LOCATION CHAR(50), NUM_EMPLOYEES DECIMAL(2,0) ); CREATE TABLE INVENTORY (BOOK_CODE CHAR(4), BRANCH_NUM DECIMAL(2,0) NOT NULL, ON_HAND DECIMAL(2,0), PRIMARY KEY (BOOK_CODE, BRANCH_NUM) );
CREATE TABLE PUBLISHER (PUBLISHER_CODE CHAR(3) PRIMARY KEY, PUBLISHER_NAME CHAR(25), CITY CHAR(20) );
CREATE TABLE WROTE (BOOK_CODE CHAR(4), AUTHOR_NUM DECIMAL(2,0) NOT NULL, SEQUENCE DECIMAL(1,0), PRIMARY KEY (BOOK_CODE, AUTHOR_NUM) );
INSERT INTO BRANCH VALUES (1,'Henry Downtown','16 Riverview',10); INSERT INTO BRANCH VALUES (2,'Henry On The Hill','1289 Bedford',6); INSERT INTO BRANCH VALUES (3,'Henry Brentwood','Brentwood Mall',15); INSERT INTO BRANCH VALUES (4,'Henry Eastshore','Eastshore Mall',9);
INSERT INTO PUBLISHER VALUES ('AH','Arkham House','Sauk City WI'); INSERT INTO PUBLISHER VALUES ('AP','Arcade Publishing','New York'); INSERT INTO PUBLISHER VALUES ('BA','Basic Books','Boulder CO'); INSERT INTO PUBLISHER VALUES ('BP','Berkley Publishing','Boston'); INSERT INTO PUBLISHER VALUES ('BY','Back Bay Books','New York'); INSERT INTO PUBLISHER VALUES ('CT','Course Technology','Boston'); INSERT INTO PUBLISHER VALUES ('FA','Fawcett Books','New York'); INSERT INTO PUBLISHER VALUES ('FS','Farrar Straus and Giroux','New York'); INSERT INTO PUBLISHER VALUES ('HC','HarperCollins Publishers','New York'); INSERT INTO PUBLISHER VALUES ('JP','Jove Publications','New York'); INSERT INTO PUBLISHER VALUES ('JT','Jeremy P. Tarcher','Los Angeles'); INSERT INTO PUBLISHER VALUES ('LB','Lb Books','New York'); INSERT INTO PUBLISHER VALUES ('MP','McPherson and Co.','Kingston'); INSERT INTO PUBLISHER VALUES ('PE','Penguin USA','New York'); INSERT INTO PUBLISHER VALUES ('PL','Plume','New York'); INSERT INTO PUBLISHER VALUES ('PU','Putnam Publishing Group','New York'); INSERT INTO PUBLISHER VALUES ('RH','Random House','New York'); INSERT INTO PUBLISHER VALUES ('SB','Schoken Books','New York'); INSERT INTO PUBLISHER VALUES ('SC','Scribner','New York'); INSERT INTO PUBLISHER VALUES ('SS','Simon and Schuster','New York'); INSERT INTO PUBLISHER VALUES ('ST','Scholastic Trade','New York'); INSERT INTO PUBLISHER VALUES ('TA','Taunton Press','Newtown CT'); INSERT INTO PUBLISHER VALUES ('TB','Tor Books','New York'); INSERT INTO PUBLISHER VALUES ('TH','Thames and Hudson','New York'); INSERT INTO PUBLISHER VALUES ('TO','Touchstone Books','Westport CT'); INSERT INTO PUBLISHER VALUES ('VB','Vintage Books','New York'); INSERT INTO PUBLISHER VALUES ('WN','W.W. Norton','New York'); INSERT INTO PUBLISHER VALUES ('WP','Westview Press','Boulder CO');
INSERT INTO AUTHOR VALUES (1,'Morrison','Toni'); INSERT INTO AUTHOR VALUES (2,'Solotaroff','Paul'); INSERT INTO AUTHOR VALUES (3,'Vintage','Vernor'); INSERT INTO AUTHOR VALUES (4,'Francis','Dick'); INSERT INTO AUTHOR VALUES (5,'Straub','Peter'); INSERT INTO AUTHOR VALUES (6,'King','Stephen'); INSERT INTO AUTHOR VALUES (7,'Pratt','Philip'); INSERT INTO AUTHOR VALUES (8,'Chase','Truddi'); INSERT INTO AUTHOR VALUES (9,'Collins','Bradley'); INSERT INTO AUTHOR VALUES (10,'Heller','Joseph'); INSERT INTO AUTHOR VALUES (11,'Wills','Gary'); INSERT INTO AUTHOR VALUES (12,'Hofstadter','Douglas R.'); INSERT INTO AUTHOR VALUES (13,'Lee','Harper'); INSERT INTO AUTHOR VALUES (14,'Ambrose','Stephen E.'); INSERT INTO AUTHOR VALUES (15,'Rowling','J.K.'); INSERT INTO AUTHOR VALUES (16,'Salinger','J.D.'); INSERT INTO AUTHOR VALUES (17,'Heaney','Seamus'); INSERT INTO AUTHOR VALUES (18,'Camus','Albert'); INSERT INTO AUTHOR VALUES (19,'Collins, Jr.','Bradley'); INSERT INTO AUTHOR VALUES (20,'Steinbeck','John'); INSERT INTO AUTHOR VALUES (21,'Castelman','Riva'); INSERT INTO AUTHOR VALUES (22,'Owen','Barbara'); INSERT INTO AUTHOR VALUES (23,'O''Rourke','Randy'); INSERT INTO AUTHOR VALUES (24,'Kidder','Tracy'); INSERT INTO AUTHOR VALUES (25,'Schleining','Lon');
INSERT INTO BOOK VALUES ('0180','A Deepness in the Sky','TB','SFI',7.19,'Y'); INSERT INTO BOOK VALUES ('0189','Magic Terror','FA','HOR',7.99,'Y'); INSERT INTO BOOK VALUES ('0200','The Stranger','VB','FIC',8.00,'Y'); INSERT INTO BOOK VALUES ('0378','Venice','SS','ART',24.50,'N'); INSERT INTO BOOK VALUES ('079X','Second Wind','PU','MYS',24.95,'N'); INSERT INTO BOOK VALUES ('0808','The Edge','JP','MYS',6.99,'Y'); INSERT INTO BOOK VALUES ('1351','Dreamcatcher: A Novel','SC','HOR',19.60,'N'); INSERT INTO BOOK VALUES ('1382','Treasure Chests','TA','ART',24.46,'N'); INSERT INTO BOOK VALUES ('138X','Beloved','PL','FIC',12.95,'Y'); INSERT INTO BOOK VALUES ('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI',13.96,'N'); INSERT INTO BOOK VALUES ('2281','Van Gogh and Gauguin','WP','ART',21.00,'N'); INSERT INTO BOOK VALUES ('2766','Of Mice and Men','PE','FIC',6.95,'Y'); INSERT INTO BOOK VALUES ('2908','Electric Light','FS','POE',14.00,'N'); INSERT INTO BOOK VALUES ('3350','Group: Six People in Search of a Life','BP','PSY',10.40,'Y'); INSERT INTO BOOK VALUES ('3743','Nine Stories','LB','FIC',5.99,'Y'); INSERT INTO BOOK VALUES ('3906','The Soul of a New Machine','BY','SCI',11.16,'Y'); INSERT INTO BOOK VALUES ('5163','Travels with Charley','PE','TRA',7.95,'Y'); INSERT INTO BOOK VALUES ('5790','Catch-22','SC','FIC',12.00,'Y'); INSERT INTO BOOK VALUES ('6128','Jazz','PL','FIC',12.95,'Y'); INSERT INTO BOOK VALUES ('6328','Band of Brothers','TO','HIS',9.60,'Y'); INSERT INTO BOOK VALUES ('669X','A Guide to SQL','CT','CMP',37.95,'Y'); INSERT INTO BOOK VALUES ('6908','Franny and Zooey','LB','FIC',5.99,'Y'); INSERT INTO BOOK VALUES ('7405','East of Eden','PE','FIC',12.95,'Y'); INSERT INTO BOOK VALUES ('7443','Harry Potter and the Goblet of Fire','ST','SFI',18.16,'N'); INSERT INTO BOOK VALUES ('7559','The Fall','VB','FIC',8.00,'Y'); INSERT INTO BOOK VALUES ('8092','Godel, Escher, Bach','BA','PHI',14.00,'Y'); INSERT INTO BOOK VALUES ('8720','When Rabbit Howls','JP','PSY',6.29,'Y'); INSERT INTO BOOK VALUES ('9611','Black House','RH','HOR',18.81,'N'); INSERT INTO BOOK VALUES ('9627','Song of Solomon','PL','FIC',14.00,'Y'); INSERT INTO BOOK VALUES ('9701','The Grapes of Wrath','PE','FIC',13.00,'Y'); INSERT INTO BOOK VALUES ('9882','Slay Ride','JP','MYS',6.99,'Y'); INSERT INTO BOOK VALUES ('9883','The Catcher in the Rye','LB','FIC',5.99,'Y'); INSERT INTO BOOK VALUES ('9931','To Kill a Mockingbird','HC','FIC',18.00,'N');
INSERT INTO WROTE VALUES ('0180',3,1); INSERT INTO WROTE VALUES ('0189',5,1); INSERT INTO WROTE VALUES ('0200',18,1); INSERT INTO WROTE VALUES ('0378',11,1); INSERT INTO WROTE VALUES ('079X',4,1); INSERT INTO WROTE VALUES ('0808',4,1); INSERT INTO WROTE VALUES ('1351',6,1); INSERT INTO WROTE VALUES ('1382',23,2); INSERT INTO WROTE VALUES ('1382',25,1); INSERT INTO WROTE VALUES ('138X',1,1); INSERT INTO WROTE VALUES ('2226',15,1); INSERT INTO WROTE VALUES ('2281',9,2); INSERT INTO WROTE VALUES ('2281',19,1); INSERT INTO WROTE VALUES ('2766',20,1); INSERT INTO WROTE VALUES ('2908',17,1); INSERT INTO WROTE VALUES ('3350',2,1); INSERT INTO WROTE VALUES ('3743',16,1); INSERT INTO WROTE VALUES ('3906',24,1); INSERT INTO WROTE VALUES ('5163',20,1); INSERT INTO WROTE VALUES ('5790',10,1); INSERT INTO WROTE VALUES ('6128',1,1); INSERT INTO WROTE VALUES ('6328',14,1); INSERT INTO WROTE VALUES ('669X',7,1); INSERT INTO WROTE VALUES ('6908',16,1); INSERT INTO WROTE VALUES ('7405',20,1); INSERT INTO WROTE VALUES ('7443',15,1); INSERT INTO WROTE VALUES ('7559',18,1); INSERT INTO WROTE VALUES ('8092',12,1); INSERT INTO WROTE VALUES ('8720',8,1); INSERT INTO WROTE VALUES ('9611',5,2); INSERT INTO WROTE VALUES ('9611',6,1); INSERT INTO WROTE VALUES ('9627',1,1); INSERT INTO WROTE VALUES ('9701',20,1); INSERT INTO WROTE VALUES ('9882',4,1); INSERT INTO WROTE VALUES ('9883',16,1); INSERT INTO WROTE VALUES ('9931',13,1);
INSERT INTO INVENTORY VALUES ('0180',1,2); INSERT INTO INVENTORY VALUES ('0189',2,2); INSERT INTO INVENTORY VALUES ('0200',1,1); INSERT INTO INVENTORY VALUES ('0200',2,3); INSERT INTO INVENTORY VALUES ('0378',3,2); INSERT INTO INVENTORY VALUES ('079X',2,1); INSERT INTO INVENTORY VALUES ('079X',3,2); INSERT INTO INVENTORY VALUES ('079X',4,3); INSERT INTO INVENTORY VALUES ('0808',2,1); INSERT INTO INVENTORY VALUES ('1351',2,4); INSERT INTO INVENTORY VALUES ('1351',3,2); INSERT INTO INVENTORY VALUES ('1382',2,1); INSERT INTO INVENTORY VALUES ('138X',2,3); INSERT INTO INVENTORY VALUES ('2226',1,3); INSERT INTO INVENTORY VALUES ('2226',3,2); INSERT INTO INVENTORY VALUES ('2226',4,1); INSERT INTO INVENTORY VALUES ('2281',4,3); INSERT INTO INVENTORY VALUES ('2766',3,2); INSERT INTO INVENTORY VALUES ('2908',1,3); INSERT INTO INVENTORY VALUES ('2908',4,1); INSERT INTO INVENTORY VALUES ('3350',1,2); INSERT INTO INVENTORY VALUES ('3743',2,1); INSERT INTO INVENTORY VALUES ('3906',2,1); INSERT INTO INVENTORY VALUES ('3906',3,2); INSERT INTO INVENTORY VALUES ('5163',1,1); INSERT INTO INVENTORY VALUES ('5790',4,2); INSERT INTO INVENTORY VALUES ('6128',2,4); INSERT INTO INVENTORY VALUES ('6128',3,3); INSERT INTO INVENTORY VALUES ('6328',2,2); INSERT INTO INVENTORY VALUES ('669X',1,1); INSERT INTO INVENTORY VALUES ('6908',2,2); INSERT INTO INVENTORY VALUES ('7405',3,2); INSERT INTO INVENTORY VALUES ('7443',4,1); INSERT INTO INVENTORY VALUES ('7559',2,2); INSERT INTO INVENTORY VALUES ('8092',3,1); INSERT INTO INVENTORY VALUES ('8720',1,3); INSERT INTO INVENTORY VALUES ('9611',1,2); INSERT INTO INVENTORY VALUES ('9627',3,5); INSERT INTO INVENTORY VALUES ('9627',4,2); INSERT INTO INVENTORY VALUES ('9701',1,2); INSERT INTO INVENTORY VALUES ('9701',2,1); INSERT INTO INVENTORY VALUES ('9701',3,3); INSERT INTO INVENTORY VALUES ('9701',4,2); INSERT INTO INVENTORY VALUES ('9882',3,3); INSERT INTO INVENTORY VALUES ('9883',2,3); INSERT INTO INVENTORY VALUES ('9883',4,2); INSERT INTO INVENTORY VALUES ('9931',1,2);
rd. 3 Henry Books Case IITvesda 1. Create a view named Penguin Books. It consists of the book code, book title, book type, and 2. Create a view named Paperback. It consists of the book code, book tite, publisher name, and 3. Create a view named Book Inventory. It consists of the book code and the total number of units of book price for every book published by Penguin USA. Display the data in the view book price for every book that is available in paperback. Display the data in the view. the book on hand at any branch. Display the data in the view the following indexes. If it is necessary to name the index in your DB S, use the indicated a. Create an index named Booklndex1 on the Publisher Name field in the Publisher b. name. table. Create an index named Bookindex2 on the Type field in the Book table. Create an index named BookIndex3 on the Type and Price fields in the Book table and list the prices in descending order. c. 5. Drop the BookIndex3 index. 6. Specify the integrity constraint that the price of any book must be less than $90. 7. Ensure that the following are foreign keys (that is, specify referential integrity) within the Henry Books database a. b. c. PublisherCode is a foreign key in the Book table. BranchNum is a foreign key in the Inventory table. AuthorNum is a foreign key in the Wrote table. 8. Add to the Book table a new character field named Classic that is one character in length. 9. Change the Classic field in the Book table to Y for the book titled The Grapes of Wrath. 10. Change the length of the Title field in the Book table to 60. 11. What command would delete the Books table from the Henry Books database? (Do not delete the book table) you use this stored procedure to change the price of book 0189 to $8.49? hand in all branches for that book. Following the style shown in the text, write the code for the 12. Write a stored procedure that will change the price of a book with a given book code. How would 13. Assume the Book table contains a column called TotalOnHand that represents the total units on following triggers. a. When inserting a row in the Inventory table, add the OnHand value to the TotalOnHand b. When updating a row in the Inventory table, add the difference between the new OnHand c. When deleting a row in the Inventory table, subtract the OnHand value from the value for the appropriate book. value and the OnHand value for the appropriate book. TotalOnHand value for the appropriate book.

Explanation / Answer

1.

create view Penguin_Books as select b.book_code "Book Code", b.title "Book Title", b.type "Book Type", b.price "Book Price" from book b, publisher p where b.publisher_code = p.publisher_code and p.publisher_name='Penguin USA' ;

select * from Penguin_Books;

2.

create view Paperback as select b.book_code "Book Code", b.title "Book Title", p.publisher_name "Publisher Name", b.price "Book Price" from book b, publisher p where b.publisher_code = p.publisher_code and b.paperback='Y' ;

select * from Paperback;

3.

create view Book_Inventory as select b.book_code "Book Code", i.on_hand "Books On Hand" from book b, Inventory i where b.book_code = i.book_code;

select * from Book_Inventory ;

4.

a. CREATE INDEX BookIndex1 ON Publisher (Publisher_name);

b. CREATE INDEX BookIndex2 ON Book (Type);

c. CREATE INDEX BookIndex3 ON Book (type,price);

select price from book order by price desc;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.