Food Material Costing:2011 Spring Home

From Openitware
Jump to: navigation, search

Contents

Project Description

The project description states the purpose of the project, introduces the project's sponsor, and summarizes in an abstract what problem the project solves and why the problem is important, what the project will accomplish and how it will do so, and what results are expected and what implications these results have. The role assumed by the developer that is responsible for the project description is the System Analyst.

Purpose

Abstract

Title
Food Material Costing
Authors
Alex M., Bego T., John M., Matt V., Mike M.
Project Sponsor
Steve

Motivation

What problem are we solving and why is it important? 

In this project, we will be creating a relational database for a restaurant chain. The main goal is to create a back-end to examine the costs of the raw materials, or ingredients to make the food. The motivation for this project is that the restaurant chain currently has no means to examine the costs of its raw materials; this relational database will allow them to do so.

Objectives

What will the project accomplish? Concretely!

The primary objective of this project is to create a workable relational database with sample data that can be queried and made sense of. The DB Administrator will be able to get procurement information for a given date and see the cost of any raw material bought for that given date.

Methods

How do we go about solving the problem? What do we actually do to arrive at a solution and get the final results?

We began to solve this problem by developing a relational model and normalizing the user view that we were given. Upon completion of this project, this database will be able to store information and be queried to show the appropriate information to allow this company to examine the costs of raw materials for a given time period or given item.

Results

What has the project accomplished?

In finality, this database will allow this company to be able to examine the costs of the raw materials that they buy, and thus, allow them to make better decisions as far as how much they buy, whom they buy from, and when they buy. This will save the company much wasted money by buying the raw materials when certain items are cheaper, or more readily available, and will produce greater efficiency.

Conclusion

What are the implications of the results that were obtained?

Activities

Team Members

Roles and Responsibilities

Note from Mihaela. The project is missing the following: brief description of the conceptual model diagram and definitions of the conceptual model entities; brief description of the relational model diagram, definitions of the relational model entities with their attributes, and summary of the relational model relationships; parent-of and child-of dependency diagrams with brief descriptions (why do we need them?); normalization narrative; and database queries. I'm assigning the missing elements as follows: Mike does the missing descriptions, definitions, and summary for the conceptual and relational models. Bego writes the normalization narrative and creates the dependency graphs and writes their brief descriptions. Everybody writes queries.

Alex
  • Primary role: System Analyst, SQL Programmer
  • Secondary role: Team Leader, Wiki Editor
  • Responsibilities: Project description, SQL script to create the database tables and constraints, preparation of team agendas and minutes, SQL queries, project wiki set-up.
Bego
  • Primary role: DB Designer, SQL Programmer
  • Secondary role: IT Support Engineer
  • Responsibilities: Normalization of the logical model in the database design, SQL queries, Google group set-up.
John
  • Primary role: DB Designer, SQL Programmer
  • Secondary role: Proxy client (developer who stand in for the real client - with sufficient knowledge of the application domain to answer clarification questions from developers).
  • Responsibilities: Logical model UML class diagram, SQL script to populate database with sample data, SQL queries.
Matt
  • Primary role: System Analyst, SQL Reviewer, SQL Programmer
  • Secondary role: IT Support Engineer
  • Responsibilities: Describe users views and system requirements. Maintain structure and organization of the project's Subversion central repository. Debug SQL scripts. Write SQL queries.
Mike
  • Primary role: DB Analyst, SQL Programmer
  • Secondary role: IT Support Engineer
  • Responsibilities: Conceptual model UML diagram, SQL queries, Google Code hosted project set-up.

Team Meetings

For each class the team leader prepares the meeting agenda. Minutes are taken during the meeting and, along with the agenda, posted on to Team Meetings wiki page.

Work Products

System Requirements

  • A Relational Database is to be designed.
  • The Database is required to hold the cost information for bulk product bought for a single store.
  • It is required to have queries that can pull up desired information in lists.
  • It must be able to use those lists to create an invoice of the cost of raw materials to better help with decision making.
