лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Фирма вторсырья Фирма имеет несколько пунктов приема вторсырья. Каждый пункт получает деньги для их выдачи сдатчикам вторсырья. Сведения о получении денег на пункт приема записываются в таблицу: 1. Income_o ( point, date,inc ) Первичным ключом является {point, date}, то есть прием денег (inc) производится не чаще одного раза в день. Сведения о выдаче денег за вторсырье записывается в таблицу: 1. Outcome_o ( point, date, out ) В этой таблице аналогичный первичный ключ {point, date} гарантирует отчетность каждого пункта о выданных деньгах (out) не чаще одного раза в день. В случае, когда приход и расход денег может фиксироваться несколько раз в день, используются таблицы (первичный ключ — code): 1. Income ( code, point, date, inc ) 2. Outcome ( code, point, date, out ) База данных «Фирма вторсырья» Фирма занимается приемом вторсырья и имеет несколько пунктов приема. Каждый пункт получает деньги для их выдачи сдатчикам в обмен на сырье. Фактически, на схеме представлены две базы данных. В каждой задаче по этой схеме используется только одна пара таблиц (либо с суффиксом «_o», либо без него). В таблицах Income_o и Outcome_o первичным ключом является пара атрибутов {point, date} — номер пункта приема и дата. Этот ключ должен моделировать ситуацию, когда сведения о получении денег на приемном пункте и их выдаче сдатчикам записываются в базу данных не чаще одного раза в день. Рис. 2.1. Схема базы данных «Фирма вторсырья» Примечание. Значения данных в столбце date не содержат времени, например, 2001-03-22 00:00:00.000. К сожалению, использование для этого столбца типа данных datetime может вызвать непонимание, поскольку очевидно, что учет времени не позволит ограничить многократный ввод значений с одной и той же датой (и номером пункта), но отличающихся временем дня. Этот недостаток, связанный с отсутствием отдельных типов данных для даты и времени, уже преодолен в версии SQL Server 2008. При использовании же SQL Server 2000 обеспечить правильность ввода можно при помощи, например, следующего ограничения (CK_Income_o): 1. ALTER TABLE Income_o ADD 2. CONSTRAINT PK_Income_o PRIMARY KEY 3. ( 4. [ point ] , 5. [ date ] 6. ) , 7. CONSTRAINT CK_Income_o CHECK 8. ( 9. DATEPART ( hour, [ date ]) + DATEPART ( minute, [ date ]) + 10. DATEPART ( second, [ date ]) + DATEPART ( millisecond, [ date ]) = 0 11. ) ; Это ограничение (сумма часов, минут, секунд и миллисекунд равна нулю) не позволит ввести какое-либо время, отличное от 00:00:00.000. При таком ограничении первичный ключ на данной таблице будет действительно гарантировать наличие лишь одной записи в день для каждой точки. Таблица Income_o (point, date, inc) содержит информацию о поступлении денежных сумм (inc) на пункт приема (point). Аналогичная таблица — Outcome_o (point, date, out) — служит для контроля расхода денежных средств (out). Вторая пара таблиц — Income (code, point, date, inc) и Outcome (code, point, date, out) — моделирует ситуацию, когда приход и расход денег может фиксироваться несколько раз в день. Следует отметить, что если записывать в последние таблицы только дату без времени (что и имеет место), то никакая естественная комбинация атрибутов не может служить первичным ключом, поскольку суммы денег также могут совпадать. Поэтому нужно либо учитывать время, либо добавить искусственный ключ. Мы использовали второй вариант, добавив целочисленный столбец code только для того, чтобы обеспечить уникальность записей в таблице. Корабли Рассматривается база данных кораблей, участвовавших во второй мировой войне. Имеются следующие отношения: 1. Classes ( class, type, country, numGuns, bore, displacement ) 2. Ships ( name, class, launched ) 3. Battles ( name, date ) 4. Outcomes ( ship, battle, result ) Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей. Корабль, давший название классу, называется головным. Отношение Classes содержит имя класса — class, тип — type (bb для боевого (линейного) корабля или bc для боевого крейсера), страну — country, в которой построен корабль, число главных орудий — numGuns, калибр орудий (диаметр ствола орудия в дюймах) — bore и водоизмещение (вес в тоннах) — displacement. В отношении Ships записаны название корабля — name, имя его класса — class и год спуска на воду — launched. В отношение Battles включены название — name и дата битвы — date, в которой участвовали корабли, а в отношении Outcomes — результат участия данного корабля в битве — result (потоплен — sunk, поврежден — damaged или невредим — ok). Примечание: 1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships. 2) Потопленный корабль в последующих битвах участия не принимает. База данных «Корабли» Рассматривается база данных кораблей, участвовавших в морских сражениях второй мировой войны. Имеются следующие отношения: 1. Classes ( class, type, country, numGuns, bore, displacement ) 2. Ships ( name, class, launched ) 3. Battles ( name, date ) 4. Outcomes ( ship, battle, result ) Корабли в «классах» построены по одному и тому же проекту. Классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое в этом случае не совпадает с именем ни одного из кораблей. Корабль, давший название классу, называется головным. Атрибутами отношения Classes являются имя класса (class), тип (значение bb используется для обозначения боевого или линейного корабля, а bc для боевого крейсера), страну (country), которой принадлежат корабли данного класса, число главных орудий (numGuns), калибр орудий (bore — диаметр ствола орудия в дюймах) и водоизмещение в тоннах (displacement). В отношение Ships записывается информация о кораблях: название корабля (name), имя его класса (class) и год спуска на воду (launched). В отношение Battles включены название (name) и дата битвы (date), в которой участвовал корабль. Отношение Outcomes используется для хранения информации о результатах участия кораблей в битвах, а именно, имя корабля (ship), название сражения (battle) и чем завершилось сражение для данного корабля (потоплен — sunk, поврежден — damaged или невредим — ok). Примечание: В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships. Рис. 3.1. Схема базы данных «Корабли» Отметим несколько моментов, на которые следует обратить внимание при анализе схемы на рис. 3.1 . Таблица Outcomes имеет составной первичный ключ {ship, battle}. Это ограничение не позволит ввести в базу данных дважды один и тот же корабль, принимавший участие в одном и том же сражении. Однако допустимо неоднократное присутствие одного и того же корабля в данной таблице, что означает участие корабля в нескольких битвах. Класс корабля определяется из таблицы Ships, которая имеет внешний ключ (class) к таблице Classes. Особенностью данной схемы, которая усложняет логику запросов и служит причиной ошибок при решении задач, является то, что таблицы Outcomes и Ships никак не связаны, то есть в таблице результатов сражений могут находиться корабли, отсутствующие в таблице Ships. На основании этого, казалось бы, можно сделать вывод о том, что для таких кораблей их класс неизвестен, а, следовательно, неизвестны и все технические характеристики. Это не совсем так. Как следует из описания предметной области, имя головного корабля совпадает с именем класса, родоначальником которого он является. Поэтому если имя корабля из таблицы Outcomes совпадает с именем класса в таблице Classes, то однозначно можно сказать, что это головной корабль, и, следовательно, все его характеристики нам известны. Каждый знает, как улучшить эту «плохую» схему: связать таблицы Ships и Outcomes по имени корабля, при этом столбец ship в Outcomes становится внешним ключом к таблице Ships. Безусловно, это так, однако не следует забывать, что в реальной ситуации не вся информация может быть доступна. Например, имеется архивная информация о кораблях, участвовавших в том или ином сражении, без указания классов этих кораблей. При наличии обсуждаемой связи сначала будет необходимо внести такой корабль в таблицу Ships, при этом столбец class должен допускать NULL-значения. С другой стороны, что нам мешает ввести головной корабль, который попал в таблицу Outcomes, также и в таблицу Ships? В принципе ничего, так как год спуска на воду не является обязательной информацией. По этому поводу следует заметить, что администратор базы данных и разработчик приложения, как правило, разные люди. Не всегда разработчик приложения и его пользователи имеют права на модификацию данных. Плохая структура еще не означает, что из нее нельзя извлечь достоверную информацию, чем собственно мы и занимаемся, решая предлагаемые задачи. Что касается учебных целей, то работа с такой структурой даст значительно больше в освоении языка, чем структура «хорошая», так как заставит писать более сложные запросы и научит учитывать дополнительные обстоятельства, накладываемые схемой. Этим видимо и руководствовались авторы этой схемы данных [2] . Кроме того, запросы, написанные для «плохой» схемы, будут давать правильные результаты и после улучшения структуры (хотя и станут менее эффективными), то есть тогда, когда вся информация станет доступной, и мы сможем установить связь между таблицами Ships и Outcomes. Наконец, стоит обратить внимание на то, что столбец launched в таблице Ships допускает NULL-значения, то есть нам может быть неизвестен год спуска на воду того или иного корабля. То же самое мы можем сказать о кораблях из Outcomes, отсутствующих в Ships. Что ж, перейдем к решению задач. Заметим лишь, что в настоящей главе мы уже не рассматриваем совсем простые задачи (хотя они имеются и для этой схемы), которых должно было хватить вам в первой главе. Аэрофлот Схема базы данных состоит из четырех отношений: 1. Company ( ID_comp, name ) 2. Trip ( trip_no, id_comp, plane, town_from, town_to, time_out, time_in ) 3. Passenger ( ID_psg, name ) 4. Pass_in_trip ( trip_no, date, ID_psg, place ) Таблица 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, идентификатор пассажира и место — place, на котором он сидел во время полета. При этом следует иметь в виду, что рейсы выполняются ежедневно, а длительность полета любого рейса менее суток; время и дата учитывается относительно одного часового пояса; среди пассажиров могут быть однофамильцы (одинаковые значения поля name, например, Bruce Willis); номер места в салоне — это число с буквой; число определяет номер ряда, буква (a — d) — место в ряду слева направо в алфавитном порядке; связи и ограничения показаны на схеме данных. Окраска Схема базы данных состоит из трех отношений (рис. П.5): 1. utQ ( Q_ID, Q_NAME ) 2. utV ( V_ID, V_NAME, V_COLOR ) 3. utB ( B_Q_ID, B_V_ID, B_VOL, B_DATETIME ) Таблица utQ содержит идентификатор — Q_ID и название квадрата — Q_NAME, цвет которого первоначально черный. Таблица utV содержит идентификатор — V_ID, название — V_NAME и цвет — V_COLOR баллончика с краской. Таблица utB содержит информацию об окраске квадрата баллончиком: B_Q_ID — идентификатор квадрата, B_V_ID — идентификатор баллончика, B_VOL — количество краски и B_DATETIME — время окраски. При этом следует иметь в виду, что: баллончики с краской могут быть трех цветов — красный 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, а количество краски в баллончике не может быть меньше или равно нулю. Приложение 2. Список задач Здесь перечислены только задачи, рассматриваемые в книге. На сайте SQL-EX.RU , помимо них, есть много других задач и, кроме того, регулярно добавляются новые. № База Уровень Задача Этап ПиР 1 П1 1 Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 долларов. Вывести: model, speed и hd Обуч. 2 П1 1 Найдите производителей принтеров. Вывести: maker Обуч. + 3 П1 1 Найдите номер модели, объем памяти и размеры экранов портативных компьютеров, цена которых превышает 1000 дол. Обуч. 5 П1 1 Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12х или 24х CD-приводы и цену менее 600 долларов Обуч. 6 П1 2 Укажите производителя и скорость портативных компьютеров с жестким Обуч. + диском объемом не менее 10 Гбайт 7 П1 2 Найдите номера моделей и цены всех продуктов (любого типа) выпущенных производителем B (латинская буква) Обуч. + 8 П1 2 Найдите производителя, продающего ПК, но не портативные компьютеры Обуч. + 10 П1 1 Найдите принтеры, имеющие самую высокую цену. Вывести: model, price Обуч. + 11 П1 1 Найдите среднюю скорость ПК Обуч. + 13 П1 1 Найдите среднюю скорость ПК, выпущенных производителем A Обуч. 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 20 П1 2 Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей Обуч. № База Уровень Задача Этап ПиР 23 П1 3 Найдите производителей, которые производили бы как ПК со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц. Вывести: Maker Обуч. + 24 П1 3 Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции Обуч. 25 П1 3 Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM. Вывести: Maker Обуч. 26 П1 3 Найдите среднюю цену ПК и Портативных компьютеров, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена Обуч. + 27 П1 3 Найдите средний размер диска ПК каждого из тех производителей, которые Обуч. + выпускают и принтеры. Вывести: maker, средний размер HD 71 П1 1 Найти тех производителей ПК, все модели ПК которых имеются в таблице PC Обуч. 30 П2 3 В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (в обе таблицы добавлен первичный ключ code), написать запрос с выходными данными (point, date, out, inc), в котором каждому пункту за каждую дату соответствует одна строка Обуч. + 59 П2 3 Посчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. Обуч. + 60 П2 1 Посчитать остаток денежных средств на начало дня 15.04.2001 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. Обуч. + 32 П3 3 Одной из характеристик корабля является половина куба калибра его главных орудий (mw). С точностью до 2 десятичных знаков определите среднее значение mw для кораблей каждой страны, у которой есть корабли в базе данных. Обуч. |