|
|
базы данных дз. Домашнее задание БД (2). Калужский филиал
Министерство образования и науки Российской Федерации
Калужский филиал
федерального государственного бюджетного образовательного учреждения
высшего образования
«Московский государственный технический университет имени Н.Э. Баумана
(национальный исследовательский университет)»
(КФ МГТУ им. Н.Э. Баумана)
Домашнее задание 1 по дисциплине: Базы данных.
Калуга, 2019
Описание предметной области.
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, например, BruceWillis);
- номер места в салоне – это число с буквой; число определяет номер ряда, буква (a – d) – место в ряду слева направо в алфавитном порядке;
- связи и ограничения показаны на схеме данных.
Список заданий
Номер
|
База
|
^
|
Упражнение
|
1
|
1
|
1
|
Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd
|
2
|
1
|
1
|
Найдите производителей принтеров. Вывести: maker
|
3
|
1
|
1
|
Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.
|
4
|
1
|
1
|
Найдите все записи таблицы Printer для цветных принтеров.
|
5
|
1
|
1
|
Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12х или 24х CD и цену менее 600 дол.
|
6
|
1
|
1
|
Укажите производителя и скорость для тех ПК-блокнотов, которые имеют жесткий диск объемом не менее 10 Гбайт.
|
7
|
1
|
1
|
Найдите номера моделей и цены всех продуктов (любого типа) выпущенных производителем B (латинская буква).
|
8
|
1
|
2
|
Найдите производителя, продающего ПК, но не ПК-блокноты.
|
9
|
1
|
1
|
Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker
|
10
|
1
|
1
|
Найдите принтеры, имеющие самую высокую цену. Вывести: model, price
|
11
|
1
|
1
|
Найдите среднюю скорость ПК.
|
12
|
1
|
1
|
Найдите среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.
|
13
|
1
|
1
|
Найдите среднюю скорость ПК, выпущенных производителем A.
|
14
|
1
|
2
|
Для таблицы Product получить результирующий набор в виде таблицы со столбцами maker, pc, laptop и printer, в которой для каждого производителя требуется указать, производит он (yes) или нет (no) соответствующий тип продукции. В первом случае (yes) указать в скобках без пробела количество имеющихся в наличии (т.е. находящихся в таблицах PC, Laptop и Printer) различных по номерам моделей соответствующего типа.
|
15
|
1
|
2
|
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
|
16
|
1
|
2
|
Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.
|
17
|
1
|
2
|
Найдите ПК-блокноты, скорость которых меньше скорости любого из ПК. Вывести: type, model, speed
|
18
|
1
|
2
|
Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price
|
19
|
1
|
2
|
Для каждого производителя найдите средний размер экрана выпускаемых им ПК-блокнотов. Вывести: maker, средний размер экрана.
|
20
|
1
|
2
|
Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей
|
21
|
1
|
2
|
Найдите максимальную цену ПК, выпускаемых каждым производителем. Вывести: maker, максимальная цена.
|
22
|
1
|
2
|
Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену компьютера с такой же скоростью. Вывести: speed, средняя цена.
|
23
|
1
|
3
|
Найдите производителей, которые производили бы как ПК со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц. Вывести: Maker
|
24
|
1
|
3
|
Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции.
|
25
|
1
|
3
|
Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM. Вывести: Maker
|
26
|
1
|
3
|
Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена.
|
27
|
1
|
3
|
Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD.
|
28
|
1
|
3
|
Найдите средний размер диска ПК (одно значение для всех) тех производителей, которые выпускают и принтеры. Вывести: средний размер HD
|
29
|
2
|
3
|
В предположении, что приход и расход денег на каждом пункте приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт, дата)], написать запрос с выходными данными (пункт, дата, приход, расход). Использовать таблицы Income_o и Outcome_o.
|
30
|
2
|
3
|
В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз [в обе таблицы добавлен первичный ключ code] , написать запрос с выходными данными (point, date, out, inc), в котором каждому пункту за каждую дату соответствует одна строка.
|
31
|
3
|
1
|
Для классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и страну.
|
32
|
3
|
4
|
Одной из характеристик корабля является половина куба калибра его главных орудий (mw). С точностью до 2 десятичных знаков определите среднее значение mw для кораблей каждой страны, у которой есть корабли в базе данных.
|
33
|
3
|
1
|
Укажите корабли, потопленные в сражениях в Северной Атлантике (North Atlantic). Вывод: ship.
|
34
|
3
|
1
|
По Вашингтонскому международному договору от начала 1922 г. запрещалось строить линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие этот договор (учитывать только корабли c известным годом спуска на воду). Вывести названия кораблей.
|
35
|
1
|
3
|
В таблице Product найти модели, у которых первый символ представляет собой четную цифру, а последний - нечетную.
При этом первый символ должен быть меньше последнего.
Вывод: номер модели, тип модели, произведение первой и последней цифр в номере модели
|
36
|
3
|
2
|
Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).
|
37
|
3
|
2
|
Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).
|
38
|
3
|
2
|
Найдите страны, имевшие когда-либо классы обычных боевых кораблей ('bb') и имевшие когда-либо классы крейсеров ('bc').
|
39
|
3
|
3
|
Найдите корабли, "сохранившиеся для будущих сражений"; т.е. выведенные из строя в одной битве (damaged), они участвовали в другой.
|
40
|
3
|
1
|
Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.
|
41
|
1
|
3
|
Для ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме кода) в два столбца:
- название характеристики (имя соответствующего столбца в таблице PC);
- значение характеристики
|
42
|
3
|
1
|
Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.
|
43
|
3
|
2
|
Для каждой страны определить год, когда на воду было спущено максимальное количество ее кораблей. В случае, если окажется несколько таких лет, взять минимальный из них. Вывод: страна, количество кораблей, год
|
44
|
3
|
1
|
Найдите названия всех кораблей в базе данных, начинающихся с буквы R.
|
45
|
3
|
1
|
Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V).
|
46
|
3
|
3
|
Укажите названия, водоизмещение и число орудий кораблей, участвовавших в сражении при Гвадалканале (Guadalcanal).
|
47
|
1
|
2
|
Пронумеровать строки из таблицы Product в следующем порядке: имя производителя в порядке убывания числа производимых им моделей (при одинаковом числе моделей имя производителя в алфавитном порядке по возрастанию), номер модели (по возрастанию).
Вывод: номер в соответствии с заданным порядком, имя производителя (maker), модель (model)
|
48
|
3
|
2
|
Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.
|
49
|
3
|
2
|
Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).
|
50
|
3
|
1
|
Найдите сражения, в которых участвовали корабли класса Kongo из таблицы Ships.
|
51
|
3
|
3
|
Найдите названия кораблей, имеющих наибольшее число орудий среди всех кораблей такого же водоизмещения (учесть корабли из таблицы Outcomes).
|
52
|
3
|
3
|
Определить названия всех кораблей из таблицы Ships, которые могут быть линейным японским кораблем, имеющим число главных орудий не менее девяти, калибр орудий менее 19 дюймов и водоизмещение не более 65 тыс.тонн
|
53
|
3
|
1
|
Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков.
|
54
|
3
|
2
|
С точностью до 2-х десятичных знаков определите среднее число орудий всех линейных кораблей (учесть корабли из таблицы Outcomes).
|
55
|
3
|
1
|
Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.
|
56
|
3
|
3
|
Для каждого класса определите число кораблей этого класса, потопленных в сражении. Вывести: класс и число потопленных кораблей.
|
57
|
3
|
3
|
Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.
|
 58
