CS435 In-lab Exercise:  DBMS, SQL and PHP (an intro)


In this exercise we'll jump start you on using MySQL and PHP.  For those of you who are experts already, there is a challenge problem at the end we'd like you to work on.

If you are taking CS462 or have taken CS462, much of this will seem easy and familiar.  We urge you to start off helping the students with no experience, acting as tutors, and the move on to one of the challenge problems.

On the challenge problems, you may work in groups.  Zip up your files and email them to the instructor with the names of your team, and we'll demo the best or most interesting system.  That team will get a small but fabulous prize!

First, we have a database set up for you all to play with.  Here are the details:

Hostname:  krondor.cs.virginia.edu
Database name: CS453
student user: cs453student
student pass: cs453pass

Second, you can log into a Web-based MySQL interface for this database server using the login info about.  Go to this link to access phpMyAdmin:
phpMyAdmin: http://krondor.cs.virginia.edu:8008/phpMyAdmin/
Note that those of you using MAMP etc on your own machine have a phyMyAdmin installed there.

Beginners:  There is a nice one-chapter intro to databases and SQL in a Java book I like.  This is available on line if you are on Grounds or using VPNLite.  Here's the info:

The booK:  Just Java, by Peter van der Linden.
Chapter 23 on Relational Databases and SQL.   Direct link (I think).
Link to Safari to the entire book in its online version (must be on UVa network).

Beginner Activity #1

We'll ask you to create an example database table based on the example in the Just Java book.

(1) Log into phpMyAdmin

(2) We'll create a table from Just Java book example.  Here's the SQL from the book:
CREATE TABLE Person (
       name VARCHAR(100) PRIMARY KEY,
       age INTEGER, 
       lives_in VARCHAR(100)
);

But you don't have to type in SQL.  To create the table, click on the CS453 database you see in phpMyAdmin.  See the list of tables that are already there?  At the bottom, there's a form to create a new table.
You should name your table PersonJJxxx where xxx is your initials or user-id.  (So we can have lots of students create tables.)

(3) Select the table, and use the phpMyAdmin commands to look at its structure.
Use commands to Insert some information into that table.  Add several rows.
Use phpMyAdmin commands to browse the database to see what's in it.

(4) Use the Search command to search your table for values that have a certain age or for Persons older than a certain age.
Look at the SQL that's generated by the form to see the structure of what the WHERE clause does.

Beginner Activity #2

We want you to write some code that reads data from the table you just created.  Also, modify this code to select rows with certain properties.

Look at these examples and modify them to do things to your database table.



Challenge Activity #1

Build me a student tracking system!

Tasks:

Students:
a) register
b) update information

Staff:
a) Report of students by major / minor
b) Generate email list by major / minor
c) Enter comment about student
d) View info about student
e) Remove student
f) Remove students who have graduated

Both:
a) login
b) reset password

Ideas for data to be stored:
Student Info:
last name, first name, UVa email-ID
Major (BSCS, BSCpE, BACS or name of other), minor
First term at UVa
Last term at UVa (so we can remove info after this date)
date info last updated
list of comment entries (comment text, by whom, date)

User Info:
login ID (UVa email ID)
password
date last logged in
administrator flag
faculty flag

Use of PHP sessions:
remember login id from form to form
remember if facutlty or if admin from form to form

Challenge Activity #2

Facebook user?  Explore how Facebook can be used with PHP.
Go to this link:
http://developers.facebook.com/resources.php