ISM 218 - Spring 2015 Project Assignment - Part 2 Oak Creek Stadium

Project Description:
After receiving 21 different setups, the management of Oak Creek Stadium has decided on the attached ER diagram and instance tables for the development of a database to keep track of its operations. Your team has been contracted to implement the database in the MySQL relational database management system (RDBMS). The following restrictions on the data were discussed in the initial meeting:

- Customer Height will be measured in inches
- Sport Type is either basketball, hockey, football, or soccer
- Food Type is either drink, snack, or main dish
- Restaurant Type is either concession stand or sit down restaurant
- The Departments are Ticketing, Food Service, Gift Shop, Maintenance, and Security
- Multiple food orders occurred on March 17th, 2020.
- All events and orders occurred in 2020.

**NOTE: Use the standard solution at the end of this description to complete the rest of the assignment.**

Phase II report should include:
1. Cover Page 
The cover page should include the course number, the semester, the team name, and the full names of all team members.
2. Table of Contents (including page numbers)
3. Database Implementation in MySQL (
Implement the database in MySQL. Specifically, write a SQL script to do the following:
a. Create the Tables. Each Table must have a Primary Key constraint, Foreign Key constraints where applicable, and other constraints as written in the table instance charts.
b. Insert rows into each Table. Make up your own data. Make sure that each Table has at least 10 rows and Associative Tables have 30 rows.
c. Develop a SQL script to delete the entire database (i.e., removing all Tables)
d. Develop a SQL script to delete the data in the tables but retain the Table structure (i.e., removing all rows of data). **NOTE: There is more than one way to complete this; only one is required**
4. Querying in MySQL (40 points)
A new management team has taken over Oak Creek Stadium due to its poor financial state. The CEO believes that the slowing economy may have caused attendance at sporting events to decrease. The CEO also believes that workforce reduction efforts need to be put in place in order to improve Oak Creek Stadium's financial standing. As a part of the reorganization efforts, the CEO has requested that your team provide reports that will be used in the organizational review.

Develop the following SQL queries:
a. The CEO is considering increasing ticket sales to improve the financial standing of Oak Creek Stadium. The CEO requested a report that lists the average event ticket price paid per customer. The list should only include the customer ID and the average ticket price.

b. As a part of the workforce reduction effort, the Oak Creek Stadium CEO is looking to layoff some employees in order to reduce overhead. Your team received a request to provide a list of the full names of all employees, their department, and their hire dates, listed in chronological order (by hire date).

c. The CEO also wants a report of the total number of employees in each department, listed in alphabetical order by department name.
d. In order to support the claim that attendance at sporting events is low, the CEO requested a list of all sporting events in chronological order. The CEO would like to see the sport type, home team ID, and visitor team ID in the report, as well as the total number of customers that attended each event as "Number of Customers in Attendance".
e. Another approach to reducing overhead is to look at the top earners at Oak Creek Stadium. Your team received a request to provide a list of all the managers (displaying their full name as one field called "Manager Name"), the name of their department, and their salary (formatted as a $xx.xx). This list should be in decreasing order based on salary.

f. The CEO wonders if there should be more holiday-themed promotions to encourage food purchases at events. Your team received a request to provide a list of all foods that have been ordered on March 17th, 2020, specifically the food name, the total quantity sold, and the total sales (qty * price). The price should be formatted as a $xx.xx.

g. The CEO is also considering offering promotions to encourage fans to attend more than one sporting event. The report requested should include the customer ID, full customer name of customers who have only attended one sporting event. The report should also include the sport type of the event that was attended.

h. It is discovered that season pass sales have also been decreasing. The CEO wants to send a promotion to all current and past season pass holders. Your team received a request to provide a list of the season pass holder ID, the full name of the season pass holder, the expiration date of the season pass, and the number of events that they have attended.

i. The CEO wants to thank all of the teams that have played at the stadium by sending a letter to their coaches. The report requested should include all the details of the teams that have played at the stadium.

j. In order to boost employee morale during the workforce reduction, the CEO wants to give an award to the top employees who had the highest food sales in 2020. The report requested should list the employee ID, the employee's full name, and their grand total of food item sales (qty * price). List the employees from the greatest sales to the least. Exclude total sales that are less than $200.

5. Views in MySQL 
To protect the data in the database, your team should develop a View and write the SQL script for it. This view is specifically for employees so that they can see their employment data. Include their employee ID, their full name, the date and time of the task they completed, and the task name.

6. Peer Evaluation 
Completed peer evaluation forms for each team member (template attached). Database Implementation Model

Group Project Peer Evaluation
Instruction: Please rate each team member, INCLUDING YOURSELF, on two scales.
1. How much effort everyone put in: Please put a percentage for each team member. The total percentage must add up to 100% for the team.

2. How cooperative each team member was: Please rate on 1-5 scale. 1 is not cooperative, 3 is average, and 5 is very cooperative. Things such as being helpful in coming up with ideas, timely email exchange, actively research the topic and share findings are viewed positive.

3. How much everyone contributed to the project: Please put a percentage for each team member. The total percentage must add up to 100% for the team. Note that the percentage of effort (input) may not equal the percentage of contribution (output).
Full Name Percentage of
Effort
Cooperative Member Percentage of
Contribution
TOTAL: 100% N/A 100%
Comments to your team members: