CIS 250

Final Project Draft: Logical Model Draft

Stuart Crome

Background Information

Background

Martha’s ‘Mazin Catering is a catering business that is expanding in the local area in which it is operating. It started as a one-person business started by Martha Mazzing, doing two or three events per month. Martha used a spiral bound notebook to track the events she catered and the recipes she used. As a result of word-of-mouth from successful events, her business expanded and she was unable to do all the work herself. She was able to get by for a time with the help of family members, but she eventually had to hire two part time employees, soon they became full time employees needed to handle the increased demand. Martha ran into problems keeping and finding information about her catering job. Her notebook was soon filled with information about events and she started to have trouble finding this information when she needed it. She moved to tracking the information on Excel spreadsheets, but this, in time, proved to be very time-consuming as the business expanded. In addition, with several employees now, information is not entered in a consistent manner.

System Requirements

Excel is not working for getting needed information. People enter information on spread sheets “in their own way”, this causes confusion. They need to be able to search previous events at the same location. This is time consuming because hundreds of records have to be scrolled through on spreadsheets.

The people involved are Martha (CEO), the event coordinator and workers handling the event bookings and logistics, people working each event, food preparation assistants, a food coordinator, and cooks.

The current process involves managing the data on spreadsheets. They have lately become so large that it is difficult to manage and access the needed information. Each recipe they use has a particular theme, and the type of event they may be used for. They also order supplies and need to keep a larger number of supplies for the most common recipes.

The information requirements include events, recipes, customers, employees, supply orders and supply vendors. All of this information requires inputs (information from events and orders as they happen) and outputs (a record of events, employees working the events, recipes used, etc.)

A strength of the current system is that Martha, and her event coordinator are familiar with Excel, the application they are using. They have an accountant and bookkeeper to handle the financial activities and the payroll is managed by another firm, leaving them free to handle the information related to the events.

The biggest weakness of the current system is that it is very time-consuming. Information on the spreadsheets is entered in an inconsistent manor by various employees causing inconsistent formatting of the data. Martha says “ . . all this extra time is cutting into the bottom line profits.”

A new database is needed to reduce the amount of time needed to track this information. Such a database could connect the information, based on how different areas of the information are related to each other.

A new system could provide data entry and output in a consistent manor and be flexible enough to output reports of the most relevant data to each employee, depending on the task they want to accomplish.

An alternative solution would be to hire another firm to handle the event details. This could be more costly financially as their business expands and begins to have more events and expand geographically.

Purpose & Scope

The purpose of this project is to create a database solution for Martha’s ‘Mazin Catering to track data related to the catering events that they plan and organize.

The scope of this project will involve further interviews with Martha and employees to cover all the information related to the events they have, and will be catering. This includes looking at how they need to track employee information, customer information, locations, the occasion of the event (theme), supplies, supply vendors and recipes.

Activities outside the scope of the project are the financial transactions and employee payroll.

Information Requirements

Events - locations, themes, date, time, number of guests, specific menu requests

Recipes (sorted be season and specific holidays) - ingredients with amounts, time to create it (including prep time), cost per serving, instructions, number of cooks and assistants needed

Customers – name, address, phone, email address, contact person

Employees – name, address, phone, email address, date hired

Supply Orders – date, item, quantity, quantity on hand

Supply Vendors – name, address, phone, email address, contact person

Ingredients - ingredient name, recipe id, quantity on hand, ingredient vendor

Recipe Ingredients - recipe id, ingredient name, quantity needed, unit

Recipe Events – theme name, theme description, recipe number

Business Rules

Process Rules

• Each event can have more than 1 employee working on it.
• They keep a larger supply of more common ingredients.
• Recipes are sorted by season – fall, winter, spring, summer.

Data rules

· Each event needs customer information, location, theme, date, time, number of guests, and any specific menu requests.

• Themes are related to recipes since recipes "pertain to a specific theme.”
• Locations have particular logistics and rules” (private home, public hall/church, country club).
• An event can have only 1 theme (wedding, cocktail party, bar mitzvah, Christmas party, etc.).

Issues

• I’m not sure how they would want to track the recipes, since this includes several different types of information such as the list of ingredients, the number of servings, the time needed to create it, the cost per serving and step-by-step instructions to prepare the dish. How would they want to access and use this information? Would they want a static specification sheet for each recipe, or one that links to other documents with further details of the recipe?

