This letter of engagement is a contract between systems consultants and Cocktail Café to outline a deliverable database that will augment the services provided by the client. The following will clearly describe the opportunity at hand, and present the deliverables chosen to address this opportunity.
Cocktail Café has hired system consultants to work with their existing database to help organize work systems and maximize efficiency for its businesses. Cocktail Café’s owners felt this advent of technology was necessary due to several key factors and questions. The 5W’s are below:
· Cocktail Café wants to know if they are efficiently using supplies and minimizing cost as much as possible
Cocktail Café is a unique and fun place for friends and families to get together and enjoy their favorite drinks, no matter what the flavor. Cocktail Café is looking to evaluate the offerings they currently present to their customers and look for other ways to both increase profits overall and take care of the loyal customers they have now.
1. What are the best-selling menu items in each city and what quantity of supplies are needed to support demand over time?
The management team wants to know which items are selling the best and generating the most profit. By knowing which drinks are the most popular, they can accurately stock which supplies are needed, reduce costs of over-buying, and prevent them from being in a situation where they would need to place emergency orders.
2. What are the busiest stores and hours of the day and is staffing optimal, or does it need to be adjusted?
The owners of Cocktail Café want to know which store is the busiest. They also want to know what hours of the day see the most customers. By knowing traffic trends, they can better staff each location to optimize customer satisfaction. Preventing short staffing situations will both keep customers happy and keep employees from becoming frustrated and overwhelmed.
3. Which menu items require the highest cost of supplies and could be removed from their menu offering.
The buying team at Cocktail Café would find it helpful to know which drink’s ingredient’s use the largest quantity of supplies for both inventory and budgeting purposes. Knowing how much one drink uses in each component will help plan budgets and may determine how much of other components are purchased.
1) Based on the Business Questions, generate a data mart. For this project, the data mart will look like this:(The datamart code is under DM Build/CocktailCafe DM.sql)
2) Use the bak file under OLTP to restore the Cocktail Cafe database. It has data in it already. For people who want to see the codes, it is CocktailCafe DB.sql. The codes include both the empty database and the bulk load data process.
