The Northwind database contains the sales data for a fictitiouscompany called Northwind Traders, which imports and exportsspecialty foods from around the world. The database was prepared byMicrosoft for technical training. The most recent version of thedatabase can be found in Microsoft Access by entering "Northwind"in the Search for Online Templates box. The database contains thefollowing list of tables (some tables are further indented to makeclear their relationships):

• Employees
  • EmployeePrivileges
  • Privileges
• Customers
• Shippers
• Products
• Orders
  • OrderStatus
  • OrderTaxStatus
  • OrderDetails
  • OrderDetailsStatus
  • Invoices
• Suppliers
• PurchaseOrders
  • PurchaseOrderStatus
  • PurchaseOrderDetails
• InventoryTransactions
  • InventoryTransactionTypes
• Messages

These tables and their attributes are further described in theER diagram, which is given in the PDF file NorthWindER.pdf.

Run SQL script northwind.sql to create the database schema andload data in MySQL.

You are asked to write an interactive text-based program usingpython3 to support basic order fulfillmentoperations. It repeatedly gives the user a menu of options tochoose from, then carries out the user's request, until the userfinally chooses the exit option. Your program should take care ofall SQL queries with proper error/exception handling. Your programshould also have proper transaction support.

Your program should interact using plain text interfaceonly. To make compiling and grading easier, do not includegraphics 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 (not shipped yet) with customerinformation
6. more options
7. exit

Keep in mind the following when you write your code:

• User will be promoted to enter the necessary info, one field ata time.
• All IDs are automatically generated. (e.g. the biggest existingnumber + 1). MySQL has a function LAST_INSERT_ID() to obtain theauto-increment value immediately after an insert.
• To add an order:
  • Populate proper information (list price, order date, shipaddress) to ORDERS and ORDER_DETAILS.
  • Pay attention to foreign key constraints on Customer, Employee,Shipper, Product, OrderID, etc.
  • Multiple products can be placed in an order.
  • The order should be rejected if a product in the order isdiscontinued.
• To remove an order:
  • Delete the entries in ORDERS and ORDER_DETAILS tables.
• To ship an order:
  • Check whether there are enough units in stock of every productin the order. To find units in stock of a product, use theInventoryTransactions table, find total quantity purchased andsubtract quantities sold and on hold. If there are not enough unitsof any product in the order, the order cannot be shipped.
  • Fill in ShippedDate, Shipper ID and Shipping Fee.
  • For each product in the order, insert inventory transaction(TransactionType="Sold") into the InventoryTransactions table.
• To print pending order list:
  • Print only pending orders (i.e. orders with NULLShippedDate).
  • Print them in the order of order date.
• Your code is expected to provide support of databasetransactions in proper ways.
• Appropriate error-checking and error-handling areexpected.
• A user might enter a record whose key already exists in thetable. Handle this appropriately.
• Always assume the way it works in real world, if the aboverules are not sufficient or not clear.

The main menu has an item: 6. more options. This is where youare encouraged to implement extra features in a separate menu.These extra features may include purchase orders (activate apurchase order when stock is low, requisition, approval), inventoryupdate, invoice, etc. For further explanation of the NorthWinddatabase, please read the paperhttp://jise.org/Volume26/n2/JISEv26n2p85.html.

If you have implemented extra features, document the featuresclearly in the README file. You can receive bonus points up to thefull mark of this assignment.

Refer to course slides for examples on how to connect todatabase. For MySQL database connectors, refer to the officialMySQL online documentation.

Submission:

Paste the content of your README,txt file as your answer to thisquestion. Put the following into a zip file and submit it as anattachment by clicking on the "Add a File" button under your answerwindow:

• README.txt file (describing how to compile your code, extrafeatures you have implemented, and other stuff you want us toknow),
• your source code,
• a typescript that logs step-by-step how you compile and runyour program.

Grading Rubric:

README.txt and typescript (5 pts)
Compile error-free (5 pts)
Connect to database (10 pts)
Support transactions (10 pts)

1. add a customer (6 pts)
2. add an order (6 pts)
3. remove an order (6 pts)
4. ship an order (6 pts)
5. print pending orders with customer information (6 pts)

Answer rating: 100% (QA)

Great Lets break down the tasks mentioned in the assignment We can start with connecting to the MySQL database To achieve this youll need a MySQL conn... View the full answer