Database Design

Hey Guys, just thought of posting this project that I did back in school. I will include the problem statement as well as the diagrams and SQL Queries. I got a good grade on this project and I did this project on my own. Some of the statements in the problem discussion can be confusing, so feel free to make your own assumptions there.

The professor does not repeat the problem statements in future semesters, so there is no risk in posting the solution online.

Problem Description

Design, develop, and test Insurance Management System that provides insurance services to clients, insurance companies and agents based on combined database. The project is in four parts: conceptual database design (Phase I), logical database design (Phase II), Oracle relational database implementation (Phase III), and final report &demo (Phase IV).

1) A client can get insurance quotes and file claims more than once. Each client contains such information: Client ID, Name, Age, Address, Phone Number (one or more phone numbers), and Email.

2) The system only provides two ways to clients getting quotes on their insurance, online with insurance companies and non-online with agents. Each quote, which has the attributes Quote ID, Date Prepared, Proposed Policy Period, and Premium, is issued by one particular client. Some quotes have discount that is uniquely determined by Discount Name and quote, whereas others do not. One quote may have one or more discounts which contain Discount Name and Rate.

3) Some clients in the system may file claims including Claim ID, Related Person (one or more), and Date. Each claim must associate with exact one policy. Adjusters who work for insurance companies evaluate the filed claims, and then process final reports respectively with the attributes Report Number, Report Date and Description. Report Number, Report Date and the claim determine one unique report. The same claim may be evaluated by different adjusters. Some reports may have refund that is an aggregation of Refund-Date, Amount, and Name of Related Person. Each refund is uniquely decided by specific report and Refund-Date. The system keeps tracking of accountants who issue refunds.

4) An agency has Name, Years in Business, and Tax ID information and must own one or more stores. The value of Tax ID is between “000001”

and “500000”. Each store belongs to one specific agency. It has the following information: Store Number, Name, Location (street, city, state, zip code), Phone Number and Level (1-5). The same store number may associate with different agencies, but Store Number with related agency determines one unique store. Agent# is a unique ID of agents, who work in stores and one or two of whom are managers of that store. Each agency sells insurance products provided by one or more insurance companies, but the number of related insurance companies cannot exceed 10. Some insurance companies may not have any agency.

5) An insurance company offers three types (auto, life, and home) of insurance products, and has the following information: Name, ISP, Website Address, Email and Phone Number. Each product contains Commission Rate, Code, Coverage, Limits/Deductibles and Premium; whereas Code, Coverage, Limits/Deductibles and the insurance company associated uniquely determine one specific product. The same product form one insurance company may have different commission rates to different agencies. Employees are hired by insurance companies with the following information: SSN, Name (Fname, Minit, and Lname), Age (<=65), Address (one or more), Phone Number, and Salary. All employees can be divided into three parts: Accountant, Actuary, and Adjuster. They may play more than one role at the same time. For adjusters, the system also records the rank.

6) Once a client accepts a quote, she/he would become a policy holder who holds a policy based on the accepted quote. One policy holder may have at most five insured persons who only have Name (may not be unique) recorded in the system. Meanwhile, each policy has a unique#, Date, Final Premium and Policy Terms. One policy must have one or more payments. For each payment, Serial Number (may not be unique), Date, and Amount are recorded, whereas Serial Number, Payment Date, and the policy determine one particular payment.

Project Questions

a)    Can you think 5 more rules (other than the one explicitly described above) that are likely to be used in the system?

Answer: The following additional rules are likely to be used in the system

1.     The insurance company must process a claim within 60 days of the claim being made. Create a special view which shows the claims that have not been processed for more than 50 days, so that they are made a priority.

2.     Claims of Clients who make more than 5 claims per year should be evaluated by at least two Adjusters.

3.     A Client who is an employee should not be allowed to process his own claims, or claims in which he is one of the Insured persons.

4.     The quote acceptance date should be captured in the system.

5.     Clients who rejected a quote should be given another quote with a higher discount.

b)    Is the ability to model super-class/subclass relationships likely to be important in such environment? Why or why not?

Yes, I believe that the ability to model super class subclass relationships is important for such an environment.

Super class/ Subclass relationships help us model complex concepts like attribute inheritance in such an environment. There are likely to be many types of Employees, and many types of Insurance products which are best represented in the form of a subclass of their respective superclass.

ASSUMPTIONS

     I made the following assumptions for this project.

1. Client is a client if he got at least one quote.
2. Each quote is given to one client only
3. Each policy may be associated with more than one claim
4. Each report is related to one claim only
5. Not all accountants issue refunds
6. Name of agency is unique
7. An insurance product by a company may be sold by zero or more agencies
8. A client may hold any number of policies ( eg one for life, one of auto)
9. A policy holder can hold one or more policies
10. A policy may not be held by 0 or more policy holders
11. A person may be related to 0 or more claims
12. A claim may be related to 1 to 5 people.
13. A quote is generated by one Agent only and each agent can generate any number of quotes.
14. Each company can generate any number of quotes. Each quote is generated by only one company.

EER DIAGRAM

RELATION SCHEMA

3.  The following are the SQL queries to CREATE the tables.

CREATE TABLE quote 
  ( 
     qid        INT NOT NULL, 
     qtype      VARCHAR(20) NOT NULL, 
     premium    DECIMAL(10, 2) NOT NULL, 
     isaccepted VARCHAR(10), 
     prepdate   DATE NOT NULL, 
     fromdate   DATE NOT NULL, 
     todate     DATE NOT NULL, 
     policyid   INT NOT NULL, 
     clientid   INT NOT NULL, 
     PRIMARY KEY(qid) 
  ); 

CREATE TABLE discount 
  ( 
     qid      INT NOT NULL, 
     discname VARCHAR(40) NOT NULL, 
     CONSTRAINT pkdiscount PRIMARY KEY(qid, discname) 
  ); 

CREATE TABLE rate 
  ( 
     discname VARCHAR(40) NOT NULL, 
     rate     DECIMAL(5, 2) NOT NULL, 
     PRIMARY KEY(discname) 
  ); 

CREATE TABLE policy 
  ( 
     policyid     INT NOT NULL, 
     finalpremium DECIMAL(10, 2) NOT NULL, 
     policyterms  VARCHAR(200), 
     policydate   DATE NOT NULL, 
     PRIMARY KEY(policyid) 
  ); 

CREATE TABLE store 
  ( 
     agencyname  VARCHAR(40), 
     storeno     INT, 
     storename   VARCHAR(40) NOT NULL, 
     storestreet VARCHAR(40) NOT NULL, 
     storezip    CHAR(5) NOT NULL, 
     storephone  VARCHAR(12) NOT NULL, 
     storelevel  INT NOT NULL, 
     CONSTRAINT pkstore PRIMARY KEY(agencyname, storeno) 
  ); 

CREATE TABLE zip 
  ( 
     storezip   CHAR(5) NOT NULL, 
     storestate VARCHAR(40) NOT NULL, 
     storecity  VARCHAR(40) NOT NULL, 
     PRIMARY KEY(storezip) 
  ); 

CREATE TABLE onlinequote 
  ( 
     qid    INT NOT NULL, 
     icname VARCHAR(40) NOT NULL, 
     PRIMARY KEY(qid) 
  ); 

CREATE TABLE holds 
  ( 
     policyid INT NOT NULL, 
     phcid    INT NOT NULL, 
     serialno INT NOT NULL, 
     CONSTRAINT pkholds PRIMARY KEY(policyid, phcid) 
  ); 

CREATE TABLE payment 
  ( 
     sno      INT NOT NULL, 
     pdate    DATE NOT NULL, 
     policyid INT NOT NULL, 
     amount   DECIMAL(10, 2) NOT NULL, 
     CONSTRAINT pkpayment PRIMARY KEY(sno, pdate, policyid) 
  ); 

CREATE TABLE claim 
  ( 
     claimid   INT NOT NULL, 
     claimdate DATE NOT NULL, 
     clientid  INT NOT NULL, 
     policyid  INT NOT NULL, 
     PRIMARY KEY(claimid) 
  ); 

CREATE TABLE agency 
  ( 
     agencyname  VARCHAR(40) NOT NULL, 
     activesince DATE NOT NULL, 
     taxid       INT, 
     PRIMARY KEY(agencyname) 
  ); 

CREATE TABLE nononlinequote 
  ( 
     qid     INT NOT NULL, 
     agentno INT NOT NULL, 
     PRIMARY KEY(qid) 
  ); 

CREATE TABLE pholder 
  ( 
     phcid    INT NOT NULL, 
     serialno INT NOT NULL, 
     CONSTRAINT pkpholder PRIMARY KEY(phcid, serialno) 
  ); 

CREATE TABLE client 
  ( 
     cid      INT NOT NULL, 
     cname    VARCHAR(40), 
     cbdate   DATE NOT NULL, 
     caddress VARCHAR(80) NOT NULL, 
     cemail   VARCHAR (40), 
     PRIMARY KEY(cid) 
  ); 

CREATE TABLE clientphone 
  ( 
     cid    INT NOT NULL, 
     cphone VARCHAR(12) NOT NULL, 
     PRIMARY KEY(cid, cphone) 
  ); 

CREATE TABLE agent 
  ( 
     agentno    INT NOT NULL, 
     agencyname VARCHAR(40) NOT NULL, 
     storeno    INT NOT NULL, 
     ismanager  VARCHAR(10) NOT NULL, 
     PRIMARY KEY(agentno) 
  ); 

CREATE TABLE insurance 
  ( 
     icname  VARCHAR(40) NOT NULL, 
     isp     VARCHAR(40) NOT NULL, 
     icemail VARCHAR(40) NOT NULL, 
     website VARCHAR(40) NOT NULL, 
     icphone VARCHAR(12) NOT NULL, 
     PRIMARY KEY(icname) 
  ); 

CREATE TABLE insuredperson 
  ( 
     ipname VARCHAR(40) NOT NULL, 
     phcid  INT NOT NULL, 
     CONSTRAINT pkinsuredperson PRIMARY KEY(ipname, phcid) 
  ); 

CREATE TABLE insuranceproduct 
  ( 
     prodcode        INT NOT NULL, 
     coverage        DECIMAL(10, 2) NOT NULL, 
     limitdeductible DECIMAL (10, 2) NOT NULL, 
     icname          VARCHAR(40) NOT NULL, 
     prodtype        VARCHAR(40) NOT NULL, 
     CONSTRAINT pkinsuranceproduct PRIMARY KEY(prodcode, coverage, 
     limitdeductible, icname) 
  ); 

CREATE TABLE associatedwith 
  ( 
     icname     VARCHAR(40) NOT NULL, 
     agencyname VARCHAR(40) NOT NULL, 
     PRIMARY KEY(icname, agencyname) 
  ); 

CREATE TABLE claimprocessing 
  ( 
     claimid     INT NOT NULL, 
     adjusterssn VARCHAR(12) NOT NULL, 
     CONSTRAINT pkclaimprocessing PRIMARY KEY(claimid, adjusterssn) 
  ); 

CREATE TABLE relatedperson 
  ( 
     claimid   INT NOT NULL, 
     ipname    VARCHAR(40) NOT NULL, 
     phcid     INT NOT NULL, 
     isrelated VARCHAR(10) NOT NULL, 
     CONSTRAINT pkrelatedperson PRIMARY KEY(claimid, ipname, phcid) 
  ); 

CREATE TABLE finalreport 
  ( 
     reportno    INT NOT NULL, 
     reportdate  DATE NOT NULL, 
     claimid     INT NOT NULL, 
     description VARCHAR(40), 
     CONSTRAINT pkfinalreport PRIMARY KEY(reportno, reportdate, claimid) 
  ); 

CREATE TABLE sells 
  ( 
     prodcode        INT NOT NULL, 
     coverage        DECIMAL(10, 2) NOT NULL, 
     limitdeductible DECIMAL(10, 2) NOT NULL, 
     icname          VARCHAR(40) NOT NULL, 
     agencyname      VARCHAR(40) NOT NULL, 
     commissionrate  DECIMAL(4, 2) NOT NULL, 
     CONSTRAINT pksells PRIMARY KEY(prodcode, coverage, limitdeductible, icname, 
     agencyname) 
  ); 

CREATE TABLE employee 
  ( 
     ssn            VARCHAR(12), 
     fname          VARCHAR(40) NOT NULL, 
     minit          VARCHAR(1), 
     lname          VARCHAR(40) NOT NULL, 
     bdate          DATE NOT NULL, 
     salary         DECIMAL(10, 2) NOT NULL, 
     phone          VARCHAR(12), 
     icname         VARCHAR(40) NOT NULL, 
     adjusterflag   VARCHAR(10) NOT NULL, 
     actuaryflag    VARCHAR(10) NOT NULL, 
     accountantflag VARCHAR(10) NOT NULL, 
     erank          INT, 
     PRIMARY KEY(ssn) 
  ); 

CREATE TABLE refund 
  ( 
     refunddate    DATE NOT NULL, 
     reportno      INT NOT NULL, 
     reportdate    DATE NOT NULL, 
     claimid       INT NOT NULL, 
     amount        DECIMAL(10, 2) NOT NULL, 
     noofpeople    INT NOT NULL, 
     accountantssn VARCHAR(12) NOT NULL, 
     CONSTRAINT pkrefund PRIMARY KEY(refunddate, reportno, reportdate, claimid) 
  ); 

CREATE TABLE employeeaddress 
  ( 
     ssn     VARCHAR(12) NOT NULL, 
     address VARCHAR(80) NOT NULL, 
     PRIMARY KEY(ssn, address) 
  ); 

