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:

• faculty2008: a listing of CS faculty and staff at Grinnell for 2008-2009
• room: a listing of most of the rooms on the 3800 corridor of the Science building
• roomCategories: a listing of room categories (e.g., Faculty Office or Commons or Classroom)
• facultyRoom: a mapping of faculty to rooms (including both offices and research labs)

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.

6. 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.)

7. 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.

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

9. 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

10. 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.

11. 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:

    • Let the database/SQL query do the hard work of organizing and ordering the people.
    • Arrange work with a database, so that you need only one SQL query.
    • Since the table asks you to print office data before lab data, it will be helpful to have the records retrieved for an individual, so that an office record comes before any lab record. (Consider a secondary SORT clause for this.)

Work to Turn In

• Statement of the SQL queries used for steps 2, 3, 4, 5, 6, 7, 8, and 9.
• URLs and scripts for the pages given for steps 10 and 11.

This document is available on the World Wide Web as

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

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