comp9311 SQL

Project 1 SQL

COMP9311 22T1

1. Aims

This project aims to give you practice in

•  Reading and understanding a moderately large relational schema (MyMyUNSW).

• Implementing SQL queries and views to satisfy requests for information.

• The goal is to build some useful data access operations on the MyMyUNSW database. The data may contain some data inconsistencies; however, they won’t affect your answers to the project.

2. How to do this project:

• Read this specification carefully and completely

• Familiarize yourself with the database schema (description, SQL schema, summary)

• Make a private directory for this project, and put a copy of the proj1.sql template there

• You must use the create statements in proj1.sql when defining your solutions

• Look at the expected outputs in the expected_qX tables loaded as part of the check.sql file

• Solve each of the problems below, and put your completed solutions into proj1.sql

• Check that your solution is correct by verifying against the example outputs and by using the check_qX() functions

• Test that your proj1.sql file will load without error into a database containing just the original

MyMyUNSW data

• Double-check that your proj1.sql file loads in a single pass into a database containing just the original MyMyUNSW data

• Submit the project via moodle

• PLpgSQL functions are not allowed to use in this project

• For each question, you must output result within 120 seconds on Grieg server. 

3. Introduction

All Universities require a significant information infrastructure in order to manage their affairs. This typically involves a large commercial DBMS installation. UNSW's student information system sits behind the MyUNSW web site. MyUNSW provides an interface to a PeopleSoft enterprise management system with an underlying Oracle database. This back-end system (Peoplesoft/Oracle) is often called NSS.

UNSW has spent a considerable amount of money ($80M+) on the MyUNSW/NSS system, and it handles much of the educational administration plausibly well. Most people gripe about the quality of

the MyUNSW interface, but the system does allow you to carry out most basic enrolment tasks online.

Despite its successes, MyUNSW/NSS still has several deficiencies, including:

• no waiting lists for course or class enrolment

• no representation for degree program structures

• poor integration with the UNSW Online Handbook 

The first point is inconvenient, since it means that enrolment into a full course or class becomes a sequence of trial-and-error attempts, hoping that somebody has dropped out just before you attempt to enroll and that no-one else has grabbed the available spot.

The second point prevents MyUNSW/NSS from being used for three important operations that would be extremely helpful to students in managing their enrolment:

• finding out how far they have progressed through their degree program, and what remains to be completed .

• checking what are their enrolment options for next semester (e.g., get a list of available courses) .

• determining when they have completed all the requirements of their degree program and are eligible to graduate .

NSS contains data about student, courses, classes, pre-requisites, quotas, etc. but does not contain any representation of UNSW's degree program structures. Without such information in the NSS database, it is not possible to do any of the above three. So, in 2007 the COMP9311 class devised a data model that could represent program requirements and rules for UNSW degrees. This was built on top of an existing schema that represented all the core NSS data (students, staff, courses, classes, etc.). The enhanced data model was named the MyMyUNSW schema.

The MyMyUNSW database includes information that encompasses the functionality of NSS, the UNSW Online Handbook, and the CATS (room allocation) database. The MyMyUNSW data model, schema and database are described in a separate document.

4. Setting Up

To install the MyMyUNSW database under your Grieg server, simply run the following two commands:

$ createdb proj1

$ psql proj1 -f /home/cs9311/web/22T1/proj/proj1/mymyunsw.dump

If you've already set up PLpgSQL in your template1 database, you will get one error message as the database starts to load:

     psql:mymyunsw.dump:NN: ERROR: language "plpgsql" already exist. 

You can ignore the above error message, but all other occurrences of ERROR during the load needs to be investigated.

If everything proceeds correctly, the load output should look something like:

 SET

SET

SET

SET

SET

psql:mymyunsw.dump:NN: ERROR: language "plpgsql" already exists

... if PLpgSQL is not already defined,

... the above ERROR will be replaced by CREATE LANGUAGE

SET

SET

SET

CREATE TABLE

CREATE TABLE

... a whole bunch of these

CREATE TABLE

ALTER TABLE

ALTER TABLE

... a whole bunch of these

ALTER TABLE

Apart from possible messages relating to plpgsql, you should get no error messages.

 

The database loading should take less than 60 seconds on Grieg, assuming that Grieg is not under heavy load. (If you leave your project until the last minute, loading the database on Grieg will be considerably slower, thus delaying your work even more. The solution: at least load the database Right Now, even if you don't start using it for a while.) (Note that the mymyunsw.dump file is 50MB in size; copying it under your home directory or your /srvr directory is not a good idea).

 

If you have other large databases under your PostgreSQL server on Grieg or if you have large files under your /srvr/YOU/ directory, it is possible that you will exhaust your Grieg disk quota. Regardless, it is certain that you will not be able to store two copies of the MyMyUNSW database under your Grieg server. The solution: remove any existing databases before loading your MyMyUNSW database.

 

Summary on Getting Started

To set up your database for this project, run the following commands in the order supplied: 

 $ createdb proj1

$ psql proj1 -f /home/cs9311/web/22T1/proj/proj1/mymyunsw.dump

$ psql proj1

... run some checks to make sure the database is ok

$ mkdir Project1Directory

... make a working directory for Project 1

$ cp /home/cs9311/web/22T1/proj/proj1/proj1.sql Project1Directory

The only error messages produced by these commands should be those noted above. If you omit any of the steps, then things will not work as planned.