-
Notifications
You must be signed in to change notification settings - Fork 0
/
SplitComplaints.sql
339 lines (281 loc) · 12.7 KB
/
SplitComplaints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
/*==============================================================*/
/* DBMS name: PostgreSQL 14.x */
/* Created on: 08.01.2024 20:50:03 */
/*==============================================================*/
drop index if exists CAR_PK CASCADE;
drop table if exists Car CASCADE;
drop index if exists ASSOCIATION_4_FK CASCADE;
drop index if exists ASSOCIATION_3_FK CASCADE;
drop index if exists ASSOCIATION_2_FK CASCADE;
drop index if exists CASE_PK CASCADE;
drop table if exists "Case" CASCADE;
drop index if exists CHILDSEAT_PK CASCADE;
drop table if exists ChildSeat CASCADE;
drop index if exists ASSOCIATION_1_FK CASCADE;
drop index if exists COMPLAINTID_PK CASCADE;
drop table if exists ComplaintID CASCADE;
drop index if exists DEALER_PK CASCADE;
drop table if exists Dealer CASCADE;
drop index if exists EQUIPMENT_PK CASCADE;
drop table if exists Equipment CASCADE;
drop index if exists FAILPLACE_PK CASCADE;
drop table if exists FailPlace CASCADE;
drop index if exists TIRE_PK CASCADE;
drop table if exists Tire CASCADE;
drop index if exists VEHICLE_PK CASCADE;
drop table if exists Vehicle CASCADE;
/*==============================================================*/
/* Table: Car */
/*==============================================================*/
create table Car (
MAKETXT varchar(254) not null,
MODELTXT text not null,
YEARTXT integer not null,
MFR_NAME varchar(254),
constraint PK_CAR primary key (MAKETXT, MODELTXT, YEARTXT)
);
/*==============================================================*/
/* Index: CAR_PK */
/*==============================================================*/
create unique index CAR_PK on Car (
MAKETXT,
MODELTXT,
YEARTXT
);
/*==============================================================*/
/* Table: "Case" */
/*==============================================================*/
create table "Case" (
ODINO integer not null,
DEALER_NAME varchar(254),
DEALER_ZIP varchar(254),
MAKETXT varchar(254) not null,
MODELTXT text not null,
YEARTXT integer not null,
CITY varchar(254),
"STATE" varchar(254),
CRASH boolean,
FAILDATE date,
FIRE boolean,
INJURED integer,
DEATHS integer,
DATEA date,
LDATE date,
OCCURENCES double precision,
COMPDESC varchar(254),
CDESCR text,
MEDICAL_ATTN boolean,
POLICE_RPT_YN boolean,
PURCH_DT date,
constraint PK_CASE primary key (ODINO)
);
/*==============================================================*/
/* Index: CASE_PK */
/*==============================================================*/
create unique index CASE_PK on "Case" (
ODINO
);
/*==============================================================*/
/* Index: ASSOCIATION_2_FK */
/*==============================================================*/
create index ASSOCIATION_2_FK on "Case" (
DEALER_NAME,
DEALER_ZIP
);
/*==============================================================*/
/* Index: ASSOCIATION_3_FK */
/*==============================================================*/
create index ASSOCIATION_3_FK on "Case" (
CITY,
"STATE"
);
/*==============================================================*/
/* Index: ASSOCIATION_4_FK */
/*==============================================================*/
create index ASSOCIATION_4_FK on "Case" (
MAKETXT,
MODELTXT,
YEARTXT
);
/*==============================================================*/
/* Table: ChildSeat */
/*==============================================================*/
create table ChildSeat (
ODINO integer not null,
CMPLID integer not null,
RESTRAINT_TYPE text,
MANUF_DT date,
SEAT_TYPE varchar(254),
constraint PK_CHILDSEAT primary key (ODINO, CMPLID)
);
/*==============================================================*/
/* Index: CHILDSEAT_PK */
/*==============================================================*/
create unique index CHILDSEAT_PK on ChildSeat (
ODINO,
CMPLID
);
/*==============================================================*/
/* Table: ComplaintID */
/*==============================================================*/
create table ComplaintID (
ODINO integer not null,
CMPLID integer not null,
COMPDESC varchar(254),
PROD_TYPE text,
constraint PK_COMPLAINTID primary key (ODINO, CMPLID)
);
/*==============================================================*/
/* Index: COMPLAINTID_PK */
/*==============================================================*/
create unique index COMPLAINTID_PK on ComplaintID (
ODINO,
CMPLID
);
/*==============================================================*/
/* Index: ASSOCIATION_1_FK */
/*==============================================================*/
create index ASSOCIATION_1_FK on ComplaintID (
ODINO
);
/*==============================================================*/
/* Table: Dealer */
/*==============================================================*/
create table Dealer (
DEALER_NAME varchar(254) not null,
DEALER_TEL varchar(254),
DEALER_CITY varchar(254) not null,
DEALER_STATE varchar(254) not null,
DEALER_ZIP text not null,
constraint PK_DEALER primary key (DEALER_NAME, DEALER_ZIP)
);
/*==============================================================*/
/* Index: DEALER_PK */
/*==============================================================*/
create unique index DEALER_PK on Dealer (
DEALER_NAME,
DEALER_ZIP
);
/*==============================================================*/
/* Table: Equipment */
/*==============================================================*/
create table Equipment (
ODINO integer not null,
CMPLID integer not null,
ORIG_OWNER_YN boolean,
constraint PK_EQUIPMENT primary key (ODINO, CMPLID)
);
/*==============================================================*/
/* Index: EQUIPMENT_PK */
/*==============================================================*/
create unique index EQUIPMENT_PK on Equipment (
ODINO,
CMPLID
);
/*==============================================================*/
/* Table: FailPlace */
/*==============================================================*/
create table FailPlace (
CITY text not null,
"STATE" text not null,
constraint PK_FAILPLACE primary key (CITY, "STATE")
);
/*==============================================================*/
/* Index: FAILPLACE_PK */
/*==============================================================*/
create unique index FAILPLACE_PK on FailPlace (
CITY,
"STATE"
);
/*==============================================================*/
/* Table: Tire */
/*==============================================================*/
create table Tire (
ODINO integer not null,
CMPLID integer not null,
DOT varchar(254),
TIRE_SIZE varchar(254),
LOC_OF_TIRE varchar(254),
TIRE_FAIL_TYPE varchar(254),
REPAIRED_YN boolean,
constraint PK_TIRE primary key (ODINO, CMPLID)
);
/*==============================================================*/
/* Index: TIRE_PK */
/*==============================================================*/
create unique index TIRE_PK on Tire (
ODINO,
CMPLID
);
/*==============================================================*/
/* Table: Vehicle */
/*==============================================================*/
create table Vehicle (
ODINO integer not null,
CMPLID integer not null,
VIN varchar(254),
MILES double precision,
ANTI_BRAKES_YN boolean,
CRUISE_CONT_YN boolean,
NUM_CYLS integer,
DRIVE_TRAIN varchar(254),
FUEL_SYS varchar(254),
FUEL_TYPE varchar(254),
TRANS_TYPE varchar(254),
VEH_SPEED double precision,
VEHICLES_TOWED_YN boolean,
constraint PK_VEHICLE primary key (ODINO, CMPLID)
);
/*==============================================================*/
/* Index: VEHICLE_PK */
/*==============================================================*/
create unique index VEHICLE_PK on Vehicle (
ODINO,
CMPLID
);
alter table "Case"
add constraint FK_CASE_ASSOCIATI_DEALER foreign key (DEALER_NAME, DEALER_ZIP)
references Dealer (DEALER_NAME, DEALER_ZIP)
on delete restrict on update restrict;
alter table "Case"
add constraint FK_CASE_ASSOCIATI_FAILPLAC foreign key (CITY, "STATE")
references FailPlace (CITY, "STATE")
on delete restrict on update restrict;
alter table "Case"
add constraint FK_CASE_ASSOCIATI_CAR foreign key (MAKETXT, MODELTXT, YEARTXT)
references Car (MAKETXT, MODELTXT, YEARTXT)
on delete restrict on update restrict;
alter table ChildSeat
add constraint FK_CHILDSEA_GENERALIZ_COMPLAIN foreign key (ODINO, CMPLID)
references ComplaintID (ODINO, CMPLID)
on delete restrict on update restrict;
alter table ComplaintID
add constraint FK_COMPLAIN_ASSOCIATI_CASE foreign key (ODINO)
references "Case" (ODINO)
on delete restrict on update restrict;
alter table Equipment
add constraint FK_EQUIPMEN_GENERALIZ_COMPLAIN foreign key (ODINO, CMPLID)
references ComplaintID (ODINO, CMPLID)
on delete restrict on update restrict;
alter table Tire
add constraint FK_TIRE_GENERALIZ_COMPLAIN foreign key (ODINO, CMPLID)
references ComplaintID (ODINO, CMPLID)
on delete restrict on update restrict;
alter table Vehicle
add constraint FK_VEHICLE_GENERALIZ_COMPLAIN foreign key (ODINO, CMPLID)
references ComplaintID (ODINO, CMPLID)
on delete restrict on update restrict;
Alter Table public.complaints Alter Column "CRASH" TYPE boolean USING "CRASH"::boolean;
Alter Table public.complaints Alter Column "FIRE" TYPE boolean USING "FIRE"::boolean;
Alter Table public.complaints Alter Column "POLICE_RPT_YN" TYPE boolean USING "POLICE_RPT_YN"::boolean;
Alter Table public.complaints Alter Column "ORIG_OWNER_YN" TYPE boolean USING "ORIG_OWNER_YN"::boolean;
Alter Table public.complaints Alter Column "ANTI_BRAKES_YN" TYPE boolean USING "ANTI_BRAKES_YN"::boolean;
Alter Table public.complaints Alter Column "CRUISE_CONT_YN" TYPE boolean USING "CRUISE_CONT_YN"::boolean;
Alter Table public.complaints Alter Column "ORIG_EQUIP_YN" TYPE boolean USING "ORIG_EQUIP_YN"::boolean;
Alter Table public.complaints Alter Column "REPAIRED_YN" TYPE boolean USING "REPAIRED_YN"::boolean;
Alter Table public.complaints Alter Column "VEHICLE_TOWED_YN" TYPE boolean USING "VEHICLE_TOWED_YN"::boolean;
Alter Table public.complaints Alter Column "MEDICAL_ATTN" TYPE boolean USING "MEDICAL_ATTN"::boolean;
INSERT INTO "Case"
SELECT DISTINCT "ODINO", "DEALER_NAME", "DEALER_ZIP", "MAKETXT", "MODELTXT", "YEARTXT", "CITY", "STATE", "CRASH", "FAILDATE", "FIRE", "INJURED",
"DEATHS", "DATEA", "LDATE", "OCCURENCES", "COMPDESC", "CDESCR", "MEDICAL_ATTN", "POLICE_RPT_YN", "PURCH_DT"
FROM complaints;