The Database Group

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

2. Overall Database Responsibility (Migration from Access to MySQL) By David Hockenberry

3. Responsibilities • Migration from Access to MySQL • Preserve existing Microsoft Access database • Supply support to database group members

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

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

6. Why use MySQL? • Many different solutions: SQL Server, Oracle, DB2, Sybase, etc… Why MySQL? • Fast – According to MySQL Benchmarks, faster then most on Microsoft OS • Easy – 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

7. Access – The Watson Adventure Game Disconnected mode MySQL – The Watson Adventure Game Connected mode Both Access and MySQL?

8. 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.

9. Table Creation Scripts By Brian Ge

10. The Task • Migrate the tables from the Microsoft Access database to MySQL. • Slight Problem • Access provides no way to export the table structure of the database.

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

12. 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

13. 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.

14. 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.

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

16. 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 MySQL database.

17. Scripts to move existing data to MySQL & Support Existing Access DB By Paul Juckiewicz

18. Tasks • Write scripts in Java to automate the move of existing data from Access to MySQL • Support existing Access Database

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

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

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

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

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

24. Expanding Challenge Data to Include EE and ME By Laurel Andersen

25. 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)

26. 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 • Senior Project I I

27. Discovering Engineering I Technical Communications I Discovering Engineering II Technical Communications II Engineering Mechanics Mechanics of Deformable Bodies Electrical Circuits Engineering Computing 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 I Senior Project II Core Courses For ME

28. Table Design • ChallengeID: Primary Key • ChallengeQuestion: stores the question • ChallengeAnswer(1-4): stores 3 incorrect answers and one right one • RightAnswer: designates the right answer • FacultyID: designates the faculty member that will present the test • HotSpotID: gives the hot spot ID

29. 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.

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

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

32. 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

33. Tables • Updates • Course Table • CourseID • Faculty Table • MajorID • Hotspot Table • MajorID • Character Table • MajorID

34. CourseID • CS Courses are labeled 1 - 42 • EE & ME courses will be labeled from 43 on

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

36. 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

37. Tables

38. MySQL Maintenance Tool By Logan Everett

39. Purpose • Remote access to MySQL Databases • Dump all data in table • Allow user edit • Upload back to table • Assume server is shut down

40. Implementation • Use Java/JDBC • Needs restrictions to preserve relational integrity

41. 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