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 4
Due Thursday Nov. 14
Personal Database Midterm Report
This is to be a formal report. It may be submitted via the online drop box or printed and turned in. Because this is a continuation and expanded version of Assignment 2, you should be sure to remedy any problems pointed out in that assignment, and also describe any design changes made since then. The instructions below include description of what is wanted depending on whether you will be using Access, Oracle or MySQL for your project. You only need to use one database system and provide the information appropriate for that system. The report should include the following information:
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. You should also describe features and changes that you plan to add to the current version.
Data Dictionary -- If using Access, use tools/analyze/documenter to generate DD for each table. Please use the Options button in the documenter to select only the "Names, Data Types, and Sizes" option for fields and "Names and fields" for indexes. If using Oracle or MySQL, you can use the "DESCRIBE" command for each table to generate the same kind of information. Or, you can have a manually maintained document with this information, just be sure that it includes what table the data element is associated with, its data type, size and whether it is a primary or foreign key.
Relationships Diagram -- For Access, Use tools/relationships and then file/print relationships to print relationships diagram. This will replace the ER Diagram from the preliminary report, or if you wish, you may also include an ER diagram). For Oracle or MySQL you will need to generate an up-to-date ER diagram (note that the tables in ER diagram SHOULD match those in the Data Dictionary.)
Sample queries and results -- Include at least 3 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. HINT -- for Access, you can also use the tools/analyzer/Documenter to generate reports on the queries, use the options button to include only "Names, Data Types, and Sizes" as with the data dictionary above.
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.) For Oracle or MySQL include the web pages you intend to use for data input and output. These need not be complete for this report.
Sample reports -- Create at least 3 reports showing information from tables and queries of your database. Print the reports and include them. For Access use the report generation tool. For Oracle or MySQL create use SQL scripts (or PHP Scripts) to generate the reports.
Assignment 3: Oracle Diveshop Queries
Due Thursday Oct. 26
Oracle Setup
Here are the steps that you need to go through to set up your Unix account for Oracle use, and to use Oracle for searching the DiveShop data.
Connecting to Oracle on Dream
Login to the Unix machine "dream" From the lab:
Login to your lab account as normal.
From the Start menu, choose Programs/Internet/SecureCRT(SSH & Telnet)/SSH - Dream
SSH will prompt you for your password for Dream (use your lab password).
First-time configuration
FIRST TIME ONLY: After you login to irony, 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:
Making queries with sqlplus
At the unix command prompt enter "sqlplus" to start interacting with Oracle:
dream% sqlplus
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 11 16:23:24 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name:
At the "Enter user-name:" prompt enter your ORACLE USER-ID. At the "Enter password:" prompt enter your ORACLE PASSWORD.
Enter user-name: yuri
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
At the "SQL>" prompt you can start entering SQL commands.
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. Alternatively, you can save your queries in a file on your desktop/laptop and copy and paste them back and forth between the file and the ssh prompt.
Be sure to end each SQL command with a semicolon.
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).
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.
When issuing queries use the "RAY.tablename" form wherever you need to name a table in your SQL commands.
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.
Formatting Output
When you run a query, you are likely to see rather messing output, e.g.:
SQL> select * from ray.sites;
<...many lines skipped...>
SITE_NO DESTINATION_NO SITE_NAME
---------- -------------- ----------------------------------------
SITE_HIGHLIGHT
------------------------------
SITE_NOTES
------------------------------------------------------------------
--------------DISTANCE_FROM_TOWN_M DISTANCE_FROM_TOWN_KM DEPTH_FT
DEPTH_M VISIBILITY_FT
-------------------- --------------------- ---------- ---------- --
-----------
VISIBILITY_M CURRENTS SKILL_LEVEL
------------ ------------------------------ --------------------
16 25.74 95 28.95
60
18.28 Mild Intermediate
41 rows selected.
This happens due to line wrapping and page wrapping. To avoid this, set pagesize and line size to some large numbers, e.g.
SQL> set pagesize 10000
SQL> set linesize 10000
Now we get somewhat better output:
SQL> select * from ray.sites;
<...many lines skipped...>
7006 7 R.P. Resor
Shipwrecks 32 51.49 130 39.62
80 24.38 Mild Advanced 7007 7 Delaware
Shipwrecks 1.5
2.41 70 21.33 50 15.24 None Intermediate
7008 7 Tolten
Shipwrecks 16 25.74 95 28.95
60 18.28 Mild Intermediate
41 rows selected.
It's still wrapped, but this is your terminal doing the wrapping now - when you spool it to a file (see below), each record will display on one line. However, there really is no good way to display super-long records, so what you should consider doing in cases like this is first of all select specific columns and giving them shorter display names:
SQL> select SITE_NAME Name, SITE_HIGHLIGHT Highlight,
DISTANCE_FROM_TOWN_M Distance
from ray.sites;
<...many lines skipped...>
Stolt Dagali Shipwrecks
18
R.P. Resor Shipwrecks
32
Delaware Shipwrecks
1.5
Tolten Shipwrecks
16
41 rows selected.
Finally, we can format individual columns (see SqlPlus Reference for details):
SQL> col Name format a20 truncate;
SQL> col Highlight format a30 truncate;
SQL> col Distance format 99999;
SQL> select SITE_NAME Name, SITE_HIGHLIGHT Highlight,
DISTANCE_FROM_TOWN_M Distance
from ray.sites;
NAME HIGHLIGHT DISTANCE
-------------------- ------------------------------ --------
<... lines skipped ...>
Pinta Shipwrecks 5
Mohawk Shipwrecks 7
Stolt Dagali Shipwrecks 18
R.P. Resor Shipwrecks 32
Delaware Shipwrecks 2
Tolten Shipwrecks 16
41 rows selected.
Diveshop Questions
What are the names and addresses of the diveshop customers who are renting snorkels?
What are the names of the wildlife that Mary Rioux might see on her trip. Are there any Shipwrecks there (give names)?
What sunken ships might be candidates for treasure hunters whose destination is New Jersey?
Who is paying the maximum amount for single type of rental equipment (use price * quantity to determine amount)?
At how many sites might you see a "Nassau Grouper"?
What are the names of customers who are paying in cash?
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.
Who is renting teal colored equipment? (see DIVESTOK.Description)
Which locations have an avg temperature of more than 75 degrees Farenheit and a travel cost of under $4000?
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, BIOSITE x
PROMPT where b.species_no = x.species_no and x.site_no = s.site_no and
PROMPT b.common_name 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:
SPOOL filename1: where filename1 is the name of the file where you want the captured results to go.
START filename2 : where filename2 is the name of the command file to run. Note that SQLPlus expects that the filename will end with the extension ".sql".
After the file has run, enter the "SPOOL STOP" command to close the capture file
E.g.:
SQL> spool /tmp/output.txt
SQL> start /home/yuri/oracle/test.sql;
...
STATE_PROV ZIP_POSTAL_CODE
------------------------------ --------------------
COUNTRY PHONE FIRST_CON
------------------------------ -------------------- ---------
26 rows selected.
SQL> spool stop
SQL>
Edit and print the capture file to turn in.
You can also use the SPOOL commands to capture everything during an interactive SQLPlus session.
Assignment 2
Due Tuesday Sept. 26
Personal Database Project Design
The following information should be turned in for the preliminary design of your personal database project.
A written description of the data you will be using for the database, and what uses you might expect the database to have (2-4 pages).
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.)
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.
The report should be printed and is due at the beginning of the class.
Assignment 1
Due Thursday Sept. 7
See Access Tutorial if you have any trouble with
the assignment. See also clarifications below.
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
(see below).
DiveShop Queries
- How many tons was the sunken ship Delaware?
- What is customer Karen Ng's address?
- At what destination and site(s) might you find a Spotted Eagle Ray?
- Where (what destination) 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.
ER diagram of the DiveShop Database
- The Database is available on the course web site
- Download your own copy
- For each of the questions create a query in Access.
- Create a document (Word, etc.) containing
- The query being answered
- 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).
- Due Thursday Sept. 7 - bring a printed copy and turn it in to Yuri before the beginning of the class
A couple of clarifications based on questions asked in office hours:
For the last question, we are asking you how much Louis Jazdzewski is PAYING for the stuff he is renting. (Keep in mind that Louis might be renting more than one of some of the items and we are interested in the total amount she is paying.) We are not asking you what was the "cost" of those items to DiveShop or what the sale price of those items would have been if they were sold rather than rented. Note that the last two numbers would naturally be higher than the rental price.
If you ever get more than a full page of results for your query, you are probably doing something wrong. Most likely you are searching two disconnected tables. All the tables that are a part of your query must be connected. See the ER diagram to see how the tables are linked.
You may get zero results in response to some your queries since some combinations just don't exist. This may mean that the answer the question really is "none/nobody/nowhere" etc. Or it may mean that you misspelt a name or set a wrong constraint.