The Watson Game

1
The Watson Game

• Database Group

Members
Joshua Shapiro - Team leaderDan Dauchy - MySQL
table creation scriptsAndy Modansky - MySQL data
transfer scriptAaron Perlstein - MySQL data
maintenance (PHP)Josh Spindel - Challenge
data/database statistics (PHP)
2
Josh Shapiro Why use a database?

• Easy to implement
• Easy to extend
• Efficient
• Easily interfaced with by a wide variety of
  languages/architectures

3
Why MySQL and not Access?

• Access is tied to the Windows platform
• Response times
• Scalability

4
SQL Table Creation Dan Dauchy

• CREATE TABLE challenge (
• ChallengeID bigint(20) NOT NULL
  auto_increment,
• CourseID bigint(20) default '0',
• ChallengeDifficulty varchar(50) default NULL,
  
• ChallengeQuestion text,
• ChallengeAnswer varchar(50) default NULL,
• KEY challenge_ID (ChallengeID),
• CONSTRAINT FK_wag_Course_Challenge FOREIGN
  KEY (CourseID) REFERENCES course (CourseID)
  
• This creates a table with its corresponding
  fields
• NOTE always create tables with foreign keys
  last (always after the tables that they
  reference)

5
SQL Insertion Example

• INSERT INTO challenge (ChallengeID, CourseID,
  ChallengeDifficulty, ChallengeQuestion,
  ChallengeAnswer (1, 2, '1', bla bla?',
  correct)
• Inserts values into corresponding fields of the
  challenge table

6
Script overview Andrew Modansky

• Export database structure from Microsoft Access
  in SQL
• Use the exported data as input into the script
• Relationships -gt Foreign Keys
• Tables without foreign keys must be created first

7
JDBC Example

• Java - robust database interfacing through the
  JDBC library

import java.sql.
Connection connClass.forName("org.gjt.mm.mysql.D
river")conn DriverManager.getConnection(url,
username, password)Statement st
conn.createStatement() st.executeUpdate(SOME_SQL_
STATEMENT)
8
Data maintenance Aaron Perlstein

• MySQL shortcoming no GUI
• But PHP makes it easy!

PHP - (Hypertext Preprocessor)Object oriented
programming language particularly suited towards
web application development. Runs by means of an
interpreter that is either called at compile time
or integrated into the web server binary.
9
Example PHP code Connecting to a database

• connect mysql_connect("localhost",
  username,password) or die("Connection Failure
  to Database") mysql_select_db(database,
  connect) or die ("Database not found.")result
  mysql_db_query(database, query) or
  die("Failed Query")
• Concise, easy to read code
• Makes interfacing with databases a pleasure

10
What the administration module does

• Acts as Access replacement
• Add/Delete/Modify for existing tables
• Integrated with in-game statistics module, which
  is also written in PHP and the responsibility
  of...

11
Web statistics Joshua Spindel

• Relevant game statistics will be displayed from
  database
• Live statistics also available
• Searchable

12
Data collection

• Game expansion
• Electrical Engineering
• Mechanical engineering
• Data goes in both Faculty and Challenge tables