Create a DB application Consider the following relational database schema written in Oracle SQL for an online book store application: drop table books cascade constraints; create table books ( isbn char(10), author varchar2(100) not null, title varchar2(128) not null, price number(7,2) not null, subject varchar2(30) not null, primary key (isbn) ); drop table members cascade constraints; create table members ( fname varchar2(20) not null, lname varchar2(20) not null, address varchar2(50) not null, city varchar2(30) not null, state varchar2(20) not null, zip number(5) not null, phone varchar2(12), email varchar2(40), userid varchar2(20), password varchar2(20), creditcardtype varchar2(10) check(creditcardtype in ('amex','discover','mc','visa')), creditcardnumber char(16), primary key (userid) ); drop table orders cascade constraints; create table orders ( userid varchar2(20) not null, ono number(5), received date not null, shipped date, shipAddress varchar2(50), shipCity varchar2(30), shipState varchar2(20), shipZip number(5), primary key (ono), foreign key (userid) references members ); drop table odetails cascade constraints; create table odetails ( ono number(5), isbn char(10), qty number(5) not null, price number(7,2) not null, primary key (ono,isbn), foreign key (ono) references orders, foreign key (isbn) references books ); drop table cart cascade constraints; create table cart ( userid varchar2(20), isbn char(10), qty number(5) not null, primary key (userid,isbn), foreign key (userid) references members, foreign key (isbn) references books ); The database consists of five tables: 1. Books: This table records information about the books on sale in the book store. Each book is classified under a subject to enable subject searches. 2. Members: This table records information about members of the application. Each member chooses their own user id and password at the time of registration. 3. Orders: This table records information about orders placed by members place orders. The orders may contain one or more books and the details of the order are kept in a separate table. A unique order number is generated by the system. 4. OrderDetails: This table records information about each order including the isbn and quantity of books in the order. 5. Cart: This table contains isbn and quantity of each book placed in the shopping cart of a member. Once a member checks out, the shopping cart is emptied and an order is created. The book store application should be developed as a terminal application in Java and should be implemented in three phases: Project requires: a) Each student to create data for approximately 10 books for two different subjects (the subjects may be assigned by the instructor of the class to each student). The instructor may then consolidate the data into a large data set and give it out to the entire class. This is an easy way to create a large data set of books. b) Each student to build program the following interface implementing only the member registration and member login functions: $ java OnlineBookStore ********************************************************************** *** *** *** Welcome to the Online Book Store *** *** *** ********************************************************************** 1. Member Login 2. New Member Registration q. Quit Type in your option: 2 Welcome to the Online Book Store New Member Registration Enter first name: Raj Enter last name: Sunderraman Enter street address: 123 Main Street Enter city: Atlanta Enter state: GA Enter zip: 30303 Enter phone: 555-1212 Enter email address: raj@cs.gsu.edu Enter userID: raj Enter password: raj Do you wish to store credit card information(y/n): y Enter type of Credit Card(amex/visa): amex Enter Credit Card Number: 121212121212121 Invalid Entry Enter Credit Card Number: 1212121212121212 Invalid Entry Enter Credit Card Number: 12121212121212 You have registered successfully. Name: Raj Sunderraman Address: 123 Main Street City: Atlanta GA 30303 Phone: 555-1212 Email: raj@cs.gsu.edu UserID: raj Password: raj CreditCard Type: amex CreditCard Number: 12121212121212 Press Enter to go back to Menu ********************************************************************** *** *** *** Welcome to the Online Book Store *** *** *** ********************************************************************** 1. Member Login 2. New Member Registration q. Quit Type in your option: 1 Enter userID: raj Enter password: raj ********************************************************************** *** *** *** Welcome to the Online Book Store *** *** Member Menu *** *** *** ********************************************************************** 1. Browse by Subject 2. Search by Author/Title/Subject 3. View/Edit Shopping Cart 4. Check Order Status 5. Check Out 6. One Click Check Out 7. View/Edit Personal Information 8. Logout Type in your option: 8 You have successfully logged out. Project requires the student to implement the following member functions: 1. Browse by Subject: This option should first list all subjects alphabetically; It then allows user to choose one subject; Upon choosing a subject, the program displays book details (2 books at a time on a screen); The option allows user to (a) enter isbn to put in cart; (b) press ENTER to return to main menu (c) press n ENTER to continue browsing User Interface follows: Type in your option: 1 1. Cooking 2. Jokes 3. Sports Enter your choice: 3 5 books available on this Subject Author: Dom Parker Title: 1,001 Baseball Questions Your Friends Can't Answer ISBN: 0451191323 Price: 22.46 Subject Sports Author: Timothy Jacobs Title: 100 Atheletes Who Shaped Sports History ISBN: 0912517131 Price: 32.56 Subject Sports Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: 0451191323 Enter quantity: 2 Author: Michael Dregni Title: 100 Years of Fishing ISBN: 0896584305 Price: 15.95 Subject Sports Author: David Claerbaut Title: The 1999 NBA Analyst: The Science of Hoops Magic ISBN: 0878332103 Price: 20.95 Subject Sports Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: n Author: Sports Collectors Digest Title: 1999 Sports Collectors Almanac (Serial) ISBN: 0987654234 Price: 17.56 Subject Sports Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: 0987654234 Enter quantity: 1 2. Search by Author/Title:This option should provide 3 sub-options: 1. Author Search 2. Title Search 3. Go Back to Member Menu In the Author or Title search sub-option, the user may enter a substring and the system should respond with all books which contain the substring in the title/author. The display should be done 2 books at a time on a screen. The system should also allow user to enter isbn to put in cart; to press ENTER to return to main menu to press n ENTER to continue browsing User Interface follows: 1. Author Search 2. Title Search 3. Go Back to Member Menu Type in your option: 2 Enter title or part of the title: cook 2 books found Author: Irma S. Rambauer Title: Joy of Cooking ISBN: 0452279232 Price: 15.25 Subject Cooking Author: Jennifer E. Darling Title: Better Homes and Gardens New Cook Book ISBN: 0696201887 Price: 21.96 Subject Cooking Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: 0696201887 Enter quantity: 1 1. Author Search 2. Title Search 3. Go Back to Member Menu Type in your option: 2 Enter title or part of the title: Computer 0 books found Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: n 1. Author Search 2. Title Search 3. Go Back to Member Menu Type in your option: 1 Enter name or part of the name: am 1 books found Author: Irma S. Rambauer Title: Joy of Cooking ISBN: 0452279232 Price: 15.25 Subject Cooking Enter ISBN to add to Cart or n Enter to continue to browse or ENTER to go back to menu: 0452279232 Enter quantity: 2 1. Author Search 2. Title Search 3. Go Back to Member Menu Type in your option: 3 3. View/Edit Shopping Cart: This option should show the contents of the cart; It should then provide options to delete items or edit (change quantity) items. User Interface (for delete and update cart) follows: Current Cart Contents: ISBN Title $ Qty Total ----------------------------------------------------------------------------- -- 0696201887 Better Homes and Gardens New Cook Book 21.95 1 21.95 0452279232 Joy of Cooking 15.25 2 30.50 ----------------------------------------------------------------------------- Total = $52.45 Enter d to delete item e to edit cart or q to go back to Menu: d Enter isbn of item: 0452279232 Delete Item Completed Press enter to go back to Menu Type in your option: 3 Current Cart Contents: ISBN Title $ Qty Total ----------------------------------------------------------------------------- -- 0696201887 Better Homes and Gardens New Cook Book 21.95 1 21.95 ----------------------------------------------------------------------------- Total = $21.95 Enter d to delete item e to edit cart or q to go back to Menu: e Enter isbn of item: 0696201887 Enter new Quantity: 2 Edit Item Completed Press enter to go back to Menu 4. Check Order Status: This option should list all orders for member and should allow user to choose one order to see details. User Interface follows: Orders placed by Raj Sunderraman ----------------------------------------------------------- ORDER NO RECEIVED DATE SHIPPED DATE ----------------------------------------------------------- 117 3-1-2001 3-3-2001 ----------------------------------------------------------- Enter the Order No to display its details or (q) to quit: 117 Details for Order no.117 Shipping Address Billing address Name: Raj Sunderraman Name: Raj Sunderraman Address: 123 Main Street Address: 123 Main Street Atlanta Atlanta GA 33333 GA 33333 ----------------------------------------------------------------------------- ISBN Title $ Qty Total ----------------------------------------------------------------------------- -- 0451191323 1,001 Baseball Questions Your Friends Can't Answer 22.45 1 22.45 0987654234 1999 Sports Collectors Almanac(Serial) 17.55 1 17.55 ----------------------------------------------------------------------------- Total = $40.01 Press Enter to go back to Menu 5. Check Out This option should display invoice; request user of they want to provide shipping address (if no use current address in file for shipping); Also this option should ask if a new credit card should be used. Finally, an invoice should be printed. User Interface follows: Current Cart Contents: ISBN Title $ Qty Total ----------------------------------------------------------------------------- 0696201887 Better Homes and Gardens New Cook Book 21.95 2 43.91 ----------------------------------------------------------------------------- Total $43.91 ----------------------------------------------------------------------------- Proceed to check out(Y/N): y Do you want to enter new shipping address(y/n): y Enter first name: John Enter last name: Smith Enter street: 123 Elm Street Enter city: Atlanta Enter state: GA Enter zip: 11111 Do you want to enter new CreditCard Number(y/n): n Invoice for Order no.118 Shipping Address Billing address Name: John Smith Name: Raj Sunderraman Address: 123 Elm Street Address: 123 Main Street Atlanta Atlanta GA 11111 GA 33333 ----------------------------------------------------------------------------- ISBN Title $ Qty Total ----------------------------------------------------------------------------- -- 0696201887 Better Homes and Gardens New Cook Book 21.95 2 43.91 ----------------------------------------------------------------------------- Total = $43.91 ----------------------------------------------------------------------------- Press enter to go back to Menu 6. One Click Check Out This option should move items in the cart to the order and odetails tables. Cart is emptied in the process and an invoice is printed. Shipping address is same as member address in this option. User Interface follows: Invoice for Order no.117 Shipping Address Billing address Name: Raj Sunderraman Name: Raj Sunderraman Address: 123 Main Street Address: 123 Main Street Atlanta Atlanta GA 33333 GA 33333 ----------------------------------------------------------------------------- ISBN Title $ Qty Total ----------------------------------------------------------------------------- -- 0451191323 1,001 Baseball Questions Your Friends Can't Answer 22.45 1 22.45 0987654234 1999 Sports Collectors Almanac(Serial) 17.55 1 17.55 ----------------------------------------------------------------------------- -- Total = $40.01 ----------------------------------------------------------------------------- -- Press enter to go back to Menu