CSCI 1300 Lab: Exploring a Database

This lab is designed to familiarize you with one category of application software, specifically database management software. Though we work with one specific product in this lab, the concepts and ideas of both databases and application software that we will encounter carry over to many other types of software.

For this lab, you will need access to the Microsoft Access database management program.

1. Locate and start up Microsoft Access on your computer.
2. If you do not see an Open option for the Northwind database on your screen, choose Help from the menu bar, then Sample Databases, then Northwind Access Database.
3. Based on the database window displayed on your screen, carry out the following tasks:

• Tables
  • Answer the following questions:
    1. How many tables are in the Northwind database?
    2. How many customer records are in the Customers table?
    3. What are the fields in a record in the Products table?
    4. What is the cost per unit of Sir Rodney's Marmalade, and what constitutes a unit for this product?
    5. How many discontinued products are there?
    6. What are the complete contents of the Notes field for employee Janet Leverling?
    7. Open the Shippers table. Click on the left-most button on the top row of buttons (right below the menu bar) on your screen to change to Design View.
       1. What data type and field length are the ShipperID field?
       2. What field is used to index the Shippers table?
       3. What are the allowable data types for a field according to the drop-down menu shown for Data Type?
• Forms
  • Answer the following questions:
    1. How many forms are in the Northwind database?
    2. What is the difference between the Orders form and the Orders subform?
    3. Which of the following countries can you NOT print customer labels for?
       • Canada
       • Chile
       • Israel
       • Lichtenstein
       • Slovenia
       • Thailand
  • Open the Categories form. Using the arrow buttons at the bottom of the window, move to a blank form. Add a new category of Frozen Treats, with a description of "Ice cream, sorbet, frozen novelties." Close the form, open the Categories Table, and print out the modified table (NOT the form).
  • Print the customer labels for the United States only. What order are these labels printed in?
• Queries
  • Answer the following questions:
    1. How many queries are in the Northwind database?
    2. What is the SQL command for the Category Sales for 1997 query (use SQL view to answer this question)?
  • Copy the Products Above Average Price query to a new query, Products Above $50. You will need to right-click on the query to copy it, then paste as a new query. Open the new query in Design view. Modify the criteria for Unit Price to say >50. Run the query by clicking on the ! button, and print out the query results. Once you have satisfactorily created and run this query, please delete it.
  • Open the Invoices query. Add a criterion to the ShipName field to set it equal to White Clover Markets. Run the query as above, and print out the query results. Do NOT save the query when prompted.
• Reports
  • Answer the following questions:
    1. How many reports are in the Northwind database?
    2. How many pages does the Products by Category report contain?
    3. What is the total cost of the order, according to the invoice, sent to Richter's Supermarket?
  • Print out the first page of the Summary of Sales by Year report.
• Relationships
  Click on the Relationships button on the button bar (or select Tools, Relationships from the menu). Then answer the following questions based on the diagram shown:
  1. Are there any tables which are not connected to at least one other table? If so, which one(s)?
  2. What field is used to connect the Employees table to the Orders table?
  3. Which table is connected to the most other tables?
  4. Which tables is this table connected to?
  5. Which fields connect it to the other tables?
  6. Why do we primarily use ID fields rather than name fields to connect tables?

Turn in the printouts and descriptions indicated in the above tasks to verify that you have completed this lab. An upcoming assignment will have you create a database of your own; therefore you will want to complete this lab as soon as possible so that you can build on this knowledge for the assignment.