HgCEgt6v

1CREATE TYPE TRAIN_STATE AS ENUM (
2  'avaliable',
3  'out_of_order',
4  'in_repair'
5);
6
7CREATE TABLE IF NOT EXISTS countries (
8  country_id serial PRIMARY KEY,
9  name varchar,
10  area float,
11  perimeter float
12);
13
14CREATE TABLE IF NOT EXISTS cities (
15  city_id SERIAL PRIMARY KEY,
16  name varchar,
17  country int REFERENCES countries (country_id)
18);
19
20CREATE TABLE IF NOT EXISTS producers (
21  producer_id serial PRIMARY KEY,
22  name varchar,
23  street varchar,
24  city int REFERENCES cities (city_id),
25  pcode varchar,
26  phone_number varchar
27);
28
29CREATE TABLE IF NOT EXISTS trains (
30  train_id varchar PRIMARY KEY,
31  train_type int REFERENCES train_types (train_type_id),
32  producer int REFERENCES producers (producer_id),
33  train_state TRAIN_STATE,
34  build_date date
35);
36
37
38CREATE TABLE IF NOT EXISTS stations (
39  station_id serial PRIMARY KEY,
40  name varchar,
41  nr_platforms int,
42  city int REFERENCES cities (city_id),
43  date_created date
44);
45
46CREATE TABLE IF NOT EXISTS routes (
47  route_id serial PRIMARY KEY,
48  start_station int REFERENCES stations (station_id),
49  end_station int REFERENCES stations (station_id),
50  distance int
51);
52
53CREATE TABLE IF NOT EXISTS schedule (
54  connection_id serial PRIMARY KEY,
55  route int REFERENCES routes (route_id),
56  train varchar REFERENCES trains (train_id),
57  start_time timestamp,
58  end_time timestamp CHECK (end_time > start_time)
59);
60
61CREATE TABLE IF NOT EXISTS passengers (
62  passenger_id serial PRIMARY KEY,
63  surname varchar,
64  name varchar,
65  street varchar,
66  city int REFERENCES cities (city_id),
67  pcode varchar,
68  phone_number varchar
69);
70
71CREATE TABLE IF NOT EXISTS wagons (
72  wagon_id serial PRIMARY KEY,
73  wagen_nr int,
74  train varchar REFERENCES trains (train_id)
75);
76
77CREATE TABLE IF NOT EXISTS seats (
78  seat_id serial PRIMARY KEY,
79  is_window_seat boolean,
80  wagon int REFERENCES wagons (wagon_id),
81  seat_nr int
82);
83
84CREATE TABLE IF NOT EXISTS passengers_schedule (
85  passenger int REFERENCES passengers (passenger_id),
86  connection int REFERENCES schedule (connection_id),
87  seat int REFERENCES seats (seat_id)
88);
89
90
91CREATE or replace FUNCTION calc_avg_speed (max_speed_train int, max_speed_route int)
92	RETURNS int
93	AS $$
94	BEGIN
95		IF $1 < $2 THEN
96			RETURN $1 - (SELECT floor(random()*($1 / 3 - 0))+10);
97		ELSE
98			RETURN $2 - (SELECT floor(random()*($2 / 3 - 0))+10);
99		END IF;
100	END;
101	$$ LANGUAGE plpgsql;
102	
103CREATE or replace FUNCTION calc_duration (speed int, distance int)
104	RETURNS int
105	AS $$
106	BEGIN
107		RETURN $2 / $1;
108	END;
109	$$ LANGUAGE plpgsql;
110	
111CREATE or replace FUNCTION calc_end_time (start_time timestamp, max_speed_train int, distance int, max_speed_route int)
112	RETURNS timestamp
113	AS $$
114	BEGIN
115		RETURN $1 + (
116			SELECT calc_duration ((
117				SELECT calc_avg_speed($2, $4)
118			), $3));
119	END;
120	$$ LANGUAGE plpgsql;
121