vJsg8eXt

1
2drop schema if exists client cascade;
3drop schema if exists station cascade;
4drop schema if exists route cascade;
5drop schema if exists wagon cascade;
6drop schema if exists train cascade;
7drop schema if exists trip cascade;
8drop schema if exists ticket cascade;
9
10create schema client;
11
12create table client.client (
13	id serial primary key,
14	full_name text not null,
15	is_active bool not null default true,
16	is_admin bool not null default false,
17
18	birthday date not null
19);
20
21
22create schema station;
23
24create table station.station (
25	id serial primary key,
26	name varchar(64) not null unique
27);
28
29
30create schema route;
31
32create table route.route (
33	id serial primary key,
34	name varchar(12) not null unique,
35	departure time not null,
36	first_station_id int,
37	last_station_id int,
38
39	foreign key (first_station_id) references station.station (id) on delete set null,
40	foreign key (last_station_id) references station.station (id) on delete set null
41);
42
43create table route.stage (
44	route_id int not null,
45	station_id int not null,
46	constraint route_stage_pkey primary key (route_id, station_id),
47	foreign key (route_id) references route.route (id) on delete cascade,
48	foreign key (station_id) references station.station (id) on delete cascade,
49
50	arrival time not null,
51	departure time not null
52);
53
54
55create schema wagon;
56
57create table wagon.class (
58	id serial primary key,
59	name varchar(8) not null unique,
60	seats_schema_url varchar(64)
61);
62
63create table wagon.seat (
64	num int not null,
65	wagon_class_id int not null,
66
67	constraint wagon_seat_pkey primary key (num, wagon_class_id),
68
69	price_per_hour money not null
70		constraint positive_price_per_hour check (price_per_hour > 0::money),
71	count int not null
72		constraint positive_count check (count > 0),
73
74	foreign key (wagon_class_id) references wagon.class (id) on delete cascade
75);
76
77
78create schema train;
79
80create table train.train (
81	id serial primary key,
82	name varchar(32) not null unique,
83	price_multiplier real not null default 1.0,
84		constraint over_100_percent_price_multipler check (price_multiplier >= 1)
85);
86
87create table train.wagon (
88	id serial primary key,
89	num int not null,
90	train_id int not null,
91
92	class_id int not null,
93
94	constraint train_wagon_unique unique (num, train_id),
95	foreign key (train_id) references train.train (id) on delete cascade,
96	foreign key (class_id) references wagon.class (id) on delete cascade -- TODO: или set null?
97);
98
99
100create schema trip;
101
102create table trip.trip (
103	id serial primary key,
104	name varchar(32) not null unique,
105	route_id int not null,
106	train_id int not null,
107	price_multiplier real not null default 1.0,
108		constraint over_100_percent_price_multipler check (price_multiplier >= 1),
109	departure_day date not null,
110
111	foreign key (train_id) references train.train (id) on delete cascade,
112	foreign key (route_id) references route.route (id) on delete cascade
113);
114
115
116create schema ticket;
117
118create table ticket.purchased (
119	id serial primary key,
120	trip_id int references trip.trip (id) on delete set null,
121	wagon_id int references train.wagon (id),
122	departure_id int references station.station (id),
123	arrival_id int references station.station (id),
124	client_id int references client.client (id)
125);
126