The Northwind database created by Microsoft contains the sales data for a fictit
ID: 3698502 • Letter: T
Question
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.
Explanation / Answer
Main.java
import java.sql.*;
import java.util.Scanner;
public class Main {
public static Scanner input = new Scanner(System.in); // global scanner
public static mySQLConnector table;
public static void main(String[] args) throws SQLException {
table = new mySQLConnector();
chooser();
}
private static void chooser() throws SQLException {
while (true) {
System.out.print("1 add a customer 4 add an order "
+ "2 remove an order 5 ship an order "
+ "3 print pending order 6 restock parts " + "7 exit "
+ "What would you like to do: ");
int choice = input.nextInt();
input.nextLine();
switch (choice) {
case 1:
addACustomer();
break;
case 2:
removeAnOrder();
break;
case 3:
printPendingOrder();
break;
case 4:
addAnOrder();
break;
case 5:
shipAnOrder();
break;
case 6:
restockParts();
break;
case 7:
exit();
return;
default:
System.out.println("Not a valid choice ");
break;
}
}
}
private static void addACustomer() throws SQLException {
String command = "insert into customers (cname, street, zip, phone) values ('";
System.out.print("Enter Customers Name: ");
command += input.nextLine() + "', '";
System.out.print("Enter Customers Street: ");
command += input.nextLine() + "', '";
System.out.print("Enter Customers Zipcode: ");
String zip = input.nextLine();
command += zip + "', '";
ResultSet zipQ = table
.executeQuery("select zip from zipcodes where zip = " + zip
+ ";");
if (!zipQ.first()) {
System.out.print("Enter Customers City: ");
table.executeUpdate("insert into zipcodes (zip, city) values ('"
+ zip + "', '" + input.nextLine() + "');");
}
zipQ.close();
System.out.print("Enter Customers Phone (###-###-####): ");
command += input.nextLine() + "');";
table.executeUpdate(command);
System.out.print("Customer Added ");
}
private static void sale() throws SQLException {
ResultSet orderQ;
try {
String command = "";
System.out.print("Enter Vendor Number: ");//if it exists keep going
int vno =
input.nextInt();
System.out.print("Enter Part Number: ");//if it exists keep going
int pnum = input.nextInt();
input.nextLine();
ResultSet partQ = table.executeQuery("select * from parts "
+ "where vno = "+vno+" and pno = " + pnum + ";");
if (//isnt an empty set) {
command = "insert into sale (eno, cno, cname, vno, pno, qty, price, total,date) values ("
+ pnum + ", '";
System.out.print("Enter Employee Number: ");//if it exists keep going
String eno= input.nextLine();
command += eno + "', ";
System.out.print("Enter Customer Number: ");
command += input.nextLine() + "', ";//if it exists keep going
orderQ = table.executeQuery("select e.ename from employees e where e.eno= " +eno +";");
orderQ.next();
String ename=orderQ.getString(1);
command += ename+"',";
command += vno + "', ";
command += pnum + "', ";
System.out.print("Enter Quanity: ");
command += input.nextInt() + ", '";
input.nextLine();
orderQ = table.executeQuery("select p.price from parts p where vno = "+vno+" and pno = " + pnum + ";");
orderQ.next();
int price = orderQ.getInt(1);
orderQ.close();
command += price + "', ";
command += input.nextInt() + ");";
partQ.close();
input.nextLine();
table.executeUpdate(command);
System.out.print("Parts Updated ");
} catch (SQLException e) {
System.out.print("Bad information Entered ");
}
}
private static void removeAnOrder() throws SQLException {
try {
System.out.print("Enter Order Number for deletion: ");
int> input.nextLine();
try {
String command = "delete from order_line where;";
table.executeUpdate(command);
} catch (Exception e) {
}
String command = "delete from orders where;";
table.executeUpdate(command);
System.out.print("Order deleted ");
} catch (Exception e) {
System.out.print("Order number does not match records ");
return;
}
}
private static void addAnOrder() throws SQLException {
String command2 = "";
int pno, amount, current;
ResultSet orderQ;
try {
System.out.print("Enter Part Number: ");
pno = input.nextInt();
command2 += pno + ", ";
input.nextLine();
orderQ = table.executeQuery("select * from parts where pno = "
+ pno + ";");
} catch (Exception e) {
System.out.print("Part number does not match records ");
return;
}
System.out.print("Enter Amount of Part Ordered: ");
amount = input.nextInt();
command2 += amount + ");";
input.nextLine();
orderQ.next();
current = orderQ.getInt(3);
if (current < amount) {
System.out.print("Not enough parts to complete order ");
return;
}
orderQ.close();
String command = "insert into orders (cno, eno) values (";
try {
System.out.print("Enter Customers Number: ");
command += input.nextInt() + ", ";
input.nextLine();
System.out.print("Enter Employees Number: ");
command += input.nextInt() + ");";
input.nextLine();
table.executeUpdate(command);
} catch (Exception e) {
System.out
.print("Customer or Employee number does not match records ");
return;
}
orderQ = table.executeQuery("select max(ono) from orders;");
orderQ.next();
int> orderQ.close();
table.executeUpdate("insert into order_line (ono, pno, qty) values ("
+ ono + ", " + command2);
table.executeUpdate("update parts set qoh=" + (current - amount)
+ " where pno=" + pno + ";");
System.out.print("Orders Updated ");
}
private static void shipAnOrder() {
try {
System.out.print("Enter Order Number to ship: ");
String command = "update orders set shipped=now() where;";
input.nextLine();
table.executeUpdate(command);
System.out.print("Order Shipped ");
} catch (Exception e) {
System.out.print("Order number does not match records ");
return;
}
}
// list with customer information
private static void printPendingOrder() throws SQLException {
ResultSet orderQ = table
.executeQuery("select o.cno, c.cname, c.street, c.zip, "
+ "o.ono, o.received from orders o, customers c where shipped <> true"
+ " group by o.received;");
System.out
.printf("------------------------------------------------------------------------- "
+ "| %5s | %-30s | %-30s | %9s | %5s | %21s | "
+ "------------------------------------------------------------------------- ",
"C Num", "C Name", "Street","Zipcode","O Num","Date Recived");
for (int i = 1; orderQ.absolute(i); i++) {
System.out.printf("| %5d | %-30s | %-30s | %9d | %5d | %21s | ",orderQ.getInt(1),
orderQ.getString(2),orderQ.getString(3),orderQ.getInt(4),orderQ.getInt(5),
orderQ.getString(6));
System.out
.printf("------------------------------------------------------------------------- ");
}
System.out.println("End Order List ");
}
private static void restockParts() throws SQLException {
try {
String command = "";
System.out.print("Enter Part Number: ");
int pnum = input.nextInt();
input.nextLine();
ResultSet partQ = table.executeQuery("select * from parts "
+ "where pno = " + pnum + ";");
if (!partQ.first()) {
command = "insert into parts (pno, pname, qoh, price, level) values ("
+ pnum + ", '";
System.out.print("Enter Part Name: ");
command += input.nextLine() + "', ";
System.out.print("Enter Quanity on Hand: ");
command += input.nextInt() + ", '";
input.nextLine();
System.out.print("Enter Part Price: ");
command += input.nextLine() + "', ";
System.out
.print("Enter Quanity Level (1 high, 2 mid, 3 low): ");
command += input.nextInt() + ");";
} else {
command = "update parts set qoh=";
System.out.print("Enter New Quanity on Hand: ");
command += input.nextInt() + ", price=";
input.nextLine();
System.out.print("Enter New Part Price: ");
command += input.nextDouble() + ", level=";
input.nextLine();
System.out.print("Enter New Quanity Level (1, 2, 3): ");
command += input.nextInt() + " where pno = " + pnum + ";";
}
partQ.close();
input.nextLine();
table.executeUpdate(command);
System.out.print("Parts Updated ");
} catch (SQLException e) {
System.out.print("Bad information Entered ");
}
}
private static void exit() throws SQLException {
System.out.print("Exiting Program ");
table.conn.close();
}
}
mySQLConnector.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class mySQLConnector {
/** The name of the MySQL account to use (or empty for anonymous) */
private final String userName = "root";
/** The password for the MySQL account (or empty for anonymous) */
private final String password = "";
/** The name of the computer running MySQL */
private final String serverName = "localhost";
/** The port of the MySQL server (default is 3306) */
private final int portNumber = 3306;
/** The name of the database we are testing with (this default is installed with MySQL) */
private final String dbName = "lab2";
/** The connection to SQL */
Connection conn = null;
public mySQLConnector() {
// Connect to MySQL
try {
conn = this.getConnection();
System.out.println("Connected to database");
} catch (SQLException e) {
System.out.println("ERROR: Could not connect to the database");
e.printStackTrace();
return;
}
}
/**
* Get a new database connection
*
* @return
* @throws SQLException
*/
private Connection getConnection() throws SQLException {
Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", this.userName);
connectionProps.put("password", this.password);
conn = DriverManager.getConnection("jdbc:mysql://"
+ this.serverName + ":" + this.portNumber + "/" + this.dbName,
connectionProps);
return conn;
}
/**
* Run a SQL command which does not return a query:
* CREATE/INSERT/UPDATE/DELETE/DROP/etc.
*
* @throws SQLException If something goes wrong
*/
public boolean executeUpdate(String command) throws SQLException {
Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.executeUpdate(command); // This will throw a SQLException if it fails
return true;
} finally {
// This will run whether we throw an exception or not
if (stmt != null) { stmt.close(); }
}
}
public ResultSet executeQuery(String command)throws SQLException {
Statement stmt = null;
stmt = conn.createStatement();
return stmt.executeQuery(command); // This will throw a SQLException if it fails
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.