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)
