Introduction
The report is portraying the database design and implementation of the complete database of CQI. All important CQI features are being shown in the report like database report, queries etc.
ER Diagram Mapping
Person (FlPersonID, FlPersonName, FlStreet, FlCity, FlPostcode, FlPersonType)
Assessor (FlPersonID, FlQualificationName, FlDateQualified)
Foreign Key (FlPersonID) References Person
Property (FlPropertyID, FlStreet, FlCity, FlPostcode, FlPersonID)
Foreign Key (FlPersonID) References Person
Policy (FlPolicyID, FlEffectiveFromDate, FlPremiumAmount, FlPaidDate, FlIsHomeBuildingPolicy, FlIsHomeContentsPolicy, FlTotalPolicyAmount, FlPropertyID)
Foreign Key (FlPropertyID) References Property
Home_Building (FlPolicyID, FlHouseType, FlYearBuilt, FlIsAlarmFitted, FlHasWindowsLocks, FlInsuredAmount, FlBuildingPremiumAmount)
Foreign Key (FlPolicyID) References Policy
Home_Content (FlPolicyID, FlContentPremiumAmount)
Foreign Key (FlPolicyID) References Policy
Content_Item (FlItemID, FlItemName, FlManufacturer, FlModel, FlPurchasePrice, FlQuantity, FlClaimedStatus, FlPolicyID)
Foreign Key (FlPolicyID) References Policy
Claim (FlClaimID, FlLodgedDate, FlStatus, FlPolicyID, FlPersonID)
Foreign Key (FlPersonID) References Person
Settled_Claim (FlClaimID, FlSettledDate, FlAmountSettled)
Foreign Key (FlClaimID) References Claim
Rejected_Claim (FlClaimID, FlRejectedDate, FlRejectedReason)
Foreign Key (FlClaimID) References Claim
Items_Claimed (FlClaimID, FlItemID, FlItemName, FlQuantity, FlItemStatus)
Foreign Key (FlClaimID) References Claim
Foreign Key (FlItemID) References Content_Item
Assessment_Report (FlReportID, FlReportDate, FlInitialRecommendation, FlReportType, FlClaimID, FlPersonID)
Foreign Key (FlClaimID) References Claim
Foreign Key (FlPersonID) References Assessor
Final_Assessment_Report (FlReportID, FlFinalAssessmentDate, FlTotalAmount, FlCostReportMemo)
Foreign Key (FlReportID) References Assessment_Report
Assessor Table
Assessor (FlPersonID, FlQualificationName, FlDateQualified)
FlQualificationName àFlPersonID
FlDateQualified àFlPersonID
The Fields of the assessor table are depending upon the Person ID field only that is playing the role of primary key in the assessor table.
ItemsClaimed
ItemsClaimed (FlClaimID, FlItemID, FlItemName, FlQuantity, FlStatus)
FlItemName à FlClaimID, FlItemID
FlQuantity àFlClaimID, FlItemID
FlStatus à FlClaimID, FlItemID
The Fields of the Items Claimed table are depending upon the Claim ID field only that is playing the role of primary key in the Items Claimed table.
Normalisation
The normalized database is created in the following manner-
- Each table contains only related data e.g. customer table only contains the customer’s data.
- Make primary key in all the tables.
- No field except primary key should uniquely identify the records in the tables.
- There should be no transitive dependency in any table.
All tables are satisfying the above rules. That is why; the CQI database is in third normal form.
(Easy Computer Academy 2017)
(BigennersBook 2017)
SQL Queries
Query 1
SELECT sq1.Total_No_Home_Policy, sq2.Total_No_Home_Content_Policy FROM (SELECT Count(*) AS Total_No_Home_Policy FROM Home_Building) AS sq1, (SELECT Count(*) AS Total_No_Home_Content_Policy FROM Home_Content) AS sq2;
Query 2
SELECT PersonID, PersonName, Street, City, Postcode, PersonType FROM PersonWHERE PersonID not in (Select PersonID from Assessment_Report) and PersonType='Assessor';
Query 3
SELECT sq1.Total_No_Settled_Claim, sq2.Total_No_Rejected_Claim FROM (SELECT Count(*) AS Total_No_Settled_Claim FROM Settled_Claim) AS sq1, (SELECT Count(*) AS Total_No_Rejected_Claim FROM Rejected_Claim) AS sq2;
Query 4
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.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
These are the features in the assignment that are giving great knowledge-
- How to make database report
- How to use sub queries
- How to use inner joins
- How to use normalization steps
Complex Features
I only faced the problems in the following assignment functions-
- Query 1 – using of sub query
- Query 3- using of sub query
- Making database report
Conclusion
The report is showing all the RDBMS features along with benefits of the assignment. It is showing the knowledge that is being provided by the assignment. The normalization technique is fully described in the report.
References
Easy Computer Academy (2017), Database Normalization Explained in Simple English, Retrieved from https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/
BigennersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database: Foreign Keys, Retrieved from:
https://beginnersbook.com/2015/05/normalization-in-dbms/