You are in charge of developing an IT solution for a new Italian restaurant named Super Linguini Brothers that is soon to open in your local neighborhood. The business owner is a first-time entreprenuer with a background as a chef and as a businessman. He has asked you to refer to him as Mr. Stagliano.

Mr. Stagliano has assembled a lawyer named Cicero, an advertising executive named Diogenes,  a general manager named Aristotle, and an accountant named Pascal. They are intending to hire managers for inventory and floor as well as additional cooks, custodians, and waiters.

The restaurant is a nice size. They are planning to have five identical registers for order input and bill paying distributed throughout the restaurant. They are hoping for all of these machines to connect to a central database. They are wanting custom-designed software built by you to manage orders and provide record-keeping. They are possibly open to having tablets for waiters to directly input orders all with the same register software. These would of course be linked to the central DBMS.

Aristotle, the general manager, has requested that the system also keep track of employees, their position, the hours they work, and their pay rates per hour. His goal is to be able to keep track of how well he is managering their hours to maximize profits and ensure employee happiness by not working anyone too much or too little or over-burdening them with responsibilities.

Pascal, the accountant, wants the system to keep track of inventory. Since he is involved with purchasing supplies for the restaurant, he would find it extremely useful to have a way of tracking items from purchase, to storage, and then to their eventual use. Supplies would be items such as ingredients to meals, napkins, condiments, grill equipment, silverware, cleaning supplies, and others. He sees this portion of the database as a way for him to automatically keep the books so that he can spend more time analyzing the net profit of the restaurant via the creation of SQL reports in order to determine how meals and services could best be priced to maximize revenue.

Diogenes, the advertising executive, is hoping to use information from the database about customer purchasing, frequency of visits, and basic demographic information to market, price, and advertise products in a way that increases consumer spending, customer satisfaction, and brand loyalty.

Cicero, the lawyer, wants the system to make sure that employees are fairly compensated and that no unfair wage practices are engaged in. He also wants to make sure that the restaurant is being run up to code and that issues such as the use of expired products are never engaged in.

Despite all of these ideas and having some basic knowledge from looking at other restaurants, they are uncertain about the details of how to build such a system. That’s where you come in.

You are going to fulfill all of the roles from logical and physical database designer to database managemer and data manager. You don’t have to worry about building the user interface, but you are in charge of building a consistent, redundant, and easily mantainable data model to help run the

organization. After the database is designed and implemented, your job in the company will be to write SQL query reports, views, and/or stored procedures that provide each team member with the information they need to properly fulfill their responsibilities for the Super Linguini Brothers restaurant.

The project will be judged on the following criteria:

I.  Conceptual Design (100pts)

1. Analysis of the Problem: You will have to write out a summary of the issue and your proposed solution. This will be in writing. You will have to evaluate what parts of the restaurant are effected, what data needs to be collected, what (physical) technology (such as apps/registers/kiosks/inventory systems/etc are needed) or method you will use to collect that data, and how that data can be used to solve the problem.

1. A set of Entity Relationship / UML / Semantic Map diagrams will be generated in order to develop a graphical representation of the situation. This model will be independent of the relational model and will focus on an entity centric analysis (meaning, at this stage, you don’t have to worry about breaking the objects down into tables. You just simply need to graph out the important objects and how they are related to each other..)

II.  Logical Design (100pts)

1. This will be the phase where you will develop a relational model of the system. It will also be in a diagram, but the objects being modeled will be the tables, columns, and key relationships that will be used in your solution. This solution will be independent of a particular database management system and will not require code (that is saved for Part III)

All of the tables must:

1. Be in 4th Normal Form.
2. Accurately capture the information to solve the problems
3. Currectly identify all primary and foreign keys
4. Correctly identify all of the multiplicity relationships between tables (one-to-one, many-to-one, and many-to-many)

III.   Physical Design (100pts)

1. This part will consist of three distinct files with code for constructing the database.

The first file generates your database schema  in Oracle 11g. This includes the tables with their columns, constraints, and if necessary, additional triggers.

The second file populates the tables with data. Make sure there is enough data in your tables to pass a series of pre-determined queries that will be tailored to your particular system design and used to test that your system meets certain criteria.

After the population of the data, you should provide a third file with a set of  three SQL queries for each member of the organization that will help them perform their duties. Each query may have parameterized inputs.

Here are the list of employees and the particular queries they will need:

Waiters:

1. A query consisting of a set of insert statements that can put a customer’s meal order into the system. This code can just be an example of a particular order being processed by a waiter. Please note, likely, you will need several insert statements to capture a single order as each meal ticket will have multiple items on it.
2. A query that calculates the total cost of an already inputted order so that the waiter can deliver the bill at the end of a dining experience.
3. A query that calculates the amount of tip money a waiter has made from all of the tables they served during their shift.

Aristotle, general manager

1. A query that returns how many hours an employee worked in a particular month and how much money they were paid. This query will show the general manager if certain employees are working too much or too little.
2. A query that returns how much money was brought in by a particular waiter (based on how much thebill was for each table they waited on minus the tip) in a particular month. This query will give the general manager an idea of which waiters are carrying the most weight financially, and whether or not maybe they should get an hourly raise.
3. A query that calculates how much money the restaurant made in a particular month (based on the total of all customers’ bills for that month combined) minus how much money was paid out to all of the employees (every employee not just waiters) of the restaurant for that month as wages. This query provides an estimate of net profits so that the manager can determine if they should be putting more or less workers on a shift. If profits are too high the customers may be being overworked. If the profits are too low, there may be too many people working per shift.

Pascal, accountant

1. A query showing how much money was spent on supplies in a given month. This includes bills for electricity, heat, water, etc as well as food, spices, napkins, plates, etc.
2. A query showing how much was spent on employee wages in a given month.
3. A query showing how much money was made in sales (customer orders) for a given month.
4. A query showing if the restaurant is making or losing money in a particular month, determined by subtracting the results of queries 1 and 2 from query 3.

Diogenes, marketer

1. A query that lists how many of each item was sold in a given month, along with the name of that item, and its price, ordered from most items sold to least items sold.
2. A query showing which day of the week makes the most money.
3. A query showing what the least popular item is on Fridays. This might be used to make a special to encourage people to buy this item on that day.

Cicero

1. A query checking to make sure that no employee is working more than 50 hours per week 2. A query checking to make sure that no part-time employee is working more than 32 hours a week
2. A query checking to make sure that there are no employees under 16 years old.

Please make absolutely sure your scripts successfully execute. If they fail and there is not enough time to return it for corrections (meaning you didn’t hand the project in earlier than the due date), I will unfortunately have no choice but to grade your project harshly.

Starter Data

Hours of Operation

Mon-Thurs     12:00PM-9:00PM

Fri-Sat            12:00PM-1:00AM

Sun                 12:00PM-8:00PM

Sample Order

Harrison, party of 5 waiter, Finn

Example Employee Shift

Toad, waiter               Week of October 22nd, 2017: