The Database Group l David Hockenberry l Brian

The Database Group l David Hockenberry l Brian Ge l Paul Juckiewicz l Logan Everett l Laurel Andersen l Di Zhang

Overall Database Responsibility (Migration from Access to My. SQL) By David Hockenberry

Responsibilities Migration from Access to My. SQL Preserve existing Microsoft Access database Supply support to database group members

Microsoft Access is a Relational Database Management tool Bundled with Microsoft Office Best selling database in the world Microsoft platform dependent

My. SQL is a Relational Database Management System (RDMS) Free Most popular open source database in the world Platform independent

Why use My. SQL? Many different solutions: SQL Server, Oracle, DB 2, Sybase, etc… Why My. SQL? – Fast – According to My. SQL Benchmarks, faster then most on Microsoft OS – Easy to download and install – Platform independent – More then 20 different platforms including Mac, Linux, Unix and Windows – Accessible – Fully networked, accessed from anywhere – Free

Both Access and My. SQL? Access – The Watson Adventure Game Disconnected mode My. SQL – The Watson Adventure Game Connected mode

Future Implement Stored Procedures – Move queries from server code to the database – Benefits: Security, performance, and business logic abstraction. Implement Transactions – For data commitment to the database – Benefits: If something goes wrong during a transaction, data is not lost.

Table Creation Scripts By Brian Ge

The Task Migrate the tables from the Microsoft Access database to My. SQL. Slight Problem – Access provides no way to export the table structure of the database.

The Solution There were multiple solutions for overcoming Access’ short fallings. These include: – Commercially available services and software. – In-house developed software. – Hybrid solutions.

Commercial Solutions You can purchase commercial software that will convert your database to another type of database, or you can hire people to do it for you. Pros – Potentially time saving. – Easy, hands off solution. – Accurate (if using consultants) representation of your existing database. Cons – Expensive! – Information Control

In-house Software We could have also built our own software package that would comb through the existing database using available APIs and then formatting the data so that it could be imported into another database. (In essence do what the commercial programs do. ) Pros – No additional monetary cost. – Customized solution. Cons – Very difficult API to work with. – Extremely time consuming to get it right.

Hybrid Solutions We eventually decided on a “hybrid solution” to our table migration problem. The size of the WAG database was of sufficiently small size that we could look at all the Access tables, and write a SQL script that would re-create the tables.

Hybrid Solutions After determining the basic structure of the Access database, we were able to create a script that can be directly imported into My. SQL.

Other Tasks My other duties included supporting the existing Access database. In that regard, I have also been working closely with other team members to keep the Access database up to date with our new My. SQL database.

Scripts to move existing data to My. SQL & Support Existing Access DB By Paul Juckiewicz

Tasks Write scripts in Java to automate the move of existing data from Access to My. SQL Support existing Access Database

Script Mechanics Scripts will be written in Java using the JDBC API (Java Database Connectivity) and My. SQL Connector/J

JDBC, My. SQL Connector/J JDBC API allows the access to any relational database from Java whether you’re on Linux, Solaris or Windows My. SQL Connector/J is the official JDBC driver for My. SQL (allows Java to connect to My. SQL)

Script Method Script will initially dump the tables in My. SQL Script will than move existing data from the Access Database to existing tables on the My. SQL Server Existing Data in Access will not be changed in anyway through this script

Support Existing Database Manage database content Make sure database is synchronized with the My. SQL database at all times. Make sure there are no inequalities between Access and My. SQL throughout the development of the database structure

Possible Future Releases Create GUI for the script to move data from Access to My. SQL Create more specific functionality for the movement of existing data from Access to My. SQL

Expanding Challenge Data to Include EE and ME By Laurel Andersen

Overview Each challenge consists of 5 questions with 4 choices for each question These questions are tailored to describe the core courses for Electrical Engineering (EE) and Mechanical Engineering (ME)

Core Courses For EE Discovering Engineering I Technical Communications I Discovering Engineering II Technical Communications II Digital Logic Design Electrical and Computer Engineering Seminar I Probabilistic Systems I Electrical Circuits Microprocessors Electronics I Signals and Systems Electrical and Computer Engineering Seminar II EE Design Lab Electromagnetics Control Systems Communication Systems Senior Project I I

Core Courses For ME Discovering Engineering I Technical Communications I Discovering Engineering II Technical Communications II Engineering Mechanics of Deformable Bodies Electrical Circuits Engineering Computer-Aided Engineering Thermodynamics Science of Engineering Materials Engineering Analysis Machine Design Fluid Mechanics Project Management Engineering Computational Methods Vibrations Heat Transfer Control Systems in Mechanical Engineering Senior Lab Senior Project II

Table Design Challenge. ID: Primary Key Challenge. Question: stores the question Challenge. Answer(1 -4): stores 3 incorrect answers and one right one Right. Answer: designates the right answer Faculty. ID: designates the faculty member that will present the test Hot. Spot. ID: gives the hot spot ID

Ideas For The Future Expanding challenges to include life challenges Ex: Your laptop has crashed while you were writing your term paper. You can either: A: Try to fix it yourself. B: Ask Computer Services for help. C: Cry yourself to sleep. Choice A will give you 50 experience points. You successfully retrieved your paper. Choice B and C will take away 50 experience points. You should know better than to ask Computer Services for help. Crying never gets you anywhere.

Expanding and Modify Database Tables to Accommodate New EE & ME Faculty Data By Di Zhang

Task To expand the current existing database of Watson faculty. – Current faculty– Computer Science – Expand Electrical Engineering(EE) Mechanical Engineering(ME)

New Faculty Data The data required for the EE & ME professors is the same as those from CS. – Pictures – Names – Phone Numbers – Emails – Course IDs

Tables Updates – Course Table Course. ID – Faculty Table Major. ID – Hotspot Table Major. ID – Character Table Major. ID

Course. ID CS Courses are labeled 1 - 42 EE & ME courses will be labeled from 43 on

New Field – Major. ID (Required – Number) CS = 0 EE = 1 ME = 2 Helps to distinguish students and faculty from the three departments when performing data searches

Look Ahead New field instead of new table, more suitable for future department expansion – More Organized – Less work for those who will be working on this game next semester and on

Tables

My. SQL Maintenance Tool By Logan Everett

Purpose Remote access to My. SQL Databases – Dump all data in table – Allow user edit – Upload back to table Assume server is shut down

Implementation Use Java/JDBC Needs restrictions to preserve relational integrity

Critical Features JTable-based GUI and Data Model General DB Info and List of Tables Foreign Key Preservation Add, Delete, Edit Rows Local Backup/Restore System