Computer Science Assignment-Database system SQL

Computer Science Assignment-Database system SQL

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.