COIT20247 Assessment 2: Database Design, SQL Queries, and Reports

This assignment solution for COIT20247, covers the design and implementation of a database system. It begins with an Entity-Relationship Diagram (ERD) that visually represents the database structure, followed by the relational schema defining tables, attributes, and primary/foreign keys. The solution then addresses normalization, ensuring data integrity and minimizing redundancy through functional dependency analysis. DDL (Data Definition Language) statements are provided to create the database tables, while DML (Data Manipulation Language) statements demonstrate data insertion and retrieval. The assignment includes SQL queries to extract specific information and create reports. An implementation report details the learning process, challenges, and design choices. References to relevant resources are also included. The database design incorporates integrity constraints to enforce data accuracy and consistency, such as validation rules, data types, and required fields. The solution demonstrates a practical understanding of database design principles, SQL implementation, and report generation.

Assessment 2
COIT20247
Student Name
Student ID

Contents
Sl. No Title Page No.
1 ERD 2
2 Relational Schema & Integrity Constraints 3
3 Normalization (Functional Dependency) 11
4 DDL and DML Statements 13
5 Query and Report 21
6 Implementation Report 23
References 24
1

ERD
2

Relational Schema
Location (Location_Id, Street, City, Post_Code)
Primary Key (Location_Id)
Person_Type (Person_Type_Id, Person_Type_Name)
Primary Key (Person_Type_Id)
Qualification (Qualification_Id, Qualification_Name)
Primary Key (Qualification_Id)
Report_Type (Report_Type_Id, Report_Type_Name)
Primary Key (Report_Type_Id)
Person (Person_Id, Person_Name, Location_Id, Person_Type_Id)
Primary Key (Person_Id),
Foreign Key (Location_Id) References Location (Location_Id),
Foreign Key (Person_Type_Id) References Person_Type (Person_Type_Id)
Assessor (Person_Id)
Primary Key (Person_Id),
Foreign Key (Person_Id) References Person (Person_Id)
Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified)
Primary Key (Person_Id, Qualification_Id),
Foreign Key (Person_Id) References Person (Person_Id),
Foreign Key (Qualification_Id) References Qualification (Qualification_Id)
3

Property (Property_Id, Location_Id, Person_Id)
Primary Key (Property_Id),
Foreign Key (Location_Id) References Location (Location_Id),
Foreign Key (Person_Id) References Person (Person_Id)
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date, Is_Home_BuildingPolicy,
Is_Home_ContentsPolicy, Property_Id)
Primary Key (Person_Id),
Foreign Key (Property_Id) References Property (Property_Id)
Home_Content (Policy_Id, Content_Premimum_Amount)
Primary Key (Policy_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id)
House_Type (House_Type_Id, House_Type_Name)
Primary Key (House_Type_Id)
Home_Building (Policy_Id, House_Type_Id, Year_Built, Is_Alarm_Fitted, Has_Window_Locks,
Issued_Amount, Building_Premium_Amount)
Primary Key (Policy_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id),
Foreign Key (House_Type_Id) References House_Type (House_Type_Id)
Policy_Status (Policy_Status_Id, Policy_Status_Name)
Primary Key (Policy_Status_Id)
4

Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id)
Primary Key (Claim_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id),
Foreign Key (Policy_Status_Id) References Policy_Status (Policy_Status_Id)
Settled_Claim(Claim_Id, Settled_Date, Amount_Settled)
Primary Key (Claim_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id)
Rejected_Claim (Claim_Id, Rejected_Date, Rejected_Reason)
Primary Key (Claim_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id)
Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date, Quantity,
Claimed_Status, Policy_Id)
Primary Key (Item_Id),
Foreign Key (Policy_Id) References Home_Content (Policy_Id)
Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status)
Primary Key (Claim_Id, Item_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id),
Foreign Key (Item_Id) References Content_Item (Item_Id)
Assessment_Report (Report_Id, Report_Date, Initial_Recommendation, Person_Id, Claim_Id,
Report_Type_Id)
Primary Key (Report_Id),
Foreign Key (Person_Id) References Assessor (Person_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id),
Foreign Key (Report_Type_Id) References Report_Type (Report_Type_Id)
5

