Бесплатный фрагмент - Базы данных. Лабораторный практикум

Введение

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

В учебном пособии представлен лабораторный практикум по дисциплине «Базы данных» и теоретический минимум, необходимый для его выполнения: проектирование баз данных с использованием CASE-средства CA Erwin Data Modeler, физическое создание и управление объектами баз данных на примере Microsoft SQL Server, создание представлений и запросов с использованием структурированного языка SQL, управление базой данных с использованием приложений сред визуального программирования на примере Embarcadero C++ Builder.

Лабораторная работа №0. Установка Microsoft SQL Server и CA Erwin Data Modeler

Тема: Установка «Microsoft SQL Server», «СA ERwin Data Modeler», создание базы данных

Цель: Научиться устанавливать Microsoft SQL Server и СA ERwin Data Modeler, создавать файл БД.

Задание:

— Установить Microsoft SQL Server

— Создать файл базы данных

— Установить СA ERwin Data Modeler

В приведенном ниже описании выполнения лабораторной работы используется сервер Microsoft SQL Server 2008 (English) и СA ERwin Data Modeler 9.6. При выполнении работы по аналогии экранные формы могут несколько отличаться от приведенных.

Описание выполнения работы:

1. Установка сервера.

Вставьте диск с ПО сервера в привод компакт дисков вашего компьютера или запустите дистрибутив, скаченный с официального сайта Microsoft. Компьютер будет проверен на наличие пакета «Microsoft. NET Framework». Если данный пакет не установлен, то появится окно подобное рис 0.1.

В появившемся окне представлено лицензионное соглашение об использовании пакета «Microsoft. NET Framework». Для того чтобы установить пакет необходимо согласиться с соглашением. Включите переключатель «Я прочитал (а) и ПРИНИМАЮ условия лицензионного соглашения» и нажмите кнопку «Установить». После завершения установки появится окно с сообщением о завершении установки.

Появится окно выбора типа установки сервера (Рис.0.2).

Выберите тип установки «New SQL Server stand-alone installation or add to an existing installation». Начнётся установка файлов обеспечивающих установку сервера (Рис.0.3).

После установки вышеперечисленных файлов в окне установки нажмите кнопку «Ok». Появится окно выбора типа лицензии использования, где можно выбрать один из двух видов лицензии:

— Specify a free edition — бесплатная версия сервера, работает 180 дней, после чего требует приобретения коммерческой версии;

— Enter the product key — коммерческая версия, в поле ввода, расположенного ниже необходимо ввести ключ коммерческой лицензии продукта.

После выбора вида лицензии нажмите кнопку «Next» (Далее). Появится окно, предназначенное для ознакомления с лицензией. Прочитайте лицензионное соглашение, включите опцию «I accept the license terms» (Я согласен с условиями лицензии) и нажмите кнопку «Next» (Рис. 0.4).

Появится окно начала установки дополнительных файлов (Рис.0.5).

В окне начала установки дополнительных файлов нажмите кнопку «Install» (Установить). Появится окно определения правил установки дополнительных файлов (Рис. 0.6).

В выше представленном окне нажмите кнопку «Next». Появится окно выбора устанавливаемых компонентов сервера (Рис. 0.7).

Выберите все компоненты сервера и нажмите кнопку «Next». Появится окно настройки устанавливаемого экземпляра сервера (Рис.0.8).

В данном окне опредеяются следующие настройки:

— «Default instance» или «Named instance» — установка экземпляра по умолчанию или поименованного экземпляра;

— «Instance ID» — имя устанавливаемого экземпляра сервера;

— «Instance root directory» — папка на диске, куда будет установлен экземпляр сервера.

Задайте вышеописанные параметры и нажмите кнопку «Next». Появится окно с отчётом о количестве свободного места на диске, куда устанавливается экземпляр сервера (Рис. 0.9)

Нажмите кнопку «Next». Появится окно настройки экземпляра сервера (Рис. 0.10).

Нажмите кнопку «Next». Появится окно создания учётной записи пользователя сервера (Рис. 0.11).

В данном окне оставьте настройки по умолчанию и нажмите кнопку «Ok». Появится окно настройки ядра сервера (Рис. 0.12).

