COMP 580 Database Systems

Lab3

Summary:

In this lab you will be building an application for a customer support person. Customer support people need to be able to both read from the databse and make updates to it.

Details

In particular, the customer support application needs to support the following features:

The Customer support people need to be able to view existing orders (much like the clerk in the last lab)
The Customer Support people need to be able to add a new order
The customer support people need to add a new customer and address to the database.
The customer support people need to be able to update any existing order, address or customer support record to deal with customer changes.
It is the job of your application to make sure that the information that the customer support people enter does not put bad data in the database. This includes but is not limited to:

make sure that the addresses refer to only states in the state table

make sure that orders are made only for valid products in the product table

Make sure that orders are made only for valid customers etc

In addition to making sure that the database keeps its data in a good state, the application needs to enforce these business practices

Customers can only have two addresses: one billing and one shipping address.
New orders should not be accepted if the total quantity ordered (with the new order and all of the orders for this product in the order table) is greater than the current quantity of that product on hand.

If for any reason the new record cannot be added, give the user an informational error message detailing why the record cannot be added.

Basically, your application is responsible for making sure that the foreign keys from the original table diagram are respected. If you entered the foriegn keys into the database, the database will enforce this for you, and you just need to catch the exception that gets sent when you try to submit the insert or update. If you didn't use the foreign key constraints and the INNIODB engine, then you might have to do some checking in your application (perhaps having some data selected using a dropdown menu rather than a text box.

Submitting:

Like in the last lab, you need to to include a readme.txt file with the following information:

Your name

The name of your java files

The implementation decisions that you made. Why did you solve the problem the way that you did?

Directions on running and using your program. What should I expect when I start the program? Do I have to login? How do I switch between the two views?

And any problems or bugs left in your program. If your program doesn't quite work in some situations, tell me about it. If your program doesn't run or compile, I want to know that before I try.

Zip up all of the files and send them to me by email. The due date is Tuesday Nov 29th at Midnight. Note that this is after Thanksgiving break. You have a bit over three weeks for this project. However, there is only one more class before the due date.