Bakery Website Description

X is the website for a bakery in New York City. Customers can use the website to place orders for their favorite treats.

Business Rules

  1. Customers sign up for the site using their email and password.
  2. Customer registration involves collecting the user's name and phone number.
  3. The bakery makes multiple items. Items are separated into food and drinks.
  4. Customers place orders purchases.
  5. A purchase contains an order and an order requests an item.
  6. An order contains only one or more item.
  7. Each item has a name, description, item #, price, and wholesale min.
  8. An order purchase can be picked up or delivered.
  9. Pick-up orders require a date, time, and branch#.
  10. Deliveries require an address, location, time, date, and contact name and phone#.
  11. A customer must order $10 to qualify for delivery.
  12. Customers can pay online or in-store.
  13. Customers, orders, and payments are recorded in a transaction purchase.
  14. An transaction order can be classified as "standard" or "wholesale."
  15. A certain quantity of an item is required for an transaction order to be considered wholesale.
  16. An item sold wholesale has a discounted price.

Exceptions

  1. Customers must pay online for delivery.
  2. Customers can cancel an order up to 24 hours before the end date.
  3. Custom order cannot be cancelled. An order can be classified as custom at the time it is placed.
  4. First time customers must confirm over the phone if their order is over a certain dollar amount.
  5. Similar items can be substituted if items for an immediate order are sold out.

Questions

  1. How many orders does the bakery have open at any given time? List the bakery orders for a given time period?
  2. What is a given customer's order history?
  3. What is the most popular order completion time? common type of payment?
  4. When should the bakery have the most employees working? What is the total projected revenue?
  5. What is the most popular bakery item?
  6. What is the name of the most loyal customer?
  7. How many of a given item were ordered?
  8. What is the total price for each order?


Bakery ER Diagram


Bakery Relational Model


Bakery DDL


Bakery DML Queries


Changes in red made on October 9, 2013

Changes in blue made on November 18, 2013