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 invoice or shipping/receiving ticket

· A stock location may contain more than one inventory item

· A PO have more than one inventory item

· A receiving document can have more than one inventory item

· A sales receipt contains the sale of more than one inventory item

· A vendor invoice contains more than one inventory item

· The shipping method is determined on the PO and it is determined by the vendor

· A PO can be split over more than one shipment

· A PO can be referenced by only one invoice

· Backorders will be carried until filled or canceled by the company

· An inventory item can have only one supplier, which is considered the preferred vendor.

Entity-Relationship Diagram

There are different versions of the ERD that exists in the real world. The Team chose a Chen ERD style format, with minor modifications for additional clarity, to use for the Grocery Store Inventory System. The illustration below encompasses the Chen ERD version as well as portraying the goals Team B plans to achieve for the creation of the inventory system.

The Team had several team meetings, and went through several ERD diagram iterations. The process that was used to develop the final entity relationship diagram required the assistance of a subject matter expert (SME) and normalization. To produce the first rough draft, Team members made suggestions and posted ERD diagrams that were incorporated into one ERD diagram document, which was maintained by Sandra. Brent was selected as the SME because of his work with Walgreen’s and their warehouse. Before developing the ERD and with the assistance of the project SME, a list of data fields was generated and categorized as either input fields, database fields, or calculated fields. The complete list of the fields is located in Attachment A. The database fields were then grouped according to function, which formed the basis for the necessary entities. Since business rules influence the entities and their relationships, the team then worked with our SME to develop the governing business rules, which are listed in the previous section. Using Microsoft Visio as our ERD modeling tool, we then created the first draft of the ERD, which contained several many-to-many relationships. At this point, the team began an iterative process of normalization, redraw, and apply the business rules.

When the business rules were applied, it was discovered that the purchase order, receiving, sales, and invoice entities had multi-valued attributes. Through the process of normalization, each of these relations was split in two relations, a main or header relation and a detail items relation. In each instance, the header relation contains information that is applicable to all the line items associated with that relation. The line items’ relation contains information that is specific to the line items only, such as Product Scan ID Number, cost, or specific quantity.

Figure 1 contains the final diagram that has been unanimously approved by all team members for the grocery store inventory system.

Data Tables

In order to create a successful database, tables (relations) with their respective fields, should be identified graphically to make sure that the data is organized in a logical manner, and relationships are properly established by identifying primary and foreign keys within the tables. Learning Team B has created the following tables to represent our database design (primary keys in red, foreign keys in blue):

Figure 2 Vendor Table

Vendor ID Primary Key

Vendor Name Name of Vendor

Vendor Address 1 Address Line 1

Vendor Address 2 Address Line 2

Vendor City City of vendor

Vendor State State of vendor

Vendor Zip Zip code of vendor

Vendor Country Country of vendor

Vendor Phone Area code and phone number of vendor

Vendor Fax Fax number of vendor

Vendor Contact Name Name of contact of vendor

Vendor Contact Title Position of contact with vendor

Vendor Tax ID Tax identification number of vendor

Vendor Email Email of vendor

Figure 3 Purchase Order Table

P.O Number Primary Key

P.O. Date P.O. Creation Date

Expected Date Expected Delivery Date

Shipping Method ID Foreign Key

Date Required Date Product Needs to be In-House

Vendor ID Foreign Key

Employee ID Foreign Key

Figure 13 Purchase Order (PO) Detail Table

Purchase Order (PO) Number Primary Key

PO Number Primary Key

Quantity Ordered Quantity of item based on order units

Quantity on Back Order Number of items on back order

Unit cost Cost per unit on item

Product Scan ID Number Foreign Key

Figure 4 Inventory Item Table

Product Scan ID Primary Key

Product Description Brief Description of Item

Quantity on Hand Current Stocking of Items

Quantity on Order Current Quantity of Items on Order

Quantity on Backorder Current Backorder of Items

Product Reorder Lead Time Time Required to Receive New Items

Wholesale Cost Cost of Wholesale Item

Retail Price Retail Price of Item

Taxable Item Is the Item Taxable Boolean Y/N

Inventory Type ID Foreign Key

Unit of Measure How Item is Counted and Inventoried

Reorder Lead Time Amount of Time Estimated for Reorder

Department ID Foreign Key

Stock Location ID Foreign Key

Figure 5 Invoice Table

Invoice No Primary Key

Invoice Date Date of Invoice

Terms The repayment terms

Due Date Date the payment is due to the vendor

Status Status of invoice, (open or closed)

Amount Approved to Pay Amount authorized to pay on invoice

Freight Charges Charges for freight or handling

Tax Tax the vendor has charged

PO Number Foreign Key

Vendor ID Foreign Key

Figure 6 Invoice Detail Table

Invoice No Primary Key

Invoice Line Item No Primary Key

