parking-fine-create-tables.sql

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;

parking-fine-queries.sql

/*
 * Количество записей на штрафстоянке машин с красным цветом.
 */
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 = 'Мужской';

СУБД sqlite3

UML диаграмма