CoCalc -- CurrentSchema.txt

GitHub Repository: CloudPak-Outcomes/Outcomes-Projects
Path: blob/main/L4assets/DSandMLOpsAssets/HandsOn/CurrentSchema.txt
¹⁹²⁸ views
1
# ================================
2
# Insurance demo tables
3
# ================================
4
#
5
CREATE TABLE INSURANCE.INSURANCE_HOUSEHOLD (
6
  HOUSEHOLD_ID            CHAR(7) NOT NULL PRIMARY KEY,
7
  ADDR_STREET_NUMBER      VARCHAR(12),
8
  ADDR_STREET_NAME        VARCHAR(60),
9
  ADDR_UNIT_DESIGNATION   VARCHAR(10),
10
  ADDR_UNIT_NUMBER        VARCHAR(10),
11
  CITY                    VARCHAR(30),
12
  STATE                   CHAR(2),
13
  ZIPCODE                 CHAR(5),
14
  FIPS                    CHAR(16),
15
  LATITUDE                DOUBLE,
16
  LONGITUDE               DOUBLE,
17
  PBKEY                   CHAR(12)
18
);
19

20
CREATE TABLE INSURANCE.INSURANCE_DRIVER (
21
  HOUSEHOLD_ID                CHAR(7) NOT NULL,
22
  DRIVER_ID                   CHAR(7) NOT NULL,
23
  FIRST_NAME                  VARCHAR(30),
24
  LAST_NAME                   VARCHAR(30),
25
  GENDER                      CHAR(1),
26
  BIRTHDATE                   DATE,
27
  SSN                         VARCHAR(12),
28
  DRIVERS_LICENSE_ID          VARCHAR(14),
29
  DRIVERS_LICENSE_EXPIRY      DATE,
30
  DRIVERS_LICENSE_STATE       VARCHAR(2),
31
  DATE_AT_CURRENT_ADDRESS     DATE,
32
  CONTACT_NUMBER              CHAR(13),
33
  EMAIL                       VARCHAR(60),
34
  COMMUTE_DISCOUNT            SMALLINT,
35
  WORK_ADDR_NUMB              VARCHAR(12),
36
  WORK_ADDR_STREET_NAME       VARCHAR(60),
37
  WORK_ADDR_UNIT_DESIGNATION  VARCHAR(10),
38
  WORK_ADDR_UNIT_NUMBER       VARCHAR(10),
39
  WORK_ADDR_CITY              VARCHAR(30),
40
  WORK_ADDR_STATE             CHAR(2),
41
  WORK_ADDR_ZIPCODE           CHAR(5),
42
  WORK_ADDR_LATITUDE          DOUBLE,
43
  WORK_ADDR_LONGITUDE         DOUBLE,
44
  WORK_ADDR_PBKEY             CHAR(12),
45
  PRIMARY KEY(HOUSEHOLD_ID, DRIVER_ID)
46
);
47

48
CREATE TABLE INSURANCE.INSURANCE_POLICY (
49
  HOUSEHOLD_ID       CHAR(7) NOT NULL,
50
  PRIMARY_DRIVER_ID  CHAR(7) NOT NULL,
51
  POLICY_ID          CHAR(9) NOT NULL,
52
  START_DATE         DATE    NOT NULL,
53
  EXPIRY_DATE        DATE    NOT NULL,
54
  MODEL_YEAR         INTEGER,
55
  MAKE               VARCHAR(15),
56
  MODEL              VARCHAR(20),
57
  PLATE              VARCHAR(10),
58
  COLOR              VARCHAR(20),
59
  INITIAL_ODOMETER   DECIMAL(10,1),
60
  LOW_MILEAGE_USE    CHAR(1),
61
  PRIMARY KEY(HOUSEHOLD_ID,PRIMARY_DRIVER_ID,POLICY_ID)
62
);
63

64
CREATE TABLE INSURANCE.INSURANCE_INCIDENT_CAUSE (
65
  INCIDENT_ID    INTEGER  NOT NULL,
66
  DESCRIPTION    VARCHAR(30)
67
) ORGANIZED BY ROW;
68

69
insert into insurance.insurance_incident_cause(incident_id, description) 
70
            values(1, 'Driver error');
71
insert into insurance.insurance_incident_cause(incident_id, description) 
72
            values(2, 'Natural causes');
73
insert into insurance.insurance_incident_cause(incident_id, description) 
74
            values(3, 'Other driver error');
75
insert into insurance.insurance_incident_cause(incident_id, description) values(4, 'Crime');
76
insert into insurance.insurance_incident_cause(incident_id, description) values(5, 'Other causes');
77

