2020/3/12 COMP3311 20T1 - Assignment 1
https://cgi.cse.unsw.edu.au/~cs3311/20T1/assignments/a1/index.php 1/4
COMP3311 20T1 Assignment 1
SQL, Views, PLpgSQL, Functions, Triggers
Database Systems
Last updated: Sunday 8th March 12:48pm

Aims
The aims of this assignment are to:
formulate SQL queries;
populate an RDBMS with a dataset, and analyse the data;
design test data for testing SQL queries;
create SQL views;
understand the limitations of SQL queries; and
create SQL functions, PLpgSQL and triggers (when needed).
Description
This assignment is based on a simplified database to manage policy information for a car insurance company. The company sells
different types of insurance coverage that protect against loss, damage, injury etc. involving a car (the insured item). For example, the
company may sell greenslips, 3rd party property damage schemes or comprehensive schemes.
A car policy is for one covered item with one or more coverages. In this assignment, we consider only the case where clients are private
individuals (i.e. we do not consider corporate insurance schemes). A policy is sold directly by the company's staff (the agent). The
insured party can be multiple people, such as a person and his/her spouse or a family.
The first step in selling insurance coverage is to draft a policy. After that, the insurance company has a rater who determines the rate that
will be charged. The rate is based on the car, the particular coverage provided, and any qualifications (fine print) given by the insured
party.
The final step is to underwrite (approve) the policy. The underwriter takes the ultimate responsibility for doing business with the insured
party. Not all the policies will be approved without change. The underwriter may choose to refuse some policies. In such cases, the
agent needs to consult with the insured parties before making any modification. The agents, raters and underwriters are all staff
members of the company.
Further notes:
A policy can include only one item.
An insurance policy may be renewed/updated many times.
A policy may have several coverages; for instance, fire, stolen, etc.
The procedures of an underwriting and rating may be processed several rounds by different staff members.
After a policy is underwritten, the client can pay and the policy will then be enforced.
From the above requirements, a relational database schema consists of 11 tables is created as below:
1. Table Person records personal details.
2. Table Policy records policy details.
Here, pname takes one of the 3 values - G, C, and T. These three values correspond to three different policy types -
Greenslip, Comprehensive Insurance, and Third Party Insurance.
The attributes effectivedate and expirydate specify the period during which a policy is active.
The attribute agreedvalue presents the amount of money that the company will pay to the client if the insured car has a total
loss.
The attribute status takes one of the 6 values - D, RR, AR, RU, AU, E. These correspond to the 6 possible states of policy
processing: once a policy is drafted its status is D. Then, the policy is sent to a rater; and the status will be set to either RR
or AR according to a rating result; AR means an approval while RR means a refusal. Finally, the policy is sent to an
underwriter where AU means an approval and RU means a refusal. Once a policy is underwritten it will be enforced;
consequently the status is set to E.
3. Table Underwriting_record presents the detail of each underwriting where status takes one of the possible three values, R, A, and
W. These correspond to a refusal, an approval, and a waiting.
4. The attribute status in the table Rating_record is similar to that in Underwriting_record. The attribute rate in Rating_record tells a
client the money that he/she has to pay to have the corresponding coverage.
5. Table Coverage records the detail of a coverage. The attribute cname describes the coverage type (e.g., the third party
insurance). The attribute uplimit specifies the maximal amount of money that the company will pay to a client under the coverage.
6. Tables Client and Staff are specializations of Person
7. Table Insured_item records the detail of insured cars.
8. The rest of the tables are shown in the overall relational schema below:
2020/3/12 COMP3311 20T1 - Assignment 1
https://cgi.cse.unsw.edu.au/~cs3311/20T1/assignments/a1/index.php 2/4
The above schema, together with some sample data, are included in preload.sql. Based on this provided schema, you are required to
answer the following questions by formulating SQL queries. You may create SQL functions or PLpgSQL to help you, if and only if the
standard SQL query language is not expressive and powerful enough to satisfy a particular question. To enable auto-marking, your
queries should be formulated as SQL views, using the view names and attribute names provided. If you wish, you may define and
include additional, intermediate views if they make your solution simpler to derive / express.
If order is specified, marks will only be granted if your solution output is in correct order.
Queries
1. List all persons who are both clients and staff members. Order the result by pid in ascending order.
create or replace view Q1(pid, firstname, lastname) as ...

