CSC 325 Grinnell College Fall, 2008
 
Databases and Web Application Design
 

Laboratory Exercise on Join Operations on a Database

Much of the power of relational databases comes from connecting data that appear in several tables. This laboratory exercise provides experience with several types of join operations that provide these connections.

References

This laboratory exercise assumes you have read the textbook's discussion of "Retrieving Data from Multiple Tables" in Chapter 9.

In addition, you may wish to consult the MySQL 5.0 Reference Manual.

A 2008-2009 Room and Faculty database

In addition to tables covered in previous labs, the csc325 database contains these tables:

The full sequence for creating and populating these tables is available in ~walker/325/database/create-2008-database. Note that the faculty2008 table includes Soren Berg as a Mellon Post-Baccalaureate Research Assistant. In other tables, there is no entry for Mr. Berg for either a room or a telephone.

Room and Room-Category Queries

The first part of this lab asks you to write SQL queries related to rooms and room categories.

Starting Steps for this Lab

  1. Log into the server aiken, MySQL, and the csc325 database.

  2. Use a SELECT statement to retrieve a listing of all classroom categories (just display the category/description, not the roomType code).

  3. Within a SELECT statement, use a simple join to obtain the Cartesian product of the room table and the roomCategories table.

  4. Starting with the SELECT statement of step 3, add a WHERE clause, so that the roomType of the room matches the roomType of the roomCategories.

  5. Modify the SELECT statement of step 4, so that only the room number and the room category are displayed.

Faculty/Staff Queries

The next several steps involve use of several tables, including faculty2008.

  1. Write a query to retrieve the first names, last names, titles, and email addresses of CS faculty and staff. (This is the entire faculty2008 table, except the facultyID.)

  2. Write a query to retrieve the first name, last name, and telephone numbers for all faculty and staff. (If a person does not have a telephone number, the person should NOT be listed as a result of this query. If a person has more than one telephone number, then each number can be listed in a separate record.)

    Hint: You will need to include an inner join of the faculty2008 table, the room table, and the facultyRoom table.

  3. Modify the previous query, so that every person is listed at least once — whether or not they have a telephone number.

  4. Modify the previous query, so that only office telephone numbers are given. (That is, only consider telephone numbers for rooms of category 1.)

Writing Queries with joins in PHP

  1. Write an HTML page that asks the user to select from among the various room categories. (Although this page could be generated directly from the database, it is fine for this lab to have the room categories hard-coded on the HTML page.)

    Then write an HTML/PHP script that reports a list of room numbers and corresponding telephone numbers for all of the rooms in the given category.

  2. Write an HTML/PHP script that prints the relevant entries under the following table headers:

    Faculty or Staff Name Office Office Lab Number Lab Phone
    First Last Number Phone (if any) (if any)

    For this table, all faculty and staff should be listed alphabetically, by last name. If a person has no office or no lab, then that part of the table should be left blank.

    Hints:

Work to Turn In


This document is available on the World Wide Web as

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

created 20 August 2008
last revised 8 October 2008
Valid HTML 4.01! Valid CSS!
For more information, please contact Henry M. Walker at walker@cs.grinnell.edu.