78
CREATE TABLE INSURANCE.INSURANCE_CLAIM (
79
  HOUSEHOLD_ID         CHAR(7) NOT NULL,
80
  DRIVER_ID            CHAR(7) NOT NULL,
81
  POLICY_ID            CHAR(9) NOT NULL,
82
  CLAIM_ID             CHAR(12) NOT NULL,
83
  INCIDENT_CAUSE       SMALLINT,
84
  DESCRIPTION          VARCHAR(1024),
85
  CLAIM_STATUS         SMALLINT,
86
  ODOMETER_AT_LOSS     DECIMAL(10,1),
87
  LOSS_EVENT_TIME      TIMESTAMP,
88
  CLAIM_INIT_TIME      TIMESTAMP,
89
  POLICE_REPORT        SMALLINT,
90
  CLAIMS_AT_LOSS_DATE  INTEGER,
91
  LOSS_LOCATION_LAT    DOUBLE,
92
  LOSS_LOCATION_LONG   DOUBLE,
93
  CLAIM_AMOUNT         DECIMAL(10,2),
94
  FLAG_FOR_FRAUD_INV   SMALLINT,
95
  PRIMARY KEY(HOUSEHOLD_ID,POLICY_ID,CLAIM_ID)
96
);
97

98
CREATE TABLE INSURANCE.INSURANCE_CLAIM_DETAIL (
99
  HOUSEHOLD_ID                CHAR(7) NOT NULL,
100
  DRIVER_ID                   CHAR(7) NOT NULL,
101
  POLICY_ID                   CHAR(9) NOT NULL,
102
  CLAIM_ID                    CHAR(12) NOT NULL,
103
  INCIDENT_CAUSE              SMALLINT,
104
  DESCRIPTION                 VARCHAR(1024),
105
  CLAIM_STATUS                SMALLINT,
106
  ODOMETER_AT_LOSS            DECIMAL(10,1),
107
  LOSS_EVENT_TIME             TIMESTAMP,
108
  CLAIM_INIT_TIME             TIMESTAMP,
109
  POLICE_REPORT               SMALLINT,
110
  CLAIMS_AT_LOSS_DATE         INTEGER,
111
  LOSS_LOCATION_LAT           DOUBLE,
112
  LOSS_LOCATION_LONG          DOUBLE,
113
  CLAIM_AMOUNT                DECIMAL(10,2),
114
  FLAG_FOR_FRAUD_INV          SMALLINT,
115
  PRIMARY_DRIVER_ID           CHAR(7),
116
  START_DATE                  DATE,
117
  EXPIRY_DATE                 DATE,
118
  MODEL_YEAR                  INTEGER,
119
  MAKE                        VARCHAR(15),
120
  MODEL                       VARCHAR(20),
121
  PLATE                       VARCHAR(10),
122
  COLOR                       VARCHAR(20),
123
  INITIAL_ODOMETER            DECIMAL(10,1),
124
  LOW_MILEAGE_USE             SMALLINT,
125
  FIRST_NAME                  VARCHAR(30),
126
  LAST_NAME                   VARCHAR(30),
127
  GENDER                      CHAR(1),
128
  BIRTHDATE                   DATE,
129
  SSN                         VARCHAR(12),
130
  DRIVERS_LICENSE_ID          VARCHAR(14),
131
  DRIVERS_LICENSE_EXPIRY      DATE,
132
  DRIVERS_LICENSE_STATE       VARCHAR(2),
133
  DATE_AT_CURRENT_ADDRESS     DATE,
134
  CONTACT_NUMBER              CHAR(13),
135
  EMAIL                       VARCHAR(60),
136
  COMMUTE_DISCOUNT            SMALLINT,
137
  WORK_ADDR_NUMB              VARCHAR(12),
138
  WORK_ADDR_STREET_NAME       VARCHAR(60),
139
  WORK_ADDR_UNIT_DESIGNATION  VARCHAR(10),
140
  WORK_ADDR_UNIT_NUMBER       VARCHAR(10),
141
  WORK_ADDR_CITY              VARCHAR(30),
142
  WORK_ADDR_STATE             CHAR(2),
143
  WORK_ADDR_ZIPCODE           CHAR(5),
144
  WORK_ADDR_LATITUDE          DOUBLE,
145
  WORK_ADDR_LONGITUDE         DOUBLE,
146
  WORK_ADDR_PBKEY             CHAR(12),
147
  ADDR_STREET_NUMBER          VARCHAR(12),
148
  ADDR_STREET_NAME            VARCHAR(60),
149
  ADDR_UNIT_DESIGNATION       VARCHAR(10),
150
  ADDR_UNIT_NUMBER            VARCHAR(10),
151
  CITY                        VARCHAR(30),
152
  STATE                       CHAR(2),
153
  ZIPCODE                     CHAR(5),
154
  LATITUDE                    DOUBLE,
155
  LONGITUDE                   DOUBLE,
156
  PBKEY                       CHAR(12),
157
  PRIMARY KEY(HOUSEHOLD_ID,DRIVER_ID,POLICY_ID,CLAIM_ID)
158
);
159

160
CREATE TABLE INSURANCE.INSURANCE_CLAIM_STATUS (
161
  STATUS_ID     INTEGER NOT NULL PRIMARY KEY,
162
  DESCRIPTION   VARCHAR(30)
163
);
164

165
Product

Resources

Company
