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. 

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

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 files and data elements of the database. You should have at least 3 files with some logical connections between them.
3. A simple entity-relationship diagram of the database.

Hints: Avoid using MEMO fields and DATE fields in your design for Access. Use character and numeric fields instead. The reason for this is that these field types often do not transfer well to the other database systems we will be using.

1. Read Chapter 9 in Kroenke
2. Complete the GROUP 1 questions
3. Turn in the SQL answers for questions 9.1-9.32

• Due October 21
• For Access-only projects
• Mid-term progress report
• For those moving personal DBs to ORACLE
• Final Access report

• Database description -- This should be 2-5 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. If you are planning to use Access for the final version of the database, indicate what you plan to add to the current version.
• Data Dictionary -- Use tools/analyze/documenter to generate DD for each table
• Relationships Diagram -- Use tools/add-ins/print relationships to print in Access 97, or, for Access 2000 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
• Sample queries and results -- Include at least 5 queries of your database that demostrate how it can be used. The report should include the SQL form of the query and the table result from running the query
• Sample forms -- 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)
• Sample reports -- Create at least 3 reports showing information from tables and queries of your database. Print the reports and include them.

Connecting to Oracle:

Login to the Unix machine "irony"

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.

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.

Saving delimited text files from Access:

1. Login to your lab account as normal.
2. Create a new folder to hold your exported data.
3. Start Access and load your database.
4. Highlight the table you want to export.
5. Select Save As/Export from the File menu.
6. In the "Save As" dialog box be sure that "to an external file or database" is selected, then click OK.
7. In the "Save Table … In" file browser box, open the new folder and select "Text files" in the "Save as type" pop-up menu, enter a name for the file (or take the default name), then click Export.
8. In the "Export Text Wizard", select the "Delimited" format, then click Next.
9. On the next page choose "Comma" as the delimiter and make sure the Text Qualifier is a double quote mark. Click Next to continue.
10. On the next page change the file name if desired, then click Finish
11. Repeat these steps for each file you wish to export.

 

Creating Tables in Oracle:

I suggest creating an SQL command file that contains multiple CREATE TABLE SQL commands, one for each table you want to create. The command files might look like the following examples from the Diveshop database:

CREATE TABLE BIOLIFE2 (

Species_No Number (10),

Category VARCHAR2(20),

Common_Name VARCHAR2(30),

Species_Name VARCHAR2(60),

Length_cm NUMBER(8,2),

Length_in NUMBER(8,2),

Notes VARCHAR2(1000),

Graphic2 VARCHAR2(20));

 

CREATE TABLE BIOSITE2 (

Species_No NUMBER(10),

Site_No NUMBER(10));

 

CREATE TABLE DEST2 (

Destination_No NUMBER(10),

Destination_Name VARCHAR2(60),

Avg_Temp_F NUMBER(8,2),

Avg_Temp_C NUMBER(8,2),

Spring_Temp_F NUMBER(8,2),

Spring_Temp_C NUMBER(8,2),

Summer_Temp_F NUMBER(8,2),

Summer_Temp_C NUMBER(8,2),

Fall_Temp_F NUMBER(8,2),

Fall_Temp_C NUMBER(8,2),

Winter_Temp_F NUMBER(8,2),

Winter_Temp_C NUMBER(8,2),

Accomodations VARCHAR2(40),

Night_Life VARCHAR2(40),

Body_of_Water VARCHAR2(40),

Travel_Cost NUMBER(8,2));

CREATE TABLE DIVECUST2 (

Customer_No NUMBER(10),

Name VARCHAR2(100),

Street VARCHAR2(100),

City VARCHAR2(50),

State_Prov VARCHAR2(30),

Zip_Postal_Code VARCHAR2(20),

Country VARCHAR2(30),

Phone VARCHAR2(20),

First_Contact DATE);

Etc…

 

Be sure each SQL create table statement is terminated by a semicolon. See the earlier handouts for the datatypes permitted in Oracle databases.

You should probably also create a command file to remove the database, it would look like the following:

DROP TABLE BIOLIFE2 ;

DROP TABLE BIOSITE2 ;

DROP TABLE DEST2 ;

DROP TABLE DIVECUST2 ;

Etc…

This can be used to completely remove the tables and their data when there are problems with loading

Login to the Unix machine "irony" :

12. Login to your lab account as normal.
13. From the Start menu, choose Programs/Internet/SSH – Telnet/SSH - Irony.
14. In the "password" dialog box, enter your lab password.
15. At the unix command prompt enter "sqlplus" to start interacting with Oracle
16. At the "Enter user-name:" prompt enter your email name (usually the same as your lab login).
17. At the "Enter password:" prompt enter your last name (if that doesn’t work try your first name).
18. At the "SQL>" prompt enter "@createfilename", where createfilename is the name you saved the command file under – alternatively you could type in the create commands one by one.

 

Edit the exported Access file for use in SQL*Loader

 

19. Edit each of the exported files from access to include the following lines (this example is for DIVECUST):

LOAD DATA

INFILE *

INTO TABLE DIVECUST2

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(Customer_No , Name, Street , City , State_Prov , Zip_Postal_Code , Country , Phone , First_Contact DATE(8) "MM/DD/YY")

BEGINDATA

1480.00,"Louis Jazdzewski","2501 O'Connor","New Orleans", "LA", "60332", "U.S.A.","(902) 555-8888",1/29/91

1481.00,"Barbara Wright","6344 W. Freeway","San Francisco","CA","95031","U.S.A.","(415) 555-4321",2/2/91

1909.00,"Stephen Bredenburg","559 N.E. 167 Place","Indianapolis","IN","46241","U.S.A.","(317) 555-3644",1/5/91

1913.00,"Phillip Davoust","123 First Street","Berkeley","CA","94704","U.S.A.","(415) 555-9184",3/9/91

 

20. Note that the field names used in the CREATE TABLE command are listed in the order that the fields appear in the data.
21. The First_Contact field in the data is declared to be a DATE and the input format for the date is given – this must be done for any DATE fields in your data.
22. Note also that each row from the Access database will appear on a single line in the exported file, it is not shown that way here because the word processor automatically split the lines.
23. Once you are satisfied with the export file run the sqlload command from the UNIX prompt (not from within sqlplus) as follows:

 

sqlload userid=youroracleid/youroraclepass control=exportfilename log=somename.log

 

24. Once the command has completed, examine the log file to see if it was successful.
25. If there were errors in the data or control file, you will need to correct them and then drop and re-create the tables (if partial data was loaded) and run the sqlload command again.
26. Repeat these steps for each table of exported data.

• Due December 2 (last class meeting)
• For Access-only projects
• Final progress report will include the same information as the mid-term report(above), plus additional data and functionality.
• For those moving personal DBs to ORACLE
• Final ORACLE report with the information described below.

• Database description -- This should be 2-5 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 and midterm report, but should be more comprehensive and reflect the ongoing design. If you are using Access for the final version of the database, indicate what you haved added to the database beyond the mid-term report. If you are using ORACLE, describe the changes needed in your Access database to get it running on ORACLE.
• 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 TAB;" "SELECT * FROM COL;" with appropriate formatting commands to produce the data dictionary information.
• Relationships Diagram
  • For Access -- Use tools/add-ins/print relationships to print in Access 97, or, for Access 2000 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 -- 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 database.
• Sample queries and results Both Access and ORACLE -- Include at least 8 queries of your database that demonstrate how it can be used. The report should include the SQL form of the query and the table result from running the query. In ORACLE use the SQLPlus formatting commands to produce readable output.
• 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 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.
• 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.