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

Далее обеспечиваем целостность данных, делаем каскадным обновление и удаление записей в таблицах:

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

Схема данных

Триггеры

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

Для создания тригера находим нужную нам таблицу, раскрываем ее, щелкаем правой кнопкой на вкладку «Triggers» и нажимаем «New Trigger»:

1.Триггеры на обновление данных

Для моей базы данных триггеры на обновление были написаны для всех таблиц . Синтаксис их похож, поэтому приведу пример на основе таблицы district:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[T_tblDistrict_UTrig] ON [dbo].[tblDistrict] FOR UPDATE AS

SET NOCOUNT ON

/* * Каскадные обновления в 'tblPacient' */

IF UPDATE(DistrictID)

BEGIN

UPDATE tblPacient

SET tblPacient.DistrictID = inserted.DistrictID

FROM tblPacient, deleted, inserted

WHERE deleted.DistrictID = tblPacient.DistrictID

END

update District set

UserName=user_name(),

[DateTime]=GetDate(),

HostName=host_name(),

ClientName=app_name()

from inserted Join District

on inserted.DistrictID=District.DistrictID

2.Триггеры на добавление данных

Для моей базы данных триггеры на добавление были написаны для таблиц tblDoctor, tblPacient и tblPriem.

Пример:

Для таблицы tblDoctor:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[T_tblDoctor_ITrig] ON [dbo].[tblDoctor] FOR INSERT AS

SET NOCOUNT ON

/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblUnivers' */

IF (SELECT COUNT(*) FROM inserted) !=

(SELECT COUNT(*) FROM tblUnivers, inserted WHERE (tblUnivers.UniversityID = inserted.UniversityID))

BEGIN

RAISERROR 44447 ‘Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице “tblUnivers".'

ROLLBACK TRANSACTION

END

Для таблицы tblPriem:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[T_tblPriem_ITrig] ON [dbo].[tblPriem] FOR INSERT AS

SET NOCOUNT ON

/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblDoctor' */

IF (SELECT COUNT(*) FROM inserted) !=

(SELECT COUNT(*) FROM tblDoctor, inserted WHERE (tblDoctor.DoctorID = inserted.DoctorID))

BEGIN

RAISERROR 44447 Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице "tblDoctor".'

ROLLBACK TRANSACTION

END

/* * ЗАПРЕТ ВСТАВКИ БЕЗ СОВПАДАЮЩЕГО КЛЮЧА В 'tblPacient' */

IF (SELECT COUNT(*) FROM inserted) !=

(SELECT COUNT(*) FROM tblPacient, inserted WHERE (tblPacient.Number = inserted.Number))

BEGIN

RAISERROR 44447 Добавление и изменение записи не допускается. Правила целостности данных требуют наличия связанной записи в таблице "tblPacient".'

ROLLBACK TRANSACTION

END

3.Триггеры на удаление данных

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

Пример:

Для таблицы tblUniversity:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[T_tblUnivers_DTrig] ON [dbo].[tblUnivers] FOR DELETE AS

SET NOCOUNT ON

/* * Каскадные удаления в 'tblDoctor' */

DELETE tblDoctor FROM deleted, tblDoctor WHERE deleted.UniversityID = tblDoctor.UniversityID

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

При создании приложения при помощи Microsoft SQL Server язык программирования Transact-SQL является основным программным интерфейсом между приложениями и базой данных Microsoft SQL Server. Использование программ Transact-SQL подразумевает два способа хранения и выполнения.

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

• Можно хранить программы в виде хранимых процедур в SQL Server и создавать приложения, исполняющие хранимые процедуры и обрабатывающие результаты.

Хранимые процедуры в Microsoft SQL Server аналогичны процедурам в других языках программирования:

• они обрабатывают входные аргументы и возвращают вызывающей процедуре или пакету значения в виде выходных аргументов;

• они содержат программные инструкции, которые выполняют операции в базе данных, в том числе вызывающие другие процедуры;

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

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

По сравнению с программами Transact-SQL, которые хранятся локально на клиентских компьютерах, хранимые процедуры SQL Server имеют следующие преимущества.

• Хранимые процедуры регистрируются на сервере.

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

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

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

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

• Хранимые процедуры позволяют уменьшить сетевой трафик. Операцию, занимающую сотни строк программного кода Transact-SQL, можно выполнить в одной инструкции, которая обрабатывает процедуру, а не отправляет этот код по сети.

Для создания хранимой процедуры находим нужную нам базу данных, раскрываем ее, щелкаем правой кнопкой на вкладку «Programmability»,там находим вкладку «Stored Procedures» и нажимаем «New Stored Procedure»:

Для Выполнения процедуры необходимо найти ее во вкладке «Stored Procedures» , нажать ПКМ и выбрать «Execute Stored Procedure»

Примеры хранимых процедур:

1) set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

--Запрос для вывода списка пациентов проживающих в определенном районе

ALTER PROCEDURE [dbo].[DistrPacient]

AS

BEGIN

SELECT tblDistrict.District, tblPacient.PacientSurname, tblPacient.PacientName

FROM tblDistrict INNER JOIN

tblPacient ON tblDistrict.DistrictID = tblPacient.DistrictID

WHERE tblDistrict.DistrictID = tblPacient.DistrictID

END

2) set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

--Запрос для вывода врачей и обслуживаемых ими пациентов.

ALTER PROCEDURE [dbo].[DoctorPacient]

AS

BEGIN

SELECT dbo.tblPacient.PacientSurname, dbo.tblPacient.PacientName, dbo.tblPacient.PolicyNumber, dbo.tblDoctor.LastName, dbo.tblDoctor.FirstName

FROM dbo.tblPacient, dbo.tblDoctor

WHERE dbo.tblPacient.DoctorID = dbo.tblDoctor.DoctorID

END

3) set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

--Запрос для вывода льшот у конкретных пациентов.

ALTER PROCEDURE [dbo].[ExcemptPacient]

AS

BEGIN

SELECT tblLgota.ExemptType, tblLgota.Exempt, tblPacient.PacientSurname, tblPacient.PacientName

FROM tblPacient INNER JOIN

tblLgota ON tblPacient.ExemptID = tblLgota.ExemptID

END

Представления

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

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

Для создания представления находим нужную нам базу данных, раскрываем ее, щелкаем правой кнопкой на вкладку «Views» и нажимаем «New View»:

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

В результате выполнения представления получим:

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

Безопасность базы данных

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

1.Создание логинов

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

Для создания логинов необходимо в Object Explorer найти вкладку «Security», раскрыть её, щелкнуть правой кнопкой мыши по вкладке «Logins» и нажимаем «New Login»:

В открывшемся окне необходимо ввести имя логина и пароль на вход. Так же рекомендую снять галку «Enforce password expiration» ( Задать срок окончания действия пароля).

Аналогичным образом создадим второй логин с именем user.

2. Создание пользователей

Теперь перейдем в пункт «Security» созданной нами базы данных в пункт «Users» и создадим двух пользователей.

Пользователю ILYA разрешим просмотр и выполнение хранимых процедур и запретим удаление и изменение. Пользователю Admin разрешены любые действия.

Заключение

Прежде всего, SQL Server является сервером баз данных. Сама по себе СУБД не может удовлетворить потребности конечного пользователя (если, конечно, не рассматривать редактор запросов как интерфейс пользователя). Если вы мало знакомы с моделью "клиент/сервер", ее нужно понять, в противном случае будет сложно понять и саму СУБД.

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