INFM 718N Homework 2
Due 6 PM Thursday March 9

The assignment is to: 

1. Create a MySQL database that implements the design described in
   class.  The procedures for doing this are described below.  You can
   use either WAMP or OTAL for this (you can also use other MySQL
   installations, but only if you can do so without help -- assistance
   is generally possible only for WAMP and OTAL).

2. Create an SQL query to list the names of each project along with
   the name of the client for the associated project

3. Run that query and submit both the query itself and the result of
   running the query using MySQL to the TA (pverdines@yahoo.com)
   before 6 PM on Thursday March 9.  You can either use cut and paste
   to include the text in an email, or you can do a sceeen capture
   (alt-printscreen on a PC to capture only a window) and send an
   image of the screen (one way to save the result of a screen capture
   on a PC is to paste it into accessories->paint).  

Note that a team assignment (a requirements description) will be due
that same day as this individual homework, so you would be wise to
complete this assignment long before that date.

To do the homework using OTAL, ssh to otal.umd.edu and run mysql using
your glue userid and the password Patricia sent you by email (if you
did not receive that email, please let both Patricia and me know
immediately).  Then issue the use command to connect to the database
named in that email.

To do the homework using WAMP, log in to mysql as root and create a
database called "project" and give some user the necessary permissions
to use it.  Then log in to mysql as that user and issue the use
command to connect to the project database.

The file tables3.txt contains a set of commands that can be used on
OTAL or WAMP to create and populate the tables and a test query that
you can use to be sure everything is working.  Because that file
contains personal information (the names of the students in the class
and their email addresses), it is being sent only by email.  Because
referential integrity constraints are enforced, the order of the
commands in that file is important.

There are two ways to create an SQL query.  The most straightforward
is to write it from scratch using the syntax described in the book.
If you don't understand that syntax, this would be a good time to
figure it out!  The other way is to implement the same database in
Microsoft Access, construct the query using the "design view", and
then View->SQL to see the equivalent SQL.  This is possible in this
case because I created (very nearly) the same database for you
(project.mdb) and sent it around last week (the differences between
that Access version and the MySQL version in table3.txt are minor, and
would not affect the SQL syntax).  This produces a different SQL
expression than you will be used to, but if you do both ways correctly
they should produce the same results.  You can submit any working
query as your homework (regardless of syntax), but regardless you
should persevere until you are able to write SQL queries without
reference to Access because duplicating complex databases in Access
and MySQL could be a lot of work.

The main purpose of the graded part of the homework is to ensure that
you have the tools at hand that you will need to learn and use MySQL,
and that you can make use of those tools.  Once you complete the
assignment, you should also several (ungraded) things, such as:

- Create an SQL query to print the name and contact information for all the
  PHP programmers.  This will be more complex than the homework.

- Alter the structure of the database so that NULL values are not
  allowable in the userid and password fields.  To do this, you will
  need to create a new "coursemember" table, since instructors and
  students have userids and passwords but clients do not.  Then create
  a query that uses the new table.

- Delete all the tables and create a new database for something from
  scratch.  Something related to your project would be a good choice,
  although you need not make it complete (or even particularly well
  designed the first time!).  The goal here is to learn how to
  describe and populate tables of your own design.  You'll undoubtedly
  make some mistakes, and learnign to correct those mistakes is an
  important part of the process.  So keep your first database
  relatively simple (at least 2, but no more than 3 tables).

Enjoy!