Project Report

The Database system is used as a new alternative to the current file based legacy system of a Sporting Goods Store. The store has a strong physical presence in Massachusetts as well a newly established online store. This detailed centralized database system has a wide-ranging functionality and it comprises of Employee Information, Customer Information, Order Details, Inventory Details and Store Locations. Using this information, the objective is to provide statistics based on the data like the most profitable products, best performing employees and store locations such that the business processes can be optimized to maximize efficiency.

1) Order_Amount_Calculation

Order_Amount is calculated based on Order Quantity and Product Price and loaded into the table.

2) Invoice_Calculation

Invoice amount is calculated using the Order Amount, Tax and Discount and loaded into the table.

3) Top_Employee

It selects the top employee based on number of orders sold by employee.

4) Most_Least_Product_Sale

The procedures determines the products which are sold the most and least times.

5) Top_Customer

It determines the top customer depending on the number of orders.

1) View for to see the Counts of Online versus In-store orders

Order_Type, Total_Orders

2) View for each Customer and their respective Membership points

Customer_Full_Name, Membership_Points

1) MemberPointsCalculation

Calculates and updates the Membership points when an order is purchased by the customer based on their total Order Amount from all their previous Orders.

1) Employee_SSN

Encrypts and populates the Employee SSN.

Employee SSN data in the employee table

1) EMPLOYEE_TYPE_CHK

On Employee_Type in the Employee table to check if the value falls under Contractor, Full-time or Part-time categories.

2) EMPLOYEE_PHONE_CHK

On Employee_Phone_No in the Employee table to check if the length of the phone number is valid.

3) PAYROLL_TYPE_CHK

On Payroll_Type in the Payroll table to check if it falls under Hourly, Weekly or Monthly categories.

4) CUSTOMER_PHONE_CHK

On Customer_phone in the Customer table to check if the length of the phone number is valid.

1) On Employee_ID in Order Table.

2) On Order_ID in Order_Product Table.