Question Details Normal
$ 50.00
Database system SQL
Question posted by

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.

Available solutions
$ 50.00
Database system SQL
  • This Solution has been Purchased 1 time
  • Submitted On 24 Apr, 2017 02:09:59
Solution posted by
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 standi...
Buy now to view full solution.
$ 40.00
Database system SQL best solution
  • This solution has not purchased yet.
  • Submitted On 24 Apr, 2017 02:52:55
Solution posted by
Database system SQL 3 a) 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. Queries: create table SeasonPass (SeasonPassID Integer(7) not null,ExpirationDate Date Not null,primary key(SeasonPassID)); create table Customer(CustomerID INTEGER(7) not null, C_FName varchar(20) not null,C_Lname varchar(30) not null,C_Height INTEGER(7) not null,SeasonPass_ID INTEGER not null,primary key(CustomerID) ,foreign key(SeasonPass_ID) references SeasonPass(SeasonPassID) ); create table Team(TeamID INTEGER(7) not null,Team_Name VARCHAR(30) not null,Coach_FName VARCHAR(20) not null,Coach_LName VARCHAR(30) not null,City VARCHAR(20) not null, State VARCHAR(20) not null,primary key(TeamID)); create table Event(EventID INTEGER(7) not null ,Event_DateTime DATETIME not null,SportType varchar(20) not null,SeatsAvailable INTEGER(4) not null,HomeTeamID INTEGER(7) not null,VisitorTeamID INTEGER(7) not null, primary key(EventID), foreign key(HomeTeamID) references Team(TeamID), foreign key(VisitorTeamID) references Team(TeamID), CHECK (SportType IN( 'basketball', 'hockey', 'football', 'soccer' ))); create table Attendance(TicketNumber INTEGER(7) not null,CustomerID INTEGER(7) not null,EventID INTEGER(7) not null,Ticket_Price FLOAT(5,2) not null, primary key(TicketNumber),foreign key(CustomerID) references Customer(CustomerID),foreign key(EventID) references Event(EventID)); create table Department(DepartmentID INTEGER(7) not null,Dept_Name VARCHAR(30) not null,ManagerID INTEGER(7) not null,primary key(DepartmentID),CHECK (Dept_Name IN( 'Ticketing', 'Food Service', 'Gift Shop', 'Maintenance', 'Security' ))); create table Employee(EmployeeID INTEGER(7) not null,E_FName VARCHAR(20) not null,E_LName VARCHAR(30) not null,E_HireDate Date not null,E_BirthDate Date not null,E_Salary FLOAT(8,2) not null,DepartmentID INTEGER(7) not null, primary key(EmployeeID),foreign key(DepartmentID) references Department(DepartmentID)); Alter table Department add constraint for_ManagerID foreign key(ManagerID) references Employee(EmployeeID); create table Food(FoodItemID Integer(7) not null,Food_Name VARCHAR(15) not null,Food_Type VARCHAR(15) not null,Food_Price FLOAT(5,2) not null,primary key(FoodItemID),CHECK (Food_Type IN('drink', 'snack', 'main dish' ))); create table Task(TaskID Integer(7) not null,Task_Name VARCHAR(30) not null,primary key(TaskID)); create table TaskAssignment(TaskAssgn_ID INTEGER(7) not null, EmployeeID INTEGER(7) not null,TaskID INTEGER(7) not null, TA_DateTime DATETIME not null,primary key(TaskAssgn_ID),foreign key(EmployeeID) references Employee(EmployeeID),foreign key(TaskID) references Task(TaskID)); create table FoodOrders (OrderID INTEGER(7) not null, FoodItemID INTEGER(7) not null,Restaurant_Type VARCHAR(20) not null ,EmployeeID INTEGER(7) not null, F_DateTime DATETIME not null,CustomerID INTEGER(7) not null,Quantity INTEGER(2) not null,primary key(OrderID),foreign key(EmployeeID) references Employee(EmployeeID),foreign key(FoodItemID) references Food(FoodItemID),foreign key(CustomerID) references Customer(CustomerID),CHECK (Restaurant_Type IN('concession stand','sit down' ))); 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. insert into seasonpass value(1,'2020-03-17'); insert into seasonpass value(2,'2020-04-17'); insert into seasonpass value(3,'2020-05-17'); insert into seasonpass value(4,'2020-04-18'); insert into seasonpass value(5,'2020-03-16'); insert into seasonpass value(6,'2020-03-17'); insert into seasonpass value(7,'2020-04-27'); insert into seasonpass value(8,'2020-04-07'); insert into seasonpass value(9,'2020-02-17'); insert into seasonpass value(10,'2020-04-23'); ------------------------------------------------------------------- insert into task value(1,'run'); insert into task value(2,'go'); insert into task value(3,'eat'); insert into task value(4,'sleep'); insert into task value(5,'drink'); insert into task value(6,'walk'); insert into task value(7,'do'); insert into task value(8,'read'); insert into task value(9,'learn'); insert into task value(10,'speak'); -------------------------------------------------------------------- insert into team value(1,'India','K.K.','def','London','U.K.'); insert into team value(2,'England','Peter','asd','M.G.','U.S.A.'); insert into team value(3,'Russia','Akola','xed','R.G.','South Africa'); insert into team value(4,'Spain','D.K.','mef','Z.G.','U.K.'); insert into team value(5,'Brazil','C.K.','kek','F.G.','India'); insert into team value(6,'Ireland','M.K.','dek','K.G.','U.K.'); insert into team value(7,'Netherland','F.K.','ref','L.G.','Bihar'); insert into team value(8,'Switzerland','G.K.','zef','M.G.','U.K.'); insert into team value(9,'Australia','Z.K.','doef','S.G.','Australia'); insert into team value(10,'New Zealand','R.K.','dref','R.T.','U.K.'); ------------------------------------------------------------------------------ insert into customer value(1,'a','ba',5,1); insert into customer value(2,'ab','bb',6,1); insert into customer value(3,'ac','bc',7,2); insert into customer value(4,'ad','b',4,3); insert into customer value(5,'ae','bd',3,1); insert into customer value(6,'af','b',5,1); insert into customer value(7,'ag','be',5,10); insert into customer value(8,'ak','b',5,4); insert into customer value(9,'al','bg',6,1); insert into customer value(10,'am','b',5,5); insert into customer value(11,'an','bk',5,1); insert into customer value(12,'ao','b',7,6); insert into customer value(13,'ap','bl',5,10); insert into customer value(14,'aq','bm',8,7); insert into customer value(15,'ar','bn',5,8); insert into customer value(16,'as','bo',4,1); insert into customer value(17,'at','bp',5,1); insert into customer value(18,'au','b',6,9); insert into customer value(19,'av','bq',5,2); insert into customer value(20,'aw','br',9,1); insert into customer value(21,'ax','bs',5,3); insert into customer value(22,'ay','b',4,1); insert into customer value(23,'az','bt',5,2); insert into customer value(24,'aa','bu',6,1); insert into customer value(25,'ba','bv',6,5); insert into customer value(26,'ca','b',5,6); insert into customer value(27,'da','bw',7,7); insert into customer value(28,'ea','bx',5,8); insert into customer value(29,'fa','by',5,9); insert into customer value(30,'ga','bz',5,10); ------------------------------------------------------------------------------- ...
Buy now to view full solution.
Attachment
$ 25.00
Database system SQL
  • This Solution has been Purchased 1 time
  • Submitted On 25 Apr, 2017 04:24:24
Solution posted by
3 a) 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. Queries: Create table Season Pass (Season Pass ID Integer(7) not null, ExpirationDate Date Not null,primary key(Season PassID)); create table Customer(CustomerID INTEGER(7) not null, C_F Name varchar(20) not null,C_Lname varchar(30) not null,C_Height INTEG...
Buy now to view full solution.
Only 45 characters allowed.

$ 629.35