Solved–Database Application Development– Solution

Description

The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database:

Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued);
Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage);
Categories (CategoryID, CategoryName, Description, Picture);
Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry);
Order_details (ID, OrderID, ProductID, UnitPrice, Quantity, Discount);
Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax);
Employees (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo);
Shippers (ShipperID, CompanyName, Phone);

Run SQL script northwind.sql to create the above schema and load data in MySQL.

This exercise lets you exercise on database application development using the JAVA programming language with JDBC.
Write an interactive text-based program. It repeatedly gives the user a menu of options to choose from, then carry out the user’s request, until he/she finally choose the exit option.
The program should take care of all SQL queries with proper error/exception handling. Your program should also have proper transaction support.

The program should interact using plain text interface only. To make compiling and grading easier, do not include graphics or any other fancy interface.

The top menu (and the tasks you are asked to implement) includes the following:

1. add a customer
2. add an order
3. remove an order
4. ship an order
5. print pending orders (not shipped yet) with customer information
6. restock products
7. exit

Make sure the above 7 items have correct implementation.
Keep in mind the following when you write the code:

• User will be prompted to enter the necessary info, one field at a time.
• All IDs except CustomerID are automatically generated. (e.g. the biggest existing number + 1).
• Many attribute values are required (NOT NULL).

• When add an order:
o Add to both ORDERS and ORDER_DETAILS.
o Pay attention to the foreign key constraints on CustomerID, EmployeeID, ShipVia, ProductID, OrderID.
o Update the Products’s UnitsOnOrder.
o The order should be rejected if a product in the order is discontinued.
• When remove an order:
o Delete the entry in both the ORDER and the ORDER_DETAILS tables.
o Update the UnitsOnOrder attribute.
• When printing pending order list:
o Print only pending orders (i.e. orders with NULL ShippedDate).
o Print them in the order of order date.
• Your code is expected to provide support of database transactions in proper ways.
• Appropriate error-checking and error-handling is expected.
• A user might enter a new record whose key already exists in the table. Handle this appropriately.
• Always assume the way it works in real world, if the above rules are not sufficient or not clear.
• If you have implemented extra features, document the features clearly in the README file.

Put your source code and a README file (describing how to compile your code, extra features you have implemented, and other stuff you want me to know) into a zip file and send it to me.