Phase+5


 * Question 1 (100 points)
 * Project Description
 * The Logical and Relational design of the database
 * The DDL for the database
 * A description of sample data
 * The functionality that your project supports and the SQL queries that are executed for each item of functionality
 * A definition of any data import, triggers, and procedures in your project
 * If a UI is part of the project, describe the UI.
 * Question 2 (20 points)
 * This question asks you to describe whether your project met the original expectations or fell short in some way.
 * Question 3 (0 points)
 * This question asks what you learnt during this project, either related to database or otherwise. There is no credit for this answer, and answering it is optional.


 * Project Files**


 * []**


 * current version of game[[image:http://www.wikispaces.com/i/mime/32/application/zip.png height="32" link="http://datacraft.wikispaces.com/file/view/DataCraft.zip"]] [|DataCraft.zip](12/11/2010) **
 * http://datacraft.wikispaces.com/file/detail/datacraft_website.zip **


 * Project Description**


 * The general idea of the program is still intact from the last phase. We are going to mine data from a video game and use this data to make the game better (through balancing and such). We will be creating our own game. The game will be a shmup (side-scrolling shoot ‘em up). **


 * //DataCraft// is meant to speed up the process of game-balancing, by finding problem areas that game developers can focus their efforts towards. //DataCraft// is a two part process, starting with data-mining. Various players will play through our game that we create. Behind the scenes, we would be recording statistics of the game. These include which ship the player chose, which weapons the players used, how far they got through the game before losing, etc. The next step in the //DataCraft// process would be analyzing the repository of data that is created after players have completed a reasonable amount of games. We could look for any number of things, one being for enemies that are too strong. If one type of enemy ship has the majority of kills, then we could focus our efforts on balancing that particular ship type. **


 * The Logical and Relational design of the database**

From the ER diagram, there are 7 entities in it. The following is the description:

1)Player who has his name plays the game which has ID# as the key, version, runtime, final score and date. 2) Game uses gamepiece which has ID# as the key, type, dead time and build time. 3)Missile which has DMG, weapon which has level, enemy which also has level and ship are all the Game piece with the same key as GP_ID. 4)Ship and enemy both have weapon, and weapon make missile. 5)Missile hit both ship and enemy by DMG and it shows the health of the ship or enemy before it was hit. From the ER diagram, we can conclude the relational schema following: Player (P_name) Primary Key (P_name) no attributes should be NULL  Game( __ G_ID __ ,final score, runtime,version,Played_By) Primary Key (G_ID) Foreign Key (Played_By) References Player (P_name) no attributes should be NULL  GamePeace( __ GP_ID __ ,type,build_time, death_time,Used_In) Primary Key (GP_ID) Foreign Key (Used_In) References Game (G_ID) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">no attributes should be NULL <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Ship(GP_ID) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Primary Key (GP_ID) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (gp_id) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">no attributes should be NULL <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Enemy(GP_ID,E_level) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Primary Key (GP_ID) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (gp_id) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">no attributes should be NULL <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Weapon(GP_ID,W_level, W_owner) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Primary Key (GP_ID)Foreign Key (gp_id) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (W_owner) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">no attributes should be NULL <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Missile(GP_ID, DMGe,Target, health, M_owner) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Primary Key (GP_ID) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (gp_id) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (target) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (m_owner) References GamePiece (gp_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Target can be NULL <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">GameDates(G_ID, datetime) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Primary Key (g_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">Foreign Key (g_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">References Game (g_id) <span style="color: black; direction: ltr; display: block; font-family: Calibri; font-size: 12pt; margin-bottom: 0pt; margin-left: 0.5in; margin-top: 0pt; text-align: left; unicode-bidi: embed;">no attributes should be NULL
 * The DDL for the database**

Create Table Player ( p_name char(20), Primary Key (p_name) );

Create Table Game ( g_id number, final_score number not null, runtime binary_double not null, version char(10) not null, played_by char(20) not null, Primary Key (g_id), Foreign Key (played_by) References Player (p_name) );

Create Table GamePiece ( gp_id number, build_time binary_double not null, dead_time binary_double not null, used_in number not null, type number not null, Primary Key (gp_id), Foreign Key (used_in) References Game (g_id) );

Create Table Ship ( gp_id number, Primary Key (gp_id), Foreign Key (gp_id) References GamePiece (gp_id) );

Create Table Enemy ( gp_id number, e_level number not null, Primary Key (gp_id), Foreign Key (gp_id) References GamePiece (gp_id) );

Create Table Weapon ( gp_id number, w_level number not null, w_owner number not null, Primary Key (gp_id), Foreign Key (gp_id) References GamePiece (gp_id), Foreign Key (w_owner) References GamePiece (gp_id) );

Create Table Missile ( gp_id number, dmg binary_float not null, target number, health binary_float, # health of the target before dmg was applied m_owner number not null, Primary Key (gp_id), Foreign Key (gp_id) References GamePiece (gp_id), Foreign Key (target) References GamePiece (gp_id), Foreign Key (m_owner) References GamePiece (gp_id) );

Create Table GameDates ( g_id number, datetime timestamp not null, Primary Key (g_id), Foreign Key (g_id) References Game (g_id) );


 * A description of sample data**

Sample data from the database


 * Player Table**

P_NAME

130.215.172.31 130.215.173.141 130.215.248.230


 * Game Table**

G_ID FINAL_SCORE RUNTIME VERSION PLAYED_BY -- --- -- -- 1 2 1.577E+003 1 130.215.173.141 6 3 1.485E+003 1 130.215.173.141 7 4 1.424E+003 1 130.215.172.31


 * GamePiece Table**

GP_ID BUILD_TIME DEAD_TIME USED_IN TYPE -- -- -- -- -- 5 2.41E+002 5.7E+002 1 1 4 2.41E+002 1.577E+003 1 1 10 3.99E+002 1.034E+003 1 1


 * Ship Table**

GP_ID

0 100 200


 * Enemy Table**

GP_ID E_LEVEL -- -- 5 1 10 1 16 1


 * Weapon Table**

GP_ID W_LEVEL W_OWNER -- -- -- 1 3 0 2 3 0 3 3 0


 * Missile Table**

GP_ID DMG TARGET HEALTH M_OWNER -- -- -- -- -- 2408 1.0E+001 2380 1.0E+001 2374 2447 1.0E+001 2415 1.0E+001 2374 2466 1.0E+001 2435 1.0E+001 2374


 * GameDates Table**

G_ID

DATETIME

16 11-DEC-10 04.26.06.000000 PM

18 11-DEC-10 07.01.08.000000 PM

20 12-DEC-10 07.58.49.000000 PM

Sample csv sent from the game to the perl script

The CSV string sent by the game doesn't map directly into the database (csv has different order and some things are computed on the perl script). All of the CSV formats are in comments in the perl script, but I will put one here.

For missile the format is "Weapon,<gp_id>,<build_time>,<dead_time>, ,<w_level>,<w_owner>".

csv=Game,3,1485,1|Ship,0,0,1485|Weapon,1,0,1485,1,3,0|Weapon,2,0,1485,2,3,0|Weapon,3,0,1485,3,3,0|Enemy,5,9,808,1,1|Weapon,4,9,1485,1,1,5|Missile,9,107,638,1,10,-1,10,6|Missile,10,109,563,1,10,-1,10,4|

Sample XML response from the perl script back to the webpage

The perl script does not know about any of the pre-made queries. It only knows how to run an SQL query, then return all of the columns names a﻿nd all of the rows that were outputted. Each column name has an abbreviated id associated with it that the javascript can use to identify each value in a row.

<?xml version="1.0" encoding="UTF-8"?> <response sql_query = "SELECT GamePiece.type, SUM(Kills.kcount) Kills FROM GamePiece, Enemy, Weapon,( SELECT m_owner, COUNT(*) kCount FROM Missile WHERE (target is not null)AND (health - dmg &lt;= 0) group by m_owner) Kills WHERE (Weapon.gp_id = kills.m_owner) AND (Enemy.gp_id = Weapon.w_owner)AND (GamePiece.gp_id = Enemy.gp_id) group by GamePiece.type"> <column name = "TYPE" id = "a1" /> <column name = "KILLS" id = "a2" />


 * The functionality that your project supports and the SQL queries that are executed for each item of functionality**


 * 1. Store data from game into database.**

This is accomplished by sending a POST command to users.wpi.edu/~chrisw/database/cgi/datacraft2.pl. The POST command would have one parameter 'csv' that would be a csv list of all of the important data in the game.

The perl script will return errors if there are any.


 * 2. Run pre-made Queries from a Website**

These queries are supposed to help the developers balance the game, by displaying information in a useful way.

This is accomplished by sending a GET command to users.wpi.edu/~chrisw/database/cgi/datacraft2.pl. The GET command would have one parameter 'sql_query' that would be an entire SQL query (see below for examples).

The perl script will return all of the columns and rows that were outputted from the query in XML form. Then, javascript on the webpage will transform the response XML into XML that fusion charts can use to render a graph.

<span style="color: #1a1aa6; font-family: monospace; font-size: medium; line-height: normal; white-space: pre-wrap;">The number of kills per enemy type

SELECT GamePiece.type, SUM(Kills.kcount) Kills FROM GamePiece, Enemy, Weapon, ( SELECT m_owner, COUNT(*) kCount FROM Missile WHERE (target is not null) AND (health - dmg <= 0) group by m_owner ) Kills WHERE (Weapon.gp_id = kills.m_owner) AND (enemy.gp_id = Weapon.w_owner) AND (GamePiece.gp_id = Enemy.gp_id) group by GamePiece.type;

<span style="color: #1a1aa6; font-family: monospace; font-size: medium; line-height: normal; white-space: pre-wrap;">The number of kills per ship type

SELECT GamePiece.type, SUM(Kills.kcount) Kills FROM GamePiece, Ship, Weapon, ( SELECT m_owner, COUNT(*) kCount FROM Missile WHERE (target is not null) AND (health - dmg <= 0) group by m_owner ) Kills WHERE (Weapon.gp_id = kills.m_owner) AND (Ship.gp_id = Weapon.w_owner) AND (GamePiece.gp_id = Ship.gp_id) group by GamePiece.type;


 * <span style="color: #1a1aa6; font-family: monospace; font-size: medium; font-weight: normal; line-height: normal; white-space: pre-wrap;">Retrieve all of the final scores for all of the players **

SELECT Played_By, Final_Score FROM Game, GameDates WHERE (Game.G_ID = GameDates.G_ID) ORDER BY Game.Played_by, GameDates.Datetime;

<span style="color: #1a1aa6; font-family: monospace; font-size: medium; line-height: normal; white-space: pre-wrap;">Number of Enemy Types on the Board at the Time of a Player's Death Per Game

SELECT Enemies.Used_In, Enemies.Type, COUNT(*) num FROM ( SELECT GamePiece.Dead_Time, GamePiece.Used_In FROM GamePiece, Ship WHERE (GamePiece.GP_ID = Ship.GP_ID) ) Ships, ( SELECT GamePiece.Build_Time, GamePiece.Dead_Time, GamePiece.Type, GamePiece.Used_In FROM GamePiece, Enemy WHERE (GamePiece.GP_ID = Enemy.GP_ID) ) Enemies WHERE (Enemies.Used_In = Ships.Used_In) AND (Enemies.Build_Time <= Ships.Dead_Time) AND (Enemies.Dead_Time >= Ships.Dead_Time) GROUP BY Enemies.Used_In, Enemies.Type ORDER BY Enemies.Used_In, Enemies.Type;

<span style="color: #1a1aa6; font-family: monospace; font-size: medium; line-height: normal; white-space: pre-wrap;">Weapon Usage Frequency for Players

SELECT GamePiece.type, ShipWeapons.lvl, COUNT(*) FROM GamePiece, Missile, ( SELECT Weapon.gp_id id, Weapon.w_level lvl FROM Weapon, Ship WHERE (Weapon.w_owner = Ship.gp_id) ) ShipWeapons WHERE (GamePiece.gp_id = ShipWeapons.id) AND (Missile.m_owner = ShipWeapons.id) GROUP BY GamePiece.type, ShipWeapons.lvl ORDER BY GamePiece.type, ShipWeapons.lvl;


 * A definition of any data import, triggers, and procedures in your project**

In order to store the date of every game played, we created the following trigger:


 * CREATE OR REPLACE TRIGGER gameINSERT **


 * AFTER INSERT ON game **
 * REFERENCING NEW AS newRow **
 * FOR EACH ROW **


 * BEGIN **


 * insert into gamedates values (:newRow.G_ID, sysdate); **


 * END; **


 * If a UI is part of the project, describe the UI.**

The UI is pretty simple. It consists of a single web page:


 * []**

On the left are a bunch of pre-made queries that we wrote. If you hover over them, it will give you a brief description of what they are. If you click on them, it will show a graphical representation of the SQL query response.


 * This question asks you to describe whether your project met the original expectations or fell short in some way.**

Chris: I think the project managed to get all of the core functionality that we planned done, with some extra goodies (like the FusionCharts graphs). One of the largest hurdles for the project was getting game data from the game into the database, but we finally managed to get a CSV protocol ironed out and communication between the game and my perl script a few days ago. We also managed to come up with some interesting queries that can be executed from the webpage and the FusionCharts graphs helps immensely to visualize the data.

The only unfortunate thing was not being able to acquire enough meaningful data, from having others play the game. The data consists only of a few test cases and our play-throughs of the game.