Quantity Billed Number of units billed

Price Billed Unit price billed

Product Scan ID Number Foreign Key

Figure 7 Employee Table

Receiving Employee Number Primary Key

Employee Last Name Department Employee’s Last Name

Employee First Name Department Employee’s First Name

Employee Title Job Title of Employee

Employee Extension Telephone number and extension of employee

Figure 8 Sales Receipt Table

Receipt Number Primary Key

Sale Date Date sale transaction occurred

Figure 9 Sale Line Items Table

Receipt Number Primary Key

Sales Receipt Line Item No Primary Key

Quantity Sold Number of units sold

Unit Selling Price Unit price item sold for

Product Scan ID Number Foreign Key

Figure 10 Department Table

Department Name Primary Key

Department Name Department Name

Figure 11 Stock Location Table

Stock Location ID Primary Key

Stock Location Description Category Description of Stock

Product Scan ID Number Foreign Key

Figure 12 Shipping Method Table

Shipping Method ID Primary Key

Shipping Method Description Description of Shipping Method

PO Number Foreign Key

Figure 14 Receiving Document Table

Receiving Document Number Primary Key

Date Received Date PO was Received

Receiving Employee Number Foreign Key

Receiving Dock Location Location where PO is delivered

Product Scan ID Number Foreign Key

Figure 15 Receiving Line Items Table

Receiving Document Number Primary Key

Receiving Document Line No Primary Key

Quantity Received Quantity of item received based on order units

Quantity Accepted Quantity of item accepted based on order units

Product Scan ID Number Foreign Key

Figure 16 Inventory Type Table

Inventory Type ID Primary Key

Type Description Inventory type description

Conclusion

In today’s highly competitive retail environment, it is imperative that grocery stores take advantage of the latest database technology and integrate it with a POS software solution that will maximize inventory-processing capabilities. Florida Food Mart has recognized this and has decided to take action by presenting Learning Team B with the opportunity to design and implement a grocery store inventory database management solution.

In putting together this database management development plan, the Team utilized sound database concepts which included the establishment of a database development methodology, identification of business rules, creation of an ERD diagram model, listing database fields, conceptualizing database tables, and establishing relationships through the use of primary and foreign keys.

By following a predefined methodology, and establishing business rules, Learning Team B has established control of the database project so the result will meet or exceed the Florida Food Marts expectations.

By creating an ERD diagram model, the Team was successfully able to identify the entities that will the database will support, and map out all relationships relative to these entities.

The Database Team created a model of each table that will be in the database, and identified both primary and foreign keys that will establish links and form relationships between the various tables. Normalization analysis was performed on each database table. Anomalies were identified, and single tables were broken down into multiple tables to eliminate anomalies.

By utilizing sound database project management concepts, the Team will produce a fully functional database managements system that will me or exceed customer expectations.

Attachment A

Data fields

Input fields

The following input fields are those fields that are input during a transaction, such as ID number, quantity, etc. Key fields are underlined and bolded for easy recognition.

Product Scan ID Number

Vendor’s Product ID Number

Date Received

Receiving Document Number

Receiving Document Line Number

Price Billed

Quantity Billed

PO Number

PO Line Item No

Quantity Ordered

Stock Location

Department ID

Receiving Dock Location

Receiving Employee Number

Unit of Measure

Unit Cost

Quantity Received

Category ID

Date Required

Ship Method ID

Freight Charges

Quantity Accepted

Quantity Sold

Unit Selling Price

Database fields

The following database fields are those fields that are input into the database, which will be used during a transaction:

Product Description

Unit of Measure

Unit Cost

Quantity on Backorder

Quantity on Order

Quantity on Hand

Category Description

Reorder Quantity

Product Reorder Lead-time

Purchasing Employee Number

Department Name

Taxable Item

Ship Method Description

Vendor Name

Vendor Contact Name

Vendor Contact Title

Vendor Tax ID

Vendor Address 1

Vendor Address 2

Vendor City

Vendor State/Province

Vendor Zip

Vendor Country

Vendor Phone

Vendor Fax

Vendor Email

Employee Last Name

Employee First Name

Employee Title

Employee Extension

Calculated fields

The following fields are designated as calculated fields and may or may not be stored in the database.

Wholesale Price

Retail Price

Base price

Quantity on Hand

Quantity on Backorder

Quantity on Order

Month-to-Date Sales

Year-to-Date Sales

Quantity Rejected

PO Total Amount

Sales Tax

Total Sale Amount

Month-to-Date Sales Tax

Year-to-Date Sales Tax

Month-to-Date Pre-Tax Profit

Year-to-Date Pre-Tax Profit

Some of the items appear in several field categories since one transaction may be entering the information while another transaction may be retrieving it from the DB to complete the transaction, while still another process may calculate a field, but store the results in the DB to be used in a future transaction.