CREATE table users(first_name string, nationality text, country text, city text, started_at date, finished_at date, price integer, hotel text, hotels_stars decimal, with_insurance boolean, insurer text);
first_name
second_name
patronymic_name
CREATE TABLE users (
id serial PRIMARY KEY,
first_name VARCHAR NOT NULL,
second_name VARCHAR NOT NULL,
patronymic_name VARCHAR
);
INSERT INTO users(first_name, second_name, patronymic_name)
VALUES
('Вадим', 'Буланов', 'Викторович'),
('Андрей', 'Иванов', 'Сергеевич'),
('Анна', 'Федорова', 'Владимировна'),
('Андрей', 'Логинов', 'Викторович');
CREATE TABLE countries (
id serial PRIMARY KEY,
name VARCHAR NOT NULL
);
INSERT INTO countries(name)
VALUES ('Россия'), ('Казахстан'), ('Вьетнам'), ('Египет'), ('Таиланд'), ('Тунис');
CREATE TABLE cities (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
country_id INTEGER REFERENCES countries (id) NOT NULL
);
INSERT INTO cities(name, country_id)
VALUES
('Фантхьет', (SELECT id FROM countries WHERE name = 'Вьетнам')),
('Шарм-эль-Шейх', (SELECT id FROM countries WHERE name = 'Египет')),
('Самуи', (SELECT id FROM countries WHERE name = 'Таиланд')),
('Джерба', (SELECT id FROM countries WHERE name = 'Тунис'));
CREATE TABLE passports (
id serial PRIMARY KEY,
country_id INTEGER REFERENCES countries (id) NOT NULL,
user_id INTEGER REFERENCES users (id) NOT NULL
);
INSERT INTO passports(country_id, user_id)
VALUES
((SELECT id FROM countries WHERE name = 'Россия'), (SELECT id FROM users WHERE second_name = 'Буланов')),
((SELECT id FROM countries WHERE name = 'Казахстан'), (SELECT id FROM users WHERE second_name = 'Иванов')),
((SELECT id FROM countries WHERE name = 'Россия'), (SELECT id FROM users WHERE second_name = 'Иванов')),
((SELECT id FROM countries WHERE name = 'Россия'), (SELECT id FROM users WHERE second_name = 'Федорова')),
((SELECT id FROM countries WHERE name = 'Россия'), (SELECT id FROM users WHERE second_name = 'Логинов'));
CREATE TABLE hotels (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
city_id INTEGER REFERENCES cities (id) NOT NULL
);
INSERT INTO hotels(name, city_id)
VALUES
('Rubens Boutique Hotel', (SELECT id FROM cities WHERE name = 'Фантхьет')),
('Hilton Sharks Bay', (SELECT id FROM cities WHERE name = 'Шарм-эль-Шейх')),
('Silavadee Resort', (SELECT id FROM cities WHERE name = 'Самуи')),
('Hotel Djerba Resort', (SELECT id FROM cities WHERE name = 'Джерба'));
CREATE TABLE hotel_stars (
id serial PRIMARY KEY,
hotel_id INTEGER REFERENCES hotels (id) NOT NULL,
value NUMERIC(2,1) NOT NULL,
from_date DATE NOT NULL,
to_date DATE
);
INSERT INTO hotel_stars(from_date, to_date, value, hotel_id)
VALUES
('2020-01-01', '2022-12-31', '4.5', (SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel')),
('2023-01-01', NULL, '5', (SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel')),
('2020-01-01', NULL, '4', (SELECT id FROM hotels WHERE name = 'Hilton Sharks Bay')),
('2020-01-01', NULL, '4.5', (SELECT id FROM hotels WHERE name = 'Silavadee Resort')),
('2020-01-01', NULL, '4', (SELECT id FROM hotels WHERE name = 'Hotel Djerba Resort'));
CREATE TABLE insurers (
id serial PRIMARY KEY,
name VARCHAR NOT NULL
);
INSERT INTO insurers(name)
VALUES
('Совкомбанк Страхование'),
('РЕСО-Гарантия'),
('АльфаСтрахование');
CREATE TABLE insurances (
id serial PRIMARY KEY,
insurer_id INTEGER REFERENCES insurers (id) NOT NULL,
passport_id INTEGER REFERENCES passports (id) NOT NULL
);
INSERT INTO insurances(insurer_id, passport_id)
VALUES
(
(SELECT id FROM insurers WHERE name = 'Совкомбанк Страхование'),
(
SELECT passports.id FROM passports
INNER JOIN users ON users.id = passports.user_id
INNER JOIN countries ON countries.id = passports.country_id
WHERE users.second_name = 'Буланов' AND countries.name = 'Россия'
)
),
(
(SELECT id FROM insurers WHERE name = 'РЕСО-Гарантия'),
(
SELECT passports.id FROM passports
INNER JOIN users ON users.id = passports.user_id
INNER JOIN countries ON countries.id = passports.country_id
WHERE users.second_name = 'Иванов' AND countries.name = 'Казахстан'
)
),
(
(SELECT id FROM insurers WHERE name = 'АльфаСтрахование'),
(
SELECT passports.id FROM passports
INNER JOIN users ON users.id = passports.user_id
INNER JOIN countries ON countries.id = passports.country_id
WHERE users.second_name = 'Иванов' AND countries.name = 'Россия'
)
);
CREATE TABLE up_to_me_trips (
id serial PRIMARY KEY,
passport_id INTEGER REFERENCES passports (id) NOT NULL,
hotel_id INTEGER REFERENCES hotels (id) NOT NULL,
started_at DATE NOT NULL,
finished_at DATE NOT NULL,
price INTEGER NOT NULL,
insurance_id INTEGER REFERENCES insurances (id),
hotel_stars NUMERIC(2,1) NOT NULL
);
WITH user_passports AS (
SELECT passports.id, users.second_name, countries.name AS country_name
FROM passports
INNER JOIN users ON users.id = passports.user_id
INNER JOIN countries ON countries.id = passports.country_id
)
INSERT INTO up_to_6nf_trips(passport_id, hotel_id, started_at, finished_at, price, insurance_id, hotel_stars)
VALUES
(
(SELECT id FROM user_passports WHERE second_name = 'Буланов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel'),
'2021-04-14',
'2021-04-28',
200000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'Совкомбанк Страхование') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Буланов' AND country_name = 'Россия')
),
(
SELECT value FROM hotel_stars
WHERE
hotel_id = (SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel') AND
(from_date, COALESCE(to_date, 'infinity')) OVERLAPS ('2021-04-14', '2021-04-28')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Казахстан'),
(SELECT id FROM hotels WHERE name = 'Hilton Sharks Bay'),
'2021-05-13',
'2021-05-27',
140000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'РЕСО-Гарантия') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Казахстан')
),
(
SELECT value FROM hotel_stars
WHERE
hotel_id = (SELECT id FROM hotels WHERE name = 'Hilton Sharks Bay') AND
(from_date, COALESCE(to_date, 'infinity')) OVERLAPS ('2021-05-13', '2021-05-27')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Silavadee Resort'),
'2021-04-12',
'2021-04-26',
250000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'АльфаСтрахование') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Россия')
),
(
SELECT value FROM hotel_stars
WHERE
hotel_id = (SELECT id FROM hotels WHERE name = 'Silavadee Resort') AND
(from_date, COALESCE(to_date, 'infinity')) OVERLAPS ('2021-04-12', '2021-04-26')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Федорова' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Hotel Djerba Resort'),
'2022-06-05',
'2022-06-19',
120000,
NULL,
(
SELECT value FROM hotel_stars
WHERE
hotel_id = (SELECT id FROM hotels WHERE name = 'Hotel Djerba Resort') AND
(from_date, COALESCE(to_date, 'infinity')) OVERLAPS ('2022-06-05', '2022-06-19')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Логинов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel'),
'2023-03-23',
'2023-04-07',
140000,
NULL,
(
SELECT value FROM hotel_stars
WHERE
hotel_id = (SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel') AND
(from_date, COALESCE(to_date, 'infinity')) OVERLAPS ('2023-03-23', '2023-04-07')
)
);
CREATE TABLE up_to_6nf_trips (
id serial PRIMARY KEY,
passport_id INTEGER REFERENCES passports (id) NOT NULL,
hotel_id INTEGER REFERENCES hotels (id) NOT NULL,
started_at DATE NOT NULL,
finished_at DATE NOT NULL,
price INTEGER NOT NULL,
insurance_id INTEGER REFERENCES insurances (id)
);
WITH user_passports AS (
SELECT passports.id, users.second_name, countries.name AS country_name
FROM passports
INNER JOIN users ON users.id = passports.user_id
INNER JOIN countries ON countries.id = passports.country_id
)
INSERT INTO up_to_6nf_trips(passport_id, hotel_id, started_at, finished_at, price, insurance_id)
VALUES
(
(SELECT id FROM user_passports WHERE second_name = 'Буланов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel'),
'2021-04-14',
'2021-04-28',
200000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'Совкомбанк Страхование') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Буланов' AND country_name = 'Россия')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Казахстан'),
(SELECT id FROM hotels WHERE name = 'Hilton Sharks Bay'),
'2021-05-13',
'2021-05-27',
140000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'РЕСО-Гарантия') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Казахстан')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Silavadee Resort'),
'2021-04-12',
'2021-04-26',
250000,
(
SELECT id FROM insurances
WHERE
insurer_id = (SELECT id FROM insurers WHERE name = 'АльфаСтрахование') AND
passport_id = (SELECT id FROM user_passports WHERE second_name = 'Иванов' AND country_name = 'Россия')
)
),
(
(SELECT id FROM user_passports WHERE second_name = 'Федорова' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Hotel Djerba Resort'),
'2022-06-05',
'2022-06-19',
120000,
NULL
),
(
(SELECT id FROM user_passports WHERE second_name = 'Логинов' AND country_name = 'Россия'),
(SELECT id FROM hotels WHERE name = 'Rubens Boutique Hotel'),
'2023-03-23',
'2023-04-07',
140000,
NULL
);
Created
September 19, 2023 19:27
-
-
Save ruvaleev/bdce8d41f8357c877da643f1a9745d1f to your computer and use it in GitHub Desktop.
Итоговый вывод Postgres можно посмотреть в файле resulting_output.md. Команды, как я создавал и наполнял таблицы - в creating_and_filling_tables.md. Результат того, как бы сделал я, я поместил в таблицу up_to_me_trips, а того, что соответствует 6НФ - в таблицу up_to_6nf_trips. Общая идея такая:
users:
first_name
second_name
patronymic_name
countries:
name
cities:
name
country_id
passports:
country_id
user_id
hotels:
name
city_id
hotel_stars:
hotel_id
level
from_date
to_date
insurers:
name
insurances:
insurer_id
passport_id
up_to_me_trips:
passport_id
hotel_id
started_at
finished_at
price
insurance_id
hotel_stars
up_to_6nf_trips:
passport_id
hotel_id
started_at
finished_at
price
insurance_id
ruslan=# ruslan=# SELECT * FROM users;
id | first_name | second_name | patronymic_name
----+------------+-------------+-----------------
1 | Вадим | Буланов | Викторович
2 | Андрей | Иванов | Сергеевич
4 | Анна | Федорова | Владимировна
5 | Андрей | Логинов | Викторович
(4 rows)
ruslan=# SELECT * FROM countries;
id | name
----+-----------
1 | Россия
2 | Казахстан
3 | Вьетнам
4 | Египет
5 | Таиланд
6 | Тунис
(6 rows)
ruslan=# SELECT * FROM cities;
id | name | country_id
----+---------------+------------
1 | Фантхьет | 3
2 | Шарм-эль-Шейх | 4
3 | Самуи | 5
4 | Джерба | 6
(4 rows)
ruslan=# SELECT * FROM passports;
id | country_id | user_id
----+------------+---------
2 | 1 | 1
3 | 2 | 2
4 | 1 | 2
5 | 1 | 4
6 | 1 | 5
(5 rows)
ruslan=# SELECT * FROM hotels;
id | name | city_id
----+-----------------------+---------
1 | Rubens Boutique Hotel | 1
2 | Hilton Sharks Bay | 2
3 | Silavadee Resort | 3
4 | Hotel Djerba Resort | 4
(4 rows)
ruslan=# SELECT * FROM hotel_stars;
id | hotel_id | value | from_date | to_date
----+----------+-------+------------+------------
6 | 1 | 4.5 | 2020-01-01 | 2022-12-31
7 | 1 | 5.0 | 2023-01-01 |
8 | 2 | 4.0 | 2020-01-01 |
9 | 3 | 4.5 | 2020-01-01 |
10 | 4 | 4.0 | 2020-01-01 |
(5 rows)
ruslan=# SELECT * FROM insurers;
id | name
----+------------------------
1 | Совкомбанк Страхование
2 | РЕСО-Гарантия
3 | АльфаСтрахование
(3 rows)
ruslan=# SELECT * FROM insurances;
id | insurer_id | passport_id
----+------------+-------------
1 | 1 | 2
2 | 2 | 3
3 | 3 | 4
(3 rows)
ruslan=# SELECT * FROM up_to_me_trips;
id | passport_id | hotel_id | started_at | finished_at | price | insurance_id | hotel_stars
----+-------------+----------+------------+-------------+--------+--------------+-------------
1 | 2 | 1 | 2021-04-14 | 2021-04-28 | 200000 | 1 | 4.5
2 | 3 | 2 | 2021-05-13 | 2021-05-27 | 140000 | 2 | 4.0
3 | 4 | 3 | 2021-04-12 | 2021-04-26 | 250000 | 3 | 4.5
4 | 5 | 4 | 2022-06-05 | 2022-06-19 | 120000 | | 4.0
5 | 6 | 1 | 2023-03-23 | 2023-04-07 | 140000 | | 5.0
(5 rows)
ruslan=# SELECT * FROM up_to_6nf_trips;
id | passport_id | hotel_id | started_at | finished_at | price | insurance_id
----+-------------+----------+------------+-------------+--------+--------------
1 | 2 | 1 | 2021-04-14 | 2021-04-28 | 200000 | 1
2 | 3 | 2 | 2021-05-13 | 2021-05-27 | 140000 | 2
3 | 4 | 3 | 2021-04-12 | 2021-04-26 | 250000 | 3
4 | 5 | 4 | 2022-06-05 | 2022-06-19 | 120000 |
5 | 6 | 1 | 2023-03-23 | 2023-04-07 | 140000 |
(5 rows)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment