IT 240 Week 2 (Lab)

September 13th, 2007

[Pine Valley Furniture Database]

Download the Pine Valley Furniture database from the class web-page at

and start it up by clicking on it. Answer the following questions to get acquainted with the database

  1. How many salepersons are there?
  2. What is the most expensive product?
  3. Which customers do business in the South West?
  4. What Work Center does Jim Jason work at?
  5. What skills does Jim Jason have?
  6. What products are produced in the Work Center that Jim Jason works at?

Relational Model Presentation

[Keys and Relationships]

Answer the following questions about the Pine Valley Furniture database. To see the details of the table design, right-click on the top bar of the window and select “Table Design” (Access 2003) or “Design View” (Access 2007).

  1. What is the primary key in the Employee table? (Both displayed name and real name).
  2. What is the primary key in the Territory table? Are there other possible keys (candidate keys)?
  3. What is the primary key in the Skill table? Are there candidate keys?
  4. What is the primary key in Works_In?
  5. Find other examples of primary keys with more than one attribute.
  6. Find all foreign keys in Product. Which tables do they reference?
  7. Find all foreign keys in Works_In. Which tables do they reference?
  8. Find all foreign keys in Employee. Which tables do they reference?

[Keys and Relationships II]

Access helps you find the information from the previous exercise more easily. In Access 2003 right-click on top of the window with your database and select “Relationships”. In Access 2007 select “Database Tools” on the top, and then click Relationships. Now go back to the last 8 questions and see whether it is easier to answer them now.

[SQL]

Here is a simple SQL statement:

SELECT *

FROM employee_t;

Run this on your database; in Access 2003 select Queries in the tab on the left, click “Create query in Design View”. In Access 2007 select Create/Query Design. In both continue by hitting “Close” to get rid of the pop-up box, then right-click on the top of the Query window bar and select SQL View. Then enter the SQL statement above and “Run” it (the red exclamation mark on the top bar).

* selects all attributes, you can also choose particular ones, e.g.

SELECT Employee_ID, Employee_Name

FROM Employee_t;

Warning: be sure to use the real field names, not the displayed ones.

  1. Write a SQL query that lists all product lines
  2. Modify your previous SQL query so it only lists the names of the product lines.
  3. List names of all employees, together with address and state information.

[MoreSQL]

We can add conditions to an SQL statement using the WHERE clause:

SELECT *

FROM employee_t

WHERE employee_city = “Nashville”

Try running this.

  1. List the product descriptions of all products with a cherry finish.
  2. List products that are more than $200.
  3. List orders placed before 11/1/2000.
  4. List customers doing business in the South East territory.