Assignment 2 - University of Ballarat

Document Sample
Assignment 2 - University of Ballarat Powered By Docstoc
					                          University of Ballarat – School of Information Technology and Mathematical Sciences
                                         CP611 Database Management Systems
                                       Assignment 2- Period 3, 2005 – Weighting 20%

                                           Implementation - An insurance company

1. Sample solution for stage 1

1.1. ER Design
                           RATE                                                                         INSUREDITEM
                           Coverage                COVERAGE                    COVER                    Id
                           Rater                   CoverageId                  Item                     Make
                           Action                  Description                 Policy                   Model
                           Happened                CoverValue                  Coverage                 Year
                           Value                                                                        Registration

                                                                 Pid                      HOLD
        EMPLOYEE                                                                          IParty
                                         UNDERWRITE              Created
Eid                                                                                       Policy
                 RATER                   Underwriter             ValidFrom
                                         Policy                  ValidUnitil
                                         Happened                Premium
             UNDERWRITER                 Action                  PaidOn
PhoneNo        ADJUSTER                                                                                   CLIENT
                                                                 CLAIM                                              GivenName
                                                                 Id                                                FamilyName
                                                                 Policy                                                  Street
                                                                 Claimant                          INSUREDPARTY         Suburb
                                                                 Adjuster                                                 State
                                                                 LodgeDate                                            PostCode
                                                                 EventDate                         CLAIMANT              Phone
                           Amount                                Reserve
  1.2. Sample schema

  Table Name            Field          Type           Description

-- Employees: information for employees

  EMPLOYEE              Eid           NUMBER     PRIMARY KEY
                        GivenName     TEXT(20)
                        FamilyName    TEXT(20)
                        Street        TEXT(20)
                        Suburb        TEXT(20)
                        State         TEXT(3)
                        PostCode      NUMBER,
                        PhoneNo       NUMBER(10)

-- Raters:

  RATER                 Eid            NUMBER         PRIMARY KEY
                                                      REFERENCES EMPLOYEE(Eid)

-- Underwriters:

  UNDERWRITER           Eid            NUMBER         PRIMARY KEY
                                                      REFERENCES EMPLOYEE(Eid)

  ADJUSTER              Eid            NUMBER         PRIMARY KEY
                                                      REFERENCES EMPLOYEE(Eid)

-- Policies:
-- * represents a single insurance policy
-- * status values:
--         DR ... currently being drafted (initial state)
--         RA ... currently being rated
--         UW ... currently being considered for underwriting
--         OK ... underwritten (active if valid fields non-NULL)
--         CA ... cancelled

POLICY                  Pid            NUMBER        PRIMARY KEY
                        Created        DATE
                        ValidFrom      DATE
                        ValidUntil     DATE
                        Premium        REAL
                        PaidOn         DATE
                        Status         TEXT(2)
-- Clients:
-- assume that every organisation has a contact person
-- for private individuals, organisation is NULL

CLIENT                  Cid             NUMBER PRIMARY KEY
                        Organisation    TEXT(40),
                        GivenName       TEXT(20)
                        FamilyName      TEXT (20)
                        Street          TEXT (20)
                        Suburb          TEXT (30)
                        State           TEXT (3)
                        Postcode        TEXT (4)
                        Phone           TEXT (15)

CLAIMANT                Id              NUMBER           PRIMARY KEY
                                                         REFERENCES CLIENT(Id)

INSUREDPARTY             Id             NUMBER           PRIMARY KEY
                                                         REFERENCES CLIENT(Id)

-- Holds:
-- relationship between client and policy
-- allows multiple persons to be associated with a single policy

                 Policy    NUMBER        REFERENCES POLICY(Pid),
                 PRIMARY KEY(IParty,Policy)

-- Underwrites:
-- audit of actions during policy underwriting
-- actions: D ... decline, A ... approve

UNDERWRITE            Underwriter NUMBER
                             REFERENCES UNDERWRITE(Eid)
                      Policy      NUMBER REFERENCES POLICY(Pid)
                      Happened    DATE
                      Action      TEXT(1)
                 PRIMARY KEY (Underwriter, Policy, Happened)

-- InsuredItems: brief information of cars

INSUREDPARY             Id             NUMBER            PRIMARY KEY
                        Make           TEXT(15)
                        Model          TEXT(20)
                        Year           TEXT(4)
                        Registration   TEXT(10)
-- Coverage:
-- description what is covered and
-- what is the entitlements if it's claimed against

COVERAGE                 Id              NUMBER        PRIMARY KEY
                         Description     TEXT(40)
                         CoverValue      REAL

-- Covers:
-- multi-to-multi relationship among insureditem, policy and coverage

COVER                    Item   NUMBER
                                REFERENCES INSUREDITEM(Id),
                   Policy       NUMBER    REFERENCES POLICY(Pid),
                   Coverage     NUMBER
                   REFERENCES COVERAGE(CoverageId),
                   value        REAL
     PRIMARY KEY(Item,Policy,Coverage)

-- Rates:
-- audit of actions during rating
-- the value is the contribution towards the preimum
-- for the particular coverage being rated
-- actions: D ... decline, A ... approve

RATE               Coverage     NUMBER
                   REFERENCES Coverage(CoverageId),
                   Rater        NUMBER REFERENCES RATER(Eid),
                   Action       TEXT(1)
                   Happened     DATE
                   value        REAL
         PRIMARY KEY (Coverage, Rater, Happened)

-- Claim:
-- * main details of a claim on a specific policy
-- * on-going processing details are held in ClaimAction
-- * status:
--        A ... active, Z ... closed

CLAIM                    Id              NUMBER        PRIMARY KEY,
                         Policy          NUMBER        REFERENCES POLICY(Pid),
                         Claimant        NUMBER        REFERENCES CLAIMANT(Cid),
                         Adjuster        NUMBER        REFERENCE ADJUSTER(Eid),
                         LodgeDate       DATE
                         EventDate       DATE
                         Reserve         REAL
                         Status          TEXT(1)
-- ClaimAction:
-- * audit of actions in the processing of a claim
-- * actions:
--       OP ... open the claim (and set reserve)
--       RE ... re-open claim (if previously closed)
--       PO ... payment out (+ amount + recipient)
--       PI ... payment in (+ amount + source)
--       SB ... subrogate claim (+ income + source)
--       CL ... close the claim
CLAIMACTION             Id             NUMBER        PRIMARY KEY
                        Claim          NUMBER        REFERENCES CLAIM(Id),
                        Action         TEXT(2)
                        Happened       DATE
                        Amount         REAL

2. SQL

2.1. Before start

When defining select queries and PL/SQL procedures, you are required to adhere to the
following output formatting conventions:

    names of people should be printed as
     GivenName FamilyName (e.g. John Smith)
     in a column labelled NAME
    addresses should be printed as
     Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038)
     in a column labelled ADDRESS
    monetary values should be printed with a dollar symbol ($), two digits after the
     decimal point, and with space for 7 digits before the decimal point
    You are free to create temporary queries for to answer these queries

You should use consistent and legibile formatting in laying out your SQL queries.
Include (brief) comments for any query or procedure that uses an "unusual" approach.

2.2. SQL

You are required to do following queries based on the standard solution. You can only
use SQL View to create your queries instead using the interface version.

    1. List the names and address of all the underwriters in the company.

    2. List the names and address of all clients who have had coverage declined.
 3. List the names of all claimants who hold an insurance policy with the company.

 4. List the total amount of money collected from policy premiums.

 5. List the total amount of money paid out in claims.

 6. List the amount of money paid out in claims of each policy.

 7. List the names of any clients who have made a claim against their own insurance

 8. List the policy numbers for any policies that have a coverage benefit which is
    larger than the market value of the car.

 9. How many of each make/model of car are insured?

 10. For each suburb in NSW, list the average premium of the policies that have been
     sold to clients living in that suburb.

 11. List the policy id and car make/model for policies where the vehicle is covered by
     all of the possible coverages.

3. Submission

3.1. When to submit

Due Wednesday 5:30 PM, Week 12 (i.e. 26th, October 2005) Period 3, 2005

Extensions will only be granted for reasons that can be substantiated by medical or
equivalent documentation e.g. medical certificate. Please see the Unit Co-ordinator.

3.2. Where to submit

Please place your assignments in my assignment box next T127 labelled “MA, Liping”.

3.3 What to do?

 1. Step 1:
     You can start with reading the sample solution of assignment stage 1, to be
       familiar with all the entities and relationships.
     Create a database named DBxxxxxxx_1.mdb (xxxxxxx is your student id)
       according to the sample solution and relational schema. All the tables should
       be created with “create statement” with SQL View. You should clearly state
       the primary and foreign keys.
     Insert 4 records into each table with “insert statement” again with SQL View.

 2. Step 2:
      Download the sample database Insurance_Ass2_SampleDB.mdb (I have
       created a database for you to run your queries) from the CP611 website and
       save as DBxxxxxxx_2.mdb in your directory.
      Download the sample output from the CP611 website as well.
      Do queries 1-11 with SQL View. Match your output of each query and the
       sample output provided and refine your query until both outputs match.

3. Step 3:
    Write a report of assignment 2 which contains
      a) All the create statements under the title of “Making Schema”

       i.e. Making Schema

          CREATE TABLE Person
             Pid  number      PRIMARY KEY,

       b) All the insert statements under the title of “Making Database”

       i.e. Making Database

          INSERT INTO Person VALUES (… …);

       c) All the queries including all the additional queries under the title of “SQL”

       i.e. SQL

          Q1: SELECT … FROM … WHWRE …;

          Q2: SELECT …


4. Step 4:
    After you complete all the queries, download Insurance_Ass2_TestDB.mdb
      (will be available soon) from the CP611 website and save it as
      DBxxxxxxx_3.mdb. Load or copy all the queries you built for
      DBxxxxxxx_2.mdb to DBxxxxxxx_3.mdb.

What to submit?

You are required to submit:
a) Documentation: a report of assignment 2 as specified in Step 3.
b) CD: contains DBxxxxxxx_1.mdb, DBxxxxxxx_2.mdb and DBxxxxxxx_3.mdb.
 Please DO NOT email your solution to me. You must submit a hardcopy of the
 documentation and a CD with a softcopy which contains three databases. Please use
 an assignment cover page to state your details.

 How to submit?

 Before the due date, you should submit your assignment to my assignment box which
 is near my office (between lecture room T127 and my office T139). Make sure you
 put your assignment into MY box (which is labelled “MA, Liping”).

3.4. Assessment Criteria

1. Creation of the database according to the documentation provided in 1.2.

2. Data correctness and quality. Please use appropriate data for your examples (e.g. do
   not use inappropriate titles or client names)

3. Queries. Correctness of queries, elegance, use of joins (only SQL joins allowed,
   please DO NOT use inner joins) , appropriate use of aggregate functions.

4. How clear and well organised your presentation is. You should write all the queries in
   consistent style and use indent format.

 Good luck … Liping

Shared By: