• Major Features
  • Warehouse Manager
  • Store Manager
  • Company Manager
• Main Data Structures
  • Store
  • Inventory
  • Groceries
  • Employee
  • Orders
  • Groceries Ordered
• Major Functions
  • Cashier
  • Stocker
  • Manager
• Hardware Requirements
• Possible Implementation
• Minimal System
• Enhancements
• Summary
• Team Structure

This system will be able to manage the ordering of inventory between an unspecified number of supermarkets and a single grocery supplier (a warehouse owned by the supermarket chain). The management plan for the system breaks down in three ways. The first is for all of the individual supermarket managers, the second for the managers at the warehouse, and the third for the company manager who oversees both the supermarkets and the warehouse.

Warehouse Manager

• The ability to track all current inventory: at any given time, this system will allow users to obtain information about the inventory of any given store.
• The ability to generate shipment invoices: the system allows the warehouse to accept purchase orders, and shipment invoices can be generated to match the corresponding purchase order.

Store Manager

• The ability to track all current store inventory: at any given time, this system will allow users to obtain all the information pertaining to an entire store's inventory.
• The ability to generate automated inventory restocking requests: if a product is flagged for automatic reordering, the system will automatically generate a purchase orders for that item when the instore quantity of the item drops below a certain level.

Company Manager

• Security: This system will have an unspecified number of security levels. The company manager will have access to all the supermarkets. Each supermarket manager can alter the data of his/her own store only. Also, each person who uses the system will be given a password. Only the people who have correct IDs and passwords can enter the system.

Store

