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