HW1: Relational Algebra & SQL

CS4380: Database Systems
Jan. 25, 2004
Due Monday, Feb. 5th, 2004, Before Class (12pm)

HINTS

  1. Refer to readme.html on how to set up your database server on our experiment machines: initialize database server, start it, create database, and pose queries on your database.
  2. You need to read the documents about SQL of PostgreSQL in order to create tables, load data and ask queries.

DESCRIPTION

This assignment will use a database containing data about a university.

The schema of the database is provided below (keys are in bold, field types are omitted):
Before you start writing SQL, it is a good idea to take a look at the database and familiarize yourself with its contents. The table contents are all available in the directory as text files (e.g. student table is in 'student.txt'), whose columns are tab delimited.

Q1. First create the tables using the CREATE TABLE command. You must declare all the primary keys and foreign keys for each table. You will have to also use appropriate domains for each field. The file 'schema.txt' gives the column types for each table.

Q2. Import data into the tables using the postgres COPY command (as opposed to writing tedious INSERT INTO statements). For example here is how you would do this for dept table:
          \test=# \set fname '\''`pwd`/dept.txt'
          \test=# COPY dept
                         FROM :fname
                         DELIMITER '\t';
 This command first reads the present working directory `pwd` and the filename 'dept.txt' into the psql variable called fname (assuming that the dept.txt file is in the current directory; if not you can always change the current directory by issuing the \cd command; type \? for all other command options). Next, we copy the entire set of values from dept.txt into table dept in just one statement. NOTE: You must not hard code the path to the file in your script, but rather you must use the pwd approach outlined above.

Q3. Execute Queries using PSQL
The query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary
  1. Print the names of professors who work in departments that have fewer than 50 PhD students.
  2. Print the name(s) of student(s) with the lowest gpa.
  3. For each Computer Sciences section, print the cno, sectno, and the average gpa of the students enrolled in the section.
  4. Print the course names, course numbers and section numbers of all class sections with less than six students enrolled in them.
  5. Print the name(s) and sid(s) of the student(s) enrolled in the most classes.
  6. Print the names of departments that have one or more majors who are under 18 years old.
  7. Print the names and majors of students (of those who have majors) who are taking one of the College Geometry courses. (Hint: You'll need to use the "LIKE" predicate and the string matching character in your query; Also be careful about the " marks surrounding College Geometry courses.)
  8. For those departments that have no majors taking a College Geometry course, print the department name and the number of PhD students in the department.
  9. Print the names of students who are taking both a Computer Sciences course and a Mathematics course.
  10. Print the age difference between the oldest and youngest Computer Sciences major(s).
  11. For each department that has one or more majors with a GPA under 1.0, print the name of the department and the average GPA of its majors.
  12. Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.
Q4. Relational Algebra Expressions: Give relational algebra expressions for queries
    A,
    C (without the average gpa, since AVG is not available in relational algebra)
    G,
    H,
     I, and
    L
   

REQUIREMENTS

  1. NO PARTNERS. You should complete this assignment all by yourself.
  2. The assignment is due before 12:00PM on Monday, Feb. 5th, 2004.
  3. Please follow good formatting rules. Make your output neat and readable. Make all SQL keywords capital, all table table with first letter capital, tuple variable in Caps, and all others in lower case. For example, the following format is not acceptable: 
                            select s.CID,S.pid fROM             STUDENTS S where s.sId  in
                                           (select C.SID From classes C);

               But, this would be:

                         SELECT S.cid, S.pid
                            FROM Students S
                            WHERE S.sid IN (SELECT C.sid
                                                    FROM Classes C);

  4. When you have your tables and queries ready (for Q1,Q2 and Q3), you need to generate a SQL script to turn in. Q4 can be typed up separately, but you should submit all work electronically (see below).

DELIVERABLES

a) Submit the following:
        i) hw1.sql file containing all SQL statements to create tables, populate them and run your queries. Remember              the  sql file contains only SQL statements and not the output.
        ii) hw1.ps or hw1.pdf file (only PS or PDF accepted) with your answers to HW questions. For the SQL                         queries,  print out the SQL command and its output, and submit that. Another way to do this             is to set "\o FILENAME" in psql. Then all output of the queries will go to that file.

b) You must submit one zip or tar.gz file containing all required files at the online submission page https://cgi.cs.rpi.edu/submit/submit.html?course=csci4380