SIMS 257 Assignments

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 Drop Box

1. How many tons was the sunken ship Delaware?
2. What is customer Karen Ng's address?
3. At what destination and site(s) might you find a Spotted Eagle Ray?
4. Where (what destination) 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.

1. The Database is available on the course web site
2. Download your own copy
3. For each of the questions create a query in Access.
4. Create a document (Word, etc.) containing
1. The query being answered
2. The results of your query cut and pasted from Access (click on the upper left corner of the results (table view) and then select copy from the Edit menu, then select Paste from the Edit menu in Word or other word processor).
5. Due Tuesday Feb. 3

The following information should be turned in for the preliminary design of your personal database project.

1. A general description of the data you will be using for the database, and what uses you might expect the database to have.
2. A preliminary data dictionary for the entities and attributes and format of the data elements of the database. You should have at least 5 entities 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.

Connecting to Oracle:

Login to the Unix machine "dream"

From the lab:

1. Login to your lab account as normal.
2. From the Start menu, choose Programs/Internet/SecureCRT(SSH & Telnet)/SSH - Dream.sims.
3. SSH will prompt you for your password for Dream (use your lab password).

  NOTE: The following should have been done automatically for you, but if you cannot connect to oracle you may need to do the following:

FIRST TIME ONLY: After you login to dream, use a unix text editor (pico is probably best for unix novices) to edit the file .cshrc. For example on the command line you would type:

pico .cshrc

and you are presented with the .cshrc file to edit. Go to the bottom of the file and add the lines:

if (-f /usr/local/skel/local.oracle) then
source /usr/local/skel/local.oracle
endif

and save the file again. Then logoff and login again, and continue as below.

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 you can start entering SQL commands
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 around strings in SQL commands).
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.

 

 
 
 

---

Assignment 3: ORACLE Diveshop Questions

Due Thursday, March 18 - Extended to Tuesday, March 30 Proceed as in steps above.

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.

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.

• Printed Reports Due MAY 11 (last day of classes)

• Database description -- This should be 4-6 pages describing the database, the organizational (or personal) context for which it is being designed, and the uses for it. The description can incorporate the previous design description from the preliminary design report, but should be more comprehensive and reflect the ongoing design. In particular, be sure to note any changes since the original design report. If you are using Access for the final version of the database, indicate what features and application support that you haved added to the database beyond the original design report. If you are using ORACLE or other DBMS, describe the changes needed in your Access database to get it running on ORACLE or other DBMS.
• Data Dictionary
• For Access -- Use tools/analyze/documenter to generate DD for each table.
• For ORACLE -- Use queries of the system metadata to show the tables, columns and datatypes. Use the queries "SELECT * FROM CAT;" and "DESCRIBE tablename;" with appropriate formatting commands to produce the data dictionary information.
• For OTHER DBMS -- Use queries (or client commands) of the system metadata to show the tables, columns and datatypes. If such queries are not possible, you should write a document describing the tables, columns and their datatypes for the database.
• Relationships Diagram
• For Access -- Use tools/relationships, then file/print relationships then file/print. This will replace the ER Diagram from the preliminary report, or if you wish, you may also include an ER diagram
• For ORACLE or Other DBMS -- If the database design has not changed between your Access and ORACLE versions, you can use the same relationship diagram or ER diagram as before. Otherwise create an ER diagram for the ORACLE (or Other) database.
• Sample queries and results Both Access and ORACLE -- Include at least 8 queries of your database that demonstrate how it can be used. For Access and Web databases please provide copies of the SQL commands used to generate the query results, but you can provide copies of the result pages, or forms for the output. For ORACLE, the report should include the SQL form of the query and the table resulting from running the query. In ORACLE use the SQLPlus formatting commands to produce readable output. (The SQL-plus reference manual is under Oracle 8i Server on the documentation web site). If using Other DBMS, and/or building a web-based interface to the system, please print out the result pages for this purpose.
• Sample forms
• For Access -- Create forms for data entry for each of the tables in your database and print the form (use file/print and pick "print range" in the printing dialog box as "selected records" to print a single page for each of the forms). Create an Access application that integrates the forms and reports with an interface.
• For Web databases include the forms used for data entry, queries etc. for interacting with your database. If you are building a web-based application with the ability to insert new data from web forms, please print out those forms along with the PHP, ColdFusion, and/or HTML associated with them.
• For ORACLE, show SQL for inserting and updating the database.
• Sample reports: Both Access and ORACLE -- Create at least 5 reports showing information from tables and queries of your database. Print the reports and include them. For ORACLE, use the SQLPlus formatting commands and queries to create your reports, include the SQLPlus commands used for each report. If you are using Other database systems, please attempt to produce something similar to the Access or ORACLE reports.
• Access Applications: Access only -- Create forms that provide an interface to your reports and other forms. See the Access example database NorthWind.mdb. Print the additional forms used and include them.
• Web-Based Applications: For Web-based applications, be sure to print out examples of any pages that are used to provide an interface to the database. If there are underlying cgi scripts these should be printed out as well.
• In-Class Reports: Everyone will be required to present your database project in class during the last two weeks of classes. This should include a demonstration of the main functionality of the database, and description of the purpose and any design issues of the database. There will be a sign-up sheet for the presentations.