-
Notifications
You must be signed in to change notification settings - Fork 6
Hw3Description
In this assignment you will solidify your knowledge in SQL, by writing SQL queries for a Ruby-on-Rails Library application. We will provide the queries in natural language, along with a sample Ruby controller that computes the answer by using NO SQL but rather plain ruby constructs, and you will come up with an SQL rewriting of that Ruby code. By the end of this homework, you should be able to convince yourselves that writing database queries in a declarative language is the best thing in using a DBMS!!!
You can use a quick install script to set up the assignment, by typing:
% hw3quickinstall.sh
at the prompt. In case that something goes wrong, or you would like to get some more insight as to what the script is doing under the covers, feel free to glance through it:
% cat `which hw3quickinstall.sh`
If the installation succeeds, you will see the following error messages:
ERROR: relation "authors" does not exist
STATEMENT: delete from authors;
psql:./library/hw3.sql:1: ERROR: relation "authors" does not exist
ERROR: relation "books" does not exist
STATEMENT: delete from books;
psql:./library/hw3.sql:2: ERROR: relation "books" does not exist
ERROR: relation "libraries" does not exist
STATEMENT: delete from libraries;
psql:./library/hw3.sql:3: ERROR: relation "libraries" does not exist
ERROR: relation "bindices" does not exist
STATEMENT: delete from bindices;
psql:./library/hw3.sql:4: ERROR: relation "bindices" does not exist
ERROR: relation "instocks" does not exist
STATEMENT: delete from instocks;
psql:./library/hw3.sql:5: ERROR: relation "instocks" does not exist
ERROR: table "authors" does not exist
STATEMENT: drop table authors;
psql:./library/hw3.sql:7: ERROR: table "authors" does not exist
ERROR: table "books" does not exist
STATEMENT: drop table books;
psql:./library/hw3.sql:8: ERROR: table "books" does not exist
ERROR: table "libraries" does not exist
STATEMENT: drop table libraries;
psql:./library/hw3.sql:9: ERROR: table "libraries" does not exist
ERROR: table "bindices" does not exist
STATEMENT: drop table bindices;
psql:./library/hw3.sql:10: ERROR: table "bindices" does not exist
ERROR: table "instocks" does not exist
STATEMENT: drop table instocks;
psql:./library/hw3.sql:11: ERROR: table "instocks" does not exist
You can safely ignore them. In case you wonder why does a successful installation produces errors, you can think of various reasons - some more philosophical (e.g. nothing in this life is perfect) than others :). Essentially, they are produced because of the first 10 SQL statements (DELETE and DROP) of the ~/Hw3/library/hw3.sql file, which is used to setup your database. The reason that these statements produce errors is that the tables they operate on have not been created yet. They have been intentionally included, in case you accidentally modified or deleted tuples from the database (after a successful installation of the homework), and you want to reset it. If you have to reset your database, on an already running instance of postgres, type:
% psql hw3 -f ~/Hw3/library/hw3.sql
In any case, take some time and read the hw3.sql file. In there, you will see some DDL (DROP and CREATE TABLE) and DML (DELETE and INSERT) SQL statements, which were not covered in class. These statements:
- delete any existing data from those tables, should they exist already
- delete the schemata of those tables, again if they already existed
- create the tables
- and insert the tuples you will need to test your queries
As you might have suspected by now, we provide you with a database containing records about books in various well-known US libraries. The file ~/Hw3/library/hw3.sql mentioned above, contains an "SQL-dump" of this database. Another incentive for you to read it, is to study the database's schema (the CREATE TABLE statements), so that you can familiarize yourselves with the relations and their attributes.
One thing to notice, is that the pgdata directory which contains your database is not located in the root of your home directory, but inside ~/Hw3/. If you ever need to stop or start postgres, you have to manually specify that directory, so that it can load the correct database. You do that by typing:
% pg_ctl -D ~/Hw3/pgdata start
or stop respectively.
As a note, the first time you install, you do not have to start postgres. The install script does this for you.
We have created for you a very simple Rails application, located in the ~/Hw3/library directory. Depending on the machine you have your DBMS running (probably the machine you are working on), you might need to change the host: rhombus.cs.berkeley.edu line of the ~/Hw3/library/config/database.yml to reflect the correct server name. After you do that, you should be ready to fire up the application, by typing:
% cd ~/Hw3/library
% server
You can browse through the five relations of your database, by using the default controllers - one per table (copy-paste to your browser the address that the server command outputted, and append the table name - e.g. authors). As a running example, we wil use http://rhombus.cs.berkeley.edu:16157 as the url of our application. Note that the output of WEBrick looks like this: http://0.0.0.0:16157. You have to substitute the default IP with the machine's name (here 0.0.0.0 with rhombus.cs.berkeley.edu).
You will need to provide the SQL equivalents for 8 queries. For each query we have created a controller named query?_controller.rb, with ? ranging from 1 to 8. You can find these controllers in the ~/Hw3/library/app/controllers directory. They contain ruby code that computes the answers for the corresponding query. The files ~/Hw3/library/app/views/query?/index.rhtml, again with ? ranging from 1 to 8, contain the dynamic html pages that call the corresponding controller (remember the MVC model that Ruby-on-Rails uses). You can access the output of these files through your browser. If for example you want to see the rendered output of the controller for query 1, visit: http://rhombus.cs.berkeley.edu:16157/query1/. Each such index.rhtml page displays one of these queries expressed in natural language, the Ruby code of the corresponding controller which computes the answer, as well as the answer itself. Note that you do not have to modify any of the controller or view files described so far.
Along with each query?_controller.rb file, there exists a query?sql_controller.rb. The same holds for the views of these skeleton controllers (~/Hw3/library/app/views/query?sql/index.rhtml). These are the files you must modify. Specifically, you should change the sample SQL statement that you'll find in each skeleton controller, as a parameter to the Author.find_by_sql() function, with your SQL query. Accordingly, you may have to change the five last lines of the corresponding view index.rhtml file, so that the fields of the result array correspond to the names of the table columns you used in your SELECT clause. As an example, the array @results computed by:
@results = Author.find_by_sql("select name, bplace from authors")
contains elements of type array (say result is one such element), that have the fields result.name and result.bplace.
You can evaluate the correctness of your SQL statements, by comparing the answers your getting (e.g. for query 1 through: http://rhombus.cs.berkeley.edu:16157/query1sql/) with the ones computed by the ruby version of the query (http://rhombus.cs.berkeley.edu:16157/query1/).
While you're trying to come up with the SQL statements for these 8 queries, you are highly encouraged to use the psql interactive shell (at the prompt type psql hw3), and try out your queries to immediately validate your answers.
You should create 8 text files, named query?.sql, with ? ranging from 1 to 8, that will contain the SQL query you came up with for each question. Please, be extra careful about the format of these files. If for example, in question 1, you subsituted the @results = Author.... line in ~/Hw3/library/app/controllers/query1sql_controller.rb with the following:
@results = Author.find_by_sql("select name, bplace from authors")
then query1.sql should contain only the query, without the quotes, followed by a semi-colon
select name, bplace from authors;
Place these files in a new directory, called hw3. Then from the parent directory, execute:
% submit hw3
That's all folks! If you run into any problems using ruby, consult your Pragmatic Bookshelf Ruby-on-Rails book, or numerous tutorials you can find online. Of course, you can pose questions to the newsgroup, but most of your RoR questions can be surely answered through the collective wisdom you can find on the web.
Please be nice to your colleagues. Since all of you will be using a handful of machines (and most of you just rhombus), when you log out, make sure you terminate your postgres and WEBrick servers. Please do not leave processes lying around, as the machines cannot handle the load. Instructions on how to stop postgres are provided in the "Library Database" section of the writeup. In order to stop the webserver, either press Ctr-C on its terminal window if it is running in the foreground, or find the process' pid and kill the process explicitly.
Good Luck!