сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
Создать запрос, иллюстрирующий работу транзакционного механизма при добавлении данных в несколько таблиц Рассмотрим последовательность действий при создании и использовании запроса, с помощью которого запускается транзакция, в затем создается новый поставщик, с этим поставщиком заключается договор на поставку, по этому договору поставляется продукция. Имитируется ситуация некорректного или корректного завершения транзакции. Состояние таблиц контролируется до начала транзакции, в процессе выполнения транзакции и после завершения транзакции. Для этого нужно выполнить следующую последовательность действий. 1. На панели инструментов нажать кнопку New Query 2. Ввести текст запроса, приведенный на рис. 7.4 Рисунок 7.4 3. Выполнить запрос. 4. В случае успешного выполнения запроса на экран будут выведены данные, иллюстрирующие состояние таблиц до начала транзакции, в процессе выполнения транзакции и после завершения транзакции (аналогично предыдущему запросу). Как видно из приведенных данных, новые записи в таблицах появляются, а затем исчезают. 5. Теперь рассмотрим ситуацию корректного завершения транзакции. Для этого в приведенном тексте запроса изменим оператор ROLLBACK на COMMIT. Выполним запрос. В результате выполнения запроса данные должны быть внесены в таблицы и сохранены. В этом нужно убедиться, открыв соответствующие таблицы в режиме просмотра данных. Запрос можно сохранить с именем SQLQuery_trans1.sql. 59 Создать запрос, иллюстрирующий работу транзакционного механизма при изменении данных в несколько таблицах Рассмотрим последовательность действий при создании и использовании запроса, с помощью которого запускается транзакция, затем изменяются данные, введенные в таблицы при выполнении предыдущего запроса. Имитируется ситуация некорректного или корректного завершения транзакции. Состояние таблиц контролируется до начала транзакции, в процессе выполнения транзакции и после завершения транзакции. Для этого нужно выполнить следующую последовательность действий. 1. Для отношений ссылочной целостности между всеми таблицами БД установить механизм Cascade. 2. На панели инструментов нажать кнопку New Query. 3. Ввести текст запроса, приведенный на рисунке 7.5. Рисунок 7.5 4. Выполнить запрос. 5. В случае успешного выполнения запроса на экран будут выведены данные, иллюстрирующие состояние таблиц до начала транзакции, в процессе выполнения транзакции и после завершения транзакции (аналогично предыдущим запросам). Как видно из приведенных данных, изменения данных в таблицах появляются, а затем исчезают. 60 6. Теперь рассмотрим ситуацию корректного завершения транзакции. Для этого в приведенном тексте запроса изменим оператор ROLLBACK на COMMIT. Выполним запрос. В результате выполнения запроса данные должны быть внесены в таблицы и сохранены. В этом нужно убедиться, открыв соответствующие таблицы в режиме просмотра данных. Запрос можно сохранить с именем SQLQuery_trans2.sql. Создать запрос, иллюстрирующий работу транзакционного механизма при удалении данных в несколько таблицах Рассмотрим последовательность действий при создании и использовании запроса, с помощью которого запускается транзакция, в рамках которой удаляется поставщик, который был создан при выполнении запроса II и данные которого были изменены при выполнении запроса III. С учетом используемого механизма контроля ссылочной целостности (Cascade) данные будут удалены в нескольких таблицах. Имитируется ситуация некорректного или корректного завершения транзакции. Состояние таблиц контролируется до начала транзакции, в процессе выполнения транзакции и после завершения транзакции. Для этого нужно выполнить следующую последовательность действий. 1. На панели инструментов нажать кнопку New Query. 2. Ввести текст запроса, приведенный на рис. 7.6. Рисунок 7.6 3. Выполнить запрос. 4. В случае успешного выполнения запроса на экран будут выведены данные, иллюстрирующие состояние таблиц до начала транзакции, в процессе выполнения транзакции и после завершения транзакции (аналогично предыдущим запросам). Как видно из приведенных данных, изменения данных в таблицах появляются, а затем исчезают. 61 5. Теперь рассмотрим ситуацию корректного завершения транзакции. Для этого в приведенном тексте запроса изменим оператор ROLLBACK на COMMIT. Выполним запрос. В результате выполнения запроса данные должны быть внесены в таблицы и сохранены. В этом нужно убедиться, открыв соответствующие таблицы в режиме просмотра данных. Запрос можно сохранить с именем SQLQuery_trans3.sql. Составить отчет по проделанной работе. 62 Практическая работа №8. Использование СУБД MS Access для создания клиентского приложения OLTP-системы I. Создание ODBC-источника данных Запуск средств доступа к ODBC-источникам данных 1. Открыть Панель управления (Control Panel) Windows. 2. Открыть папку Администрирование (Administrative Tools). 3. Выбрать пункт Источники данных (ODBC) (Data Sources (ODBC)). 4. Открыть список ODBC-источников двойным щелчком мыши. Создать новый ODBC-источник, для чего: 1. Выбрать вкладку Пользовательский DSN (User DSN) и нажать кнопку Добавить (Add). 2. Выбрать драйвер SQL Server и нажать кнопку Готово (Finish) 3. Ввести имя ODBC-источника и выбрать сервер, к которому нужно подключиться (рис. 8.1). При выборе сервера нужно выбрать сервер, соответствующий компьютеру, на котором выполняется работа. Рисунок 8.1 63 4. В окне проверки подлинности пользователя оставить данные без изменений (рис. 8.2) и нажать кнопку Next (Далее). Рисунок 8.2 5. Выбрать БД, к которой по умолчанию будет осуществлено подключение (рис. 8.3) и нажать кнопку Next (Далее). В следующем окне нажать кнопку Готово (Finish). Рисунок 8.3 64 6. Проверить подключение к серверу и в случае успешного подключения нажать кнопку ОК. В этом случае ODBC-источник будет сохранен и появится в списке ODBC-источников. Запустить СУБД MS Access. Создать новую БД с именем client_mssql (желательно в той же папке, где находится БД, созданная при выполнении практических работ). В подменю пункта File выбрать пункт Внешние данные (Get External Data) и подпункт Связь с таблицами (Link Tables…) (рис. 8.4). Рисунок 8.4 В окне Связь (Link), в поле со списком Тип файлов (Files of type) выбрать пункт БД ODBC () (ODBC Databases ()) В окне Выбор источника данных (Select Data Source) открыть вкладку Источник данных компьютера (Machine Data Source), выбрать ODBC-источник с именем delivery В окне Связь с таблицами (Link Tables) выбрать таблицу dbo.Поставщики. В результате будет создана таблица dbo_Поставщики Аналогично создать таблицу dbo_Договоры, которая будет связана с таблицей Договоры. Проверить возможность работы с базой данных, используя в качестве интерфейса пользователя СУБД MS Access. Для этого открыть созданные таблицы dbo_Поставщики (рис. 8.5) и dbo_Договоры. Используя интерфейс MS Access, выполнить для проверки операции манипулирования данными (добавление, удаление, изменение данных). Проверить сохранение результатов манипулирования, используя SQL Server Management Studio. Рисунок 8.5 65 Разработать средствами MS Access экранную форму, которая позволит для каждого поставщика видеть список заключенных с ним договоров. Возможный вариант реализации такой формы приведен на рис. 8.6. Как видно из рисунка, форма состоит из главной и подчиненной форм. Рисунок 8.6 Эти главная и подчиненная формы в режиме конструктора приведены на рисунках 8.7 и 8.8. Рисунок 8.7 66 Рисунок 8.8 После создания формы проверить возможность работы с базой данных, используя в качестве интерфейса пользователя созданную форму. Используя ранее полученные ранее навыки работы с СУБД MS Access создать другие формы, позволяющие работать с базой данных, а также средства обработки данных (запросы, отчеты), позволяющие обрабатывать информацию, хранящуюся в базе данных, выводить ее на печать и т.д. Составить отчет по проделанной работе. 67 Практическая работа №9. Применение встроенных вычислительных функций MS Excel для финансового анализа Задание: Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных. Исходные данные представлены на рис.9.1, результаты работы – на рисунках 9.2, 9.3. и 9.4. Порядок выполнения работы: 1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу. Рисунок 9.1. Исходные данные для задания 2. Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход. 3. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций f(x). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». 4. Вычислите «Общий финансовый результат за неделю». 68 Рисунок 9.2. Таблица расчета финансового результата 5. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм. Рисунок 9.3. Конечный вид диаграммы задания 6. Произведите фильтрацию значений дохода, превышающих 4000 р. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход») и вы увидите список неповторяющихся значений этого поля. Выберите команду для фильтрации – Условие. В открывшемся окне Пользовательский автофильтр задайте условие «Больше 4000». Произойдет отбор данных по заданному значению. Проследите как изменился вид таблицы и построенная диаграмма (рис. 9.4) 69 Рисунок 9.4. Вид таблицы и диаграммы после фильтрации Дополнительные задания: Задание 1. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки; по результатам расчета построить круговую диаграмму сумы продаж. Задание 2. Заполнить ведомость учета брака, произвести расчеты, выделить максимальную, минимальную и среднюю суммы брака, а также средний процент брака: произвести фильтрацию данных по условию процента брака <8%, 70 построить график отфильтрованных значений изменения суммы брака по месяцам. Формула для расчета: Сумма брака = Процент брака х Сумма зарплаты. В колонке «Процент брака» установите процентный формат чисел. Задание 3. Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму)Ж произвести фильтрацию по цене, превышающей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции. Формулы для расчета: Всего = Безналичные платежи + Наличные платежи; Выручка от продажи = Цена х Всего. Составить отчет по проделанной работе. 71 Практическая работа №10. Анализ данных «что-если» в MS Excel Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требует-ся ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Анализ «что-если» -процесс изменения значений ячеек и анализа влияния этих изменений на ре-зультат вычисления формул на листе. Подбор параметра -способ поиска определенного значения ячейки путем изменения значения вдругой ячейке. При подборе параметра Microsoft Excel изменяет значение в одной конкретной в ячейке до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат. Данные'>Инструмент «Подбор параметра» 1. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если»,а затем выбрать в списке пункт Подбор параметра. 2. В поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую. 3. Ввести искомый результат в поле Значение. 4. В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать. ! Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку. Требования к использованию возможности «Подбор параметра»: 1. Ячейка, адрес которой указывается в поле Значение должна содержать формулу, а значение в ней должно быть наиболее близким к тому, которое требуется получить. 2. Ячейка, в которой должно быть выведено искомое значение, должна прямо или косвенно влиять на результат в ячейке с формулой (п.1). Задание 1. Рассмотрим задачу с поступлением в определенное учебное заведение. На данный момент абитуриентом набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение. На изображении ниже видно, что баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что неизвестно, каким будет балл за последнее задание (тестирование 3), можно написать формулу, которая вычислит средний балл сразу за все задания. Для этого необходимо вычислить среднее арифметическое для всех пяти оценок. Введем выражение =СРЗНАЧ(B2:B6) в ячейку B7. После применения Подбора параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение (рисунок 10.1). 72 Рисунок 10.1 1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, необходимо выбирать ячейку, которая уже содержит формулу или функцию. В рассматриваем случае выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6) (рис.10.2). Рисунок 10.2 2. На вкладке Данные выберите команду Анализ "что если", а затем в выпадающем меню нажмите Подбор параметра (рис. 10.3). Рисунок 10.3 73 3. Появится диалоговое окно с тремя полями: – Установить в ячейке - ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее. – Значение - требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить. – Изменяя значение ячейки - ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании. 4. Выполнив все шаги, нажмите ОК (рис.10.4). Рисунок 10.4 5. Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК. Рисунок 10.5 6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше (рис. 10.6). 74 Рисунок 10.6 Задание Для уравнения y=23+x+x 2 в Excel выполнить подбор параметра, который дает значение y равное 112. Составить отчет по проделанной работе. 75 Практическая работа №11. Консолидация данных и их анализ в сводной таблице MS Excel Консолидация данных - это один из способов вычисления Итогов, но данные источников могут располагаться на одном или нескольких листах, одной или нескольких книг (рис. 11.1). Рисунок 11.1 В Excel существуют операции, позволяющие выполнять несколько вариантов консолидации списков. – по расположению; – по категориям; – с помощью сводных таблиц. При консолидации по расположению все списки источники имеют структуру полей, названия заголовков и количество записей. Например, ведомости заработных плат за несколько месяцев (если отсутствует «текучка кадров»). В данном варианте строка заголовков списков источников одинаковая и совпадает со строкой заголовков результата. Операция консолидации по расположению коммутативна, т.е. результат не зависит от порядка консолидации списков источников. При консолидации по категориям списки источники содержат однотипные данные, но расположены поля в этих списках по-разному и количество записей тоже разное. Список результатов консолидации также содержит поля отличающихся от расположения полей источников. Операций консолидации по категориям не коммутативна, т.е. структура списка результата зависит от порядка консолидируемых источников. Консолидация данных производится с помощью команды Данные / Консолидация. Область консолидации Область источник Лист 1 Область источник Книга 1/лист1 Область источник Лист 2 76 Сводная таблица - это таблица для автоматического подведения итогов взятых из разных списков-таблиц, которые могут находиться на разных источниках, в том числе и на Сайтах Интернет, отличаются структурой полей и количеством записей. Таким образом, сводная таблица является наиболее полным способом вычисления итогов. Сводная таблица Excel является пользовательским интерфейсом для отображения OLAP-куба. Создание и обработка сводных таблиц осуществляется с помощью Мастера сводных таблиц, Данные / Сводная таблица. Задание 1. Консолидация данных по расположению. Есть 4 файла, одинаковых по структуре. Допустим, поквартальные итоги продаж мебели (рис. 11.2) Лист 1кв Лист 2кв Лист 3кв Лист 4кв Рисунок 11.2 Нужно сделать общий отчет с помощью «Консолидации данных». Сначала проверим, чтобы: – макеты всех таблиц были одинаковыми; – названия столбцов – идентичными (допускается перестановка колонок); – нет пустых строк и столбцов. Диапазоны с исходными данными нужно открыть. Для консолидированных данных отводим новый лист «ИТОГИ». Открываем 77 его. Ставим курсор в первую ячейку объединенного диапазона. Внимание! Правее и ниже этой ячейки должно быть свободно. Команда «Консолидация» заполнит столько строк и столбцов, сколько нужно. Переходим на вкладку «Данные». В группе «Работа с данными» нажимаем кнопку «Консолидация» (рис. 11.3). Рисунок 11.3 Открывается диалоговое окно вида (рис. 11.4): Рисунок 11.4 На картинке открыт выпадающий список «Функций». Это виды вычислений, которые может выполнять команда «Консолидация» при работе с данными. Выберем «Сумму» (значения в исходных диапазонах будут суммироваться). Переходим к заполнению следующего поля – «Ссылка». Ставим в поле курсор. Открываем лист «1 квартал». Выделяем таблицу вместе с шапкой. В поле «Ссылка» появится первый диапазон для консолидации. Нажимаем кнопку «Добавить». Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Нажимаем на кнопку «Добавить» (рис. 11.5). 78 Рисунок 11.5 Таблицы для консолидации отображаются в поле «Список диапазонов». Чтобы автоматически сделать заголовки для столбцов консолидированной таблицы, ставим галочку напротив «подписи верхней строки». Чтобы команда суммировала все значения по каждой уникальной записи крайнего левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными» (рис. 11.6) Рисунок 11.6 Внимание! Если вносить в исходные таблицы новые значения, сверх выбранного для консолидации диапазона, они не будут отображаться в объединенном отчете. Чтобы можно было вносить данные вручную, снимите флажок «Создавать связи с исходными данными». Для выхода из меню «Консолидации» и создания сводной таблицы нажимаем ОК (рис. 11.7). |