сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
1 Министерство образования и науки Российской Федерации Тольяттинский государственный университет Институт математики, физики и информационных технологий Кафедра «Прикладная математика и информатика» С.В. МКРТЫЧЕВ СОВРЕМЕННЫЕ ТЕХНОЛОГИИ БАЗ ДАННЫХ И АНАЛИЗА ИНФОРМАЦИИ УЧЕБНО-МЕТОДИЧЕСКОЕ ПОСОБИЕ ПО ВЫПОЛНЕНИЮ ПРАТИЧЕСКИХ РАБОТ Тольятти -2018 2 СОДЕРЖАНИЕ Практическая работа №1. Функциональность и архитектура СУБД Microsoft SQL Server ........................ 3 Практическая работа №2. Объекты СУБД MS SQL Server ........................................................................... 13 Практическая работа №3. Создание запросов на языке Transact-SQL ........................................................ 17 Практическая работа №4. Создание и использование программных объектов БД СУБД MS SQL Server ............................................................................................................................................................................ 26 Практическая работа №5. Создание и использование представлений ........................................................ 38 Практическая работа №6. Изучение основ работы со средствами контроля ссылочной целостности данных ................................................................................................................................................................ 42 Практическая работа №7. Использование транзакций .................................................................................. 52 Практическая работа №8. Использование СУБД MS Access для создания клиентского приложения OLTP-системы ................................................................................................................................................... 62 Практическая работа №9. Применение встроенных вычислительных функций MS Excel для финансового анализа ........................................................................................................................................ 67 Практическая работа №10. Анализ данных «что-если» в MS Excel ............................................................ 71 Практическая работа №11. Консолидация данных и их анализ в сводной таблице MS Excel .................. 75 Практическая работа № 12. Создание отчетов сводной таблицы ................................................................ 81 Практическая работа №13. Способы сравнения таблиц в Excel .................................................................. 88 Составить отчет по проделанной работе. ..................................................................................................... 122 Практическое занятие №14. Применение функции ВПР в Excel .............................................................. 123 Составить отчет по проделанной работе. ..................................................................................................... 129 Практическая работа №15. Использование MS Query для работы с OLAP-кубами ............................... 130 Практическая работа №16. Создание витрины данных в MS SQL Server ................................................ 132 Практическая работа №18. Использование надстройки «Поиск решения» MS Excel ........................... 137 Литература ....................................................................................................................................................... 143 3 Практическая работа №1. Функциональность и архитектура СУБД Microsoft SQL Server Для выполнения практических работ могут быть использована СУБД Microsoft SQL Server Express версий 2005, 2008 и 2012. Для хранения и обработки подобной информации средствами СУБД Microsoft SQL Server предварительно создадим БД (БД). Создание БД 1. Создать на диске компьютера каталог с произвольным именем (например, E:\PRSQL). 2. Запустить Microsoft SQL Server Management Studio, для чего: – в панели задач выбрать пункт Microsoft SQL Server; – выбрать подпункт SQL Server Management Studio; – в окне подключения (рис. 1.1) нажать кнопку Connect; Рисунок 1.1 3. После появления на экране среды Microsoft SQL Server Management Studio в окне Object Explorer выбрать пункт Databases, нажать правую кнопку мыши и в появившемся меню выбрать пункт New Database…. В результате на экране появится окно, позволяющее ввести основные параметры новой БД. Необходимо ввести имя новой БД – DELIVERY и определить место размещения файлов - E:\PRSQL (рис. 1.2). После ввода данных нажать кнопку ОК. Новая БД появится в списке баз данных в окне Object Explorer (рис. 1.3). 4 Рисунок 1.2 Рисунок 1.3 5 4. Выбрать созданную БД и раскрыть список ее объектов (рис. 1.4). Рисунок 1.4 5. В списке объектов БД щелкнуть правой кнопкой мыши по пункту Tables и в появившемся меню выбрать пункт New Table…. Ввести поля новой таблицы (рис. 1.5), определив при этом типы данных и ключевое поле (для этого нужно щелкнуть по полю правой кнопкой мыши и выбрать в меню соответствующий пункт (рис. 1.6)). Рисунок 1.5 Рисунок 1.6 6. Закрыть вкладку со структурой новой таблицы. Сохранить новую таблицу с именем «Поставщики» (без кавычек). 7. Аналогично создать таблицы «ФизическиеЛица» и «ЮридическиеЛица». Их структуры приведены на рисунках 1.7 и 1.8 соответственно. 6 Рисунок 1.7 Рисунок 1.8 8. Создать таблицу «Договоры». Структура таблицы приведена на рис. 1.9. Особенностью этой таблицы является то, что для поля «НомерДоговора» должно быть установлено свойство автоинкрементации (autoincrement) с начальным значением 1 и шагом изменения 1. Для этого в Microsoft SQL Server используется свойство Identity. Необходимо изменить значения свойства так, как показано на рис. 1.10. Рисунок 1.9 Рисунок 1.10 7 9. Создать таблицу «Поставлено. Структура таблицы приведена на рис. 1.11. Особенностью таблицы является составной первичный ключ. Для его создания нужно выделить ключевые поля мышью при нажатой клавише Shift и затем определить их как ключевые. Рисунок 1.11 10. В результате создания таблиц структура созданной БД будет иметь вид (рис. 1.12). В том случае, если список таблиц не отображается, можно щелкнуть правой кнопкой мыши по имени БД и в появившемся меню выбрать пункт Refresh. Рисунок 1.12 11.Теперь между созданными таблицами нужно установить связи. Это, в частности, можно сделать, используя визуальные средства. Для этого необходимо создать диаграмму БД. Для создания диаграммы нужно щелкнуть правой кнопкой мыши по пункту Database Diagrams и в появившемся меню выбрать пункт New Database Diagram. Затем нужно последовательно добавить в состав диаграммы таблицы, выбирая их из списка и нажимая кнопку Add (рис. 1.13). Рисунок 1.13 8 12.После включения таблиц в состав диаграммы нужно связать их ключевые поля. Для этого нужно выбрать с помощью мыши ключевое поле в родительской таблице и, не отпуская кнопку мыши, тянуть указатель мыши к дочерней таблице. В результате установления связи на экран будет выведено окно, отображающее имя связи и связываемые поля (рис. 1.14). Этот пример отображает установление связи между таблицами «Поставщики» и «ЮридическиеЛица». Подтвердив параметры связи, пользователь затем может подтвердить или изменить параметры внешнего ключа и тип отношений ссылочной целостности (рис. 1.15). Рисунок 1.14 Рисунок 1.15 9 13.В результате установления связей между таблицами диаграмма может иметь вид (рису. 1.16). Сформированную диаграмму можно закрыть и сохранить при этом с произвольным именем, например Diagram_0. Эта диаграмма появится в общем списке диаграмм БД. Рисунок 1.16 14. С помощью диаграммы БД можно изменять структуру таблиц, устанавливать связи, дополнительные свойства полей и т.д. Предположим, что для поле «Количество» и «Цена» таблицы «Поставлено» необходимо реализовать требования, состоящие в том, что данные, хранящиеся в этих полях, должны быть положительными. Для этого вновь откроем диаграмму, щелкнем правой кнопкой мыши по таблице «Поставлено» и в появившемся меню выберем пункт Check Constraints…. В появившемся окне нужно нажать кнопку Add и ввести выражение для контроля и название (рис. 1.17). Рисунок 1.17 10 15.Аналогично можно сформировать контрольное выражение для поля «Цена». В этом случае выражение (Expression) будет иметь вид: ([Цена]>0), а имя (Name): СК_Поставлено_Цена. После внесения этих изменений диаграмму можно закрыть и сохранить. 16.После закрытия диаграммы необходимо проанализировать структурные изменения, сделанные в таблицах (появление новых ключей и т.д.). Для этого следует проанализировать объекты каждой таблиц, последовательно открывая таблиц в списке таблиц. Ввод данных в таблицы БД 1. Для ввода информации в таблицу нужно выбрать таблицу в списке таблиц, щелкнув по ней правой кнопкой мыши, и в появившемся меню выбрать пункт Open Table. В результате таблица будет выведена на экран в виде, позволяющем вводить новые данные или корректировать введенные ранее. 2. Используя интерактивные средства SQL Server Management Studio необходимо ввести в таблицы БД информацию, приведенную на рисунках 1.18 – 1.22. Рисунок 1.18 – Данные, введенные в таблицу «Поставщики» Рисунок 1.19 – Данные, введенные в таблицу «ФизическиеЛица» Рисунок 1.20 – Данные, введенные в таблицу «ЮридическиеЛица» Внимание! При вводе данных в таблицу «Договоры» следует учитывать, что номер каждого договора изменяется автоматически. 11 Рисунок 1.21 – Данные, введенные в таблицу «Договоры» Рисунок 1.22 – Данные, введенные в таблицу «Поставлено» Отключение и подключение БД В процессе работы с базой данных может возникнуть необходимость копирования файлов БД с целью создания резервной копии и т.д. В СУБД Microsoft SQL Server существует несколько способов создания копий баз данных. Одним из простейших способов является отключение и подключение БД. Для отключения и подключения БД нужно выполнить следующую последовательность действий: 12 1. Выбрать отключаемую БД (в данном случае – ранее созданную бузу данных delivery) 2. Щелкнуть по базе данных правой кнопкой мыши и в появившемся меню выбрать пункт Tasks. Этому пункту соответствует подменю, в котором нужно выбрать пункт Detach…. Затем в появившемся окне Detach Database нужно нажать кнопку ОК. В результате отключенная БД исчезнет из списка баз данных, а файлы БД станут доступными для выполнения файловых манипуляции. 3. Отключенную БД можно вновь подключить. Для этого в окне Object Explorer нужно щелкнуть правой кнопкой мыши по пункту Databases и в появившемся меню выбрать пункт Attach…. Затем в появившемся окне Attach Databases нужно нажать кнопку Add и выбрать подключаемую БД, указав местоположение ее файлов. После этого нужно нажать кнопку ОК. В результате БД появится в списке баз данных 4. Проверить возможность работы с базой данных (т.е. наличие объектов вновь подключенной БД, наличие данных в таблицах и т.д.). Составить отчет по проделанной работе. 13 Практическая работа №2. Объекты СУБД MS SQL Server Действия, рассмотренные в практической работе 1, могут быть выполнены не только в интерактивном режиме, но и на основе использования языковых средств DDL и DML языка SQL. Для этого необходимо создать новую БД (например, c именем dlvr). Последовательность действий при создании БД аналогична действиям в практической работе 1. Для размещения файлов БД можно указать тот же каталог. Использование средств DDL для создания объектов БД В среде SQL Server Management Studio работать с базой данных можно, используя операторы языка SQL. Для этого необходимо создать один или несколько запросов. Каждый запрос может содержать произвольное количество операторов языка SQL. Рассмотрим последовательность действий при создании запроса, с помощью которого будут созданы таблицы БД и связи между ними. 1. На панели инструментов нажать кнопку New Query 2. Ввести текст запроса, приведенный на рис. 2.1. Рисунок 2.1 14 3. Выполнить запрос. Для этого на панели инструментов нужно нажать кнопку Execute. В том случае, если текст запроса не содержит ошибок, на экране появится окно Messages с сообщением Command(s) completed successfully. В противном случае будет выведена информация об имеющихся в тексте запроса ошибках. 4. В случае успешного выполнения запроса далее следует проверить наличие объектов БД. В том случае, если список таблиц сразу не отображается, можно щелкнуть правой кнопкой мыши по имени БД и в появившемся меню выбрать пункт Refresh. 5. Созданный запрос можно закрыть и сохранить с произвольным именем (например, SQLQuery_create_tables.sql) С помощью операторов DDL языка SQL можно не только создавать объекты БД, но и изменять структуру ранее созданных объектов. Предположим, что в таблице «Поставлено» размер поля «Количество» может не соответствовать реальным значениям хранимых данных. В связи с этим размер поля необходимо увеличить. Это можно сделать с помощью следующего запроса (рис. 2.2) Рисунок 2.2 Последовательность действий при создании и выполнении запроса аналогична последовательности действий, рассмотренных выше. Созданный запрос можно закрыть и сохранить с произвольным именем (например, SQLQuery_alter_tables.sql) Использование средств DМL для ввода информации в таблицы БД Запросы могут содержать не только операторы DDL, но и операторы DМL. Это позволяет реализовать основные операции манипулирования данными. Рассмотрим последовательность действий при создании запроса, с помощью которого в таблицы созданной БД будет введена информация. 1. На панели инструментов нажать кнопку New Query 2. Ввести текст запроса, приведенный на рисунках 2.3 – 2.5. 15 Рисунок 2.3 Рисунок 2.4 16 Рисунок 2.5 3. Выполнить запрос. Для этого на панели инструментов нужно нажать кнопку Execute. В том случае, если текст запроса не содержит ошибок, на экране появится окно Messages с сообщениями типа (1 row(s) affected). В противном случае будет выведена информация об имеющихся в тексте запроса ошибках. 4. В случае успешного выполнения запроса далее следует проверить наличие информации в таблицах БД. Для этого нужно выбрать таблицу, щелкнув по ней правой кнопкой мыши, и в появившемся меню выбрать пункт Open Table. 5. Созданный запрос можно закрыть и сохранить с произвольным именем (например, SQLQuery_insert.sql). Составить отчет по проделанной работе. 17 Практическая работа №3. Создание запросов на языке Transact-SQL Для выполнения работы необходимо подключить БД, которая была создана и заполнена данными в процессе выполнения практической работы Основной целью данной работы является изучение особенностей использования оператора SELECT-SQL при разработке запросов в среде SQL Server Management Studio, а также рассмотрение некоторых особенностей реализации оператора SELECT- SQL в языке Transact-SQL (T-SQL). Рассмотрим последовательность действий по созданию и выполнению запроса, позволяющего обрабатывать данные с помощью оператора SELECT- SQL с помощью утилиты ISQL на примере запроса 1. Запрос 1 Условие Вывести на экран список товаров, поставленных поставщиком 1 (ЧП Иванов И.И.) по договору 1. Создание и выполнение запроса. 1. На панели инструментов нажать кнопку New Query. 2. Ввести текст запроса, приведенный на рис. 3.1. Рисунок 3.1 3. Нажать кнопку «Execute». В том случае, если в тексте запроса нет ошибок, будет выведен результат запроса. 4. Текст запроса можно сохранить в виде файла (например, SQLQuery01_1.sql). В том случае, если в дальнейшем этот запрос нужно будет выполнить повторно или изменить, можно открыть файл запроса. Для этого в главном меню нужно выбрать пункт File, а затем в вертикальном меню выбрать пункт Open, подпункт File и выбрать соответствующий файл. Как видно из текста запроса, этот запрос является многотабличным, причем таблицы соединяются на основе использования естественного соединения. В случае использования открытого соединения этот запрос имел бы вид (рис. 3.2). Этот запрос также необходимо создать и выполнить для проверки работоспособности, а затем сохранить в файле с именем SQLQuery01_2.sql Рисунок 3.2 18 Создание и выполнение остальных запросов выполняется аналогично. Поэтому далее будет приведено назначение каждого запроса и текст. Запрос 2 Вывести на экран список товаров, поставленных поставщиком 1 (ЧП Иванов И.И.) в период с 05/09/2018 по 12/09/2018. Текст запроса приведен на рис. 3.3. Рисунок 3.3 Запрос можно сохранить в файле с именем SQLQuery02.sql Запрос 3 Вывести на экран список товаров, поставленных в 9 месяце 2018 года с выводом наименования поставщика и даты поставки. Текст запроса приведен на рис. 3.4 Рисунок 3.4 Запрос можно сохранить в файле с именем SQLQuery03.sql Запрос 4 Вывести на экран список договоров (номер, дата, название) и общую сумму по каждому договору (размер партии умножить на цену за штуку и просуммировать по договору). Список должен быть отсортирован в порядке возрастания номеров договоров. Текст запроса приведен на рис. 3.5. Рисунок 3.5 19 Запрос можно сохранить в файле с именем SQLQuery04.sql Запрос 5 Вывести на экран список договоров (номер, дата, название) и общую сумму по каждому договору (размер партии умножить на цену за штуку и просуммировать по договору). Список должен быть отсортирован в порядке возрастания общих сумм по каждому договору. После этого на список должно быть наложено условие фильтрации, состоящее в исключении из результата запроса записей, для которых номер договора больше 3. Текст запроса приведен на рис. 3.6. Рисунок 3.6 Запрос можно сохранить в файле с именем SQLQuery05.sql |