Advertisement
Guest User

CREATE

a guest
Dec 16th, 2017
114
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.85 KB | None | 0 0
  1. /*---------------------DROP_TABLES--------------------------*/
  2. DROP TABLE best_path_algorithm CASCADE CONSTRAINT;
  3. DROP TABLE section_direction CASCADE CONSTRAINT;
  4. DROP TABLE road_typology CASCADE CONSTRAINT;
  5. DROP TABLE toll_fare CASCADE CONSTRAINT;
  6. DROP TABLE vehicle_motorization CASCADE CONSTRAINT;
  7. DROP TABLE vehicle_type CASCADE CONSTRAINT;
  8. DROP TABLE vehicle_toll_class CASCADE CONSTRAINT;
  9. DROP TABLE vehicle CASCADE CONSTRAINT;
  10. DROP TABLE road_network CASCADE CONSTRAINT;
  11. DROP TABLE network_analysis CASCADE CONSTRAINT;
  12. DROP TABLE road CASCADE CONSTRAINT;
  13. DROP TABLE node CASCADE CONSTRAINT;
  14. DROP TABLE SECTION CASCADE CONSTRAINT;
  15. DROP TABLE segment CASCADE CONSTRAINT;
  16. DROP TABLE throttle_id CASCADE CONSTRAINT;
  17. DROP TABLE fuel_type CASCADE CONSTRAINT;
  18. DROP TABLE gear CASCADE CONSTRAINT;
  19. DROP TABLE vehicle_velocity_limit CASCADE CONSTRAINT;
  20. DROP TABLE regime CASCADE CONSTRAINT;
  21. DROP TABLE gear_ratio CASCADE CONSTRAINT;
  22.  
  23. DROP SEQUENCE seq_section_id;
  24.  
  25. /*---------------------CREATE_SEQUENCES--------------------------*/
  26. CREATE SEQUENCE seq_section_id
  27.  START WITH     1
  28.  INCREMENT BY   1
  29.  MINVALUE      1
  30.  MAXVALUE      9999999999
  31.  ;
  32. /*---------------------CREATE_TABLES--------------------------*/
  33. CREATE TABLE best_path_algorithm (
  34.     best_path_algorithm VARCHAR(40),
  35.     CONSTRAINT pk_best_path_algorithm PRIMARY KEY (best_path_algorithm)
  36. );
  37.  
  38. CREATE TABLE section_direction(
  39.     direction VARCHAR(30),
  40.     CONSTRAINT pk_section_direction PRIMARY KEY(direction)
  41. );
  42.  
  43. CREATE TABLE road_typology(
  44.     typology VARCHAR(30),
  45.     CONSTRAINT pk_road_typology PRIMARY KEY(typology)
  46. );
  47.  
  48. CREATE TABLE road_network(
  49.     name VARCHAR(20),
  50.     description VARCHAR(255),
  51.     CONSTRAINT pk_road_network PRIMARY KEY(name)
  52. );
  53.  
  54. CREATE TABLE vehicle_toll_class(
  55.     toll_class NUMBER(10),
  56.     CONSTRAINT pk_vehicle_toll_class PRIMARY KEY(toll_class)
  57. );
  58.  
  59. CREATE TABLE throttle_id (
  60.     id NUMBER(3),
  61.     CONSTRAINT pk_throttle_id PRIMARY KEY(id)
  62. );
  63.  
  64.  
  65. CREATE TABLE vehicle_motorization (
  66.     motorization VARCHAR(20),
  67.     CONSTRAINT pk_vehicle_motorization PRIMARY KEY(motorization)
  68. );
  69.  
  70. CREATE TABLE vehicle_type (
  71.     TYPE VARCHAR(20),
  72.     CONSTRAINT pk_vehicle_type PRIMARY KEY(TYPE)
  73. );
  74.  
  75. CREATE TABLE fuel_type (
  76.     TYPE VARCHAR(20),
  77.     CONSTRAINT pk_fuel_type PRIMARY KEY(TYPE)
  78. );
  79.  
  80. CREATE TABLE vehicle (
  81.     name VARCHAR(20),
  82.     description VARCHAR(255),
  83.     TYPE VARCHAR(20),
  84.     toll_class NUMBER(10),
  85.     motorization VARCHAR(20),
  86.     road_network_name VARCHAR(20),
  87.     fuel_type VARCHAR(20),
  88.     mass NUMBER(4,2) NOT NULL,
  89.     LOAD NUMBER(4, 1) NOT NULL,
  90.     drag_coeficient NUMBER(4, 2) NOT NULL,
  91.     rolling_resistence_coefficient NUMBER(4,2) NOT NULL,
  92.     wheel_size NUMBER(4,2) NOT NULL,
  93.     braking_energy_regeneration NUMBER(4, 2),
  94.     frontal_area NUMBER(4, 2) NOT NULL,
  95.     min_rpm NUMBER(4, 2) NOT NULL,
  96.     max_rpm NUMBER(4, 2) NOT NULL,
  97.     final_drive_ratio NUMBER(4, 2) NOT NULL,
  98.     CONSTRAINT pk_vehicle PRIMARY KEY(name),
  99.     CONSTRAINT fk_vehicle_road_network FOREIGN KEY(road_network_name) REFERENCES road_network(name),
  100.     CONSTRAINT fk_vehicle_vehicle_motorization FOREIGN KEY(motorization) REFERENCES vehicle_motorization(motorization),
  101.     CONSTRAINT fk_vehicle_vehicle_toll_class FOREIGN KEY(toll_class) REFERENCES vehicle_toll_class(toll_class),
  102.     CONSTRAINT fk_vehicle_vehicle_type FOREIGN KEY(TYPE) REFERENCES vehicle_type(TYPE),
  103.     CONSTRAINT fk_vehicle_fuel_type FOREIGN KEY(fuel_type) REFERENCES fuel_type(TYPE)
  104. );
  105.  
  106. CREATE TABLE regime (
  107.     throttle_id NUMBER(3),
  108.     vehicle_name VARCHAR(20),
  109.     rpm_high NUMBER(4, 2),
  110.     rpm_low NUMBER(4, 2),
  111.     torque NUMBER(4, 2) NOT NULL,
  112.     sfc NUMBER(4,2),
  113.     CONSTRAINT pk_regime PRIMARY KEY(throttle_id, vehicle_name, rpm_high, rpm_low),
  114.     CONSTRAINT fk_regime_throttle_id FOREIGN KEY(throttle_id) REFERENCES throttle_id(id),
  115.     CONSTRAINT fk_regime_vehicle FOREIGN KEY(vehicle_name) REFERENCES vehicle(name)
  116. );
  117.  
  118. CREATE TABLE network_analysis (
  119.     vehicle_name VARCHAR(20),
  120.     best_path_algorithm VARCHAR(40),
  121.     toll_costs NUMBER(4, 2) NOT NULL,
  122.     travelling_time VARCHAR(8),
  123.     energy_consumption NUMBER(5, 2) NOT NULL,
  124.     sections_list VARCHAR(200),
  125.     CONSTRAINT fk_network_analysis_best_path_algorithm FOREIGN KEY(best_path_algorithm) REFERENCES best_path_algorithm(best_path_algorithm),
  126.     CONSTRAINT fk_netword_analysis_vehicle FOREIGN KEY(vehicle_name) REFERENCES vehicle(name),
  127.     CONSTRAINT pk_network_analysis PRIMARY KEY(vehicle_name, best_path_algorithm)
  128. );
  129.  
  130.  
  131. CREATE TABLE road(
  132.     name VARCHAR(10),
  133.     road_network_name VARCHAR(20),
  134.     typology VARCHAR(20),
  135.     CONSTRAINT fk_road_road_network FOREIGN KEY(road_network_name) REFERENCES road_network(name),
  136.     CONSTRAINT pk_road PRIMARY KEY(name),
  137.     CONSTRAINT fk_road_road_typology FOREIGN KEY(typology) REFERENCES road_typology(typology)
  138. );
  139.  
  140. CREATE TABLE node(
  141.     id VARCHAR(5),
  142.     road_network_name VARCHAR(10),
  143.     has_toll CHAR(1),
  144.     CONSTRAINT pk_node PRIMARY KEY(id),
  145.     CONSTRAINT fk_node_road_network FOREIGN KEY(road_network_name) REFERENCES road_network(name)
  146. );
  147.  
  148. CREATE TABLE vehicle_velocity_limit (
  149.     velocity_limit NUMBER(4, 2) NOT NULL,
  150.     vehicle_name VARCHAR(20),
  151.     road_typology VARCHAR(30),
  152.     CONSTRAINT pk_vehicle_velocity_limit PRIMARY KEY(vehicle_name, road_typology),
  153.     CONSTRAINT fk_vehicle_velocity_limit_vehicle FOREIGN KEY(vehicle_name) REFERENCES vehicle(name),
  154.     CONSTRAINT fk_vehicle_velocity_limit_road_typology FOREIGN KEY(road_typology) REFERENCES road_typology(typology)
  155. );
  156.  
  157.  
  158. CREATE TABLE SECTION(
  159.     id NUMBER(10) DEFAULT seq_section_id.NEXTVAL,
  160.     road_name VARCHAR(10),
  161.     direction VARCHAR(30),
  162.     ending_node VARCHAR(5),
  163.     begining_node VARCHAR(5),
  164.     CONSTRAINT pk_section PRIMARY KEY(id),
  165.     CONSTRAINT fk_section_road FOREIGN KEY(road_name) REFERENCES road(name),
  166.     CONSTRAINT fk_section_section_direction FOREIGN KEY(direction) REFERENCES section_direction(direction),
  167.     CONSTRAINT fk_section_ending_node FOREIGN KEY(ending_node) REFERENCES node(id),
  168.     CONSTRAINT fk_section_begining_node FOREIGN KEY(begining_node) REFERENCES node(id)
  169. );
  170.  
  171. CREATE TABLE segment(
  172.     order_in_section NUMBER(10),
  173.     section_id NUMBER(10),
  174.     has_toll CHAR(1),
  175.     initial_height NUMBER(4,2) NOT NULL,
  176.     slope NUMBER(4,2) NOT NULL,
  177.     LENGTH NUMBER(4,2) NOT NULL,
  178.     wind_direction NUMBER(4,2) NOT NULL,
  179.     wind_speed NUMBER(4,2) NOT NULL,
  180.     maximum_velocity NUMBER(4,2) NOT NULL,
  181.     minimum_velocity NUMBER(4,2) NOT NULL,
  182.     final_height NUMBER(4,2) NOT NULL,
  183.     CONSTRAINT pk_segment PRIMARY KEY(order_in_section, section_id),      
  184.     CONSTRAINT fk_segment_section FOREIGN KEY(section_id) REFERENCES SECTION(id)
  185. );
  186.  
  187. CREATE TABLE toll_fare (
  188.     vehicle_toll_class NUMBER(10),
  189.     road_name VARCHAR(10),
  190.     price NUMBER(4, 2) NOT NULL,
  191.     CONSTRAINT fk_toll_fare_vehicle_toll_class FOREIGN KEY(vehicle_toll_class) REFERENCES vehicle_toll_class(toll_class),
  192.     CONSTRAINT fk_toll_fare_road FOREIGN KEY(road_name) REFERENCES road(name),
  193.     CONSTRAINT pk_toll_fare PRIMARY KEY(vehicle_toll_class, road_name)
  194. );
  195.  
  196. CREATE TABLE gear (
  197.     id NUMBER(10),
  198.     CONSTRAINT pk_gear PRIMARY KEY(id)
  199. );
  200.  
  201. CREATE TABLE gear_ratio (
  202.     gear_id NUMBER(10),
  203.     vehicle_name VARCHAR(20),
  204.     ratio NUMBER(4, 2) NOT NULL,
  205.     CONSTRAINT pk_gear_ratio PRIMARY KEY(gear_id, vehicle_name),
  206.     CONSTRAINT fk_gear_ratio_gear FOREIGN KEY(gear_id) REFERENCES gear(id),
  207.     CONSTRAINT fk_gear_ratio_vehicle FOREIGN KEY(vehicle_name) REFERENCES vehicle(name)
  208. );
  209.  
  210. /*------------------CREATE_INSERTS-----------------------*/
  211. INSERT INTO section_direction VALUES('bidirectional');
  212. INSERT INTO section_direction VALUES('direct'); -- begin to end
  213. INSERT INTO section_direction VALUES('reverse'); -- end to begin
  214. INSERT INTO vehicle_motorization VALUES('combustion');
  215. INSERT INTO vehicle_motorization VALUES('electric');
  216. INSERT INTO vehicle_motorization VALUES('hybrid');
  217. INSERT INTO road_typology VALUES('regular');
  218. INSERT INTO road_typology VALUES('urban');
  219. INSERT INTO road_typology VALUES('highway');
  220. INSERT INTO road_typology VALUES('express');
  221. INSERT INTO vehicle_type VALUES('car');
  222. INSERT INTO vehicle_type VALUES('motorcycle');
  223. INSERT INTO vehicle_type VALUES('truck');
  224. INSERT INTO vehicle_type VALUES('tractor');
  225. INSERT INTO fuel_type VALUES('gasoline');
  226. INSERT INTO fuel_type VALUES('diesel');
  227. INSERT INTO fuel_type VALUES('electric');
  228. INSERT INTO throttle_id VALUES(25);
  229. INSERT INTO throttle_id VALUES(50);
  230. INSERT INTO throttle_id VALUES(100);
  231. INSERT INTO gear VALUES(1);
  232. INSERT INTO gear VALUES(2);
  233. INSERT INTO gear VALUES(3);
  234. INSERT INTO gear VALUES(4);
  235. INSERT INTO gear VALUES(5);
  236. INSERT INTO gear VALUES(6);
  237. INSERT INTO vehicle_toll_class VALUES(1);
  238. INSERT INTO vehicle_toll_class VALUES(2);
  239. INSERT INTO vehicle_toll_class VALUES(3);
  240. INSERT INTO vehicle_toll_class VALUES(4);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement