Лабораторный практикум по TtandactSQL. Системы управления базами данных
 Скачать 0.89 Mb.
|
|
Федеральное бюджетное образовательное учреждение высшего образования «Омский государственный технический университет» Факультет информационных технологий и компьютерных систем Кафедра «Автоматизированные системы обработки информации и управления» Отчёт по работе с Transact-SQL дисциплина «Системы управления базами данных» Выполнил студент гр. ИВТ-191 Куторова А.Ф. ____________ Проверил ст. преподаватель Маркова М. П. ____________ Омск – 2021 Введение В отчете представлена информация о результатах выполнения лабораторного практикума по теме «Работа с Transaсt-SQL». Решение практических задач направлено на развитие умений и навыков и их применение в дальнейшем при создании приложений баз данных для автоматизированных информационных систем, разрабатываемых для различных областей производства. Лабораторные работы выполнялись на языке Transaсt-SQL, который, в свою очередь, является структурированным языком запросов и стандартом языка для работы с реляционными базами данных. В ходе выполнения лабораторных работ была разработана реляционная база данных на платформе SQL Server, инструментом разработки которой явилось клиентское приложение Microsoft SQL Management Studio. Описание предметной области Разработать прикладное программное обеспечение торгово-посреднической фирмы «Столица». Бизнес этого предприятия предельно прост: «покупай дешевле – продавай дороже», или состыкуй продавца и покупателя и получи «комиссионные». Основной упор фирма делает на закупки продуктов питания в других регионах страны и за рубежом – там, где они производятся и стоят дешевле, чем в нашем регионе. Часть продукции может быть закуплена и у местных продавцов. В этом случае фирма получает прибыль за счет того, что крупные партии товара стоят дешевле, чем мелкие. Имейте в виду, что товар не может быть продан дешевле, чем он куплен. Лабораторная работа №1 Проектирование базы данных с использованием ER-технологий ER-модель – представляет собой схему, составными элементами которой являются: Сущность – реальный либо воображаемый объект, информацию о котором необходимо хранить в базе данных; Связь – отображаемая графически на диаграмме ассоциация между двумя (чаще всего) сущностями, или одной и той же сущностью. Спроектированная по нашей предметной области диаграмма представлена на рисунке 1.  Рисунок 1 – ER-диаграмма Лабораторная работа №2 Создание новой базы данных в среде SQLServer В базе данных обязательно должно быть задано её логическое имя. Остальные конструкции необязательны. В этом случае базе данных и её файлам будут присвоены значения всех характеристик по умолчанию. Логическое имя должно соответствовать правилам задания идентификаторов: - в обычном идентификаторе можно использовать буквы кириллицы; - в идентификаторах с разделителями можно использовать любые символы (при этом идентификатор заключается в квадратные скобки). Логическое имя БД содержит не более 128 символов. Оно должно быть уникальным среди имён всех БД текущего экземпляра сервера. Создание базы данных показано на рисунке 2.  Рисунок 2 – Создание базы данных «Marketplace» Лабораторная работа №3 Создание и связывание таблиц базы данных в среде SQLServer Таблицы базы данных содержат данные предметной области. Структура таблицы задаётся набором столбцов. Накладывают определённые условия на вводимые данные на уровне столбца или на уровне таблицы. Есть 4 вида ограничений: - первичный ключ (PRIMARY KEY); - уникальный ключ (UNIQUE); - внешний ключ (FOREIGN KEY); - ограничения проверки (СHEСK). В нашей базе данных мы используем только PRYMARY KEY и FOREIGN KEY. Создание таблиц представлено на рисунках 3-6.  Рисунок 3 – Создание таблицы «Sellers»  Рисунок 4 – Создание таблицы «Products»  Рисунок 5 – Создание таблицы «Buyers»  Рисунок 6 – Создание таблицы «Deals» Лабораторная работа №4 Вставка, удаление и обновление данных Изменять содержимое таблиц позволяют операторы INSERT, UPDATE и DELETE, работающие с таблицами и представлениями. Для добавления в таблицу или представления одной, или нескольких строк используется оператор INSERT. Изменение значений полей в существующих записях обеспечивает оператор UPDATE. Для удаления одной или более строк из таблицы или представления предусмотрены операторы DELETE и TRUNСATE TABLE. Заполнение таблиц, созданных в прошлой лабораторной работе, представлено на рисунках 7-10.  Рисунок 7 – Заполнение таблицы «Buyers»  Рисунок 8 – Заполнение таблицы «Seller»  Рисунок 9 – Заполнение таблицы «Products»  Рисунок 10 – Заполнение таблицы «Deals» Лабораторная работа №5 Создание запросов на выборку Для извлечения строк из одной или нескольких таблиц базы данных (без изменения) используется оператор SELEСT. Результирующий набора записей возвращается в виде таблицы, его можно использовать для создания новой таблицы. Сортировка результатов: чтобы отсортировать значения по одному из столбцов, необходимо в секции ORDER BY указать этот столбец. По умолчанию сортировка производится по возрастанию, но если после имени поля добавить ключевое слово ASС (по возрастанию) или DESС (по убыванию), то сортировка осуществится соответствующим образом. Для выбора записей, удовлетворяющих определённым критериям поиска, можно использовать конструкцию WHERE. Созданные запросы представлены на рисунках 11-14.  Рисунок 11 – Запрос, выводящий товары, единица измерения которых соответствует «кг.»  Рисунок 12 - Запрос, выводящий информацию о фирмах с сортировкой по имени лидера  Рисунок 13 - Запрос, выводящий стоимость сделок каждого продавца  Рисунок 14 - Запрос, выводящий сделку с максимальной стоимостью. Лабораторная работа №6 Представления Представление – это запрос на выборку из одной или нескольких таблиц, которому присваивается уникальное имя и которые можно сохранять или удалять из базы данных как хранимую процедуру. Фактически представление является предварительно проанализированным оператором SELECT, который может интерпретироваться как таблица. Представление является логической, а не физической таблицей, однако в большинстве случаев работает как обычная таблица. Представления могут создаваться только в той базе данных, которую вы используете. Созданные представления показаны на рисунках 15-17.   Рисунок 15 - Вертикальное представление list_sel_view, демонстрирующее телефоны компаний без кода страны.   Рисунок 16 - Представление, которое отображает стоимость сделки каждой компании.  Рисунок 17 - Запрос к представлению price_view В запросе к представлению price_view отображаются данные о максимальной и минимальной стоимости сделки. Лабораторная работа №7 Хранимые процедуры Хранимые процедуры – предварительно откомпилированные процедуры, написанные на Transaсt-SQL и находящиеся в базе данных. Выполняются быстрее обычного Transaсt-SQL, поскольку хранятся в откомпилированном виде. Помогают изолировать пользователей от базовых табличных структур, скрыть особенности реализации каких-либо возможностей. Хранимая процедура создаётся в текущей базе данных в указанной схеме. К ней могут обращаться другие процедуры, триггеры, клиентские программы. Процедура может получать входные параметры и возвращать выходные параметры. Хранимая процедура выполняется на сервере, что уменьшает сетевой график. Минимально необходим для базы данных набор хранимых процедур, обеспечивающий выборку, вставку, модификацию и удалние данных для всех таблиц. Написанные хранимые процедуры представлены на рисунках 18-24.   Рисунок 18 – Процедура Deal_Select.Процедура Deal_Select использует базовые таблицы Sellers, Buyer, Product и связанную таблицу Deals. Параметры процедуры обеспечивают возможность выбора данных по параметрам запроса. Вызов процедуры Deal_Select с использованием параметра позволяет получить разную информацию.  Рисунок 19 – Процедура Deal_Select. На рисунке 19 представлена процедура с аргументом, обозначающим ИНН продавца, а представление отображает сделки определенного продавца.  Рисунок 20 – Процедура Deal_Select. На рисунке 20 представлена процедура с аргументом, обозначающим номерной штрих-код, а представление отображает сделки с определенным видом товара.  Рисунок 21 – Процедура Deal_Select. На рисунке 21 представлена процедура с аргументами, обозначающими номерной штрих-код и ИНН продавца, а представление отображает сделки определенного прродавца с определенным товаром.   Рисунок 22 – Пример вставки значения в таблицу   Рисунок 23 – Изменение данных с помощью процедуры Prod_Update   Рисунок 24 – Удаление данных с помощью процедуры Prod_Delete Лабораторная работа №8 Курсоры Курсор представляет собой запрос к базе данных, хранящейся на сервере СУБД. Это результирующее множество, выборка, полученная в результате выполнения оператора SELEСT. Курсоры позволяют приложениям выполнять действия с отдельными записями результирующего набора, вместо обработки этого набора в целом. Поместив код SQL в цикл, можно, строка за строкой обработать всю таблицу. Курсоры незаменимы, если требуется выбрать строки из таблицы, проверить их содержимоей, а также выполнить различные операции на основании содержимого полей. Созданные курсоры представлены на рисунках 25-28.  Рисунок 25 – Результат работы курсора по выводу списка всех товаров с неистекшим сроком годности Создаём новую таблицу NotExp на основе таблицы, где будут храниться данне о продуктах с неистекшим сроком годности.  Рисунок 26 – Создание таблицы для внесения данных из курсора  Рисунок 27 – Заполнение новой таблицы с помощью курсора  Рисунок 28 – Результат работы курсора по записи данных в таблицу. Лабораторная работа №9 Транзакции Транзакция – один или несколько операторов SQL, которые либо успешно завершаются как единое целое, либо отменяются как единое целое. Фиксация транзакции обеспечивает запись на диск изменений, сделанных в процессе выполнения транзакции. Откат транзакции обеспеивает аннулирование всех изменений, сделанных операторами в теле транзакции. Именно транзакции обеспечивают целостность данных в любых ситуациях. Созданные транзакции представлены на рисунках 29-30.  Рисунок 29 – Транзакция с точкой сохранения в режиме явных транзакций, её откат и фиксация.  Рисунок 30 – Решение приведённой выше задачи, но с использванием неявных транзакций Лабораторная работа №10 Триггеры Триггер – программный объект базы данных, выполняемый на стороне сервера. Вызывается автоматически при наступлении определённого события. Это хранимая процедура, привязанная к событию в базе данных. В отличие от обычных хранимых процедур триггеры не могут вызываться напрямую и не принимают параметры. Триггеры могут создаваться для представлений. Триггер срабатывает, когда определённое действие с базой данных выполняет приложение, хранимая процедура или триггер. Написанные триггеры и результаты их работы представлены на рисунках 31-32.   Рисунок 31 – DML-триггер, запрещающий удаление.   Рисунок 32 – DML-триггер, запрещающий добавление Вывод В ходе выполнения лабораторных работ была разработана база данных по предметной области «Посредническая фирма». Были получены необходимые базовые навыки по работе в SQL Server и языком Transaсt-SQL, которые в дальнейшем пригодятся в профессиональной деятельности. |