Final_Assessment_Report (Report_Id, Final_Assessment_Date, Total_Amount,
Cost_Report_Demo)
Primary Key (Report_Id),
Foreign Key (Report_Id) References Assessment_Report (Report_Id)
6

Integrity Constraints
Insured amount should be positive currency and expressed in 2 decimals only
Set Validation Rule to >=0 and Validation Rule to Premium Amount Should be greater than or
equal to zero and Data Type to Currency and Decimal Places to 2
7

Policy date should have appropriate date value
Set the format to Short Date
8

Name of persons such as customer and assessor should not be null
Set Required to Yes and Allow zero length to No
9

Each policy should be related to one property/home
From ERD, Each policy belong to any one of the property
10

Functional Dependencies
Relationship 1
INF
In Policy table,
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date, Is_Home_BuildingPolicy,
Is_Home_ContentsPolicy, Property_Id, Content_Premium_Amount)
Policy_Id is the primary key. It is used to prevent the repeated group of data in the table
2NF
Remove partial dependency
In Policy table, Property_Id is the foreign key. It avoids partial dependencies in the table
3NF
Removed transitive dependency
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id)
Home_Content (Policy_Id, Content_Premium_Amount)
11

Relationship 2
INF
In Person table,
Person (Person_Id, Person_Name, Location_Id, Person_Type_Id, Person_Type_Name)
Location (Location_Id, Street, City, Post_Code)
Person_Id, Location_id is the primary key. It is used to prevent the repeated group of data in the table
2NF
Remove partial dependency
In Person table, Location_Id is the foreign key. It avoids partial dependencies in the table
3NF
Removed transitive dependency
Person (Person_Id, Person_Name, Location_Id, Person_Type_Id)
Location (Location_Id, Street, City, Post_Code)
Person_Type (Person_Type_Id, Person_Type_Name)
12

DDL Statements:
CREATE TABLE Location (Location_Id INT, Street VARCHAR(60) NOT NULL, City VARCHAR(25),
Post_Code INT NOT NULL, Primary Key (Location_Id));
CREATE TABLE Person_Type (Person_Type_Id VARCHAR(1), Person_Type_Name VARCHAR(25)
NOT NULL, Primary Key (Person_Type_Id));
CREATE TABLE Qualification (Qualification_Id VARCHAR(5), Qualification_Name VARCHAR(25)
NOT NULL, Primary Key (Qualification_Id));
CREATE TABLE Report_Type (Report_Type_Id VARCHAR(1), Report_Type_Name VARCHAR(25)
NOT NULL, Primary Key (Report_Type_Id));
CREATE TABLE Person (Person_Id VARCHAR(6), Person_Name VARCHAR(45) NOT NULL,
Location_Id INT NOT NULL, Person_Type_Id VARCHAR(1)NOT NULL, Primary Key (Person_Id),
Foreign Key (Location_Id) References Location (Location_Id), Foreign Key
(Person_Type_Id) References Person_Type (Person_Type_Id));
CREATE TABLE Assessor (Person_Id VARCHAR(6), Primary Key (Person_Id), Foreign Key
(Person_Id) References Person (Person_Id));
CREATE TABLE Ass_Qualification (Person_Id VARCHAR(6), Qualification_Id VARCHAR(5),
Date_Qualified Date NOT NULL, Primary Key (Person_Id, Qualification_Id), Foreign Key
(Person_Id) References Person (Person_Id), Foreign Key (Qualification_Id) References
Qualification (Qualification_Id));
CREATE TABLE Property (Property_Id INT, Location_Id INT NOT NULL, Person_Id VARCHAR(6)
NOT NULL, Primary Key (Property_Id), Foreign Key (Location_Id) References Location
(Location_Id), Foreign Key (Person_Id) References Person (Person_Id));
CREATE TABLE Policy (Policy_Id VARCHAR(10), Effective_From_Date Date NOT NULL,
Premium_Amount CURRENCY NOT NULL, Paid_Date Date NOT NULL, Is_Home_BuildingPolicy
13

YesNo NOT NULL, Is_Home_ContentsPolicy YesNo NOT NULL, Property_Id INT NOT NULL,
Primary Key (Policy_Id), Foreign Key (Property_Id) References Property (Property_Id));
CREATE TABLE Home_Content (Policy_Id VARCHAR(10), Content_Premimum_Amount
CURRENCY NOT NULL, Primary Key (Policy_Id), Foreign Key (Policy_Id) References Policy
(Policy_Id));
CREATE TABLE House_Type (House_Type_Id VARCHAR(3), House_Type_Name VARCHAR(25)
NOT NULL, Primary Key (House_Type_Id));
CREATE TABLE Home_Building (Policy_Id VARCHAR(10), House_Type_Id VARCHAR(3) NOT NULL,
Year_Built INT NOT NULL, Is_Alarm_Fitted YesNo NOT NULL, Has_Window_Locks YesNo NOT
NULL, Issued_Amount CURRENCY NOT NULL, Building_Premium_Amount CURRENCY NOT NULL,
Primary Key (Policy_Id), Foreign Key (Policy_Id) References Policy (Policy_Id), Foreign Key
(House_Type_Id) References House_Type (House_Type_Id));
CREATE TABLE Policy_Status (Policy_Status_Id VARCHAR(5), Policy_Status_Name VARCHAR(25)
NOT NULL, Primary Key (Policy_Status_Id));
CREATE TABLE Policy_Claim (Claim_Id INT, Lodged_Date Date NOT NULL, Policy_Status_Id
VARCHAR(5) NOT NULL, Policy_Id VARCHAR(10) NOT NULL, Primary Key (Claim_Id), Foreign Key
(Policy_Id) References Policy (Policy_Id), Foreign Key (Policy_Status_Id) References Policy_Status
(Policy_Status_Id));
CREATE TABLE Settled_Claim(Claim_Id INT, Settled_Date Date NOT NULL, Amount_Settled
CURRENCY NOT NULL, Primary Key (Claim_Id), Foreign Key (Claim_Id) References Policy_Claim
(Claim_Id));
CREATE TABLE Rejected_Claim (Claim_Id INT, Rejected_Date Date NOT NULL, Rejected_Reason
VARCHAR(50) NOT NULL, Primary Key (Claim_Id), Foreign Key (Claim_Id) References
Policy_Claim (Claim_Id));
CREATE TABLE Content_Item (Item_Id INT, Item_Name VARCHAR(50) NOT NULL, Manufacturer
VARCHAR(35) NOT NULL, Model VARCHAR(25) NOT NULL, Purchase_Date Date NOT NULL,
14

Quantity INT NOT NULL, Claimed_Status VARCHAR(25) NOT NULL, Policy_Id VARCHAR(10) NOT
NULL, Primary Key (Item_Id), Foreign Key (Policy_Id) References Home_Content (Policy_Id));
CREATE TABLE Claim_Item (Claim_Id INT, Item_Id INT, Quantity INT NOT NULL, Item_Status
VARCHAR(25) NOT NULL, Primary Key (Claim_Id, Item_Id), Foreign Key (Claim_Id) References
Policy_Claim (Claim_Id), Foreign Key (Item_Id) References Content_Item (Item_Id));
CREATE TABLE Assessment_Report (Report_Id INT, Report_Date Date NOT NULL,
Initial_Recommendation VARCHAR(45) NOT NULL, Person_Id VARCHAR(6) NOT NULL, Claim_Id
INT NOT NULL, Report_Type_Id VARCHAR(1) NOT NULL, Primary Key (Report_Id), Foreign Key
(Person_Id) References Assessor (Person_Id), Foreign Key (Claim_Id) References Policy_Claim
(Claim_Id), Foreign Key (Report_Type_Id) References Report_Type (Report_Type_Id));
CREATE TABLE Final_Assessment_Report (Report_Id INT, Final_Assessment_Date Date NOT
NULL, Total_Amount CURRENCY NOT NULL, Cost_Report_Demo VARCHAR(60) NOT NULL,
Primary Key (Report_Id), Foreign Key (Report_Id) References Assessment_Report (Report_Id));
15

