Генерация схемы базы данных в MS SQL SERVER

 

Для генерации схемы БД из Erwin 7.1 в MS SQL SERVER следует:

1) перейти к вкладке «Physical» (физическая), выбрав нужный пункт из выпадающего списка на панели инструментов;

2) в меню «Database» выбрать пункт «Choose Database»;

3) в открывшемся диалоговом окне выбрать в разделе «Target SQL DBMS» выбрать SQL Server, в разделе «SQL Server Version» из выпадающего списка выбрать 2000 (рисунок 14). «Нажать ОК».

 

 

Рисунок 14 – Диалоговое окно Target Server

 

4) на сервере с помощью преподавателя создать базу данных.

5) в меню «Tools» выбрать пункт «Forward Engineer/Schema Generation…».

В открывшемся диалоговом окне «SQL Server Schema Generation» нажать «Generate» (рисунок 15). (Если необходимо сохранить скрипт создания базы данных, то до нажатия кнопки «Connect», следует нажать «Report». Erwin предложит выбрать файл для сохранения скрипта после указания имени файла и расширения. Нажать «ОК»).

 

 

Рисунок 15 – Диалоговое окно SQL Server Schema Generation

 

В открывшемся окне SQL Server Connection ввести (рисунок 16):

- в поле User Name – имя пользователя;

- в поле Password – свой пароль;

- в поле Database – имя базы данных;

- в поле Server Name – имя сервера.

Нажать Connect (см. рисунок 16).

 

 

Рисунок 16  – Диалоговое окно SQL Server Connection

 

Появится диалоговое окно «Generate Database Schema», в котором отражаются результаты. При возникновении ошибки генерация приостанавливается. Для продолжения необходимо выбрать «Continue». Чтобы при возникновении ошибки процесс генерации не останавливался, необходимо снять метку «Stop If Failure». Нажать «ОК».

Создание представлений

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

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

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

Для создания представлений средствами Transact-SQL используется следующая конструкция:

CREATE VIEW view_name [(column [,...n])]

[WITH ENCRYPTION]

AS

select_statement

[WITH CHECK OPTION]

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

column – имя колонки, которое будет использоваться в представлении. Максимальная длина имени составляет 128 символов. По умолчанию имена колонок в представлении соответствуют именам колонок в исходных таблицах. Явное указание имени колонки требуется при использовании вычисляемых колонок или при объединении нескольких таблиц, имеющих колонки с одинаковыми именами. Имена колонок перечисляются через запятую в соответствии с их порядком в представлении. Имена колонок можно указывать в команде SELECT, определяющей представление.

WITH ENCRYPTION – использование этого параметра предписывает серверу шифровать код SQL-запроса. Это гарантирует, что пользователи не смогут просмотреть код запроса и использовать его. Если при определении представления необходимо скрыть имена исходных таблиц и колонок, а также алгоритм объединения данных, то необходимо использовать эту опцию.

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

1) нельзя создавать новую таблицу на основе результатов, полученных в ходе выполнения запроса, то есть запрещается использование параметра INTO;

2) нельзя проводить выборку данных из временных таблиц, то есть нельзя использовать имена таблиц, начинающихся на # или ##;

3) в представление нельзя включать операции вычисления и группировки, то есть запрещается указание параметров ORDER BY, COMPUTE и COMPUTE BY.

Для удаления представления используется команда Transact SQL DROP VIEW{view […n]}. За один раз можно удалить несколько представлений.

Далее приведены примеры представлений.

 

1 Информация о списанных экземплярах по программированию на Delphi.

CREATE VIEW infoCpicanie

AS

SELECT   /*Указывается, какие поля из каких таблиц будут выведены*/

Книга.Автор, Книга.Название, Экземпляр.Шифр, Списанные_книги.причина_списания, Списанные_книги.номер_протоко-ла_списания, Списанные_книги.Табельный_номер_списавшего

FROM    /*Указывается таблица, и связанные с ней таблицы, из которых выбираются связанные данные.*/

Книга INNER JOIN (Экземпляр INNER JOIN Списанные_книги ON Экземпляр.Шифр = Списанные_книги.Шифр) ON Книга.ISBN = Экземпляр.ISBN

WHERE Книга.Название LIKE '%Delphi'          /*Выбираются только те книги, в названии которых присутствует “ Delphi */

 

