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

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 (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 Model

1
N
1 N N 1
Food
PK FoodItemID
Food_Name
Food_Type
Food_Price
1
N
Employee
PK EmployeeID
E_FName
E_LName
E_Salary
E_BirthDate
E_HireDate
DepartmentID*
N 1 N 1
TaskAssignment
PK TaskAssgn_ID
TaskID*
EmployeeID*
TA_DateTime
1
N
N 1
Attendance
PK TicketNumber
CustomerID*
EventID*
Ticket_Price
Team
PK TeamID
Team_Name
Coach_FName
Coach_LName
City
State
FoodOrders
PK OrderID
FoodItemID*
Restaurant_Type
EmployeeID*
F_DateTime
CustomerID*
Quantity
1
N
SeasonPass
PK SeasonPass_ID
ExpirationDate
1
1
Customer
PK CustomerID
C_FName
C_LName
C_Height
SeasonPass_ID*
Task
PK TaskID
Task_Name
Department
PK DepartmentID
Dept_Name
ManagerID*
Event
PK EventID
Event_DateTime
Sport_Type
SeatsAvailable
HomeTeamID*
VistorTeamID*
Database Table Instance Charts
Page | 5
Table Name: Attendance
Column Name: TicketNumber CustomerID EventID Ticket_Price
Key Type: PK FK FK -
Null/Unique: NN/U NN NN NN
FK Table: - Customer Event -
FK Column: - CustomerID EventID -
Datatype: INTEGER INTEGER INTEGER FLOAT
Max Length: 7 7 7 5,2
Table Name: Customer
Column Name: CustomerID C_FName C_Lname C_Height SeasonPass_ID
Key Type: PK - - - FK
Null/Unique: NN/U NN NN NN NN
FK Table: - - - - SeasonPass
FK Column: - - - - SeasonPassID
Datatype: INTEGER VARCHAR VARCHAR INTEGER INTEGER
Max Length: 7 20 30 2 7
Table Name: Department
Column Name: DepartmentID Dept_Name ManagerID
Key Type: PK - FK
Null/Unique: NN/U NN NN
FK Table: - - Employee
FK Column: - - EmployeeID
Datatype: INTEGER VARCHAR INTEGER
Max Length: 7 30 7
Database Table Instance Charts
Page | 6
Table Name: Employee
Column Name: EmployeeID E_FName E_LName E_HireDate E_BirthDate E_Salary DepartmentID
Key Type: PK - - - - - FK
Null/Unique: NN/U NN NN NN NN NN NN
FK Table: - - - - - - Department
FK Column: - - - - - - DepartmentID
Datatype: INTEGER VARCHAR VARCHAR DATE DATE FLOAT INTEGER
Max Length: 7 20 30 - - 8,2 7
Table Name: Event
Column
Name:
EventID Event_DateTime SportType SeatsAvailable HomeTeamID VisitorTeamID
Key Type: PK - - - FK FK
Null/Unique: NN/U NN NN NN NN NN
FK Table: - - - - Team Team
FK Column: - - - - TeamID TeamID
Datatype: INTEGER DATETIME VARCHAR INTEGER INTEGER INTEGER
Max
Length:
7 - 20 4 7 7
Table Name: Food
Column Name: FoodItemID Food_Name Food_Type Food_Price
Key Type: PK - - -
Null/Unique: NN/U NN NN NN
FK Table: - - - -
FK Column: - - - -
Datatype: INTEGER VARCHAR VARCHAR FLOAT
Max Length: 7 15 15 5,2
Database Table Instance Charts
Page | 7
Table Name: FoodOrders
Column Name: OrderID FoodItemID Restaurant_Type EmployeeID F_DateTime CustomerID Quantity
Key Type: PK FK - FK - FK -
Null/Unique: NN/U NN NN NN NN NN NN
FK Table: - FoodItem - Employee - Customer -
FK Column: - FoodItemID - EmployeeID - CustomerID -
Datatype: INTEGER INTEGER VARCHAR INTEGER DATETIME INTEGER INTEGER
Max Length: 7 7 15 7 - 7 2
Table Name: SeasonPass
Column Name: SeasonPassID ExpirationDate
Key Type: PK -
Null/Unique: NN/U NN
FK Table: - -
FK Column: - -
Datatype: INTEGER DATE
Max Length: 7 -
Table Name: Task
Column
Name:
TaskID Task_Name
Key Type: PK -
Null/Unique: NN/U NN
FK Table: - -
FK Column: - -
Datatype: INTEGER VARCHAR
Max Length: 7 30
Database Table Instance Charts
Page | 8
Table Name: TaskAssignment
Column Name: TaskAssgn_ID EmployeeID TaskID TA_DateTime
Key Type: PK FK FK -
Null/Unique: NN/U NN NN NN
FK Table: - Employee Task -
FK Column: - EmployeeID TaskID -
Datatype: INTEGER INTEGER INTEGER DATETIME
Max Length: 7 7 7 -
Table Name: Team
Column Name: TeamID Team_Name Coach_FName Coach_LName City State
Key Type: PK - - - - -
Null/Unique: NN/U NN NN NN NN NN
FK Table: - - - - - -
FK Column: - - - - - -
Datatype: INTEGER VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR
Max Length: 7 30 20 30 20 20

____________ ______________ _____________________
First Name Last Name Team Name

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: