Приложение 1. Описание учебных баз данных

Упражнения выполняются на базах данных, описание которых приводится ниже.

1. Компьютерная фирма

Схема БД состоит из четырех таблиц:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, price, screen)
Printer(code, model, color, type, price)
Таблица Product представляет производителя (maker), номер модели (model) и тип ('PC' - ПК, 'Laptop' - ПК-блокнот или 'Printer' - принтер). Предполагается, что номера моделей в таблице Product уникальны для всех производителей и типов продуктов. В таблице PC для каждого ПК, однозначно определяемого уникальным кодом – code, указаны модель – model (внешний ключ к таблице Product), скорость - speed (процессора в мегагерцах), объем памяти - ram (в мегабайтах), размер диска - hd (в гигабайтах), скорость считывающего устройства - cd (например, '4x') и цена - price. Таблица Laptop аналогична таблице РС за исключением того, что вместо скорости CD содержит размер экрана -screen (в дюймах). В таблице Printer для каждой модели принтера указывается, является ли он цветным - color ('y', если цветной), тип принтера - type (лазерный – 'Laser', струйный – 'Jet' или матричный – 'Matrix') и цена - price.

2. Фирма вторсырья

Фирма имеет несколько пунктов приема вторсырья. Каждый пункт получает деньги для их выдачи сдатчикам вторсырья. Сведения о получении денег на пунктах приема записываются в таблицу:
Income_o(point, date, inc)
Первичным ключом является (point, date). При этом в столбец date записывается только дата (без времени), т.е. прием денег (inc) на каждом пункте производится не чаще одного раза в день. Сведения о выдаче денег сдатчикам вторсырья записываются в таблицу:
Outcome_o(point, date, out)
В этой таблице также первичный ключ (point, date) гарантирует отчетность каждого пункта о выданных деньгах (out) не чаще одного раза в день.
В случае, когда приход и расход денег может фиксироваться несколько раз в день, используется другая схема с таблицами, имеющими первичный ключ code:
Income(code, point, date, inc)
Outcome(code, point, date, out)
Здесь также значения столбца date не содержат времени.

3. Корабли

Рассматривается БД кораблей, участвовавших во второй мировой войне. Имеются следующие отношения:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей в БД. Корабль, давший название классу, называется головным.
Отношение Classes содержит имя класса, тип (bb для боевого (линейного) корабля или bc для боевого крейсера), страну, в которой построен корабль, число главных орудий, калибр орудий (диаметр ствола орудия в дюймах) и водоизмещение ( вес в тоннах). В отношении Ships записаны название корабля, имя его класса и год спуска на воду. В отношение Battles включены название и дата битвы, в которой участвовали корабли, а в отношении Outcomes – результат участия данного корабля в битве (потоплен-sunk, поврежден - damaged или невредим - OK).
Замечания. 1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships. 2) Потопленный корабль в последующих битвах участия не принимает.

4. Аэрофлот

Схема БД состоит из четырех отношений:
Company (ID_comp, name)
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Таблица Company содержит идентификатор и название компании, осуществляющей перевозку пассажиров. Таблица Trip содержит информацию о рейсах: номер рейса, идентификатор компании, тип самолета, город отправления, город прибытия, время отправления и время прибытия. Таблица Passenger содержит идентификатор и имя пассажира. Таблица Pass_in_trip содержит информацию о полетах: номер рейса, дата вылета (день), идентификатор пассажира и место, на котором он сидел во время полета. При этом следует иметь в виду, что
- рейсы выполняются ежедневно, а длительность полета любого рейса менее суток; town_from <> town_to;
- время и дата учитывается относительно одного часового пояса;
- время отправления и прибытия указывается с точностью до минуты;
- среди пассажиров могут быть однофамильцы (одинаковые значения поля name, например, Bruce Willis);
- номер места в салоне – это число с буквой; число определяет номер ряда, буква (a – d) – место в ряду слева направо в алфавитном порядке;
- связи и ограничения показаны на схеме данных.

5. Окраска

Схема базы данных состоит из трех отношений:
utQ (Q_ID int, Q_NAME varchar(35)); utV (V_ID int, V_NAME varchar(35), V_COLOR char(1)); utB (B_Q_ID int, B_V_ID int, B_VOL tinyint, B_DATETIME datetime).
Таблица utQ содержит идентификатор и название квадрата, цвет которого первоначально черный.
Таблица utV содержит идентификатор, название и цвет баллончика с краской.
Таблица utB содержит информацию об окраске квадрата баллончиком: идентификатор квадрата, идентификатор баллончика, количество краски и время окраски.
При этом следует иметь в виду, что:
- баллончики с краской могут быть трех цветов - красный V_COLOR='R', зеленый V_COLOR='G', голубой V_COLOR='B' (латинские буквы).
- объем баллончика равен 255 и первоначально он полный;
- цвет квадрата определяется по правилу RGB, т.е. R=0,G=0,B=0 - черный, R=255, G=255, B=255 - белый;
- запись в таблице закрасок utB уменьшает количество краски в баллончике на величину B_VOL и соответственно увеличивает количество краски в квадрате на эту же величину;
- значение 0 < B_VOL <= 255;
- количество краски одного цвета в квадрате не превышает 255, а количество краски в баллончике не может быть меньше нуля;
- время окраски B_DATETIME дано с точностью до секунды, т.е. не содержит миллисекунд.

Приложение 2. Список упражнений (SELECT)

Ниже приводится список рейтинговых упражнений первого этапа, необязательных к решению с точки зрения сертификации и продвижения по этапам.
Указывается номер, под которым упражнение фигурирует в заданиях, номер базы данных, к которой относится упражнение, и уровень сложности.

Номер	База	Уровень	Упражнение
-1	5	1	Методом наименьших квадратов найти линейную зависимость мгновенного расхода краски от времени: V = at + b, где V - расход краски; t - время в секундах, отсчитываемое от первой окраски (t = 0). Вывод: a с 8-ю знаками после десятичной точки; b - с 2-мя знаками после десятичной точки.
-2	3	2	Для каждой страны определить год, когда на воду было спущено максимальное количество ее кораблей. В случае, если окажется несколько таких лет, взять минимальный из них. Вывод: страна, количество кораблей, год
-3	5	2	На планете Торус 6x4 живут торусианцы трёх цветов (красные, зелёные и синие). Первые 24 квадрата таблицы utQ, отсортированные по Q_ID - страны планеты: Т00 - первый квадрат Т03 - четвёртый квадрат Т10 - пятый квадрат T53 - двадцать четвертый квадрат Количество краски на квадрате - численность торусианцев соответствующего цвета в стране (R, G, B). В день рождения Меркадота все торусианцы в каждой стране разбиваются на группы - 8 групп каждого цвета; численность каждой группы определяется как R/8, G/8, B/8 - и направляются в 8 соседних стран (по одной группе каждого цвета на страну). Не попавшие в группы торусианцы в количестве R%8, G%8, B%8 остаются дома. Найти количество торусианцев каждого цвета в каждой стране в этот знаменательный день. Вывести на карте Меркадота для страны "Т00" количество торусианцев каждого цвета в странах Txy (x - номер строки, y - номер столбца) в формате "Txy - cccR cccG cccB", где Txy - название страны, ccc - количество с ведущими нулями.
-4	5	2	Найти квадраты, время между первой и последней окраской которых превышает среднее время по всем окрашенным квадратам. Вывести название квадрата и наибольшее время между двумя его последовательными окрасками в секундах.
-5	3	1	В таблице Battles кроме крупных битв с участием многих кораблей содержатся также записи, начинающиеся с символа #, содержащие информацию о мелких стычках. Связанные инциденты объединены в группы от 1 до 6 стычек. Формат наименования для таких записей следующий: - после # идет код группы инцидентов (не содержит цифр, может отсутствовать) - далее идет номер группы в журнале регистрации (целое число, присутствует обязательно) - далее указывается номер инцидента внутри группы. Инциденты могут нумероваться арабскими и римскими цифрами, буквами латинского алфавита с различными разделителями, например a,b,c... /1,/2,/3... .1,.2,.3... i,ii,iii... и т.п. Нумерация едина для всей группы и не содержит пропусков. Первая или единственная стычка в группе может не иметь дополнительного номера. Требуется вывести список стычек, отсортированный по коду группы, номеру группы, дополнительному номеру. Вывод: наименование стычки, номер по порядку (начиная с 1)
-6	4	2	Опираясь на утверждение, что одна секунда полета каждого пассажира приносит перевозчику 1 цент (0.01$) дохода, провести АВС-анализ привлекательности пассажиров вне зависимости от перевозчика. В основе метода лежит принцип Парето - 20% всех товаров дают 80% оборота. При выполнении анализа пассажиры делятся на 3 категории по степени их ценности: А, В и С. Порядок проведения анализа: 1. Рассчитываем долю дохода пассажира от общей суммы дохода, приносимой всеми пассажирами, с накопительным итогом. Доля с накопительным итогом высчитывается путём прибавления доли конкретного пассажира к сумме долей пассажиров с не меньшей долей дохода (при одинаковой доле дохода меньший накопительный итог будет у пассажира, имя которого идет раньше в алфавитном порядке). 2. Выделяем категории А,В и С. Категория А - округленная до двух десятичных знаков накопительная доля с 0,00% по 80,00% включительно, категория В - с 80,01% до 95,00%, категория С - с 95,01% до 100%. Вывод: имя пассажира, сумма прибыли в долларах, доля с накопительным итогом в процентах (точность - 2 знака после запятой), категория (А, В или С - буквы латинские)
-7	1	1	В таблице Product найти модели, у которых первый символ представляет собой четную цифру, а последний - нечетную. При этом первый символ должен быть меньше последнего. Вывод: номер модели, тип модели, произведение первой и последней цифр в номере модели
-8	2	2	Фирма открывает новые пункты по приему вторсырья. При открытии, каждому из них были выданы "подъемные" в размере 20 т.р. Каждому из пунктов была поставлена задача об увеличении первоначального капитала до 150%, с отчетностью - один раз в день. Используя одну только таблицу Outcome_o и при условии, что пункты работают с двойной накруткой, то есть на каждый выплаченный сдатчику рубль они получают доход 2 рубля, найти: - Для пунктов, справившихся с заданием, определить дату его выполнения и сумму денежных средств, полученных сверх плана на эту дату; - Для пунктов, которые не справились с заданием, определить на последнюю отчетную дату сумму денежных средств, недостающих до его выполнения. Вывод: пункт, дата выполнения (или последний день), сумма сверх плана (или недостающую сумму до плана).
-9	3	1	В таблице Outcomes один тот же корабль может встречаться неоднократно (принимал участие в нескольких сражениях). Требуется найти корабли, имена которых различаются только регистром букв, например, "Duke of York" и "duke Of york". Вывод: имя корабля (любой из вариантов), число различных вариантов написания имени данного корабля в таблице.
-10	4	1	Отобразить карту полётов на статических картах Google. На карте вывести только уникальные направления полётов таким образом, что название первого города раньше по алфавиту, чем название второго города. Например, если есть рейс из Рима в Берлин, но нет из Берлина в Рим, то вывести направление Берлин-Рим. Или если есть оба рейса, из Милана в Мадрид и из Мадрида в Милан, то вывести только направление Мадрид-Милан. Полученные направления вывести в алфавитном порядке их городов. Для каждого города вывести метки с первой буквой его названия. В итоге должна получиться строка следующего вида (без переводов строк): <img src="http://maps.googleapis.com/maps/api/staticmap ?path=weight:3|Aaa|Bbb &path=weight:3|Aaa|Ccc &path=weight:3|Bbb|Ccc &markers=label:A|Aaa &markers=label:B|Bbb &markers=label:C|Ccc &size=512x512&sensor=false"> где <img src="http://maps.googleapis.com/maps/api/staticmap - это указание использовать статические карты Google; ?path=weight:3|Aaa|Bbb - первое направление, из пункта Aaa в Bbb, с толщиной линии 3; &path=weight:3|... - все следующие направления; &markers=label:A|Aaa - метка (A) города Aaa; &size=512x512 - указание размера карты 512x512; &sensor=false"> - обязательный параметр.
-11	3	1	Для каждого корабля из таблицы Ships вывести его название в base64 (wikipedia). Вывод: name, base64name.
-12	4	1	В аэропорту математик Иванов развлекал себя подсчетом факториала от номера рейса. Для каждого рейса в Trip определите на сколько нулей оканчивается вычисленное Ивановым число. Замечание: номер рейса содержит не более 4 цифр. Вывод: trip_no, число нулей.
-13	4	1	Определить количество перевезенных пассажиров за каждый календарный день (по дате вылета) первого полугодия 2003 года, начиная от даты первого рейса и заканчивая датой последнего рейса в этом полугодии. Полугодием считать интервал с (01.01.03 по 30.06.03). Вывести дату, количество пассажиров.
-14	3	1	Вывести названия только тех классов, у которых каждый корабль имеет такое имя, что любой его символ встречается в названии какого-либо класса. Замечание: регистр букв при сравнении не учитывается.
-15	5	2	На плоском песчаном пляже в координатах B_Q_ID, B_V_ID установлены круглые пляжные зонтики радиуса B_VOL. Зонтики параллельны песку, солнце в зените. Каждое значение B_DATETIME - отдельная задача. Найти площадь тени для каждого B_DATETIME. Вывод: B_DATETIME, площадь тени округлённая до целых.
-16	2	1	Найти такие пункты приема, которые имеют в таблице Outcome записи на каждый рабочий день в течение некоторой недели (календарные дни, исключая субботу и воскресенье). Вывод: номер пункта, дата понедельника полной рабочей недели в формате "YYYY-MM-DD", суммарное значение out за эту рабочую неделю.