SIMS 257 Assignments

Most of the assignments in this course will be related to your own database and exercises to familiarize you with the systems that we will be using. A second set of assignments will involve the use of an existing database to answer a set of queries, or to modify the database. The same database (called "Diveshop") will be used on both systems. We will discuss these assignments further in class.

Although there are no formal laboratory sessions scheduled for this course, it should be obvious that you will need to spend a lot of time in the School's computer lab (2nd floor, South Hall), or on your home machine if you have the appropriate software. 


 

Assignment 1

Due Tuesday Sept. 12
Using the Diveshop Access database, create queries that produce the answers to the following questions. The results should contain only the answers requested -- plus evidence to support them -- and no more.

DiveShop Queries

  1. How many tons was the sunken ship Delaware?
  2. What is customer Karen Ng's address?
  3. At what site might you find a Spotted Eagle Ray?
  4. Where is the site Palancar Reef?
  5. What sites might Lorraine Vega dive on her trip?
  6. Keith Lucas wants to see a shipwreck on his trip. Is he going to the right place?
  7. What equipment is Richard Denning getting?
  8. What is the cost of the equipment rental for Louis Jazdzewski.
ER diagram of the DiveShop Database

Assignment 2: Personal Database Preliminary Specifications

Due Thursday Sept. 14

The following information should be turned in for the preliminary proposal for your personal database project:

  1. A short description of the main idea and purpose for your database.
  2. A short general description of the data you will be using for the database, and what uses you might expect the database to have.

These will be preliminary specifications, so do not feel that you must follow everything that you describe here in the final database design. This description should be under one page in length.

Assignment 3: Full Personal Database Specifications and Conceptual Design

Due Tuesday Sept. 26

The following information should be turned in for the preliminary design of your personal database project. This builds on the previous assignment with some additions:

  1. A general description of the data you will be using for the database, and what uses you might expect the database to have (should be expanded from the previous assignment).
  2. A preliminary data dictionary for the files and data elements of the database. You should have at least 5 files with some logical connections between them. The data dictionary consists of all of the attributes that you have identified for each entity, along with indication of whether the attribute is a primary key (or part of a primary key), and what format the data will be (e.g.: text, decimal number, integer, etc.)
  3. Produce an entity-relationship diagram of the database OR a UML diagram.
These will be preliminary design specifications, so do not feel that you must follow everything that you describe here in the final database design.

 

Assignment 4: SQL

Due Thursday, October 12
  1. Read Chapter 9 in McFadden
  2. Turn in the answers for questions 1-10 from the questions and problems section

Assignment 5: Midterm Personal Database Reports

Due Tuesday, October 31
This is to be a formal report, printed on a good printer (no hand-written reports will be accepted). It should include the following information:
 Assignment #6
Diveshop Queries for Oracle
Due Tuesday, November 7

 

Connecting to Oracle:

Login to the Unix machine "irony"

From the lab:
  1. Login to your lab account as normal.
  2. From the Start menu, choose Programs/Internet/SSH Telnet/SSH – Irony.sims.
  3. SSH will prompt you for your password for Irony (use your lab password).
  4. At the unix command prompt enter "sqlplus" to start interacting with Oracle
  5. At the "Enter user-name:" prompt enter your ORACLE USER-ID.
  6. At the "Enter password:" prompt enter your ORACLE PASSWORD.
  7. At the "SQL>" prompt start entering SQL commands to answer the questions below.
  8. Enter "edit" at the SQL> prompt to go into your default editor (PICO is the default). When you save and exit from the editor, the edited query will be returned in the SQL buffer.
  9. Be sure to end each SQL command with a semicolon.
  10. To see what diveshop tables are available, submit the query "SELECT * FROM ALL_CATALOG WHERE OWNER = ‘RAY’;"
  11. To see the fields for a particular table issue the command "DESCRIBE tablename;" where "tablename" is replace with the name of the table you are interested in.
  12. When issuing queries use the "RAY.tablename" form wherever you need to name a table in your SQL commands.
  13. To change your oracle password to something else, use the SQLPlus command "PASSWORD" , you will be prompted for the old password, and a new password.
From a dialup or internet connection:
  1. Use telnet or SSH to connect to irony
  2. Proceed as in steps 4-13 above.
 

Diveshop Questions

  1. What are the names and addresses of the diveshop customers who are renting snorkels?
  2. What are the names of the wildlife that Mary Rioux might see on her trip. Are there any Shipwrecks there (give names)?
  3. What sunken ships might be candidates for treasure hunters whose destination is New Jersey?
  4. Who is paying the maximum amount for single type of rental equipment (use price * quantity to determine amount)?
  5. At how many sites might you see a "Nassau Grouper"?
  6. What are the names of customers who are paying in cash?
  7. Produce a list of all equipment being rented for a dive vacation that costs more than $30000, make the list in descending order of the rental price of the equipment.
  8. Who is renting teal colored equipment?
  9. Which locations have an avg temperature of more than 75 degrees farenheit and a travel cost of under $4000?
  10. Make up two queries of your own and run them turn in the queries and the results.
 

 

You can also create an SQL command file that contains multiple SQL and SQLPlus commands. The command files might look like the following: (PROMPT is an SQLPlus command that simply puts the rest of the line to the screen.)

PROMPT ****************************************************************

PROMPT DiveShop Query #1

PROMPT select ship_name, tonnage from shipwrck where ship_name like PROMPT 'Delaware';

select ship_name, tonnage from shipwrck where ship_name like 'Delaware';

PROMPT ****************************************************************

PROMPT DiveShop Query #2

PROMPT select d.name, d.street, d.city, d.state_prov, d.zip_postal_code PROMPT from divecust d where d.name like '%Ng%';

select d.name, d.street, d.city, d.state_prov, d.zip_postal_code from

divecust d where d.name like '%Ng%';

PROMPT ****************************************************************

PROMPT DiveShop Query #3

PROMPT select b.common_name, s.site_name from BIOLIFE b, SITES s, PROMPT BIOSITE x

PROMPT where b.species_no = x.species_no and x.site_no = s.site_no and PROMPT b.common_name

PROMPT like '%Spotted Eagle Ray%';

select b.common_name, s.site_name from BIOLIFE b, SITES s, BIOSITE x

where b.species_no = x.species_no and x.site_no = s.site_no and b.common_name

like '%Spotted Eagle Ray%';

 

A command file like the one above can be created in a text editor (be sure to save as a plain text file). To run a command file like this one, and capture the results for further editing or printing, issue the commands:

  1. SPOOL filename : where filename is the name of the file where you want the captured results to go.
  2. START filenane : where filename is the name of the command file to run. Note that SQLPlus expects that the filename will end with the extension ".sql".
  3. After the file has run, enter the "SPOOL STOP" command to close the capture file
  4. Edit and print the capture file to turn in.
  5. You can also use the SPOOL commands to capture everything during an interactive SQLPlus session.
 

 

Final Project Reports
As with the mid-term reports, this is to be a formal report, printed on a good printer (no hand-written reports will be accepted). It should include the following information: