support@essaywritingexpert.orgIDS 410: Business Database Technology
Individual Assignment #1 (database analysis) E-R Modeling of Lambs-Are-Us Restaurant System
Due Date: Mon, Jan 31, 2022 11:59PM
Submit your ER diagram as a PDF or Word file to the Blackboard.
Note: to draw your ER diagram, you can use a computer-based graphic tool such as VISIO, Lucid Chart (lucidchart.com), Creately (creately.com), or Gliffy (gliffy.com).
Problem Statement
Please read the Lambs-Are-Us Restaurant case study (see pages 2-6 below. In particular, please carefully read the “Data Requirements for Revenue and Production Cycles” section on pages 3-6). For this first assignment, your task is to draw an E-R diagram for the Lambs-Are-Us Restaurant database.
Assume that you are one of the managers in this restaurant; and you would like to propose a project to develop an application (a computerized information system) that would not only perform the restaurant’s basic operations but also help the restaurant keep records of its customers, orders, inventories, and so on. This application would help the restaurant to identify its regular customers and design sales promotion schemes as well as to keep track of its inventories and sales. Moreover, you would like to have an Internet presence for the restaurant from which customers can create an account, browse the menu items, and submit orders.
Use the Supertype and Subtype concepts (see Chapter 4 [8th or 9th edition] or Chapter 3 [10th edition]) if needed. State any assumption you have when developing the diagram.
IDS 410: Business Database Technology Lambs-Are-Us Restaurant
Lambs-Are-Us Restaurant Case Study
Introduction
This case study describes a hypothetical restaurant called Lambs-Are-Us, which specializes in lamb dishes. Currently, the restaurant manually does all of its operations (such as generation of bill, keeping records of its staff, menu items, and inventory management). The restaurant has many employees with different roles, including the following:
• Managers involve in the following tasks: control the overall restaurant activities, create
and manage budgets, generate different types of sales, inventory, and financial reports.
• Waiters/waitresses receive orders from customers and send order requests to the
kitchen.
• Chefs receive customers’ orders lists from the waiters and fulfill the orders and send
them back to the waiters.
• Cashiers handle cash registers.
In general, a restaurant business has several departments:
1. Restaurant department is responsible for everything related to customers, which include
assigning tables, showing customers to an assigned table, taking orders, sending orders
to the kitchen, serving food, clearing tables, and billing customers.
2. Kitchen department is responsible for all food related activities, including determining the
menu items, preparing food, stocking ingredients, and washing and cleaning kitchen and
utensils.
3. Stock keeping department is responsible for managing restaurant supplies, including
managing kitchen stocks and ordering necessary items.
4. Shipping and receiving department is responsible for incoming and outgoing restaurant
items, including receiving food for the kitchen, receiving items for the restaurant,
handling receipts for received items, garbage collection and disposal.
5. Administration department is responsible for handling all incoming and outgoing
restaurant finances. It receives receipts for bills from shipping and receiving department,
orders for items from stock keeping department, and receipts for usage of inventory from
the restaurant department.
The main problems faced by Lambs-Are-Us Restaurant are time wastage to maintain its records of inventory, preparation of invoice, as well as high labor and overhead costs. Moreover, several cases of frauds were found due to mismanagement of manual operations. As its business grows, manual operations are too slow, tedious, and producing many errors. Thus, the restaurant is planning to computerize many of its business operations. Several objectives of the computerized system are as follows: properly maintain stock keeping, properly maintain financial
statements, maintain high quality customer services, including seating, serving, and billi ng the customers.
As a manager of this restaurant, you would like to propose a computerized system that would not only perform its basic operations but also help the restaurant to keep records of its
IDS 410: Business Database Technology Lambs-Are-Us Restaurant
customers, which would help the restaurant to identify its regular customers and design sales promotion schemes. Moreover, the restaurant would like to have an Internet presence from which customers can create an account, browse the menu items, and submit orders.
Proposed System
The Lambs-Are-Us restaurant system should provide the following functionalities:
1. Customer management: the restaurant would like to keep track of its customers who
submit the orders via the Internet, call by phone, or walk-in to reserve tables.
2. Employee management: the restaurant should be able to keep track of its employees.
3. Inventory management: the restaurant staff should be able to enter inventory items.
When a customer orders a menu item, the quantity on hand of each ingredient used to
prepare that menu item should be reduced and updated accordingly. This way, the
purchasing manager would be able to make a decision to reorder ingredients that are
low in quantity from the appropriate vendors.
4. Menu item management: the restaurant has different menu items for breakfast, lunch,
and dinner.
5. Invoice (bill) printing and tips management: the restaurant should be able to print bills as
well as compute tips for each waiter based on his/her services.
6. Reservation management: the restaurant should be able to reserve tables for customers.
7. Table management: the restaurant system should be able to display graphic on screen,
which lets waitresses/waiters know the tables they should be covering.
Data Requirements for Revenue and Production Cycles Customer and Credit Card
Customer data items such as customer names and addresses appear on invoices or bills, shipping documents, and other business documents. To ensure that customer information is consistent wherever it is used, the restaurant keeps all customer information in one entity. This makes adding, deleting, displaying, or editing customer data easy and efficient. If a customer moves to a new location, the customer’s address needs to be changed only once. When a customer reserves a table in the restaurant by phone or via the Internet, his or her detail is recorded. Each customer is identified by a unique customer number. Other customer’s attributes include phone and email address. A customer may place a number of orders and each order can have a number of meals. The customer may own one or more credit cards. Each credit card can be used to pay many bills whereas each bill is associated only with one credit card (i.e. no splitting the bill with two or more cards). With a customer’s permission, the card data might be stored by the restaurant once the payment is processed.
Staff (Employee)
The restaurant has several managers responsible for overseeing the operations of the office. The restaurant closely follows the performance of its managers, and notes the date that they assumed their position at the restaurant. Each manager is allocated an annual car allowance and a monthly bonus payment based upon the restaurant’s performance. The restaurant has
IDS 410: Business Database Technology Lambs-Are-Us Restaurant
Figure 1: Lambs-Are-Us Staff Detail form.
members of staff with the job title of Chef. They are responsible for preparing the meal every day. Each member of staff is given a unique staff number. Data items held on each member of staff includes the name (first and last name), address (street, city, zip code), telephone number, gender, date of birth, job title (position), salary, and the date the member of staff joined the restaurant.
It is the company policy to record the details of the next-of-kin of members of staff, including the next-of-kin’s full name, relationship to the member of staff, address (street city, zip code), and phone number. Only the details of a single next-of-kin are held for each member of staff.
An example of the Lambs-Are-Us form used to record the details of a member of staff called John Doe is shown in Figure 1 below.
| Lambs-Are-Us Staff Detail Form | |||
| Staff Number | 10 | ||
| Personal Details | |||
| First Name | John | Last Name | Doe |
| Address | 750 Halsted, Chicago IL 60607 | Gender | Male |
| Phone | 312-555-5555 | Date of Birth | 05/05/1985 |
| SSN | 999-999-9999 | Job Title | Chef |
| Salary | $$$$ | Join Date | 09/04/2010 |
| Next-of-Kin Details | |||
| Full Name | Jane Doe | Address | 750 Halsted, Chicago IL 60607 |
| Relationship | Wife | Phone | 312-555-5555 |
Dining Table
When customers arrive at the restaurant, a waiter/waitress assigns them to an available table. The restaurant would like to keep track of its tables. Each table is identified by a unique table number, the number of seats associated with that table, and whether or not it is reserved.
Menu
The restaurant has different menu for breakfast, lunch, and dinner. Each menu is identified by a unique menu number. Other attributes are menu name, the start time of day, end time of day, as
IDS 410: Business Database Technology Lambs-Are-Us Restaurant
well as the season (i.e., fall, winter, spring, and summer) that the menu applies. A single menu has many menu items.
Menu Item (Meal)
A menu item (meal, drink, etc.) is identified by a unique item (meal) number. The details for a menu item include name, description, and price. A chef can prepare several menu items and a menu item can be prepared by several chefs. A single menu item can appear in more than one menu. For example, Lamb Vindaloo can appear in lunch as well as in dinner menus.
Ingredient
Each menu item may have several ingredients. Each ingredient is identified by a unique ingredient number, name, description, quantity on hand, unit of measure, and calories per unit of measure. The restaurant stores the recipe of each menu item (meal) that contains the ingredient details with the specific amount of calories present. A single ingredient may be used in more than one menu item (meal). A sample of a recipe to make Lamb Vindaloo is as follows:
| Recipe: Lamb Vindaloo | Serving: 4-6 Meals |
| Ingredients | Directions |
| 3 tablespoons coriander | 1. Grind coriander, cumin, cloves, cinnamon,peppercorns, fenugreek and fennel to afine powder.2. Grind chilies with garlic, onion and gingerto make a paste.3. Mix spice powder with vinegar.4. Place meat in container and rub well withspice mixture.5. Pour chili paste over meat and marinate forat least 3 hours, or overnight.6. Over high heat, heat ghee or butter in largeheavy-bottom Dutch oven and fry meat until deeply colored.7. Add bay leaves and stock and bring to aboil.8. Reduce heat and simmer about 1 1/2- 2hours, or until meat is very tender.9. Season to taste. |
| 1 tablespoon cumin | |
| 6 cloves | |
| 2 inches cinnamon sticks | |
| 1 teaspoon black peppercorns | |
| 2 teaspoons fenugreek seeds | |
| 1 teaspoon fennel seed | |
| 5 dried chilies, soaked | |
| 8 cloves garlic | |
| 1 large onion | |
| 1 inch fresh ginger | |
| 1⁄4 cup white vinegar | |
| 2 lbs lamb shoulder, trimmed and cut into 2 inch cubes | |
| 3 tablespoon butter | |
| 2 bay leaves | |
| 2 cups beef stock |
Supply Item
In addition to ingredients, the restaurant also would like to keep track of supply items it uses to support its activities. These supply items include different types of utensils, cleaning items, etc. Supply item is identified by item number, name, description, and quantity on hand.
IDS 410: Business Database Technology Lambs-Are-Us Restaurant
Sales Order
If a customer wants to buy menu items (meals), he or she initiates this intention by making a phone call, submitting an order over the Internet, or simply walk into the restaurant. Each of these actions is a sales order. Sales orders contain common components: order number (a unique value), the date and time the order is placed, the customer who places the order, the table assigned to this customer, as well as the quantity and price for each requested menu item the customer wishes to purchase. A single sales order may request multiple menu items; and a popular menu item may be requested by multiple sales orders.
Bill
Bill is concerned with billing the customers. A bill is specific to a particular order. A bill has bill number and amount. A single bill is associated with a single order. An example of Lambs-AreUs bill is shown in Figure 2 below.
| Lambs-Are-Us | |
| The Lamb Specialist | |
| Thu 06/19/2015 at 18:30:15 | Bill Number: 104 |
| Your server today was: | Joe Public |
| Table Number: | 8 |
| Party of: | 2 |
| 2 x Lamb Vindaloo @$15.00 | $30.00 |
| 2 x Lamb Fajita @$10.00 | $20.00 |
| 2 x Coca Cola @$2.00 | $4.00 |
| ————- | |
| Sub Total: | $54.00 |
| Tax (10%): | $5.40 |
| ————- | |
| Total: | $59.40 |
| Thank you for your patronage. | |
| Figure 2: Lambs-Are-Us Bill. |
Cash Received from Customers
The purpose of a restaurant business is to receive customers’ payments for the menu items or services they have purchased. The Cash Receipt entity captures data about payments received from customers. A cash receipt is immediately created after a bill is being paid. In addition to the CashReceipt identifier, you may want to include attributes for the date and time that the payment was received, the credit card number (if applicable), the type of payment (i.e., cash or credit card) and the amount of the payment. A single cash receipt is associated with a single bill.