COSC 416 - Special Topics in Databases
Assignment 1 - Relational Databases
Each assignment you will build a simple application that uses a database. Every assignment will use the same web interface but change the database system that supplies the data.
Assignments may be done in groups of 2 or 3.
The domain that we will model is a Game Center system that stores information on games, players, and their achievements and high scores. A domain description:
- A Game consists of an id, name, publisher name, release date, and rating.
- A Player has an id, firstName, lastName, birthDate, sex, and a profile picture.
- A Achievement is associated with a game and has an unique achievement id per game, a name, and a number of points.
- When a player achieves a game achievement, it is stored along with the date and a comment.
- For each game played, a player has an integer score.
Use MySQL to host your database. The database should be on cs-suse-5.ok.ubc.ca (note it is cs-suse-5 not cs-suse-4!).
Task #1 (20 marks) - Create your Database
Create a SQL DDL script that contains CREATE TABLE statements and INSERT statements to populate the database. The database requires at least 5 games, 5 players, and 10 achievements. A player does not have to play all games and may not have an achievement in a game. Add some player scores and achievements. You must upload some form of profile picture for each player.
Bonus: (10 marks) Create a generator program (preferably in Java) that can generate a DDL script, CSV files, or automatically creates the database from code that can have a customizable number of games, achievements, and players.
Task #2 (80 marks) - Create your Web Interface
The web interface will allow easy browsing of games, players, achievements, and scores. You can build the interface in any language you wish (JSP recommended). You can host on cs-suse-5.ok.ubc.ca or your own site. We will be using this web interface throughout the course, so you will want to design and build it for easy maintenance and modification.
Web Site Requirements
- Must list all games in a browsable/searchable way. When a user clicks on a particular game, go to Game Detail view. (10 marks)
- Must list all players in a browsable/searchable way. When a user clicks on a particular player, go to Player Detail view. (10 marks)
- The Game Detail view consists of all game information, summary information including (number of players, number of achievements, total number of achievements for all players), and a top 10 list of the highest player scores. (20 marks)
- The Player Detail view shows all player information including the profile picture as well as lists all the games the player plays with the player's current score. Must also have the ability to list all player achievements (either overall or per game). (20 marks)
- A web-based update capability for at least the player information (including profile picture) and a player score in a game. (20 marks)
Bonus: (up to 30 marks)
- +10 marks - For a "professional-quality" web site that is easy to navigate and looks good. Bonus marks will be subjectively assigned based on perceived effort, functionality, and design.
- +20 marks - For an automated way (such as a background process) for updating player scores, adding achievements, and adding new players to games "randomly" to mimic how game data would be collected in real-life.
Submission
Submit all code and files using Connect. You can demonstrate your web site at any time for feedback and marking.
Home