Martha’s
‘Mazin Catering
Pam Bolton
Database Analyst
CIS 250 Project Scope Statement
Martha’s ‘Mazin Catering
October 30, 2011
TABLE OF CONTENTS
Project
Overview
Background Information................................................................... 3
System Requirements
Problem........................................................................................... 3
Stakeholders.................................................................................. 3
Processes
– current system....................................................... 4
Information
Requirements – current system........................... 4
Strengths
– current system......................................................... 4
Weaknesses/Problems
– current system................................ 4
Objectives....................................................................................... 5
Benefits........................................................................................... 5
Alternative
Solutions.................................................................... 5
Purpose and Scope............................................................................ 5
Business Rules................................................................................... 6
Issues.................................................................................................... 6
Assumptions....................................................................................... 7
Technical
Design
Table List.............................................................................................. 6
Model Assertions................................................................................ 8
Logical Entity-Relationship Design................................................. 9
Metadata Dictionary.................................................................... 10-14
Peer
Review
Peer Walkthrough 1.......................................................................... 16
Peer Walkthrough 2.......................................................................... 17
Peer Walkthrough 3.......................................................................... 18
BACKGROUND INFORMATION
As Martha’s
business and reputation grew from a spiral notebook to track 2 or 3 events per
month to notoriety on a local PBS station, expectations for more venues escalated. Martha had trouble finding dishes, organizing
her catering jobs, and she could no longer do the work herself. Due to the fact relatives were not always the
best solution for assistance, she hired an event coordinator to handle bookings
and logistics and another employee to help with the food preparation. Within 2 years, these part time employees
became fulltime, with additional part time assistants to work the events and
assist in food preparation.
With the assistance
of Martha’s event coordinator, a spreadsheet application was created to keep
track of customers, events, and recipes. With the spreadsheet system, Martha was
challenged in finding the information she needed due to inconsistency or lack
of information and Martha is unprepared for the business demands of
growth. Martha needs to grow her
information with her business which could be accomplished with an organized and
automated system
PROBLEM
Application
being utilized cannot access and retrieve information efficiently. Information may also be lacking or incorrect.
STAKEHOLDERS
- Owner, Martha
- Event Coordinator, Betty Booker
- Food Coordinator, Cherrie
- Customers
- Other employees
Per the
scenario, the following stakeholders will not have a direct impact, but should
have a basic knowledge of the new system.
- Accountant
- Bookkeeper
- Payroll, outsourced
PROCESSES – current
system
·
Customer
initiates a phone call.
·
Event
coordinator collects information for event by:
o
Verifying
if customer information in Excel correct
o
Asking
type or theme involved customer wishes
to book
o
Location
of event, if no preference, search location & reserve
o
Date
& time of event
o
Number
of guests
o
Special
menu requests
·
Event
coordinator evaluates and searches employee
needs for event
·
Owner
and event coordinator:
o
Plan
menu based on theme or special dietary requests
o
Calculate
cost based on # of guests
o
Verify
menu, theme, location, cost with customer for approval
INFORMATION REQUIREMENTS
– current system
- Customer information
- Location
- Themes (Wedding, cocktail party, bar mitzvah,
Christmas Party, birthday, fundraisers, each holiday party)
- Event
- Employee
- Recipe
- Category
STRENGTHS – current
system
Employees
are familiar with existing system.
WEAKNESSES/PROBLEMS –
current system
The
current system has several problems:
- Difficulty in accessing
information
- Searching information is slow
- Timeliness of accessing
information while customer on phone
- No longevity or growth potential
Growth
of the catering business will just exacerbate the problems and frustrate the
employees and customers.
OBJECTIVES
Application being proposed would access and retrieve
information efficiently. Information
would be required, in such a way, as to avoid misinformation.
BENEFITS
Application being proposed would be able to:
- Save
time for customer in booking time
- Create
less stress for staff
- Equate
to customer satisfaction with better employee efficiency
- Provide
reporting, billing, marketing
- Access
and retrieve information efficiently.
- Input information
would be required, in such a way, as to avoid misinformation.
- Easier
menu planning
ALTERNATIVE SOLUTIONS
The present system
could possibly be improved with macros and additional employee training.
PURPOSE &
SCOPE
The purpose
of this project is to create an automated system to manage and track
customer information, menus, locations, themes, and catering reservations.
The
scope of this project will include
information about customers, locations, employees, recipes, themes, categories.
The project will not include accounting, bookkeeping and processing of payroll.
All other non-event related business functions
will not be included as part of
database system.
List of ingredients for recipes will not be included as part of database
system.
List of supplies will not be included as part of database system.
BUSINESS RULES
·
We cater our events.
·
Locations can be identified for many
events, but only one event on a given day and time.
·
Customers can be identified for many
events, but only one event on a given day, time and location.
·
An employees
can be assigned to only one event on a specific date and specific time.
·
Customers can have one or more events.
·
Customers will have an event, where
they have stated an event theme, date, time, number of guests menu and
location. If customers do not have a
preference for a location, suggestions for an optional location will be
recommended.
·
Logistics and specific rules are
maintained for each location.
·
An
event can have only one theme.
·
An
event must have a date and time.
·
Guests
per event can be one or more.
·
Recipes
can be assigned to one or many events.
·
A
recipe must have a designated category.
ISSUES
The following are issues that need to be
addressed:
·
What is the time frame for conversion?
·
Who will provide conversion of
information from old system to new system?
·
Who will train employees on new
system? Is there ‘position’ specific
training required?
·
Are vegan,
vegetarian and kosher the only categories of menu items you provide?
·
Customer Number on event detail form:
o Is
it the same as customer ID?
o How
is it derived? What additional information
required, if any?
·
How is the event price quote
calculated? Is it calculated outside the
system? Is additional information needed
to include inside system?
·
Is the cost field on the event detail
report calculated based on number of servings times
price per serving?
·
How is the number of servings for a
recipe determined? Is it based on number
of menu items per course? Is it based on
experience? Will number of serving be
included in database?
·
Is decorating included as part of your
services?
ASSUMPTIONS
·
CustomerNumber
on the event detail report will be an additional system generated number that
identifies the customer to an event, but does not require an event id until the
event has been confirmed and the required deposits paid.
·
EventPriceQuote,
being calculated outside database system, is input after the preliminary
information about the event is determined (recipes, location, etc.).
·
EventPriceQuote
will include requirements for staffing, location, menu, and all administrative
costs.
·
The
EventPriceQuote is not required at the time of the EventBookDate.
·
EventBookDate
is the date the information was received from the customer about the event
likely to take happen.
·
Cost
field on the event detail report is a calculated field not included in the
database.
·
NumberServings
is based on number of menu items for each course (hors d’oeuvre, main course,
dessert) and previous experience.
·
Cost
is calculated by number of servings times price per serving
Martha’s ‘Mazin Catering Table List (as of 10/29/2011)
|
NAME |
TYPE |
DESCRIPTION |
|
Category |
Validation |
Category is utilized to organize recipes into menu items – vegan, vegetarian,
kosher. |
|
Customer |
Data |
Customers are the business clients.
Customers hire us to provide an event.
Customer table keeps track of name, address, phone number, email for
billing and future marketing. |
|
Employee |
Data |
Employee is staff.
Information would provide salary, specific positions available to work
and job responsibilities. |
|
Event |
Data |
Event is specific details about types. Event provides history and specifics that
can be utilized as a model for other events. |
|
Event_Employee |
Linking |
Event_Employee is an intersecting table to provide information for
which employee(s) is available to work which event. |
|
Event_Menu |
Linking |
Event_Menu is an intersecting table to provide information for which
recipe(s) could be utilized to provide the menu for a particular theme and/or
event. |
|
Location |
Data |
Location is information about where an event can take place. Includes requirements of # in party,
facility size, table sizes, chairs, and themes potentially available. |
|
Recipe |
Data |
Recipe is listing ingredients for a menu item. Recipe table would provide category, cost
per serving, description and specifically to assist in menu preparation. |
|
Theme |
Validation |
Theme is a type of event. Theme
table would give requirements and specifics for particular venue. |
MODEL ASSERTIONS
·
Each customer may be
related to zero or more events and an event must be related to only one customer.
·
Each event may be
related to zero or more event_employees and an event_employee must be related to only one employee.
·
Each location may be
related to zero or more events and an event
must be related to only one location.
·
Each theme may be
related to zero or more events and an event must be related to only one theme.
·
Each employee may be
related to zero or more event_employee and an event_ employee must be related to only one event.
·
Each recipe may be related
to zero or more event_menus and an event_menu must be related to only one event.
·
Each category may be
related to zero or more recipes and a recipe must be related to only one category.
Logical
Entity-Relationship Design
Martha’s ‘Mazin Catering Metadata Dictionary
|
Entity Name |
Attribute Name |
Definition |
Domain Constraints |
Referential Integrity Constraints |
|
CATEGORY |
CategoryID |
Food grouping for
recipes called category |
PK(Primary Key) |
|
|
|
CategoryName |
Name of recipe
groupings |
Required. Non-unique Lookup: valid values = “vegan”, “vegetarian”,
“kosher”. |
|
|
CUSTOMER |
CustomerID |
Identifier for each
customer |
PK(Primary Key) |
|
|
|
CustNumber |
Identifies an event
that has been booked but has not been confirmed/finalized
or the deposit paid. |
System
Assigned. Unique. Numeric (10) |
|
|
|
CustName |
Business or
organization name of Customer. If
customer is an individual, input last name, comma, first name of individual |
Required.
Non-unique Char (15) |
|
|
|
CustAddress1 |
Primary Street
Address of the Customer |
Required.
Non-unique Char (15) |
|
|
|
CustAddress2 |
Secondary Address
of the Customer |
Not required.
Non-unique Char (15) |
|
|
|
CustCity |
City where Customer
lives |
Required.
Non-unique Char (25) |
|
|
|
CustState |
State in which
Customer lives |
Required. Non-unique Lookup: State = AL, AK,CA,
etc. |
|
|
|
CustZip |
Zip for Customer
address |
Required. Non-unique Numeric (9) |
|
|
|
CustPhone |
Primary contact
telephone number for Customer |
Required.
Non-unique Input
Mask: Phone(###)-###-#### |
|
|
|
CustEmail |
Email contact
information for customer |
Not required.
Non-unique Char (15) |
|
|
|
CustFContact |
First Name of
Customer Contact (example: would be
utilized if customer is a business or organization) |
Required.
Non-unique Char (15) |
|
|
CUSTOMER, contd |
|
|
|
|
|
|
CustLContact |
Last Name of
Customer Contact (example: would be
utilized if customer is a business or organization) |
Required.
Non-unique Char (15) |
|
|
|
CustNotes |
Information about
customer specific needs, challenges and could also include things like – slow
pay, grumpy, etc. |
Not required.
Non-unique Char (150) |
|
|
EMPLOYEE |
EmployeeID |
Unique identifier
for each Employee |
System
Assigned. Unique. Numeric (10) |
PK (Primary Key) |
|
|
EmpFName |
First Name of
Employee |
Required. Non-unique Char (25) |
|
|
|
EmpLName |
Last Name of Employee |
|
|
|
|
EmpJobTitle |
Job title for
employee according to event specifications |
Required.
Non-unique Char (25) Lookup: valid values= “Wait Staff”, “Chef”,
“Coordinator”, “Valet”, etc. |
|
|
EVENT |
EventID |
Identifier for
specific event |
System
Assigned. Unique. Numeric (10) |
PK (Primary Key) |
|
|
CustomerID |
Identifier for each
customer |
Required. Non-unique |
FK (CUSTOMER) An event cannot occur without a customer. |
|
|
LocationID |
Identifier for
location of a facility |
Required. Non-unique |
FK (LOCATION) An event cannot occur without a location. |
|
|
ThemeID |
Identifier for a
theme designation |
Required. Non-unique |
FK (THEME) An event cannot occur without a theme. |
|
|
EventDate |
Date the booked
event will take place |
Required. Non-unique |
|
|
|
EventTime |
Time the booked
event will take place |
Required. Non-unique |
|
|
EVENT, contd |
|
|
|
|
|
|
EventBookDate |
Date
the information was received from the customer about the event likely to take
happen. |
Required. Non-unique |
|
|
|
EventNoGuests |
Number of guests
attending the event |
Required. Non-unique Numeric(5) |
|
|
|
EventNotes |
Specific
information about staffing requirements, particulars of customer to theme,
event, location, etc. |
Not required.
Non-unique Char (150) |
|
|
|
EventPriceQuote |
Calculated field
outside of database. |
Not required. Non-unique Numeric (13) Values in USD Mask: $X,XXX,XXX.XX |
|
|
|
EventStaffNotes |
Specific
information about staffing requirements and maybe location of the valets or
where they are to park cars. |
Not required.
Non-unique Char (150) |
|
|
EVENT_EMPLOYEE |
EventID |
Unique identifier
for specific event |
Required. Non-unique FK (EVENT) |
CPK (Composite Primary Key) An EVENT_EMPLOYEE cannot exist
without a related EVENT record. |
|
|
EmployeeID |
|
Required. Non-unique FK (EMPLOYEE) |
CPK (Composite Primary Key) An EVENT_EMPLOYEE cannot exist
without a related EMPLOYEE record. |
|
|
|
|
|
|
|
EVENT_MENU |
EventID |
Unique identifier
for specific event |
Required. Non-unique FK (EVENT) |
CPK (Composite Primary Key) An EVENT_MENU
cannot exist without a related EVENT record. |
|
|
RecipeID |
Unique identifier
for a recipe |
Required. Non-unique FK (RECIPE) |
CPK (Composite Primary Key) An EVENT_MENU cannot exist
without a related RECIPE record. |
|
|
NumberServings |
Number of serving
of a menu item to meet the requirements of even |
Required. Non-unique CHAR (5) Mask X,XXX |
|
|
LOCATION |
LocationID |
Identifier for
location of a facility |
System
Assigned. Unique. Numeric (10) |
PK(Primary Key |
|
|
LocDescription |
The name of the
location of facility or place (park, hillside) |
Required.
Non-unique Char (25) |
|
|
|
LocType |
Type of location |
Required.
Non-unique Char(15) Lookup: valid values = “private residence”,
“church”, “office”, “meeting hall”,
“country club”, “park”, etc. |
|
|
|
LocRulesNotes |
Specific
information relative to this facility.
(ex:
deposit required, types of chairs/tables available, handicapped,
parking, etc.) |
Not required.
Non-unique Char (150) |
|
|
RECIPE |
RecipeID |
Identifier for a
recipe |
System
Assigned. Unique. Numeric (10) |
PK (Primary Key) |
|
|
CategoryID |
Food grouping for
recipes called category |
Required. Non-unique |
FK (CATEGORY) A recipe must have a designated category. |
|
|
RecipeName |
Name of recipe |
Required. Non-unique Char(25) |
|
|
|
PricePerServing |
Cost for a serving
of a recipe |
Required. Non-unique Numeric(4) |
|
|
THEME |
ThemeID |
Identifier for a
theme designation |
System
Assigned. Unique. Numeric (10) |
PK (Primary Key) |
|
|
ThemeDescription |
Type of themes
available, but not limited to. |
Required.
Non-unique Lookup valid values = “holiday party”, “bar
mitzvah”, “birthday”, “anniversary”, “wedding”, “cocktail party”,
“fundraiser”, “OTHER”, etc. |
|
I accept the above proposal.
_______________________ ___________________
Signature Date
Owner
Martha’s ‘Mazin Catering
_______________________ ___________________
Signature Date
Pam Bolton
