UNIVERSITY OF ROCHESTER

DEPARTMENT OF COMPUTER SCIENCE

CSC 296 - Database Systems

Homework

Revised:  2012-03-04

 

Some of the homework assignments will be online, using the GOAL facility (Gradiance Online Accelerated Learning). You will need the class token I emailed to the course list in order to sign up for the GOAL class. Once you have signed up for the GOAL class, you will have access to the homework assignments.

The online homework assignments will be marked in the schedule below as "GOAL". The rest will be more traditional homework assignments, where you actually have to hand stuff in.

Note that you may submit each GOAL homework assignment multiple times, prior to the deadline, but with a minimum of 15 minutes between submissions. The grade for a homework assignment is based on the score of your final submission.

Unless otherwise specified, all homework assignments are due by 23:59 on the due date.


Homework Schedule

No. Due Description
1 Fri 2/3GOAL: Relational Model
2 Fri 2/10GOAL: Classical Relational Algebra
3A,B Fri 2/17GOAL: Bag Relational Algebra
GOAL: Extended Relational Algebra
4 Fri 3/2SQL Queries
(HK's sample solution log)


Homework Details

4. SQL queries

The CS Department gave you a database of your very own, that lives inside the PostgreSQL DBMS on the department's undergrad server, betaweb.csug.rochester.edu, and a username and password to access the undergrad server as well as a username and password to access the DBMS. (Hopefully your username is the same for both cases, but the password will be different.) I have a database on this system also, namely koomen_db, in which I have created two tables, features and cities. You're welcome to see how I created these, by examining the creation script. I have given you read access to these two tables. Your task is to find answers to the questions below. Obviously, I'm not interested in the answers themselves, but in the queries that obtain those answers.

Steps you will want to take:

  1. Log in to betaweb using a Secure Shell. (SSH is built into Mac OS and Linux; Windows users can grab PuTTY, OpenSHH, or alternatives.)
     
  2. Use the PostgreSQL shell psql (interactive, keyboard-oriented user interface to a PostgreSQL server), like this:
    [~]$ psql koomen_db -e -U yourdbusername -L my_psql_log.txt -c "select count(*) from features;"
    Password:
    select count(*) from features;
     count
    --------
     198974
    (1 row)
    
    Or you can run psql interactively, and type in queries:
    [~]$ psql koomen_db -e -U yourdbusername -L my_psql_log.txt
    Password:
    Welcome to psql 8.1.23, the PostgreSQL interactive terminal.
    koomen_db=> select * from cities limit 3;
     city_id |  feature_class  | feature_name |  pop_range   | pop_2000 | fips55 |        county_name         | fips  | state_alpha | state_fips | display 
    ---------+-----------------+--------------+--------------+----------+--------+----------------------------+-------+-------------+------------+---------
           1 | Populated Place | Attu         | Undetermined |   -99999 | 04540  | Aleutians West Census Area | 02016 | AK          | 02         | t
           2 | Populated Place | Point Hope   | 0 - 9,999    |      757 | 61630  | North Slope Borough        | 02185 | AK          | 02         | f
           3 | Populated Place | Point Lay    | Undetermined |   -99999 | 61700  | North Slope Borough        | 02185 | AK          | 02         | t
    (3 rows)
    
    Or you can put the SQL query in a file myqueries.sql, like this:
    select 0 as problem, count(*) as nr_of_features from features;
    and then execute the query like this:
    [~]$ psql koomen_db -e -U yourdbusername -L my_psql_log.txt < myqueries.sql
    Password:
    select 0 as problem, count(*) as nr_of_features from features;
     problem | nr_of_features
    ---------+----------------
           0 |         198974
    (1 row)
    
    You will probably need to consult the psql documentation for more details on various options and interaction styles.
    General hint: everything you type into psql is assumed to be SQL and is passed on to the PostgreSQL server, unless it starts with a backslash ('\') in which case it is assumed to be a psql command. Try '\?' or '\d cities' (without the quotes, of course!)
     
  3. Design your SQL queries to answer the following questions:
    1. How many people live in Rochester, NY?
    2. Where is Rochester, NY located? (county and geo coordinates)
    3. How many places does the DB contain that are in Texas?
    4. How many places are there with populations of 1,000,000 or more?
    5. What is the state with the highest population, and what is that population?
    6. How many people live at an altitude of 10,000 feet or higher?
    7. What is the distance in miles between Rochester, NH and Rochester, NY?
    8. (!) What is the greatest distance between any two large cities (with population >= 100,000) in the DB, and which two cities are they?
    9. (!!) How many people live within a radius of 100 miles from Rochester, NY?
    10. (!!) With a single query determine, for each of the following P's, how many places exist with a population of P, and what is the total population in all those places:
          P < 10
          10 <= P < 100
          100 <= P < 1,000
          1,000 <= P < 10,000
          10,000 <= P < 100,000
      etc.
       
    Hint: for problems 8 and 9, think "nested queries"!

    Note: to compute the distance between two places, you may assume that the earth is flat and use the Pythagorean equation, or you may assume that the earth is a perfect sphere and use the Spherical Law of Cosines (but watch out for radians vs. degrees!) I'm NOT looking for driving distance! You may find this site helpful.

    Note also: design your queries so that the column headers in the output are meaningful and human-friendly!
     
  4. Run all your queries with psql, echoing the input (-e) and capturing the output in a log file (-L)
     
  5. Submit your homework by emailing your log file to me and the TA.