CSC 325	Grinnell College	Fall, 2008

Databases and Web Application Design

Laboratory Exercise on Database Storage and Retrieval using MySQL

This laboratory exercise provides practice with working directly with a MySQL database.

Getting Started with MySQL

On the CS/Math/Stat network, the MySQL database facility resides on server aiken. The package is open source, and may be obtained from www.mysql.com.

Steps for this Lab

Prepare to use MySQL:
1. In a terminal window, use ssh to log into aiken.
2. Start up MySQL with the command:
```
   mysql -p
```
  The -p indicates that you will supply a password. Use the MySQL password distributed in class.
3. For this (and all later database-related for this course), specify that you want to use the csc325 database:
```
   use csc325;
```
  As with the C and Java programming languages, many MySQL commands end with a semicolon.

Examining the `faculty` Table

Within a relational database, the table is the basic organizational unit. Data are organized into rows, called records. Columns indicate a type of information; and each column is given a title, called a field name.

For example, in the csc325 database, table faculty contains the listing of faculty for the Department of Mathematics and Computer Science for 1998-1999. The first four records for this table are:


+---------+---------+-----------+------------------------------------+------------------------+-----+------------+
|facultyID|first    |last       |title                               |email                   |phone|office      |
+---------+---------+-----------+------------------------------------+------------------------+-----+------------+
|       1 |Arnold   |Adelberg   |Myra Steele Professor of Mathematics|adelbe@math.grin.edu    |4201 |Science 2405|
|       2 |Nathaniel|Borenstein |Noyce Visiting Professor            |borenste@cs.grinnell.edu|3017 |Science 1131|
|       3 |Marc     |Chamberland|Assistant Professor of Mathematics  |chamberl@math.grin.edu  |4207 |Science 2410|
|       4 |Pamela   |Ferguson   |Professor of Mathematics            |fergusoa@ac.grin.edu    |3017 |Science 1131|

In this table, the first row shows the field names. Each subsequent row provides a separate directory entry, beginning with a unique facultyID number. Thus, the first record provides information for Arnold Adelberg, the Myra Steele Professor of Mathematics, whose office was Science 2405 and whose telephone extension was 4201. Prof. Adelberg's facultyID in this record is the integer 1.

`SELECT:` Accessing Existing Tables and Data

Steps for this Lab

Issue the following commands to get an overview of the csc325 database, its faculty table, and several versions of the SELECT statement.:


   show tables;
   describe faculty;
   SELECT * FROM faculty;
   SELECT last, email FROM faculty WHERE title > "Associate";
   SELECT faculty.* FROM faculty WHERE last="Moore" AND first="Emily";

In each case, record the results obtained.

It is common to capitalize keywords in SQL queries, but to what extent is capitalization necessary?
1. In a SELECT statement, does the keyword SELECT have to be capitalized?
2. In a SELECT statement, can you change the capitalization of the table faculty (e.g., try an initial capital: Faculty)?
3. In a SELECT statement, can you change the capitalization of a field (e.g., try an initial capital for Title?
Using the textbook for reference, develop queries to retrieve the following:
1. Find all faculty whose telephone extension is 3017.
2. Find all faculty whose telephone extension is greater than or equal to 2405.
3. Find all faculty whose office numbers are between "Science 2412" and "Science 2420" (inclusive).
4. List all faculty who are Associate Professors in descending alphabetical order.

Resetting the Database

Subsequent parts of this lab will ask you to change existing entries in the faculty table and to add new entries. Periodically, however, you may want to restore the original database table.

Reset the faculty database by running the script fac-dir-db-reset.php.
(In the next lab when we discuss HTML/PHP scripting, we will examine how this script works.)

`INSERT:` Adding New Data

The insert statement adds new records to the database. The simplest version of this statement specifies each field, in the order specified in the database. Thus, the following statement would add Arnold Adelberg's record to the campus directory:


INSERT INTO faculty VALUES
(NULL, "Arnold",    "Adelberg",
              "Myra Steele Professor of Mathematics",
              "adelbe@math.grin.edu",     4201,"Science 2405");

As this example illustrates, it is common for keywords in an SQL statement to be capitalized, although this is not necessary.

The complete statement to add all records to this departmental database lists each record in parentheses, separated by commas.

When inserting records, we could include the facultyID number explicitly, using a statement:


INSERT INTO faculty VALUES
(1, "Arnold",    "Adelberg",
              "Myra Steele Professor of Mathematics",
              "adelbe@math.grin.edu",     4201,"Science 2405");

However, since facultyID is defined with auto_increment, we can leave that field as NULL, in which case the database will determine an appropriate value; more about auto_increment shortly.

In yet another variation of the insert statement, we specify explicitly which value goes with which field:


INSERT INTO faculty 
   set first = "Arnold",
       last  = "Adelberg",
       title = "Myra Steele Professor of Mathematics",
       email = "adelbe@math.grin.edu",
       phone= 4201,
       office= "Science 2405";

Although this form is somewhat wordy, it clearly identifies exactly which value goes where within a record. Again, since no facultyID is given, the auto_increment attribute will assign a new record number to this field.

Steps for this Lab

Insert 3 records of your choosing into the database. To avoid confusion among other students doing this lab, use your last name in each case. Also, use several variations of the insert statement to perform this task.

`DELETE:` Deleting Data

The Delete statement removes records from the database.
In using Delete be sure to limit the scope of your work, since this statement has the potential to remove all records from the database.

The following statement removes all records for those with the last name "Hill" from the database:


   DELETE FROM faculty WHERE last="Hill";

Steps for this Lab

Delete exactly one of your newly-inserted records from the database.

`UPDATE:` Changing Existing Data

The Update statement changes fields within records.
As with Delete be sure to limit the scope of a Update, since this statement has the potential to change all records in the database.

The following statement updates Adelberg's e-mail and office information from the 1998-1999 directory:


   UPDATE faculty
      SET email="adelbe@math.grinnell.edu",
          office="Carnegie Hall 308"
      WHERE last="Adelberg";

Steps for this Lab

Change one of your remaining two newly-created records, perhaps changing a first name and phone extension.

Creating Tables

For this laboratory exercise, we will use the database csc223 that has been created specifically for this discussion.

Grant user permissions

Once a database has been established, the user(s) designated by the MathLAN Administrator can grant access to the database to other users. For the csc325 database, this adding of permissions utilizes a SQL statement with one of two forms:


GRANT insert, select, update, delete, alter
    ON csc325.*
    TO username IDENTIFIED BY 'password';
GRANT insert, select, update, delete, alter
    ON csc325.*
    TO username;

Each form allows the person with the given username to access all tables related to the csc223 database. The first form specifies the password for that access; the second form assumes the user is already known to MySQL and will use the existing password. Specific capabilities allow the specified user to add new records to any table (insert), retrieve records (select), modify existing records (update), remove current records (delete), and change the design of a table (e.g. add, remove, or change columns) (alter). Additional permissions are possible:

create: add new tables
drop: remove tables from the database

For the current lab, permission has been granted for a group account to insert, select, update, delete, alter. Username and password information will be distributed in class, rather than posted on this Web site.

Create tables

The creation of a table involves a specification of the various fields for that table. For the current directory example, this might be done with the following statement:


create table faculty
( facultyID bigint(20) unsigned not null auto_increment,
  first     varchar(25) not null,
  last      varchar(25) not null,
  title     varchar(255) not null,
  email     varchar(255) not null,
  phone     bigint(20) unsigned not null,
  office    varchar(255) not null,
  primary key (facultyID)
);

This SQL statement establishes a new table, called faculty. This table has 7 fields:

Two fields, facultyID and phone are unsigned, 20-digit integers
Two fields, first and last are strings of up to 25 characters
Three fields, title, email and office, are strings of up to 255 characters each

Further, the specification indicates that none of these fields can be null: that is, each of these fields must contain data, and cannot be omitted from a record.

The facultyID field has two additional properties. First, it is a "primary key", meaning that the value in this field must be unique; two records are not allowed to have the same facultyID. In this case, the primary key is a single field, although in other tables a primary key could involve a combination of fields that together would identify a record uniquely. Second, when information is inserted into this table, one could specify the facultyID explicitly, but one also could ask the database to take then next highest integer value for the facultyID field; if this field is not specified, the database will auto_increment the highest field value to maintain a new, unique integer for this field.

Steps for this Lab

Create your own table within the csc325 database.
1. So that you can distinguish your table from those from other students, use your initials as the first two characters of the table name (e.g., HWCDs)
2. Define your table, so that it has at least 4 fields, including at least one character-based field and one numeric field. For example, your table might store information on your CD collection, with the title of the CD, the year it was released, its its initial cost, and a unique ID (as primary key).
Write SQL statements to insert at least 3 records into your new table.

Work to Turn In

Statement of SQL statements for steps 4, 6, 7, 8, 10.
MySQL statement to create your own table in step 9.

This document is available on the World Wide Web as

     http://www.cs.grinnell.edu/~walker/courses/325.fa08/lab-database-mysql.shtml

created 20 August 2008 last revised 20 August 2008
For more information, please contact Henry M. Walker at walker@cs.grinnell.edu.

Laboratory Exercise on Database Storage and Retrieval using MySQL

Getting Started with MySQL

Steps for this Lab

Examining the faculty Table

SELECT: Accessing Existing Tables and Data

Steps for this Lab

Resetting the Database

INSERT: Adding New Data

Steps for this Lab

DELETE: Deleting Data

Steps for this Lab

UPDATE: Changing Existing Data

Steps for this Lab

Creating Tables

Grant user permissions

Create tables

Steps for this Lab

Work to Turn In

Examining the `faculty` Table

`SELECT:` Accessing Existing Tables and Data

`INSERT:` Adding New Data

`DELETE:` Deleting Data

`UPDATE:` Changing Existing Data