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.
| No. | Due | Description |
|---|---|---|
| 1 | Fri 2/3 | GOAL: Relational Model |
| 2 | Fri 2/10 | GOAL: Classical Relational Algebra |
| 3A,B | Fri 2/17 | GOAL: Bag Relational Algebra GOAL: Extended Relational Algebra |
| 4 | Fri 3/2 | SQL Queries
(HK's sample solution log) |
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:
Or you can run psql interactively, and type in queries:[~]$ 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)
[~]$ 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.