attachment_2 (20)

ITEC 451 Assignment 3: Stored Procedures This assignment document is intellectual property of Towson University and it is a copyright violation to post it online. The posting of this document online will result in an official copyright investigation by Towson University. This assignment is 100% unique so it will be very easy to find if it is posted online. The purpose of this assignment is to give students practice with writing stored procedures. All stored procedures will be based on the database depicted above. Write the stored procedure for each question below and submit it to Blackboard as a word document. 1. Create a stored procedure that lists information about the games played by a team for a given year and season. This should include the game time, location, home team, away team, and score (goalsHome and goalsAway). The procedure should be named uspGetGameSchedule and should take the year, season, and team as input parameters. 2. Create a stored procedure that calculates a team’s record for a given year and season. The result of the procedure should include the team name, wins, losses, and draws. The procedure should be named uspGetTeamRecord and should take the year, season, and team as input parameters. 3. Create a stored procedure that calculates the total number of goals and assists for a player for a season and stores the result as output parameters. The procedure should be named uspCalculatePlayerStats and should take the year, season, and player as input parameters and return the total goals and assists for the player as output parameters.

ITEC 451 Assignment 3: Stored Procedures 4. Create a trigger that updates the wins, losses, draws, pointsFor, and pointsAgainst columns in the Team table when the result of a new game is inserted into the Game table. The trigger should be named trUpdateTeamStats and should be run whenever there is an insert into the games table. Hint: The update of the wins, losses, and draws columns should be based on an IF statement (https://www.mysqltutorial.org/mysql-if-statement/). Access Control The purpose of this section of the assignment is to give students additional experience with scripting access control in SQL. Create an SQL script to based on the following permission requirements: Role: League Commissioner Name: Leslie Higgins Database Permission Requirements:  Query information about all players, teams, coaches, and schedules  Create new games and schedules  Update game scores  Add new teams  Update team information  Add new coaches  Update existing coach information Role: Coach Names: Ted Lasso, Chris Beard, Nathan Shelley, George Cartrick  Update team stats  Update player stats for each game  Add new players  Update existing player information Role: Player Names: Sam Obisanya, Isaac McAdoo, Colin Hughes, Richard Montlaur, Dani Rojas  Query player stats  Query game stats  Query team stats The deliverable for this part of the assignment is just a script. It does not need to be implemented in a database. SQL Performance The purpose of this section of the assignment is to give students experience with analyzing SQL queries to maximize performance. All queries are based on the above database model. Assume that there are no indexes in the database at this point. For each of the following queries, write a set of recommendations for creating indexes and rewriting the queries using the 12 SQL Rules of Thumb found in Database Performance slides . 5. The following query is used to list a players stats for a year at a particular field location: SELECT * FROM player as p INNER JOIN PlayerGame as pg ON p.playerID = pg.playerID