IS215 Database Lab and Assignment
Shared by: ljm64622
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 (email@example.com) 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).