Web Application Technologies - Security Interview Preparation Notes

HTTP Protocol

• was originally meant for retrieving static text based resources
• has been extended in various ways to support complex applications
• is a REQUEST-RESPONSE protocol
• is a STATELESS protocol
• uses STATEFUL TCP protocol for the transport mechanism, but HTTP itself is STATELESS

HTTP Messages (Both request & Response)

Both Requests and Responses have 

• One or more headers ( each on a separate line)
• A MANDATORY blank line (CRLF)
• An OPTIONAL body.

HTTP Requests

First line of HTTP request always contains the following three items , separated by spaces

1. Method - GET - GET does not allow a message body, since it is used for requests

                 - POST
2. Path & query string - path is the location of the resource 
                                       - query string is to pass parameters to the resource 
                                       - query string is followed by "?"
3. Protocol - HTTP 1.0
                    - HTTP 1.1 - In this version the "HOST" header is mandatory

Other Headers in a Request

1. Referer - URL from which request originated 

                  - the header was misspelled originally and has retained the wrong spelling

2. User-Agent -info about the software that generated the request

                         -MOZILLA is used as a prefix for historical reasons, to assert that the requesting                                   browser is compatible with the "Netscape" standard.

3. HOST  - specifies the hostname as the "path" in first line does not usually contain the hostname

HTTP Response

                               

First line of HTTP response always contains the following three items, separated by spaces

1. HTTP Version  -  HTTP 1.0

                               - HTTP 1.1

2. Status code - 200  (numeric code)

3. Status Description - OK ( textual description of Status code)

Other Interesting Headers in HTTP Response

1. Server  - Info about the Web Server and installed modules and OS

                 - Info may not be accurate

2. Set-Cookie - a further cookie , that is, a cookie to be included in future requests

3. Pragma - a response header

                  - directs not to store the response in cache

4. Expires - to indicates that the response content expiry date

                  - a date in the past means response should not be cached

What is the difference between "No-Cache" & "No-Store"

No-Cache 

Response is stored in the cache, but the response should not be reused for subsequent requests without first re-validation with the server using 

1. if-Modified-Since,
2. If-Unmodified-Since,
3. If-Match,
4. If-None-Match . 

No-Store 

• Instructs to make a best effort not to write the response to disk (i.e not to cache it). 
• Provides no guarantee that it will not be written to disk. 
• If sent in a request, a cache MUST NOT store any part of either this request or any response to it.
• If sent in a response, a cache MUST NOT store any part of either this response or the request that elicited it. This directive applies to both non-shared and shared caches. 

Provides no guarantee that it will not be written to disk.

Reason is - the HTTP/1.1 definition makes a distinction between history stores and caches. 

If the user navigates back to a previous page a browser may still show you a page that has been stored on disk in the history store

What is the difference between Pragma & Cache-Control?

• The "Pragma" header field allows backwards compatibility with HTTP/1.0 caches.
• Cache-Control was not defined until HTTP/1.1
• HTTP /1.0 clients do not understand "Cache-Control" header
• When the Cache-Control header field is also present and understood in a request, Pragma is ignored.
• Cache-Control header takes precedence in case of HTTP/1.1 ==> Pragma is ignored when both "Cache-Control" and "Pragma" are present.
• When the "Cache-Control" header field is not present in a request, caches MUST consider the "No-cache" request pragma-directive as having the same effect as if "Cache-Control: no-cache" were present.
• When sending a no-cache request, a client ought to include both the Pragma and cache-control directives in order to target both HTTP 1.0 and HTTP 1.1 caches

Example
 GET / HTTP/1.1
 Host: www.example.com
 Cache-Control: max-age=30
 Pragma: no-cache

This will constrain HTTP/1.1 caches to serve a response no older than 30 seconds, while precluding HTTP /1.0 clients from serving a cached response.

 What is the Difference between GET & POST methods?

 GET Method

• Designed to retrieve resources
• Can be used to send parameters in the query string of the URL
• GET requests can be bookmarked and  reused later.
• "GET-Request-URL" is displayed on screen
• GET requests get logged in browser history & Web Server's access log
• GET requests can be cached, if "no-store" directive is not present.
•  "GET-Request-URL" is also sent to other sites in "Referer" header.
• GET Requests should NOT BE USED to transmit sensitive information.
• The semantics of the GET method change to a "conditional GET" if the request message includes an If-Modified-Since, If-Unmodified-Since, If-Match, If-None-Match
• GET Request has Length Restrictions ( 2048 characters)
• GET Request does not have a BODY
• Only ASCII encoding is allowed ( Encoding type: application/x-www-form-urlencoded)
• Hitting "Back Button" will simply resubmit the GET Request

POST Method

• Designed to perform "Actions".
• Parameters can be submitted using both the URL Query String as well as using Message  Body Parameters submitted using the Message Body ( not the Query String) will not be logged on Server or in Browser History.
• Hitting Back button will alert the user that the Data will be resubmitted and ask for confirmation.
• POST Request cannot be bookmarked (POST request URL can still be Bookmarked)
• No restrictions on type of data, even Binary is allowed (multipart/form-data)
• No length restrictions

Other HTTP Methods

HEAD

•  Returns the HEAD of the Response to corresponding GET Request
• Aim is to check if the resource exists on the server
• Server's response = (Response to a GET request)  MINUS (The body)
• Server returns the same headers as it would for a GET Request

TRACE

•  Used for diagnostics
•  Server returns the exact "REQUEST" message it received.
•  Used to detect the effect of Proxy servers that manipulate the request

OPTIONS

• Returns the list of Methods supported for a particular Resource
• Response contains "Allow" Header
• Eg. Allow : GET, POST , PUT, DELETE, OPTIONS

 PUT

•  Attempts to upload the specified resource to the server
•  Content to be uploaded is in the Request Body
•  Can be used to attack the Application.

URLs

• scheme://domain:port/path?query_string#fragment_id
• SCHEME is mostly the protocol ( but not always)
• PORT is OPTIONAL
• PORT is used when it differs from the Default Port used by the Protocol
• PATH is specified using /a/b/c/ and is OPTIONAL
• QUERY STRING starts with "?"
• QUERY STRING contains "name=value" pairs separated by Ampersand (&)
• FRAGMENT ID starts with "#", but "#" is not a part of the Fragment ID
• FRAGMENT ID identifies a portion of the document
• FRAGMENT ID is not sent to the server along with the GET Request
• FRAGMENT ID is processed fully on the client side

REST - Representational State Transfer

• A style of Architecture meant for distributed systems
• In REST "Requests & Responses" both contain "Representation of the State" of the system (Example: HTTP)
• World wide web (HTTP Protocol, URL format) conforms to REST Architecture
• REST- URL is a UNIQUE identifier for the resource being accessed.
• The HTTP Methods (GET, POST, PUT, DELETE) describe the operations to be performed on the resource addressed by the Unique URL.

REST-style URL

• URL containing its parameters within the URL file path rather than the Query String
• http://example.com/search?os=android&model=nexus4
• [Above is a Normal URL, though it is still conforms to REST]
• http://example.com/search/android/nexus4
• [REST style URL]

HTTP Headers

CONNECTION

•  Whether to keep the TCP connection open or close it

CONTENT ENCODING

•  Type of encoding (Gzip) used in to compress the Response Body

CONTENT LENGTH

•  Length of Message Body.
•  In case of "HEAD" Method, Content Length tells the length of corresponding GET Method

CONTENT TYPE

•  Type of content contained in message body (text/html)

TRANSFER ENCODING

•  Chunked
•  Transfer-Encoding HTTP header in used in place of the Content-Length header
• Senders can begin transmitting dynamically-generated content before knowing the total size of that content.

 ACCEPT

•  Types of CONTENT (image, docs) the client is willing to accept

ACCEPT ENCODING

•  Types of content encoding the client is willing to accept.

 AUTHORIZATION

•  Used to submit the credentials for one of the HTTP Authentication types

HOST

•  Specifies the name of the host that appears in URL being requested

IF-MODIFIED-SINCE

•  To check if the resource has changed since the last request, if not, then server may allow use of Cached copy.
• Response code 304 will mean => Use the cached copy

IF-NONE-MATCH

• Specifies an Entity tag ( a string identifier)
• Client submits the Etag along with request
• Etag is used to identify the version of resource.
• If client "Etag" from client matches current version => Response code 304
• Response code 304 will mean => Use the cached copy

ORIGIN Header ( Used in Cross Origin Requests)

• Used in cross domain requests to indicate the domain (only the domain name) from which request originated

Referer Header

• Gives the complete URL from which request originated

Response Headers

Access-Control-Allow-Origin

• Part of HTTP CORS (Cross site request Standard)
• Indicates the sites from which a resource can be accessed via XMLHTTPRequest (AJAX)
• Earlier, HTTP Requests generated by Scripts could only be sent to the origin domain.

Cache Control 

• Passes caching directives

See above for difference between "Cache control" and "Pragma"

Etag

• Specifies an entity Tag (is received in a Response)
• Is used to track the version of the resource
• Is sent back to server using "If-None-Match" header
• If Etag of a resource is the same, then a cached copy can be used

Expires

• Server the duration for which the resource can be cached, using this header.

Location

• Used in Redirection Responses (Responses with 3xx Status code)
• Identifies the location of the Redirect

WWW-Authenticate

• Sent along with a 401 (Not authorized) response
• Provides the details of types of authentication the server supports
• Client sends the authorization values using "Authentication" Header

X-Frame-Options

• Used to protect against ClickJacking
• Declares a policy (communicated from server to client browser)
• Declares whether the client browser may display that response in an <IFRAME> which is part of other web-pages
• Values: DENY, SAMEORIGIN, ALLOW-FROM

Cookies

• Are Data Values stored on client, by the server
• Are sent to server along with every request
• Can be used to exploit vulnerabilities
• Are name-value pairs, the value of cookie, does not contain "spaces"
• Cookie is set using "Set-Cookie" Header
• Multiple cookies require multiple "Set-Cookie" Headers

"Set Cookie Header" can have following OPTIONAL ATTRIBUTES

• Expires: The date until which cookie is valid. 
• Expires: Future Expiry => cookie is stored in persistent storage, and is reused later browser sessions,
• Expires: If not set => the cookie is only valid for current browser session.
• Domain: The domain for which cookie is valid
• Domain: Must be same as or subdomain of parent domain
• Path: URL Path for which cookie is valid
• Secure: Cookie is transmitted over HTTPS Only
• HTTPONLY: Cookie cannot be accessed by Client Side Javascript (For XSS Prevention)

HSTS : HTTP STRICT TRANSPORT SECURITY

• An opt-in Security Enhancement 
• Specified by a web application using "Strict-Transport-Security" Response Header
• Once this Header is received, then all future communication is sent on HTTPS
• Strict-Transport-Security: max-age=31536000; includeSubDomains
• Strict-Transport-Security: max-age=31536000; includeSubDomains; preload
• Max-Age: applicable for duration
• includeSubDomains: all subdomains should be served on HTTPS
• preload: Site owners can sign-up to get their domain on HSTS Preload list maintained by Major browsers

Status Codes

1xx : Informational

2xx: Successful ( 200 OK)

3xx: Redirection related 

4xx: Error of some Kind

5xx: Server Error of some kind

100 CONTINUE

• Initial Request: Client sends Header - "Expect: 100-continue" , To determine if server is willing to accept a request (based on the request headers) before the client sends the body, to check the legitimacy of the Request (Example : POST)
• Server Responds with 100 CONTINUE or else 417 EXPECTATION FAILED

200 OK

• Request is Successful

301 Moved Permanently ( Redirect Related)

• Redirect THIS and ALL FUTURE requests to a URL specified by the "Location Header" in Response

302 Found (Redirect Related)

• Temporary Redirect to a URL Specified in "Location Header"

304 Not Modified ( Redirect Related)

• Instructs to use the cached copy stored on client

400 Bad Request

• Invalid Request (Probably malformed)

401 Unauthorized 

• Server requires HTTP Authentication
• Server response will contain - "WWW-Authenticate header"
• "WWW-Authenticate" Header will give details of supported authentication types

403 Forbidden

• No one is allowed to access this resource, regardless of authentication

404 Not Found

• Resource does not exist

405 Method Not Allowed

• Method Used (Eg. PUT, DELETE) is not supported by specified URL

413 Request Entity is Too Large

• The "Request" sent is too large to Handle

414 Request URI Too Long

• Request URL is too long

500 Internal Server Error

• Server Encountered an error
• May be because of an unexpected Input caused unhandled error
• See server's full response for details

503 Service Unavailable

• Server is working, but the backend application is not working 
• Try to identify if one of your actions caused this.

What is HTTPS? - HTTP Secure

• HTTP uses TCP as its transport mechanism
• HTTP is unencrypted and can be intercepted
• HTTPS is essentially HTTP tunneled over SSL.
• SSL protects Confidentiality & Integrity of underlying HTTP Traffic
• HTTPS Prevents eavesdropping & MITM Attacks
• HTTPS also provides Authentication of the Website & WebServer
• A site must be Entirely hosted over HTTPS
• Partially loading some of the content (Scripts & CSS) on HTTP, makes the user vulnerable to attacks.
• Cookies on an HTTPS site should have "Secure" Flag Set
• SSL uses long-term public and secret keys to generate a short term session key to encrypt the data flow between client and server

A in CIA = Availability ( not Authentication)

HTTP Authentication

HTTP Protocol has its own mechanism to authenticate users. This is mostly used over Intranet only

Basic

• credentials are sent in base64 encoded string
• credentials are sent with each request
• Should only be used over HTTPS

NTLM

• Uses a challenge/response mechanism to avoid password capture or replay attacks

Digest

• credentials are sent as MD5 hash 
• HASH is calculated using a NONCE & Password
• NONCE is used to avoid replay attacks

Web Application Technologies

• Web Applications use multiple technologies to delivery their functionality.
• To be able to attack a Web application, understand how various components work together.
• Understand the weakness of each component

 Server Side Functionality

• Server serves both static & dynamic resources.
• Dynamic resources - Generated by scripts,
• Scripts - take input and give an output
• Input is in the form of parameters
• Parameters allows the scripts to tailor the output to the individual user.

Parameters are sent to the application in following ways 

• URL Query String
• REST-Style URL's file path
• HTTP Cookies
• Request Body (POST method)

Additionally

• Any part of HTTP Request can be used as an Input for processing Eg - User-Agent 

Technologies Used on Server Side

• Scripting Languages ( PHP, Perl )
• Application Platform (Java, ASP.NET)
• Web Server - IIS, Apache
• Database - MYSQL, MS-SQL, Oracle
• Other Backend Components - SOAP-based Web Service, Directory Service

Source of many web vulnerabilities

1. Application Design
2. Third party packages

Some commonly Used Technologies

1. Java Platform ( Application Platform)

WAS de-facto for large scale enterprise applications

Long history & widely adopted = > Many high quality dev tools, app servers & frameworks available

Can be run on several OSes

Important Terms

Enterprise Java Bean ( EJB ) 

• A heavyweight software component 
• Encapsulates a business function or logic
• Intended to handle concerns like - persistence, transactional Integrity & Security

Plain Old Java Object (POJO)

• An ordinary Java Object
• POJO refers to User defined Objects
• Much simpler than EJB
• Lightweight compared to EJB

Java Servlet

• An Object that resides on an application Server
• Processes HTTP Requests & HTTP Responses

WEB Container

• An engine which provides run-time environment for Java-based web applications
• Example : Apache Tomcat, WebLogic, JBoss

Java is well suited for modular approach. Many open source, third party components exist to help reduce development time

Examples

• Authentication Module - JAAS, ACEGI
• Presentation Layer - SiteMesh, Tapestry 
• Database Object Relational Mapping - Hibernate 
• Logging - Log4J

To attack: Identify the component being used, download it and try to find a vulnerability in the component and use it to attack the application.

2. PHP

• Personal Home Page
• Highly powerful and rich framework for web applications
• Used in conjugation with other technologies
• Ex: LAMP ( Linux, Apache, My SQL, PHP)

Many Open Source components exist for

• Bulletin Boards: PHPBB, PHP-Nuke
• Admin Front Ends: PHPMyAdmin
• Webmail: SquirrelMail, IlohaMail
• Photo galleries: Gallery
• Shopping Carts: osCommerce, ECW-Shop
• Wikis- MediaWiki, WakkaWikki

Design & default config of PHP framework makes it easy to unintentionally introduce security bugs

PHP Platform itself have several bugs

These bugs can be used to attack PHP Application

3. XML (Extensible Markup Language)

• Specification to encode "DATA" in both human & Machine readable form.
• XML document contains - DATA & MARKUP
• Extensible means - XML allows arbitrary tags & attribute names
• Data Type Definition (DTD) - Information contained in XML Document, which describes the Tags and attributes used in the XML Document.
• XML is used extensively in web applications

Web Services

• Web application Vulnerabilities are equally applicable to Web Services
• A web service is a "method of communication" which allows two software systems to exchange DATA over Internet
• Service Requester - requests the data
• Service Provider - processes request & provides the data
• Service provider & Requester often use different programming language
• Web Service is thus a "Method to exchange Data".
• Web Service uses two methods to exchange data

1. SOAP (Simple Object Access Protocol)
2. REST (Representational State Transfer)

• SOAP uses HTTP protocol to transmit messages
• SOAP uses XML to represent the data  (Content-Type: application / soap+xml)
• Using user-supplied-data in SOAP messages can lead to vulnerabilities.
• Directly exposed web-services are worthy of vulnerability examination
• WSDL (Web Services Description Language) file describes the following

-How to call the web service
-What parameters it expects
-What data structure it returns

• Tools for testing WEB Service Testing - SoapUI

Web Service ( on the Left )

SOAP Request (on the Right)

What is the difference between SOAP Web Service & REST Web Service ? ( Interview Question)

RESTful Web Services

• is faster compared to SOAP Web Services
• using REST for a Web Service is a recent trend
• does NOT NECESSARILY have to use XML 
• Easy & Lightweight to use RESTful Web Service
• RESTful Web Service is STATELESS
• provides CACHING over HTTP GET method ( improves efficiency)
• has no standard set of Rules to describe the Web Service Interface.
• Client should already know what to expect from the web-service
• Resource is accessed using a UNIQUE URL
• Easy to Integrate with Existing websites
• has a small learning curve compared to REST

SOAP based Web Services

• uses XML to fetch the DATA.
• provides WSDL to specify the interface of the web-service
• allows a mechanism to describe the web-service and advertise its existence.
• useful for complex operations and those which require maintenance of STATE.
• SOAP supports multiple protocols ( REST mostly uses HTTP)
• SOAP has a bigger learning curve

Client Side Functionality ( Small Notes )

Document Object Model (DOM)

• A programming interface for HTML documents.
• Can be used to QUERY the HTML document & MODIFY it through API.
• can access HTML elements by ID.
• can traverse the HTML document.
• can be used to Read & Update Cookies.
• is Manipulated by AJAX to dynamically updated the page.

AJAX ( Asynchronous Javascript and XML)

• used to avoid full page reload
• performs an "in the background" HTTP Request & updates the page
• "XMLHttpRequest" object is used to make AJAX requests
• Allows arbitrary content to be sent ( not only XML)
• uses Synchronous method when it makes sense to make the user wait for the response
• increases the attack surface & has historically introduced some vulnerabilities.

What is Same Origin Policy ?

• Security policy implemented by browsers
• DISALLOWS content coming from one origin from accessing content coming from another origin.
• defines ORIGIN as = Protocol + host + port ( all three should match)
• -- A subdomain is considered a different host

Subtle Points of Same origin policy

1. A page on domain A can send a request to domain B, but cannot process the response within the context of domain A.
2. page from domain A can load a script from domain B and execute it within the context of domain B 
3. page on domain A cannot modify the cookies or other DOM data of domain B

Encoding Schemes

URL Encoding

• To safely transport characters over HTTP
• URLs can have only printable ASCII characters
• Prefix % followed by "ASCII code expressed in Hexadecimal" 
• %25   =  %
• %0D = Carriage Return
• %0A = New Line
• = Null Byte
• +      =  Space 
• %20 = Space

To attack: URL encode "space"  ? % & ; + #  if you submit them as data

Unicode Encoding

• Supports all the world's writing systems
• it  is a 16 bit encoding scheme
• Prefix is "%u" Suffix is hexadecimal of the character "Unicode"

UTF-8 

• Variable length encoding
• uses one or more bytes to represent each character
• each byte of the UTF-8 is encoded and Prefixed with %
• Eg:   %e2%89%a0 is a single character

HTML Encoding

• to represent "metacharacters" as "content" within HTML document
• <    &lt 
• >    &gt
• Any character can be HTML encoded as follows
• &#65   (Notice, the additional #)

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' );