2. For each car brand, list the car insured by the most expensive policy (the premium, i.e., the sum of its coverages' rates). Order the
result by brand, and then by car id, pno if there are ties, all in ascending order.
create or replace view Q2(brand, car_id, pno, premium) as ...

3. List all the staff members who did not sell any policies in the last 365 calendar days (from today). Note that policy.sid records the
staff who sold this policy, underwritten_by.wdate records the date a policy is sold (we ignore the status here). Order the result by
pid in ascending order.
create or replace view Q3(pid, firstname, lastname) as ...

4. For each suburb in NSW, compute the number of policies that have been sold to the policy holders living in the suburb (regardless
of the policy status). Order the result by Number of Policies (npolicies), then by suburb, in ascending order. Exclude suburbs with
no sold policies. Furthermore, suburb names are output in all uppercase.
create or replace view Q4(suburb, npolicies) as ...

5. Find the policies which are rated, underwritten, and sold by the same staff member. Order the result by pno in ascending order.
create or replace view Q5(pno, pname, pid, firstname, lastname) as ...

6. List the staff members (their firstname, a space and then the lastname as one column called name) who only sell policies to cover
one brand of cars. Order the result by pid in ascending order.
create or replace view Q6(pid, name, brand) as ...

2020/3/12 COMP3311 20T1 - Assignment 1
https://cgi.cse.unsw.edu.au/~cs3311/20T1/assignments/a1/index.php 3/4
7. List clients (their firstname, a space and then the lastname as one column called name) who hold policies to cover all brands of
cars recorded in the database. Order the result by pid in ascending order.
create or replace view Q7(pid, name) as ...

8. For each policy X, compute the number of other policies (excluding X) whose coverage is contained by the coverage of X. For
example, if a policy X has 3 coverages (identified by cname), say {C1, C2, C3}, and another policy Y has 2 coverages, {C1, C3},
we say Y's coverage is contained by X's. In case if X's and Y's coverages are identical, their coverages are contained by each
other. Order the result by pno in ascending order.
create or replace view Q8(pno, npolicies) as ...

9. Create a stored function that increases/decreases the rate by Adj% for all active (as of today) and enforced (with status E)
policies. Other policies shall not be affected. Adj is an integer between -99 and 99. For example, if the original rate is 200 and Adj
is -20, the new rate will be 160. If the original rate is 300 and Adj is 10, the new rate will be 330. The function returns the number
of policies that have been adjusted.
create or replace function ratechange(Adj integer) returns integer ...

10. The insurance company is to going to run a promotion campaign. If you buy a new policy (whether solely or jointly) and it is finally
approved (i.e., the policy status becomes E), all other active and enforced policies that you are involved (including solely and
jointly) will have their expiry dates extended by 30 calendar days. However, if any staff of the company is covered by the new
policy, this promotion will not apply. Create a trigger (or triggers) to keep track when a policy status is changed to E and implement
this promotion campaign accordingly.
Submission
Submission : Submit this assignment by doing the following:
Login to Course Web Site > Assignments > Assignment 1 > Assignment 1 Specification > Make Submission > upload a1.sql > [Submit]
The a1.sql file should contain answers to all of the exercises for this assignment. It should be completely self-contained (without the
schema and sample data) and able to load in a single pass.
Deadline : Friday 20th March 17:00
Late Penalty: Late submissions will have marks deducted from the maximum achievable mark at the rate of 0.5% of the total mark per
hour that they are late (i.e., 12% per day).
Assessment
This assignment is worth a total of 12 marks. It will later be scaled to 20 percent for the course as described in the course outline.
Your submission (in a file called a1.sql) will be auto-marked to check:
whether it is syntactically correct;
if using SQL queries without creating a function or PLpgSQL unless it is necessary; and of course,
if each query produces correct results.
Queries Q1-Q8 are each worth 1 mark. The stored function and the trigger questions are each worth 2 marks.
What To Do Now
Make sure you read the above description thoroughly, and review and/or test out the provided schema and sample data preload.sql. The
sample data is provided to help you quickly get started. While the same schema will be used to test your submission, a different dataset
(that may be larger, smaller, or totally different) may be used for auto-marking. Therefore, you may need to create your own or modify
the provided data file to test your queries before submitting your assignment. Note that you DO NOT need to submit your data file as
part of the submission..
Reminder: before you submit, ensure that your solution (a1.sql) will load into PostgreSQL without error if used as follows on grieg:
% dropdb a1
% createdb a1
% psql a1 -f preload.sql
% psql a1 -f a1.sql
... will produce notices, but should have no errors ...
% psql a1
... can start testing your solution ...
Do not include any content from preload.sql, any schema definitions or data insertions in your a1.sql. If we have to fix errors in your
solution before it will load, you will incur a 5 (out of total 20) mark "penalty". For example, if any of your view names or attribute names
are different from the names specified above, you will incur a 5 mark "administrative penalty". Finally, it is entirely your responsibility to
backup your solution. If you cannot submit the assignment because you lost data due to inadequate backup procedures, you will be
treated as if you had not done the assignment in the first place.
Sample Output
Sample output from queries Q1 to Q8 based on the provided sample dataset in preload.sql is listed below. It is included in this
specification so that you can verify the output formatting. Do not rely on them for correctness checking. We may use a different / much
2020/3/12 COMP3311 20T1 - Assignment 1
https://cgi.cse.unsw.edu.au/~cs3311/20T1/assignments/a1/index.php 4/4
larger dataset in auto-marking. Before submitting your assignment solution, please test it thoroughly by consider all situations and
exceptions carefully (e.g., by updating the data or creating more data).
a1=> select * from Q1;
pid | firstname | lastname
-----+-----------+----------
0 | Jack | White
8 | Frederick | Brown
(2 rows)

a1=> select * from Q2;
brand | car_id | pno | premium
--------+--------+-----+---------
Honda | 4 | 4 | 1500
Nissan | 3 | 3 | 500
Toyota | 5 | 6 | 600
(3 rows)

a1=> select * from Q3;
pid | firstname | lastname
-----+-----------+----------
1 | David | Lee
5 | Teresa | Story
6 | Alice | Wang
7 | David | Bond
8 | Frederick | Brown
(5 rows)

a1=> select * from Q4;
suburb | npolicies
--------------+-----------
ALEXENDRIA | 1
PARRAMATTA | 2
WOOLOOMOOLOO | 3
SURRY HILL | 4
(4 rows)

a1=> select * from Q5;
pno | pname | pid | firstname | lastname
-----+-------+-----+-----------+----------
0 | C | 0 | Jack | White
6 | G | 0 | Jack | White
7 | G | 0 | Jack | White
(3 rows)

a1=> select * from Q6;
pid | name | brand
-----+-----------------+--------
4 | Vicent Thomas | Honda
5 | Teresa Story | Nissan
6 | Alice Wang | Nissan
7 | David Bond | Nissan
8 | Frederick Brown | Nissan
(5 rows)

a1=> select * from Q7;
pid | name
-----+--------------
3 | Vicky Donald
(1 row)


a1=> select * from Q8;
pno | npolicies
-----+-----------
0 | 1
1 | 2
2 | 1
3 | 2
4 | 2
5 | 6
6 | 5
7 | 5
8 | 5
9 | 5
10 | 5
11 | 5
(12 rows)