View the step-by-step solution to:

The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports...

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 sample data in MySQL.

This assignment lets you exercise on database application development using a programming language of your choice (C, Java, or PHP). Write an interactive text-based program. It repeatedly gives the user a menu of options to choose from, then carry out their request, until he/she finally choose the exit option. Your program should take care of all SQL queries with proper error/exception handling. Your program should also have proper transaction support.

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

Your 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 with customer information

6. restock parts

7. exit

You'll receive 10 points for correct implementation of each of the above items 1-6. Keep in mind the following when you write your code:

• User will be promoted to enter the necessary info, one field at a name.

• All IDs except CustomerID are automatically generated. (e.g. the biggest existing number + 1).

• Most 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.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question