CREATE TABLE AvailableCoverage (
-- Available Coverage involves Coverage Type and Coverage Type has Coverage Type Name, CoverageTypeName varchar NOT NULL, -- Available Coverage involves Product Offering and Product Offering has Product Offering Name, ProductOfferingName varchar NOT NULL, PRIMARY KEY(CoverageTypeName, ProductOfferingName)
) GO
CREATE TABLE Claim (
-- Claim has Claim Number, ClaimNumber Int NOT NULL, PRIMARY KEY(ClaimNumber)
) GO
CREATE TABLE ClaimPayment (
-- Claim Payment is for Claim Details and Claim Details involves Claim and Claim has Claim Number, ClaimDetailsClaimNumber Int NOT NULL, -- Claim Payment is for Claim Details and Claim Details involves Date, ClaimDetailsDate datetime NOT NULL, -- Claim Payment is for Claim Details and Claim Details involves Policy Coverage and Policy Coverage involves Policy and Policy is from Insurer and Insurer has Insurer Name, ClaimDetailsPolicyCoveragePolicyInsurerName varchar NOT NULL, -- Claim Payment is for Claim Details and Claim Details involves Policy Coverage and Policy Coverage involves Policy and Policy has Policy Number, ClaimDetailsPolicyCoveragePolicyNumber Int NOT NULL, -- Claim Payment is for Claim Details and Claim Details involves Policy Coverage and Policy Coverage involves Coverage Type and Coverage Type has Coverage Type Name, ClaimDetailsPolicyCoverageTypeName varchar NOT NULL, -- Claim Payment is of Claim Payment Type and Claim Payment Type has Claim Payment Type Code, ClaimPaymentTypeCode varchar(16) NOT NULL, PRIMARY KEY(ClaimDetailsClaimNumber, ClaimDetailsPolicyCoveragePolicyInsurerName, ClaimDetailsPolicyCoveragePolicyNumber, ClaimDetailsPolicyCoverageTypeName, ClaimDetailsDate), FOREIGN KEY (ClaimDetailsClaimNumber) REFERENCES Claim (ClaimNumber)
) GO
CREATE TABLE ClaimPaymentType (
-- Claim Payment Type has Claim Payment Type Code, ClaimPaymentTypeCode varchar(16) NOT NULL, -- maybe Claim Payment Type has descriptive-Text, DescriptiveText varchar NULL, PRIMARY KEY(ClaimPaymentTypeCode)
) GO
CREATE TABLE CoverageType (
-- Coverage Type has Coverage Type Name, CoverageTypeName varchar NOT NULL, -- maybe Coverage Type has descriptive-Text, DescriptiveText varchar NULL, PRIMARY KEY(CoverageTypeName)
) GO
CREATE TABLE Incident (
-- maybe Incident resulted in Claim and Claim has Claim Number, ClaimNumber Int NULL, -- Incident affected Claimant and Claimant is a kind of Stakeholder and Stakeholder is a kind of Party and Party has Party ID, ClaimantID int NOT NULL, -- Incident occurred on Date, Date datetime NOT NULL, -- maybe Incident has descriptive-Text, DescriptiveText varchar NULL, -- Incident has Incident ID, IncidentID int IDENTITY NOT NULL, -- Incident is of Incident Type and Incident Type has Incident Type Name, IncidentTypeName varchar NOT NULL, PRIMARY KEY(IncidentID), FOREIGN KEY (ClaimNumber) REFERENCES Claim (ClaimNumber)
) GO
CREATE VIEW dbo.Incident_ClaimNumber (ClaimNumber) WITH SCHEMABINDING AS
SELECT ClaimNumber FROM dbo.Incident WHERE ClaimNumber IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX IX_IncidentByClaimNumber ON dbo.Incident_ClaimNumber(ClaimNumber) GO
CREATE TABLE IncidentType (
-- Incident Type has Incident Type Name, IncidentTypeName varchar NOT NULL, PRIMARY KEY(IncidentTypeName)
) GO
CREATE TABLE Insurer (
-- Insurer has Insurer Name, InsurerName varchar NOT NULL, PRIMARY KEY(InsurerName)
) GO
CREATE TABLE Party (
-- Party has Party ID, PartyID int IDENTITY NOT NULL, PRIMARY KEY(PartyID)
) GO
CREATE TABLE Policy (
-- Policy was purchased on Date, Date datetime NOT NULL, -- Policy is from Insurer and Insurer has Insurer Name, InsurerName varchar NOT NULL, -- Policy is held by Policy Holder and Policy Holder is a kind of Stakeholder and Stakeholder is a kind of Party and Party has Party ID, PolicyHolderID int NOT NULL, -- Policy has Policy Number, PolicyNumber Int NOT NULL, -- Policy was purchased from Product Offering and Product Offering has Product Offering Name, ProductOfferingName varchar NOT NULL, PRIMARY KEY(InsurerName, PolicyNumber), FOREIGN KEY (InsurerName) REFERENCES Insurer (InsurerName), FOREIGN KEY (PolicyHolderID) REFERENCES Party (PartyID)
) GO
CREATE TABLE PolicyCoverage (
-- maybe Policy Coverage is at Coverage Level, CoverageLevel Int NULL, -- Policy Coverage involves Coverage Type and Coverage Type has Coverage Type Name, CoverageTypeName varchar NOT NULL, -- Policy Coverage involves Policy and Policy is from Insurer and Insurer has Insurer Name, PolicyInsurerName varchar NOT NULL, -- Policy Coverage involves Policy and Policy has Policy Number, PolicyNumber Int NOT NULL, PRIMARY KEY(PolicyInsurerName, PolicyNumber, CoverageTypeName), FOREIGN KEY (CoverageTypeName) REFERENCES CoverageType (CoverageTypeName), FOREIGN KEY (PolicyInsurerName, PolicyNumber) REFERENCES Policy (InsurerName, PolicyNumber)
) GO
CREATE TABLE ProductOffering (
-- Product Offering is offered by Insurer and Insurer has Insurer Name, InsurerName varchar NOT NULL, -- Product Offering has Product Offering Name, ProductOfferingName varchar NOT NULL, PRIMARY KEY(ProductOfferingName), FOREIGN KEY (InsurerName) REFERENCES Insurer (InsurerName)
) GO
ALTER TABLE AvailableCoverage
ADD FOREIGN KEY (CoverageTypeName) REFERENCES CoverageType (CoverageTypeName)
GO
ALTER TABLE AvailableCoverage
ADD FOREIGN KEY (ProductOfferingName) REFERENCES ProductOffering (ProductOfferingName)
GO
ALTER TABLE ClaimPayment
ADD FOREIGN KEY (ClaimPaymentTypeCode) REFERENCES ClaimPaymentType (ClaimPaymentTypeCode)
GO
ALTER TABLE ClaimPayment
ADD FOREIGN KEY (ClaimDetailsPolicyCoveragePolicyInsurerName, ClaimDetailsPolicyCoveragePolicyNumber, ClaimDetailsPolicyCoverageTypeName) REFERENCES PolicyCoverage (PolicyInsurerName, PolicyNumber, CoverageTypeName)
GO
ALTER TABLE Incident
ADD FOREIGN KEY (IncidentTypeName) REFERENCES IncidentType (IncidentTypeName)
GO
ALTER TABLE Incident
ADD FOREIGN KEY (ClaimantID) REFERENCES Party (PartyID)
GO
ALTER TABLE Policy
ADD FOREIGN KEY (ProductOfferingName) REFERENCES ProductOffering (ProductOfferingName)
GO