Question

List the statements for the following transaction using the relations below: jsmith orders 1 book with ISBN 1045678900 on April 24, 2018.

Note: Bold Represents Primary Key. Italizied attributes in some relations denote foregin keys.

MEMBER(last_name, first_name, email, password, user, street, city, state, zip, card_type, card_no, expiration, name_on_card)

BOOKS_FOR_SALE(listing_no, seller, isbn, condition, price)

ORDERS(order_no, buyer, order_date, total)

ITEMS(order_no, listing_no)

BOOK(isbn, title, author, edition, publisher, keywords)

Answer 1

As the transaction is an Order, We need to insert the record in ORDERS and ITEMS tables. For Insert into ORDERS, We need price of the book which can be retrieved from BOOKS_FOR_SALE table. Also we need listing_no to store in ITEMS which also can be retrieved from BOOKS_FOR_SALE table. For these two we need to create local varaibles and fetch values in these variables.

DECLARE @price DECIMAL(5,2);
DECLARE @listing_no NUMBER(30);

SELECT @listing_no = listing_no, @price = price FROM BOOKS_FOR_SALE
         WHERE isbn = 1045678900 ;

Suppose We are inserting order with Order number as 1. Insert the record in ORDERS as well as ITEMS using above variables.

INSERT INTO ORDERS VALUES(1, '<span data-scaytid="27" data-scayt_word="jsmith">jsmith</span>', to_date('2018-04-24', '<span data-scaytid="28" data-scayt_word="yyyy-mm-dd">yyyy-mm-dd</span>'), @price);

INSERT INTO ITEMS VALUES(1, @listing_no);

Now that we have inserted record in ORDERS, we need to delete the record with isbn = 1045678900 from the BOOKS_FOR_SALE table, as the book is no longer available for sale.

DELETE FROM BOOKS_FOR_SALE WHERE isbn = 1045678900 

OR

DELETE FROM BOOKS_FOR_SALE WHERE listing_no = @listing_no;

These are the complete set of statements for above transaction. Please feel free to ask any questions about this.