MIS 4372 -- Midterm -- Spring 2015 -- Parks
Name_________________________________________________________________Last 4 digits of your PSID _________________

Execute problem 1 here
Execute problem 2 here
Source code problem 1 here
Source code problem 2 here

Baseball Version 2

Assume, there is a little league baseball league that keeps player statistics. There is a table named LLteam that holds the team data; a table named players that holds individual player demographic information; and a table named game_perf that records the events associated with a player's game performance. The data structures for these tables are shown below:

LL_team	player	game_perf
team_id int IDENTITY (1,1), team_name varchar(30), coach_first_name varchar(25), coach_last_name varchar(25), coach_home_phone varchar(10), coach_cell1 varchar(10), coach_cell2 varchar(10), PRIMARY KEY (team_id)	player_id int IDENTITY (1,1), first_name varchar(25), last_name varchar(25), home_phone varchar(10), cell1 varchar(10), cell2 varchar(10), team_id int, age int, PRIMARY KEY (player_id)	game_data_id int IDENTITY (1,1), player_id int NOT NULL, opponent_id int, game_date char(6), innings_played int, at_bats int, hits int, walks int, fielding_errors int, innings_pitched numeric (4,1), hits_allowed int walks_allowed int, winning_or_losing_pitcher char(1), PRIMARY KEY (game_data_id)

1. (50 points) Write the second pass of an insert program that adds a new player. Specifically:

The first pass (DO NOT WRITE THIS pass) provides an HTML page for adding a new player. The HTML is:

<form action="fix2.asp" method="POST">
First Name <input type="text" size="25" maxlength="25" name="fn"><br>
Last Name <input type="text" size="25" maxlength="25" name="ln"><br>
Home Phone <input type="text" size="10" maxlength="10" name="hp"><br>
Cell Phone 1 <input type="text" size="10" maxlength="10" name="c1"><br>
Cell Phone 2 <input type="text" size="10" maxlength="10" name="c2"><br>
Team <select name="teamid"><option value="1">Cardinals<option value="2">Dodgers
<option value="3">Orioles<option value="4">Pirates<option value="5">Braves
<option value="6">Yankees<option value="7">Rangers<option value="8">Astros
<option value="9">Marlins<option value="10">Giants</select><br>
Age <input type="text" size="2" maxlength="2" name="age"><br>
<input type="hidden" name="token" value="2">
<input type="submit></form>

The second pass performs the insert. Using the first name and last name, check to be sure that the player does NOT exist in the player table before performing the insert. If a player with the same first and last names already exists, send the user a message that says "Player already in the database". If the insert is successful, tell the user: "Added OK". Do not attempt to provide a value for player_id (it is automatically generated by the database).

Do not edit the data. Do not use transaction control. Do not use parameterization. Do not write the "main".

2. (50 points) Write an *.asp program that calculates each player's batting average. Using the game_perf table, for each player display and accumulate the game details and totals for: at_bats and hits and then calculate the batting average = (total number of hits) / (total number of at_bats). This is a single control break program. So:

Order the report table by ascending player_id
For each player, show each game's values (from the game_perf table) in a single HTML table row (i.e., one table row for each game with three columns: player_id, hits, at_bats)
At the end of each the player's data, calculate the player's batting average and create an HTML table row with 1 cell and a colspan='3' with: (1) the player_id; (2) the string " Batting Average = " and (3) the calculated batting average all inside a single table cell (again: the batting average is the sum of the player's hits divided by the sum of the player's at_bats). For the batting average show three digits to the right of the decimal.
As the data is being processed accumulate two grand totals: total_hits and total_at_bats, (i.e., hits and at_bats for every game for every player). After the table show the batting average for all players in all games.