-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path2010 Database Schema.txt
184 lines (170 loc) · 10.9 KB
/
2010 Database Schema.txt
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
--2011 VIN DECODE
-- VIN '3' Vehicle Type
YEAR, VIN_CODE, VEHICLE_CLASS_ID, SALES_CODE, FAMILY_ID
insert into VIN03 values(2011,'4', 13 , NULL, NULL);
insert into VIN03 values(2011,'6', 15, null, null);
insert into VIN03 values(2011,'7' , 'Truck');
-- VIN '4' GVWR_Safety_Equip
create table Restraint_Types(Type_ID NUMERIC, Description TEXT);
insert into Restraint_Types values(1, 'Active Belts (A.S.P.), Front Air Bags (O.S.P.), No Side Air Bags');
insert into Restraint_Types values(2, 'Active Belts (A.S.P.), Front Air Bags (O.S.P.), Side Air Bags Front Row');
insert into Restraint_Types values(3, 'Active Belts (A.S.P.), Front Air Bags (O.S.P.), Side Air Bags All Rows');
insert into Restraint_Types values(4, 'Active Belts (A.S.P.), No Air Bags');
insert into Restraint_Types values(5, 'Active Belts (A.S.P.), Trucks over 10,000lbs GVWR');
insert into Restraint_Types values(6, 'Incomplete Vehicles With Hydraulic Brakes');
create table GVWR_Restraints(Year NUMERIC, VIN_Code TEXT, GVRW TEXT, Restraints NUMERIC);
insert into GVWR_Restraints values(2010, 'A','4001-5000#',1);
insert into GVWR_Restraints values(2010, 'G','4001-5000#',2);
insert into GVWR_Restraints values(2010, 'N','4001-5000#',3);
insert into GVWR_Restraints values(2010, 'V','4001-5000#',4);
insert into GVWR_Restraints values(2010, 'B','5001-6000#',1);
insert into GVWR_Restraints values(2010, 'H','5001-6000#',2);
insert into GVWR_Restraints values(2010, 'P','5001-6000#',3);
insert into GVWR_Restraints values(2010, 'X','5001-6000#',4);
insert into GVWR_Restraints values(2010, 'C','6001-7000#',1);
insert into GVWR_Restraints values(2010, 'J','6001-7000#',2);
insert into GVWR_Restraints values(2010, 'R','6001-7000#',3);
insert into GVWR_Restraints values(2010, 'Y','6001-7000#',4);
insert into GVWR_Restraints values(2010, 'D','7001-8000#',1);
insert into GVWR_Restraints values(2010, 'K','7001-8000#',2);
insert into GVWR_Restraints values(2010, 'S','7001-8000#',3);
insert into GVWR_Restraints values(2010, 'Z','7001-8000#',4);
insert into GVWR_Restraints values(2010, 'E','8001-9000#',1);
insert into GVWR_Restraints values(2010, 'L','8001-9000#',2);
insert into GVWR_Restraints values(2010, 'T','8001-9000#',3);
insert into GVWR_Restraints values(2010, '1','8001-9000#',4);
insert into GVWR_Restraints values(2010, 'F','9001-10000#',1);
insert into GVWR_Restraints values(2010, 'M','9001-10000#',2);
insert into GVWR_Restraints values(2010, 'U','9001-10000#',3);
insert into GVWR_Restraints values(2010, '2','9001-10000#',4);
insert into GVWR_Restraints values(2010, '3','10001-14000#',5);
insert into GVWR_Restraints values(2010, '4','14001-16000#',5);
insert into GVWR_Restraints values(2010, '5','16001-19500#',5);
insert into GVWR_Restraints values(2010, '6','19501-26000#',5);
insert into GVWR_Restraints values(2010, 'W','Incomplete Vehicle',6);
-- VIN '5' Model Line and Vehicle Family
create table model_line(Vehicle_Type TEXT, Year NUMERIC, VIN_Code TEXT, Description TEXT, Platform TEXT);
insert into model_line values('Truck', 2010, '3' , 'Ram Cab Chassis (4x4)', 'DX');
insert into model_line values('Truck', 2010, '5' , 'Ram Pickup Light Duty (4x2)', 'DX');
insert into model_line values('Truck', 2010, '6' , 'Ram Pickup Light Duty (4x4)', 'DX')
insert into model_line values('Truck', 2010, 'B' , 'Ram Pickup Light Duty (4x2)', 'DS');
insert into model_line values('Truck', 2010, 'C' , 'Ram Cab Chassis (4x2),'DM');
insert into model_line values('Truck', 2010, 'D' , 'Ram Chassis Cab (4x4) ,'DM');
insert into model_line values('Truck', 2010, 'E' , 'Dakota 4x2','ND');
insert into model_line values('Truck', 2010, 'G' , 'Ram Chassis Cab (4x2)','DC');
insert into model_line values('Truck', 2010,'H' , 'Ram Chassis Cab (4x4),'DC');
insert into model_line values('Truck', 2010,'L' , 'Ram Pickup Heavy Duty (4x2)','D1');
insert into model_line values('Truck', 2010,'N' , 'Ram Cab Chassis (4x2), 'DX');
insert into model_line values('Truck', 2010,'R' , 'Ram Pickup Heavy Duty (4x2), 'DH');
insert into model_line values('Truck', 2010,'S' , 'Ram Pickup Heavy Duty (4x4), 'DH');
insert into model_line values('Truck', 2010,'V' , 'Ram Pickup Light Duty (4x4), 'DS');
insert into model_line values('Truck', 2010,'W' , 'Dakota 4x4,'ND');
insert into model_line values('Truck', 2010,'X' , 'Ram Pickup Heavy Duty (4x4)','D1');
insert into model_line values('MPV', 2010,'5' => 'Journey (RHD)(FWD)', 'JC');
insert into model_line values('MPV', 2010,'9', 'Nitro (RHD)(4x4)','KA');
insert into model_line values('MPV', 2010,'G', 'Journey (LHD)(FWD)', 'JC');
insert into model_line values('MPV', 2010,'H', 'Journey (LHD)(AWD)', 'JC');
insert into model_line values('MPV', 2010,'N', 'Caravan (LHD)(FWD)', 'RT');
insert into model_line values('MPV', 2010,'R', 'Town & Country (LHD)(FWD), 'RT');
insert into model_line values('MPV', 2010,'S', 'Grand Voyager (LHD)(FWD), 'RT');
insert into model_line values('MPV', 2010,'T', 'Grand Voyager (RHD)(FWD), 'RT');
insert into model_line values('MPV', 2010,'T', 'Nitro (LHD)(4x2)','KA');
insert into model_line values('MPV', 2010,'U', 'Nitro (LHD)(4x4)','KA');
-- VIN '6' 'description' => 'Series/Price Class',
create table series_price_class(Vehicle_Type TEXT, Year NUMERIC, VIN_Code TEXT, Description TEXT);
insert into series_price_class values('Truck', 2010, '1' ,'1500');
insert into series_price_class values('Truck', 2010, '2' , '2500');
insert into series_price_class values('Truck', 2010, '3' , '3500 Less DRW');
insert into series_price_class values('Truck', 2010, '4' , '3500 DRW');
insert into series_price_class values('Truck', 2010, '5' , '4000 DWR');
insert into series_price_class values('Truck', 2010, '6' , '4500 DWR');
insert into series_price_class values('Truck', 2010, '7' , '5500 DWR');
insert into series_price_class values('MPV', 2010, '1' , 'E (Economy)');
insert into series_price_class values('MPV', 2010,'2' , 'L (Low Line)');
insert into series_price_class values('MPV', 2010,'3' , 'M (Medium)');
insert into series_price_class values('MPV', 2010,'4' , 'H (High Line)');
insert into series_price_class values('MPV', 2010,'5' , 'P (Premium)');
insert into series_price_class values('MPV', 2010,'6' , 'S (Sport)');
insert into series_price_class values('MPV', 2010,'7' , 'X (Special)');
-- VIN '7' 'Body Style by Vehicle Line'
create table body_style_by_vehicle_line(Year NUMERIC, Vehicle_Line TEXT, VIN_Code TEXT, Description TEXT);
insert into body_style_by_vehicle_line values(2010,'Dakota','B','Extended Cab / Dakota Pickup');
insert into body_style_by_vehicle_line values(2010,'Dakota','G','Crew Cab / Dakota Pickup');
insert into body_style_by_vehicle_line values(2010,'Ram','C','Crew Cab');
insert into body_style_by_vehicle_line values(2010,'Ram','G','Quad Cab');
insert into body_style_by_vehicle_line values(2010,'Ram','E','Regular Cab/Chassis');
insert into body_style_by_vehicle_line values(2010,'Ram','H','Mega Cab');
insert into body_style_by_vehicle_line values(2010,'MPV','A','Grand Caravan C/V');
insert into body_style_by_vehicle_line values(2010,'MPV','F' , 'Journey Body Style');
insert into body_style_by_vehicle_line values(2010,'MPV','G' , 'Sport Utility 4 Door');
insert into body_style_by_vehicle_line values(2010,'MPV','D' , 'Grand Caravan/T&C');
-- VIN '8' Engine
create table engine(Year NUMERIC, VIN_Code TEXT, Description TEXT);
insert into engine values(2010,'C','1.8L 4CYL Gasoline Non-Turbo (EBA)');
insert into engine values(2010,'1','3.8L 6CYL Gasoline Non-Turbo (EGL, EGT)');
insert into engine values(2010,'5','2.8L 4CYL Diesel (ENS)');
insert into engine values(2010,'A','2.0L 4CYL Gasoline Non-Turbo (ECN, ECP)');
insert into engine values(2010,'B','2.4L 4CYL Gasoline Non-Turbo (EDG, ED3)');
insert into engine values(2010,'D','2.7L 5CYL Gasoline Non-Turbo (EER)');
insert into engine values(2010,'E','3.3L 6CYL Gasoline Non-Turbo (EGV)');
insert into engine values(2010,'F','2.4L 4CYL Gasoline Turbo (ED4)');
insert into engine values(2010,'K','3.7L 6CYL Gasoline Non-Turbo (EKG)');
insert into engine values(2010,'L','6.7L 6CYL Diesel (ETJ)');
insert into engine values(2010,'M','3.0L 6CYL Diesel (EXL)');
insert into engine values(2010,'P','4.7L 8CYL Gasoline Non-Turbo (EVE)');
insert into engine values(2010,'T','5.7L 8CYL Gasoline Non-Turbo (EZC, EZE, EZH)');
insert into engine values(2010,'U','2.2L 4CYL Diesel (ENE)');
insert into engine values(2010,'V','3.5L 6CYL Gasoline Non-Turbo (EGF, EGG)');
insert into engine values(2010,'W','6.1L 8CYL Gasoline Non-Turbo (ESF)');
insert into engine values(2010,'X','4.0L 6CYL Gasoline Non-Turbo (EGQ, EGS)');
insert into engine values(2010,'Y','2.0L 4CYL Diesel(ECD, ECE)');
insert into engine values(2010,'Z','8.4L 10CYL Gasoline Non-Turbo (EWE)');
},
-- VIN '9' Check Digit
-- VIN '10' Model Year
create TABLE model_year(VIN_Code TEXT, Year NUMERIC);
insert into model_year values('1' ,2001);
insert into model_year values('2' ,2002);
insert into model_year values('3' ,2003);
insert into model_year values('4' ,2004);
insert into model_year values('5' ,2005);
insert into model_year values('6' ,2006);
insert into model_year values('7' ,2007);
insert into model_year values('8' ,2008);
insert into model_year values('9' ,2009);
insert into model_year values('A' ,2010);
insert into model_year values('B' ,2011);
insert into model_year values('C' ,2012);
insert into model_year values('D' ,2013);
insert into model_year values('E' ,2014);
insert into model_year values('F' ,2015);
insert into model_year values('G' ,2016);
insert into model_year values('H' ,2017);
insert into model_year values('J' ,2018);
insert into model_year values('K' ,2019);
insert into model_year values('L' ,2020);
insert into model_year values('M' ,2021);
insert into model_year values('N' ,2022);
insert into model_year values('P' ,2023);
insert into model_year values('R' ,2024);
insert into model_year values('S' ,2025);
insert into model_year values('T' ,2026);
insert into model_year values('V' ,2027);
insert into model_year values('W' ,2028);
insert into model_year values('X' ,2029);
insert into model_year values('Y' ,2000);
-- VIN '11' Assembly Plant
create table assembly_plant(VIN_Code TEXT, Description TEXT);
insert into assembly_plant values('A', 'Chrysler Technical Center Pre-Production & Pilot');
insert into assembly_plant values('C', 'Jefferson North Assembly');
insert into assembly_plant values('D', 'Belvidere Assembly');
insert into assembly_plant values('E', 'Saltillo Van/Truck Assembly Plant');
insert into assembly_plant values('G', 'Saltillo Truck Assembly Plant');
insert into assembly_plant values('H', 'Brampton Assembly');
insert into assembly_plant values('J', 'St. Louis Assembly North');
insert into assembly_plant values('L', 'Toledo South Assembly');
insert into assembly_plant values('N', 'Sterling Heights Assembly');
insert into assembly_plant values('R', 'Windsor Assembly');
insert into assembly_plant values('S', 'Warren Truck Assembly');
insert into assembly_plant values('T', 'Toluca Assembly');
insert into assembly_plant values('W', 'Toledo North Assembly');