The purpose of this project is to put to work the knowledge you’ve gained regarding PostgreSQL and MongoDB by implementing a database design in both databases, solving the same problem twice! You will then load data into your databases and run queries against them to compare performance, add indexes to address performance problems you identify, and then rerun those same queries to see how performance improves!
In this project you will be implementing a database for Bob’s Pizza, a local pizza company in town. Bob needs to have the ability to store recipes for different kinds of pizza, create users, take orders, and track inventory!
· (5 points) Creating Users
· Users in the database need to have, at a minimum, the following fields:
· Email Address
· First Name
· Last Name
· Phone Number
· Address Line 1
· Address Line 2
· City
· State
· Zip
· (5 points) Tracking Inventory
· The start of any good pizza is quality ingredients! At a minimum we need to track the following fields per ingredient:
· Name
· Description
· Quantity
· I realize that in real life “1 cheese” doesn’t make sense, but for the sake of the project let’s just assume that if a pizza needs cheese, it takes a whole number quantity of “cheese” ingredients that can be decremented from the quantity value
· Feel free to make this section more complex, however!
· (15 points) Storing Recipes
· A receipe contains the following attributes:
· Name
· Description
· A list of ingredients (referencing the ingredients already stored in the inventory table)
· Cooking instructions
· (35 points) Taking Orders
· A order is both an entity tracked in the database, as well as a function allowing for the submission of new orders
· In the database, an order consists of:
· The user that created the order
· The time an order was placed
· The recipe that is being used to make the pizza
· No substituions allowed! Our recipes are perfect
· But what if there isn’t enough ingredients to make the order!
· Create a server-side function in PostgreSQL (PL/SQL) and MongoDB (JavaScript) that creates a new order
· The function will take in as parameters a user and recipe
· It will then need to validate that there are ingredients sufficient to make the pizza, then update the ingredients to decrement the count of each ingredient by what is required for the recipe
· If there aren’t enough ingredients, raise an exception!
· Finally, an entry will be written to the orders table to track the order, generating a new timestamp in the process
· NOTE: Be sure to include the SQL needed to create your tables for PostgreSQL in your submission. For MongoDB, include a writeup explaining how you will be using your collections, by convention, to enforce the organization I described above.
· (20 points) Now that we have the schema in place, we need to insert a bunch of data and see how it performs!
· Create load scripts for each database that will insert at least 1000 unique users, 25 different recipes, and at least 50,000 orders
· Run queries against the order table by recipe Id (a non-indexed field) and see how the EXPLAIN results look in both PostgreSQL and MongoDB
· Apply indexes as appropriate to the recipe Id field, and then rerun the queries to see how the results improve!
· Include the data creation scripts, the EXPLAIN results, and a writeup of the comparison of before/after in your submission for each database
· If 50,000 orders isn’t enough for your query performance to show any improvement, feel free to increase that number to 100,000 or more!