|
1
|
4
|
Для каждого типа продукции и каждого производителя из таблицы Product c точностью до двух десятичных знаков найти процентное отношение числа моделей данного типа данного производителя к общему числу моделей этого производителя.
Вывод: maker, type, процентное отношение числа моделей данного типа к общему числу моделей производителя
|
59
|
2
|
3
|
Посчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток.
|
60
|
2
|
2
|
Посчитать остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток.
Замечание. Не учитывать пункты, информации о которых нет до указанной даты.
|
61
|
2
|
2
|
Посчитать остаток денежных средств на всех пунктах приема для базы данных с отчетностью не чаще одного раза в день.
|
62
|
2
|
2
|
Посчитать остаток денежных средств на всех пунктах приема на начало дня 15/04/01 для базы данных с отчетностью не чаще одного раза в день.
|
63
|
4
|
2
|
Определить имена разных пассажиров, когда-либо летевших на одном и том же месте более одного раза.
|
64
|
2
|
2
|
Используя таблицы Income и Outcome, для каждого пункта приема определить дни, когда был приход, но не было расхода и наоборот.
Вывод: пункт, дата, тип операции (inc/out), денежная сумма за день.
|
65
|
1
|
3
|
Пронумеровать уникальные пары {maker, type} из Product, упорядочив их следующим образом:
- имя производителя (maker) по возрастанию;
- тип продукта (type) в порядке PC, Laptop, Printer.
Если некий производитель выпускает несколько типов продукции, то выводить его имя только в первой строке; остальные строки для ЭТОГО производителя должны содержать пустую строку символов ('').
|
66
|
4
|
3
|
Для всех дней в интервале с 01/04/03 по 07/04/03 определить число рейсов из Rostov.
Вывод: дата, количество рейсов
|
67
|
4
|
2
|
Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов.
Замечания.
1) A - B и B - A считать РАЗНЫМИ маршрутами.
2) Использовать только таблицу Trip
|
68
|
4
|
3
|
Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов.
Замечания.
1) A - B и B - A считать ОДНИМ И ТЕМ ЖЕ маршрутом.
2) Использовать только таблицу Trip
|
69
|
2
|
2
|
По таблицам Income и Outcome для каждого пункта приема найти остатки денежн`ых средств на конец каждого дня, в который выполнялись операции по приходу и/или расходу на данном пункте. Учесть при этом, что деньги не изымаются, а остатки/задолженность переходят на следующий день.
Вывод: пункт приема, день в формате "dd/mm/yyyy", остатки/задолженность на конец этого дня.
|
70
|
3
|
3
|
Укажите сражения, в которых участвовало по меньшей мере три корабля одной и той же страны.
|
71
|
1
|
1
|
Найти тех производителей ПК, все модели ПК которых имеются в таблице PC.
|
72
|
4
|
3
|
Среди тех, кто пользуется услугами только какой-нибудь одной компании, определить имена разных пассажиров, летавших чаще других. Вывести: имя пассажира и число полетов.
|
73
|
3
|
1
|
Для каждой страны определить сражения, в которых не участвовали корабли данной страны. Вывод: страна, сражение
|
74
|
3
|
2
|
Вывести классы всех кораблей России (Russia). Если в базе данных нет классов кораблей России, вывести классы для всех имеющихся в БД стран. Вывод: страна, класс
|
75
|
3
|
3
|
Для каждого корабля из таблицы Ships указать название ближайшего по времени сражения из таблицы Battles,в котором корабль мог бы участвовать после спуска на воду. Если год спуска на воду неизвестен, взять последнее по времени сражение.Если нет сражения, произошедшего после спуска на воду корабля, вывести NULL вместо названия сражения.
Замечание. Считать, что корабль может участвовать в сражении, которое произошло в год спуска на воду корабля. Вывод: имя корабля, год спуска на воду, название сражения
|
76
|
3
|
2
|
Определить названия всех кораблей из таблицы Ships, которые удовлетворяют, по крайней мере, комбинации любых четырёх критериев из следующего списка: numGuns = 8 bore = 15 displacement = 32000 type = bb launched = 1915 class=Kongo country=USA
|
77
|
4
|
1
|
Для каждой компании, перевозившей пассажиров, подсчитать время, которое провели в полете самолеты с пассажирами. Вывод: название компании, время в минутах.
|
78
|
3
|
2
|
Найдите названия всех тех кораблей из базы данных, о которых можно определенно сказать, что они были спущены на воду до 1941 г.
|
79
|
3
|
1
|
Для каждого сражения определить первый и последний день месяца, в котором оно состоялось.
Вывод: сражение, первый день месяца, последний день месяца.
Замечание: даты представить без времени в формате "yyyy-mm-dd".
|
80
|
2
|
2
|
Определить лидера по сумме выплат в соревновании между каждой парой пунктов с одинаковыми номерами из двух разных таблиц - outcome и outcome_o - на каждый день, когда осуществлялся прием вторсырья хотя бы на одном из них. Вывод: Номер пункта, дата, текст: - "once a day", если сумма выплат больше у фирмы с отчетностью один раз в день; - "more than once a day", если - у фирмы с отчетностью несколько раз в день; - "both", если сумма выплат одинакова.
|
81
|
3
|
3
|
Вес снаряда (в фунтах), выпускаемого орудием, примерно равен половине куба его калибра (в дюймах). Определите средний вес снарядов для кораблей каждой страны. Учесть также корабли из таблицы Outcomes.
|
82
|
2
|
2
|
Фирма открывает новые пункты по приему вторсырья. При открытии, каждому из них были выданы "подъемные" в размере 20 т.р. Каждому из пунктов была поставлена задача об увеличении первоначального капитала до 150%, с отчетностью - один раз в день, т.е. использовать только таблицу Outcome_o. При условии, что пункты работают с двойной накруткой, то есть на каждый выплаченный сдатчику рубль они получают доход 2 рубля, найти: - Для пунктов, справившихся с заданием, определить дату его выполнения и сумму денежных средств, полученных сверх плана; - Для пунктов, которые не справились с заданием, определить на последнюю отчетную дату сумму денежных средств, недостающих до его выполнения. Вывод: пункт, дата выполнения (или последний день), сумма сверх плана (или недостающую сумму до плана).
|
87
|
4
|
4
|
Считая, что пункт самого первого вылета пассажира является местом жительства, найти не москвичей, которые прилетали в Москву более одного раза. Вывод: имя пассажира, количество полетов в Москву
|
100
|
3
|
2
|
Для таблицы Outcomes преобразовать названия кораблей, содержащих более одного пробела, следующим образом. Заменить все символы между первым и последним пробелами (исключая сами эти пробелы) на символы звездочки (*) в количестве, равном числу замененных символов. Вывод: название корабля, преобразованное название корабля
|
101
|
4
|
3
|
Среди пассажиров, летавших на самолетах только одного типа, определить тех, кто прилетал в один и тот же город не менее 2-х раз. Вывести имена пассажиров.
|
102
|
3
|
2
|
Для каждого сражения определить день, являющийся последней пятницей месяца, в котором произошло данное сражение. Вывод: сражение, дата сражения, дата последней пятницы месяца. Даты представить в формате "yyyy-mm-dd"
|
103
|
4
|
3
|
Определить имена разных пассажиров, которые летали только между двумя городами (туда и/или обратно).
|
Варианты:
2. Номера заданий: 13, 18, 64, 58
3. Номера заданий: 6, 49, 72, 87
4. Номера заданий: 9, 60, 81, 32
5. Номера заданий: 55, 23, 52, 87
6. Номера заданий: 12, 17, 63, 32
7. Номера заданий: 4, 47, 26, 68
8. Номера заданий: 1, 37, 23, 59
9. Номера заданий: 79, 27, 65, 101
10. Номера заданий: 12, 21, 52, 32
11. Номера заданий: 71, 24, 56, 58
12. Номера заданий: 16, 35, 59, 72
13. Номера заданий: 13, 22, 56, 58
14. Номера заданий: 15, 30, 57, 70
15. Номера заданий: 19, 46, 72, 101
16. Номера заданий: 17, 39, 65, 75
17. Номера заданий: 18, 41, 70, 81
18. Номера заданий: 7, 54, 75, 58
19. Номера заданий: 45, 82, 41, 70
20. Номера заданий: 40, 76, 30, 81
21. Номера заданий: 11, 62, 66, 103
22. Номера заданий: 34, 74, 29, 101
23. Номера заданий: 31, 36, 57, 87
24. Номера заданий: 8, 28, 66, 81
25. Номера заданий: 14, 29, 56, 68
26. Номера заданий: 20, 51, 75, 103
27. Номера заданий: 10, 61, 68, 101
28. Номера заданий: 73, 25, 57, 32
29. Номера заданий: 2, 38, 24, 65
30. Номера заданий: 50, 100, 46, 68
31. Номера заданий: 53, 102, 51, 66
32. Номера заданий: 42, 78, 35, 75
34. Номера заданий: 33, 69, 28, 103
35. Номера заданий: 77, 26, 59, 103
36. Номера заданий: 44, 80, 39, 72
37. Номера заданий: 5, 48, 27, 70
38. Номера заданий: 3, 43, 25, 66
39. Номера заданий: 31, 19, 67, 87
Номер варианта соответствует номеру в списке группы на сайте. |
|
|
Скачать 51.42 Kb.