Reference no: EM133529034

Assignment: Business Intelligence and Data Mining- Design and Implement a Database System for a Restaurant Operation- Design Entity Relationship Diagram

Objective:

The objective of this course project is to design an Entity Relationship Diagram (ERD) for a restaurant sales and management system. The project aims to provide students with hands-on experience in analyzing complex business rules and modeling a database schema that effectively captures the data requirements and relationships of a restaurant operation. Through this project, students will enhance their understanding of conceptual database design, normalization, and data modeling techniques.

Business Scenario Description:

PG (Papa Georgios), the owner of a pizzeria and restaurant, wishes to capture all sales data with detailed order information. An order can consist of multiple items, such as pizzas, breadsticks, and beverages, placed by a customer. Employees are hired for various roles, including a restaurant manager, assistant managers, chefs, cooks, waiting staff, and delivery drivers. Reporting hierarchies exist among the employees, with the assistant managers reporting to the manager, and the chefs, waiting staff, and delivery drivers reporting to the assistant managers. Employees can have multiple supervisors. Job history for each employee needs to be maintained.

The restaurant operates from 11 am to 11 pm, and employees are required to be present between 10 am and 12 am to open and close the restaurant. The manager, assistant managers, and chefs receive salaries, while the cooks, waiters, and delivery drivers are paid hourly wages. Chefs require certifications to prepare specific dishes, which need to be up to date. The restaurant manager and assistant managers are required to have special degrees in restaurant management. Employee information such as names, addresses, and dates of birth also needs to be stored.

Employees on salaries do not need to clock in or out, whereas those on hourly wages must record their work hours for payroll calculations. Historical data regarding employment status and promotions of each employee must be stored. The restaurant serves various food items such as pizzas, pasta, garlic bread, chicken wings, beverages, desserts, and sushi. Each item has different serving sizes, prices, and historical prices. Payment methods accepted include cash, checks, and major credit cards.

The restaurant wishes to provide an app that updates customers on the food preparation stage of their order, indicated by coded numbers for stages such as making, baking, preparing, and on the way. Inventory tracking is required to ensure timely reordering of ingredients, including fresh dough, sauces, and other food supplies. Information about food vendors and other supplies, such as cleaning supplies, should be stored in the database.

The restaurant offers a club card (PG Card) to registered customers, awarding points for every dollar spent. Customers accumulate points and can redeem a $10 coupon upon reaching 100 points, which resets their point balance to zero. At the time of payment, customers are prompted to provide a rating for food and service quality on a scale of 1 to 5.

Task

I. Data Analysis and Requirements Gathering:

1. Start by understanding the problem domain thoroughly. Knowing what you're trying to represent in your database is half the battle.

2. Analyze the provided business scenario in detail.

3. Identify entities, relationships, and attributes based on the requirements.

4. Consider complex business rules and data dependencies while gathering requirements.

II. ERD Design and Modeling:

1. Create an Entity Relationship Diagram (ERD) that accurately represents the entities, relationships, and attributes identified.

2. Consider different types of relationships (one-to-one, one-to-many, many-to-many), cardinalities, and attributes required for each entity.

3. Incorporate advanced concepts such as weak entities, ternary relationships, and inheritance (sub-type, super-type relationships) if relevant to the scenario.

4. Use Crow's Foot notations to depict entities, relationships, and cardinalities.

5. Apply normalization techniques to ensure data integrity and minimize redundancy. Entities should be normalized to 3NF.

III. Documentation and Presentation:

1. Prepare comprehensive documentation that explains the rationale behind the design decisions and justifies the chosen relationships, attributes, and cardinalities.

2. Create a professional presentation to showcase the ERD, discussing the entities, relationships, and complex business rules depicted in the diagram.

3. Highlight any additional considerations or design

IV. Evaluation Criteria:

1. Accuracy and completeness of the ERD in reflecting the selected scenario and its complex business rules.

2. Adherence to normalization principles and best practices for efficient and effective database design.

3. Clarity and organization of the documentation and presentation.

4. Demonstration of an in-depth understanding of the scenario and the ability to translate complex business rules into a structured ERD.