ERD Normalization Relational Schema

ERD

Normalization
Relational Schema
PersonType (PersonTypeID, PersonTypeName)
Primary Key (PersonTypeID)
Address (AddressID, Street, City, Postcode)
Primary Key (AddressID)
Person (PersonID, PersonName, AddressID, PersonTypeID)
Primary Key (PersonID),
Foreign Key (AddressID) References Address (AddressID),
Foreign Key (PersonTypeID) References PersonType (PersonTypeID)
Property (PropertyID, AddressID, PersonID)
Primary Key (PropertyID),
Foreign Key (AddressID) References Address (AddressID),
Foreign Key (PersonID) References Person (PersonID)
Policy (PolicyID, EffectiveFromDate, PremiumAmount, PaidDate,
IsHomeBuildingPolicy, IsHomeContentsPolicy,PropertyID)
Primary Key (PolicyID),
Foreign Key (PropertyID) References Property (PropertyID)
Home_Content (PolicyID, ContentPremiumAmount)
Primary Key (PolicyID),
Foreign Key (PolicyID) References Policy (PolicyID)
Home_Building (PolicyID, HouseType, YearBuilt, IsAlarmFitted, HasWindowLocks,
InsuredAmount, BuildingPremiumAmount)
Primary Key (PolicyID),

Foreign Key (PolicyID) References Policy (PolicyID)
Content_Item (ItemID, ItemName, Manufacturer, Model, PurchaseDate, Quantity,
ClaimedStatus, PolicyID)
Primary Key (ItemID),
Foreign Key (PolicyID) References Home_Content (PolicyID)
Claim (ClaimID, LodgedDate, Status, PolicyID)
Primary Key (ClaimID),
Foreign Key (PolicyID) References Policy (PolicyID)
ItemsClaimed (ClaimID, ItemID, Quantity, ItemStatus)
Primary Key (ClaimID, ItemID),
Foreign Key (ClaimID) References Claim (ClaimID)
Foreign Key (ItemID) References Content_Item (ItemID)
Settled_Claim (ClaimID, SettledDate, AmountSettled)
Primary Key (ClaimID),
Foreign Key (ClaimID) References Claim (ClaimID)
Rejected_Claim (ClaimID, RejectedDate, RejectedReason)
Primary Key (ClaimID),
Foreign Key(ClaimID) References Claim (ClaimID)
Assessor (PersonID)
Primary Key (PersonID),
Foreign Key(PersonID) References Person (PersonID)
Qualification (QualificationID, QualificationName)
Primary Key (QualificationID)

AssessorQualify (PersonID, QualificationID, DateQualified)
Primary Key (PersonID, QualificationID),
Foreign Key(PersonID) References Assessor (PersonID)
Foreign Key(QualificationID) References Qualification (QualificationID)
Assessment_Report (ReportID, ReportDate, InitialRecommendation, ReportType,
PersonID, ClaimID)
Primary Key (ReportID)
Foreign Key (PersonID) References Assessor (PersonID),
Foreign Key (ClaimID) References Claim (ClaimID)
Final_Assessment_Report (ReportID, FinalAssessmentDate,TotalAmount,
CostReportMemo)
Primary Key (ReportID)
Foreign Key (ReportID) References Assessment_Report (ReportID)