Grocery Store Database

Introduction

Florida Food Mart is a medium size grocery store located in Tampa, Florida. The store and management offices are housed in a 20,000 square foot facility on the North West side of Tampa. There are approximately 45 employees consisting of 20 Cashiers, 10 Stock Clerks, 8 Bagging Associates, 3 Shipping and Receiving Clerks, 3 Store Managers, 1 Technician, 2 Purchasing Agents, 1 Accountant, 1 Marketing Director, 1 Vice President, and 1 President.

The Database Team has been assigned the task of upgrading Florida Food Mart's outdated inventory processing system by designing and implementing a state of the art relational database management system utilizing MS Access integrated with Florida Food Marts present point-of-sale (POS) software.

The Database Team has agreed to have a fully functional system in place by December 1, 2002. A preliminary budget of $75,000.00 has been approved and allocated to this project.

Scope

The Database Team proactively worked on the development of the Entity Relationship Diagram (ERD). The ERD is being designed as a management tool to assist with the development of the Team's Grocery Store Inventory System. The Grocery Store Inventory System is to be utilized by four main sections: purchasing agent's ordering merchandise, merchandise being received and placed in storage, customer purchasing merchandise, and accounting paying invoices and calculating profit and losses. Learning Team B has established the following methodology that will be followed throughout the project.

* Establish a scope of work

* Analysis of current system

* Analysis of future system

* Cost analysis

* Requirements documentation

* Database Entity-Relationship model

* Detail design of database system

* Testing

* Implementation

* Maintenance

Purpose

The purpose of the grocery inventory system is to track inventories, sales, and the calculation of profits and losses to effectively manage a grocery store. This inventory system will allow for the close monitoring of many different aspects of the inventory process including shipping, receiving, and invoicing. This will, in turn, allow the grocery store to take a more active role in the inventory process, which will allow employees to become more effective and productive due to this automation. We feel that with this grocery store inventory system it will allow for the increased efficiency, increased productivity and increased accountability in the inventory process.

Assumptions

In addition to the business rules that constrain the design of the inventory system, the design team has made several assumptions regarding the functionality and implementation of the inventory system. The inventory system will use the vendor file currently maintained and used by accounting for the payment of invoices. The invoicing process will be integrated with the existing payables process. The current POS equipment will provide the sales receipt transactions to the inventory system. Every invoice from a vendor will reference the PO number and associate the invoice line items with the PO line items. Every receipt from a vendor will reference the PO number and associate the invoice line items with the PO line items. Items that are received will be properly received against the PO, line item that they were ordered against. No additional computing hardware or infrastructure will be necessary since existing computer systems and the placement of those systems is sufficient to support the inventory project. Microsoft Access, which is currently being used to provide accounting support, will be used for the inventory system. The personnel currently involved in the manual processing of the inventory function will be retrained to support the automated inventory system. The automated input process will reduce the time needed to process purchase orders and receipts. The discovered business rules will remain in force throughout the design and implementation of the project, which will allow for a minimum development time in a stable environment.

Cost Analysis

The following figures represent the cost analysis of current inventory operation versus the anticipated costs for the automated inventory system:

Present inventory processing expense:

* $20,000.00/Month

* $240,000.00/Year

Estimated inventory processing expense after completed database implementation:

* $14,000.00/Month

* $168,000.00/Year

Estimated cost to develop and implement new database management system:

* $75,000.00/Total

Cost benefit estimated to be achieved after 12 months

Estimated monthly cost savings after the first year:

* $6,000.00

Estimated annual cost savings after the first year:

* $72,000.00

Estimated cost savings in five year after the first year:

* $360,000.00

Based on this cost analysis, Florida Food Mart management has decided that the initial investment of $75,000.00 for the development, and implementation of a new database management system is in the best interest of the company. This system will facilitate increases in productivity that will result in a substantial improvement to the company's bottom line.

Business Rules

The Database Team has established some business rules as it pertains to the grocery inventory system. The following list is a comprehensive list of the rules:

* An employee can only be attached to one department

* A purchase order (PO) must be authorized by a designated employee

* A vendor can have an account, even though they may not have supplied the company in some time

* PO may only have one shipping method

* A PO can only have one