THE FOLLOWING ARE THE SQL QUERIES TO ADD CONSTRAINTS

ALTER TABLE quote 
  ADD CONSTRAINT fkquote FOREIGN KEY (policyid) REFERENCES policy(policyid) ON 
  DELETE CASCADE; 

ALTER TABLE discount 
  ADD CONSTRAINT fkdiscount1 FOREIGN KEY (discname) REFERENCES rate(discname) ON 
  DELETE CASCADE; 

ALTER TABLE discount 
  ADD CONSTRAINT fkdiscount2 FOREIGN KEY (qid) REFERENCES quote(qid) ON DELETE 
  CASCADE; 

ALTER TABLE store 
  ADD CONSTRAINT fkstore1 FOREIGN KEY (agencyname) REFERENCES agency(agencyname) 
  ON DELETE CASCADE; 

ALTER TABLE store 
  ADD CONSTRAINT fkstore2 FOREIGN KEY (storezip) REFERENCES zip(storezip) ON 
  DELETE CASCADE; 

ALTER TABLE onlinequote 
  ADD CONSTRAINT fkonlinequote1 FOREIGN KEY (qid) REFERENCES quote(qid) ON 
  DELETE CASCADE; 

ALTER TABLE onlinequote 
  ADD CONSTRAINT fkonlinequote2 FOREIGN KEY (icname) REFERENCES insurance(icname 
  ) ON DELETE CASCADE; 

ALTER TABLE holds 
  ADD CONSTRAINT fkholds1 FOREIGN KEY (policyid) REFERENCES policy(policyid) ON 
  DELETE CASCADE; 

ALTER TABLE holds 
  ADD CONSTRAINT fkholds2 FOREIGN KEY (phcid, serialno) REFERENCES pholder(phcid 
  , serialno) ON DELETE CASCADE; 

ALTER TABLE payment 
  ADD CONSTRAINT fkpayment FOREIGN KEY (policyid) REFERENCES policy(policyid) ON 
  DELETE CASCADE; 

ALTER TABLE claim 
  ADD CONSTRAINT fkclaim FOREIGN KEY (policyid) REFERENCES policy(policyid) ON 
  DELETE CASCADE; 

ALTER TABLE nononlinequote 
  ADD CONSTRAINT fknononlinequote1 FOREIGN KEY (qid) REFERENCES quote(qid) ON 
  DELETE CASCADE; 

ALTER TABLE nononlinequote 
  ADD CONSTRAINT fknononlinequote2 FOREIGN KEY (agentno) REFERENCES agent( 
  agentno) ON DELETE CASCADE; 

ALTER TABLE pholder 
  ADD CONSTRAINT fkpholder FOREIGN KEY (phcid) REFERENCES client(cid) ON DELETE 
  CASCADE; 

ALTER TABLE clientphone 
  ADD CONSTRAINT fkclientphone FOREIGN KEY (cid) REFERENCES client(cid) ON 
  DELETE CASCADE; 

ALTER TABLE agent 
  ADD CONSTRAINT fkagent1 FOREIGN KEY (agencyname, storeno) REFERENCES store( 
  agencyname, storeno) ON DELETE CASCADE; 

ALTER TABLE insuredperson 
  ADD CONSTRAINT fkinsuredperson FOREIGN KEY (phcid) REFERENCES client(cid) ON 
  DELETE CASCADE; 

ALTER TABLE insuranceproduct 
  ADD CONSTRAINT fkinsuranceproduct FOREIGN KEY (icname) REFERENCES insurance( 
  icname) ON DELETE CASCADE; 

ALTER TABLE associatedwith 
  ADD CONSTRAINT fkassociatedwith1 FOREIGN KEY (icname) REFERENCES insurance( 
  icname) ON DELETE CASCADE; 

ALTER TABLE associatedwith 
  ADD CONSTRAINT fkassociatedwith2 FOREIGN KEY (agencyname) REFERENCES agency( 
  agencyname) ON DELETE CASCADE; 

ALTER TABLE claimprocessing 
  ADD CONSTRAINT fkclaimprocessing1 FOREIGN KEY (adjusterssn) REFERENCES 
  employee(ssn) ON DELETE CASCADE; 

ALTER TABLE claimprocessing 
  ADD CONSTRAINT fkclaimprocessing2 FOREIGN KEY (claimid) REFERENCES claim( 
  claimid) ON DELETE CASCADE; 

ALTER TABLE relatedperson 
  ADD CONSTRAINT fkrelatedperson1 FOREIGN KEY (claimid) REFERENCES claim(claimid 
  ) ON DELETE CASCADE; 

