Background: Sal's Bagel shop is in desperate need of a relational database to manage sales. Sal's head of IT has gone on strike and they need a replacement quick. Can you help? Sure, you can, you are in the IT 3060 Database Il course. 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 customer's 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 1...n listed next to the column indicates the field will need to be a primary key and be set to have the property identity specification. Menultem, MenultemType, 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. MenultemID (1..n) Menultem MenultemDescription MenultemPrice MenultemTypelD (1...n) MenultemType CustomerID (1...n) CustomerLast CustomerFirst CustomerBirthMonth OrderlD(1...n) 2. 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. Save your .sql file as 3060Midterm_CreateTables_[LastName].sql AND a screenshot of the results showing all tables were created successfully is saved as 3060_CreateTableResults_[LastName].png. 3. Create query statements that will pull the following data from the database created: a. A list of the customers for February's birthday month offering. You can display just customer name (first and last). b. A list of the orders and their associated items purchased on February 14th. In the output include customer name (first and last), order id, order date and the list of items purchased. c. A list of the menu items ordered by menu item type and then by the item name. d. A list of orders and the total price (quantity * price) for each order. e. The order id of the highest price order. Save your.sql file 3060Midterm_QueryData_[LastName].sql
