Assignment 0: Getting Started with PostgreSQL
Carnegie Mellon Computer Science CS415 S06
January 18, 2006
Due at 3PM on Wednesday, Jan 25, 2006
DESCRIPTION
This is a "Pass/Fail" assignment. You
will not receive any points on it, but you have to pass it in order to
stay in the course. The purpose of this assignment is to familiarize
yourselves with PostgreSQL and try some simple SQL. In this assignment,
you will have to do the following:
- Design a tiny database.
- Construct example SQL queries which produce some meaningful results
on your database.
An account has been created for you on a machine
running a PostgreSQL server.
Instructions for logging into this account will be sent to you in a
separate email. Once you are logged in, issue the following command at
the shell prompt:
$ psql -U postgres <andrew-id>
This will connect you to the PostgreSQL server. Once you are connected,
you can start typing your SQL!
You have to design a database for a course registration system. The
database must contain information about all courses being offered, the
relevant departments, the students registered for each course (their
name, id, department, etc.) A sample database schema is provided
below.
The information we might want to store in this database is:
| For courses: |
(Course number, Course title, Course description, Department) |
| For students: |
(Student ID, Name, Department) |
| For registration: |
(Student registered in a course, and the relevant course number) |
We can design the following three tables:
| Courses |
(C_ID int, C_Title Char (40), C_Desc Char (100), C_Dept Char (20)) |
| Students |
(S_ID int, S_Name Char (30), S_Dept Char (20)) |
| Registrations |
(RS_ID int, RC_ID int) |
The database design is up to you. The principle here is keeping things simple.
Feel free to come up with an alternative design, possibly storing more
fields (e.g., student age). The example design shown above is a rough
idea of what we expect from you (you can use this design in
your homework.)
Once you have designed the database, create the tables and populate them
with data (10 records per table would be sufficient).
Next, think of some interesting queries for the database that you
designed. Example queries might be:
- Find all the courses for which a student has registered
- Find number of students who have registered in a course
which is not offered by their own department
You might want to add more columns to your database tables to construct
more interesting queries (e.g., you can track the gender of each
student, and then query the ratio of males to females registered for various
courses.)
PASSING CRITERION
- You should define at least 3 tables.
- Each table should contain at least 10 records.
- You should write at least 2 queries (no more than 5
queries). Each query should return some result.
REQUIREMENTS
- NO PARTNERS. You should complete this assignment
all by yourself.
- The assignment is due at 3PM
on Wednesday, Jan 25, 2006.
- We emphasize that late submissions receive a "Fail" in this assignment,
which also means you cannot stay in this course. You cannot use slip_days
in this assignment.
- When you have your tables and queries ready, you need to generate a script
to turnin. You should do it via "
script" command.
a) When you are ready, type "script hw0.script" at a shell
prompt;
b) Type "psql -U postgres <andrew-id>" to connect to the PostgreSQL database.
c) For each table you define, run the query "select * from
<tablename>;" so we
can see the content of the tables.
d) For each query you wrote, run that query;
e) Quit psql via \q or control-d;
f) Type "exit" to end the script program. It
should tell you "Script done, file is hw0.script".
You can use "cat hw0.script" to see the script.
- Briefly describe what your tables and what your queries mean in another file called "readme".
DELIVERABLES
Submit printouts of "hw0.script" and
"readme" in class on January 25. DO NOT FORGET TO WRITE
YOUR ANDREW ID ON THE FIRST PAGE OF THE PRINTOUT.
HINTS
- Refer to this document
on how to set up your database server: initialize
database server, start it, create database, and pose queries on your database.
- You need to read the documents about SQL of PostgreSQL in order to create
tables, load data and ask queries.
- You might want to use pgaccess, a GUI tool for PostgreSQL to help you compose your SQL.
Send all your questions to the course newsgroup, academic.cs.15-415. All questions
sent to TAs will be posted to the newsgroup.