SQLQuery_dbInsurance.sql

CREATE DATABASE db_Insurance

USE db_Insurance


-- 1.Branch

CREATE TABLE Tbl_Branch
(
    BranchID INT PRIMARY KEY ,
    Address NVARCHAR(300) not null,
    Phone VARCHAR(200) not null --چندمقداری
)


-- 2.Agent

CREATE TABLE Tbl_Agent
(
    AgentID INT PRIMARY KEY,
    FullName NVARCHAR(100) not null,
    HireDate DATE,
    Phone VARCHAR(200) not null, --چندمقداری

    BranchID_FK INT not null, --رابطه 1 به چند

  
        FOREIGN KEY (BranchID_FK) REFERENCES Tbl_Branch(BranchID)
)


-- 3.Customer

CREATE TABLE Tbl_Customer
(
    CustomerID INT PRIMARY KEY,
    FullName NVARCHAR(50) not null,
    NationalCode NCHAR(10) UNIQUE not null,
    Address NVARCHAR(300) not null,
    Phone VARCHAR(200) not null --چندمقداری
)


-- 4.InsuranceType

CREATE TABLE Tbl_InsuranceType
(
    InsuranceTypeID INT PRIMARY KEY,
    TypeName NVARCHAR(100) not null
)


-- 5.InsurancePolicy

CREATE TABLE Tbl_InsurancePolicy
(
    PolicyID INT PRIMARY KEY,
    StartDate DATE not null,
    EndDate DATE not null,
    PremiumAmount DECIMAL(18,2) not null,
    Status NVARCHAR(50) not null,
    InsuranceTypeID_FK INT not null,--رابطه 1 به چند

        FOREIGN KEY (InsuranceTypeID_FK) REFERENCES Tbl_InsuranceType(InsuranceTypeID)
)


-- 6.Payment

CREATE TABLE Tbl_Payment
(
    PaymentID INT PRIMARY KEY,
    Amount DECIMAL(18,2) not null,
    PaymentDate DATE not null,
    PaymentMethod NVARCHAR(50) not null,

    PolicyID_FK INT not null,

        FOREIGN KEY (PolicyID_FK) REFERENCES Tbl_InsurancePolicy(PolicyID)
)


-- 7.Installment/اقساط

CREATE TABLE Tbl_Installment
(
    InstallmentID INT PRIMARY KEY,
    DueDate DATE not null, -- تاریخ سررسید
    Amount DECIMAL(18,2) not null,
    Status NVARCHAR(50),

    PaymentID_FK INT, -- رابطه 1 به چند

        FOREIGN KEY (PaymentID_FK) REFERENCES Tbl_Payment(PaymentID)
)


-- 8.Accident

CREATE TABLE Tbl_Accident
(
    AccidentID INT PRIMARY KEY,
    AccidentDate DATE not null,
    Location NVARCHAR(200) not null,
    Status NVARCHAR(50) not null
)


-- 9.Damage

CREATE TABLE Tbl_Damage
(
    DamageID INT PRIMARY KEY,
    DamageDate DATE not null,
    Amount DECIMAL(18,2) not null,
    Status NVARCHAR(50) not null,

    PolicyID_FK INT not null, -- رابطه 1 به چند
    AccidentID_FK INT not null, -- رابطه یک به چند

        FOREIGN KEY (PolicyID_FK) REFERENCES Tbl_InsurancePolicy(PolicyID),
        FOREIGN KEY (AccidentID_FK) REFERENCES Tbl_Accident(AccidentID)
)


-- 10.Contract

CREATE TABLE Tbl_Contract
(
    ContractID INT,
    Status NVARCHAR(50) not null,
    ContractDuration INT not null, -- مدت قرارداد
    ContractDate DATE not null,

    CustomerID_FK INT, -- رابطه 1 به چند
    PolicyID_FK INT UNIQUE not null, -- موجودیت ضعیف برای وجود داشتن به موجودیت مالک وابسته است
    PRIMARY KEY (PolicyID_FK, ContractID),
        FOREIGN KEY (CustomerID_FK) REFERENCES Tbl_Customer(CustomerID),
        FOREIGN KEY (PolicyID_FK) REFERENCES Tbl_InsurancePolicy(PolicyID)
)


-- 11.Issuance/صدور

CREATE TABLE Tbl_Issuance 
(
    AgentID_FK INT,
    PolicyID_FK INT,

    PRIMARY KEY (AgentID_FK, PolicyID_FK),-- بیشتر از یکی کلید اصلی داریم

        FOREIGN KEY (AgentID_FK) REFERENCES Tbl_Agent(AgentID),
        FOREIGN KEY (PolicyID_FK) REFERENCES Tbl_InsurancePolicy(PolicyID)
)

-- 12.Registration

CREATE TABLE Tbl_Registration
(
    CustomerID_FK INT,
    PolicyID_FK INT,

    PRIMARY KEY (CustomerID_FK, PolicyID_FK),

        FOREIGN KEY (CustomerID_FK) REFERENCES Tbl_Customer(CustomerID),
        FOREIGN KEY (PolicyID_FK) REFERENCES Tbl_InsurancePolicy(PolicyID)
)

DROP TABLE Tbl_Contract