-
Notifications
You must be signed in to change notification settings - Fork 2
/
Create and fill DB.sql
144 lines (134 loc) · 11.3 KB
/
Create and fill DB.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
/*=============== Create all BASIC tables-relations ==================*/
CREATE TABLE ACCIDENT(ST_CASE INT PRIMARY KEY,STATE INT,VE_FORMS INT,PVH_INVL INT,PERNOTMVIT INT,PERMVIT INT,DAY INT,MONTH INT,YEAR INT,DAY_WEEK INT,HOUR INT,MINUTE INT,ROAD_FNC INT,LGT_COND INT,WEATHER INT ,NOT_HOUR INT,NOT_MIN INT,ARR_HOUR INT,ARR_MIN INT,HOSP_HR INT,HOSP_MIN INT);
CREATE TABLE VEHICLE(VEH_ID INT PRIMARY KEY,ST_CASE INT,NUMOCCS INT,HIT_RUN INT,REG_STAT INT,OWNER INT,MAKE INT,MOD_YEAR INT,HAZ_INV INT,TRAV_SP INT,DEFORMED INT,SPEEDREL INT,DEATHS INT,DR_DRINK INT);
CREATE TABLE PERSON(PER_ID INT PRIMARY KEY,ST_CASE INT,VEH_ID INT,VEH_NO INT,AGE INT,SEX INT,PER_TYP INT,INJ_SEV INT,SEAT_POS INT,REST_USE INT,AIR_BAG INT,EJECTION INT,DOA INT,DEATH_DA INT,DEATH_MO INT,DEATH_YR INT);
CREATE TABLE DAMAGE(VEH_ID INT,MDAREAS INT);
CREATE TABLE FACTOR(VEH_ID INT,MFACTOR INT);
CREATE TABLE VIOLATION(VEH_ID INT,MVIOLATN INT);
CREATE TABLE VISION(VEH_ID INT,MVISOBSC INT);
CREATE TABLE MANEUVER(VEH_ID INT,MDRMANAV INT);
CREATE TABLE DISTRACT(VEH_ID INT,MDRDSTRD INT);
CREATE TABLE DRIVER_IMPAIRMENT (VEH_ID INT,DRIMPAIR INT);
CREATE TABLE NON_MOTORIST_IMPAIRMENT (PER_ID INT,NMIMPAIR INT);
CREATE TABLE NON_MOTORIST_CRASH (PER_ID INT,MTM_CRSH INT);
CREATE TABLE NON_MOTORIST_PRIOR (PER_ID INT,MPR_ACT INT);
CREATE TABLE NON_MOTORIST_SAFETY_EQUIPMENT (PER_ID INT,MSAFEQMT INT);
CREATE TABLE AGE (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE AIR_BAG (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE ARR_HOUR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE ARR_MIN (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DAY_WEEK (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DEATH_DA (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DEATH_MO (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DEATH_YR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DEFORMED (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DOA (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DR_DRINK (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE DRIMPAIR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE EJECTION (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE HAZ_INV (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE HIT_RUN (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE HOSP_HR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE HOSP_MIN (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE HOUR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE INJ_SEV (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE LGT_COND (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MAKE(Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MDAREAS (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MDRDSTRD (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MDRMANAV (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MFACTOR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MINUTE (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MONTH (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MPR_ACT (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MSAFEQMT (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MTM_CRSH (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MVIOLATN (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE MVISOBSC (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE NMIMPAIR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE NOT_HOUR (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE NOT_MIN (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE NUMOCCS (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE OWNER(Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE PER_TYP(Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE REG_STAT(Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE REST_USE (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE ROAD_FNC (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE SEAT_POS (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE SEX (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE SPEEDREL (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE STATE (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE TRAV_SP (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE VEH_NO (Code INT PRIMARY KEY, Description TEXT);
CREATE TABLE WEATHER(Code INT PRIMARY KEY, Description TEXT);
/*=====================Fill all BASIC tables=========================*/
\COPY ACCIDENT FROM '/home/db1u36/Data/Data_Files/accident_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY VEHICLE FROM '/home/db1u36/Data/Data_Files/vehicle_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY PERSON FROM '/home/db1u36/Data/Data_Files/person_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DAMAGE FROM '/home/db1u36/Data/Data_Files/damage_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY FACTOR FROM '/home/db1u36/Data/Data_Files/factor_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY VIOLATION FROM '/home/db1u36/Data/Data_Files/violation_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY VISION FROM '/home/db1u36/Data/Data_Files/vision_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MANEUVER FROM '/home/db1u36/Data/Data_Files/maneuver_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DISTRACT FROM '/home/db1u36/Data/Data_Files/distract_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DRIVER_IMPAIRMENT FROM '/home/db1u36/Data/Data_Files/driver_impairment_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NON_MOTORIST_IMPAIRMENT FROM '/home/db1u36/Data/Data_Files/non_motorist_impairment_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NON_MOTORIST_CRASH FROM '/home/db1u36/Data/Data_Files/non_motorist_crash_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NON_MOTORIST_PRIOR FROM '/home/db1u36/Data/Data_Files/non_motorist_prior_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NON_MOTORIST_SAFETY_EQUIPMENT FROM '/home/db1u36/Data/Data_Files/non_motorist_safety_equipment_data.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY AGE FROM '/home/db1u36/Data/Description_Codes/AGE.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY AIR_BAG FROM '/home/db1u36/Data/Description_Codes/AIR_BAG.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY ARR_HOUR FROM '/home/db1u36/Data/Description_Codes/ARR_HOUR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY ARR_MIN FROM '/home/db1u36/Data/Description_Codes/ARR_MIN.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DAY_WEEK FROM '/home/db1u36/Data/Description_Codes/DAY_WEEK.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DEATH_DA FROM '/home/db1u36/Data/Description_Codes/DEATH_DA.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DEATH_MO FROM '/home/db1u36/Data/Description_Codes/DEATH_MO.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DEATH_YR FROM '/home/db1u36/Data/Description_Codes/DEATH_YR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DEFORMED FROM '/home/db1u36/Data/Description_Codes/DEFORMED.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DOA FROM '/home/db1u36/Data/Description_Codes/DOA.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DR_DRINK FROM '/home/db1u36/Data/Description_Codes/DR_DRINK.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY DRIMPAIR FROM '/home/db1u36/Data/Description_Codes/DRIMPAIR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY EJECTION FROM '/home/db1u36/Data/Description_Codes/EJECTION.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY HAZ_INV FROM '/home/db1u36/Data/Description_Codes/HAZ_INV.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY HIT_RUN FROM '/home/db1u36/Data/Description_Codes/HIT_RUN.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY HOSP_HR FROM '/home/db1u36/Data/Description_Codes/HOSP_HR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY HOSP_MIN FROM '/home/db1u36/Data/Description_Codes/HOSP_MIN.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY HOUR FROM '/home/db1u36/Data/Description_Codes/HOUR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY INJ_SEV FROM '/home/db1u36/Data/Description_Codes/INJ_SEV.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY LGT_COND FROM '/home/db1u36/Data/Description_Codes/LGT_COND.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MAKE FROM '/home/db1u36/Data/Description_Codes/MAKE.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MDAREAS FROM '/home/db1u36/Data/Description_Codes/MDAREAS.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MDRDSTRD FROM '/home/db1u36/Data/Description_Codes/MDRDSTRD.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MDRMANAV FROM '/home/db1u36/Data/Description_Codes/MDRMANAV.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MFACTOR FROM '/home/db1u36/Data/Description_Codes/MFACTOR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MINUTE FROM '/home/db1u36/Data/Description_Codes/MINUTE.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MONTH FROM '/home/db1u36/Data/Description_Codes/MONTH.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MPR_ACT FROM '/home/db1u36/Data/Description_Codes/MPR_ACT.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MSAFEQMT FROM '/home/db1u36/Data/Description_Codes/MSAFEQMT.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MTM_CRSH FROM '/home/db1u36/Data/Description_Codes/MTM_CRSH.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MVIOLATN FROM '/home/db1u36/Data/Description_Codes/MVIOLATN.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY MVISOBSC FROM '/home/db1u36/Data/Description_Codes/MVISOBSC.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NMIMPAIR FROM '/home/db1u36/Data/Description_Codes/NMIMPAIR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NOT_HOUR FROM '/home/db1u36/Data/Description_Codes/NOT_HOUR.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NOT_MIN FROM '/home/db1u36/Data/Description_Codes/NOT_MIN.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY NUMOCCS FROM '/home/db1u36/Data/Description_Codes/NUMOCCS.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY OWNER FROM '/home/db1u36/Data/Description_Codes/OWNER.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY PER_TYP FROM '/home/db1u36/Data/Description_Codes/PER_TYP.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY REG_STAT FROM '/home/db1u36/Data/Description_Codes/REG_STAT.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY REST_USE FROM '/home/db1u36/Data/Description_Codes/REST_USE.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY ROAD_FNC FROM '/home/db1u36/Data/Description_Codes/ROAD_FNC.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY SEAT_POS FROM '/home/db1u36/Data/Description_Codes/SEAT_POS.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY SEX FROM '/home/db1u36/Data/Description_Codes/SEX.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY SPEEDREL FROM '/home/db1u36/Data/Description_Codes/SPEEDREL.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY STATE FROM '/home/db1u36/Data/Description_Codes/STATE.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY TRAV_SP FROM '/home/db1u36/Data/Description_Codes/TRAV_SP.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY VEH_NO FROM '/home/db1u36/Data/Description_Codes/VEH_NO.txt' WITH CSV HEADER DELIMITER AS ',';
\COPY WEATHER FROM '/home/db1u36/Data/Description_Codes/WEATHER.txt' WITH CSV HEADER DELIMITER AS ',';
/*=============== Create and fill extra relations ===================*/
CREATE TABLE NON_BOARD AS SELECT PER_ID, ST_CASE FROM PERSON WHERE VEH_NO=0;
CREATE TABLE BOARD AS SELECT PER_ID, VEH_ID FROM PERSON WHERE VEH_NO=1;
CREATE TABLE INVOLVE AS SELECT VEH_ID, ST_CASE FROM VEHICLE;
/*================== Change some tables =============================*/
ALTER TABLE VEHICLE DROP ST_CASE;
ALTER TABLE PERSON DROP ST_CASE;
ALTER TABLE PERSON DROP VEH_ID;