In SQL server answer for following questions then apply it and screenshot output
ID: 3818336 • Letter: I
Question
In SQL server answer for following questions then apply it and screenshot output :
- Create all tables and relationship
- Insert values for tables
- Create one index,cube query for each tables
Show transcribed image text
Insurance Claims Dimensional Model Calendar PK Day Date Claims Week Number Month Number PK Claim ID Year Number FK Claim outcome Code FK Customer Policy. ID Date of Claim Date of Settlement Amount Claimed Amount Paid Claims Dimensional Model Total Policies Revenue Total Claims Settlements o PK Fact ID Claim Description FK Claim ID Other Details FK Claim Status Code FA Customer ID FK Date of Claim Customers FK Date of Settlement FK Employee ID PK customer ID FK Policy ID First Name FK Reporting Day.Date Middle Name Amount Claimed Last Name Amount Paid Gender MFU Total Claims Settlements Date of Birth Other Details Medication Medical Conditions Comments Other Details Database Answers Ltd. 2013 Employees o PK Employee ID Employee Details Policies PK Policy ID Policy Type Code Start Date End Date Other Details Ref Claim Status PK Claim Status Code Claim Status Description eg Disputed, in Progress, SettledExplanation / Answer
CREATE TABLE Claims(
Claim_ID varchar(20),
Claim_outcome_code varchar(20),
Customer_Policy_ID varchar(20),
Date_of_Claim Date,
Date_of_Settlement Date,
Amount_Claimed int(50),
Amount_paid int(50),
Total_Policies_revenue varchar(50),
Total_Claims_Settlements varchar(50),
Claim_Description varchar(100),
Other_detalis varchar(200)
CONSTRAINT Claims_pk PRIMARY KEY (Claim_ID),
FOREIGN KEY (Claim_outcome_code) REFERENCES Claim_Dimensional_model(Other_Details),
FOREIGN KEY (Customer_Policy_ID) REFERENCES Customers(Customer_ID)
);
CREATE TABLE Customers(
Customet_ID varchar(20),
First_Name varchar(20),
Middle_Name varchar(20),
Last_Name varchar(20),
Gender_MFU varchar(10),
Date_of_Birth Date,
Medication varchar(20),
Medical_Conditions varchar(20),
Comments varchar(20),
Other_Details varchar(20),
CONSTRAINT Coustomers_pk PRIMARY KEY (Customer_ID)
);
CREATE TABLE Claims_Dimensional_Model(
Fact_ID varchar(20),
Claim_ID varchar(20),
Claim_Status_Code varchar(20),
Customer_ID varchar(20),
Date_of_Claim Date,
Date_of_Settlement Date,
Employee_ID varchar(20),
Policy_ID varchar(20),
Reporting_Day_Date Date,
Amount_Claimed int (100),
Amount_Paid int(100),
Total_Claims_Settlements varchar(20),
Other_Details varchar(20),
CONSTRAINT Claims_Dimensional_Model_pk PRIMARY KEY (Fact_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims(Claim_ID),
FOREIGN KEY (Claim_Status_Code) REFERENCES Ref_Claim_Status(Claim_Status_Code),
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
FOREIGN KEY (Date_of_Claim) REFERENCES Claims(Date_of_Claim),
FOREIGN KEY (Date_of_Settlement) REFERENCES Claims(Date_of_Settlement),
FOREIGN KEY (Employee_ID) REFERENCES Employees(Employee_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies(Policy_ID)
);
CREATE TABLE Calender(
Day_Date date,
Week_Number int(100),
Month_Number int(100),
Year_Number Int(100),
CONSTRAINT Calender_pk PRIMARY KEY (Day_Date)
);
CREATE TABLE Employees(
Employee_ID varchar(20),
Employee_Details varchar(100),
CONSTRAINT Employees_pk PRIMARY KEY (Employee_ID)
);
CREATE TABLE Policies(
Policy_ID varchar(20),
Policy_Type_Code varchar(20),
Start_Date Date,
End_Date Date,
Other_Details varchar(20),
CONSTRAINT Plolicies_pk PRIMARY KEY (Policy_ID)
);
CRETAE TABLE Ref_Claim_Status(
Claim_Status_Code varchar(20),
Claim_Staus_Description Varchar(20),
CONSTRAINT Ref_Claim_Status_pk PRIMARY KEY(Claim_Status_Code)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.