CIS 360 - Project Part 2 Logical Design Tests & Advanced Queries Overview The proper ERD for Palermo was given to you, so your final project ERD, schema and data dictionary should now be in their final forms after comparison to the given ERD. Use the notepad scripts given in this portion of the project to create the database and run queries to test the outputs. Using your revised ERD and data dictionary, create your revised database schema. Identify the order of entry for all entities in the schema. Using the information given with the ERD and the notepad scripts, draft the metadata for each entity in your schema. Now, you will build the tables for the Palermo database in Microsoft SQL Server using the scripts given. Next, conduct integrity tests using some basic queries described below to prove that your design is good (it works). Provide cropped screenshots (including the SQL statements in the query window (with comments) and the results from SQL Server Management Studio to prove that they give the expected results. Finally, create advanced queries to further test the design you have implemented. Provide cropped screenshots (including the SQL statements in the query window (with comments) and the results from SQL Server Management Studio to prove that they give the intended results. Use any tables you wish to test your advanced queries. Part 2 Deliverables: For full credit: 1. PDF file (.pdf) Database schema (including all primary and foreign keys identified) Screenshots showing the queries and results used to test the design (see below) Tests Required: First, write the queries to test the following: Two INSERT statements in a row the first one adding a new Customer, the second one adding a new Ticket for that Customer, should succeed. A similar two INSERT statements executed in the opposite order should fail.

Next, write queries to answer the following questions: NOTE** 2 of your queries must be fully NESTED queries (no joins) 1. Which movie sold the most tickets? 2. Names of customers who have achieved EGOT status. 3. Names of customers who have the most visits. 4. Descriptions of any movie that did not sell any tickets. 5. Menu items that have not been ordered in the past 3 months. 6. Items for which QOH is less than 5. 7. What menu item is most popular? 8. Total customer visits that were only for the bar. 9. Bar items with monthly average sales exceeding last years average for the same month. 10. Workers statistics - how many customers has each worker member assisted? Check your file before you submit this portion of the project to Canvas each student is allowed only 1 submission.