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 Thurs Sept. 13
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 and Conceptual Design

Due THURSDAY Oct. 4 (date change)

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

  1. A description of the main idea and purpose for your database.
  2. A general description of the data you will be using for the database, and what uses you might expect the database to have.
For the conceptual design of the database you should include:
  1. 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.)
  2. 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 3: Research an Online Database -- Exploration and reporting

Due Tuesday Oct. 2

(Due on Oct 2. Please submit by emailing to Rosa.)

Goal:
To learn about different applications of databases, particularly ones that can help provide information useful for regional economic forecasting. Examples are sites dealing with jobs, real estate, traffic congestion, etc. We are interested in which sites cover particular regions or types of jobs/real estate particularly well... Hopefully you will get some ideas for the course database project in the process and even find some sources of data.

Step 1:
Start with a tutorial provided by Berkeley library for some background and pointers on searching and accessing databases on the web.

Step 2:
Download a list of suggested databases and choose one to do some research. If you don't find anything of interest or have problems with the ones you started with, try following some of the directory suggested by and see if you can find one. Specifically, look in categories of jobs, real estate, travel... that also allows you to limit your searches by region.

Step 3:
After selecting a database, obtain the following information about it:

I Basic Information  
a. Name of Database (Website)  

b. Web Location - URL

  
c. Summary description of content  
d. Responsible organization  
e. Contact Info: person, email, phone number, etc.  
 
II Content  
f. Do an image capture of the main search screen(s) - enough to show how searches can be done  
g. Do a couple of queries. If possible summarize what kind of information you tried to look for and what was retrieved. Attach some image captures of result pages.  
 
III Technical Information  
Try to figure out as much of the following as possible, perhaps by contacting those who are responsbile for the database:  
h. Size of the database  
i. Number of records, ex: 50,000 job descriptions  
j. Type of backend server (Oracle, Access,... etc.  
k. Technologies used to implement the front end (Cold fusion, PHP, ASP, Java Script...)  

 

 

 

 Assignment #4
Diveshop Queries for Oracle
Due Thursday October 25th

 

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';" (Note the use of SINGLE quotes)
  11. To see the fields for a particular table issue the command "DESCRIBE tablename;" where "tablename" is replaced 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 SSH (or SecureCRT) to connect to irony.sims.berkeley.edu. You can obtain a copy of SecureCRT and the licensing information on the Lab machines at S:\windows\securecrt.
  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.
 

 

Assignment 5: Midterm Personal Database Reports

Due Tuesday, Nov. 13
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:

 

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: