Last active
December 30, 2020 05:54
-
-
Save PNZeml/288a1735c35c7c4d4125182800c53fd7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN TRANSACTION; | |
/* | |
* Владельцы транспортных средств. | |
*/ | |
CREATE TABLE main.vehicle_owners ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
surname TEXT NOT NULL, | |
given_name_s TEXT NULL, | |
phone TEXT NOT NULL, | |
sex TEXT NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')), | |
CONSTRAINT vehicle_owners_ch0 CHECK (sex IN ('Мужской', 'Женский')) | |
); | |
/* | |
* Основной документ владельца траспортного средства (паспорт). | |
*/ | |
CREATE TABLE main.vehicle_owner_passports ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
vehicle_owner_id INTEGER NOT NULL, | |
issuing_country TEXT NOT NULL, | |
issue_datetime DATE NOT NULL, | |
expire_datetime DATE NOT NULL, | |
number TEXT NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')), | |
FOREIGN KEY (vehicle_owner_id) REFERENCES vehicle_owners (id), | |
CONSTRAINT vehicle_owner_passports_uq0 UNIQUE (number) | |
); | |
/* | |
* Транспортные средства. | |
*/ | |
CREATE TABLE main.vehicles ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
license_plate_number TEXT NOT NULL, | |
car_model TEXT NOT NULL, | |
car_color TEXT NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')) | |
); | |
/* | |
* Отношение: транпортные средства к владельцам. | |
*/ | |
CREATE TABLE main.vehicles_to_vehicle_owners ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
vehicle_id INTEGER NOT NULL, | |
vehicle_owner_id INTEGER NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')), | |
FOREIGN KEY (vehicle_id) REFERENCES vehicles (id), | |
FOREIGN KEY (vehicle_owner_id) REFERENCES vehicle_owners (id) | |
); | |
/* | |
* Записи на штрафстоянке | |
*/ | |
CREATE TABLE main.parking_fine_records ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
vehicle_id INTEGER NOT NULL, | |
starting_datetime DATE NOT NULL, | |
ending_datetime DATE NULL, | |
is_closed INTEGER NOT NULL DEFAULT 0, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')), | |
FOREIGN KEY (vehicle_id) REFERENCES vehicles (id), | |
CONSTRAINT parking_fine_records_ch0 CHECK (is_closed IN (0, 1)) | |
); | |
/* | |
* Вид штрафа | |
*/ | |
CREATE TABLE main.ticket_types ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
name TEXT NOT NULL, | |
fine REAL NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')) | |
); | |
/* | |
* Отношение: запись на штрафстоянке к видам штрафов | |
*/ | |
CREATE TABLE main.parking_fine_records_to_ticket_types ( | |
id INTEGER PRIMARY KEY AUTOINCREMENT, | |
parking_fine_record_id INTEGER NOT NULL, | |
ticket_type_id INTEGER NOT NULL, | |
creating_datetime DATE DEFAULT (datetime('now', 'utc')), | |
FOREIGN KEY (parking_fine_record_id) REFERENCES parking_fine_records (id), | |
FOREIGN KEY (ticket_type_id) REFERENCES ticket_types (id) | |
); | |
INSERT INTO main.vehicle_owners (surname, given_name_s, phone, sex) VALUES | |
('Иванов', 'Иван Иванович', '+7983123713', 'Мужской'), | |
('Капустина', 'Виктория Альбертовна', '+79812314122', 'Женский'), | |
('Журавлёва', 'Лигия Евгеньевна', '+78231235411', 'Женский'); | |
INSERT INTO main.vehicle_owner_passports (vehicle_owner_id, issuing_country, issue_datetime, expire_datetime, number) VALUES | |
(1, 'RUS', '2014-05-01', '2030-05-01', '9832 123453'), | |
(2, 'RUS', '2020-01-15', '2036-10-15', '7239 123123'), | |
(3, 'RUS', '2016-02-29', '2030-05-01', '8712 123123'); | |
INSERT INTO main.vehicles (license_plate_number, car_model, car_color) VALUES | |
('у981пр', 'Toyota Corolla', 'Красный'), | |
('н123уф', 'ВАЗ 2107', 'Красный'), | |
('б932бу', 'Volkswagen Passat', 'Черный'); | |
INSERT INTO main.vehicles_to_vehicle_owners (vehicle_id, vehicle_owner_id) VALUES | |
(1, 1), | |
(2, 2), | |
(3, 3); | |
INSERT INTO main.parking_fine_records (vehicle_id, starting_datetime, ending_datetime, is_closed) VALUES | |
(1, '2020-01-01 13:30', '2020-01-04 15:00', 1), | |
(1, '2020-01-15 10:45', '2020-01-16 16:00', 1), | |
(1, '2020-02-01 12:00', null, 0), | |
(2, '2020-03-05 10:00', '2020-03-10 15:00', 1), | |
(3, '2020-06-05 10:00', null, 0); | |
INSERT INTO main.ticket_types (name, fine) VALUES | |
('Парковка в неположеном месте', 5000), | |
('Управление транспортным средством без водительских прав', 15000), | |
('Управление транспортным средством с техническими неисправностями', 500); | |
INSERT INTO main.parking_fine_records_to_ticket_types (parking_fine_record_id, ticket_type_id) VALUES | |
(1, 1), | |
(1, 2), | |
(2, 2), | |
(3, 3), | |
(4, 1), | |
(5, 3); | |
COMMIT; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* Количество записей на штрафстоянке машин с красным цветом. | |
*/ | |
SELECT | |
count(*) | |
FROM | |
parking_fine_records pfr | |
LEFT OUTER JOIN | |
vehicles v ON v.id = pfr.vehicle_id | |
WHERE | |
v.car_color = 'Красный' | |
; | |
/* | |
* Общая сумма штрафов уплаченая автовладельцами мужского пола. | |
*/ | |
SELECT | |
SUM(tt.fine) | |
FROM | |
parking_fine_records pfr | |
LEFT OUTER JOIN | |
parking_fine_records_to_ticket_types pfrtt ON pfr.id = pfrtt.parking_fine_record_id | |
LEFT OUTER JOIN | |
ticket_types tt on pfrtt.ticket_type_id = tt.id | |
LEFT OUTER JOIN | |
vehicles v on v.id = pfr.vehicle_id | |
LEFT OUTER JOIN | |
vehicles_to_vehicle_owners vtvo on v.id = vtvo.vehicle_id | |
LEFT OUTER JOIN | |
vehicle_owners vo on vtvo.vehicle_owner_id = vo.id | |
WHERE | |
vo.sex = 'Мужской'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
СУБД sqlite3
UML диаграмма