timer Asked: Apr 29th, 2020

Question Description

I need help creating this database. I have attached the project information.

-need to create an employees table

employees scheduling table

menus table

menus with more menus in it table

and a table with the sales

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 used. 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. Sample Data 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 and sales. 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. Stored Function 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] Stored Procedure 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 Validation • 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 Reports • 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 weeks above. • 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) User Accounts • 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. ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors