Database System Engineering Assignment 3 - School of ...

newshumansvilleΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 4 μήνες)

261 εμφανίσεις

1

Assignment 3 SW304-11/ass3(pm)

School of Engineering and Computer Science
SWEN 304 Database System Engineering
Assignment 3

Due date: Friday 09 September

at 23:59


The objective of this assignment is to test your understanding of stored procedures, user
defined functions, and triggers, and your ability to test them in the environment of
PostgreSQL. It is worth 4.0% of your final grade. The assignment is marked out of 100.

In this assignment you are asked to:
1. Define referential integrity constraints using triggers, and
2. Define a user function that performs complex database updates.

References
For this assignment, you will need to refer to lecture notes, and you will also need to
consult PostgreSQL Manual, which is available online (there is a link to it from the
COMP302/SWEN304 home page). Please do not print out all of the PostgreSQL Manual!

Note: There is a short instruction on using PostgreSQL given at the end of this handout.

Question 1. Triggers [40 marks]
Soon after you have got a position as a database specialist, your manager approached you
and asked you to implement the following normalized database schema:
S = {
Student ({StudentId, Stud_Name, Major, TotPts}, {StudentId})
Major ({MajorId, Maj_Name, Department}, {MajorId })
} I = {
Student [MajorId ] 
((d, r), (m, r))
Major [MajorId ]
}
This database should contain data about students and their majors. Each student has only
one major. If a major has been taken by a student that major should be neither deleted,
nor its id changed.
Your manager also told you that some other people unsuccessfully attempted to
implement the same database. They managed to implement the set of relation schemes,
2

but when they realized that the version of PostgreSQL at hand does not support
declarative referential integrity constraints, they gave up. The file ass3_db_q1.sql
containing their implementation is published on the course Assignments and Projects
page.
You are asked to implement the defined referential constraint for the given database
schema using triggers and PL/pgSQL trigger procedures. (Recall, you should pretend as
if the current version of PostgreSQL really does not support declarative referential
integrity constraints.)
Start from the partial implementation in the file ass3_db_q1.sql, and retain all
already implemented solutions. Create your own database and issue the command
\i ~/ass3_db_q1.sql
which will populate your database by table definitions and data. Then implement and test
your referential integrity constraint triggers.
When you finish implementation and testing your referential integrity constraints, restore
your database to its initial state (defined by data in ass3_db_q1.sql), and use the
command
\i ~/TriggerTests_11.cmd
to perform the final testing.
Submit:
i. The code of your functions and triggers both in a printed form and electronically
and
ii. PostgreSQL output when executing TriggerTests_11.cmd file only in a
printed form.

Question 2. User Defined Function [60 marks]
Your manager approached you again, and told you that the company has acquired a new
version of PostgreSQL that supports declarative defining of referential integrity
constraints. He also asked you to improve reliability of a database application.
The application is mainly aimed for tracking graduate students. It contains the following
relational database schema: S = {
Student ({StudentId, Stud_Name, Major, TotPts}, {StudentId})
Major ({MajorId, Maj_Name, Department}, {MajorId })
Course ({CourseId, Cour_Name, NoOfPts}, {CourseId })
Graduate ({StudentId, Grad_Date}, {StudentId })
Grades ({StudentId, CourseId, Term, Year, Grade}, { StudentId + CourseId + Term +
Year })
} I = {
Student [MajorId ]  Major [MajorId ],
3

Graduate [StudentId ]  Student [StudentId ],
Grades [StudentId ]  Student [StudentId ],
Grades [CourseId ]  Course [CourseId ]
}
A student may get more than one grade for the same course, but each in a different (term,
year) pair. For each course passed, a student may get the number of course points added
to his/her total points. Only one of all pass grades for a course adds points to student’s
total number of grades. When a student gets 360 points or more, he/she is recorded in the
graduate table.
Whenever a student passes a course, the database is updated by issuing a sequence of
INSERT and UPDATE commands. The grade is recorded first, then is the number of
points added to student’s total number of points, and finally is the total number of points
checked, and eventually, the student becomes a graduate.
Since database updates are made manually, many errors happen. Wrong numbers of
course points are added, duplicate points are added for the same course, total number of
points is erroneously computed, and so on. To avoid database inconsistency, people
spend hours checking consistency of the database after each examination period.
A consistent version of the database is stored in the file ass3_db_q2.sql published
on the course Assignments and Projects web page.
After a careful examination of the problem, you made the following decisions:
1. To use the current version of the database schema unchanged, to create your own
database, and to populate it by definitions and data using the command
\i ~/ass3_db_q2.sql
2. To implement the following user defined function using PL/pgSQL
grad_app(In_StudentId int, In_CourseId char, In_Term int,
In_Year int, In_Grade char, In_Grad_Date date)
that inserts a new tuple into Grades table, updates Student table, if needed, and
inserts a student in the Graduate table, if the conditions are satisfied.
3. To make the function grad_app() user friendly so that it conveys polite and
useful messages to users, and avoid crashing due to database exceptions.
4. To check your implementation using the command
\i ~/FunctionTests.cmds
on the initial database state (as defined by data in ass3_db_q2.sql), and
5. To run the following SQL statements
GRANT CONNECT ON DATABASE <database_name> TO PUBLIC;
GRANT SELECT, INSERT, DELETE, UPDATE ON Student, Major,
Course, Graduate, Grades TO PUBLIC;
against the database <database_name> before you allow users to use your database.

