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.**
Querying in MySQL
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.
Select customerid,avg(ticket_price) from attendance group by customerid;
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).
Select concat(E_FName,concat(' ',E_LName)) as FullName, departmentId,E_HireDate from employee order by E_HireDate asc;
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 p
romotions 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 discover
ed 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.
Select temp. FullName, SeasonPass. SeasonPassID, SeasonPass. ExpirationDate,temp. numberOfevents from SeasonPass inner join (Select concat(C_FName, concat(' ',C_LName)) as FullName, Customer. SeasonPass_ID ,d.number as numberOfevents from Customer inner join (Select CustomerID,count(EventID) as number from Attendance group by CustomerID) as d on d. CustomerID= Customer. CustomerID) as temp on temp. SeasonPass_ID= SeasonPass. SeasonPassID;
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.
Select distinct Team. TeamID, Team_Name,Coach_FName,Coach_LName ,City, State from Team inner join Event on Event. HomeTeamID = Team. TeamID UNION Select distinct Team. TeamID, Team_Name,Coach_FName,Coach_LName ,City, State from Team inner join Event on Event. VisitorTeamID = Team. TeamID;
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.
Select temp. EmployeeID,temp. totalsales,CONCAT(E_FName,CONCAT(' ',E_LName)) as FullName from (Select (FoodOrders.Quantity * Food.Food_Price) as totalsales,EmployeeID from FoodOrders inner join Food on FoodOrders. FoodItemID = Food. FoodItemID group by EmployeeID) as temp inner join Employee on Employee.EmployeeID = temp. EmployeeID and temp.totalsales > 200;
5. Views in MySQL (15 points)
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.