В выше представленном окне выберите смешанный режим подключения (Mixed Mode). Задайте пароль (Enter password) и его подтверждение (Confirm password). Добавьте нового администратора сервера, нажав кнопку «Add Current User» (Добавить текущего пользователя) и нажмите кнопку «Next». Появится окно для определения пользователя управляющего всеми службами сервера (Рис.0.13).

Для добавления пользователя нажмите кнопку «Add Current User», а затем нажмите кнопку «Next». Появится окно настройки сервера отчётов (Рис.0.14).

— данном окне можно выбрать один из трёх вариантов настройки отчёта:

— Install the native mode default configuration — установить настройки по умолчанию;

— Install the SharePoint integrated mode configuration — настройка отчётов для интеграции их в систему SharePoint.

— Install, but do not configure the report server — установить но не настраивать сервер отчётов.

Выберите первый вариант настройки и нажмите кнопку «Next». Появится окно настройки отчётов об ошибках в работе сервера (Рис.0.15).

Если в окне настройки отчёта об ошибках включить все опции, то в случае ошибки вся информация об ошибке будет отправлена на сервер разработчика (Microsoft) для анализа. Если вам это необходимо то можете включить все опции, если нет, то не включайте. Нажмите кнопку «Next». Появится окно, отображающее правила установки (Рис. 0.16).

В выше представленном окне нажмите кнопку «Next». Появится окно с полным отчётом по установке сервера (Рис.0.17).

Для начала установки нажмите кнопку «Install» (Установить). Начнётся процесс установки сервера. Появится окно, отображающее ход установки сервера (Рис. 0.18).

После окончания процесса установки нажмите кнопку «Next». Появится окно отчёта по результатам установки (Рис.0.19).

Для завершения установки сервера в данном окне нажмите кнопку «Close» (Закрыть).

2. Создание базы данных

Создание любой базы данных (БД) начинается с создания файла данных. Рассмотрим этот процесс в «Microsoft SQL Server» на примере создания простой БД по учёту успеваемости студентов. Для начала необходимо запустить среду разработки «SQL Server Management Studio» (Рис. 0.20).

В этом окне необходимо нажать кнопку «Connect» (Соединить).

Замечание: Если при установке «Microsoft SQL Server» был задан логин и пароль подключения к серверу, то перед нажатием кнопки «Connect», в выпадающем списке «Authentication» нужно выбрать «SQL Server Authentication» (Проверка подлинности SQL Server), а затем необходимо ввести заданные при установке логин и пароль.

После нажатия кнопки «Connect» появится окно среды «SQL Server Management Studio» (Рис. 0.21).

Данное окно имеет следующую структуру:

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

— Панель инструментов — содержит кнопки для выполнения наиболее часто производимых операций. Внешний вид данной панели зависит от выполняемой операции.

— Панель «Object Explorer» — обозреватель объектов. Обозреватель объектов — это панель с древовидной структурой, отображающая все объекты сервера, а также позволяющая производить различные операции, как с самим сервером, так и с БД. Обозреватель объектов является основным инструментом для разработки БД.

— Рабочая область. В рабочей области производятся все действия с БД, а также отображается её содержимое.

Замечание: В обозревателе объектов сами объекты находятся в папках. Чтобы открыть папку необходимо щёлкнуть по знаку «+» слева от изображения папки.

Теперь перейдём непосредственно к созданию файла данных. Для этого в обозревателе объектов щёлкните ПКМ на папке «Databases» (Базы данных) (Рис. 0.21) и в появившемся меню выберите пункт «New Database» (Новая база данных). Появится окно настроек параметров файла данных новой БД «New Database» (Рис. 0.22). В левой части окна настроек имеется список «Select a page». Этот список позволяет переключаться между группами настроек.

Для начала настроим основные настройки «General». Для выбора основных настроек нужно просто щёлкнуть мышью по пункту «General» в списке «Select a page». В правой части окна «New Database» появятся основные настройки (Рис. 0.22)

Рассмотрим их более подробно. Верхней части окна расположено два параметра: «Database name» (Имя БД) и «Owner» (Владелец). Зададим параметр «Database name» равным «Students». Параметр «Owner» оставим без изменений.

Под вышеприведёнными параметрами в виде таблицы располагаются настройки файла данных и журнала транзакций. Таблица имеет следующие столбцы:

— Logical Name — логическое имя файла данных и журнала транзакций. По этим именам будет происходить обращение к вышеприведённым файлам в БД. Можно заметить, что файл данных имеет то же имя что и БД, а имя файла журнала транзакций составлено из имени БД и суффикса «_log».

— File Type — тип файла. Этот параметр показывает, является ли файл файлом данных или журналом транзакций.

— Filegroup — группа файлов, показывает к какой группе файлов относится файл. Группы файлов настраиваются в группе настроек «Filegroups».

— Initial Size (MB) — начальный размер файла данных и журнала транзакций в мегабайтах.

— Autogrowth — автоувеличение размера файла. Как только файл заполняется информацией его размер автоматически увеличивается на величину, указанную в параметре «Autogrowth». Увеличение можно задавать как в мегабайтах так и в процентах. Здесь же можно задать максимальный размер файлов. Для изменения этого параметра надо нажать кнопку «…». В нашем случае размер файлов не ограничен. Файл данных увеличивается на 1 мегабайт, а файл журнала транзакций на 10%.

— Path — путь к папке, где хранятся файлы. Для изменения этого параметра также надо нажать кнопку «…».

— File Name — имена файлов. По умолчанию имена файлов аналогичны логическим именам. Однако файл данных имеет расширение «mdf», а файл журнала транзакций — расширение «ldf».

Замечание: Для добавления новых файлов данных или журналов транзакций используется кнопка «Add», а для удаления кнопка «Remove». В нашем случае мы оставим все основные настройки без изменений.

Теперь перейдём к другим второстепенным настройкам файла данных. Для доступа к этим настройкам необходимо щёлкнуть мышью по пункту «Options» в списке «Select a page». Появится следующее окно (Рис. 0.23).

В правой части окна мы видим следующие настройки:

— Collation — этот параметр отвечает за обработку текстовых строк, их сравнение, текстовый поиск и т. д. Рекомендуется оставить его как « <server default>». При этом данный параметр будет равен значению, заданному на вкладке «Collation», при установке сервера.

— Recovery Model — модель восстановления. Данный параметр отвечает за информацию, предназначенную для восстановления БД, хранящуюся в файле транзакций. Чем полнее модель восстановления, тем больше вероятность восстановления данных при сбое системы или ошибках пользователей, но и больше размер файла журнала транзакций. При наличии места на диске, рекомендуется оставить этот параметр в значении «Full».

— Compatibility level — уровень совместимости, определяет совместимость файла данных с более ранними версиями сервера. Если планируется перенос данных на другую, более раннюю версию сервера, то её необходимо указать в этом параметре.

— Other options — второстепенные параметры. Данные параметры являются необязательными для изменения.

В нашем случае все параметры в разделе «Options», рекомендуется оставить как на рис. 0.23.

Наконец, рассмотрим последнюю группу настроек «Filegroups». Данная группа настроек отвечает за группы файлов. Для её отображения в списке «Select a page» необходимо щёлкнуть мышью по пункту «Filegroups». Отобразятся настройки групп файлов (Рис. 0.24).

Группы файлов представлены в таблице «Rows» в правой части окна (Рис. 0.24). Данная таблица имеет следующие столбцы:

— Name — имя группы файлов.

— Files — количество файлов входящих в группу.

— Read only — файлы в группе будут только для чтения. То есть, их можно только просматривать, но нельзя изменять.

— Default — группа по умолчанию. Все новые файлы данных будут входить в эту группу.

Замечание: Как и в случае с файлами данных, для добавления новых групп используется кнопка «Add», а для удаления кнопка «Remove».

В рассматриваемой БД нет необходимости добавлять новые группы файлов. Поэтому оставим группу настроек «Filegroups» без изменений.

На этом мы заканчиваем настройку свойств наших файлов. Для принятия всех настроек и создание фала данных и журнала транзакций нашей БД в окне «New Database» нажмём кнопку «Ok».

Произойдёт возврат в окно среду разработки «SQL Server Management Studio». На панели обозревателя объектов в папке «Databases» появиться новая БД «Students» (Рис. 0.25).

Замечание: Для переименования БД необходимо в обозревателе объектов щёлкнуть по ней ПКМ и в появившемся меню выбрать пункт «Rename». Для удаления в это же меню выбираем пункт «Delete», для обновления — пункт «Refresh», а для изменения свойств описанных выше — пункт «Properties».