4

Submit:
 The code of your grad_app() function both in a printed form and
electronically,
 PostgreSQL output when executing FunctionTests.cmds file only in a
printed form.
 A printed explanation of what is the purpose of SQL statements
GRANT CONNECT ON DATABASE <database_name> TO PUBLIC;
GRANT SELECT, INSERT, DELETE, UPDATE ON Student, Major,
Course, Graduate, Grades TO PUBLIC;
To answer this question, read the PostgreSQL Manual. Find out:
 What is a database ROLE,
 What is the GRANT close used for,
 What kind of a role is PUBLIC?
Also, ask a pier to log in into your database before you execute the GRANT
statements. Then execute the GRANT statements and ask your pier again to log-in
and perform both a SELECT and a valid INSERT (or DELETE or UPDATE)
statement on any of the tables. Ask your pier to run your grad_app() function.
Analyze PostgreSQL’s reactions and messages on these actions.

Marking schedule:
 Function grad_app()45 points.
 Explanation of the GRANT statement 15 points.

What to hand in:  A statement of any assumptions you have made.
 The code of your functions, triggers and grad_app() both in a printed form and
electronically and
 PostgreSQL output when executing TriggerTests_11.cmd,
FunctionTests.cmd files only in a printed form, and
 Your explanation of the GRANT statement in a printed form.

5

Using PostgreSQL on the Workstations
We have a command line interface to PostgreSQL server, so you need to run it from a
Unix prompt in a shell window. To enable the various applications required, first type
either
> need comp302tools
or
> need postgresql
You may wish to add either “need comp302tools”, or the “need postgresql”
command to your .cshrc file so that it is run automatically. Add this command after
the command need SYSfirst, which has to be the first need command in your
.cshrc file.

There are several commands you can type at the unix prompt:
> createdb 〈database_name〉
Creates an empty database. The database is stored in the same PostgreSQL server
used by all the students in the class. Your database may have an arbitrary name, but
we recommend to name it either userid or userid_x, where userid is your
ECS user name and x is a number from 0 to 9. To ensure security, you must issue the
following command as soon as you log-in into your database for the first time:
REVOKE CONNECT ON DATABASE <database_name> FROM PUBLIC;
You only need to do this once (unless you get rid of your database to start again).
Note, your markers may check whether you have issued this command and if they
find you didn’t, you may be penalized.
> psql [ –d 〈db name〉]
Starts an interactive SQL session with PostgreSQL to create, update, and query tables
in the database. The db name is optional (unless you have multiple databases)
> dropdb 〈databas_name〉
Gets rid of a database. (In order to start again, you will need to create a database
again)
> pg_dump -i 〈databas_name〉>〈file_name〉
Dumps your database into a file in a form consisting of a set of SQL commands that
would reconstruct the database if you loaded that file.
> psql –d <database_name> -f <file_name>
Copies the file <file_name> into your database <database_name>.
Inside and interactive SQL session, you can type SQL commands. You can type the
6

command on multiple lines (note how the prompt changes on a continuation line). End
commands with a ‘;’
There are also many single line PostgreSQL commands starting with ‘\’ . No ‘;’ is
required. The most useful are
\? to list the commands,
\i 〈file_name〉
loads the commands from a file (eg, a file of your table definitions or the file of data
we provide).
\dt to list your tables.
\d〈table_name〉to describe a table.
\q to quit the interpreter
\copy <table_name> to <file_name>
Copy your table_name data into the file file_name.
\copy <table_name> from <file_name>
Copy data from the file file_name into your table table_name.

Note also that the PostgreSQL interpreter has some line editing facilities, including up
and down arrow to repeat previous commands.
For longer commands, it is safer (and faster) to type your commands in an editor, then
paste them into the interpreter!