Functional Requirements
  • Provide cost information of specified raw materials
  • List materials needed for specified menu item
  • List which stores use a specified material or menu item
  • List vendors that supply a specified material or menu item
  • List material costs as they apply to various components or products
  • Identify costs of raw materials by ingredient or sub ingredient
  • Correlate raw materials with finished products
  • Allow access to information from a management portal
  • Provide profit loss information such as percent yield from food items and spoilage
  • Track lack of orders or over-ordering of food items
  • Compute gross margins by menu item, store, day of week, meal time
Non-functional Requirements
  • System must be run on a server containing XAMPP components
  • System runs on open source tools with an open source license

Database Analysis

Conceptual Model Diagram

Dbanalysis.png

Database Design

MenuItem contains many PlateItems, each PlateItem is the marriage of a particular Food with an offering on the menu. This table will be auto-generated at menu item creation when foods are added to that menu item as an offering. This means a single food (ex. fried shrimp) can appear on multiple menu items but be prepared identically.

Food is the logical center point of the menu-end of the database, it is between two intersection tables and at the base level is the first point of data entry for creating menu items. Foods are made from any number of Ingredients. In this case Ingredients are an auto-generated intersection table which are unique components of a particular Food, though the same raw materials may go into similar ingredients for multiple dishes. RawMaterials is the most granular form of food and is how items are purchased.

Procurement is the table between RawMaterials and the Vendor, and each tuple is a single line item for an invoice. Each shipment must be recorded in order to keep accurate cost information, HOWEVER the database does not intrinsically rely on current data so profitability studies can be done with market changes by creating a query which will report old pricing data in order to give management an easy way to determine new costs without committing.

Vendor and Store should be self-explanatory. StoreVendorRelationship is created as an intersection table to describe the particular relationship between any number of vendors and any number of stores. Contacts are stored relative to this particular table as a company may have different points of contacts for different stores as they are in different regions.

Relational Model Diagram

FoodMaterialCosting.png

Normalization

A brief explanation of normalization can be found on here.

UserView: MenuItemDescription, MenuDateAdded, FoodDescription, RawMatsQty, RawMatsDescription, ProcurementCost, ProcurementDate, ProcurementQty, WastePercentage, ExpirationDate, UnitMeasure, VendorName, ContactFirstName, ContactLastName, ContactAddress, ContactPhone, ContactEmail, StoreLocation, StoreAddress, StorePhone, StoreManager.

First Normal Form:

  • Menu: MenuID (PK), MenuDescription.
  • Menu item: MenuitemID (PK) MenuID (FK), MenuItemDescription, MenuDateAdded, MenuID (PK, FK), FoodID (PK, FK)
  • Food: FoodID (PK, FK), RawMatID (PK, FK), FoodDescription,
  • Ingredient: RawMatID (PK, FK), RawMatsQty, RawMatsDescription.
  • Procurement: ProcurementID (PK), IngredientID (FK), ProcurementCost, ProcurementDate, ProcurementQty, WastePercentage, ExpirationDate, UnitMeasure, VendorID (FK). VendorID (PK), VendorInfo.
  • StoreVendorRelationship: StoreID (PK), VendorID (PK, FK), ContactID (FK) StoreLocation, StoreAddress, StorePhone, StoreManager.
  • VendorContact: ContactID (PK), ContactFirstName, ContactLastName, ContactAddress, ContactPhone, ContactEmail.
  • Vendor: VendorID (PK), VendorName, VendorAddress, VendorPhone.

Second Normal Form:

  • Menu: MenuID (PK), MenuID (FK), MenuDescription.
  • Menu item: MenuitemID (PK), MenuItemDescription, MenuDateAdded, MenuID (PK, FK), FoodID (PK, FK)
  • Food: FoodID (PK, FK), FoodDescription,
  • Ingredient: RawMatID (PK, FK), RawMatsQty,
  • RawMaterials: RawMatsID (PK), RawMatsDescription.
  • Procurement: ProcurementID (PK), RawMatsID (FK), ProcurementCost, ProcurementDate, ProcurementQty, WastePercentage, ExpirationDate, UnitMeasure, VendorID (FK). VendorID (PK), VendorName, VendorAddress, VendorPhone.
  • StoreVendorRelationship: StoreID (PK), VendorID (PK, FK), ContactID (PK), ContactFirstName, ContactLastName, ContactAddress, ContactPhone, ContactEmail. StoreLocation, StoreAddress, StorePhone, StoreManager.