ALTER TABLE relatedperson 
  ADD CONSTRAINT fkrelatedperson2 FOREIGN KEY (ipname, phcid) REFERENCES 
  insuredperson(ipname, phcid) ON DELETE CASCADE; 

ALTER TABLE finalreport 
  ADD CONSTRAINT fkfinalreport FOREIGN KEY (claimid) REFERENCES claim(claimid) 
  ON DELETE CASCADE; 

ALTER TABLE sells 
  ADD CONSTRAINT fksells1 FOREIGN KEY (prodcode, coverage, limitdeductible, 
  icname) REFERENCES insuranceproduct(prodcode, coverage, limitdeductible, 
  icname) ON DELETE CASCADE; 

ALTER TABLE sells 
  ADD CONSTRAINT fksells5 FOREIGN KEY (agencyname) REFERENCES agency(agencyname) 
  ON DELETE CASCADE; 

ALTER TABLE employee 
  ADD CONSTRAINT fkemployee FOREIGN KEY (icname) REFERENCES insurance(icname) ON 
  DELETE CASCADE; 

ALTER TABLE refund 
  ADD CONSTRAINT fkrefund1 FOREIGN KEY (reportno, reportdate, claimid) 
  REFERENCES finalreport(reportno, reportdate, claimid) ON DELETE CASCADE; 

ALTER TABLE refund 
  ADD CONSTRAINT fkrefund4 FOREIGN KEY (accountantssn) REFERENCES employee(ssn) 
  ON DELETE CASCADE; 

ALTER TABLE employeeaddress 
  ADD CONSTRAINT fkemployeeaddress FOREIGN KEY (ssn) REFERENCES employee(ssn) ON 
  DELETE CASCADE;

ALTER TABLE agency 
  ADD CONSTRAINT taxidcheck CHECK(taxid>=1 AND taxid<=500000) 

ALTER TABLE store 
  ADD CONSTRAINT storelevelcheck CHECK(taxid>=1 AND taxid<=5) 

ALTER TABLE employee 
  ADD CONSTRAINT employeeage CHECK(((bdate))>='2-MAY-1948') 

4. The following are the SQL queries to Create the Views.

--VIEW 1/////////////////////////////////////////////// 
CREATE VIEW prod_quotes 
AS 
  SELECT * 
  FROM   ((SELECT DISTINCT i.icname, 
                           i.prodcode, 
                           i.prodtype, 
                           i.limitdeductible, 
                           i.coverage 
           FROM   insuranceproduct I, 
                  onlinequote O, 
                  quote Q 
           WHERE  I.icname = O.icname 
                  AND O.qid = Q.qid) 
          UNION 
          (SELECT DISTINCT i.icname, 
                           i.prodcode, 
                           i.prodtype, 
                           i.limitdeductible, 
                           i.coverage 
           FROM   insuranceproduct I, 
                  nononlinequote N, 
                  quote Q, 
                  agent A, 
                  store S, 
                  sells SL 
           WHERE  N.qid = Q.qid 
                  AND N.agentno = A.agentno 
                  AND A.storeno = S.storeno 
                  AND S.agencyname = SL.agencyname 
                  AND SL.icname = I.icname)) 
  ORDER  BY icname, 
            prodcode, 
            prodtype, 
            limitdeductible, 
            coverage; 

--VIEW 2/////////////////////////////////////////////// 
CREATE VIEW store500 
AS 
  SELECT DISTINCT agencyname, 
                  storeno, 
                  storename, 
                  storestreet, 
                  storezip, 
                  storephone, 
                  storelevel 
  FROM   (((quote 
            NATURAL join policy) 
           NATURAL join nononlinequote) 
          NATURAL join agent) 
         NATURAL join store 
  WHERE  finalpremium >= 500; 

--VIEW 3/////////////////////////////////////////////// 
CREATE VIEW policyrefund2010 
AS 
  SELECT DISTINCT policydate, 
                  refunddate, 
                  policyid, 
                  finalpremium, 
                  policyterms 
  FROM   ((policy 
           NATURAL join claim) 
          NATURAL join refund) 
  WHERE  refunddate LIKE '%-10%'; 

--VIEW 4 /////////////////////////////////////////////// 
CREATE VIEW onlineclient 
AS 
  SELECT DISTINCT qtype, 
                  cid, 
                  cname, 
                  cbdate, 
                  cemail, 
                  caddress 
  FROM   client 
         join quote 
           ON client.cid = quote.clientid 
  WHERE  qtype IN( 'ONLINE' );