UC Berkeley School of Information

IS 257: Database Management

Announcements

Description

Mission statement

Concepts

Outline & Schedule

Instructor / TA

Enrolling

Grading

Assignments

Laboratory

Textbooks

Useful links

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 Thursday Sept. 11

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

  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.

ER diagram of the DiveShop Database

  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
  5. The query being answered
  6. 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).
  7. Due Thursday Sept. 11 - bring a printed copy and turn it in to Yuri before the beginning of the class

A couple of clarifications:

  1. 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 he 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.

  2. 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.

  3. 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.

Assignment 2

Due Tuesday Sept. 25

Personal Database Project Design

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

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 3: MySQL Diveshop Queries

Due Tuesday Nov. 4th

MySQL Setup

Here are the steps that you need to go through to set up your Unix account for MySQL use, and to use MySQL for searching the DiveShop data.

Connecting to MySQL on Harbinger

Making queries with mysql

At the unix command prompt enter "mysql -p yourusername" to start interacting with MySQL: 

  harbinger:~ --> mysql -p ray
  Enter password:

  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A

  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 788010
  Server version: 5.0.45 Source distribution

  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

  mysql>

At the "mysql>" prompt you can start entering SQL commands.

Enter "edit" at the mysql> 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 mysql 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 command: 

show tables;

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.

Capturing Output

When you run a query, you are likely to see rather messing output, e.g.: 

mysql> select * from SITES;

+---------+----------------+---------------------+----------------+------------+----------------------+-----------------------+----------+---------+---------------+--------------+---------+--------------+
| 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 | Current | Skill_Level  |
+---------+----------------+---------------------+----------------+------------+----------------------+-----------------------+----------+---------+---------------+--------------+---------+--------------+
|    1001 |              1 | Palancar Reef       | Reef           | NULL       |                   10 |              16.09344 |      100 |   30.48 |           150 |        45.72 | Strong  | Intermediate |
|    1002 |              1 | Santa Rosa Reef     | Reef           | NULL       |                    8 |             12.874752 |       80 |  24.384 |           150 |        45.72 | Strong  | Intermediate |
|    1003 |              1 | Chancanab Reef      | Reef           | NULL       |                    4 |              6.437376 |       60 |  18.288 |       
 ...many lines skipped...

About the only way to capture the results and make them pretty is to use the "tee" command to put the output into an external file and then use an editor to modify it. 

 mysql> \T MYSQLOUTPUTFILENAME
 Logging to file 'MYSQLOUTPUT'
 -> select * from SITES;

 ... lots of lines like above ...

 mysql> \t
 Outfile disabled.
 mysql> \q    

 Bye
 harbinger:~ --> more MYSQLOUTPUT

 mysql> select * from SITES;
 +---------+----------------+---------------------+----------------+------------
 +----------------------+-----------------------+----------+---------+----------
 -----+--------------+--------
 ... etc. ...

This MYSQLOUTPUT file can be edited in any editor you prefer (it is just a text file)

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? (see DIVESTOK.Description)

  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 mysql commands. The command file contents might look like the following: 

\T myoutputfile

select Ship_Name, Tonnage from 
       SHIPWRCK where Ship_Name like 'Delaware'
\p
\g

...

\t

The "\p" command prints the current command in full (so it can be captured to the "myoutputfile" file along with the query results). The "\g" command runs the query (like a semicolon).

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, which already includes the commands to capture the results for further editing or printing, issue the commands: 

mysql> \. commandfilename

Where "commandfilename" is the name you saved the command file as.

Turn in the edited version of your commands to answer the questions above from the Diveshop database, including the SQL commands used to obtain your results.

Assignment 4

Due Thursday Nov. 13

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, MySQL or some other database system 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: