Материал: BD_Laboratornyi_774_praktikum

Министерство образования Республики Беларусь

Учреждение образования «Белорусский государственный университет информатики и радиоэлектроники»

Кафедра экономической информатики

А. А. Бутов, И. Г. Орешко, Е. А. Шестаков

Базы данных

лабораторный практикум

для студентов специальности «Информационные системы и технологии»

всех форм обучения

Минск 2009

УДК 004.6 (076)

ББК 32.973.26-018 я 73

Б 93

Р е ц е н з е н т

зав. кафедрой ИИТ БГУИР, д-р техн. наук

проф. В. В. Голенков

Бутов А. А.

Б 93 Базы данных. Лабораторный практикум для студ. спец. «Информационные системы и технологии» БГУИР / А. А. Бутов, И. Г. Орешко, Е. А. Шестаков. – Минск : БГУИР, 2009. – 108 с. : ил.

ISBN

В практикуме представлен курс из восьми лабораторных работ, предназначенных для практического освоения методов работы с базами данных в среде СУБД и Microsoft SQL Server, а также способов моделирования данных с помощью CASE-средства разработки информационных систем ERwin. К каждой работе даны краткие теоретические сведения, необходимые для ее выполнения, и приведены соответствующие задания.

УДК 004.6 (076) ББК 32.973.26-018 я 73

ISBN  Бутов А. А., Орешко И. Г., Шестаков Е. А., 2009

 УО «Белорусский государственный университет информатики и радиоэлектроники», 2009

Содержание

Введение 4

Лабораторная работа №1. Логические и физические модели данных 6

Методические указания 6

Задание к работе 10

Лабораторная работа №2. Усложненные диаграммы «сущность-связь» 18

Методические указания 18

Задание к работе 19

25

Лабораторная работа №3. Работа в среде СУБД «MS SQL Server 2005» 25

Методические указания 25

Задание к работе 28

Лабораторная работа №4. Создание базы данных и ее объектов с помощью команд языка Transact-SQL 39

Методические указания и задания к выполнению 39

Лабораторная работа №5. Манипулирование данными с помощью команд языка Transact-SQL 57

Методические указания 57

Задание к работе 66

Лабораторная работа №6. Программирование на языке Transact-SQL 70

Методические указания 70

Задание к работе 84

Лабораторная работа №7. Курсоры и триггеры в языке Transact-SQL 87

Методические указания 87

Задание к работе 98

Литература 101

Введение

В рамках дисциплины «Базы данных» изучаются теоретические основы и практические методы проектирования баз данных. Настоящий практикум предназначен для освоения практических методов проектирования реляционных баз данных и приобретения навыков использования языков баз данных для проектирования, управления, сопровождения и администрирования реляционных БД.

Практические методы, осваиваемые в ходе выполнения лабораторных работ, входящих в настоящий практикум, позволяют:

• построить информационную модель предметной области;

• создать соответствующую модели базу данных в контексте используемой системы управления базами данных (СУБД);

• обеспечить защиту информации от несанкционированного доступа;

• организовать ввод информации в таблицы базы данных;

• сформулировать запросы к базе данных;

• освоить программирование на языке баз данных;

• организовать работу в многопользовательской базе данных.

Осваиваемыми инструментальными средствами являются система управления базами данных (СУБД) Microsoft SQL Server 2005, а также CASE-средство разработки моделей данных ERwin 7.1.

Важнейшей задачей любой СУБД является разработка баз данных, содержащих информацию, связанную с той или иной предметной областью. База данных, разрабатываемая в среде СУБД, может содержать в себе следующие основные объекты: таблицы, индексы, просмотры, хранимые процедуры, триггеры и др.

Таблицы непосредственно хранят информацию, относящуюся к конкретной предметной области.

Индексы предназначены для представления данных в упорядоченном виде и для ускорения операций по манипулированию данными.

Просмотры позволяют получать всевозможные выборки информации из одной или нескольких таблиц и/или просмотров, используя при этом различные критерии отбора данных.

Хранимые процедуры являются подпрограммами на языке SQL и играют примерно ту же роль, что и функции в языках программирования.

Триггеры также являются подпрограммами на языке SQL и служат обработчиками событий, связанных с текущими изменениями данных в базе данных.

Все объекты базы данных тесно связаны между собой, причем важнейшими объектами являются таблицы, т. к. без них создание остальных объектов и, в первую очередь индексов и просмотров, лишено какого бы то ни было практического смысла. В свою очередь, хранимые процедуры, как правило, работают с данными из каких-либо таблиц и/или просмотров, триггеры же могут выполнять самые различные действия, чаще всего связанные с поддержкой реализованных в базе данных бизнес-правил.

Лабораторная работа №1. Логические и физические модели данных

Цель работы

1. Ознакомиться с процессами создания логической и физической моделей данных.

2. Освоить способ установления связей между сущностями.

3. Изучить процессы прямого и обратного проектирования в ERwin.

Задачи

1. Создание логической модели данных.

2. Установление связей между сущностями типа 1 : М и М : М.

3. Создание подмножеств модели и хранимых отображений.

4. Формирование новых доменов, альтернативных ключей и инверсионных входов.

5. Переход к физической модели данных с преобразованием связей типа М:М в связи 1:М и дополнительные связующие таблицы, установка правил валидации и значений по умолчанию.

6. Выполнение с помощью мастеров процессов прямого и обратного проектирования.

Методические указания

Математический аппарат, используемый для моделирования табличного представления данных, получил название реляционной алгебры. В ней используются следующие основные понятия.

