Homework 1 (due 9/18)

Homework is due at class-time on Tuesday (9/18).

We covered most of chapter 1 in the book, and then began talking about the relational data model (chapter 5). We will continue with chapter 5 (up to page 213), and then switch to chapter 7 on SQL.

For this homework you will need Microsoft Access, you can find it in the labs, or get a free license and software at http://www.cti.depaul.edu/resources/msdnaa.asp. Microsoft offers introductory tutorials on Access 2003 and Access 2007.

1. [Pine Valley Furniture and Company Databases] Download the Pine Valley Furniture database as well as the Company database.

The Pine Valley Furniture database is called PFVC.mdb, the company database simply company.mdb. (Both are also available off the class web-page.)

2. [The Company] Open the company database (click or double-click on it). If there is a security warning, select open to open the database. If you are using Access 2003, the tab on the left of the window contains several objects. Select Tables (should be initially selected). If you are using Access 2007, the column on the left should be set to Tables (something you can change using the drop-down list), and you should see the tables in the database listed.

By clicking on the table names you can open them up; do this to answer the following questions:

  1. What are the three departments in this company called?
  2. Which employee(s) make the least money, and how much is it?
  3. Who manages the Administration department?
  4. Which employees work on a project located in Houston?

3. [Pine Valley] Open the pine valley database, and, by looking at the tables, answer the following questions:

  1. Which products are available in the Scandinavia product line?
  2. How many orders did the customer Value Furniture place, and when?
  3. What is the most expensive product that "California Classics" ordered?
  4. What is the primary key in Customer_t?
  5. What is the primary key in Order_line_t?

Marcus Schaefer
Last updated: September 11th, 2007.