Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask Accounting Basics Expert

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 
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)
3. Database Implementation in MySQL (
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 
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 
Completed peer evaluation forms for each team member (template attached). Database Implementation Model

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:

Accounting Basics, Accounting

  • Category:- Accounting Basics
  • Reference No.:- M91590063
  • Price:- $10

Priced at Now at $10, Verified Solution

Have any Question?


Related Questions in Accounting Basics

Question - a few members in the circular club do not want

Question - A few members in the Circular Club do not want to continue with annual rodeo. However, Shelly is insistent the club must continue to conduct the rodeo as an annual fundraiser. Shellley aegues she has spent hun ...

Assignment 2 discussion questionthe finance department of a

Assignment 2: Discussion Question The finance department of a large corporation has evaluated a possible capital project using the NPV method, the Payback Method, and the IRR method. The analysts are puzzled, since the N ...

Question - at the end of the year but before an adjustment

Question - At the end of the year, but before an adjustment had been made to close Manufacturing Overhead Control, A-1 Frames had the following account balances: Raw Materials Inventory$126,955Work in Process Inventory$1 ...

Have you ever been involved in the budget process at your

Have you ever been involved in the budget process at your organization? If so, describe your role and responsibilities. Do you think people at your level in the organization should provide budget inputs, and why or why n ...

Question using finviz select four filters your choice

Question: Using FINVIZ, select four filters, (your choice). Select one of the companies from your list and using the Ratio Analysis Worksheet below, complete the financial analysis for the company using Word. Your financ ...

Question - f l wright architects incorporated as licensed

Question - F. L. Wright Architects incorporated as licensed architects on September 1, 2018. During the first month of the operation of the business, these events and transactions occurred: Sept. 1 Stockholders invested ...

Question - topcap co is evaluating the purchase of another

Question - TopCap Co. is evaluating the purchase of another sewing machine that will be used to manufacture sport caps. The invoice price of the machine is $152,000. In addition, delivery and installation costs will tota ...

Question - eagle owns 80 of flyways common stock that was

Question - Eagle owns 80% of Flyway's common stock that was purchased at its underlying book value. The two companies report the following information for 2004 and 2005. During 2004, one company sold inventory to the oth ...

Question - last year garrison manufacturing sold 500 000

Question - Last year, Garrison Manufacturing sold 500 000 units at $4 each. Both sales volume and sales price are expected to increase by 15 per cent in the upcoming year. Calculate the expected sales revenue for the upc ...

Question - bob smith borrowed 200000 on january 1 2015 the

Question - Bob Smith borrowed $200,000 on January 1, 2015. The interest rate of 8% is compounded semiannually to be repaid January 1, 2025. To repay this Bob wants to start making five equal annual deposits into fund tha ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As