Third Normal Form:

  • Menu: MenuID (PK), MenuID (FK), MenuDescription.
  • Menu item: MenuitemID (PK), MenuItemDescription, MenuDateAdded.
  • PlateItem: MenuID (PK, FK), FoodID (PK, FK)
  • Food: FoodID (PK), FoodDescription.
  • Ingredient: FoodID (PK, FK), RawMatID (PK, FK), RawMatsQty.
  • RawMaterials: RawMatsID (PK), RawMatsDescription.
  • Procurement: ProcurementID (PK), RawMatsID (FK), ProcurementCost, ProcurementDate, ProcurementQty, WastePercentage, ExpirationDate, UnitMeasure, VendorID (FK).
  • Vendor: VendorID (PK), VendorName, VendorAddress, VendorPhone.
  • StoreVendorRelationship: StoreID (PK, FK), VendorID (PK, FK), ContactID (FK).
  • Store: StoreID (PK), StoreLocation, StoreAddress, StorePhone, StoreManager.
  • VendorContact: ContactID (PK), ContactFirstName, ContactLastName, ContactAddress, ContactPhone, ContactEmail.

Entity Dependency Diagrams

Parent Dependency Table

Parent-child relationships are described here from the top-down respectively. Important in the creation of tables because parents should be created first as they contain primary keys which must exist first before becoming foreign keys in child entities. ParentTablesV2.png

Child Dependency Table

Child-parent relationships are described here from the top-down respectively. These are important in the removal of tables. Child tables need to be removed first along with their foreign key constraints. ChildTablesV1.png

Implementation

Code Base

SQL scripts and design diagrams are stored on the project's central repository on the Google Code site at food-material-costing.

Query Examples

Here's some examples of queries for this database:

View the cost for each food item:

+----------------------------+-------+
| Food                       | Cost  |
+----------------------------+-------+
| steamers, cooked           |  3.13 |
| lobster salad roll, 8 inch | 1.404 |
| french fries, side order   | 0.224 |
+----------------------------+-------+

And viewing the sources of ingredients and any expiration dates:

+------------------------+---------+-----------------+------------+
| Item                   | Invoice | Expiration Date | Vendor     |
+------------------------+---------+-----------------+------------+
| steamer clams          | 11935   | 2011-06-12      | Acme Foods |
| 8 inch sub roll        | 11935   | 2011-05-10      | Acme Foods |
| precooked lobster meat | 11935   | 2011-05-26      | Acme Foods |
| mayonnaise             | 11935   | 2012-08-01      | Acme Foods |
| lettuce, shredded      | 11935   | 2011-05-15      | Acme Foods |
| potatoes               | 11935   | 2011-05-18      | Acme Foods |
+------------------------+---------+-----------------+------------+

View of which materials, and in what quantity, are needed to make a particular food:

+----------------------------+------------------------+----------+
| Food                       | MaterialsUsed          | Quantity |
+----------------------------+------------------------+----------+
| steamers, cooked           | steamer clams          |        1 |
| lobster salad roll, 8 inch | 8 inch sub roll        |        1 |
| lobster salad roll, 8 inch | precooked lobster meat |       12 |
| lobster salad roll, 8 inch | mayonnaise             |        8 |
| lobster salad roll, 8 inch | lettuce, shredded      |        3 |
| french fries, side order   | potatoes               |        8 |
+----------------------------+------------------------+----------+

View of vendors by first and last name, and which materials they supply:

+-----------------+----------------+------------------------+
| VendorFirstName | VendorLastName | RawMaterialDescription |
+-----------------+----------------+------------------------+
| Roger           | Random         | steamer clams          |
| Roger           | Random         | 8 inch sub roll        |
| Roger           | Random         | precooked lobster meat |
| Roger           | Random         | mayonnaise             |
| Roger           | Random         | lettuce, shredded      |
| Roger           | Random         | potatoes               |
+-----------------+----------------+------------------------+