In this lab, you’ll start work on relational databases and meet with your team.
Relational database modeling requires that we lay out the tables, their contents and their inter-relationships. Consider the following schema for the Monopoly domain.
Player(ID, emailAddress, name)
PlayerGame(gameID, playerID, score)
Game (ID, time)
This model specifies the tables required to represent Monopoly games over time, who played in them, and who won. It assumes that all games are finished games (because it doesn’t represent the board, pieces, or properties) and that the score is the computed value of all player assets. Note that PlayerGame(gameID) and PlayerGame(playerID) are foreign keys referring to Game(ID) and Player(ID) respectively.
Add tables and relationships to this game database model to support saving Monopoly games in progress rather than just finished games. Keep track of each player’s cash, their properties, houses, hotels and where their piece is currently located.
Specify your solution by extending the monopoly schema given above.
Once it is designed, the database is implemented in a relational database management system (DBMS). In these labs, we’ll use PostgreSQL , a well-known, open-source system based on the original Postgres research system. DBMSs may or may not have GUI interfaces, but they all support command-line access, so we’ll focus on textual commands in this lab. This has the added benefit that it is also compatible with standard APIs to database systems.
Here are the basic tools for starting a PostgreSQL server and building the monopoly database on various systems.
Choose whichever platform is most appropriate but be sure to save your SQL source code, both to submit for this lab and to use in future lab exercises.
The sample Monopoly database shown above can be implemented in PostgreSQL
using this SQL command file:
cs262-code/../lab07/monopoly.sql; use this as the
basis for your lab solution. You can find documentation on additional
Postgres SQL data types here: Chapter 8. Data Types.
Fire up PostgreSQL as just described, edit the schema definition to create tables for your extensions to the monopoly database, and add at least one sample record for each table. The new tables should be based on the tables you designed in the previous exercise.
One good way to do this exercise (for small databases) is to modify
the
monopoly.sql command file you just downloaded. Add new
DROP, CREATE TABLE,
GRANT, and INSERT commands as appropriate and re-load the command
file.
This will then delete the old versions of the tables, create news
ones,
and load them as
specified. Note that tables must sometimes be dropped and records
inserted in particular
orders, based on foreign key constraints (tables with foreign keys
must
be dropped before
the tables with their parent keys).
Save this new command file to submit for this lab.
As a quick check that your database creation commands worked, check out the PGAdmin tool’s view of your monopoly database. It should list the schema you created. Then try running some simple SQL queries commands against the tables using the PGAdmin query tool. Here are some good ones to try:
| Command | Type | Description |
|---|---|---|
SELECT COUNT(*) FROM
|
SQL | returns the number of records in the given table |
SELECT * FROM
|
SQL | returns all the records from the given table |
We’ll discuss SQL more carefully next week. For now, just try these commands on your tables to convince yourself that your database is in place and built as you expect it to be built. You can run them by starting up another PostgreSQL query tool and typing/executing the commands as appropriate.
Participate in your team’s meeting with stakeholders.
One task for the next deliverable is to build a database schema for your domain model.
Design a database schema for your team project based on your domain model from the previous lab.