26 Sep Papa Georgeo (AKA PG) is opening a Pizzeria and he needs a database to capture and maintain the data for the entities Sales, Customers, Products, Purchase, Suppliers, and Employees
Let’s consider the following scenario:
Papa Georgeo (AKA PG) is opening a Pizzeria and he needs a database to capture and maintain the data for the entities Sales, Customers, Products, Purchase, Suppliers, and Employees.
Business Needs:
PG would like to capture all the sales data with the details of every order. An order could be consisted of multiple items. For example, a customer could order 3 large pizza, 1 bread sticks, and 2 beverages in one order with one order ID. The Sales schema should capt
ure and maintain order date, customer ID, Order Time, Sub Total, Tax Amount, Total Due, Order Item, Qty, Price Charged for each item, Discount Amount for each item etc. PG is going to offer a club card (AKA PG Card) for his customers which they will hand it to the cashier to get points (those points are known as, you guessed it correctly, PG points! ???? ) every time they buy anything from the pizzeria. A customer can get a free pizza when he/she has accumulated enough points. In addition, PG would like to track who is buying what so he can create different promotions for his customers with that information. The restaurant will accept cash, checks, and all major credit cards as payment method.
Note: Find the complete question in the attached document
ERD Assignment
Let’s consider the following scenario:
Papa Georgeo (AKA PG) is opening a Pizzeria and he needs a database to capture and maintain the data for the entities Sales, Customers, Products, Purchase, Suppliers, and Employees.
Business Needs:
PG would like to capture all the sales data with the details of every order. An order could be consisted of multiple items. For example, a customer could order 3 large pizza, 1 bread sticks, and 2 beverages in one order with one order ID. The Sales schema should capture and maintain order date, customer ID, Order Time, Sub Total, Tax Amount, Total Due, Order Item, Qty, Price Charged for each item, Discount Amount for each item etc. PG is going to offer a club card (AKA PG Card) for his customers which they will hand it to the cashier to get points (those points are known as, you guessed it correctly, PG points! ) every time they buy anything from the pizzeria. A customer can get a free pizza when he/she has accumulated enough points. In addition, PG would like to track who is buying what so he can create different promotions for his customers with that information. The restaurant will accept cash, checks, and all major credit cards as payment method.
PG is also going to hire Shift Managers, Pizza Bakers and Delivery Drivers for the pizzeria. The employees will work in different shifts, receive wages based on their ranks, maintain physical addresses, etc. PG plans to store all these data in the database about the employees.
The pizzeria is going to serve Pizza, Pasta, Garlic Bread, Chicken Wings, Beverages, and Desserts. The restaurant will offer different kinds of pizza like Mediterranean pizza, Authentic Italian pizza etc., and each pizza will have different sizes like small, medium, and large. The pasta, wings, disserts, beverages will come in same serving sizes. The database needs to capture all the sales data for all the products the restaurant is going to offer. PG also wants to maintain data for the product inventory, and the product reviews he gets on the website.
The restaurant will also capture and maintain the supply data (all the ingredients it will purchase from the vendors, other supplies such as cleaning supplies, etc.) for future analysis. In addition, the restaurant wants to capture data about the customers. The customer data may include their names, telephone numbers, addresses, email addresses.
PG also wants to capture and maintain data of his suppliers.
Assignment Requirements:
In this assignment, you will build an Entity Relationship Diagram (ERD) for the database schema that will be used for the Online Transaction Processing (OLTP) system. The tables in the schema must be normalized to 3rd NF. In addition to the data columns (attributes) described above for different tables, you can be creative and include other attributes in the tables if you find them relevant.
Once your schema is completed, think of running the following queries and checking if the structure of your ERD can support the following queries:
Who are the top 10 selling customers in terms of sales amount?
Which customers have accumulated how many points?
Which are the top product among Pizza, Pasta, and Wings in terms of sales amount?
In terms of the size (L, M, S) of the Pizzas, which size is the top selling product?
For the top 10 customers in terms of accumulated points, show their quantities of Pizza, Pasta, and Breadsticks purchased overtime, in each order?
Show the suppliers’ names, address, and the products the restaurant has purchased from each of them.
Show all the purchased products with their category and subcategory.
Show the customer reviews with the customer name, email address, and reviews.
For this assignment, based on the need of the restaurant, you can create the following schemas: Suppliers, Sales, Products, Customers, and Employees. You can decide what tables you think are going to be relevant to capture and store the data to fulfill all the business needs while normalizing the tables to 3NF.
Your ERD must show the followings: name of each of the tables (keep the tables that belong to a certain schema close to each other), the joins from one table to other(s), the cardinality of the relationships between the tables with the signs that show the types of relationship exist, primary key(s) shown in bold in each table. The screen shot below demonstrate these requirements.
Figure 1. An ERD of a fictitious schema
Deliverables:
Tables in each schema should be normalized to 3NF. Here are the deliverables of the assignment:
1. An ERD for the entire database. You will need to make sure that each schema is joined with the other schema through one or many tables and the tables within each schema are also joined with each other
Important Instruction:
Your work must look professional. The assignment must be typed with all the tables and schema shown. You can use MS word or any other software to draw the diagrams for tables and fields within each tables. Each schema and table must have appropriate heading (or table name).
Good luck!
image1.gif
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Wridemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.