Database Entity

[pushoo-sharma]

User:

Attributes: user_id (Primary Key) first_name last_name date_of_birth gender contact_number email address Type: admin or policyholder

Policyholder:

Attributes: policyholder_id (Primary Key) user_id (Foreign Key referencing User) Associations: Each Policyholder is associated with a User.

Insurance Policy:

Attributes: policy_id (Primary Key) start_date end_date coverage_type premium_amount deductible_amount status Associations: Each Insurance Policy is associated with a Policyholder (policyholder_id as Foreign Key).

Healthcare Provider:

Attributes: provider_id (Primary Key) name address contact_number email

Claim:

Attributes: claim_id (Primary Key) policy_id (Foreign Key referencing Insurance Policy) claim_date diagnosis treatment_cost status Associations: Each Claim is associated with an Insurance Policy (policy_id as Foreign Key).

[pushoo-sharma]

User Query

CREATE TABLE Users ( user_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), date_of_birth DATE, gender CHAR(1), contact_number VARCHAR(15), email VARCHAR(100), address VARCHAR(255), user_type VARCHAR(20) CHECK (user_type IN ('admin', 'policyholder')) );

INSERT INTO Users VALUES (1, 'John', 'Doe', '1990-05-15', 'M', '1234567890', 'john.doe@example.com', '123 Main St, Cityville', 'admin'), (2, 'Jane', 'Smith', '1985-08-22', 'F', '9876543210', 'jane.smith@example.com', '456 Oak St, Townsville', 'policyholder'), (3, 'Bob', 'Johnson', '1978-12-10', 'M', '5555555555', 'bob.johnson@example.com', '789 Pine St, Villagetown', 'policyholder'), (4, 'Alice', 'Williams', '1992-03-25', 'F', '3333333333', 'alice.williams@example.com', '101 Cedar St, Hamletville', 'admin'), (5, 'Charlie', 'Brown', '1980-06-05', 'M', '4444444444', 'charlie.brown@example.com', '202 Maple St, Countryside', 'policyholder');

[pushoo-sharma]

CREATE TABLE Policyholders ( policyholder_id SERIAL PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES Users(user_id) );

INSERT INTO Policyholders (user_id) VALUES (1), -- Assuming user_id 1 corresponds to John Doe (2), -- Assuming user_id 2 corresponds to Jane Smith (3), -- Assuming user_id 3 corresponds to Bob Johnson (5);

[pushoo-sharma]

CREATE TABLE InsurancePolicy ( policy_id SERIAL PRIMARY KEY, start_date DATE, end_date DATE, coverage_type VARCHAR(255), -- Adjust the size based on your requirements premium_amount DECIMAL(10, 2), -- Assuming a decimal type for currency, adjust as needed deductible_amount DECIMAL(10, 2), -- Assuming a decimal type for currency, adjust as needed status VARCHAR(50), -- Adjust the size based on your requirements policyholder_id INT, FOREIGN KEY (policyholder_id) REFERENCES Policyholders(policyholder_id) );

INSERT INTO InsurancePolicy (start_date, end_date, coverage_type, premium_amount, deductible_amount, status, policyholder_id) VALUES ('2023-01-01', '2023-12-31', 'Health', 1000.00, 250.00, 'Active', 1), -- Assuming policyholder_id 1 exists (John Doe) ('2023-02-15', '2024-02-14', 'Auto', 800.00, 200.00, 'Pending', 2), -- Assuming policyholder_id 2 exists (Jane Smith) ('2023-03-10', '2024-03-09', 'Life', 1200.00, 300.00, 'Active', 3), -- Assuming policyholder_id 3 exists (Bob Johnson) ('2023-04-20', '2024-04-19', 'Health', 900.00, 200.00, 'Active', 4); -- Assuming policyholder_id 4 exists (Alice Williams)

[pushoo-sharma]

-- Create HealthcareProvider table CREATE TABLE HealthcareProvider ( provider_id SERIAL PRIMARY KEY, name VARCHAR(100), address VARCHAR(255), contact_number VARCHAR(15), email VARCHAR(100) );

-- Insert dummy data into HealthcareProvider table INSERT INTO HealthcareProvider (name, address, contact_number, email) VALUES ('City Hospital', '123 Medical St, Cityville', '555-1234', 'city.hospital@example.com'), ('Town Clinic', '456 Health St, Townsville', '555-5678', 'town.clinic@example.com'), ('Village Medical Center', '789 Care St, Villagetown', '555-9876', 'village.medical@example.com');

[pushoo-sharma]

-- Create Claim table CREATE TABLE Claim ( claim_id SERIAL PRIMARY KEY, policy_id INT, FOREIGN KEY (policy_id) REFERENCES InsurancePolicy(policy_id), claim_date DATE, diagnosis VARCHAR(255), -- Adjust the size based on your requirements treatment_cost DECIMAL(10, 2), -- Assuming a decimal type for currency, adjust as needed status VARCHAR(50) -- Adjust the size based on your requirements );

-- Insert dummy data into Claim table INSERT INTO Claim (policy_id, claim_date, diagnosis, treatment_cost, status) VALUES (11, '2023-02-01', 'Routine Checkup', 150.00, 'Pending'), -- Assuming policy_id 1 corresponds to John Doe's Health policy (12, '2023-03-15', 'Auto Accident Injury', 2500.00, 'Approved'), -- Assuming policy_id 2 corresponds to Jane Smith's Auto policy (13, '2023-04-10', 'Flu Treatment', 100.00, 'Pending'), -- Assuming policy_id 3 corresponds to Bob Johnson's Life policy (14, '2023-05-05', 'Dental Cleaning', 80.00, 'Pending'); -- Assuming policy_id 4 corresponds to Alice Williams's Health policy