Домашнее задание БД


Московский Государственный
Технический Университет
им. Н.Э. Баумана
Калужский филиал
Домашнее задание 1
по дисциплине:
Базы данных.
Калуга, 2013
Описание предметной области.
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 содержит информацию о полетах: номер рейса, дата вылета (день), идентификатор пассажира и место, на котором он сидел во время полета. При этом следует иметь в виду, что- рейсы выполняются ежедневно, а длительность полета любого рейса менее суток;- время и дата учитывается относительно одного часового пояса;- время отправления и прибытия указывается с точностью до минуты;- среди пассажиров могут быть однофамильцы (одинаковые значения поля 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, а количество краски в баллончике не может быть меньше нуля.

Список заданий
Номер База Задание
1 1 Для каждой пятой модели (в порядке возрастания номеров моделей) из таблицы Product определить тип продукции и среднюю цену модели.
2 3 Для таблицы Outcomes преобразовать названия кораблей, содержащих более одного пробела, следующим образом. Заменить все символы между первым и последним пробелами (исключая сами эти пробелы) на символы звездочки (*) в количестве, равном числу замененных символов. Вывод: название корабля, преобразованное название корабля
3 2 Определить лидера по сумме выплат в соревновании между каждой парой пунктов с одинаковыми номерами из двух разных таблиц - outcome и outcome_o - на каждый день, когда осуществлялся прием вторсырья хотя бы на одном из них. Вывод: Номер пункта, дата, текст: - "once a day", если сумма выплат больше у фирмы с отчетностью один раз в день; - "more than once a day", если - у фирмы с отчетностью несколько раз в день; - "both", если сумма выплат одинакова.
4 1 Посчитать сумму цифр в номере каждой модели из таблицы Product Вывод: номер модели, сумма цифр
5 4 Определить количество перевезенных пассажиров за каждый календарный день (по дате вылета) первого полугодия 2003 года, начиная от даты первого рейса и заканчивая датой последнего рейса в этом полугодии. Полугодием считать интервал с (01.01.03 по 30.06.03). Вывести дату, количество пассажиров.
6 4 Определить имена разных пассажиров, которым чаще других доводилось лететь на одном и том же месте. Вывод: имя и количество полетов на одном и том же месте.
7 4 Среди пассажиров, летавших на самолетах только одного типа, определить тех, кто прилетал в один и тот же город не менее 2-х раз. Вывести имена пассажиров.
8 3 Для каждого сражения определить день, являющийся последней пятницей месяца, в котором произошло данное сражение. Вывод: сражение, дата сражения, дата последней пятницы месяца. Даты представить в формате "yyyy-mm-dd"
9 4 Определить имена разных пассажиров, которые летали только между двумя городами (туда и/или обратно).
10 4 Выбрать из таблицы Trip такие города, названия которых содержат минимум 2 разные буквы из списка (a,e,i,o,u) и все имеющиеся в названии буквы из этого списка встречаются одинаковое число раз.
11 4 Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 2003 года определить компанию, номер рейса и дату вылета. Замечание. Считать, что два рейса одновременно вылететь из Ростова не могут.
12 5 Предполагая, что среди идентификаторов квадратов имеются пропуски, найти минимальный и максимальный "свободный" идентификатор в диапазоне между имеющимися максимальным и минимальным идентификаторами. Например, для последовательности идентификаторов квадратов 1,2,5,7 результат должен быть 3 и 6. Если пропусков нет, вместо каждого искомого значения выводить NULL.
13 2 Фирма открывает новые пункты по приему вторсырья. При открытии, каждому из них были выданы "подъемные" в размере 20 т.р. Каждому из пунктов была поставлена задача об увеличении первоначального капитала до 150%, с отчетностью - один раз в день. Используя одну только таблицу Outcome_o и при условии, что пункты работают с двойной накруткой, то есть на каждый выплаченный сдатчику рубль они получают доход 2 рубля, найти: - Для пунктов, справившихся с заданием, определить дату его выполнения и сумму денежных средств, полученных сверх плана на эту дату; - Для пунктов, которые не справились с заданием, определить на последнюю отчетную дату сумму денежных средств, недостающих до его выполнения. Вывод: пункт, дата выполнения (или последний день), сумма сверх плана (или недостающую сумму до плана).
14 1 Для таблицы Product получить результирующий набор в виде таблицы со столбцами maker, pc, laptop и printer, в которой для каждого производителя требуется указать, производит он (yes) или нет (no) соответствующий тип продукции. В первом случае (yes) указать в скобках без пробела количество имеющихся в наличии (т.е. находящихся в таблицах PC, Laptop и Printer) различных по номерам моделей соответствующего типа.
15 1 Дима и Миша пользуются продуктами от одного и того же производителя. Тип Таниного принтера не такой, как у Вити, но признак "цветной или нет" - совпадает. Размер экрана Диминого ноутбука на 3 дюйма больше Олиного. Мишин ПК в 4 раза дороже Таниного принтера. Номера моделей Витиного принтера и Олиного ноутбука отличаются только третьим символом. У Костиного ПК скорость процессора, как у Мишиного ПК; объем жесткого диска, как у Диминого ноутбука; объем памяти, как у Олиного ноутбука, а цена - как у Витиного принтера. Вывести все возможные номера моделей Костиного ПК.
16 4 Считая, что первый пункт вылета является местом жительства, найти пассажиров, которые находятся вне дома. Вывод: имя пассажира, город проживания
17 5 Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид "yyyy" для года, "yyyy-mm" для месяца и "yyyy-mm-dd" для дня. Вывести только те группы, в которых количество различных моментов времени (b_datetime), когда выполнялась окраска, более 10. Вывод: идентификатор группы, суммарное количество потраченной краски.
18 2 Вывести все записи из Outcome и Income, даты которых отстоят не менее чем на 2 календарных месяца от максимальной даты в обеих таблицах (т.е. при максимальной дате 2009-12-05 последняя выводимая дата должна быть меньше 2009-10-01). Выполнить помесячное разбиение этих записей, присвоив порядковый номер каждому месяцу (с учётом года), попавшему в выборку. Вывод: порядковый номер месяца, первый день месяца в формате "yyyy-mm-dd", последний день месяца в формате "yyyy-mm-dd", код записи, пункт, дата, сумма (для таблицы Outcome должна быть отрицательной)
19 3 Найдите названия всех тех кораблей из базы данных, о которых можно определенно сказать, что они были спущены на воду до 1941 г.
20 4 Предполагая, что не существует номера рейса большего 65535, вывести номер рейса и его представление в двоичной системе счисления (без ведущих нулей)
21 4 Среди пассажиров, которые пользовались услугами не менее двух авиакомпаний, найти тех, кто совершил одинаковое количество полётов самолетами каждой из этих авиакомпаний. Вывести имена таких пассажиров.
22 2 Написать запрос, который выводит все операции прихода и расхода из таблиц Income и Outcome в следующем виде: дата, порядковый номер записи за эту дату, пункт прихода, сумма прихода, пункт расхода, сумма расхода. При этом все операции прихода по всем пунктам, совершённые в течение одного дня, упорядочены по полю code, и так же все операции расхода упорядочены по полю code. В случае, если операций прихода/расхода за один день было не равное количество, выводить NULL в соответствующих колонках на месте недостающих операций.
23 2 Найти такие пункты приема, которые имеют в таблице Outcome записи на каждый рабочий день в течение некоторой недели (календарные дни, исключая субботу и воскресенье). Вывод: номер пункта, дата понедельника полной рабочей недели в формате "YYYY-MM-DD", суммарное значение out за эту рабочую неделю.

Варианты:
Номера заданий: 1, 3, 8, 19
Номера заданий: 2, 9, 10, 20
Номера заданий: 4, 11, 13, 21
Номера заданий: 5, 12, 14, 22
Номера заданий: 6, 15, 16, 23
Номера заданий: 7, 17, 18, 14.

Приложенные файлы

  • docx 625656
    Размер файла: 68 kB Загрузок: 3

Добавить комментарий