Unformatted Attachment Preview
Project Scenario #3 Restaurant
Your client owns a fine dining restaurant and is looking to modernize their data processes. They want to
integrate their data for the following areas of their restaurant.
Employees. They need to keep track of their employees (current and former) including contact
information (name, address, phone number, email address), wages, employment dates (start
and end dates), and employee type (wait staff, kitchen, management, etc.)
Employee scheduling. The client needs to track which shifts each employee works each week.
Shifts are not always the same times each week, so you need to track the start and end time
along with the day for each shift for each employee. An employee can have multiple shifts each
week and could even have multiple shifts during the same day. The first shift begins at 10am
and the last shift ends at midnight each day.
Menu Items. Each menu item has a name, description, category (e.g., beef entrees, pasta
entrees, soups), price, and can be made up of one of more food items (e.g., the Rib-eye dinner is
comprised of a rib-eye steak, baked potato, asparagus).
Menus. A menu is made up of multiple menu items. Examples of menus are Summer Lunch
Menu 2015, Spring Dinner Menu 2016, and Christmas 2016 Season Menu. Each menu can
change from time to time so you need to also track the start and end dates each menu was
Table Sales. This is the purchase made by each table. Data that needs to be tracked is date and
time of the sale, primary wait staff, table number (tables 1 – 30), number of people at the table,
and the menu items purchased.
You need to populate your database with sample data. Your sample data must include
At least 10 employees, 5 wait staff and 5 others
Enough data in the remaining tables to cover at least four weeks in the restaurant for schedules
Recommendation. Create the data in Excel and export to a CSV file. You can load the CSV file into the
appropriate tables for your database.
Create a stored function that returns the total weekly sales for a given employee for a given week (i.e.,
pass the employee and the week of the year as arguments, the function should return the total sales for
that employee for that year). [Stored Function]
Create a stored procedure that adds a shift for a particular employee.
Arguments. Employee, date, start time, end time
Results. The new shift is added to the schedule
• The new shift should not overlap an existing shift for the employee.
• No employee should work more than 12 hours on a given day
• (Bonus) No employee should work more than 60 hours during a given week
• Create a report using the stored function you created abo e that shows the employee with the
most sales for each of the four weeks.
• A report showing all employees and their total hours worked per week for at least the 4 four
• Based on the scheduling data show the total hours worked by each employee for a given month.
• Show the weekly sales for the entire restaurant (show the year, week of the year and total sales
for all weeks)
• Create an account for a wait staff member. This account should have read-only access to all
data EXCEPT for the wages field in the employees table. This account should not be able to read
that field at all.
• Create an account for the head chef. This account has the same access as the wait staff, except
it should be able to change the menu items and menus.
• Create an account for the scheduling manager. This account should have read access to all data
and be able to make changes to all fields in the employees and scheduling tables.