DML Statements:
INSERT INTO Location (Location_Id, Street, City, Post_Code) VALUES (1,'Church Street',
'Melbourne', 4789);
INSERT INTO Location (Location_Id, Street, City, Post_Code) VALUES (2,'Victoria Street', 'Perth',
4587);
INSERT INTO Location (Location_Id, Street, City, Post_Code) VALUES (3,'Queen Street',
'Brisbane', 1268);
INSERT INTO Location (Location_Id, Street, City, Post_Code) VALUES (4,'Elizabeth Street',
'Sydney', 2147);
INSERT INTO Location (Location_Id, Street, City, Post_Code) VALUES (5,'King Street', 'Perth',
3658);
INSERT INTO Person_Type (Person_Type_Id, Person_Type_Name) VALUES ('A','Assessor');
INSERT INTO Person_Type (Person_Type_Id, Person_Type_Name) VALUES ('C','Customer');
INSERT INTO Qualification (Qualification_Id, Qualification_Name) VALUES ('BS','Bachelor Of
Science');
INSERT INTO Qualification (Qualification_Id, Qualification_Name) VALUES ('BD','Bachelor Of
Data');
INSERT INTO Qualification (Qualification_Id, Qualification_Name) VALUES ('BHM','Bachelor Of
Hotel Mgmt');
INSERT INTO Qualification (Qualification_Id, Qualification_Name) VALUES ('BB','Bachelor Of
Business');
INSERT INTO Qualification (Qualification_Id, Qualification_Name) VALUES ('BAI','Bachelor Of
Applied IT');
INSERT INTO Report_Type (Report_Type_Id, Report_Type_Name) VALUES ('F','Final');
INSERT INTO Report_Type (Report_Type_Id, Report_Type_Name) VALUES ('P','Partial');
16

INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER001','Jordina',2,'C');
INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER002','Rhianne',5,'C');
INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER003','Borce',1,'A');
INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER004','Jaris',3,'A');
INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER005','Jordina',4,'C');
INSERT INTO Person (Person_Id, Person_Name, Location_Id, Person_Type_Id) VALUES
('PER006','Khiem',3,'A');
INSERT INTO Assessor (Person_Id) VALUES ('PER003');
INSERT INTO Assessor (Person_Id) VALUES ('PER004');
INSERT INTO Assessor (Person_Id) VALUES ('PER006');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER001','BD','15-05-2002');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER001','BHM','21-05-2007');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER002','BAI','05-09-2009');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER003','BB','09-07-2010');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER003','BS','24-02-2008');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER004','BAI','30-03-2007');
INSERT INTO Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified) VALUES
('PER005','BHM','18-07-2011');
INSERT INTO Property (Property_Id, Location_Id, Person_Id) VALUES (11,3,'PER001');
17

INSERT INTO Property (Property_Id, Location_Id, Person_Id) VALUES (12,4,'PER005');
INSERT INTO Property (Property_Id, Location_Id, Person_Id) VALUES (13,5,'PER002');
INSERT INTO Property (Property_Id, Location_Id, Person_Id) VALUES (14,2,'PER001');
INSERT INTO Property (Property_Id, Location_Id, Person_Id) VALUES (15,1,'PER005');
INSERT INTO Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id) VALUES ('PLY1001','16-03-
2011',3600,'18-03-2011',0,1,12);
INSERT INTO Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id) VALUES ('PLY1002','27-06-
2010',2400,'04-08-2013',1,1,14);
INSERT INTO Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id) VALUES ('PLY1003','19-05-
2013',6330,'08-09-2012',1,0,15);
INSERT INTO Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id) VALUES ('PLY1004','06-07-
2014',5200,'19-11-2010',0,1,13);
INSERT INTO Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id) VALUES ('PLY1005','09-05-
2013',4500,'21-12-2012',1,1,14);
INSERT INTO Home_Content (Policy_Id, Content_Premimum_Amount) VALUES ('PLY1004',480);
INSERT INTO Home_Content (Policy_Id, Content_Premimum_Amount) VALUES ('PLY1002',640);
INSERT INTO Home_Content (Policy_Id, Content_Premimum_Amount) VALUES ('PLY1005',720);
INSERT INTO Home_Content (Policy_Id, Content_Premimum_Amount) VALUES ('PLY1001',540);
INSERT INTO Home_Content (Policy_Id, Content_Premimum_Amount) VALUES ('PLY1003',575);
INSERT INTO House_Type (House_Type_Id, House_Type_Name) VALUES ('B','Building');
INSERT INTO House_Type (House_Type_Id, House_Type_Name) VALUES ('I','Individual');
INSERT INTO House_Type (House_Type_Id, House_Type_Name) VALUES ('A','Apartment');
18

INSERT INTO Home_Building (Policy_Id, House_Type_Id, Year_Built, Is_Alarm_Fitted,
Has_Window_Locks, Issued_Amount, Building_Premium_Amount) VALUES
('PLY1003','A',2014,1,1,5700,570);
INSERT INTO Home_Building (Policy_Id, House_Type_Id, Year_Built, Is_Alarm_Fitted,
Has_Window_Locks, Issued_Amount, Building_Premium_Amount) VALUES
('PLY1002','I',2016,0,1,7500,750);
INSERT INTO Home_Building (Policy_Id, House_Type_Id, Year_Built, Is_Alarm_Fitted,
Has_Window_Locks, Issued_Amount, Building_Premium_Amount) VALUES
('PLY1005','B',2015,1,0,8400,840);
INSERT INTO Policy_Status (Policy_Status_Id, Policy_Status_Name) VALUES ('S','Settled');
INSERT INTO Policy_Status (Policy_Status_Id, Policy_Status_Name) VALUES ('R','Rejected');
INSERT INTO Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id) VALUES (101,'15-
05-2013','S','PLY1002');
INSERT INTO Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id) VALUES (102,'28-
09-2014','S','PLY1001');
INSERT INTO Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id) VALUES (103,'20-
06-2012','R','PLY1005');
INSERT INTO Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id) VALUES (104,'02-
02-2015','S','PLY1003');
INSERT INTO Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id) VALUES (105,'19-
08-2016','R','PLY1004');
INSERT INTO Settled_Claim(Claim_Id, Settled_Date, Amount_Settled) VALUES (102,'21-10-
2014',7200);
INSERT INTO Settled_Claim(Claim_Id, Settled_Date, Amount_Settled) VALUES (101,'18-06-
2013',7200);
INSERT INTO Settled_Claim(Claim_Id, Settled_Date, Amount_Settled) VALUES (104,'05-05-
2015',7200);
INSERT INTO Rejected_Claim (Claim_Id, Rejected_Date, Rejected_Reason) VALUES (103,'15-07-
2012','Error found');
19

INSERT INTO Rejected_Claim (Claim_Id, Rejected_Date, Rejected_Reason) VALUES (105,'27-09-
2016','Invalid data');
INSERT INTO Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date,
Quantity, Claimed_Status, Policy_Id) VALUES
(201,'Laptop','Dell','Inspiron',720,2,'Claimed','PLY1005');
INSERT INTO Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date,
Quantity, Claimed_Status, Policy_Id) VALUES (202,'Bed','Dehrun','New
Wood',810,1,'Claimed','PLY1003');
INSERT INTO Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date,
Quantity, Claimed_Status, Policy_Id) VALUES (203,'Two Wheeler','Atlas','DX',930,3,'Not
Yet','PLY1001');
INSERT INTO Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date,
Quantity, Claimed_Status, Policy_Id) VALUES (204,'Car','Audi','BX-100',650,2,'Not
Yet','PLY1002');
INSERT INTO Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status) VALUES
(102,201,3,'Claimed');
INSERT INTO Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status) VALUES
(103,203,1,'Claimed');
INSERT INTO Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status) VALUES (104,202,2,'Not
Yet');
INSERT INTO Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status) VALUES (105,204,4,'Not
Yet');
INSERT INTO Assessment_Report (Report_Id, Report_Date, Initial_Recommendation, Person_Id,
Claim_Id, Report_Type_Id) VALUES (301, '19-07-2014','KYC','PER003',102,'F');
INSERT INTO Assessment_Report (Report_Id, Report_Date, Initial_Recommendation, Person_Id,
Claim_Id, Report_Type_Id) VALUES (302, '21-05-2015','KYC','PER004',105,'F');
INSERT INTO Final_Assessment_Report (Report_Id, Final_Assessment_Date, Total_Amount,
Cost_Report_Demo) VALUES (301,'20-08-2014',4500,'Verified')
INSERT INTO Final_Assessment_Report (Report_Id, Final_Assessment_Date, Total_Amount,
Cost_Report_Demo) VALUES (302,'14-06-2015',7200,'Verified')
20

Query Statements
Q1:
SELECT SUM(Is_Home_BuildingPolicy) AS NumBldgPolicy, SUM(Is_Home_ContentsPolicy) AS
NumContentPolicy FROM Policy;
Q2:
SELECT p.Person_Id, p.Person_Name, p.Location_Id, p.Person_Type_Id FROM Person p, Assessor
a WHERE p.Person_id=a.Person_Id and a.Person_Id NOT IN (SELECT Person_Id FROM
Assessment_Report);
Q3:
SELECT Policy_Status_Id, COUNT(*) AS Total_Claim FROM Policy_Claim GROUP BY
Policy_Status_Id;
21

Q4:
SELECT pr.Person_Id, pr.Person_Name, pr.Location_Id, pr.Person_Type_Id FROM Person AS pr,
Property AS p, Policy AS py WHERE pr.Person_Id=p.Person_Id AND p.Property_Id
=py.Property_Id AND py.Policy_Id IN (SELECT Policy_Id FROM Policy_Claim WHERE
Policy_Status_Id='R');
Q5:
SELECT MAX(Amount_Settled) AS Highest_Settle_Amount FROM Settled_Claim;
Q6:
SELECT py.Policy_Id, (hbd.Building_Premium_Amount+hct.Content_Premimum_Amount) AS
Total_Amount_Of_Premium_Paid FROM Policy AS py, Home_Building AS hbd, Home_Content
AS hct WHERE py.Policy_Id=hct.Policy_Id AND py.Policy_Id=hbd.Policy_Id;
22

Report Creation using Query
SELECT pr.Property_Id, loc.Street, loc.City, loc.Post_Code, py.Policy_Id, py.Effective_From_Date,
py.Premium_Amount, pc.Lodged_Date FROM Property pr, Policy py, Policy_Claim pc, Location
loc WHERE pr.Property_Id=py.Property_Id AND py.Policy_Id=pc.Policy_Id AND
pr.Location_Id=loc.Location_Id AND pc.Policy_Status_Id='R' ORDER BY pc.Lodged_Date;
Implementation Report
Interesting things I have learnt
 Design the tables using query (without using table design)
 Design the report using query
 Design the CHECK constraints for validating the input value like date, amount and etc.
Complex to Solve
Design sub query (in query 2)
23

References
M. (n.d.). Microsoft Access Data Types. Retrieved from https://docs.microsoft.com/en-
us/sql/odbc/microsoft/microsoft-access-data-types?view=sql-server-2017
Explain types of integrity constraints with example. (1968, October 01). Retrieved from
http://www.ques10.com/p/17134/explain-types-of-integrity-constraints-with-exampl/
Restrict data input by using validation rules. (n.d.). Retrieved from
https://support.office.com/en-us/article/restrict-data-input-by-using-validation-rules-
b91c6b15-bcd3-42c1-90bf-e3a0272e988d
24