IS215 Database Lab and Assignment

							IS215 Database Lab and Assignment
In this lab, you will learn how to query a database using structured query language
(SQL). As you are probably aware from class, data management is central to any
information system be it my.emich or your myspace page. By learning a little SQL, you
will understand what goes on under the hood as you use these applications. It will also
help you better understand their limits and possibilities. Finally, SQL will help you get
ahead in the job because it is a skill that not many have but is needed to directly access
one of a corporation’s assets, its data.
We’ll be using Microsoft Access, a personal database program that comes with Microsoft
Office. Many companies use Access to manage marketing and sales data, so knowing
Access will be a plus in future jobs.

Lab
The first thing you need to do is get the database we will use for this project. It is already
in Microsoft Access format. Go to Bud’s web site at:
http://people.emich.edu/fgibson/IS215
and select the file “Premium Products.zip”. When you download the file, unzip it to
create the file Premium Products.mdb. This is the Access file.
Once you have unzipped the file, double click on Premium Products.mdb. You’ll receive
two warnings. First, indicate not to block unsafe expressions as follows:




Next, say to open the database Premiere Products.mdb
After answering these two dialogs, you will see the following window in Microsoft
Access. This window shows the tables in your database. Recall from class that tables are
used to store data about one entity (person, place, thing, etc.). This database has
information about four entities: customer, order_line, orders, part, and rep.




We want to run queries on the tables in our database to get data out. You will spend the
lab doing practice queries. This will familiarize you with how the data is organized in the
tables as well as giving you a chance to learn SQL. In the assignment, you will
demonstrate the knowledge you gained by formulating your own queries to answer
questions.
So, click on the queries icon as in the database view below.
Now double click on “Create Query in Design view” wizard. Click close on the on the
table selection dialog, and your screen should look as follows:




To get into SQL mode, click on the SQL button in the upper left hand corner. You’ll get
a little query worksheet as you see in the following screen shot. Type in “select * from
orders;” exactly. The SQL tutorial will help you understand the details of this query. For
now, all you need to know is that it lists all the items in the table “orders”. To see the
query run, click on the exclamation point as highlighted below.
You’ll get the following result if you did it right.




Now, you need to save your query so you can run it again. Click on the save icon circled
above. You’ll get a dialog asking you to name your query. If you call it Practice 1,
you’ll get a screen like the following once you press OK.
Now, with these preliminaries behind you, go through the tutorial. As you do the tutorial,
create each query (like we just did) and run it.

Assignment
Microsoft Access is available in COB 301. You can also use your home computer with
Microsoft Access installed from Microsoft Office. You will use the Premiere Products
database we used for the tutorial.
You will develop SQL queries to answer five questions. You should run the queries and
save them in your database. You will then email your database to Bud Gibson
(fgibson@emich.edu) by midnight, November 7, 2006.
You should complete the assignment in groups of two or three.
Here is the exact format you should use for your email:
Subject: IS215 Database assignment
Body: Names of group members and their e-numbers.
Attachment: A zip file containing your completed version of Premier Products.mdb
Here are the questions you should answer. In the database you submit, label your
answers Question 1, Question 2, etc.
   1. List the part number, description, and price for all parts.
   2. List the number and name of each customer represented by sales rep 35 or sales
      rep 65.
   3. List the part number, part description, and on-hand value (units on hand * unit
      price) of each part in item class AP. (On-hand value is really units on hand *
   cost, but there is no COST column in the PART table.) Assign the name
   ON_HAND_VALUE to the computation.
4. Use the IN operator to list the part number and part description of each part in
   item class AP or SG.
5. List the part number, part description, and on-hand value of each part whose
   number of units on hand is more than the average number of units on hand for all
   parts (Hint: use a subquery).