The Store database used to identify the information from store to store. Each store has its own Store ID, which is a number used to identifly itself. Once a valid Store ID is obtained, one can get the store order/inventory information (according to the employee's security level). Store requires the following fields:

Field name	Type	Description
Store ID (key)	integer	The ID number of the store
Store Name	character	The name of the store
Address	character	The address of the store
Contact Name	character	The name of the store's contact person
Phone Number	character	The phone number of the contact person

Inventory

The Inventory database contains the inventory information for all the stores. Individual stores will only see the inventory information for their store come up on the screen (although store managers may have read-only access to other store's inventories.) One should use the Store ID to indicate which store he wishes to check. Once the quantity of an item in stock drops below the reorder value, the alert level (flag) changes and indicates the item need to be restocked. This flag is then checked at the end of the day to determine whether more of the product should be ordered (which is done automatically if that product's status flag is set to 'A'). Inventory requires the following fields:

Field name	Type	Description
Store ID (key)	integer	The ID number of a store
Quantity	integer	Amount of stock that a store currently has
Store Price	double	Price of an item in the store
Status	character	There are 3 types of ordering A - automatic M - manual P - requires managerial approval
Alert Level	integer	Indicates when restocking is required
Target Restock Quantity	integer	How many of item will be reordered if it is an automatically reordered item

Groceries

The Groceries database contains all the possible items which stores can order (and hence, all the items found in the warehouse). Groceries require the following fields:

Field name	Type	Description
UPC (key)	integer	The Universal Product Code of an item
Description	string	The description of an item
Category	string	Type of an item
Warehouse Price	double	Item price in the warehouse

Employee

The employee database is used to store information about employees. The Password field will be unreadable. Employee requires the following fields:

Field name	Type	Description
Name	string	The employee's name
Employee ID	integer	The employee's ID number
Password	string	The password used by the employee
Security Level	integer	The security level of the employee
Store ID	integer	The store the employee works at
Position	string	The employee's position

Orders

The Orders database contains the order information for all the stores. As with the inventory database, when an employee brings up the orders, only the orders for their store will appear. The warehouse will have access to the orders from all the stores. An Order ID with Store ID determines the order information of a particular store. The database also contains the information of the employee who made the order. An new order is added to the Orders database automatically (if a product level drops too low) or after the manager of the store makes or approves an order. Automatic ordering will be performed at the end of the day. Generally, items requiring managerial approval to order would not have automatic ordering status. The status of an order can be: pending, filled, received or incomplete. Orders requires the following fields:

Field name	Type	Description
Order Number (key)	Integer	The order number
Date Ordered	string	The date of order
Employee ID	integer	The ID number of the employee making the order
Status	character	The status of the order

Groceries Ordered

The Groceries Ordered database used to determine the quantity ordered of a particular item and the quantity received of a particular item. Once the Store ID is determined in the Orders database, we know what and how many items a store ordered. Groceries Ordered requires the following fields:

Field name	Type	Description
Order Number (key)	integer	The order number
UPC	integer	The Universal Product Code
Quantity Ordered	integer	The number of items ordered
Quantity Received	integer	The number of items received

Security

This table contains the security level information.

Field name	Type	Description
Security Level (key)	string	The security level of an employee
Category Access	boolean	Determines whether an employee has access to that category

Since GUIDOs is designed as an interactive system, the functions must allow the users to easily carry out their duties. These duties, however, are different for each position within the grocery service. The three basic positions in the supermarket are cashier, stocker and manager. However, manangers can create as many positions as they need. Here is a sample setup that could be implemented in GUIDOs. In this sample the positions are hierarchical, in which every position can access functions for position below it. However, this may be tailored to the individual employee.

Cashier

• Selling an item: this requires entering the UPC and quantity/volume of an item, which will automatically decrement the store's inventory of that item by that quantity/volume.
• Reading/searching the databases: if needed, the cashier may search though the inventory, grocery or orders database. Note that this is read-only access.

Stocker

• Filling an order: this requires entering the UPC and quantity/volume of each item ordered.
• Receiving an order: this requires changing the status of an order, and possible changing the number of items received within an order.
• Reading/searching databases: this function allows the stocker to search though the orders, inventory or groceries database.

Manager

• Adding a grocery item to the database: this requires that the manager enter the UPC for the new item, then enter the appropriate attributes (price, description, etc.)
• Modifying/deleting a grocery item: this function allows the manager to modify grocery attributes after entering a valid (existing) UPC. He/she may also delete the item.
• Adding an employee: this function allows the manager to enter a new employee into the database. The system will assign a unique employee ID number, after which the manager enters information about the employee.
• Modifying/deleting an employee: this function allows the manager to modify or delete an employee from the database, after providing the correct employee ID number.
• Reading/searching databases: this function allows the manager to search any of the databases.

In addition, the system will implement a password system restricting access to employees of the grocery. The password is only required upon entry to the system.

Another major function of GUIDOs will be the automatic ordering of groceries. Every time an item is purchased, the inventory is automatically adjusted. When the inventory of a grocery item falls to a given level, the system will automatically generate an order to restock the item.

The system will be running off of a series of workstations that will be networked off one server. The server will contain the all the databases necessary for the system to operate. This will be remotely and frequently updated from the workstations. The server will reside in the warehouse for access and maintenance. Since it is essential to prevent from the loss of data, there will be a second hard drive with mirrored information of the primary drive to ensure that the failure of the primary drive will not impede operations until the drive can be replaced. Also, a tape back-up will be installed on the server.

The workstations will be installed into the warehouse and each of the supermarkets that are connected to this system.

The implementation depends on what kind of platform and language that will be used.

Since a PC based networking system is relatively easy to use and less costly, we assume that we will use PC based networking system.

To provide a user-friendly environment, the workstations will run the Microsoft Windows 95 Operating System. The program itself will use Microsoft Access for the databases and interfaces. This was chosen because of its ease of use for designing and building windows/forms type graphical interfaces that will be simple to use and understood by users. With this package, it would also be easier to develop and maintain the database of the system.

At the end of February, we will have the first version of the system ready. It covers the main functions of inventory management on both sides and all required interfaces. The following functions will be included in the system.

Supermarkets

• Point of Sale: update quantity of a product when it is sold.
• Ordering Requests:
  • automatic reordering of product when the quantity of the product falls below the reordering limit.
  • manual ordering of products
  • generate purchase order which will be sent to the grocery supplier when an automatic reordering or manual ordering occurs
• Receive Shipments:
  • modify the quantity of products when received
  • acknowledge the grocery supplier if the shipment is accepted
• Inventory Control:
  • modify inventory database if necessary after counting the inventory
• Management:
  • create/modify employee records
  • modify status of orders
• Query System:
  • product enquiry: prints out the information of the product
  • order enquiry: prints out the information of a specified order or all outstanding orders
• Security:
  • Full password protection and security flag system will be implemented.

Grocery Supplier

• Take orders from supermarkets: automatically generate a list of products and quantity required for each product for each order.
• Ship an order to a supermarket:
  • generate bill of laiding
  • generate invoice for the order
• Management:
  • create/modify/delete supermarkets
  • create/modify/delete product information

If everything goes well, we would probably include the following to the system.

Distinguish between an item of product that is sold or lost

Instead of directly modifying the quantity of a product when there is discrepancy between the quantity of the product on-shelf and the quantity of the product stored in the system, we will add another field to the database to record the discrepancy for inventory control purpose.

Production of hard copy reports

Our minimal system will be able to provide several reports based on the databases, but we would include several other preformatted reports which would deal with supermarket operations on a larger time scale (such as monthly, quarterly and yearly reports.)

GUIDOs is a software package which is designed to handle the ordering of inventory between supermarkets and a single grocery supplier. It is a user-friendly software package which requires minimum of data entry from users and provides the most accurate data efficiently to users. Also, security is one of the major concern of GUIDOS. Computer crime may be in the form of sabotage from disgruntled employees. In order to reduce the possibility of sabotage, GUIDOs is designed to allow only authorized people to access the system.(i.e. an authorized person needs an correct ID and password in order to access the system.) Indeed, GUIDOs is not only efficient, reliable and easy to use, but also cost-effective.

The team will tentatively be divided into five subgroups

Management:Keith Uyeno and Joe Goethals

responsible for communication with customers, communication among subgroups, team supervision and deadline enforcement

Technical Writers: (approx. 3 people)

responsible for writing up the user manual

Programmers: (approx. 6 people)

• approx. 2 people are responsible for user-interface
• approx. 4 people are responsible for database system and the integration of user-interface and database system

Program Testing: (whole team except programmers)

responsible for developing and carrying out test plan

Document Manager: Marius Zydyk

responsible for all documentation to be posted on the web

Glossary  
Main Page