-
Notifications
You must be signed in to change notification settings - Fork 0
/
createTable.sql
91 lines (81 loc) · 2.37 KB
/
createTable.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
-- a table containing basic information of the accident
create table if not exists Accident
(
AID char(20) primary key,
Source char(20),
TMC char(5),
Severity smallint,
StartTime datetime,
EndTime datetime,
Distance float,
Description varchar(255)
);
-- a table recording location information of an accident
create table if not exists Location
(
LID integer not null auto_increment primary key,
LAID char(20) not null,
StartLat double null,
StartLng double null,
EndLat double null,
EndLng double null,
_Number char(16) null,
Street varchar(64) null,
Side char(1) null,
City varchar(64) null,
County varchar(64) null,
_State varchar(64) null,
Zipcode varchar(64) null,
Country varchar(64) null,
Timezone varchar(64) null,
AirportCode varchar(64) null,
foreign key (LAID) references Accident(AID) on delete cascade
);
-- a table containing weather information
create table if not exists WeatherCondition
(
WID integer not null auto_increment primary key,
WAID char(20) not null,
WeatherTimeStamp datetime null,
Temperature decimal(4, 1) null,
WindChill decimal(4, 1) null,
Humidity decimal(4, 1) null,
Pressure decimal(4, 2) null,
Visibility decimal(4,1) null,
WindDirection char(16) null,
WindSpeed decimal(5, 2) null,
Precipitation decimal(3, 2) null,
WeatherCondition varchar(64) null,
foreign key (WAID) references Accident(AID) on delete cascade
);
-- a table containing information of nearby point-of-interst
create table if not exists PointOfInterst
(
PID integer not null auto_increment primary key,
PAID char(20) not null,
Amenity char(8) null,
Bump char(8) null,
Crossing char(8) null,
GiveWay char(8) null,
Junction char(8) null,
NoExit char(8) null,
RailWay char(8) null,
RoundAbout char(8) null,
Station char(8) null,
Stop char(8) null,
TrafficCalmig char(8) null,
TrafficSignal char(8) null,
TurningLoop char(8) null,
foreign key (PAID) references Accident(AID) on delete cascade
);
-- a table containing twilight information
create table if not exists Twilight
(
TID integer not null auto_increment primary key,
TAID char(20) not null,
SunriseSunset char(16) null,
CivilTwilight char(16) null,
NauticalTwilight char(16) null,
AstronomicalTwilight char(16) null,
foreign key (TAID) references Accident(AID) on delete cascade
);