Inventory Database Management System for a Supermarket

A. Authorizing Subsystem

• Initialize company information
  • Company name, contact information
  • User domain and management setting
  • Password setting
• Initialize customer setting
  • Reminder setting, reminder of out-of-stock prompt and date-expired
  • Ordering form prototype
• Login validation
  

B. Database Management Setting Subsystem

• Data display
  • Display categorized record.
  • Sort and index.
  • Change the view ability of fields
• Edit data fields
  • Add new fields
  • Add new category
• Maintain database among various database
  • Keep on tracking and updating 
• Date-expiry prompt
  

C. Demand Prediction Subsystem

• Count sales record
  • Auto display best sales, worst sales and out-of-stock items
  • Display any record with given item code
  • Draw sales curve 
• Predict future demand
• Prepare ordering bill
  • Display suppliers information related to the product
  • Provide choice for user to decide
• Out-of-stock reminder prompt
  

D. Printing Subsystem

• Print ordering bill 
• Print store information
• Print transaction record for both customer and company 
  

E. Network support

• Allow different user to access database from different computer via Intranet
  

F. User interface

• Store- man interface
• Casher interface
• Store Keeper interface
  

[Click to Enlarge]

• Authorizing Subsystem [View]
• Database Management Setting Subsystem [View]
• Demand Prediction Subsystem [View]

• Authorizing Subsystem [View]
• Database Management Setting Subsystem [View]
• Demand Prediction Subsystem [View]

• Authorizing Subsystem [View]
• Database Management Setting Subsystem [View]
• Demand Prediction Subsystem [View]

• Entity-Relationship Diagram [View]
• Normalization [View]

Authorizing system allows different domain users to access different interface of the Inventory Database System. There are there domain users: storekeeper, storeman, and cashier. The login process will lead the users to different interface according to the different domain.

Storekeeper Domain

Storekeeper is the highest domain to access the inventory database system. It allows storekeeper to manage the whole database. Only storekeeper can be put in the domain. There are there main managements in the storekeeper interface. They are consolidated sales management, data management and setting. In the consolidated sales management window, storekeeper is allowed to view consolidated sales and update consolidated sales. In the data management window, storekeeper can manage each sub databases: product database, supplier database and product class database. In the setting windows, storekeeper can manage the user database, updater company information and set the expire-reminding-day.

Storeman Domain

There are only two main jobs for the storeman, one is to register the stock arrive, and the other is to disposal the expired products, so storeman can fully access the product database.

Cashier Domain

Cashier can only read from the product database, they are not allowed to change any attribute of the product database.

Manager Interface

Update Consolidate

1.    ‘Transaction’ table is accessed.

2.    Entries with same date and same ProductID are combined as one entry.

3.    The entries are then append to the ‘Consolidated’ table.

4.    Steps 2, 3 are repeated until all entries of  ‘Transaction’ table is parsed.

5.    Clear ‘Transaction’ table.

Cashier Interface (when a customer comes)

1.      An empty Bill is created.

2.      The items bought will be scan with a bar code reader to get the ProductID.

3.      By using the ProductID, the items’ description, unit price is extract from the inventory database.

4.      Quantity bought for the item is then keyed in.

5.      Subtotal is calculated.

6.      An new entry is entered into “Transaction’ table.

7.      Steps 2,3,4 are repeated for every type of items bought by the customer.

8.      Cash amount is keyed in and transaction type is selected as the customer pay.

9.      The Bill is filled up with Date, BillID, list of products with quantity, subtotal, total and change.

Storeman Interface

Stock Arrival

1.      An Order is selected using its OrderID (accessing ‘Order’ table)

2.      In an Order, there is a list of products ordered (POrdered).

3.      For every product ordered (Pordered), the Quantity ordered will be added to the Quantity of the corresponding entry in ‘Products’ table (with the same ProductID).

4.      The list of products ordered is cleared.

Stock Disposal

1.      ‘Product’ table is accessed.

2.      Entries of expired items are extracted form ‘Product’ table and displayed on screen.

3.      Once confirmed by store man, Quantity disposed is subtracted from the Quantity of the item in ‘Product’ table.

Main Function

l       Predict future demand for a product.

l       Handling ordering action, including supplier and quantity decision as well as ordering bill generation.

Policy

l       Products which are out of stock must be predicted and ordered.

l       Those products which have been ordered and haven’t been shipped should not be predicted and ordered.

Sub-system input requirement

l       User inputs the product ID, one at one prediction go. The ID can be select in 2 ways: from the current-day best sales or from random selection.

l       Current-day best sales selection will not display those products ID already under ordering. An out-of-stock is set if no more items remains. A SSR notation stands for Sales-Stock Ratio, which is equal to current day sales/ (current day sales + in stock value). If a product ID is select and is ordered, next time it will not be displayed in the table.

l       Random selection includes 2 ways, select from class and select from product ID list.

Sub-system output requirement

l       The system will update table POrdered only which will record ordering information.

Prediction algorithm

l       First use moving average value to filter the data. The periods to be averaged are provided for user to choose.

l       The future demand is predicted by calculating the sum of factor times the difference between current day data and any day before. The formula can be written as: An+r  =  （1-1/factor） * ( An+r-1 – An+r-2 ) + (1/factor ) * A n+r-1  

A1，A2 till An are the daily sales for history while An+1, An+2 till An+r are the predicted value in the algorithm.

Helper tables

l       BestSales_Pre , to record current day best sales, actually list from the best sales to the worst sales. It is used for ID selection.

l       AvgTemp_Pre, to hold the main data of the current product’s sales records as well as predicted values. The date should be consecutive which means even there are some days that not any of the product items are sold, the record should be there in the table, with a value 0.

l       Prediction Save, to hold the predicted data for current predicting product, include id, predicted value, ordered value, and some information of the chosen supplier. Only one record can be hold by Prediction Save.

l       Shopping Chart, when the ordering procedure for a single product is complete, system will save the record in Prediction Save into Shopping Chart and empty Prediction Save.

 System division

5 parts  

l       Select Product for Prediction: Update BestSales_Pre, select a product ID then save it to Prediction Save.

l       Get History Record: Update AvgTemp_Pre, draw a histogram.

l       Predict Future Demand: Update AvgTemp_Pre, result save into Prediction Save.

l       Ordering Check: Update Shopping Chart.

l       Generating Order Bill: Update POrdered.

 Linking Between 5 parts

l       Considering of guide for users, will warning if critical actions perform or show any error message.