Сущность – это объект предметной области, которая исследуется и моделируется.

Кортеж – это экземпляр объекта предметной области (экземпляр сущности).

Атрибут отражает определенное свойство, качество, признак сущности.

Домен задает множество допустимых значений атрибута.

Смысл доменов состоит в том, что они ограничивают сравнения и позволяют правильно моделировать предметную область.

Моделирование структур данных в рамках реляционной модели осложняется невозможностью сразу определить полный список сущностей, связей, атрибутов и определяющих их доменов, а также нужное распределение атрибутов по сущностям, поскольку эти аспекты в процессе проектирования могут многократно уточняться и изменяться. Поэтому различают три уровня логической модели, отличающиеся глубиной представления информации о структуре данных:

• Модель уровня сущностей

• Модель данных, основанная на ключах

• Полная атрибутивная модель.

Модель уровня сущностей представляет собой модель данных верхнего уровня, которая отражает основные бизнес-правила предметной области (бизнес-правила – это принятые в организации ограничения, спецификации, критерии, соглашения по ведению учета и отчетности). Модель этого уровня не слишком подробна и включает в себя лишь основные сущности и связи между ними. Как правило, модель уровня сущностей используется для презентаций и обсуждения структуры данных с экспертами предметной области.

Модель данных, основанная на ключах, является более подробной и включает в себя все сущности, их первичные ключи, а также связи между сущностями.

Полная атрибутивная модель дает наиболее детальное представление о структуре данных и включает все сущности, атрибуты, домены и связи. Сущности, как правило, приведены к третьей нормальной форме.

Эти разновидности моделей, представленные в графической форме, называются диаграммами «сущность-связь» или ER‑диаграммами (Entity – сущность, Relation – связь). Существуют различные варианты ER‑диаграмм, отличающиеся способами графического изображения сущностей и связей. Первый вариант был предложен Питером Ченом в 1976 г. (нотация Чена). Затем появились другие варианты (нотация Мартина, нотация IDEFIX, нотация Баркера и др.).

Впоследствии был создан целый ряд программных средств для автоматизированного построения ER-диаграмм, например: ERwin, SilverRun, Design/IDEF, Power Designer и др. Все они относятся к классу CASE-технологий (Computer-Assisted Systems Engineering – компьютеризированное проектирование систем).

Эти CASE-средства удобны тем, что в них процесс выделения сущностей, связей, а затем и атрибутов, является итерационным (повторяющимся). Разработав первый приближенный вариант ER-диаграммы, далее его уточняют, опрашивая экспертов предметной области, после чего все повторяется. При этом документацией, в которой фиксируются результаты опросов, являются сами ER-диаграммы. Попутно выполняется нормализация данных. Процесс заканчивается получением ER-диаграммы, соответствующей полной атрибутивной модели.

Рассмотрим работу с ER-диаграммами в нотации IDEFIX, используемой в CASE-средстве ERwin. Изучив конкретную предметную область, строим исходную ER‑диаграмму, соответствующую модели уровня сущностей (рис. 1.1).

Клиент размещает Заказ выполняется Сотрудник

Отдел состоит из

Рис. 1.1. ER‑диаграмма модели уровня сущностей

Связи между сущностями на ER‑диаграммах обозначаются следующим образом:

сильная или идентифицирующая связь типа 1 : М

слабая или неидентифицирующая связь типа 1 : М

связь типа М : М

В случае связи 1 : М (один ко многим) одна из связываемых сущностей выступает в роли родительской или главной, а другая – в роли дочерней или подчиненной. Эта связь каждому кортежу родительской сущности ставит в соответствие любое (в том числе нулевое) число кортежей дочерней сущности, однако каждый кортеж дочерней сущности может быть связан только с одним кортежем родительской сущности.

Механизм реализации связи «один ко многим» состоит в том, что в дочернюю сущность добавляются атрибуты, дублирующие ключевые атрибуты родительской сущности (т.е. атрибуты, входящие в первичный или альтернативный ключ). Эти атрибуты получают название внешнего ключа (Foreign Key, сокращенно FK) и с их помощью устанавливается связь между кортежами родительской сущности – с одной стороны – и подмножествами кортежей дочерней сущности – с другой. Еще такие атрибуты называют мигрирующими из родительской сущности. Если дочерняя сущность является зависимой от родительской сущности, то мигрирующие атрибуты включаются в состав первичного ключа дочерней сущности, в противном случае – в состав ее неключевых атрибутов.

На уровне логической модели возможны также связи типа М : М (многие ко многим), которые используются тогда, когда между атрибутами сущностей существуют многозначные зависимости. Связь такого типа каждому кортежу одной сущности ставит в соответствие любое (в том числе нулевое) число кортежей другой сущности и наоборот.

Возвращаясь к рассмотрению примера, строим на следующем итерационном шаге более полную ER‑диаграмму, соответствующую модели данных, основанной на ключах (рис. 1.2).

Клиент размещает Заказ выполняется Сотрудник

Код клиента

/

№ отдела (FK)

Товар заказывается Отдел состоит из

№ отдела

Рис. 1.2. ER‑диаграмма модели, основанной на ключах

Далее, на очередном итерационном шаге, получаем ER‑диаграмму, соответствующую полной атрибутивной модели данных (рис. 1.3).

Клиент Заказ Сотрудник

Код клиента

Фамилия

Адрес

Телефон

размещает выполняется

Дата заказа

Количество

Описание заказа