ISM 218 Project Assignment - Part 2 Oak Creek Stadium | Section 3 and 4 Solution

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 (3 points)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) (2 points)3. Database Implementation in MySQL (30 points)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 (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.

6. Peer Evaluation (10 points)Completed peer evaluation forms for each team member (template attached). Database Implementation Model1N1 N N 1FoodPK FoodItemIDFood_NameFood_TypeFood_Price1NEmployeePK EmployeeIDE_FNameE_LNameE_SalaryE_BirthDateE_HireDateDepartmentID*N 1 N 1TaskAssignmentPK TaskAssgn_IDTaskID*EmployeeID*TA_DateTime1NN 1AttendancePK TicketNumberCustomerID*EventID*Ticket_PriceTeamPK TeamIDTeam_NameCoach_FNameCoach_LNameCityStateFoodOrdersPK OrderIDFoodItemID*Restaurant_TypeEmployeeID*F_DateTimeCustomerID*Quantity1NSeasonPassPK SeasonPass_IDExpirationDate11CustomerPK CustomerIDC_FNameC_LNameC_HeightSeasonPass_ID*TaskPK TaskIDTask_NameDepartmentPK DepartmentIDDept_NameManagerID*EventPK EventIDEvent_DateTimeSport_TypeSeatsAvailableHomeTeamID*VistorTeamID*Database Table Instance ChartsPage | 5Table Name: AttendanceColumn Name: TicketNumber CustomerID EventID Ticket_PriceKey Type: PK FK FK -Null/Unique: NN/U NN NN NNFK Table: - Customer Event -FK Column: - CustomerID EventID -Datatype: INTEGER INTEGER INTEGER FLOATMax Length: 7 7 7 5,2Table Name: CustomerColumn Name: CustomerID C_FName C_Lname C_Height SeasonPass_IDKey Type: PK - - - FKNull/Unique: NN/U NN NN NN NNFK Table: - - - - SeasonPassFK Column: - - - - SeasonPassIDDatatype: INTEGER VARCHAR VARCHAR INTEGER INTEGERMax Length: 7 20 30 2 7Table Name: DepartmentColumn Name: DepartmentID Dept_Name ManagerIDKey Type: PK - FKNull/Unique: NN/U NN NNFK Table: - - EmployeeFK Column: - - EmployeeIDDatatype: INTEGER VARCHAR INTEGERMax Length: 7 30 7Database Table Instance ChartsPage | 6Table Name: EmployeeColumn Name: EmployeeID E_FName E_LName E_HireDate E_BirthDate E_Salary DepartmentIDKey Type: PK - - - - - FKNull/Unique: NN/U NN NN NN NN NN NNFK Table: - - - - - - DepartmentFK Column: - - - - - - DepartmentIDDatatype: INTEGER VARCHAR VARCHAR DATE DATE FLOAT INTEGERMax Length: 7 20 30 - - 8,2 7Table Name: EventColumnName:EventID Event_DateTime SportType SeatsAvailable HomeTeamID VisitorTeamIDKey Type: PK - - - FK FKNull/Unique: NN/U NN NN NN NN NNFK Table: - - - - Team TeamFK Column: - - - - TeamID TeamIDDatatype: INTEGER DATETIME VARCHAR INTEGER INTEGER INTEGERMaxLength:7 - 20 4 7 7Table Name: FoodColumn Name: FoodItemID Food_Name Food_Type Food_PriceKey Type: PK - - -Null/Unique: NN/U NN NN NNFK Table: - - - -FK Column: - - - -Datatype: INTEGER VARCHAR VARCHAR FLOATMax Length: 7 15 15 5,2Database Table Instance ChartsPage | 7Table Name: FoodOrdersColumn Name: OrderID FoodItemID Restaurant_Type EmployeeID F_DateTime CustomerID QuantityKey Type: PK FK - FK - FK -Null/Unique: NN/U NN NN NN NN NN NNFK Table: - FoodItem - Employee - Customer -FK Column: - FoodItemID - EmployeeID - CustomerID -Datatype: INTEGER INTEGER VARCHAR INTEGER DATETIME INTEGER INTEGERMax Length: 7 7 15 7 - 7 2Table Name: SeasonPassColumn Name: SeasonPassID ExpirationDateKey Type: PK -Null/Unique: NN/U NNFK Table: - -FK Column: - -Datatype: INTEGER DATEMax Length: 7 -Table Name: TaskColumnName:TaskID Task_NameKey Type: PK -Null/Unique: NN/U NNFK Table: - -FK Column: - -Datatype: INTEGER VARCHARMax Length: 7 30Database Table Instance ChartsPage | 8Table Name: TaskAssignmentColumn Name: TaskAssgn_ID EmployeeID TaskID TA_DateTimeKey Type: PK FK FK -Null/Unique: NN/U NN NN NNFK Table: - Employee Task -FK Column: - EmployeeID TaskID -Datatype: INTEGER INTEGER INTEGER DATETIMEMax Length: 7 7 7 -Table Name: TeamColumn Name: TeamID Team_Name Coach_FName Coach_LName City StateKey Type: PK - - - - -Null/Unique: NN/U NN NN NN NN NNFK Table: - - - - - -FK Column: - - - - - -Datatype: INTEGER VARCHAR VARCHAR VARCHAR VARCHAR VARCHARMax Length: 7 30 20 30 20 20____________ ______________ _____________________First Name Last Name Team Name

Group Project Peer EvaluationInstruction: 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 ofEffortCooperative Member Percentage ofContributionTOTAL: 100% N/A 100%Comments to your team members