This assignment aims to develop a broad knowledge of storing and managing data to satisfy the organizational requirements. These will be reinforced by developing the practical skills required for using the structures and features of a query language in order to maintain and interrogate a relational database management system using Oracle SQL.
Description
Best Insurance Company (BIC) is a car insurance company based in America. The company sells different insurance products which have different types of coverage that protect against accidents, loss, damage, injury etc. involving a car (the insured item). For example, for car insurance, the company may sell 3rd party property damage schemes or comprehensive schemes. The type of coverage provided is described in an insurance policy.
For an individual customer, a car policy is for one covered item with one or more coverages. The clients (customers) are private individuals (i.e. do not consider corporate insurance schemes).
A policy is sold directly by the company's staff (an internal agent). The insured party can be multiple people, such as a person and his/her spouse or a family or any additional drivers they want to include.
The first step in selling insurance coverage is to create a policy. This determines precisely which items are covered and for what kind of eventualities.
After a policy has been created, the insurance company has a rater who determines the rate that should be charged. The rate (in terms of money) is based on the car, the particular coverage provided, and any qualifications 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 Since the underwriter may choose to refuse some policies. In such cases, the agent needs to consult with the insured parties before making any modification. Typically, the agent (or broker), rater and underwriter are staff members in the insurance company.

Notes:
⦁ A policy can include only one item and we are dealing with car insurance business only.
⦁ An insurance policy may be renewed/updated many times.
⦁ A policy may have several coverages; for instance, accident, fire, theft, etc.
⦁ The procedures of an underwriting and rating may be processed several rounds by different staff members.
⦁ Once a policy is underwritten, the client will pay and the policy will be enforced.

Given below are the initial documentations submitted by the database consultant who was working for BIC. Use these files as a starting point, you must make the necessary amendments to ensure compliances with normalisation (at least up to 3NF) and data integrity.

Entity type Attributes
Person pid, name(firstname, lastname), bdate, address(street, suburb, state, postcode)
Policy pno, pname, status, effectivedate, expirydate
Client cid
Staff sid
Underwriting_record urid, status
Insureditem id, brand, model, year, reg
Coverage coid, cname, uplimit, comments
Rating_record rid, rate, status
Underwritten_by wdate, comments
Rated_by rdate, comments
Insured_By Cid, Pno

Design Notes
⦁ Table Person records personal details.
⦁ Tables Client and Staff are specializations of Person
⦁ Table Policy records policy details of the insured Item, staff who deals with the insurance as well as the following 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 (enforced).
⦁ Table Underwriting_record presents the detail of each underwriting for the policy where status takes one of the possible three values, R, A, and W. These correspond to a refusal, an approval, and a waiting.
⦁ 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.
⦁ Table Coverage records the detail of coverage. The attribute cname should take a value from a fixed domain, so that it can be queried by a database user. The attribute uplimit specifies the maximal amount of money that the company will pay to a client under the coverage.
⦁ Table Insured_item records the detail of insured cars.
Once you have completed the design (after submitting the Part 1 of your assignment), you will be provided with some sample data (uploaded on Moodle under Assignments section) which contains the ORACLE SQL scripts for the existing design. You may open this file using SQL Developer or using WordPad. Please note this data is only for reference purpose, as they are the data dump from their legacy system. You must amend the data to reflect your own design, and a more relevant and current details wherever needed.

Tasks

Part 1 - Database design 40%

Task 1 - Entity Relationship Diagram

Create a conceptual data model to represent the data requirements of the BIC scenario. Your model should include an E-R diagram and entity headings (listing the attributes associated with entity types). List any assumptions you have made.

Task 2 – Relational Schema

Using your conceptual data model, develop a relational schema consisting of the definitions of the domains and relations that represent the entity and relationship types, including primary keys, foreign keys and any constraints.

Part 2 - Database implementation 60%

Task 3 – Create Tables and Insert Sample Data
Use your design to implement the Help Desk database using Oracle DBMS. You should use SQL statements to create tables and insert data. SQL statements (scripts) used for Creating Tables and Inserting Data must be saved as a TEXT or .sql file for submission.

Using Oracle DBMS (in the University server), Create the Tables and relations consistent with your design. Tables must be created using SQL statements (CREATE TABLE) making sure the appropriate fields and data types are defined, appropriate Primary Key and Foreign Keys (wherever required) are defined, and that other data integrity rules are enforced. Create appropriate indexes if required.
Make sure the tables are accessible in the University Oracle Server using your username (student number) and password.
Populate the sample data to the tables you have created. Values must be inserted using SQL (INSER INTO) statements.
⦁ You will be provided with some sample data (as INSERT Queries devised by the consultants who were working on this project). You may modify and use the given sample data to match your own design and add a set of your own sample Data (minimum 3, maximum 5 records in each table).
Task 4: Oracle SQL & PL/SQL
You should use correct SQL statements in Oracle to implement the following queries and data modifications.
SELECT Queries
⦁ List the staff members who are not currently insured with the company.
Hint: Currently insured: Policy is enforced, and current date is within the effective and expiry dates
⦁ List the policy details (policy holder details, policy details including expiry date, car details etc.) of all the policies that is going to expire between now and 31-Dec-2022.
Hint: Take the system date into consideration; you must list the policies that are going to expire from system date to 31st December 2022.
⦁ List the most popular car models (the most popular model holds the maximal number of policy) insured in Randwick (that is, cars whose owners live in Randwick).
Hint: This includes all cars (past insured, currently insured, being insured, etc); look at the cars in the policy table and ignore the status. Don't forget to deal with case sensitivity (lower case, upper case, and mixture of Randwick).
⦁ List all the staff members whose application for insurance has been rejected in the last 12 months (or 365 days).
Hint: underwriting status shows rejected, and underwriting date is in the last 12 months.
⦁ List all the staff members who own a policy with premium amount less than 600. (Note the premium of a policy is the sum of the rates of the coverage of the policy.)
Hint: Just look at the policies in the policy table regardless their status.
⦁ Find the staff members who process their own policies. That is, each of such staff members who either sells or rates or underwrites a policy owned by him/herself.
Hint: Staff deals with her own policy. Or staff rates her own policy. Or staff underwrite her own policy
⦁ List the staff members whose selling covers every car brand in the database.
Hint: Look at insured item regardless status.
⦁ List the most common insurance coverages which have been sold to clients in the last 9 months.
Hint: A policy is enforced, and effective date is in the last 9 months
UPDATE and DELETE Queries
⦁ Decrease the rate by 10% for all the coverages that are still waiting for approval.
Hint: Waiting means underwriting status shows waiting.
⦁ Delete a client whose policy has been expired.
Can you delete the client? If you can’t delete explain why?

PL/SQL Procedures:
⦁ Create a PL/SQL Procedures to compute the difference between average premium of the policies held by staff members and the average premium held by non-staff members in the last 12 months.
Hint: Policy is enforced, and Policy duration intersects last 12 months from current date.
If you have a policy jointly held by a staff member and non-staff member then count it twice: once in each category.
⦁ Create a PL/SQL Procedures to compute for each suburb in NSW, the number of the policies that have been sold in the last 12 months to the policy holders living in the suburb.
Hint: Policy is enforced, and policy effective date is in the last 12 months.
⦁ For each current policy X, compute (count) the number of other current policies each of which has the coverage containing the coverage of X. (A policy is ``current'' if the current date lies between the policy's effectivedate and expirydate). The procedure should be able to accept the value for ‘X’ during run time
Hint: A policy is current has the same meaning as "currently insured".