ITCS Topic 3 CQL Query Assignment 6160 In this assignment, you will use a version of the insurance database we have been using throughout the learning materials for this Development course. 0 Schema for this assignment For this assignment, use the conceptual model and the Cassandra column families given below. For this assignment, ONLY use the column families described below. DRIVER id (PK) INT lastName VARCHAR(50) firstName VARCHAR(50) dateOfBirth DATE gender VARCHAR(10) POLICY_POLICYHOLDER OpolicyholderID (PK, FK) INT policyID (PK, FK) INT purchases is covered on POLICYHOLDER id (PK) INT lastName VARCHAR(50) firstName VARCHAR(50) phone CHAR(10) email VARCHAR(100) DRIVER_POLICY policyID (PK, FK) INT driverID (PK, FK) INT id (PK) type limit COVERAGE INT VARCHAR(50) DECIMAL(10,2) id (PK) lastName firstName Iphone email AGENT INT VARCHAR(50) sells VARCHAR(50) CHAR(10) VARCHAR(100) POLICY id (PK) INT type VARCHAR(50) effectiveDate DATE expiration Date DATE agentID (FK) INT deductible DECIMAL COVERAGE_POLICY coveragelD (PK, FK) INT policyID (PK, FK) INT is on falls under is filed on INT + is for CLAIM id (PK) 04incident_desc text incident_date date at_fault bool status text EXPENSE id (PK) INT Odate_requested date date_disbursed date amount double pay_to text purpose text status text o o POLICY_COVERAGES o policylD o policyholderID o policyholder_firstname, policyholder_lastname o policyeffectivedate policyexpirationdate o coverageld o coveragetype o coveragelimit - PRIMARY KEY (policyID, coveragetype, coveragelimit, coverageld) . POLICYHOLDER PAYOUT o policyholderID e policyholder_firstname, policyholder_lastname, policyholder_email, policyholder_phone o policylD o policytype policyeffectivedate o policyexpirationdate o policy deductible o claim incident date o claim status o expenseld o expense date disbursed o expense amount o coverage type o coverage limit o PRIMARY KEY (policyholderID, policylD, policydeductible, claimstatus, expenseld) o o ACTIVE_CLAIMS_BY_POLICYHOLDER . Same columns as CLIENT_PAYOUT but different primary key o PRIMARY KEY ((policyholderID, policylD, claimstatus), expenselD) ACTIVE CLAIMS o Same columns as CLIENT_PAYOUT but different primary key - PRIMARY KEY ((claimstatus, policyholderID), policylD, expenselD) To turn in Write the CQL queries needed to meet each requirement below. Or, explain why a CQL query using the given column families cannot be written using only the column families provided. 1. For a specific policy holder, list all policy IDs and their respective effective and expiration dates. 2. For a specific policy holder, list all policy IDs, arranged by expiration date from newest to oldest. 3. For a specific policy holder, list all expense types and amounts. 4. For a specific policy holder, list all expense types and amounts associated with claims that have a status of 'Open! 5. For a specific policy holder, give the total (sum) of all expenses. 6. For a specific policy holder, give the total (sum) of all expenses that have a disbursement date (not null). 7. For a specific policy, give the total (sum) of all coverages with a type of 'Personal property'. 8. For a specific policy, give the total (sum) of all expenses. 9. Show the policy ID for all policies with an effective date after January 1, 2022. 10. Show the ID for any policy that has a claim with a status of 'Open. Also include the policy holder information of first and last name, email address, and phone number. Submit your solutions as a .cal file. Put the problem number as a comment before each solution. For example: // Problem : Show all user information. // Not possible because..... // Problem 1: Show all project information. SELECT ... ITCS Topic 3 CQL Query Assignment 6160 In this assignment, you will use a version of the insurance database we have been using throughout the learning materials for this Development course. 0 Schema for this assignment For this assignment, use the conceptual model and the Cassandra column families given below. For this assignment, ONLY use the column families described below. DRIVER id (PK) INT lastName VARCHAR(50) firstName VARCHAR(50) dateOfBirth DATE gender VARCHAR(10) POLICY_POLICYHOLDER OpolicyholderID (PK, FK) INT policyID (PK, FK) INT purchases is covered on POLICYHOLDER id (PK) INT lastName VARCHAR(50) firstName VARCHAR(50) phone CHAR(10) email VARCHAR(100) DRIVER_POLICY policyID (PK, FK) INT driverID (PK, FK) INT id (PK) type limit COVERAGE INT VARCHAR(50) DECIMAL(10,2) id (PK) lastName firstName Iphone email AGENT INT VARCHAR(50) sells VARCHAR(50) CHAR(10) VARCHAR(100) POLICY id (PK) INT type VARCHAR(50) effectiveDate DATE expiration Date DATE agentID (FK) INT deductible DECIMAL COVERAGE_POLICY coveragelD (PK, FK) INT policyID (PK, FK) INT is on falls under is filed on INT + is for CLAIM id (PK) 04incident_desc text incident_date date at_fault bool status text EXPENSE id (PK) INT Odate_requested date date_disbursed date amount double pay_to text purpose text status text o o POLICY_COVERAGES o policylD o policyholderID o policyholder_firstname, policyholder_lastname o policyeffectivedate policyexpirationdate o coverageld o coveragetype o coveragelimit - PRIMARY KEY (policyID, coveragetype, coveragelimit, coverageld) . POLICYHOLDER PAYOUT o policyholderID e policyholder_firstname, policyholder_lastname, policyholder_email, policyholder_phone o policylD o policytype policyeffectivedate o policyexpirationdate o policy deductible o claim incident date o claim status o expenseld o expense date disbursed o expense amount o coverage type o coverage limit o PRIMARY KEY (policyholderID, policylD, policydeductible, claimstatus, expenseld) o o ACTIVE_CLAIMS_BY_POLICYHOLDER . Same columns as CLIENT_PAYOUT but different primary key o PRIMARY KEY ((policyholderID, policylD, claimstatus), expenselD) ACTIVE CLAIMS o Same columns as CLIENT_PAYOUT but different primary key - PRIMARY KEY ((claimstatus, policyholderID), policylD, expenselD) To turn in Write the CQL queries needed to meet each requirement below. Or, explain why a CQL query using the given column families cannot be written using only the column families provided. 1. For a specific policy holder, list all policy IDs and their respective effective and expiration dates. 2. For a specific policy holder, list all policy IDs, arranged by expiration date from newest to oldest. 3. For a specific policy holder, list all expense types and amounts. 4. For a specific policy holder, list all expense types and amounts associated with claims that have a status of 'Open! 5. For a specific policy holder, give the total (sum) of all expenses. 6. For a specific policy holder, give the total (sum) of all expenses that have a disbursement date (not null). 7. For a specific policy, give the total (sum) of all coverages with a type of 'Personal property'. 8. For a specific policy, give the total (sum) of all expenses. 9. Show the policy ID for all policies with an effective date after January 1, 2022. 10. Show the ID for any policy that has a claim with a status of 'Open. Also include the policy holder information of first and last name, email address, and phone number. Submit your solutions as a .cal file. Put the problem number as a comment before each solution. For example: // Problem : Show all user information. // Not possible because..... // Problem 1: Show all project information. SELECT