OLD PROJECT, INITIAL COMMIT, GATHERING FILES FOR UPLOAD

RETAIL BUSINESS MANAGEMENT SYSTEM

How to Run/Compile

1. Extract project2_aniket_aamir_prashant.zip file.
2. Now go to project2_aniket_aamir_prashant directory using terminal.
3. Now run following command to compile java code javac *.java
4. Execute following command java RetailBusiness
5. This will open window which has all functionality.

Contributors:

• Aamir Yousuf ayousuf2@binghamton.edu
• Prashant Kolhe pkolhe1@binghamton.edu
• Aniket Rajmane arajman1@binghamton.edu

DESIGN:

GUI:

GUI has been developed using java swing components. RELATIONS:-

1. Employees: Objective- The table stores the information about employees of the retail business. Attributes of table are -Employees (eid, ename, telephone#)

2. Customers:- Objective- The table stores the information about all the customers who have purchased at least 1 product from the retail business store. Attributes of table are - Customers (cid, cname, telephone#, visits_made, last_visit_date)

3. Products:- Objective- The table stores the information about each product sold by retail business. Attributes of table are - Products (pid, pname, qoh, qoh_threshold, original_price, discnt_rate)

4. Suppliers:- Objective- The table stores the information about all suppliers who supplied products to retail business store. Attributes of table are - Suppliers (sid, sname, city, telephone#)

5. Supply:- Objective- The table stores the information about products which are supplied by the supplier. It also represent the quantity supplied. Attributes of table are - Supply (sup#, pid, sid, sdate, quantity)

6. Purchases:- Objective- The table stores a detailed information about each purchase made at retail business store. Attributes of able are - Purchases(pur#, eid, pid, cid, ptime, qty, total_price)

7. Logs Objective- The table is used to store the keep track of detailed information about operations performed on tables. Attributes of table are - Logs(log#, who, otime, table_name, operation, key_value)

STORED PROCEDURES:

1. Add Purchase Objective- To insert a detailed information about every valid purchase into purchases table. Usage- Input parameters for the procedures are employee id, product id, customer’s id and the quantity of product being purchased. The procedure calculates total price for the transaction based on quantity, unit price and discount rate of product. The procedure stores date and time of purchase as system time. Each purchase is stored with unique purchase number which is generated by sequence.

2. Add Product Objective- To add a new product that will be sold by retail business store. Usage- Input parameters for procedure are unique product id, name of product, quantity of product available, threshold quantity, original price and discount rate. Threshold quantity represent the quantity below which a new supply of product is required.

FUNCTIONS:

1. Generate Monthly Sale Report of Product Objective- To generate a report that shows monthly sale of product. Usage- Input parameter for function is a product id for which report is supposed to be generated. For the given product id report gives name of product, month in which it was sold, year of sale, total quantity sold in the month, total amount after sale and the average price at which product was sold in that month.

TRIGGERS:

1. To Check Sufficient Quantity For Purchase Objective- To restrict purchase with quantity more than available. Usage- Before a purchase is made available quantity is checked, if it is less than required quantity purchase will not be allowed.

2. To Update Quantity of Product Objective- To modify quantity attribute of products table after a valid purchase is made. Usage- After a valid purchase is made by a customer, purchased quantity will be reduced form available quantity.

3. To Update Visits Made and Last Visit Time of Customer Objective- Update Customers last visit time and visits made by customer. Usage- When a customer successfully do a purchase, visits made attribute of customers table will be increased by 1 and last visit time will be updated to time of purchase. Visits made will be incremented only once for each day.

4. To Update Logs Table After Visits Made of Customers Table Updated Objective- Populate Logs table when visits made attribute of customers column is updated. Usage- A tuple will be added to Logs table which will indicate update operation is made on customers table for the given customer id.

5. To Update Logs Table After Insert On Supply Table Objective- Populate Logs table when insert operation is made on Supply table. Usage- A tuple will be added to Logs table which will indicate insert operation is made on Supply table with the given supply id.

6. To Update Logs Table After Insert On Purchases Table Objective- Populate Logs table when insert operation is made on Purchase table. Usage- A tuple will be added to Logs table which will indicate insert operation is made on purchase table with given purchase number.

7. To Update Logs After Quantity of Products table Updated Objective- Populate Logs table when update operation is made on Products table. Usage- A tuple will be added to Logs table which will indicate update operation is made on products table with given product id.

SEQUENCES:

1. To Generate New Purchase Number Objective- Generation of a unique purchase number which can be used for insert operation on purchase table. Usage- A number will be generated that will be used as primary key for adding a purchase into purchase table.

2. To Generate New Supply Number Objective- Generation of unique supply number which can be used for insert operation on supply table. Usage- A number will be generated that will be used as primary key for adding supply into purchase table.

3. To Generate New Log Number Objective- Generation of a unique number which can be used for insert operation on logs table. Usage- A number will be generated that will be used as primary key for adding a record in logs table.

RefCursors:

1. get_employees: This ref cursor is used to provide all details about employees table, which is called from java.
2. get_customers This ref cursor is used to provide all details about customers table, which is called from java.
3. get_products This ref cursor is used to provide all details about products table, which is called from java.
4. get_purchases This ref cursor is used to provide all details about purchases table, which is called from java.
5. get_supply This ref cursor is used to provide all details about supply table, which is called from java.
6. get_suppliers This ref cursor is used to provide all details about suppliers table, which is called from java.

License

MIT