Background:

Sals Bagel shop is in desperate need of a relational database to manage sales. Sals head of IT has gone on strike and they need a replacement quick. Can you help?

Database Requirements:

The bagel shop database needs to track their menu items. Each item will be assigned a menu type such as; bagels, drinks, and spreads (cream cheese, butter and other flavored toppings for the bagels).

They offer a customer loyalty program and need to track their customer information. They offer incentives for frequent customers and a special discount during a customers birthday month.

Sal wants to be able to pull the following reports:

A menu displaying items and price, sorted by menu item type.

A list of orders that includes the order date, customer, order items, quantity order and total item price.

Total monthly sales for a given date range.

List of customers getting the birthday month discount for a given month.

To help you get started, I have provided a list of fields that you will need to include in the database. The 1n listed next to the column indicates the field will need to be a primary key and be set to have the property identity specification. MenuItem, MenuItemType, Customer, and Order.

Note: This field list gets you started with the main tables and is not a full list of all the tables/fields in the database. Read over the database requirements and determine the relationships between these tables to determine what other tables/fields are required.

MenuItemID (1n)

MenuItem

MenuItemDescription

MenuItemPrice

MenuItemTypeID (1n)

MenuItemType

CustomerID (1n)

CustomerLast

CustomerFirst

CustomerBirthMonth

OrderID(1n)

OrderDate

Project Requirements:

Create an E-R Diagram with a normalized data model. The E-R Diagram must include notation for primary keys, foreign keys and illustrate the proper relationship between relating tables. The diagram should also include the data type for each data field.

Create a script that will create the database, tables and fields to match your database model in the E-R diagram. Proper data types should be used as well as the creation of primary keys and foreign keys as needed on each table.

Create query statements that will pull the following data from the database created:

A list of the customers for Februarys birthday month offering. You can display just customer name (first and last).

A list of the orders and their associated items purchased on February 14^th. In the output, include customer name (first and last), order id, order date and the list of items purchased.

A list of the menu items ordered by menu item type and then by the item name.

A list of orders and the total price (quantity * price) for each order.

The order id of the highest price order.