Размещено на http://www.allbest.ru/

110

МИНОБРНАУКИ РФ

федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Ивановский государственный энергетический университет имени В.И. Ленина»

И.Д. Ратманова, Е. Е. Булатова

Учебное пособие

НА ТЕМУ «ПРОЕКТИРОВАНИЕ И РАЗРАБОТКА БАЗЫ ДАННЫХ»

2013УДК 004.6

Р25 Ратманова И. Д., Булатова Е. Е. Проектирование и разработка баз данных в СУБД Microsoft SQL Server: учебное пособие / ФГБОУВПО «Ивановский государственный энергетический университет имени В.И. Ленина».? Иваново, 2013.? 112 с.

ISBN

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

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

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

Печатается по решению редакционно-издательского совета ФГБОУВПО «Ивановский государственный энергетический университет имени В.И. Ленина».

Научный редактор - Е. Р. Пантелеев

Рецензент кафедра программного обеспечения компьютерных систем

ФГБОУВПО «Ивановский государственный энергетический университет имени В.И. Ленина»

ISBN © И.Д. Ратманова, Е.Е. Булатова, 2013

Предисловие

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

Целью данного учебного пособия является знакомство с организацией базы данных на основе реляционной СУБД. В качестве СУБД используется сервер Microsoft SQL Server 2008 R2.

Поддерживаемый сервером язык запросов Transact-SQL является реализацией стандарта SQL-92 с множественными расширениями (в том числе дополнительный синтаксис для хранимых процедур и поддержка транзакций). Основными интерфейсами взаимодействия приложений с сервером являются ODBC и JDBC (для приложений под управлением Java). Версия SQL Server 2008 обеспечивает возможность подключения пользователей через веб-сервисы, использующие протокол SOAP, что позволяет клиентским программам, не предназначенным для Windows, кроссплатформенно соединяться с сервером. Кроме того, в SQL Server 2008 встроена поддержка .NET Framework, благодаря этому хранимые процедуры могут быть написаны на любом языке платформы .NET, используя полный набор библиотек, доступных для .NET Framework.

Используемая в лабораторном практикуме версия Microsoft SQL Server Express Edition является бесплатно распространяемой версией SQL Server. Данная версия имеет некоторые технические ограничения (1 поддерживаемый процессор, 1 Гб адресуемой памяти и предел в 4 Гб для размера базы), не препятствующие, однако, использованию сервера в учебном процессе и в небольших программных приложениях. Фактически, это полноценный MS SQL Server, включая все его компоненты программирования, поддержку национальных алфавитов, Unicode и новых типов данных, в том числе XML-спецификации. Для администрирования сервера используется утилита с графическим интерфейсом Microsoft SQL Server Management Studio Express, которая также доступна для бесплатного скачивания с сайта корпорации Microsoft (http://www.microsoft.com).

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

Ниже приведено содержание лабораторного практикума по выполнению курсовой работы.

Тема 1 (4 часа). Анализ предметной области. Анализ полученного задания; формулировка требований к функциям (задачам) и составу потенциальных пользователи создаваемой информационной системы и построение диаграммы вариантов использования в нотации UML. Концептуальное моделирование предметной области и построение диаграммы классов в нотации UML. По результатам работы в итоговый отчет помещаются текст задания, название информационной системы, диаграммы.

Тема 2 (4 часа). Проектирование базы данных. Проектирование базы данных с оформлением модели на стандарте IDEF1X. По результатам работы в отчет помещаются модели (логическая и физическая) базы данных.

Тема 3 (4 часа). Создание базы данных. Создание базы данных на сервере. Добавление ограничений для поддержания целостности (см. гл. 3). По результатам работы в итоговый отчет помещается сценарий создания базы данных.

Тема 4 (4 часа) Загрузка информации в базу данных. Заполнение базы данными в графической среде Management Studio. Вставка, удаление и модификация данных средствами языка SQL (см. гл. 4). По результатам работы в итоговый отчет помещается содержимое заполненных таблиц и тексты запросов.

Тема 5 (4 часа). Выборка данных. Формирование десяти запросов на выборку данных, охватывающих основные возможности языка SQL. Тестирование получившихся запросов. По результатам работы в итоговый отчет помещаются тексты запросов с результатами выполнения (копии экрана).

Тема 6 (4 часа). Реализация бизнес-логики приложения в теле сервера. Создание триггеров и хранимых процедур, охватывающих бизнес-логику приложения и поддержание целостности базы данных. По результатам работы в итоговый отчет помещаются коды создания и вызова хранимых процедур и триггеров с кратким описанием.

Тема 7 (2 часа). Разработка клиентского приложения для ведения созданной базы данных. Знакомство с основными компонентами программного интерфейса СУБД. Разработка интерфейсов загрузки, просмотра, поиска и постобработки накопленной информации. По результатам работы в итоговый отчет помещается краткое руководство пользователя с копиями основных экранов.

Для выполнения лабораторного практикума необходимо установить MS SQL Server 2008 R2 Express, Visual Studio 2008 и получить задание (см. прил. А).

Форма отчетности. Курсовая работа должна быть представлена в виде итогового отчета по лабораторному практикуму в электронном и печатном виде. К нему должны прилагаться файлы базы данных и клиентского приложения.

Авторы выражают благодарность инженеру-программисту отдела геоинформационных технологий ИГЭУ А.Б. Гадалову за оказанную помощь в подготовке учебного пособия.

1. Анализ предметной области

1.1 Анализ требований. Диаграмма вариантов использования

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

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

Для документирования требований используются диаграммы языка UML.

UML (англ. Unified Modeling Language - унифицированный язык моделирования) - язык графического моделирования в области разработки программного обеспечения. UML не является языком программирования, но на основании UML-моделей возможна генерация кода.

Сущности - это абстракции, являющиеся основными элементами модели.

Отношения связывают сущности.

Совокупности сущностей и отношений группируются в диаграммы.

Диаграмма вариантов использования

Диаграмма вариантов использования (Use Case Diagram, диаграмма прецедентов) - иллюстрирует, какие актеры в каких вариантах использования фигурируют. Таким образом, диаграмма прецедентов полностью описывает ожидаемые функциональные возможности системы (требования).

Правила:

1. Не моделировать связи между актерами. По определению актеры находятся вне системы и связи между ними не относятся к сфере компетенции диаграммы.

2. Не соединять связью непосредственно два варианта использования. Диаграммы данного типа не описывают последовательность выполнения действий или потоки данных.

3. Каждый вариант использования должен быть инициирован актером.

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

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

§ Для более легкого прочтения диаграммы

§ При их большой важности для проекта

§ При их частой повторяемости

§ Если этот вариант использования является единственным для определенного актера.

Рис. 1.1. Диаграмма вариантов использования

1.2 Концептуальное моделирование предметной области

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

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

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

На языке UML концептуальная модель представлена в виде статической структурной Диаграммы классов (Class Diagram), на которой не определены никакие операции. Диаграмма классов определяет объекты системы и статические связи между ними.

Мощность отношений (множественность) - число связей между каждым экземпляром класса (объектом) в начале линии с экземпляром класса в её конце. Различают следующие случаи:

Таблица Мощность отношений (множественность)

Нотация	Описание	Пример
0..1	Ноль или один экземпляр	Поставка упаковывается одним способом или не упаковывается
1 / 1..1	Обязательно один экземпляр	Каждая деталь изготовлена из одного сплава
0..N / 0..*	Ноль или более экземпляров	Из каждого сплава изготовлены ноль, одна или более деталей
1..N / 1..*	Один или более экземпляров	В каждом чеке одна или более строк

Правило: Для проверки читайте связь со слова «Каждый...». Пример: «Каждая поставка осуществляется одним поставщиком».

2. Проектирование базы данных

2.1 Логическая модель базы данных

Концептуальная модель базы данных (Логическая модель базы данных) описывает объекты предметной области, их атрибуты и взаимосвязи между ними в том объеме, в котором они подлежат непосредственному хранению в базе данных системы (рис. 2.1).

Рис. 2.1. Концептуальная модель базы данных

Методология IDEF1х - один из подходов к семантическому моделированию данных, основанный на концепции "сущность - связь" (Entity - Relationship). Информационная модель, построенная с помощью IDEF1х_методологии, отображает логическую структуру информации об объектах системы, т.е. является логической схемой базы данных.

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

Зависимая сущность представляет данные, которые зависят от других сущностей в системе. Например, строка документа не может существовать без самого документа. Зависимая сущность всегда имеет отношения с другими сущностями. Изображается в виде прямоугольника с закругленными краями.

2.2 Физическая модель базы данных

Физическая модель базы данных содержит все детали, необходимые конкретной СУБД для создания базы (наименования таблиц и столбцов, типы данных, определения первичных и внешних ключей и т.п.).

Физическая модель строится на основе логической с учетом ограничений, накладываемых возможностями выбранной СУБД (в нашем случае - MS SQL Server 2008 R2):

Имена

Имена сущностей и полей (таблиц и столбцов) должны быть записаны латинскими буквами, без пробелов, начиная с буквы.

Типы данных

Для каждого поля необходимо определить тип данных, которые в нем содержатся. В таблице

Связи

Реляционная модель данных требует разрешения связей «многие ко многим» и иерархии наследования и допускает их только на концептуальной модели.

На физическом уровне эти связи должны быть преобразованы.

Приведенные три варианта физической модели различаются способами реализации наследования:

§ миграция первичного ключа и неключевых атрибутов в иерархии от потомков к предку (рис. 2.2);

§ миграция первичного ключа и неключевых атрибутов в иерархии от предка к потомкам (рис. 2.3).

§ замена иерархии наследования идентифицирующими связями (рис. 2.4);

Преобразование связи «многие ко многим» (таблицы «Поставка» и «Способ доставки») включает создание новой таблицы развязки и двух новых связей «один ко многим» от старых таблиц к новой.

Таблица 2.2. Основные типы данных в СУБД MS SQL Server

Тип данных	Описание
bigint	Целочисленные данные от -263 до 263-1
int	Целые числа от -214748364 до 2147483647
smallint	Целые числа от -32768 до 32767
tinyint	Целые числа от 0 до 255
decimal или numeric	Являются синонимами. Заданная точность и масштаб от -1038-1 до 1038-1
money	Количество денежных единиц от -263 до 263 с точностью до четырех десятичных позиций
smallmoney	Денежные единицы от -214748,3648 до 214748,3647
float	Приближенные числовые данные от -1,79Е+308 до 1,79Е+308
datetme	Данные о дае и (или) времени, которые относятся к периоду с 1 января 1753 года по 31 декабря 9999 года,определяемые с точностью до трех сотых секунды
smalldatetime	Данные о дате и (или) времени, которые относятся к периоду с 1 января 1900 года по 6 июня 2079 года, опрделяемые с точностью до одной минуты
cursor	Указатель на курсор
Timestamp / rowversion	Специальное значение, которое является уникальным в пределах данной базы данных. Устанавливается автоматически
uniqueidentifier	Специальный глобально уникальный идентификатор (GUID). Уникальность любого идентификатора GUID в пространстве и времени является гарантированой.
char (n)	Символьные данные фиксированной длины. Значения данных с длиной короче заданной заполняются пробелами до указанной длины. Максимальное заанное значение длины может составлять 8000 символов.
varchar (n)	Символьные данные переменной длины. Значения данных с длиной короче заданной не дополняются пробелами. Максимальное заданное значение длины может составлять 8000 символов, но для обозначения длины можно использовать ключевое слово max, что позволяет определять столбцы с символьными данными объемом до 231 байтов.
text	Устаревший тип данных, вместо него следует использовать тип varchar (max).
nchar (n)	Символьные данные в кодировке Unicode фиксированной длины. Максимальное заданное значение длины может составлять 4000 символов
nvarchar (n)	Символьные данные в кодировке Unicode переменной длины. Максимальное заданное значение длины может составлять 4000 символов, но для обозначения длины можно использовать ключевое слово max, что позволяет определять столбцы с символьными данными объемов до 231 байт.
ntext	Устаревший тип данных, вместо него следует использовать тип данных nvarchar (max).
binary	Двоичные данные фиксированной длины с максимальой длиной 800 байт.
varbinary	Двоичные данные переменной длины с максимальной указанной длиной 8000 байт, но для обозначения длины можно использовать ключевое слово max, что позволяет определять столбцы типа LOB, имеющие объем до 231 байт
image	Устаревший тип данных, вместо него следует использовать тип данных varbinary (max).
table	Данные типа таблицы, предназначены для использования в работе с результирующими наборами. Как правило, они передаются из пользовательских функций. Применение данных типа table в определениях таблиц не допускается.
sql_variant	Данные различных типов.
xml	Определяет символьное поле как содержащее данные XML.

Рис. 2.2. Физическая модель базы данных: реализация наследования через миграцию потомков в предка



Рис. 2.3. Физическая модель базы данных: реализация наследования через миграцию предка в потомков

Рис. 2.4. Физическая модель базы данных: реализация наследования через замену иерархии идентифицирующими связями

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

В состав Microsoft SQL Server 2008 R2 входит графическая среда SQL Server Management Studio, являющаяся одним из основных программных средств администрирования СУБД SQL Server. В этой программе предусмотрен целый ряд функциональных средств управления сервером, в которых применяется относительно простой графический интерфейс.

3.1 Подключение к серверу MS SQL Server 2008 R2

При запуске Management Studio на экране появляется окно подключения к серверу (рис. 3.1).

Поле «Тип сервера» предназначено для выбора одной из нескольких подсистем SQL Server, в которую должен войти пользователь: сам сервер, службы Analysis Services, Report Services и т. п. Для подключения к серверу следует выбрать вариант «Компонент Database Engine».

Рис. 3.1. Окно подключения к MS SQL Server в среде Management Studio

В поле «Имя сервера» указывается имя сервера, к которому должно быть выполнено подключение. В выпадающем списке поля перечисляются сервера, которые анонсируют свои службы в сети. Сервера могут быть локальными или сетевыми.

В поле «Проверка подлинности» следует выбрать вариант проверки прав пользователя на доступ к серверу. В случае выбора проверки подлинности Windows права берутся из учетной записи пользователя Windows. Проверка подлинности SQL Server - вариант входа в систему с использованием имен пользователей и паролей, которые являются локальными по отношению к SQL Server.

По кнопке «Соединить» происходит подключение к серверу и открывается основное окно среды Management Studio (рис. 3.2). В левой части окна расположен «Обозреватель объектов», предназначенный для гибкой навигации по функциональным элементам сервера («Базы данных», «Таблицы», «Безопасность» и т.д.). В ветке «Базы данных» перечисляются все базы, зарегистрированные на текущем сервере. Эта ветка может быть развернута дополнительно вплоть до получения списков таблиц и их атрибутов.

Рис. 3.2. Основное окно среды Management Studio

3.2 Создание новой базы данных на сервере

Создание новой базы данных начинается с создания физических файлов, в которых будет храниться база. Для этого в «Обозревателе объектов» в контекстном меню ветки «Базы данных» следует выбрать пункт «Создать базу данных».

В появившемся диалоговом окне (рис. 3.3) в поле «Имя базы данных» вводится имя создаваемой базы. SQL Server для хранения базы данных создает 2 файла - основной с расширением mdf, в котором хранятся непосредственно данные, и файл журнала транзакций с расширением ldf - лог-файл, используемый для откатов в случае сбоев в базе данных.

После нажатия кнопки «OK» в «Обозревателе объектов» в ветке «Базы данных» появится созданная база. Если ее развернуть, то появится перечень видов объектов этой базы: диаграммы, таблицы, представления и т. п. Изначально все они пустые или содержат системные объекты.

Рис. 3.3. Окно создания новой базы данных

3.3 Создание новой таблицы на сервере

В «Обозревателе объектов» в контекстном меню «Таблицы» базы данных следует выбрать пункт «Создать таблицу». В верхней части появившегося окна (рис. 3.4) задаются столбцы (поля) таблицы: имя столбца, его тип (табл. 2.2) и допустимость пустых значений (NULL) в этом столбце. При именовании столбцов рекомендуется пользоваться латинским алфавитом и избегать пробелов. Для указания первичного ключа в контекстном меню соответствующего поля выбирается пункт «Задать первичный ключ»; при этом в столбце «Разрешить значения null» для этого поля автоматически снимается метка (галочка), поскольку уникальный идентификатор должен быть задан в каждой строке таблицы.

Рис. 3.4. Создание полей таблицы базы данных

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

Рис. 3.5. Окно выбора имени таблицы базы данных

Для редактирования структуры ранее созданной таблицы (добавление / удаление полей, изменение типов данных, установка значений по умолчанию и т. п.) используется пункт «Проект» контекстного меню этой таблицы.

Автоинкрементное поле

MS SQL Server позволяет создавать автоинкрементные поля (счетчики); сервер сам будет задавать последовательные значения этого поля в каждой новой строке таблицы (1, 2, 3…).

Для назначения полю свойства автоинкрементности на закладке «Свойства столбцов» внизу окна создания полей таблицы выбирается раздел «Спецификация идентифицирующего столбца» (рис. 3.6).

Рис. 3.6. Создание автоинкрементного поля

Здесь для строки «Является идентифицирующим столбцом» требуется установить значение «Да». При этом автоматически заполнятся две следующие строки - «Начальное значение» и «Приращение», по умолчанию в обоих появится единица. Это значит, что значение первичного ключа в таблице начнется с единицы и будет увеличиваться дальше с шагом 1. Можно задать и другие значения начального значения и приращения счетчика.

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

3.4 Создание связей

Связи между таблицами в MS SQL Server задаются в виде ссылок, т.е. внешних ключей FOREIGN KEY.

Рассмотрим установление связи в среде Management Studio между таблицами «Деталь» и «Материал». Для этого необходимо открыть окно редактирования структуры таблицы «Деталь», с помощью пункта «Проект» контекстного меню этой таблицы.

Далее в контекстном меню открывшегося окна редактирования следует выбрать пункт «Отношения» или же воспользоваться одноименной кнопкой на панели инструментов. На экране появится окно «Отношения внешнего ключа», изначально пустое. Для добавления нового внешнего ключа следует нажать кнопку «Добавить» в нижнем правом углу окна. Далее в появившемся шаблоне настройки внешнего ключа (рис. 3.7) необходимо выделить строку «Спецификация таблиц и столбцов» и нажать на кнопку с многоточием в этой строке справа. На экране появится окно настройки связи между таблицами (рис. 3.8). Здесь в разделе «Имя отношения» можно задать название создаваемой связи или же оставить название по умолчанию.



Рис. 3.7. Окно добавления / удаления отношений между таблицами

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

Рис. 3.8. Окно настройки отношения между таблицами

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

3.5 Создание диаграммы базы данных

Среда Management Studio предоставляет возможность быстрого построения диаграммы созданной базы данных (рис. 3.9).

Рис. 3.9. Диаграмма базы данных в Management Studio

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

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

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

3.6 Ограничения целостности базы данных

СУБД MS SQL Server поддерживает три вида ограничений целостности базы данных: значения по умолчанию, проверки вводимых данных и ограничения ссылочной целостности.

Значения по умолчанию DEFAULT

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

Для установки значения по умолчанию для полей некоторого столбца таблицы заполняется свойство «Значение или привязка по умолчанию» в свойствах этого столбца (рис. 3.6).

В качестве значения по умолчанию может выступать текстовая или строковая константа либо системная функция SQL Server (например, GETDATE () - текущая дата, USER() - текущий пользователь и т. п.).

Проверки CHECK

Ограничения CHECK задают условия проверки значений одного или нескольких полей таблицы (включая проверку сочетаний значений полей таблицы); при этом значения берутся из одной и той же вставляемой или обновляемой строки.

Создание новой проверки осуществляется по команде «Проверочные ограничения» контекстного меню редактора структуры таблицы. В открывшемся окне следует создать новое ограничение, воспользовавшись кнопкой «Добавить», и записать условие проверки в строке «Выражение» (рис. 3.10).

Рис. 3.10. Окно создания проверочных ограничений CHECK

Ограничения ссылочной целостности: правила удаления

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

Подобное поведение можно изменить, выбрав один из трех доступных вариантов действий для зависимых таблиц:

§ каскадное удаление/обновление;

§ установка пустого (NULL) значения;

§ установка значения по умолчанию.

Настройка осуществляется в окне «Отношения внешнего ключа» (рис. 3.7) зависимой таблицы, в разделе «Спецификация INSERT и UPDATE», правило изменения / удаления.

3.7 Сценарий создания базы данных

За всеми действиями по созданию и редактированию баз данных в графическом интерфейсе Management Studio стоят команды языка SQL. Для получения сценария (скрипта) создания базы данных используется пункт «Задачи - Сформировать сценарии» контекстного меню базы в «Обозревателе объектов». При этом запустится «Мастер формирования сценариев», где шаг за шагом выполняются настройки получаемого сценария. Фрагмент сценария базы данных оптового заводского склада приведен на рис. 3.11.

Рис. 3.11. Фрагмент сценария создания базы данных

Заполнение базы данных

3.8 Загрузка информации в базу данных средствами Management Studio

Среда Management Studio предоставляет возможность заполнения базы данных средствами графического интерфейса. Для этого следует воспользоваться командой «Открыть таблицу» контекстного меню таблицы в «Обозревателе объектов».



Рис. 4.1. Переход в режим загрузки данных в таблицу

При этом откроется окно редактирования содержимого таблицы, содержащее одну пустую строку, если ранее в таблицу не записывались данные (рис. 4.2) Для ввода данных в таблицу необходимо заполнить ячейки пустой строки и нажать клавишу Enter. При этом будет создана новая пустая строка, предназначенная для ввода новых данных.

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

Рис. 4.2. Заполнение таблицы данными

Поэтому по завершении заполнения таблицы данными необходимо нажать на кнопку «Выполнить код SQL» на панели инструментов (кнопка со знаком восклицания) для сохранения произведенных изменений. Следует отметить, что только после подтверждения транзакции внесенные данные окончательно запишутся в базу.

3.9 Ведение базы данных средствами SQL

Написание и выполнение запросов в среде Management Studio

В среде Management Studio писать запросы можно либо в специальном окне запросов, либо в области SQL-кода некоторой таблицы базы данных. Второй способ предпочтителен, если создаваемые запросы имеют отношение к одной и той же таблице. Чтобы открыть область SQL-кода таблицы необходимо сначала открыть эту таблицу на редактирование (см. рис. 4.1), затем в контекстном меню окна редактирования таблицы выбрать пункт Область / SQL-код, либо воспользоваться кнопкой «Показать область SQL-кода» на панели инструментов (рис. 4.3).

Эта кнопка активна только в случае, если открыто окно редактирования содержимого таблицы.

Рис. 4.3. Открытие области SQL-кода таблицы

После выполнения вышеописанных действий окно редактирования содержимого таблицы примет вид, показанный на рис. 4.4, где верхняя часть предназначена для написания SQL-запросов.

По умолчанию область SQL-кода содержит шаблон для написания запросов на выборку информации из таблицы (запрос SELECT). Смена шаблона выполняется кнопкой «Изменить тип» на панели инструментов (рис. 4.5).

При этом применение шаблонов не является обязательным, пользователь может писать в области SQL-кода любые запросы к таблице по своему усмотрению. Для выполнения запросов используется кнопка «Выполнить код SQL».

Рис. 4.4. Окно редактирования таблицы с открытой областью SQL-кода

Рис. 4.5. Смена шаблона SQL-запроса к таблице

Специальное окно запросов, которое открывается по кнопке «Создать запрос» на панели инструментов, предоставляет пользователю более широкие возможности по написанию, проверке, исполнению и отображению результатов SQL-запросов к базе данных.

При активации окна запросов на панели инструментов становятся доступными такие кнопки, как

§ «Синтаксический анализ» запроса,

§ комментирование выделенных строк,

§ цветовое выделение ключевых слов, констант, комментариев

и прочие возможности, облегчающие процесс написания и исполнения SQL-запросов.

Правила оформления значений различных типов данных в СУБД MS SQL Server:

§ строковые значения заключаются в одинарные кавычки;

§ даты записываются: '15.10.2009' или '15/10/2009' или '20091015', в зависимости от настроек сервера;

§ в вещественных числах дробная часть отделяется точкой.

3.10 SQL-запросы на вставку, модификацию и удаление данных

Добавление новых записей в таблицу (INSERT)

Общий синтаксис:

а) вставка одной новой строки в таблицу:

б) Вставка в таблицу одной или более строк, взятых из источника, указанного в подзапросе:

Правила:

1. Количество элементов в разделе «Столбцы» должно совпадать с количеством элементов в разделе «Значения»; типы данных должны совпадать либо допускать неявное преобразование;

2. В случае, когда раздел «Столбцы» отсутствует, раздел «Значения» должен содержать столько же значений, сколько всего столбцов в редактируемой таблице, кроме того, значения должны перечисляться в том же порядке, в каком объявлены столбцы таблицы; поэтому рекомендуется всегда явно указывать список столбцов, в которые будут добавляться новые значения;

3. В случае использования синтаксиса (б) подзапрос SELECT может быть адресован либо к этой же таблице, либо к другой таблице базы данных, либо к другой базе данных на сервере;

4. Если для некоторого столбца таблицы задано значение по умолчанию, и требуется в добавляемой записи использовать именно это значение, то в разделе «Значения» для этого столбца необходимо написать ключевое слово DEFAULT.

Примеры:

INSERT INTO detail (detail_name)

VALUES ('Подшипник')

INSERT INTO supply (supplier_id, supply_quantity, supply_cost,

supply_date, detail_id)

VALUES (4, 177, 453.45, '25.09.2009', 1)

INSERT supplier (supplier_type, supplier_name)

VALUES (DEFAULT, 'Смирнов С. С.')

1.1.1 Модификация существующих записей (UPDATE)

Примеры:

UPDATE detail

SET weight = 210

UPDATE material

SET material_name = 'Олово'

WHERE material_id = 2

UPDATE delivery

SET department_id = NULL,

delivery_date = delivery_date + 1 /*увеличение даты поставки на день

WHERE delivery_id = 1 OR department_id > 3

UPDATE detail

SET detail_name = detail_name + '!!!'

WHERE detail_name LIKE '_а%' AND weight BETWEEN 6 AND 10

Удаление записей из таблицы (DELETE)

Примеры:

DELETE detail

WHERE detail_id IN (2, 5, 8)

DELETE detail

WHERE weight IS NULL

DELETE detail

4. Запросы на выборку данных

4.1 Общий синтаксис запроса SELECT

Разделы SELECT и FROM обязательно должны присутствовать в каждом запросе; остальные разделы могут отсутствовать или присутствовать частично.

4.2 Примеры реализации запросов

Наиболее простые запросы:

Рис. 5.1. Результаты выполнения запроса №1

2. Выбрать всю информацию из таблицы материалов

Рис. 5.2. Результаты выполнения запроса №2

Уникальность DISTINCT

3. Выбрать уникальные коды поставщиков из таблицы поставок



Рис. 5.3. Результаты выполнения запроса №3

Ограничение WHERE

4. Выбрать количество и даты поставки детали с кодом 1

Рис. 5.4. Результаты выполнения запроса №4

5. Выбрать названия поставщиков с кодами 1, 4 и 6

Рис. 5.5. Результаты выполнения запроса №5

6. Выбрать всю информацию о поставках, сделанных до 1.10.2009



Рис. 5.6. Результаты выполнения запроса №6

7. Выбрать всю информацию о деталях, не начинающихся на букву «В» (в любом регистре) и чей вес меньше 50

Рис. 5.7. Результаты выполнения запроса №7

8. Выбрать название и код материала для деталей с весом от 5 до 10 г или имеющих в названии букву «н» в третьей позиции

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



Объединение таблиц UNION

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

37. Получить таблицу из двух полей, где первое поле - название детали, материала, поставщика или дата поставки, а второе поле - длина строки из первого поля

5. Организация бизнес-логики

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

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

5.1 Хранимые процедуры

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

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

Среда Management Studio предоставляет два способа создания хранимой процедуры. Первый способ - использование рассмотренного выше окна редактора запросов. Второй способ - в «Обозревателе объектов» в ветке нужной базы данных выбрать пункт «Программирование / Хранимые процедуры» (рис. 6.1). При этом будет сгенерирован шаблон новой хранимой процедуры, снабженный подробными комментариями, которые призваны помочь в написании текста процедуры.

Примеры:

1. Хранимая процедура, возвращающая сведения о поставках деталей

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

3. Процедура, добавляющая сведения в таблицу деталей

5.2 Триггеры

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

Таблица 6.2. Сравнение триггеров и хранимых процедур

Хранимые процедуры	Триггеры
Являются самостоятельным объектом базы данных	Привязываются к таблице или виду
Явно вызываются на выполнение командой EXECUTE	Реагируют на события, связанные с таблицей-владельцем
Могут иметь входные и / или выходные параметры, коды возврата	Не имеют входных / выходных параметров и кодов возврата
Могут выполняться в отдельной транзакции	Выполняются в той же транзакции, в которой осуществляются изменения таблицы-владельца

Возможность закрепления триггеров за конкретными операторами определяется тем, что в языке SQL предусмотрены три типа запросов, предназначенных для изменения данных - INSERT, UPDATE и DELETE. В связи с этим выделяют шесть видов триггеров (табл. 6.3).

Таблица 6.3. Виды триггеров

Объект	Действие	Триггер
Таблица / вид	Вставка строк INSERT	Вместо вставки строк INSTEAD OF
		После вставки строк FOR (AFTER)
	Модификация строк UPDATE	Вместо модификации строк INSTEAD OF
		После модификации строк FOR (AFTER)
	Удаление строк DELETE	Вместо удаления строк INSTEAD OF
		После удаления строк FOR (AFTER)

Примечание: триггеры FOR и AFTER являются синонимами.

Независимо от используемого типа триггера для реализации действий, предусмотренных этим триггером в СУБД SQL Server, используются две виртуальные таблицы.

В одной из них хранятся копии всех вставляемых строк (поэтому данная таблица именуется INSERTED), а во второй хранятся копии всех удаляемых строк (эта таблица именуется DELETED).

Таблица 6.4. Содержимое таблиц INSERTED и DELETED

Вид триггера	Таблицы INSERTED и DELETED
INSTEAD OF INSERT	INSERTED содержит вставляемые в таблицу записи. DELETED пуста.
FOR INSERT	INSERTED содержит вставленные в таблицу записи. DELETED пуста.
INSTEAD OF UPDATE	INSERTED содержит новые значения модифицируемых записей. DELETED содержит старые значения модифицируемых записей.
FOR UPDATE	INSERTED содержит новые значения модифицированных записей. DELETED содержит старые значения модифицированных записей.
INSTEAD OF DELETE	INSERTED пуста. DELETED содержит удаляемые записи.
FOR DELETE	INSERTED пуста. DELETED содержит удаленные записи.

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

Создавать новые триггеры можно либо в редакторе запросов среды Management Studio, либо воспользовавшись специальным шаблоном (рис. 6.10), который открывается при выборе в «Обозревателе объектов» пункта «Создать триггер» у таблицы, к которой будет привязываться создаваемый триггер. сервер база данный интерфейс

Примеры:

1. Триггер на поддержание актуального количества товаров на складе при добавлении поставок деталей



2. Триггер на поддержание актуального количества товаров на складе при удалении поставок

3. Триггер на поддержание актуального количества товаров на складе при отпуске деталей в цеха

6. Прикладной программный интерфейс СУБД

6.1 Знакомство с основными объектами ado.net

Для обеспечения доступа к базам данных СУБД предоставляет разработчику набор стандартных процедур и функций (API - Application Programming Interface).

Для обеспечения быстрой разработки приложений, работающих с СУБД MS SQL Server, были созданы различные наборы компонентов, облегчающие взаимодействие с сервером:

§ BDE - набор компонентов для доступа к большому числу СУБД; разработан фирмой Borland (на текущий момент более не поддерживается); этот способ доступа не является рекомендуемым, поскольку не обеспечивает полноценный доступ к возможностям сервера;

§ ODBC (Open Database Connectivity) - широко распространенный программный интерфейс фирмы Microsoft. ODBC-драйвер для SQL Server, обеспечивает полнофункциональный доступ к серверу.

§ OLE DB и ADO - часть универсального механизма доступа к данным Microsoft. ADO (ActiveX Data Objects) - надстройка над интерфейсом OLE DB, позволяющая обеспечить бизнес-приложениям высокоуровневый доступ к данным. В качестве драйверов баз данных ADO использует так называемые OLE DB-провайдеры (для MS SQL Server используется провайдер Microsoft OLE DB Provider for SQL Server), которые представляют собой COM-серверы, реализующие предопределенный набор COM-интерфейсов.

§ ADO.NET - основная модель доступа к данным для приложений, основанных на Microsoft .NET. Представляет собой совершенно самостоятельную (по отношению к рассмотренному выше механизму ADO) технологию. Компоненты ADO.NET входят в поставку оболочки .NET Framework.

Общее описание объектной модели ADO.NET

ADO.NET (ActiveX Data Object.NET) - новая объектная модель, которая обеспечивает доступ к источникам данных в платформе .NET и использует стандарт XML для передачи данных.

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

Одна из характерных особенностей ADO.NET - это использование управляемых провайдеров (managed provider) для доступа к данным. Управляемый провайдер данных - это набор объектов ADO.NET, разработанных для соединения с определенным источником данных. Все провайдеры обеспечивают одинаковый набор базовых методов и свойств, скрывая в своей реализации всю работу с API доступа к источнику данных. Необходимо только знать, какой управляемый провайдер должен использоваться в конкретном случае. В Visual Studio 2008 (.NET Framework 2.0) доступны:

§ SQL Server .NET Data Provider: предназначен для работы с базами данных Microsoft SQL Server 7.0 и более поздних версий; оптимизирован для доступа к SQL Server и взаимодействует с ним напрямую по «родному» протоколу передачи данных SQL Server;

§ OLE DB .NET Data Provider: управляемый провайдер для источников данных OLE DB; немного уступает по эффективности SQL Server .NET Data Provider, так как взаимодействует с базой данных через уровень OLE DB; используется для доступа к базам данных Access, Oracle, Active Directory, каталогам LDAP и другим источникам, поддерживающим OLE DB;

§ ODBC .NET Data Provider обеспечивает нативный доступ к ODBC-драйверам;

§ Oracle .NET Data Provider предназначен для работы с базами данных СУБД Oracle.

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

§ System.Data.SqlClient - содержит типы SQL Server .NET Data Provider;

§ System.Data.OleDb - содержит типы OLE DB .NET Data Provider;

§ System.Data.Odbc. - содержит типы ODBC .NET Data Provider;

§ System.Data.OracleClient - содержит типы Oracle .NET Data Provider;

§ System.Data. содержит типы, независимые от провайдеров, например, DataSet , DataTable, DataRelation, DataColumn, DataRow.

Рассмотрим основные классы объектов ADO.NET для работы с СУБД MS SQL Server.

DataSet - ключевой объект, который хранит данные в ADO.NET и представляет моментальный снимок части базы данных, размещенный в памяти. DataSet не имеет собственных средств для работы с источниками данных. Связующую роль между DataSet и источником данных выполняют управляемые провайдеры. Класс DataSet состоит из коллекции таблиц DataTable, которые можно связать между собой с помощью объектов класса DataRelation. Кроме того, для обеспечения целостности данных в классе DataSet можно использовать объекты UniqueConstraint и ForeignKeyConstraint.

DataRelation - класс, представляющий отношение "родительский - дочерний объект" между двумя объектами DataTable. Например, в отношении "Покупатель-Заказы" таблица "Покупатели" является родительской, а таблица "Заказы" -- дочерней. Является аналогом отношения первичный ключ - внешний ключ.

DataTable - класс, представляющий одну таблицу с данными в памяти. Является центральным объектом библиотеки ADO.NET.

SqlDataAdapter - представляет набор выполняемых над данными команд и подключения базы данных, которые используется для заполнения DataSet и обновления базы данных SQL Server. Класс по сути служит мостом между DataSet и SQL Server для извлечения и сохранения данных; метод Fill изменяет данные в DataSet для соответствия их источнику данных, а метод Update изменяет данные в источнике данных для их соответствия DataSet с помощью выполняемых над источником данных инструкций Transact-SQL. Обновление выполняется построчно. SqlDataAdapter используется совместно c SqlConnection и SqlCommand для увеличения производительности при подключении к базе данных Microsoft SQL Server.

SqlDataReader - предоставляет возможность чтения потока строк только в прямом направлении из базы данных SQL Server. Используется, как правило, для хранения результатов выполнения SqlCommand.

SqlConnection - предоставляет открытое подключение к базе данных SQL Server и эквивалентен сетевому подключению к серверу.

SqlCommand - класс, представляющий инструкцию Transact-SQL или хранимую процедуру, выполняемую над базой данных SQL Server.

SqlCommandBuilder - связывается с объектом SqlDataAdapter и создает автоматически инструкции Transact-SQL, необходимые для согласования изменений, сделанных в DataSet со связанным экземпляром SQL Server.

Элементы управления для Windows Forms

BindingNavigator - элемент управления, который обеспечивает пользовательский интерфейс для перехода и управления элементами управления, привязанными к данным. BindingNavigator как правило используется совместно с компонентом BindingSource для создания стандартного средства поиска и изменения данных в форме Windows Form.

BindingSource - класс, инкапсулирующий источник данных для формы. Компонент используется для многих задач, выполняя различные функции связи между элементами управления Windows Forms и источниками данных. Это достигается путем присоединения компонента BindingSource к источнику данных с помощью свойства DataSource. Перемещение по источнику данных и его обновление выполняется посредством таких методов, как MoveNext, MoveLast и Remove. Такие операции, как сортировка и фильтрация, обрабатываются посредством свойств Sort и Filter.

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

6.2 Разработка клиентского приложения

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

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

Базовый формат строки подключения содержит последовательность пар "ключевое слово - значение", разделенных символами точки с запятой. Знак равенства связывает каждое ключевое слово с его значением. Для подключения к локальному компьютеру используется имя "(local)" либо символ «точка». Если имя сервера не задано, будет сделана попытка установить соединение с экземпляром по умолчанию на локальном компьютере.

После установления связи с источником формируется «снимок» используемой на клиенте части базы данных, инкапсулированный в DataSet и представляемый в виде коллекции объектов DataTable, связанных отношениями DataRelation. Как правило, данный этап реализуется следующей последовательностью шагов.

1. Построение и заполнение каждого объекта DataTable класса DataSet данными из источника с помощью класса DataAdapter (рис. 7.3 и 7.4).

2. Автоматическая генерация команд на вставку, удаление и модификацию записей каждого объекта SqlDataAdapter для согласования DataSet и источника данных посредством SqlCommandBuilder (рис. 7.5). Одновременно можно связать только один объект SqlDataAdapter или SqlCommandBuilder друг с другом. Для создания инструкций INSERT, UPDATE или DELETE объект SqlCommandBuilder использует свойство SelectCommand, чтобы автоматически получать нужный набор метаданных.

...