• Who will determine how many cooks and assistants are needed for each event? How is this determined? What happens if a recipe changes and there is no prior experience to make a judgment?

Assumptions

• Their business will continue to expand and they will need a database solution that will allow for the changes that are required.

• They will need to continually modify their recipe collection as the popularity of some dishes rise and fall.

• They will want to have the recipe instructions as separate text or XML files for printing.

• Tracking the employees working on each event will be limited to the primary cook responsible for each event

• Both Vendors and Customers will have a singe designated contact person that will be tracked in those entities

• They want to be able to print recipes for the cooks to follow

• They will want to print the menus for the events

• Each ingredient has only one vendor

• There is one ingredient for each recipe ingredient

• There is one recipe associated with each recipe ingredient

• Several recipes can be used in each menu item

• There is one menu associated with each menu event

• There are many menu events associated with each menu item

• A menu event can be related to many events

• There is only one menu event related to each event

• A recipe is associated with only one menu item

Final Table List

NAME	TYPE	DESCRIPTION
Events	data	The past and presently pending catering events organized by the company.
Customers	data	Customers and potential customers of the company.
Employees	data	Part-time and full-time employees of the company.
Menu	data	Lists menu items that are used to make an event menu
Theme	data	The themes used for events
Menu Events	linking	Categorizes each menu with the event it is used for
Ingredients	data	The ingredients used for recipes for events catered by the company.
Recipe Ingredients	linking	The relation of all ingredients to the recipes used at events catered by the company.
Recipes	data	Basic information about the recipes used by the company for events, identified by season of the year.
Supply Orders	data	Supply orders made by the company for catering events and daily business operation.
Supply Vendors	data	The vendors who sell ingredients and equipment to the company.

List of Attributes

Events (EventId, CustID, Location, Date, Time, NumGuests, MenuRequests, MenuItemID)

Customers (CustId, Fname, Lname, Address, City, State, Zip, Phone, Email)

Employees (EmployId, Fname, Lname, Address, City, State, Zip, Phone, Email)

Menu (MenuItemID, MenuItem, ThemeName )

Themes (ThemeID, ThemeName ThemeDesc )

Menu Events (EventID, MenuItemID, EmployID, ThemeID )

Ingredients (IngredientName, RecipeNum, QuantityOnHand)

Recipe Ingredients (IngredientName, RecipeNum, QuantityNeeded)

Recipes (RecipeNum, Season, PrepTime, CostPerServing, PersNeeded, Instructions, MealType, Servings, MenuItemID, ThemeName)

Supply Orders (SupplyId, VendorId, Date, Item, Quantity, QuantityOnHand)

Supply Vendors (VendorId, Name, Address, City, State, Zip, Phone, Email, ContactPerson)

Logical-Level Entity Relationship Diagram

Entity Type:	Events
Definition:	Events are the catering events sponsored by the company.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
EventID	The unique identifier assigned by the business for each event	Char	Unique	PK, Required
CustID	The unique identifier assigned by the business for the customer the event is being held for	Char		PK, FK, Required
Location	The building location for the event	Char		Required
Date	The date of the event	DateTime	Date Only
Time	The time of the event	DateTime	Time Only
NumGuests	The number of guests attending or expected to attend an event	Numeric
MenuRequests	Names of recipes the customer may request	Text
MenuItemID	The unique identifier for a menu item, equivalent to RecipeID Recipe table	Numeric		FK

Entity Type:	Customers
Definition:	Customers are the clients who have requested catering events and have had events catered on their behalf.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
CustID	The unique identifier assigned by the business for the customer the event is being held for	Char	Unique	PK, Required
Fname	The first name of the customer	Char
Lname	The last name of the customer	Char
Address	The street address of the customer	Char
City	The city of the street address of the customer	Char
State	The state of the city of the customer	Char
Zip	The zip code used for the street address of the customer	Numeric	Lookup List from postal service
Phone	The customer’s phone number	Numeric, (###) ###-####
Email	The email address of the customer	Char

Entity Type:	Employees
Definition:	Employees are people employed by the company who work in various areas of the business
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
EmployID	The unique identifier assigned by the business for the employee	Char	Unique	PK, Required
Fname	The first name of the employee	Char
Lname	The last name of the employee	Char
Address	The street address of the employee	Char
City	The city of the street address of the employee	Char
State	The state of the city of the employee	Char
Zip	The zip code used for the street address of the employee	Numeric	Lookup List from postal service
Phone	The employee’s phone number	Numeric, (###) ###-####
Email	The email address of the employee	Char

Entity Type:	Menu
Definition:	Menus are lists of separate menu items that are combined to make an event menu
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
MenuItemID	The unique identifier for a menu item	Numeric		PK, Required
MenuItem	The designated name of the menu item	Char
ThemeName	The name of the theme the menu item is being used for	Char		FK, Required

Entity Type:	Themes
Definition:	The themes used for events
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
ThemeID	The unique identifier for the theme of the event	Numeric	Unique	PK, Required
ThemeName	The theme or type of event	Char
ThemeDesc	A description of the theme or type of event	Text

Entity Type:	Menu Events
Definition:	Menu Events Categorizes each menu with the event it is used for
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
EventID	The unique identifier assigned by the business for each event	Char	Unique	PK, Required
MenuItemID	The unique identifier for the menu item used	Numeric		PK, FK, Required
EmployID	The unique identifier of the employee mainly responsible for the event	Numeric		FK, Required
ThemeID	The unique identifier for the theme of the event	Numeric		FK, Required

Entity Type:	Ingredients
Definition:	Ingredients are the raw materials used to create menu items at events.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
IngredientName	The unique name of the ingredient to be used	Char	Unique	PK, Required
VendorID	The unique identifier for the vendor supplying the ingredient	Numeric		PK, FK, Required
RecipeNum	The unique identifier for the recipe the ingredient is used for	Numeric		FK
QuantityOnHand	.The amount of units available for use.	Numeric

Entity Type:	Recipe Ingredients
Definition:	Recipe Ingredients matches the recipe that each ingredient is used on.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
IngredientName	The unique name of the ingredient to be used	Char	Unique	PK, FK, Required
RecipeNum	The unique identifier for the recipe	Numeric		PK, FK, Required
QuantityNeeded	The number of units of each ingredient used for the recipe	Numeric
Unit	The unit measure of the ingredient	Char

Entity Type:	Recipes
Definition:	Recipes are listings of the ingredients, amounts, quantities and instructions for creating menu items for events.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
RecipeNum	The unique identifier for the recipe	Numeric	Unique	PK, Required
RecipeName	The name of the recipe	Char	Unique
Season	The yearly season the recipe is appropriate for	Char
PrepTime	The time it takes to prepare the dish	Char
CostPerServing	The cost per serving charged by the company	Currency
PersNeeded	The number of employees needed to complete the dish	Numeric
Instructions	A link to a text or XML document with the ingredients and instructions	URL
MealType	The type of meal the recipe is typically used for	Char
Servings	The number of servings the recipe will create	Numeric
MenuItemID	The unique identifier for the menu item the recipe is used for	Char		FK, Required
ThemeName	The theme that the recipe is typically used for	Char		FK, Required

Entity Type:	Supply Orders
Definition:	Supply Orders are orders for supplies used by the company for preparation of menu items, including the ingredients used for the menu items.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
SupplyID	The unique identifier for the supply	Numeric	Unique	PK, Required
VendorID	The unique identifier of the vendor who is taking the supply order	Numeric		PK, FK, Required
SupplyType	The type of supply that is being ordered	Char
Date	The date in which the order is placed	DateTime
Item	The name of the supply being ordered	Char
Quantity	The quantity of the supply being ordered	Numeric
QuantityOnHand	The amount the company currently has in stock	Numeric

Entity Type:	Supply Vendors
Definition:	Supply Vendors are suppliers of equipment and ingredients used by the company for events.
Attribute	Definition	Data Type Constraints	Data Value Constraints	Referential Constraints
VendorID	The unique identifier of the vendor who is taking the supply order	Numeric	Unique	PK, Required
Name	The company name of the Vendor	Char		Required
Address	The street address of the vendor	Char
City	The city of the street address of the vendor	Char
State	The state of the city of the vendor	Char
Zip	The zip code used for the street address of the vendor	Numeric	Lookup List from postal service
Phone	The phone number of the vendor	Numeric, (###) ###-####
Email	The email address of the vendor	Char
ContactPerson	The primary contact person at the vendor’s location	Char

[END]