Relational Database

User Generated

orafbajvmml

Writing

Description

Revisit Task 1. Look for the corrections the instructor put as annotations on your submission on Canvas. Figure out the correct queries. 2. Open the Python program that you worked on in Task 2. Find the point in the code after the new record is inserted into the database, but before the database connection is closed. 3. In Task 1, Problem 1, we wrote a query to find Crono’s high score. Adapt this so that the player name is a parameter in an SQL string. Use the player’s screenname to set the parameter when execute the SQL on the cursor. The Python code for this is almost exactly the same as looking up the manager by ID in the Lab. I will leave this one to you. Print out the high score so the player can compare their new score to the high score. 4. In Task 2, Problem 2, we wrote a query to find the list of high scores. The problem didn’t say, but let’s limit it to the top 20. I’ll walk you through this one, so you can complete the others on your own. a

Unformatted Attachment Preview

CMPSC 321 Relational Database Theory and Design PA 1: Space Invaders Leaderboard Programming assignment: A video game with a database-backed leaderboard Finally, we combine the queries from Task 1 into the Java code from Task 2. Every time a user plays the game, when the game is over, after the new record is added to the database, the program needs to display the high scores and the leaderboards so the player can see how they stack up to the competition. This is the goal of Task 3. INVADERS id player 1 crono 2 crono 3 marle 4 lucca 5 crono hero 1 2 3 2 1 gametime 2017-11-14 12:17:01 2017-11-14 12:17:01 2017-11-14 12:17:01 2017-11-14 12:17:01 2017-11-14 12:17:01 score 50 470 320 5630 480 Task 3: Display the leaderboards 1. Revisit Task 1. Look for the corrections the instructor put as annotations on your submission on Canvas. Figure out the correct queries. 2. Open the Python program that you worked on in Task 2. Find the point in the code after the new record is inserted into the database, but before the database connection is closed. 3. In Task 1, Problem 1, we wrote a query to find Crono’s high score. Adapt this so that the player name is a parameter in an SQL string. Use the player’s screenname to set the parameter when execute the SQL on the cursor. The Python code for this is almost exactly the same as looking up the manager by ID in the Lab. I will leave this one to you. Print out the high score so the player can compare their new score to the high score. 4. In Task 2, Problem 2, we wrote a query to find the list of high scores. The problem didn’t say, but let’s limit it to the top 20. I’ll walk you through this one, so you can complete the others on your own. a. We need a string containing the SQL query. We don’t need any parameters for this or the rest of the problems. sql = """SELECT score, player, hero FROM invaders ORDER BY score DESC LIMIT 20""" b. Execute the query: cursor.execute(sql) c. We need to print a table. We start with the header of the table: print("---------------------------------") print(" Top 20 High Scores") print("---------------------------------") d. Next we loop over the result and get the values out of it: for row in cursor: hplayer = row["player"] hscore = row["score"] hhero = heroNames[row["hero"]-1] e. Now we need to print a row of the table. Here we use formatting techniques to make the table look nice. You can look this up for details, but put simply, %d is for an integer and %s is for a string. You can also specify how wide you want the column, and it fills in the rest with spaces. Here’s what we have: print("%9d %-16s %-6s" % (hscore, hplayer, hhero)) CMPSC 321 Relational Database Theory and Design PA 1: Space Invaders Leaderboard It’s width 9 for the score, 16 (left aligned) for the screenname, and 6 (left aligned) for the hero name. f. Finally, finish the loop and print a bottom for the table. print("---------------------------------") g. Try it out. 5. Task 2, Problem 3, was a leaderboard for each hero. Write code similar to the above to print 3 more tables—one for Currer, one for Acton, and one for Ellis. Each query should have a GROUP BY player clause, because we want to see each player only once (or possibly not at all) in each leaderboard. 6. Task 2, Problem 4, was an overall leaderboard for the game. This one was more complex because I wanted to see the details of the game in which the high score was achieved. Print a table for this one, too. Here is a query you can use: SELECT U.player, U.hero, U.gametime, U.score FROM invaders U, ( SELECT MIN(id) AS first_id FROM invaders S, ( SELECT player, MAX(score) AS high_score FROM invaders GROUP BY player ORDER BY high_score LIMIT 10 ) H WHERE S.player = H.player AND S.score = H.high_score GROUP BY S.player ) F WHERE U.id = F.first_id ORDER BY U.score DESC 7. Play the game several times, and give it to your friends to play. Check the leaderboards to see if they look right. 8. Upload the diceGame.py file to Canvas by the posted due date.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags