CQ Insurance DB

Introduction

Students must complete all the following parts of this assessment task:

• SQL Queries
• Form
• Report

Important

Download the Microsoft Access database, CQInsurance.accdb, from the unit website and use it to complete this assessment task.

If you get a security warning message when opening the downloaded database, then you need to open/click the options and then choose the option "enable this content".

Do not modify the structures of the tables and relationships. Do not create additional tables. It is not recommended to delete any of the existing records from the downloaded database (hereafter referred as 'your database').

You must create SQL queries in your database and make sure those queries produce the desired results as shown in Part A. Save and name those queries suitably such as Q1, Q2, ......, Q10 respective to the question that you are answering.

You also need to create a MS Access-form and a MS Access-report that are related to Part B and Part C and save them in your database.

CQ Insurance database

The database, CQInsurance.accdb, contains the following tables:

• Person - This table lists prospective customers including those who have not yet requested for a policy.
• Property - This table lists the properties/homes for which the policy coverage is done. The PersonID refers to the person who owns the property. It is assumed that each property is owned by one person only. One person can own many properties. One property can have at most one policy only.
• Policy - This table lists home content insurance policies by CQInsurance. Each policy is for one property only. Each policy can have many ContentItems. The policy coverage period is assumed to be a year from the "effective from date".
• ContentGroup - This table lists the current insurance premium rate for each group of content items.
• ContentItem - This table lists content items that are covered by the policies. Each policy can have/cover many ContentItems. Each ContentItem, within a policy, should belong to one of the listed ContentGroup.

Part A - SQL Queries

Using your database, write SQL queries to answer all questions in this part. Each of the following questions has an information request followed by the expected results when your database has the given sample data.

Please note:

• Where a column has been renamed in the expected result, you must reproduce that name in your results. For example, if PolicyID appears as PolicyNbr in the expected result, then you must make this column name appear as PolcyNbr in your output.
• Simple queries are preferred. You may lose marks if your query is overly complex.
• You are not required to enforce row order on your queries unless specified.
• Be careful of duplicates; remove duplicates if necessary.
• You are not required to use one type of clause or construct (e.g. you are not required to use an explicit join over an implicit join) unless specifically instructed to do so in the question.
• Your SQL must continue to produce the correct answer even if the data changes.

Questions:

1.List all persons who are having policies. Display the person id and person name in ascending order of person name.

Expected result using sample data:

2.How many policies that each person is holding/having? List their id, name and number of policies in descending order of person name.

Expected result using sample data:

3.List the person Id and person name of all persons who have more than two policies. Expected result using sample data:

4.List the person name and email Id of those persons whose name contains the word 'Smith'. Expected result using sample data:

5.List the Person Id and person name of the persons who do not have any policy.

Expected result using sample data:

6.List the yearly premium amount for all the policies. Note: Yearly premium of a policy is calculated based on its content items' insured amount and their respective premium rate percent.

Expected result using sample data:

7.List all the policies that do not cover jewels.

Expected result using sample data:

8.What is the total amount covered by each policy? List the policy Id and total coverage amount in ascending order of policy Id. Note: A policy's coverage amount is equal to the sum of insured amount of all the content items in that policy.

Expected result using sample data:

9.Which policies are having the highest policy coverage amount? Note: A policy's coverage amount is equal to the sum of insured amount of all the content items in that policy.

Expected result using sample data:

10.List the person Id and person name who hold(s) the highest insured amount of painting that has been covered in any of the policies.

Expected result using sample data:

Part B - Form

Using the CQInsurance database, develop a form that can be used for data entry for Policy. You are allowed to use any number of tables and any suitable layout for developing that form. Name the form as Policy Entry.

Part C - Report

Using CQInsurance database, create a report that lists policies and all content items covered by the policy. Your report should

• include a title, date, column headings and page numbering
• have column headings/names named appropriately
• show the appropriate columns and data that are meaningful to the user.