Unit Learning Outcomes
The report is giving complete overview about CQI database that is implemented as normalized database in MS Access. The CQI database is containing queries, reports etc. that are very helpful for management.
ER Diagram Mapping
Person (FielldPersonID, FielldPersonName, FielldStreet, FielldCity, FielldPostcode, FielldPersonType)
Assessor (FielldPersonID, FielldQualificationName, FielldDateQualified)
Foreign Key (FielldPersonID) References Person
Property (FielldPropertyID, FielldStreet, FielldCity, FielldPostcode, FielldPersonID)
Foreign Key (FielldPersonID) References Person
Policy (FielldPolicyID, FielldEffectiveFromDate, FielldPremiumAmount, FielldPaidDate, FielldIsHomeBuildingPolicy, FielldIsHomeContentsPolicy, FielldTotalPolicyAmount, FielldPropertyID)
Foreign Key (FielldPropertyID) References Property
Home_Building (FielldPolicyID, FielldHouseType, FielldYearBuilt, FielldIsAlarmFitted, FielldHasWindowsLocks, FielldInsuredAmount, FielldBuildingPremiumAmount)
Foreign Key (FielldPolicyID) References Policy
Home_Content (FielldPolicyID, FielldContentPremiumAmount)
Foreign Key (FielldPolicyID) References Policy
Content_Item (FielldItemID, FielldItemName, FielldManufacturer, FielldModel, FielldPurchasePrice, FielldQuantity, FielldClaimedStatus, FielldPolicyID)
Foreign Key (FielldPolicyID) References Policy
Claim (FielldClaimID, FielldLodgedDate, FielldStatus, FielldPolicyID, FielldPersonID)
Foreign Key (FielldPersonID) References Person
Settled_Claim (FielldClaimID, FielldSettledDate, FielldAmountSettled)
Foreign Key (FielldClaimID) References Claim
Rejected_Claim (FielldClaimID, FielldRejectedDate, FielldRejectedReason)
Foreign Key (FielldClaimID) References Claim
Items_Claimed (FielldClaimID, FielldItemID, FielldItemName, FielldQuantity, FielldItemStatus)
Foreign Key (FielldClaimID) References Claim
Foreign Key (FielldItemID) References Content_Item
Assessment_Report (FielldReportID, FielldReportDate, FielldInitialRecommendation, FielldReportType, FielldClaimID, FielldPersonID)
Foreign Key (FielldClaimID) References Claim
Foreign Key (FielldPersonID) References Assessor
Final_Assessment_Report (FielldReportID, FielldFinalAssessmentDate, FielldTotalAmount, FielldCostReportMemo)
Foreign Key (FielldReportID) References Assessment_Report
Assessor (FielldPersonID, FielldQualificationName, FielldDateQualified)
FielldQualificationName à FielldPersonID
FielldDateQualified à FielldPersonID
All assessor records are depending upon the Person ID as it is the primary key in the Assessor table.
Property (FielldPropertyID, FielldStreet, FielldCity, FielldPostcode, FielldPersonID)
FielldStreetà FielldPropertyID
FielldCityà FielldPropertyID
FielldPostcodeà FielldPropertyID
FielldPersonIDà FielldPropertyID
All property records are depending upon the Property ID as it is the primary key in the Property table.
The CQI database is designed as third normalized database by using the following rules-
- Create new tables for separated data.
- Set primary key in all newly created tables.
- All fields depend upon the primary key only.
- Transitive dependency should not occur in any table.
(USA Informa 2018)
(BigennersBook 2017)
SELECT homeSubQuery.Home_Policy, contentSubQuery.Home_Content_Policy FROM (SELECT Count(*) AS Home_Policy FROM Home_Building) AS homeSubQuery, (SELECT Count(*) AS Home_Content_Policy FROM Home_Content) AS contentSubQuery;
SELECT Person.PersonID, Person.PersonName, Person.Street, Person.City, Person.Postcode, Person.PersonType FROM Person WHERE Person.PersonID not in (Select PersonID from Assessment_Report) and Person.PersonType=’Assessor’;
SELECT settledSubQuery.Settled_Claim, rejectedSubQuery.Rejected_Claim
FROM (SELECT Count(*) AS Settled_Claim FROM Settled_Claim) AS settledSubQuery, (SELECT Count(*) AS Rejected_Claim FROM Rejected_Claim) AS rejectedSubQuery;
SELECT * FROM Person WHERE PersonID in (Select Property.PersonID FROM Property INNER JOIN (Policy INNER JOIN Claim ON Policy.PolicyID = Claim.PolicyID) ON Property.PropertyID = Policy.PropertyID Where Claim.Status=’Rejected’);
SELECT TOP 1 ClaimID, AmountSettled, SettledDate FROM Settled_Claim ORDER BY AmountSettled DESC;
SELECT Policy.PolicyID, Policy.IsHomeBuildingPolicy, Policy.IsHomeContentsPolicy, Policy.PremiumAmount AS [‘Total Premium Amount’] FROM Policy WHERE Policy.IsHomeBuildingPolicy<>0 and Policy.IsHomeContentsPolicy<>0;
The assignment is giving lots of knowledge in the following fields-
- Database SQL queries
- Database report
- Work on sub queries
- Work on join queries
I faced complexity only in two areas-
- Sub queries
- Join queries
Conclusion
The report is completely based upon the CQI database and showing all the features that are used in making the CQI database like normalization, functional dependencies, queries, reports etc.
References
BigennersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database: Foreign Keys, Retrieved from:
USA Informa (2018), SQL by Design: Why You Need Database Normalization. Retrieved from:
https://www.itprotoday.com/risk-and-compliance/ibm-executives-press-us-lawmakers-not-adopt-eu-privacy-law.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
