Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Министерство образования Республики Беларусь
Учреждение образования
«Белорусский государственный университет
информатики и радиоэлектроники»
Кафедра экономической информатики
А. А. Бутов, И. Г. Орешко, Е. А. Шестаков
БАЗЫ ДАННЫХ
лабораторный практикум
для студентов специальности «Информационные системы и технологии»
всех форм обучения
Минск 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
Содержание
|
[0.0.0.1] Кафедра экономической информатики
[1]
[2] [2.1] Методические указания [2.2] Задание к работе [3] Литература |
В рамках дисциплины «Базы данных» изучаются теоретические основы и практические методы проектирования баз данных. Настоящий практикум предназначен для освоения практических методов проектирования реляционных баз данных и приобретения навыков использования языков баз данных для проектирования, управления, сопровождения и администрирования реляционных БД.
Практические методы, осваиваемые в ходе выполнения лабораторных работ, входящих в настоящий практикум, позволяют:
Осваиваемыми инструментальными средствами являются система управления базами данных (СУБД) Microsoft SQL Server 2005, а также CASE-средство разработки моделей данных ERwin 7.1.
Важнейшей задачей любой СУБД является разработка баз данных, содержащих информацию, связанную с той или иной предметной областью. База данных, разрабатываемая в среде СУБД, может содержать в себе следующие основные объекты: таблицы, индексы, просмотры, хранимые процедуры, триггеры и др.
Таблицы непосредственно хранят информацию, относящуюся к конкретной предметной области.
Индексы предназначены для представления данных в упорядоченном виде и для ускорения операций по манипулированию данными.
Просмотры позволяют получать всевозможные выборки информации из одной или нескольких таблиц и/или просмотров, используя при этом различные критерии отбора данных.
Хранимые процедуры являются подпрограммами на языке SQL и играют примерно ту же роль, что и функции в языках программирования.
Триггеры также являются подпрограммами на языке SQL и служат обработчиками событий, связанных с текущими изменениями данных в базе данных.
Все объекты базы данных тесно связаны между собой, причем важнейшими объектами являются таблицы, т. к. без них создание остальных объектов и, в первую очередь индексов и просмотров, лишено какого бы то ни было практического смысла. В свою очередь, хранимые процедуры, как правило, работают с данными из каких-либо таблиц и/или просмотров, триггеры же могут выполнять самые различные действия, чаще всего связанные с поддержкой реализованных в базе данных бизнес-правил.
Цель работы
Задачи
Математический аппарат, используемый для моделирования табличного представления данных, получил название реляционной алгебры. В ней используются следующие основные понятия.
|
Реляционная алгебра (логическая модель) |
Реляционная база данных (физическая модель) |
|
Сущность Кортеж Атрибут Домен |
Таблица Строка (Запись) Столбец (Поле) Тип данных |
Сущность – это объект предметной области, которая исследуется и моделируется.
Кортеж – это экземпляр объекта предметной области (экземпляр сущности).
Атрибут отражает определенное свойство, качество, признак сущности.
Домен задает множество допустимых значений атрибута.
Смысл доменов состоит в том, что они ограничивают сравнения и позволяют правильно моделировать предметную область.
Моделирование структур данных в рамках реляционной модели осложняется невозможностью сразу определить полный список сущностей, связей, атрибутов и определяющих их доменов, а также нужное распределение атрибутов по сущностям, поскольку эти аспекты в процессе проектирования могут многократно уточняться и изменяться. Поэтому различают три уровня логической модели, отличающиеся глубиной представления информации о структуре данных:
Модель уровня сущностей представляет собой модель данных верхнего уровня, которая отражает основные бизнес-правила предметной области (бизнес-правила – это принятые в организации ограничения, спецификации, критерии, соглашения по ведению учета и отчетности). Модель этого уровня не слишком подробна и включает в себя лишь основные сущности и связи между ними. Как правило, модель уровня сущностей используется для презентаций и обсуждения структуры данных с экспертами предметной области.
Модель данных, основанная на ключах, является более подробной и включает в себя все сущности, их первичные ключи, а также связи между сущностями.
Полная атрибутивная модель дает наиболее детальное представление о структуре данных и включает все сущности, атрибуты, домены и связи. Сущности, как правило, приведены к третьей нормальной форме.
Эти разновидности моделей, представленные в графической форме, называются диаграммами «сущность-связь» или 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).
Клиент размещает Заказ выполняется Сотрудник
//
Товар заказывается Отдел состоит из
Рис. 1.2. ER диаграмма модели, основанной на ключах
Далее, на очередном итерационном шаге, получаем ER диаграмму, соответствующую полной атрибутивной модели данных (рис. 1.3).
Клиент Заказ Сотрудник
размещает выполняется
Товар
Отдел
заказывается состоит из
Рис. 1.3. ER диаграмма полной атрибутивной модели
Переход от логической модели данных к физической заключается в том, что сущности преобразуются в таблицы, атрибуты и кортежи становятся соответственно столбцами и строками таблиц, домены отображаются в типы данных, принятые в конкретной СУБД. Таблицы, как и сущности, снабжаются первичными и внешними ключами и связываются между собой с помощью связей типа 1:1 и 1:М. Связи типа М:М при переходе к физической модели преобразуются в пары связей типа 1:М и связующие таблицы.
Если логическая модель представлена в виде ER-диаграммы, то переход к физической модели значительно упрощается. В этом случае с помощью CASE-средства, например ERwin, можно выбрать нужную СУБД и автоматически создать соответствующую физическую модель данных. Затем на ее основе ERwin может сгенерировать системный каталог базы данных или соответствующий SQL-скрипт (описание базы данных на языке SQL). Этот процесс называется прямым проектированием. Тем самым достигается масштабируемость – создав один раз логическую модель данных, можно генерировать физические модели данных под любую СУБД, которую поддерживает ERwin. С другой стороны, ERwin способен по содержимому системного каталога базы данных или SQL-скрипту воссоздать физическую и логическую модели данных. Этот процесс называется обратным проектированием. На основе логической модели, полученной в процессе обратного проектирования, можно сгенерировать физическую модель и системный каталог базы данных для другой СУБД. Тем самым решается задача по переносу структуры базы данных с одной СУБД на другую, например с SQL Server на Oracle или с Access на Sybase и т.д.
Раздел I. Создание сущностей в ERwin
Создайте в ERwin новую модель данных. Тип модели (New Model Type) – Логическая/Физическая, целевая база данных (Target Database) – Access.
Логическая (Logical) модель должна включать в себя следующие 6 приведенных ниже сущностей (Entity). При этом первый атрибут в каждой сущности сделайте первичным ключом (Primary Key); атрибуты, отмеченные одной или двумя звездочками, – не создавайте, так как в дальнейшем они будут созданы автоматически в процессе установления связей между сущностями.
Сущность 1: Поставщик
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код поставщика Имя поставщика Условия оплаты Код региона** Заметки |
Number String String Number Blob |
Да Да |
Сущность 2: Товар
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код товара Наименование Единица_изм Цена Код валюты** |
Number String String Number String |
Да Да |
Сущность 3: Клиент
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код клиента Имя клиента ФИО руководителя Код региона** |
Number String String Number |
Да Да |
Сущность 4: Заказ
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код заказа Код клиента* Код товара* Количество Дата заказа Срок поставки Код поставщика** |
Number Number Number Number Datetime Datetime Number |
Да Да Да Да |
Сущность 5: Регион
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код региона Страна Область Город Адрес Телефон Факс |
Number String String String String String String |
Да Да Да Да Да |
Сущность 6: Валюта
|
Атрибут (Attribute) |
Домен (Domain) |
Обязательный атрибут (Not Null) |
|
Код валюты Имя валюты Шаг округления Курс валюты |
String String Number Number |
Да Да |
Каждую сущность снабдите кратким описанием ее назначения (Definition). Например, для первой сущности это может быть «Список поставщиков товаров», для второй – «Перечень предлагаемых товаров» и т.д.
Раздел II. Создание связей между сущностями, подмножеств модели и хранимых отображений. Переход к физической модели данных
1. Создайте между сущностями связи типа 1 : М (один ко многим) таким образом, чтобы в результате сущности оказались дополнены атрибутами, отмеченными выше звездочками.
Связь 1:М между парой сущностей, одна из которых рассматривается как родительская, а другая – как дочерняя, создается следующим образом: на панели инструментов нужно нажать одну из двух кнопок создания связи 1:М (идентифицирующей – Identifying relationship или неидентифицирующей – Non-identifying relationship), после чего щелкнуть мышью на родительской сущности, а затем – на дочерней. При этом между сущностями появляется связь и в дочернюю сущность будет автоматически добавлены атрибуты, дублирующие ключевые атрибуты родительской сущности. Эти атрибуты получают название внешнего ключа (FK) и будут включены в состав ключевых (идентифицирующая связь) или неключевых (неидентифицируюшая связь) атрибутов дочерней сущности. Выше в сущностях атрибуты внешних ключей отмечены звездочками, причем одна звездочка указывает на идентифицирующую связь 1:М, а две – на неидентифицирующую связь 1:М.
Например, чтобы в сущности Поставщик появился атрибут Код региона нужно создать неиндефицирующую связь 1:М между родительской сущностью Регион и дочерней сущностью Поставщик.
2. Создайте между сущностями Поставщик и Товар связь типа М:М (многие ко многим). Эта связь создается следующим образом: на панели инструментов нужно нажать кнопку создания связи М:М (Many-to-many relationship), после чего щелкнуть мышью на одной сущности, а затем – на другой.
Для удобства разместите сущности в рабочей области так, чтобы связи между ними не пересекались между собой.
3. Выделите подсветкой одну из связей между сущностями и затем с помощью команды меню Model►Relationships откройте окно Relationships и дайте имя этой, а затем и остальным связям, используя для этого подходящие глаголы или глагольные формы. Для связей 1:М достаточно указать имя, характеризующее отношение или от родительской сущности к дочерней (Parent-to-Child) или от дочерней сущности к родительской (Child-to-Parent). Для связи М:М следует указывать имена как Parent-to-Child так и Child-to-Parent.
Например связи М:М между сущностями Поставщик и Товар можно дать такие имена: «Поставляет» (Parent-to-Child) и «Поставляется» (Child-to-Parent).
4. Измените заданное по умолчанию правило ссылочной целостности RESTRICT (Ограничить) на правило CASCADE (Каскадировать) для двух связей: для связи между сущностями Клиент и Заказ и для связи между сущностями Товар и Заказ. Для этого в окне Relationships перейдите на вкладку RI Actions, выберите вверху с помощью выпадающего списка Relationship нужную связь и далее поменяйте правило RESTRICT на правило CASCADE в каждом из списков: Parent Delete и Parent Update.
5. С помощью команды меню Model►Subject Areas откройте окно Subject Areas и создайте помимо существующего основного множества Main Subject Area еще два подмножества модели: Subject Area 1 и Subject Area 2, включив (вкладка Members) в первое из них сущности Клиент, Поставщик, Регион, а во второе – Товар со всеми непосредственно связанными с ней (Level = 1) сущностями. После этого просмотрите поочередно все три модели, используя для переключения между ними кнопку с выпадающим списком Create Subject Area, расположенную на панели инструментов.
6. С помощью команды меню Format►Stored Display Settings откройте окно Stored Displays и создайте для каждого из подмножеств модели Subject Area 1 и Subject Area 2 по два новых хранимых отображения (Display2 и Display3), отличающиеся друг от друга и от исходного отображения Display1 расположением частей логической модели на экране, масштабом, уровнями просмотра этой модели, видимостью отдельных компонентов сущностей и связей, а также другими характеристиками.
7. Сохраните модель данных на жестком диске в файле Склад.erwin.
8. Переключитесь на Main Subject Area / Display1, а затем с помощью команды меню Model►Relationships откройте окно Relationships и опробуйте настройку характеристик связей между сущностями, таких как имя связи (Verb Phrase), мощность (Cardinality), имя роли (RoleName), правила ссылочной целостности (RI Actions). Опробуйте режимы показа на диаграмме указанных выше характеристик связей с помощью команд меню: Format►Relationship Display►... В конце восстановите первоначальные настройки характеристик связей между сущностями (это можно сделать, если закрыть без сохранения текущую диаграмму модели с помощь команды меню File►Close, а затем снова открыть командой File►Open).
9. Опробуйте варианты показа на диаграмме различных уровней логической модели (команды меню: Format►Display Level►…) и различных способов отображения атрибутов в сущностях (команды меню: Format►Entity Display►…).
10. С помощью команды меню Model►Domain Dictionary откройте окно Domain Dictionary и создайте на основе домена Number новые домены Денежный и Точность. Далее свяжите атрибут Цена (сущность Товар) с новым доменом Денежный, а атрибут Шаг округления (сущность Валюта) - с новым доменом Точность.
11. С помощью команды меню Model►Key Groups откройте окно Key Groups и, используя кнопку New, создайте для сущностей дополнительно к имеющемуся первичному ключу еще и возможные альтернативные ключи (Alternate Key) и возможные инверсионные входы (Inversion Entry). Включите режим их отображения на диаграмме.
Факт вхождения отдельных атрибутов в состав ключей и инверсионных входов можно также увидеть в окне Attributes, которое открывается с помощью команды меню Model►Attributes. Для этого надо перейти на вкладку Key Group и начать последовательно перемещаться по списку атрибутов, расположенному слева.
12. Переключившись в режим показа физической модели, уберите пробелы в названиях полей (сделав первую букву второго слова прописной), а также проверьте и, в случае необходимости, скорректируйте их типы и длины, ориентируясь на специфику типов данных СУБД Access. В частности, поле Цена должно иметь тип Currency, а поля Шаг округления и Количество - тип Single.
13. В физической модели с помощью команды меню Model►Columns откройте окно Columns, перейдите на вкладку Constraint и задайте для тех полей, где это возможно, правила валидации (Validation Constraint) и/или значения по умолчанию (Default). Например, для полей Цена, Количество и КурсВалюты правило валидации заключается в выборе только неотрицательных значений, что обеспечивается вводом выражения: >= 0. Для поля ШагОкругления – в выборе значений из множества {10, 1, 0.01}, что обеспечивается вводом выражения: IN (10, 1, 0.01). Значением по умолчанию для поля Страна будет "Беларусь"; для поля УсловияОплаты – "Предоплата"; для поля ШагОкругления – 0.01; для поля Единица_изм – "штука"; для поля КодВалюты (только из таблицы Товар) – "BYR"; для поля ДатаЗаказа – Date(), т.е. текущая дата; для поля СрокПоставки – Date() + 14, т.е. через две недели от текущей даты.
14. В физической модели (окно Columns, вкладка ...Access) настроите свойство Формат (Format), установив его для поля Количество равным #,##0.000 и для поля КурсВалюты – равным #,##0. Настройте также свойство Маска ввода (Mask) для полей Телефон и Факс, установив его равным (999) 999-99-99.
15. В окне Model Properties, которое вызывается командой меню Model►Model Properties, на вкладке General установите флажок Auto apply Supertype-Subtype Identity transform. В физической модели выделите мышью связь М:М между таблицами Поставщик и Товар, после чего нажмите кнопку Many to Many Transform на панели инструментов и запустите мастер преобразования связи М:М. В результате эта связь будет преобразована в две связи 1:М путем создания дополнительной связующей таблицы. При этом в логической модели преобразований не будет и останется прежняя связь М:М.
Примечание 1. Если в окне Model Properties на вкладке General не устанавливать флажок Auto apply Supertype-Subtype Identity transform, то указанное выше преобразование связи будет выполнено также и в логической модели.
Примечание 2. Если в окне Model Properties на вкладке General установить флажок Auto apply Many-to-Many transform, то Erwin будет автоматически выполнять упомянутую выше трансформу связи М:М при переключении с логической модели на физическую.
16. Сохраните полученный вариант модели данных в файле Склад.erwin.
Раздел III. Выполнение операции прямого проектирования
1. Запустите СУБД Access и создайте новую пустую базу данных Склад.mdb, после чего закройте Access.
2. В ERwin из режима показа физической модели с помощью команды меню Tools►Forward Engineer/Schema Generation (или соответствующей кнопки на панели инструментов) откройте окно Forward Engineer Schema Generation и нажмите кнопку Generate. В появившемся окне Access Connection задайте имя пользователя (User Name) равным Admin, а также с помощью кнопки Browse (первой сверху) задайте полное имя созданной базы данных Склад.mdb. Далее нажмите кнопку Connect и выполните процесс прямого проектирования (Forward Engineer) с наполнением файла базы данных Склад.mdb метаданными согласно созданной физической модели данных. После завершения процесса прямого проектирования с помощью команды меню Database►Database Connection откройте окно Access Connection и разорвите соединение с базой данных Склад.mdb путем нажатия кнопки Disconnect.
3. Запустите СУБД Access, откройте базу данных Склад.mdb и просмотрите структуры полученных таблиц и наличие в них первичных ключей.
4. Убедитесь, что для полей Цена, Количество, КурсВалюты и ШагОкругления установлено свойство «Условие на значение», а для полей Страна, УсловияОплаты, ШагОкругления, Единица_изм, КодВалюты (из таблицы Товар), ДатаЗаказа и СрокПоставки – установлено свойство «Значение по умолчанию», которые соответствуют правилам валидации (Valid) и значениям по умолчанию (Default) в физической модели данных.
5. Убедитесь, что для поля Количество (таблица Заказ) и для поля КурсВалюты (таблица Валюта) установлено свойство Формат. Убедитесь, что в таблице Регион для полей Телефон и Факс установлено свойство «Маска ввода». Эти свойства должны соответствовать аналогичным свойствам в физической модели данных.
6. Откройте окно Схема данных и проанализируйте структуру связей между таблицами. Схема данных должна выглядеть примерно так, как показано на рис. 1.4.
Рис. 1.4. Схема данных базы данных Склад
7. Путем ввода данных в таблицы убедитесь, что таблицы снабжены уникальными индексами, соответствующими альтернативным ключам в логической модели данных (или индексам в физической модели данных).
Раздел IV. Выполнение операции обратного проектирования
1. Закройте СУБД Access, после чего в ERwin закройте текущую модель данных с помощью команды меню File►Close.
2. В ERwin с помощью команды меню Tools►Reverse Engineer запустите мастер выполнения процесса обратного проектирования. На его странице Reverse Engineer – Select Template задайте тип новой модели – Логическая/Физическая, целевую базу данных – Access. На следующей странице Reverse Engineer – Set Options в древовидной структуре Items to Reverse Engineer найдите объект View и отключите его (сбросьте флажок) вместе со всеми подчиненными ему элементами. В появившемся окне Access Connection задайте имя пользователя (User Name) равным Admin, а также с помощью кнопки Browse (первой сверху) задайте полное имя созданной ранее в Access базы данных Основные средства.mdb. Далее нажмите кнопку Connect и выполните процесс обратного проектирования (Reverse Engineer), в результате чего будет создана модель данных, соответствующая системному каталогу базы данных Основные средства.mdb. После завершения процесса обратного проектирования с помощью команды меню Database►Database Connection откройте окно Access Connection и разорвите соединение с базой данных путем нажатия кнопки Disconnect.
3. Убедитесь, что полученная модель данных соответствует схеме данных созданной ранее базы данных Основные средства.mdb.
4. Сохраните модель данных на жестком диске в файле Основные средства.erwin.
Основная
Дополнительная
Бойко В.В., Савинков В.Л. Проектирование баз данных информационных систем. - М.: Финансы и статистика, 1989.