Project: Deliverable 2

Database

• Because not everyone has taken the database course, we are providing a sample database that can be used in your projects.
  • We provide the database in Microsoft Access format (seg3202.mdb) and as a My SQL script (seg3202.sql)
  • See below for installation instructions
• We have not extensively tested it, so you might run into some unforeseen problems. If you do find any issues, don't hesitate to post your comments at the bottom of the page so that more inexperienced students can learn from them.
• We didn't go into detail with the database constraints so don't be surprised if you have some constraints that need to be implemented in the business logic layer. Hopefully we did not over constrain certain aspects.
• The database does not cover all aspects described in the requirements, seing as deliverable two should only be concerned with the team creation subsystem.
• You should be able to use this database to build the functionality required to support people from multiple course sections working in the same team, but I (Jason) would not focus on this. It is vaguely covered in the use cases, vaguely covered in the DB and the logic-layer will be painful to implement. [to be confirmed]
• If you find it is hard to implement the logic layer over this database, feel free to modify it.

Interesting SQL Queries

• To do.
• Meanwhile, peruse the web (http://www.1keydata.com/sql/sqlselect.html) and re-read Lab Four for information.
• You should be able to pick up the basics of SQL very quickly. You need to know very little SQL to be able to complete this deliverable; we don't care about performance aspects or normalization in this course.

Getting it to run on My SQL

• Go back and read Lab Four.
• Make sure you have My SQL and My SQL Connector/J installed on your computer. Don't hesitate to install Connector/J in your to the jre\lib\ext directory of your JDK.
• We will assume a default installation where root's password is empty.
• Copy seg3202.sql to the same directory as your mysql.exe and mysqld.exe file (mysql\bin).
• As in Lab Four, go to mysql\bin from the command prompt and run mysqld -–console or start it as a service
• Type mysql -u root < seg3202.sql
  • If you have a non-empty password, use the -p argument.
• If all works well, no output should be given.
• Test it out:
  • mysql -u root seg3202
  • show tables;

+-------------------+
| Tables_in_seg3202 |
+-------------------+
| courses           |
| coursesections    |
| mixedteamsections |
| professors        |
| programs          |
| registrations     |
| students          |
| team_members      |
| teams             |
+-------------------+

• If you want to re-run the script, type drop database seg3202; at the mysql prompt and rerun the installation script.
• Also included in the zip file is DatabaseTest.java, which by default connects to the local mysql database and displays the student number and name of the first student. You can then navigate through the list.

• If all goes well you should see something like this:

• Error messages will be shown in the console.

Getting it to run on Microsoft Access

• http://www.developer.com/db/article.php/942641 provides a good guide on how to do this (the example comes from there)
• We have to create an ODBC DSN to be able to connect to our Microsoft Access database from Java (can't specify the filename directly).
• Basically, we define a global reference on our computer that points to this file.
• Configure an ODBC DSN
  • http://support.microsoft.com/default.aspx?scid=kb;en-us;305599&sd=tech
  • http://www.egs-howto.com/fr/bdd/odbc_install.php (French!)
• Change DatabaseTest.java to use this DSN.

• If all goes well you should see something like this:

• Error messages will be shown in the console.

My SQL + ODBC?

• You can also connect to a My SQL database via ODBC. See http://www.netadmintools.com/art429.html and http://www.netadmintools.com/art430.html

Discussion

• Share your experience here

-- Jason Kealey - 09 Nov 2005