Retail Business System
Repository of work for CS532 Database Systems at Binghamton University, Fall 2015.
In this project, we built a Retail Business System for the given requirements using JavaEE, Oracle PL/SQL and JDBC.
We are given the following tables in this project: Employees(eid, ename, telephone#) Customers(cid, cname, telephone#, visits_made, last_visit_date) Products(pid, pname, qoh, qoh_threshold, original_price, discnt_rate) Suppliers(sid, sname, city, telephone#) Supply(sup#, pid, sid, sdate, quantity) Purchases(pur#, eid, pid, cid, ptime, qty, total_price) Logs(log#, who, otime, table_name, operation, key_value)
Requirements and functionalities
We need to create a PL/SQL package and other Oracle objects (sequences, triggers, ...) to implement this project.
Values for pur#, sup# and log# need to be generated by appropriate sequences automatically when new tuples are to the corresponding tables. For each of these primary key attributes, if its data type is number(n), its values should have n digits. Implement a different sequence for each of these attributes.
Display the tuples in each table. If you just want to run your package in the SQL*Plus environment, it is sufficient to create a procedure for each table. As an example, you can implement a procedure, say show_products, in your package to display all products in the products table. You need to implement six procedures, one for each table. However, if you want to get the results to your JDBC interface program, you should create a function and use ref cursor (see sample program 3 for the use of ref cursor).
Report the monthly sale information for any given product. For example, you can use a procedure, say report_monthly_sale(prod_id), for this operation. For the given product id, you need to report the product name, the month (the first three letters of the month, e.g., FEB for February), the year (4 digits), the total quantity sold each month, the total dollar amount sold each month, and the average sale price (the total dollar amount divided by the total quantity) of each month. Only need to list the information for those months during which the given product has actual sales.
Add tuples to all tables with input from the keyboard except the logs table (The logs table will be populated by the application automatically). In this project, you are only required to implement procedures to add tuples into the purchases table and the products table. As an example, you can use a procedure, say add_purchase(e_id, p_id, c_id, pur_qty), in your package to add a tuple in the purchases table, where e_id, p_id, c_id and pur_qty are parameters of the procedure. Note that for this example, the pur# of any newly added purchase should be automatically generated by your sequence. In addition, total_price should be computed based on the data in the database automatically and ptime should be the current date (use sysdate).
Add a tuple to the logs table automatically whenever any table is modified. To simplify, you are only required to consider the following modifications (events): (1) insert a tuple into the purchases table; (2) update the qoh attribute of the products table; (3) update the visits_made attribute of the customers table, and (4) insert a tuple into the supply table. When a tuple is added to the logs table due to the first event, the table_name should be “purchases”, the operation should be “insert” and the key_value should be the pur# of the newly inserted purchase. When a tuple is added to the logs table due to the second event, the table_name should be “products”, the operation should be “update” and the key_value should be the pid of the affected product. When a tuple is added to the logs table due to the third event, the table_name should be “customers”, the operation should be “update” and the key_value should be the cid of the affected customer. When a tuple is added to the logs table due to the fourth event, the table_name should be “supply”, the operation should be “insert” and the key_value should be the sup# of the newly inserted supply. Adding tuples to the logs table should be implemented using triggers. You need to implement four triggers for this task, one for each event.
Before a purchase is actually made (i.e., before a tuple is added into the purchases table), your program needs to make sure that, for the involved product, the quantity to be purchased is equal to or smaller than the quantity on hand (qoh). Otherwise, an appropriate message should be displayed (e.g., “Insufficient quantity in stock.”) and the purchase request should be rejected.
After adding a tuple to the purchases table, the qoh column of the products table should be modified accordingly, that is, the qoh of the product involved in the purchase should be reduced by the quantity purchased. If the purchase causes the qoh of the product to be below qoh_threshold, your program should perform the following tasks: (1) print a message saying that the current qoh of the product is below the required threshold and new supply is required, (b) prompt the operator (user) to order supply for the product (need sid and quantity from keyboard, should show the minimum value for quantity such that quantity + qoh > qoh_threshold to the user so the user can enter a number that is equal to or larger than the minimum) , (c) insert the supply information into the supply table as a new tuple (use sysdate for sdate), (d) increase qoh of the product by the quantity ordered, and (e) print another message showing the new value of the qoh of the product. In addition, the insertion of the new tuple in the purchases table may cause the visits_made of the involved customer to be increased by one if the purchase is made on a new date and the last_visit_date may also have a new date. Use triggers to implement the update of qoh, the printing (displaying) of the messages and the updates of visits_made and last_visit_date.
You need to make your package user friendly by designing and displaying appropriate messages for all exceptions. For example, if someone wants to find the purchases of a customer but entered a non-existent customer id, your program should report the problem clearly.
Database and the tables are created according to the given schema and relationships. All the procedures, triggers and cursors are created to allow the Java Classes to call using JDBC.
Java Classes are divided into controllers, data-access object classes, models and utility classes for modularity. Utilty class can establish connection and close connection to the database. Models represent the sql tables as templates and are used to display data on the web page. DAO classes run the queries using the PL/SQL procedures to fetch data into the models. Whereas Controller gives access to the web pages to request any operation on the database.
Java Server Pages are used to serve the views of each web page.
The work done here may only be used as reference material, not to be submitted as your own, with or without edits.
Copyright © 2017