CASE STUDY -
The Garden Restaurant has been serving fine food to the city for over a decade. It has been decided to develop a computerized information system for its business operation. This information system will have two sections. One section will handle reservations and the other section will hold information on food and drinks orders and produce customer bills.
A loyalty program is simple points based rewards offered by the Garden Restaurant to customers who frequently make purchases. Customers swipe their stylish card at every purchase, and the card tracks the amount of money spent within 30 days (their stipulated time period for loyalty). Every $50 spent earns the customer a free item. The Garden Restaurant speaks the language of its audience by measuring points in dollars, and rewards in food and drink items.
Reservation Section:
The restaurant has a fixed number of tables; each table can hold up to 4 people. Advance reservations are made either by telephone or in person. The person making the reservation will give the date and time preferred and the number of people. The computer system will check availability and confirm the reservation if there are sufficient tables available for that date, time, number of people combination. If there are not sufficient tables, the computer system will search forwards or backwards for an alternative space. Once a table is booked, it will be booked for a period of 2 hours.
Customers may come to the restaurant without any reservation. If they do so, the reservation subsystem is used to determine if there is room to accommodate them or not. If not, the computer system will search forwards (only) as described above to determine the likely waiting time for the potential customer.
Ordering and Billing Subsystem:
All orders will be taken on line through the wireless keyboard fixed on each table. The order will collect the code of the various items ordered. (Item codes are shown on the menu for this purpose). If any specific ingredients are to be added to the food items, it may be indicated in the order. Upon completion of the order, the system will display back the items ordered so that order can be confirmed.
Food items will be sent to the kitchen, where they will be displayed on a large plasma screen. Once an item is ready to be served, the kitchen staff will use the kitchen terminal to indicate its readiness. Waiters will receive notification of the item's readiness and then serve the item to the appropriate table. Drinks orders are relayed to an LCD screen in the bar and once again, when the drinks are ready to be served, the bar staff will use the bar terminal to indicate this.
Please note that the above scenario is intentionally brief, to make students produce a sensible draft set of requirements based on the level of understanding. In reality, more information may be needed to establish a clear set of requirements.
TASK 1 -
Using the information from the scenario, develop a detailed Enhanced Entity-Relationship (EER) model to reflect the data requirements for the Garden Restaurant system.
- You should state clearly the assumptions that you made in developing the diagram;
- Identify all the real world entities. Give a candidate key and suitable attributes for each entity. Indicate clearly the multi-valued attributes. Show clearly the weak entities and determine their relationship with the owner entities;
- Identify any super/ subtype entities available in the given scenario. Identify the constraints on the generalization/specialization as either disjoint or overlapping;
- Identify the relationships among the entities. Indicate clearly the cardinality of each relationship. Show the attributes for each relationship, if any. Give any additional semantic feature of the relationships such as total participation or partial participation.
- Draw a complete EER diagram.
- EER DIAGRAM 10-15 ENTITIES + BRIEF DESCRIPTION
TASK 2 -
Use the EER from Task 1 to derive a relational database schema. Explain and justify the types of integrity constraints that will be imposed on the proposed database.
TASK 3 -
Use any relational database software to implement the relations. Populate the relations in the database with sample data.
DATABASE - POPULATE 8-10 RECORDS + SCREEN CAPATURE EVIDENCES
TASK 4 -
(a) Write relational algebraic expressions for the following queries.
(i) Find the list of menu that has highest and lowest number of ordered items made by customer bookings in any day of the month.
(ii) Find the list of customers (they have credit period for loyalty) who have failed to visit the restaurant within their stipulated time period.
(b) Rewrite the relational algebraic expressions in SQL and test the results.
- ONE QUERY EACH FOR RELATIONAL ALGEBRAIC AND SQL
TASK 5 -
CONCLUTION AND REFERENCES LIST.