You have been employed to create program to assist the manager of a coffee shop. You decide to make a prototype application in Java to show what you can do, and to be able to ask the manager what features he/she would like to be developed in the complete version you will create for them in the coming months. Your prototype needs to demonstrate creating a database (tables and their data), modifying the records in it with values from the user, and perform validation on all input from the user (to reduce errors.) This application allows customers to run tabs (carry a credit with the store) as the store itself is in a large office building and sometimes large orders will come in for meetings and are then later paid by the individual or by their company. To facilitate this the system must keep customer data including a credit limit. Here are the requirements for this project.... General Requirements You must use NetBeans. This is to ensure that the project can be graded, and database access is the same. Create derby database called dbCoffeeStoreData in Java. Store it in the localhost location as we learned in class during the lecture on databases when this project was assigned. (This is very important in testing. Your database will not be created on my machine and is partly a manual process. You need to have the same name and location as the one I am going to create before I import your project for testing.) Database Schema / Create Table Statements Your project must have create statements for the following tables. Please create your own field names as ones in this description with spaces (like customer number) are invalid field names. Customer Table: customer number, first and last name fields, street, city, state, zip, phone, and email address as well as credit limit (must be double). Coffee Table: coffee id, coffee name, coffee description, price (must be double), number in stock (must be integer). Orders Table: order id, customer id, coffee id, number ordered (must be double), and the total(must be double). (5 points) [Note: The Orders Table is holding a total because prices are subject to change. The coffee shop runs occasional specials on some products to bring in customers. In a later prototype you would be storing the date in the Orders Table as well to track this.... but you are avoiding dates in the first prototype...this project...because the conversions can be tricky.] [Note2: The Create and Drop table statements will be taught during the lecture on databases. They are not in the Deitel book but will be covered in the lecture.] Insert Statements Create Insert Statements which will add 5 records with sample data into the Customer Table, 5 records into the Coffee Table, and 10 records (you must have some orders for at least 4 of the 5 customers and the orders must include at least 3 of the 5 coffees so you have a mix of them in these 10 records) into the Orders table. If you want more than these 20 insert statements...add them. You must represent at least 4 customers and e different coffees in the orders. But if you want to add extra records to make even more interesting reports...by all means do so. (5 points) Drop Statements This is a proof-of-concept prototype. So, you will be creating the tables with your start test data several times. To do this you must be able to delete the tables that are currently there. Create the Drop Statements which will Drop/delete the Customer, Coffee, and Orders tables. (5 points) Main Interface Create main screen JFrame with a logo image, a short paragraph about the company, and controls (buttons or links) to navigate to the other screens the side on the left side. Alternately, for the controls you can have them in a menu. The controls to show other screens should include ones to: Display Coffees, Display Customers, Display Orders for Customer, New Customer, and New Order. As this is a testing prototype. This screen also should have buttons or similar controls to: Create Database and Reset Database. Write code under the navigation controls to show the appropriate screen. (Create JFrame object of the type below and show it on the screen for the user.) Write code under the testing controls to connect to the database and: Create Database: execute the create statements to make the three tables and insert the starting data. Reset Database: execute the drop statements, create statements, and insert the starting data. Make sure to include Try/Catches around the code that makes database commands of course and give the user an appropriate message if there is a problem. (You will have to do this any way, or the program will not compile in NetBeans on a database connection.) (15 points) Display Customer Interface Create JFrame that shows the information in the Customer Table for the user. It should be presented in a grid or table. Keep the same look and feel of the main interface (same fonts and colors.) [Note: For this and all interfaces other than the main one you will probably need to change the default close operation on these Frames to DO_NOTHING_ON_CLOSE or HIDE_ON_CLOSE . That will keep the program from ending if the user closes them to work with the main screen.] 

(10 points) Display Coffees Interface Create JFrame that shows the information in the Coffee Table for the user. It should be presented in a grid or table. Keep the same look and feel of the main interface (same fonts and colors.) 

Display Orders for Customer Interface Create JFrame that shows the information in the Orders Table for the one customer. You can create this in a grid or table with filter controls on the screen or have a combo box loaded with customer IDs to choose from (as may be shown in the lecture on databases) or use similar means to display the orders for one customer along with their name. [You can't hard code the customers in as menu choices or radio buttons. You could have the user enter in an ID, name, or other identifying information into text boxes and use them for a select. You could also get this information from a JOptionPane before showing this form if you like. The point of this one is that you will have to join tables and pass in a value for a where clause in the SQL statement.] Keep the same look and feel of the main interface (same fonts and colors.) 

 New Customer Interface Create screen to add a new customer. It should contain text boxes for all the fields in the customer table except the credit limit. Every new user has a starting Credit Limit of 200.00 so you don't need a control to get this from the user. Have a button to validate all the fields (see below) and then insert a new record into the customer table. (10 points) New Order Interface Create screen to add a new order. It should have controls to accept values from the user to create new record in the order table. Have a button to validate all the fields (see below) and then insert a new record into the order table. 

Make sure to have a try/catch around the code to connect to and insert into the database. You should also use string functions or regex controls to make sure string values are not larger than the fields you created in your database before you do the insert (and if so you should give the user a JOptionPane message about it rather than doing the insert.) Also use a Regular Expression to check and make sure an e-mail address is in a valid e-mail address format. 

[Note: For the e-mail address regular expression it is not in your book. I would find the pattern on the internet. It took me less than 10 seconds when I searched "regular expression format for email" and looked at the one under www.java2s.com . Real ones that check e-mail address patterns will be long because they account for everything. If you want to write own simpler pattern to check for the @ and dot with characters before and after I am fine with it.]