HW #4 Solution

$30.00

Description

The first 10 problems count 10 points each on the homework.

1. Work exercise 5.1.3b, p. 212. Write a corresponding select statement using SimpleDB for the shipsDB you created in HW2 and show the output. Remember, SimpleDB only implements a subset of SQL (see slide included with HW1).

2. Work exercise 5.2.1, parts d, and g. p. 222.

3. Work exercise 6.1.3, part f, p. 257. Run the statement under DERBY and turn in a screen shot of the results of the query. (If you haven’t done so already, you need

to create the manfdb database from HW1).

4. Work exercise 6.1.4, part d, p. 257. Run the statement under SimpleDB and the shipsdb you created in HW2. Turn in a screen shot of the results of the query.

5. Work exercise 6.2.2, part e, p. 267. Run the statement under DERBY and turn in screen shot of the results of the query.

6. Work exercise 6.2.3, parts b and c, p. 267. Write SQL statements for the queries.

Run the statements under MySQL (MariaDB) and turn in a screen shot of the results of each query. Use the data from HW2 to create the shipsDB using MariaDB. Here is a URL to get you started using MariaDB (MySQL): https://wiki.cse.tamu.edu/index.php/How_to_Use_MySQL

7. Work exercise 6.3.1, part e, p. 279. Run the statement under DERBY and turn in a screen shot of the query and the results.

8. Work exercise 6.3.2, part c, p. 279. Write an SQL statement for the query. Run the statement under MySQL (MariaDB) and turn in a screen shot of the results of the query.

9. Work exercise 6.4.6, parts a and d, p. 289. Run the statement under DERBY and turn in a screen shot of the results of the query.

10. Work exercise 6.4.7, parts a and e, p. 290. Write SQL statements for the queries.

Run the statements under MySQL (MariaDB) and turn in a screen shot of the results of each query.

11. PROJECT work (50 points). Using either Derby or MariaDB, create the WWSNdb you designed in HW3. Turn in your CREATE TABLE statements and screen shots showing that you successfully created the tables.

12. PROJECT work (150 points). Use https://toolbox.google.com/datasetsearch with a search term of FOOTBALL PLAY BY PLAY and locate the Kaggle dataset named Detailed NFL PlaybyPlay Data 20092018. If you click the Kaggle”

button you will see a webpage describing the playbyplay data. Download and Unzip it and load it with Excel just to look at the data. Each row corresponds to a play in an NFL game. Note: The data is not normalized into a relational data base.

a. Write a Java program to print out the total number of real NFL games represented in the data, the total number of new quarter or Overtime period starts in all these games, the total number of times that a real game

ended in a tie, and the total number of times the home team scored and the total number of times the away team scored in all these games. Turn in your java code on ecampus and a screen shot of its output.

b. Looking at your output, how likely is an overtime period in a game?

c. Similarly, what is the likelihood of the home team getting a score compared to that of the away team?

Note: your Java program is basically a loop where you read each row in the data and process the “event each row represents—such as the start of a new game, or a new period in a game, or a score by the home team or the away team, the end of a game and whether or not it ended in a tie. You will need to understand the data to properly distinguish the events in which you are interested.