COIT20247 Database Design and Development - Free Samples to Students
Even though a property can be owned by many persons, it is assumed that only one person owns a property. A person’s details may be existing even though the related property details are not available at that time
A person can have many properties.
A policy cannot exist without the property details that it is covering. However, a property’s details may be existing and it may be related to a policy later on.
A policy can be either Home Building policy or Home Content policy or it can be of both.
A Home Content policy can cover many items of different kind/type. A policy can cover more than one item of the same kind. i.e., If there are more than one piece of item of the same kind such as 60” LED LG TV to be covered in the same policy, then quantity value has to be entered appropriately.
Each claim is for one policy only and every claim requires an existing policy. Due to operational reasons, a claim may contain details of some contents other than those mentioned in Home content policy. A claim when processed results into either settled or rejected claim only. If a claim is rejected, then it cannot be settled at all. i.e., combination of settlement of some items and rejection of remaining items from the same claim are not performed in this model.
Each claim is handled by one assessor only.
An assessor may handle many claims but for each claim, he/she submits one initial and one final report only and hence final report is assumed to be an extension of initial report (initial report has been treated as report).
Reason for rejecting a claim may be due to lapsed period or as recommended by the assessor.
An assessor is also a person and there could many other type of persons in future.
An assessor can have many qualifications.
Total premium amount is the sum of Home Building Premium and Content premium amount for a combined policy.
The amount settled for a claim is as per the final assessment submitted by the assessor.
The details of the recommendation for claim settlement is submitted in the form of memo/report by the assessor.
Answer:
Introduction
The database development of CQI is done in MS Access and the report is describing the same. Complete detail about CQI database and all the features and functionality of MS Access is being shown in the report.
Person (PersonID, PersonName, Street, City, Postcode, PersonType)
Assessor (PersonID, QualificationName, DateQualified)
Foreign Key (PersonID) References Person
Property (PropertyID, Street, City, Postcode, PersonID)
Foreign Key (PersonID) References Person
Policy (PolicyID, EffectiveFromDate, PremiumAmount, PaidDate, IsHomeBuildingPolicy, IsHomeContentsPolicy, TotalPolicyAmount, PropertyID)
Foreign Key (PropertyID) References Property
Home_Building (PolicyID, HouseType, YearBuilt, IsAlarmFitted, HasWindowsLocks, InsuredAmount, BuildingPremiumAmount)
Foreign Key (PolicyID) References Policy
Home_Content (PolicyID, ContentPremiumAmount)
Foreign Key (PolicyID) References Policy
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchasePrice, Quantity, ClaimedStatus, PolicyID)
Foreign Key (PolicyID) References Policy
Claim (ClaimID, LodgedDate, Status, PolicyID, PersonID)
Foreign Key (PersonID) References Person
Settled_Claim (ClaimID, SettledDate, AmountSettled)
Foreign Key (ClaimID) References Claim
Rejected_Claim (ClaimID, RejectedDate, RejectedReason)
Foreign Key (ClaimID) References Claim
Items_Claimed (ClaimID, ItemID, ItemName, Quantity, ItemStatus)
Foreign Key (ClaimID) References Claim
Foreign Key (ItemID) References Content_Item
Assessment_Report (ReportID, ReportDate, InitialRecommendation, ReportType, ClaimID, PersonID)
Foreign Key (ClaimID) References Claim
Foreign Key (PersonID) References Assessor
Final_Assessment_Report (ReportID, FinalAssessmentDate, TotalAmount, CostReportMemo)
Foreign Key (ReportID) References Assessment_Report
(teratrax.com n.d.)
Functional Dependency
Table Person
Person (PersonID, PersonName, Street, City, Postcode, PersonType)
PersonNameàPersonID
PersonTypeàPersonID
All the fields depend upon the primary key just like PersonName and PersoneType. There is not any other field that identify the persons except PersonID.
Table Property
Property (PropertyID, Street, City, Postcode, PersonID)
StreetàPropertyID
CityàPropertyID
PostcodeàPropertyID
PersonIDàPropertyID
All the fields depend upon the primary key PropertyIDonly.There is not any other field that identify the propert except PropertyID.
Normalisation
A third normal form is achieved by the following way-
- Make all the separate tables.
- Make primary key in all separate tables.
- No fields should uniquely identify the record except primary or composite key.
- No transitive dependency should occur into any table.
Property and Person both the tables are satisfying the above normalization rules. Not only Person and Property, all the tables are satisfying the third normal form.
SQL Queries
Query 1
SELECT subQuery1.Home_Policy, subQuery2.Home_Content_PolicyFROM (SELECT Count(*) AS Home_Policy FROM Home_Building) AS subQuery1, (SELECT Count(*) AS Home_Content_Policy FROM Home_Content) AS subQuery2;
Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonTypeFROM PersonWHERE PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT subQuery1.Settled_Claim, subQuery2.Rejected_ClaimFROM (SELECT Count(*) AS Settled_Claim FROM Settled_Claim) AS subQuery1, (SELECT Count(*) AS Rejected_Claim FROM Rejected_Claim) AS subQuery2;
Query 4
SELECT * FROM PersonWHERE PersonID in (Select Property.PersonIDFROM Property INNER JOIN (Policy INNER JOIN Claim ON Policy.PolicyID = Claim.PolicyID) ON Property.PropertyID = Policy.PropertyIDWhere Claim.Status='Rejected');
Query 5
SELECT TOP 1 ClaimID, AmountSettled, SettledDate FROM Settled_Claim ORDER BY AmountSettled DESC;
Query 6
SELECT Policy.PolicyID, Policy.IsHomeBuildingPolicy, Policy.IsHomeContentsPolicy, Policy.PremiumAmount FROM PolicyWHERE Policy.IsHomeBuildingPolicy<>0 and Policy.IsHomeContentsPolicy<>0;
Learning features while database development of CQI are as follows-
- Learned to use the sub queries.
- Learned to use the database queries.
- Learned to use the database reports.
- Learned to make normalized database
The assignment is very practical and giving complete working knowledge. There are not so much complex points. I faced only small issues in making the database report.
Conclusion
The report is showing the complete working and functionality of database development of CQI. The user can easily understand the full concept of the database development. Most of the supported features of MS Access are used in the assignment like queries, reports, relations etc.
The report is showing all the queries along with query results which is showing the database working very clearly.
References
Tutorialspoint.com(2018).DBMS - Normalization.[online]. Available from: https://www.tutorialspoint.com/dbms/database_normalization.htm [Accessed: 10-May-2018]
teratrax.com (n.d.). SQL Server Data Types and Ranges. [online.] Available from:https://www.teratrax.com/sql-server-data-types-ranges/ [Accessed 10 May 2018].
Buy COIT20247 Database Design and Development - Free Samples to Students Answers Online
Talk to our expert to get the help with COIT20247 Database Design and Development - Free Samples to Students Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
