Order Now
Uncategorized

Implementing An ER Model With Microsoft Access

16 min read
Posted on 
April 23rd, 2025
Home Uncategorized Implementing An ER Model With Microsoft Access

Tasks to be completed

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Location (Location_Id, Street, City, Post_Code)

Primary Key (Location_Id)

Person_Type (Person_Type_Id, Person_Type_Name)

Primary Key (Person_Type_Id)

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

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)

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)

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)

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)

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

Set the format to Short Date

Set Required to Yes and Allow zero length to No

From ERD, Each policy belong to any one of the property

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

Integrity constraints

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)

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)

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

Creating queries

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

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

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

Location

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

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

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

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’)

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;

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;

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;

 

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.

Design sub query (in query 2)

References

(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 https://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

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order
Calculate the price
Pages (275 words)
$0.00
Course Scholars
Company
Legal
How Our Service is Used:
Course Scholars essays are NOT intended to be forwarded as finalized work as it is only strictly meant to be used for research and study purposes. Course Scholars does not endorse or condone any type of plagiarism.
Subscribe
No Spam
© 2025 Course Scholars. All rights reserved.
Course Scholars will be listed as ‘Course Scholars’ on your bank statement.

Order your essay today and save 15% with the discount code GINGER

Order Now