Продолжаем тему изучения баз данных. Первая часть (10 модуль) тут:
Задача 1. Перевозка вакцины
Условия хранения этой вакцины весьма необычные — в отсеке должна быть температура -18 ± 2 градуса. Если температурный режим был нарушен, вакцина считается испорченной.
Для проверки состояния вакцины применяется датчик, который раз в час измеряет температуру внутри контейнера. Если температура в контейнере была вне указанной хотя бы три часа, температурный режим считается нарушенным.
Реализуйте функцию, которая по номеру грузовика определяет, испортилась ли вакцина, с помощью таблицы table_truck_with_vaccine.
Для реализации данной задачи применяются три ключевых слова в запросе BETWEEN, AND, EXISTS. С их помощью мы и составим запрос к базе данных, ответ от которой будет либо 0 (False), либо 1 (True). Я было подумал, нужно же проверить а вообще существуют ли номера автомобилей в базе данных?! Но потом ответ родился сам собой: если номера в базе данных нет, то нам будет возвращаться False (или ноль), то же самое будет возвращаться если температурный режим не нарушен. То какая нам разница есть или нет номер автомобиля в БД, если он не нарушал никаких температурных режимов.
То есть True (1) у нас вернется только в том случае, если номер автомобиля существует в базе данных и он нарушает условия температурного режима.
Вот как это выглядит в коде:
Формулировка задачи довольно таки туманная, возможно нужно еще проверять по штампу даты-времени, был ли нарушен температурный режим в течении трёх часов...
Поэтому я это оставлю как есть. И если при проверке домашнего задания потребуется высчитывать дельту по времени - займусь. А пока едем дальше...
P.S: Подписчица решила за меня "расчет дельты по времени", а у меня и так прокатило, поэтому посчитал целесообразным вставить её код в статью:
Задача 2. Избавление от штрафов
Вы работаете программистом в IT-отделе ГИБДД. Ваш отдел отвечает за обслуживание камер, которые фиксируют превышение скорости и выписывают автоматические штрафы.
За последний месяц к вам пришло больше тысячи жалоб на ошибочно назначенные штрафы, из которых около 100 были действительно ошибочными.
Реализуйте функцию, которая по названию CSV-файла с данными об ошибочных штрафах удалит записи о них из таблицы table_fees.
Список из дат и номеров автомобилей ошибочных штрафов хранится в файле wrong_fees.csv.
Суть задания сводится к тому чтобы сопоставить данные в базе данных в таблице table_fees:
С данными в файле CSV:
Если находим номер автомобиля и время в файле CSV, то такой же номер и время удаляем из базы данных.
Самым простым способом удалить записи об автомобилях с номером "таким-то" это пройтись циклом и по одному номеру удалять, но это не наш метод. Наверняка существует способ как удалить из базы все записи об автомобилях с номерами из полученного списка. Так и есть, делается всё довольно таки просто.
Вроде всё просто получилось...
Если мы попробуем выполнить поиск автомобилей с такими номерами которые мы только что удалили, то будет пусто...
Вроде бы задача выполнена, можно приступать к следующей...
Задача 3. Журнал птиц
Юный натуралист Петя решил посетить Юнтоловский заказник на рассвете и записать в журнал всех птиц, которых увидел в заказнике. Он написал программу, но в процессе так устал, что уснул на клавиатуре, поэтому половина программы стёрлась.
Петя точно помнит, что программа позволяла добавить в БД новую птицу и говорила, видел ли он её раньше.
Помогите восстановить исходный код программы ЮНат v0.1, реализовав функции log_bird (добавление новой птицы в БД) и check_if_such_bird_already_seen (уже видел такую птицу).
Изначально, в программе почему-то было реализовано сначала добавление птицы в базу данных а потом добавление, я решил что так будет неправильно и изменил порядок вызова функций. И вот что у меня получилось в коде:
Всё довольно просто... Но если понадобятся объяснения - комменты внизу...
Задача 4. Эффективный менеджер
Иван Совин — эффективный менеджер. Когда к нему приходит сотрудник, чтобы просить повышение з/п, Иван может повысить её только на 10%.
Если з/п сотрудника будет больше з/п Ивана Совина, сотрудника уволят. Если з/п сотрудника при увеличении не превысит з/п Ивана, то з/п сотрудника повышают.
Помогите Ивану стать ещё эффективнее, автоматизировав его нелёгкий труд. Реализуйте функцию, которая по имени сотрудника либо повышает его з/п, либо увольняет его (удаляет запись о нём из БД).
Таблица с данными называется table_effective_manager.
Зарплата Ивана Совина вынесена в константу.
Эффективный менеджер не удаляется из БД.
Хитрая ситуация получается. Хочешь ты такой увеличения зарплаты, приходишь к руководителю и он тебе предлагает сыграть в игру: Если после увеличения на 10 процентов твоя зарплата станет больше моей - ТЫ УВОЛЕН!!!
А если нет, то всё ок и я тебе подниму зарплату! Прикольно. Я было хотел сделать всё одним запросом к базе данных, но в условиях задачи этого не было. Значит можно не городить сложных конструкций в SQL-запросах. И вот что из этого вышло:
Я увеличивал зарплату сотруднику "Кузнецову Щ.Я, с id=22 и начальной зарплатой в 40 тысяч" до тех пор, пока он не удалился из базы данных))) Да простит меня тот сотрудник с именем на Щ. Кто нибудь знает имя на Щ???
Задача 5. Жеребьёвка УЕФА
Глава УЕФА попросил вас провести жеребьёвку команд для предстоящего Чемпионата Европы.
Имеется N групп. В каждую группу попадают:
одна сильная команда;
две средние команды;
одна слабая команда.
Реализуйте функцию, которая по количеству групп (от 4 до 16) генерирует данные, которыми заполняются две таблицы:
Таблица со списком команд (uefa_commands):
№ команды | название | страна | уровень команды
Таблица с результатами жеребьевки (uefa_draw):
№ команды | № группы
Программа корректно заполняет любое количество групп от 4 до 16.
Названия команд уникальны.
Соблюдено условие распределения по уровням.
Не уверен в правильности реализации данной задачи. По идее можно было заморочиться разными проверками, накидать данных которые не будут повторяться. Но по большому счету, смысл данного задания - массовая загрузка в базу данных. И она выполнена, может быть не так красиво как хотелось бы...
Я совершенно ничего не понимаю в футболе и мне это не интересно. Поэтому расклад получился такой:
1. Накидать в БД какой то информации о странах и названиях футбольных клубов. Я сделал это много раз, дабы в следующем пункте вероятность наткнуться на не уникальное значение номера футбольной команды стремилась к минимуму. Но она может быть
2. Составляем список кортежей, так чтобы в кортеже присутствовал номер группы и номер команды, каждый номер состоит из четырех команд, при том одна команда слабая, две средних и одна сильная.
3.Вставляем наш полученный список кортежей в sql-запрос...
Данная программа неплохо работает на 4-5 групп, дальше, с повышением числа групп, всё хуже и хуже. Я пока всё это дело оставлю как есть, но если при проверке ДЗ куратор вернет на доработку - отпишусь в комментариях.
А пока, считаю её выполненной.
Задача 6. Завод «Дружба»
На заводе «Дружба» дружный коллектив. Сотрудники работают посменно, в смене десять человек. Всего 366 рабочих.
Бухгалтер завода составила расписание смен на 366 дней и занесла его в БД в таблицу table_friendship_schedule, но не учла тот факт, что все сотрудники ходят на различные спортивные кружки:
понедельник — футбол;
вторник — хоккей;
среда — шахматы;
четверг — SUP-сёрфинг;
пятница — бокс;
суббота — Dota2;
воскресенье — шахбокс.
Нельзя выходить на работу в день тренировки: сотрудник, занимающийся шахматами, не может работать в среду, а его коллега-боксёр — не может в пятницу.
Помогите изменить расписание смен с учётом личных предпочтений рабочих.
Количество вложенных циклов минимально. Постарайтесь, чтобы сложность вашего алгоритма не превышала O(NlogN + MlogM), где N и M — количество сотрудников и рабочих дней соответственно. О том, что такое O большое, можно почитать в статьях Big O и «Сложность алгоритмов. Big O. Основы». Задействована большая часть работников.
На ум приходит вот такое решение: первым запросом к БД я получаю значения таблицы table_friendship_schedule, из нее делаю список кортежей вида: [(номер дня недели от 0 до 6, id-работника, дата выхода на работу), ]. Вторым запросом я получаю список кортежей всех работников. У меня есть константа дней недели, где дню недели соответствует цифра и занятие работником спортом. Получаются вот такие данные на выходе:
По идее, на основании этих данных, нужно составить некую структуру данных для обновления списка работников по сменам. Самое главное условие - нельзя выходить на работу в день тренировки!!!
У меня получился вот такой алгоритм, он меняет человека на выбранного случайным образом из всего списка сотрудников, если нам выпадает снова такой человек, который тренируется и работает в один и тот же день мы снова запускаем рандомный выбор человека, до тех пор пока не попадется нужный. Единственный косяк - в один и тот же день могут работать два одинаковых человека в одной смене (буду думать что этот человек работает за две ставки:-))). Да, программа отработает только один раз, ибо она заменит старые данные на новые и все условия уже выполняться не будут и ничего в таблице уже меняться не будет.
И вот какой результат получается в таблице:
Изначальные данные:
Ну собственно посчитаю что я данную задачу выполнил и иду дальше...
Задача 7. Делаем инъекцию
Мы часто говорили о важности параметризованных запросов и упоминали понятие SQL-инъекции. Давайте взглянем на это со стороны хакера.
В таблице table_users имена и пароли пользователей. Также есть функция register, она регистрирует пользователя, добавляя запись в эту таблицу. Функцию писал неопытный и наивный программист, поэтому в ней есть уязвимые места.
Ваша задача — задать переменным username и password в функции hack такие значения, чтобы с таблицей произошло нечто страшное. Например, её удаление или добавление очень большого количества новых записей.
Как оказалось, всё довольно таки просто. Надо только правильно сделать разделения между запросами и получается нечто подобное. Делать это осторожно ибо первая запись очищает таблицу от всех данных. Вторая создает новую таблицу и вставляет туда свои данные.
Вот такой получается результат...
Ну вот как-то так. Про футболистов - у меня есть сомнения, возможно куратор вернет на доработку.
Коллеги, товарищи, это мой конспект и пишется он налету, тут может быть множество ошибок и недоработок. Не нужно рассматривать данную статью как руководство к действию. Возможно кому-то это поможет выйти из ступора непонимания и прокрастинации.
Если будут изменения в коде, я об этом сообщу в комментах.