Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
ГЛАВА 7. ПРАКТИКУМ ПО SQL-ПРОГРАММИРОВАНИЮ 7.1. Общие методические указания Практические занятия нацелены на изучение базовых элементов языка SQL, освоение инструментальных средств, используемых при про- граммировании и отладке SQL-запросов, и получение практических на- выков работы в среде СУБД в процессе выполнения практических заданий, каждое из которых связано с написанием и отладкой некоторого SQL- запроса. Все задания выполняются в учебной базе данных, схема которой приве- дена на рисунке 3.2. В качестве базовой СУБД рекомендуется использовать MS Access со встроенным языком Microsoft Jet SQL, что обосновывается сле- дующими соображениями: 1) язык Microsoft Jet SQL является диалектом стандартного SQL-89, и его вполне достаточно для демонстрации типовых возможностей SQL; 2) MS Access поддерживает технологию визуального конструирования запросов с автоматической генерацией их SQL-кода, что представляется весьма полезным на начальной стадии освоения языка; 3) MS Access является стандартным Windows-приложением, входящим в комплект поставки популярного пакета MS Office, и не предъявляет особых требований к аппаратуре и системному ПО. Перед выполнением практических заданий рекомендуется самостоятель- но проанализировать и программно реализовать SQL-запросы, примеры кото- рых приведены на листингах 3.3–3.13. Защита практических заданий производится в форме демонстрации тек- стов подготовленных SQL-запросов и результатов их выполнения. 7.2. Учебная база данных Для выполнения практических заданий предоставляется учебная база данных (файл mdb-формата), обеспечивающая процессы торгово-складского учета и анализа в торговой компании. Схема базы данных (в стиле MS Access) приведена на рисунке 3.2. 16 / 19 112 Рис. 3.2 Схема учебной базы данных 17 / 19 113 7.3. Практические задания Задание № 1. Простейшие запросы выборки данных 1.1. Выбрать товары, складской запас которых превышает минимально допустимый запас не менее чем на 50%. Определить количество единиц и сто- имость складского запаса по каждому такому товару. 1.2. Из числа товаров, имеющихся на складе, выбрать такие, поставки ко- торых прекращены. Определить количество единиц и стоимость складского за- паса по каждому такому товару. 1.3. Выбрать заказы, время исполнения которых превысило 1 месяц. 1.4. Выбрать сотрудников, стаж работы которых превышает n лет (n зада- ется параметром, значение которого запрашивается у пользователя). 1.5. Выбрать заказы, исполненные в k-м квартале p-го года (год p и номер квартала k задаются параметрами запроса). 1.6. Получить список городов для страны, код которой задается параметром. Задание № 2. Запросы с соединением таблиц 2.1. Прокомментировать SQL-запросы, представленные в листинге 3.14, и оценить результаты их выполнения. a) SELECT Город, Страна FROM Страны, Города; б) SELECT Город, Страна FROM Страны AS С LEFT JOIN Города AS Г ON С.КодСтраны = Г.КодСтраны; в) SELECT Город, Страна FROM Страны AS С, Города AS Г WHERE Г.КодСтраны=С.КодСтраны; г) SELECT Город, Страна FROM Страны AS С RIGHT JOIN Города AS Г ON С.КодСтраны = Г.КодСтраны; д) SELECT Город, Страна FROM Страны AS С INNER JOIN Города AS Г ON С.КодСтраны = Г.КодСтраны; Листинг 3.14 Примеры SQL-запросов с соединением таблиц 2.2. Сформировать список сотрудников по филиалам, городам, странам и регионам. 2.3. Выбрать товары, заказанные в первом квартале 2017 г. клиентами из Европы, которым товары были доставлены по почте. Отсортировать по назва- ниям стран и городов. 2.4. Выбрать товары, заказанные во втором квартале 2001 г. клиентами из Америки, которым товары были доставлены по почте. 2.5. Выбрать товары, заказанные в первом квартале 2001 г. клиентами из России и Белоруссии, которым товары были доставлены по почте. 2.6. Выбрать зарубежных поставщиков, остаток товаров которых на скла- де не превышает установленного минимального запаса. 18 / 19 114 2.7. Выбрать поставщиков из Северной Америки, поставки товаров кото- рых прекращены. 2.8. Выбрать рыбные и мясные товары (категория и марка товара, по- ставщик, оптовая цена, единица измерения, складской запас и ожидаемое коли- чество), поставки которых продолжаются. 2.9. Для каждого клиента, разместившего заказ, выбрать поставщиков за- казанных товаров, находящихся в том же городе, что и клиент. 2.10. Для каждого клиента, разместившего заказ, выбрать поставщиков заказанных товаров, находящихся в той же стране, что и клиент. 2.11. Для каждого клиента, разместившего заказ, выбрать поставщиков заказанных товаров, находящихся в том же регионе, что и клиент. 2.12. Выбрать товары, заказчики которых (клиенты) находятся в том же городе, что и поставщики заказанных товаров. 2.13. Выбрать товары, заказчики которых (клиенты) находятся в той же стране, что и поставщики заказанных товаров. 2.14. Выбрать товары, заказчики которых (клиенты) находятся в том же регионе, что и поставщики заказанных товаров. Задание № 3. Статистическая обработка данных 3.1. Прокомментировать SQL-запросы, представленные в листинге 3.15, и оценить результаты их выполнения. 3.2. Определить общее количество заказов, оформленных каждым из со- трудников компании в первом квартале 2017 г. 3.3. Определить общее количество заказов, оформленных каждым из со- трудников компании в каждом квартале 2017 г. 3.4. Определить суммарную стоимость товаров в каждом из исполненных заказов. 3.5. Определить средний срок исполнения заказа по каждому региону. 3.6. Определить суммарную стоимость каждой категории товаров, вклю- ченных в заказы 2017 г. 3.7. Определить среднюю оптовую цену имеющихся на складе товаров по категориям. 3.8. Определить количество и общую стоимость имеющихся на складе товаров для каждого из поставщиков. 3.9. Определить суммарную стоимость доставки заказов каждым из спо- собов в каждом квартале 2017 г. 3.10. Определить суммарную стоимость доставки заказов каждым из спо- собов в каждую из стран клиентов, заказавших товары. 3.11. Определить заказы, стоимость которых превышает среднюю. 3.12. Выбрать города, количество клиентов из которых превышает задан- ное значение. 3.13. Выбрать заказы, при выполнении которых товары, поставляемые из той же страны, из которой поступил заказ, составляли не менее половины стои- мости заказа. 19 / 19 115 3.14. Выбрать заказы, поступившие из США, в которых заказано рыбо- продуктов на сумму большую, чем средняя стоимость аналогичной продукции в заказах клиентов из других стран (указать код заказа, наименование клиента и сумму, уплаченную за рыбопродукты). a) SELECT COUNT(*) FROM Города; б) SELECT COUNT(*), Города.КодСтраны, Страны.КодРегиона FROM Регионы INNER JOIN (Страны INNER JOIN Города ON Страны.КодСтраны = Города.КодСтраны) ON Регионы.КодРегиона = Страны.КодРегиона GROUP BY Города.КодСтраны, Страны.КодРегиона; в) SELECT COUNT(КодСтраны) FROM Города; г) SELECT COUNT(*), КодСтраны FROM Города; д) SELECT COUNT(*), КодСтраны FROM Города GROUP BY КодСтраны ; е) SELECT AVG(Товары.ЦенаПоставщика),Категории.Категория FROM Категории INNER JOIN Склад ON Категории.Код_Категории = Склад.КодКатегории GROUP BY Категории.Категория HAVING AVG(Склад.Количество)>30; ж) SELECT COUNT(*), КодСтраны FROM Города GROUP BY КодСтраны HAVING Count(*)>10; и) SELECT COUNT(*), Города.КодСтраны, Страны.КодРегиона FROM Регионы INNER JOIN (Страны INNER JOIN Города ON Страны.КодСтраны = Города.КодСтраны) ON Регионы.Код_Региона = Страны.КодРегиона GROUP BY Страны.КодРегиона, Города.КодСтраны; к) SELECT Город, COUNT(*) AS [Количество клиентов] FROM Города INNER JOIN Клиенты ON Города.Код_Города = Клиенты.КодГорода GROUP BY Город; л) SELECT Город FROM Города WHERE (SELECT COUNT(*) FROM Клиенты WHERE Города.Код_Города = Клиенты.КодГорода)>2; Листинг 3.15 Примеры SQL-запросов с групповой обработкой данных Задание № 4. Модифицирующие SQL-запросы 4.1. Запросом к таблице Заказы создать новую таблицу Т1, содержащую всю информацию о заказах, размещенных клиентами в 2017 г. 4.2. Исключить из таблицы Т1 все записи о заказах, полученных от клиен- тов из Северной Америки. 4.3. Создать новую таблицу Т2, содержащую следующую информацию о заказах, размещенных в первом квартале 2016 г.: – код заказа и дата его размещения; 1 / 19 116 – сведения о клиенте (клиент, город, страна, регион); – сведения о заказанных товарах (категория, товар, количество в заказе, сумма, уплаченная за товар); 4.4. На базе таблицы Т2 создать таблицу для заказчиков из Европы. 4.5. На базе таблицы Т2 создать таблицу для заказчиков из Америки. 4.6. В таблице Заказы продлить на 30 дней срок исполнения заказов, по- лученных от клиентов из Франции и Испании. Отменить все эти изменения. 4.7. Увеличить на 10 лет все даты в таблице Заказы. 4.8. Для всех заказанных товаров обновить базовую цену их реализации в соответствии с торговой наценкой, заданной для категории товара. 4.9. Для всех заказанных товаров обновить величину скидки в соответ- ствии со значением персональной скидки клиентов, заказавших эти товары. Задание № 5. Запросы с объединением таблиц 5.1. На базе таблиц Клиенты и Поставщики составить запрос для получе- ния объединенного списка контрагентов. 5.2. На базе таблиц Регионы, Страны и Города составить запрос для полу- чения объединенного списка, содержащего поля Наименование, Код и допол- нительное поле Тип, содержащее значения: «Город» — для городов, «Стра- на» — для стран и «Регион» — для регионов. 5.3. На базе запросов, созданных при выполнении заданий 5.1 и 5.2, со- здать две новые базовые таблицы. 5.4. Объединить таблицы Сотрудники и Представители в одну новую ба- зовую таблицу. Задание № 6. Перекрестные запросы 6.1. Составить перекрестный запрос, позволяющий представить инфор- мацию о суммах, полученных от клиентов за исполненные заказы (стоимость товаров + доставка), в координатах СОТРУДНИК — СТРАНА ПОЛУЧАТЕЛЯ. 6.2. Составить перекрестный запрос, позволяющий представить инфор- мацию о суммах выручки, полученных от реализации товаров, в координатах ПОСТАВЩИК — КЛИЕНТ. 6.3. Составить перекрестный запрос, позволяющий представить инфор- мацию о суммах выручки, полученных от реализации товаров, в координатах ПОСТАВЩИК — СОТРУДНИК. 6.4. Составить перекрестный запрос, позволяющий представить инфор- мацию о суммах выручки, полученных от реализации товаров, в координатах КЛИЕНТ — ТОВАР. 6.5. Составить перекрестный запрос, позволяющий представить инфор- мацию о суммах выручки, полученных от реализации товаров, по кварталам каждого года. 6.6. Составить перекрестный запрос, позволяющий сопоставить инфор- мацию о стоимости доставки товаров каждым из способов доставки по странам с суммами соответствующих заказов. 2 / 19 117 ЧАСТЬ 4. УПРАВЛЕНИЕ И АДМИНИСТРИРОВАНИЕ 3 / 19 118 ГЛАВА 8. ОБЗОР ФУНКЦИЙ СУБД Как уже отмечалось, существенной особенностью АИС, отличающей их от многих других программных систем, является обеспечение автономности подсистемы хранения данных, в основе которой — принцип независимости данных от прикладных программ, обрабатывающих хранимые данные в инте- ресах конечных пользователей системы. Реализация принципа автономности баз данных потребовала совместного файлового хранения метаданных с основными данными, представления модели данных на трех иерархических уровнях — концептуальном, логическом и фи- зическом, запрета непосредственного доступа к базе данных со стороны при- кладных программ и лишения их возможности (и необходимости) интерпрета- ции низкоуровневых структур данных. В результате была сформирована кон- цепция СУБД (рис. 1.1) как специализированной программной системы, обес- печивающей решение всего комплекса задач управления базами данных. Обсуждение всех функций СУБД и методов их реализации выходит за рамки данного издания, ниже приведен лишь их обзор с краткими комментари- ями и более детально рассмотрены две важнейшие и взаимосвязанные функции СУБД: управление транзакциями и управление блокировками, обеспечивающие эффективную работу АИС в условиях интенсивного многопользовательского доступа к базам данных. Реализация функций поддержки физической модели данных, трансляции SQL-запросов и управления производительностью, а также функций защиты от несанкционированного доступа к данным рассмотрена применительно к СУБД MS SQL-Server. Поддержка физической модели данных. Физическая модель представ- лена множеством взаимосвязанных низкоуровневых структур данных, обеспе- чивающих их внутреннее (как межфайловое, так и внутрифайловое) представ- ление. Физическая модель недоступна пользовательским программным прило- жениям АИС, на основе этой модели в СУБД реализованы низкоуровневые ал- горитмы исполнения запросов к базе данных — так называемые процедурные планы. СУБД обеспечивает взаимодействие компонентов логической и физиче- ской моделей базы данных, а также отображение структур физической модели на файловые структуры, поддерживаемые операционной системой, управляю- щей аппаратным комплексом АИС. Поддержка системного каталога базы данных. Системный каталог (называемый также словарем данных) обеспечивает хранение в самой базе дан- ных метаданных — описаний объектов логической и физической моделей дан- ных, что позволяет прикладным программам оперировать исключительно объ- ектами логической модели, не имея информации об их низкоуровневом пред- ставлении. Наличие системного каталога обеспечивает независимость данных от об- рабатывающих их прикладных программ и относительную автономность функ- 4 / 19 119 ционирования базы данных в составе прочих компонентов программного обес- печения АИС. Системный каталог пользовательской базы данных представлен множе- ством системных таблиц, а также хранимых в базе данных представлений, функций и процедур, используемых как самой СУБД в процессе трансляции и исполнения SQL-запросов, так и администраторами баз данных для анализа, настройки и оптимизации работы системы. Трансляция SQL-запросов. Процесс трансляции SQL-запроса включает отображение затрагиваемых запросом объектов логической модели данных на соответствующие объекты физической модели и формирование оптимального процедурного плана его исполнения. Оптимизация процедурных планов произ- водится с целью повышения производительности работы системы на основании информации о наличии индексных структур данных и в соответствии с резуль- татами автоматически выполняемого «сбора статистики» о текущем состоянии объектов базы данных. Управление производительностью. Основным критерием оценки произ- водительности системы является среднее время ее отклика на пользовательские SQL-запросы. Для минимизации времени отклика СУБД использует различные индексные структуры данных и предоставляет администраторам средства мо- ниторинга пользовательской активности, обновления статистической информа- ции о состоянии объектов базы данных, а также средства анализа процедурных планов исполнения запросов. Производительность СУБД в режиме многопользовательского доступа к данным во многом определяется настройкой и реализацией подсистемы управ- ления транзакциями и блокировками. Управление надежностью. Надежное функционирование базы данных предполагает сохранение ее целостности и работоспособности в течение дли- тельного периода времени и обеспечивается различными средствами на всех стадиях ее жизненного цикла. СУБД выполняет следующие функции управле- ния надежностью на стадии эксплуатации базы данных: – автоматический контроль заданных разработчиком ограничений це- лостности данных, в том числе ограничений типа и домена, проверяемых (check constraints) и ссылочных (foreign key) ограничений; – контроль взаимовлияния транзакций, конкурирующих за доступ к объ- ектам базы данных в многопользовательских системах, которое может приво- дить к искажениям результатов выполнения SQL-запросов (с точки зрения кли- ентских приложений, инициировавших транзакции); – создание и надежное хранение резервных копий баз данных (в том чис- ле и разностных копий) с возможностью восстановления по ним баз данных по- сле «жестких сбоев», связанных с потерей данных на внешних запоминающих устройствах; – поддержку журналов транзакций (LOG-файлов), обеспечивающих хра- нение «исторической» информации об операциях, модифицировавших базу данных; 5 / 19 120 – эффективное управление журналом транзакций в соответствии с прото- колом WAL (Write Ahead LOG), гарантирующим сохранение журнальной инфор- мации во внешней памяти прежде, чем там будут сохранены результаты соот- ветствующих модифицирующих операций; – восстановление согласованного состояния базы данных по журналу транзакций после «мягких сбоев», которые не приводят к потере информации, размещенной на внешних запоминающих устройствах; – автоматическое восстановление согласованного состояния базы данных по журналу транзакций после неудачного завершения (отката) транзакций. Защита от несанкционированного доступа. Коммерческие СУБД об- щего применения предоставляют стандартный набор возможностей дискреци- онной (логической) защиты информации: идентификация и аутентификация пользователей (субъектов доступа), разграничение прав доступа субъектов к логическим объектам базы данных с возможностью группировки как субъектов, так и объектов доступа, шифрование хранимых данных. Специальные СУБД обеспечивают более высокий уровень информационной безопасности, исполь- зуя методы мандатной (физической) защиты данных. Инструментальная поддержка. СУБД предоставляют программистам и администраторам баз данных разнообразные программные и визуальные сред- ства поддержки процессов разработки и управления: – визуализация схем баз данных; – визуальное конструирование и написание SQL-запросов, хранимых представлений, пользовательских функций и процедур; – просмотр и обновление различных статистических характеристик объ- ектов базы данных; – анализ эффективности процедурных планов исполнения SQL-запросов; – просмотр объектов системного каталога; – сознание, корректировка и группирование субъектов доступа; – настройка системы разграничения доступа к данным и др. 6 / 19 |