2 Информация о месте хранения периодических изданий название которых содержит в себе слово «вестник», но при этом должны быть исключены периодические издания, первое слово которых начинается на «Э», например «Экономический вестник».

CREATE VIEW INFOPeriodika

AS

SELECT Периодические_издания.Название, Периодические_изда-ния.Дата_издания, Отделы.Название_отдела, Отделы.Номер_отдела

FROM       Отделы INNER JOIN Периодические_издания ON Отделы.Номер_отдела = Периодические_издания.Номер_отдела

WHERE Периодические_издания.Название LIKE '[^Э] %вестник%'

/*[^Э] запрещает начинаться первому слову на «Э», % вестник% – указывает на то, что оставшаяся часть названия должна содержать слово «вестник»*/

3 Полная информация об экземпляре, который был издан с 2000 до текущего года

CREATE VIEW FullInfoEkzemplar

AS

SELECT               /*Указываем, какие поля будут выбраны*/

Экземпляр.шифр,Книга.Автор,Книга.Название,Книга.Год_издания, Книга.Издательство,Экземпляр.Предметная_область, Экземпляр.Но-мер_отдела, Экземпляр.Отметка_о_списании, Экземпляр.Отметка_о_замене

FROM       /*Указываем таблицу и связанные с ней таблицы, из которых выбираются связанные данные.*/

Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN

WHERE Книга.Год_издания BETWEEN 2000 AND YEAR (GETDATE())

/* GETDATE () возвращает текущую дату, YEAR (<дата>) – год <даты>*/

Создание триггер ов

 

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

Существует три типа триггеров в зависимости от команд, на которые они реагируют:

1) триггеры на вставку;

2) триггеры на обновление;

3) триггеры на удаление.

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

Для создания триггера используется следующая команда Transact SQL:

 

CREATE TRIGGER trigger_name

ON TABLE

[WITH ENCRYPTION]

{FOR {[DELETE] [,] [INSERT] [,] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

sql_statement [...n]

}  

 

Рассмотрим основные элементы данной команды.

Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.

TABLE – имя таблицы БД, к которой будет привязан триггер.

WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.

[DELETE] [,] [INSERT] [,] [UPDATE] – эта конструкция определяет, на какие автоматы будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов. Допускается создание триггера, реагирующего на две или три команды.

WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.

NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.

sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.

Второй вариант команды создания триггера:

CREATE TRIGGER trigger_name ON TABLE

[WITH ENCRYPTION]

{FOR { [[INSERT] [,] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

{ IF UPDATE (column)

[ { AND/OR }

UPDATE(column) ]

[..n]}

sql_statement [...n]}}

FOR { [[INSERT] [,] [UPDATE] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.

IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.

AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при осуществлении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).

Для изменения триггера используется команда ALTER TRIGGER.

 

Примеры.

1 Триггер, который будет запрещать удаление записей таблицы «Пользование библиотекой», если текущий пользователь не владелец базы данных и если поле «дата выдачи» содержит какое-либо значение.

CREATE TRIGGER udalenie     /* Задается имя триггера */

ON Пользование_библиотекой2 /*Указывается имя таблицы, с которой будет связан триггер*/

FOR DELETE /*Указывается операция, на которую будет срабатывать триггер (в данном случае - на удаление)*/

AS

IF (SELECT count(*)                                                                

from Пользование_библиотекой2

where Пользование_библиотекой2.дата_выдачи is not null)>0

/*условие проверяет наличие записей в поле «дата выдачи». Если count возвращает значение отличное от нуля (означает, что запись есть), то первое условие IF не выполнено*/

AND (CURRENT_USER <> 'dbo') /*вызывается функция определения имени текущего пользователя и проверяется, является ли текущий пользователь владельцем базы данных*/

ROLLBACK TRANSACTION   /*откат (отмена) транзакции*/

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

Перед созданием такого триггера необходимо создать таблицу DeletedItems, куда будет производиться запись:

CREATE TABLE DeletedItem (

     [Читательский_номер] [int] NOT NULL, /*объявляются поля таблицы*/

      [имя] [varchar] (25) NULL,

      [отчество] [varchar] (75) NULL,

       [фамилия] [varchar] (25) NULL,

      [должность] [varchar] (35) NULL,

     [Имя_пользователя] [varchar] (50) NULL,

     [Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Сам триггер выглядит следующим образом:

CREATE TRIGGER deletedby

ON Преподаватели         /* триггер связывается с таблицей Преподаватели*/

FOR DELETE

AS

INSERT INTO DeletedItem                 

(Читательский_номер,имя,отчество, /*указывается, какие поля нужно вставить*/

фамилия,должность,Имя_пользователя,Дата_удаления)

SELECT Читательский_номер,имя,отчество,фамилия, должность,

SYSTEM_USER,   /*функция определяет текущего пользователя*/

getdate()      /*функция возвращает текущую дату*/

FROM deleted /* deleted – временная таблица, куда заносятся удаляемые данные*/

3 Триггер, запрещающий ввод значения в поле «год_поступления», если вводимое значение превышает текущий год.

CREATE TRIGGER proverka_Studentov ON Студенты FOR INSERT

AS

DECLARE @@t int /*Объявляется переменная t */

Set @@t=5           /*Присваивается переменной t значение 5*/

IF NOT EXISTS (SELECT * FROM Студенты, inserted

WHERE Студенты.год_поступления = inserted.год_поступления)

Set @@t=0

IF EXISTS (SELECT * FROM Студенты, inserted

WHERE inserted.год_поступления>YEAR(GETDATE())) /*Проверяется, что больше - значение вводимого года или текущего*/

Set @@t=0   /*Если год поступления больше текущего, то меняется значение переменной */

If @@t=0 /*если t =0, значит были ошибки*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

Создание хранимых процедур

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

CREATE PROCEDURE] procedure_name [:number]

[ {parameter data_type} [VARYING] [= default] [OUTPUT] ] [...n]

[WITH { RECOMPILE

| ENCRYPTION

| RECOMPILE. ENCRYPTION } ]

[FOR REPLICATION]

AS sql_statement [...n]

Синтаксис имеет следующее назначение:

- procedure_name – имя создаваемой процедуры. Используя префиксы sp_, # и ##, можно определить создаваемую процедуру как системную или временную. При этом необходимо позаботиться и о размещении процедуры в соответствующей системной базе данных, поскольку команда CREATE PROCEDURE создает хранимую процедуру в текущей базе данных. Поэтому перед созданием процедуры необходимо выполнить команду USE, чтобы сделать требуемую базу данных текущей;

- number – параметр определяет идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур;

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

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

- OUTPUT – использование этого ключевого слова определяет указанный па­раметр как выходной;

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

- default – позволяет определить для параметра значение по умолчанию, которое хранимая процедура будет использовать в случае, если при ее вызове указанный параметр был опущен; 

- RECOMPILE – ключевое слово, предписывающее системе создавать план выполнения процедуры при каждом ее вызове;

- FOR REPLICATION – процедура, определенная с использованием данного ключевого слова, предназначена исключительно для осуществления процесса репликации. Вы не можете сочетать это ключевое слово с ключевым словом WITH RECOMPILE;

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

- AS – ключевое слово, определяющее начало кода хранимой процедуры. После этого ключевого слова следуют команды Transact-SQL, которые и составляют непосредственно тело процедуры (sql statement). Здесь можно использовать любые команды, включая вызов других хранимых процедур, за исключением команд, начинающихся с ключевого слова CREATE.

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

 

1 Хранимая процедура, выбирающая книги, выданные за текущий день.

CREATE PROCEDURE CpicokVidach

AS

SELECT /*Перечисляются поля, которые будут выведены в результате запроса */

Пользование_библиотекой2.Табельный_номер, COUNT(Пользование_библиотекой2.Дата_выдачи)   

FROM /*указывается имя таблиц, из которых выбираются записи*/

Пользование_библиотекой2

WHERE /*задаются условия отбора*/

Пользование_библиотекой2.Дата_выдачи=(SELECT GETDATE())

GROUP BY /*производится группировка по указанному полю*/

Табельный_номер

/*SELECT GETDATE() позволяет получить текущую дату (год, месяц, число)

COUNT (<поле>) возвращает количество записей заданного поля*/

2 Хранимая процедура для вставки данных в таблицу «Заказы». Вставка разрешена, если в таблицах «Книга» и «Преподаватели» есть записи, на которые будет ссылаться новая запись.

PROCEDURE NewZakaz

@Kolvo int,   /*Объявляются необходимые переменные*/

@data_zakaza datetime,

@Chit_nomer int,

@ISBN varchar(20)

AS /*Проверяется, есть ли запись в таблице «Заказы» с такими же значениями ключевых полей, как у новой записи*/

IF EXISTS (SELECT * FROM Заказы WHERE ISBN = @ISBN AND Читательский_номер=@Chit_nomer)

RETURN 0 /*Если есть, завершаем выполнение процедуры*/

IF EXISTS (SELECT * FROM Преподаватели WHERE Читательский_номер = @Chit_nomer)

/*Проверяется, есть ли в таблице «Преподаватели» соответствующая запись*/

IF EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

/*Проверяется, есть ли в таблице «Книга» соответствующая запись*/

INSERT INTO Заказы /*Указывается таблица, в которую вставляется запись*/

VALUES (@Kolvo,@data_zakaza,@Chit_nomer, @ISBN) /* Указывается, какие значения вставляются в таблицу*/

 

3 Хранимая процедура для обновления данных в таблице «Студенты» (изменение фамилии студента).

CREATE PROCEDURE UpdateStudent

 @Chit_nom int,   /*Объявляются необходимые переменные*/

 @Fam varchar(20)

 AS

IF EXISTS (SELECT * FROM Студенты            

WHERE Читательский_номер = @Chit_nom) / Проверяется, существуют ли студенты, читательский номер которых равен искомому*/

UPDATE Студенты          /*Если такие есть обновляется таблица «Студенты»

SET Фамилия=@Fam      /*полю «Фамилия» присваивается новое значение*/

WHERE Читательский_номер = @Chit_nom  /*если  читательский номер записи равен искомому*/

4 Хранимая процедура для подсчета количества экземпляров книги с заданным ISBN

CREATE PROCEDURE KolExzemplarov

/*Объявляем параметр хранимой процедуры - ISBN */

@ISBN varchar(20)

AS

/* Следующая конструкция проверяет, существуют ли записи в таблице «Книги» с заданным ISBN */

IF not EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

RETURN 0 /*Вызывает конец процедуры KolExzemplarov */

SELECT Экземпляр.ISBN

INTO TEMP1 /*Сохраняет выбранные поля во временной таблице Temp 1*/

FROM Экземпляр

WHERE ISBN = @ISBN

SELECT COUNT(ISBN) /* Count подсчитывает количество неповторяющихся записей поля ISBN */

FROM TEMP1

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

CREATE PROCEDURE CpicokKnigCtudenta

@Chit_nom int     /*Объявляем параметр хранимой процедуры – читательский номер студента*/

AS SELECT Студенты.Имя, Студенты.Фамилия, Пользование_библиотекой2.Шифр, Книга.Автор, Книга.Название

FROM   /* указываем имена таблиц, из которых выбираются записи*/

Книга, Экземпляр,Студенты, Пользование_библиотекой2

WHERE (Студенты.Читательский_номер = Пользование_библиотекой2.Читательский_номер) AND (Экземпляр.Шифр = Пользование_библиотекой2.Шифр) AND (Экземпляр.ISBN =Книга.ISBN)

/* AND позволяет задать в операторе WHERE несколько условий, которые должны выполняться одновременно*/

Содержание разделов пояснительной записки

 

Введение

 

Введение должно содержать краткое обоснование необходимости разработки ИС: где и как ИС может быть применена, в чем состоит ее практическая значимость.

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

3.1 Техническое задание

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

          

Анализ предметной области выполняется на основе [8].

В разделе «Анализ предметной области» указываются:

1) общие сведения об объекте автоматизации и документах, на основании которых создается ИС;

2) назначение и цели создания ИС (при этом необходимо определить критерии оценки достижения целей системы).

 

Постановка задачи

 

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

1) характеристика объекта автоматизации. Здесь необходимо:

– выполнить описание работы объекта автоматизации (например, отделов предприятия, для автоматизации работы которых создается ИС);

– перечислить основные функции объекта автоматизации и указать информацию, подлежащую хранению;

– перечислить категории пользователей будущей базы данных, определить права доступа различных категорий пользователей к различной информации в ИС;

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

2) требования к системе:

– требования, предъявляемые к ИС в целом (надежности, безопасности, защиты информации, квалификации персонала);

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

– требования к программному обеспечению;

3) состав и содержание работ по созданию системы.