3. Установка СA Erwin Data Modeler.

Установка СA Erwin Data Modeler не представляет из себя сложную задачу. Для этого достаточно запустить установочный файл с компакт-диска или скаченный с официального сайта CA ERwin и следовать стандартным инструкциям мастера установки (рис. 0.26—0.30). При первом запуске программы необходимо указать имеющуюся лицензию (рис. 0.30—0.32). Лицензию можно приобрести или использовать условно бесплатную с ограничением на количество сущностей в проекте (не более 25).

Лабораторная работа №1. Проектирование БД

Тема: Проектирование базы данных

Цель: Научиться осуществлять инфологическое и физическое моделирование базы данных выбранной предметной области.

Задание:

— В среде моделирования баз данных CA Erwin Data Modeler разработать проект базы данных на инфологическом (ER-модель) и физическом уровнях.

— В среде Microsoft SQL Server создать пустую базу данных (выполнение этого задания не требуется при условии выполнения задания №2 лабораторной работы №0).

— Произвести экспорт созданной физической модели базы данных на сервер Microsoft SQL Server.

В приведенном ниже описании выполнения этой и последующих лабораторных работ используется сервер Microsoft SQL Server 2014 и среда моделирования CA Erwin Data Modeler 9.6. При выполнении работы по аналогии экранные формы могут несколько отличаться от приведенных.

Описание выполнения работы:

1. Основные понятия ER-моделирования: Сущности, атрибуты, связи. Нормализация базы данных

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

Такими моделями являются модели» сущность-связь» (Entity- Relationship, ER-модели). Наибольшее распространение построения ER-моделей получила методология IDEF1X. Ниже будет рассмотрено построение моделей «сущность-связь», ориентируясь на продукт CA ERwin Data Modeler 9.6 (Erwin) и методологию IDEF1X.

ERwin имеет два уровня представления модели:

— Логический уровень, соответствующий инфологическому этапу проектирования и не привязанный к конкретной СУБД. Модели логического уровня оперируют с понятиями сущностей, атрибутов и связей, которые на этом уровне именуются на естественном языке.

— Физический уровень — это отображение логической модели на модель данных конкретной СУБД. Одной логической модели может соответствовать несколько физических моделей. Erwin позволяет автоматизировать отображение логической модели на физическую.

Модель «сущность-связь» строится в виде диаграммы, основными компонентами которой являются сущности (Entity) и связи (Relationship).

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

Сущности обладают определенными свойствами — атрибутами. Атрибут — это абстракция одной характеристики объекта или явления реального мира. Каждый атрибут должен иметь имя — существительное в единственном числе, и получать значение из некоторого множества допустимых значений — домена.

У каждой сущности должен быть выделен идентификатор, или первичный ключ. Первичный ключ — это один или несколько атрибутов, однозначно определяющих каждый экземпляр сущности. Если первичный ключ состоит из нескольких атрибутов, то он называется составным. Первичный ключ не должен изменяться и принимать неопределенное значение (NULL). Ключ должен быть компактным, т. е. не содержать слишком много атрибутов. Сущность может иметь несколько потенциальных ключей, из которых должен быть выбран первичный ключ. Иногда используют искусственный первичный ключ (некоторый номер или код), когда ключ содержит слишком много атрибутов. Используется также понятие внешнего ключа. Внешний ключ — это первичный ключ другой сущности, который мигрирует (копируется) в сущность и служит для связи сущностей.

Пример сущности показан на рис. 1.1.

Каждая сущность должна сопровождаться описанием. Описание сущности должно объяснять ее смысл. Описание должно быть ясным, полным, непротиворечивым и понятным.

Связи между объектами реального мира отражаются в виде связей между сущностями, описывающими их отношения. Отношение — это ассоциация или «связь» между двумя сущностями. Отношение представляется в модели линией, соединяющей две сущности, и именем отношения — глагольной конструкцией, которая описывает, как две сущности зависят друг от друга. Имена сущностей, соединенные именем отношения, должны образовывать осмысленную фразу, описывающую правило отношения. Например, Сотрудник <Работает в> Отделе. В примере имя отношения показано в угловых скобках. Отношения двунаправлены, поэтому должны иметь имена в каждом направлении.

Отношение обладает следующими свойствами:

— степень,

— направленность,

— тип,

— мощность,

— обязательность.

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

Направленность отношения указывает на исходную сущность в отношении. Сущность, из которой отношение исходит, называется родительской сущностью. Сущность, в которой отношение заканчивается, называется подчиненной (дочерней) сущностью. Направленность отношения определяется взаимосвязью между сущностями и зависит от типа и мощности отношения. В отношении между независимой и зависимой сущностями отношение исходит из независимой сущности и заканчивается в зависимой сущности. Если обе сущности независимые, отношение симметрично. В отношении один-ко-многим родительской является сущность, входящая в отношение однократно. Отношения многие-ко-многим симметричны. Ключ родительской сущности мигрирует (повторяется) в дочерней сущности. Такой мигрировавший ключ в дочерней сущности называется внешним ключом. Внешний ключ в зависимости от типа связи может стать частью составного ключа дочерней сущности или неключевым атрибутом дочерней сущности. С помощью внешнего ключа экземпляр дочерней сущности ссылается на соответствующий экземпляр родительской сущности.

В ERwin отношение между двумя сущностями, или сущности самой с собой, может принадлежать к одному из следующих типов:

— идентифицирующее отношение,

— неидентифицирующее отношение,

— типизирующее отношение,

— отношение многие-ко-многим,

— рекурсивное отношение.

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

Идентифицирующим является отношение между двумя сущностями, в котором каждый экземпляр подчиненной сущности идентифицируется значениями атрибутов родительской сущности. Это означает, что экземпляр подчиненной сущности зависит от родительской сущности и не может существовать без экземпляра родительской сущности. В идентифицирующем от-ношении единственный экземпляр родительской сущности связан с множеством экземпляров подчиненной. Атрибуты первичного ключа родительской сущности мигрируют в атрибуты подчиненной, чтобы стать там атрибутами первичного ключа. На рис. 1.2 представлено идентифицирующее отношение между сущностями Сотрудник и Телефон. В сущности Сотрудник использован первичный ключ Табельный номер, который мигрировал в дочернюю сущность Телефон и стал там первичным ключом.

Неидентифицирующим называется отношение между двумя сущностями, в котором каждый экземпляр подчиненной сущности не зависит от значений атрибутов родительской сущности. Это означает, что экземпляр подчиненной сущности не зависит от родительской сущности и может существовать без экземпляра родительской сущности. В неидентифицирующем отношении единственный экземпляр родительской сущности связан с множе-ством экземпляров подчиненной. Атрибуты первичного ключа родительской сущности мигрируют в подчиненную, чтобы стать там неключевыми атрибутами. На рис. 1.3 показано неидентифицирующее отношение между сущностями Должность и Сотрудник. Каждое из этих отношений имеет собственный первичный ключ. Первичный ключ родительской сущности Должность мигрировал в подчиненную сущность Сотрудник и стал там неключевым атрибутом.

На примере рис. 1.3 также можно рассмотреть понятие обязательности отношения. Неидентифицирующее отношение называется обязательным (No Nulls), если все экземпляры дочерней сущности должны участвовать в отношении. Неидентифицирующее отношение называется необязательным (Nulls Allowed), если некоторые экземпляры дочерней сущности могут не участвовать в отношении. Сотрудник организации в любом случае занимает какую-либо должность, поэтому на рис. 1.3 отношение обязательное должен принадлежать одной из учебных групп. На рис. 1.4 показан пример необязательно отношения в предположении, что сотрудник может не относится к какому-либо отделу.

Типизирующими называются отношения между родительской и одной или более подчиненными сущностями, когда сущности разделяют общие характеристики. Типизирующие отношения используются в том случае, когда экземпляр родительской сущности определяет различные наборы атрибутов в подчиненных сущностях. Например, имеются различные категории сотрудников, отличающиеся только небольшим количеством атрибутов (рис. 1.5). Для каждой категории необходимо указать дискриминатор — атрибут родительской сущности, показывающий, как отличить одну категориальную сущность от другой. Данным дискриминатором в описанный примере является атрибут Тип. На рис. 1.5 показана полная категория, т. е. каждый экземпляр сущности сотрудник относится к одной из перечисленных категорий. В случае неполной категории, когда существуют экземпляры родительской сущности, не имеющие соответствующих экземпляров в дочерних сущностях значок категории содержит одну горизонтальную линию.