Создадим базу данных в SQL Server, состоящую из описанных выше таблиц. Для хранения базы данных будем использовать собственную созданную папку, по умолчанию СУБД хранит файлы в своей рабочей папке в «Program Files». Для создания базы данных воспользуемся утилитой SQL Server Management Studio. При запуске среды разработки появляется окно соединения с сервером.
Если при установке была выбрана проверка подлинности Windows, то нажимаем «Соединить». Если был задан логин и пароль подключения к серверу, то перед нажатием кнопки «Соединить», в выпадающем списке «Проверка подлинности» нужно выбрать «Проверка подлинности SQL Server», а затем необходимо ввести заданные при установке логин и пароль.
Далее при разработке базы данных мы будем использовать диалект языка SQL - Transact-SQL. Transact-SQL (T-SQL) — процедурное расширение языка SQL, созданное компанией Microsoft для Microsoft SQL Server.
SQL был расширен такими дополнительными возможностями как:
· управляющие операторы,
· локальные и глобальные переменные,
· различные дополнительные функции для обработки строк, дат, математики и т. п.,
· поддержка аутентификации Microsoft Windows
Язык Transact-SQL является ключом к использованию MS SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.
Для создания базы данных необходимо в «Обозревателе объектов» выбрать позицию «Базы данных», нажав на правую кнопку мыши, выбрать строку «Создать базу данных…» с вызовом на экран следующего диалогового окна:
В пункте «Имя базы данных» необходимо набрать идентификатор базы. В диалоге задаются логическое имя файла данных и журнала транзакций. По этим именам будет происходить обращение к вышеприведенным файлам в БД. Можно заметить, что файл данных имеет то же имя что и БД, а имя файла журнала транзакций составлено из имени БД и суффикса "_log".
«Начальный размер(MБ») - начальный размер файла данных и журнала транзакций в мегабайтах.
«Автоувеличение/максимальный размер»- автоувеличение разме-ра файла. Как только файл заполняется информацией его размер автоматически увеличивается на величину, указанную в параметре. Увеличение можно задавать как в мегабайтах так и в процентах. Здесь же можно задать максимальный размер файлов.
«Путь» - путь к папке, где хранятся файлы. Для изменения этого параметра надо нажать кнопку "…".
«Имя файла» - имена файлов. По умолчанию имена файлов аналогичны логическим именам. Однако файл данных имеет расширение "mdf", а файл журнала транзакций - расширение "ldf". Завершение процедуры создания базы данных осуществляется нажатием на кнопку OK. В результате на экране в списке баз данных появится новая БД.
Также создать новую базу данных можно при помощи команды SQL. Для этого выбираем из стандартной панели инструментов пиктограмму «Создать запрос». 
Откроется окно редактирования, используем команду «CREATE DATABASE», она имеет следующий синтаксис:
CREATE DATABASE <Имя БД>
ON (Name=<Логическое имя>,
FileName=<Имя файла>
[Size=<Нач.размер>,]
[Maxsize=<Макс.размер>,]
[FileGrowth=<Шаг>])
[LOG ON
(Name=<Логическое имя>,
FileName=<Имя файла>
[Size=<Нач.размер>,]
[Maxsize=<Макс.размер >,]
[FileGrowth=<Шаг>])
Где < Имя БД> - имя создаваемой БД, раздел ON описывает файла данных БД, раздел LOG ON определяет параметры журнала транзакций. В нашем случае, аналогичная предыдущей база данных создается командой:
CREATE DATABASE myBase
ON (Name = myBase,
FileName = 'C:\Uchbase\myBase.mdf',
Size = 5Mb,
Maxsize = 500Mb,
FileGrowth= 5Mb)
LOG ON
(Name = myBaseLog,
FileName = 'C:\Uchbase\myBase.ldf',
Size = 5Mb,
Maxsize = 500Mb,
FileGrowth = 5Mb)
Для исполнения команды выбираем из панели инструментов «Редактор SQL» пиктограмму «Выполнить». 
При успешном выполнении команды, в окне сообщений отобразится соответствующее сообщение и в списке баз данных появится новая БД.
Для переноса БД с одного компьютера на другой можно воспользоваться механизмом резервного копирования, он сохраняет как и метаданные вашей базы, так и все хранящиеся в ней данные. Заходим в контекстное меню в списке баз данных и выбираем пункт «Задачи» -> «Создать резервную копию…». Выбираем БД, которую хотим выгрузить и задаем имена файлов и пути куда будет выгружаться резервная копию. По умолчанию, SQL Server сохраняет в файл с именем БД и расширением.bak в специальную папку Backup
Обратная процедура, извлечения базы данных из резервной копии производится при помощи команды контекстного меню «Восстановить базы данных…». В диалоге выбираем файл, из которого будет производится восстановление. Далее можно будет задать остальные параметры БД.
Вся информация в базе данных хранится в таблицах. Таблицы это обычные таблицы для хранения данных. Таблицы состоят из записей. Запись это строка в таблице. Вся информация обрабатывается по записям. Каждая запись состоит из полей. Поле это столбец таблицы. Каждое поле имеет три характеристики:
· Имя поля - используется для обращения к полю;
· Значение поля - определяет информацию, хранимую в поле;
· Тип данных поля - определяет какой вид информации можно хранить в поле.
В SQL сервер используется следующие типы данных:
· Битовые типы данных которые содержат последовательности нулей и единиц: Binary(n) и Varbinary(n), где n длина. Длина содержимого полей типа Binary всегда равна n, разница заполняется пробелами. Varbinary размер поля равен n или меньшему;
· Целочисленные типы данных - типы данных для хранения целых чисел (в скобках указан диапазон значений типа данных, примерно): Tinyint (0-255), Smallint (±215), Int (±231), Bigint (±263);
· Типы данных для хранения дробных чисел: Real семь знаков после запятой, Float(m) может хранить числа из m знаков, максимальное m=38, Decimal(m, n) дробные числа с m знаков до запятой и n после;
· Специальные типы данных: Bit - логический тип данных.является заменой логическому типу Boolean в Visual Basic, Text - тип для хранения больших объемов текста, одно поле может хранить до 2 Гб текста, Image - тип данных для хранения до 2Гб рисунков, RowGUID - уникальный идентификатор строки таблицы, SQL_Variant - аналогичен типу Variant в Visual Basic;
· Типы данных даты и времени: Datetime (1 января 1753 года — 31 декабря 9999 года). SmallDatetime (от 1.01.1900 до 06.06.2079), Date, Time;
· Денежные типы данных для хранения финансовой информации: Money (от -922 337 203 685 477,5808 до 922 337 203 685 477,5807), Smallmoney (от -214 748,3648 до 214 748,3647);
Для создания таблиц в SQL Server в первую очередь необходимо сделать активной ту БД, в которой создается таблица. Для этого в новом запросе можно набрать команду: USE <Имя БД>, либо на панели инструментов «Редактор SQL» необходимо выбрать в выпадающем списке рабочую БД. 
После выбора БД можно создавать таблицы. Для создания таблиц можно использовать операторы языка SQL.
На рисунке приведен пример такого оператора для создания структуры таблицы «Товары» создаваемой базы данных.
Кнопка 
используется для проверки синтаксиса набранного оператора SQL. Запуск оператора в диалоговом окне осуществляется нажатием на кнопку 
. После выполнения оператора в базе данных создается таблица TOVARY, ее поля соответственно получают наименования KOD_TOVAR, TOVAR, ED_IZM, ZENA, COUNT_TOV. Параметр NOT NULL вводится для контроля начального заполнения поля при его обработке (в этом случае, если поле не получило никакого значения, на уровне сервера вырабатывается исключительная ситуация; мы сознательно не поставили этот параметр на полях ED_IZM и COUNT_TOV для демонстрации обработки соответствующей ситуации на уровне клиентского места). Кроме того, параметр DEFAULT задает значение соответствующего поля по умолчанию. Поскольку поле ZENA не может принимать отрицательных значений, то для этого поля определен предикат ZENA >= 0, исполняющий роль сторожа. Ложное значение предиката генерирует исключительную ситуацию. Поле COUNT_TOV также должно принимать неотрицательные значения. При проектировании базы данных проверка этого предиката оставлена клиентской части программы. Для таблицы TOVARY определен первичный ключ KOD_TOVAR. Для этого ограничения мы задали имя PK_TOV, в дальнейшем, по этому имени мы сможем совершать действия с этим ограничением. Также SQL Server при возникновении исключительных ситуаций будет объявлять, какое ограничение их вызвало. Если не указывать имя, сервер сгенерирует имя самостоятельно.
Таблица имеет первичный ключ - целочисленное поле. Значения такого поля для различных записей должны быть разными по определению. Поскольку это поле не имеет содержательного смысла и используется только для связи между таблицами, то заполнение этого поля (проверку уникальности его значения) можно поручить серверу базы данных. Для этого удобно использовать IDENTITY. Каждому полю с IDENTITY SQL Server ставит в соответствие счетчик и следит за нумерацией вновь создаваемых записей.
SQL операторы могут быть загружены (сохранены) из текстового файла с расширением.sql
Перед созданием таблицы «Покупатели» создадим домен(пользовательский тип) KEY_TYPE, используя оператор:
CREATE TYPE KEY_TYPE FROM INT NOT NULL;
Использование домена предпочтительно в том случае, когда в базе данных присутствуют различные столбцы, обладающие одними и теми же характеристиками. Домен появится в БД в разделе «Программирование» - «Типы» - «Определяемые пользователем типы данных»
Таблица «Покупатели» определяется оператором:
CREATE TABLE POKUPATELI (
KOD_POKUP KEY_TYPE IDENTITY,
POKUP VARCHAR(30) NOT NULL,
GOROD VARCHAR(20) DEFAULT '',
ADRES VARCHAR(25) DEFAULT '',
TEL VARCHAR(8),
CONSTRAINT PK_POK PRIMARY KEY (KOD_POKUP));
Отметим, что при определении поля KOD_POKUP используется ранее определенный домен KEY_TYPE[1]. Для таблицы определен первичный ключ KOD_POKUP.
Таблица «РАСХОД ТОВАРА» определяется оператором:
CREATE TABLE RASXOD(
KOD_RASH KEY_TYPE IDENTITY,
DATA_RASH DATE NOT NULL,
KOLVO INT DEFAULT 0,
STOIM KEY_TYPE,
KOD_TOVAR KEY_TYPE,
KOD_POKUP KEY_TYPE,
CONSTRAINT PK_RASH PRIMARY KEY (KOD_RASH));
Поскольку в нашей задаче существует связь между таблицами (каждая покупка, описанная в таблице RASXOD связывает конкретного покупателя, описанного в таблице POKUPATELI с товаром, описание которого дано в таблице TOVARY), необходимо зафиксировать эту связь на уровне базы данных. Отсутствие такой информации может привести к ситуации, когда зафиксирована покупка отсутствующего товара, или товар приобретен не зафиксированным покупателем [2]. Поддержка концепции целостности данных обеспечивается установкой специальных связей.
Добавим связи между таблицами
ALTER TABLE RASXOD
ADD CONSTRAINT TOV_RASH
FOREIGN KEY(KOD_TOVAR) REFERENCES TOVARY
ON DELETE CASCADE ON UPDATE CASCADE;
В базу данных добавлена связь таблицы «Расход» по полю KOD_TOVAR с родительской таблицей «Товары». Теперь SQL-сервер не допустит появления в таблице «Расход» строк со значениями поля KOD_TOVAR, которые не встречаются в таблице «Товары». Причем, благодаря дополнительным указаниям ON..., удаление товара или изменение кода товара в таблице «Товары» повлечет соответствующие изменения в соответствующих записях дочерней таблицы «Расход» (режим каскадного обновления содержимого таблиц)[3].
Связь между таблицами «Расход» и «Покупатели» определим следующим оператором:
ALTER TABLE RASXOD
ADD CONSTRAINT POK_RASH
FOREIGN KEY(KOD_POKUP) REFERENCES POKUPATELI;
В этом случае не объявлено автоматическое каскадное обновление и удаление записей дочерней таблицы для соответствующих операций в родительской таблице. По умолчанию будет использована RESTRICT-стратегия поддержания ссылочной целостности, т.е. SQL-сервер не допустит удаления строки и изменения кода покупателя таблице «Покупатели», если на этого покупателя (по коду) ссылается хотя бы одна строка таблицы «Расход». Программист может запрограммировать соответствующие действия каскадного обновления и удаления в процедурах-триггерах базы данных.
Соответствующие триггеры выглядят следующим образом.
¨ Триггер для события удаления записей выглядит следующим образом:
CREATE TRIGGER ID_POKUPATELI on POKUPATELI
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM RASXOD
WHERE RASXOD.KOD_POKUP in (
select KOD_POKUP from deleted)
DELETE FROM POKUPATELI
WHERE POKUPATELI.KOD_POKUP in (
select KOD_POKUP from deleted)
END
Триггер – это процедура, которая выполняется автоматически при изменениях, происходящих в таблице. В данном случае триггер будет выполняться вместо изменений (INSTEAD OF) в таблице POKUPATELI и выполнит соответствующий код. Аббревиатура ID в названии триггера построена из символов I(INSTEAD) и D(DELETE). Таблица deleted это виртуальная таблица имеющая такую же структуру как POKUPATELI, и хранящая удалённые строки. В подзапросах триггер получает все коды удалённых покупателей в текущюю операцию. В первую очередь из таблицы RASXOD удаляются все записи с выбранными кодами покупателей KOD_POKUP. Так как триггер отрабатывает вместо действия его вызвавшего (INSTEAD OF), то необходимо удалить строки и в нашей таблице POKUPATELI. Если создать триггер выполняющийся после события (AFTER), то код триггера будет запускаться после события. В нашем случае – это даст генерацию исключения, так как нарушается ограничение внешнего ключа FOREIGN KEY в таблице RASXOD, существуют записи с покупателями удалённым из таблицы POKUPATELI.
¨ Для обновления дочерней таблицы
CREATE TRIGGER IU_POKUPATELI on POKUPATELI
INSTEAD OF UPDATE AS
BEGIN
ALTER TABLE RASXOD NOCHECK CONSTRAINT POK_RASH;
DECLARE @old INT;
DECLARE @new INT;
IF @@ROWCOUNT = 1
BEGIN
SELECT @old=KOD_POKUP FROM deleted;
SELECT @new=KOD_POKUP FROM inserted;
UPDATE RASXOD SET KOD_POKUP = @new
WHERE KOD_POKUP=@old;
UPDATE POKUPATELI SET KOD_POKUP = @new
WHERE KOD_POKUP=@old;
END
ALTER TABLE RASXOD CHECK CONSTRAINT POK_RASH;
END
Триггер запускается вместо события обновления таблицы POKUPATELI. В нашем примере этот триггер не имеет практического смысла, так как поле KOD_POKUP в таблице POKUPATELI регулируется при помощи IDENTITY, то есть недоступно для редактирования. При включенной проверке ссылочной целостности, сервер не даст внести изменения в родительский и внешний ключи. Чтобы избежать этого отключим проверку ограничения POK_RASH на время выполнения триггера. Объявим две переменные @old, @new. Все переменные начинаются с символа @. Если изменяется только одна строка в таблице POKUPATELI, то получаем значения переменных из поля KOD_POKUP таблиц deleted и inserted. В MS SQL Server для события изменения строк создаются две виртуальные таблицы – удаленных и добавленных строк. Далее обновляем в таблицах RASXOD и POKUPATELI поля KOD_POKUP и включаем проверку ограничения. Теперь при изменении кода покупателя в таблице POKUPATELI соответствующие каскадные изменения произойдут в дочерней таблице RASXOD.
Следует отметить, что таблицы «Товары» и «Расход товара» связаны по формулам:
¨ Стоимость купленного товара:= Количество купленного товара * цена единицы товара [4];
¨ Количество товара на складе:= Количество товара на складе – Количество купленного товара.
Эти соотношения тоже являются требованиями целостности (правильности) базы данных, однако имеют специальный связанный с конкретной предметной областью характер. Контроль первого требования оставим клиентской части программы.
Решение второй задачи возложим на SQL-сервер. Для этого добавим в БД триггер, отрабатывающий после ввода новых данных в таблицу RASXOD
CREATE TRIGGER AI_RASXOD1 on RASXOD
AFTER INSERT AS
BEGIN
UPDATE TOVARY SET COUNT_TOV = COUNT_TOV-(
SELECT SUM(KOLVO) FROM inserted WHERE
TOVARY.KOD_TOVAR = inserted.KOD_TOVAR)
WHERE KOD_TOVAR in (SELECT KOD_TOVAR FROM inserted);
END
Триггер, отрабатывающий перед удалением данных в таблице RASXOD, выглядит следующим образом
CREATE TRIGGER BD_RASXOD1 ON RASXOD
AFTER DELETE AS
BEGIN
UPDATE TOVARY
SET COUNT_TOV = COUNT_TOV+(
SELECT SUM(KOLVO) FROM deleted WHERE
TOVARY.KOD_TOVAR = deleted.KOD_TOVAR)
WHERE KOD_TOVAR in (SELECT KOD_TOVAR FROM deleted);
END
Триггер, отрабатывающий после обновления данных в таблице RASXOD (а точнее при изменении количества купленного товара)
CREATE TRIGGER AU_RASXOD1 ON RASXOD
AFTER UPDATE AS
BEGIN
IF UPDATE(KOLVO)
begin
UPDATE TOVARY SET COUNT_TOV = COUNT_TOV –
(SELECT SUM(KOLVO) FROM inserted
where TOVARY.KOD_TOVAR = inserted.kod_tovar) +
(SELECT SUM(KOLVO) FROM deleted
where TOVARY.KOD_TOVAR = deleted.kod_tovar)
WHERE KOD_TOVAR in (SELECT KOD_TOVAR FROM inserted);
end
END;
Остается еще один не рассмотренный случай – изменение кода купленного товара KOD_TOVAR в строке таблицы RASXOD [5] должно повлечь перерасчет значений количества товаров на складе COUNT_TOV в таблице TOVARY.
CREATE TRIGGER AU_RASXOD2 ON RASXOD
AFTER UPDATE AS
BEGIN
IF UPDATE(KOD_TOVAR)
begin
UPDATE TOVARY SET COUNT_TOV = COUNT_TOV-(
SELECT SUM(KOLVO) FROM inserted
WHERE TOVARY.KOD_TOVAR = inserted.KOD_TOVAR)
WHERE KOD_TOVAR in (SELECT KOD_TOVAR FROM inserted);
UPDATE TOVARY SET COUNT_TOV = COUNT_TOV+(
SELECT SUM(KOLVO) FROM deleted
WHERE TOVARY.KOD_TOVAR = deleted.KOD_TOVAR)
WHERE KOD_TOVAR in (SELECT KOD_TOVAR FROM deleted);
end
END;
Комментарий к триггерам обновления:
§ Вообще-то можно было бы написать один триггер обновления, подходящим образом объединив их содержание. Но раз уж их два, то возникает вопрос об их взаимоотношениях:
· После события обновления таблицы RASXOD (ON RASXOD AFTER UPDATE) отработают оба триггера.
· Порядок, в котором они отработают явно не задан. Правда, в нашем случае этот порядок и не важен.
· Однако важно, чтобы только один из них внес изменения в таблицу TOVARY, поэтому в теле использованы альтернативные IF-условия.
§ В триггере AU_RASXOD2 используется еще одно (внутреннее) IF-условие и связанный с ним оператор SELECT.
Это IF-условие связано со спецификой триггеров – в отличие от обычных процедур триггер вызывается не оператором процедуры, а неявно по соответствующему событию, что достаточно необычно для традиционного процедурного программирования. Поэтому программирование триггеров требует специального внимания – дабы не допустить действий, просто лишних, а тем более искажающих результат или ведущих к порочному кругу (Deadlock).
В нашем случае, если в таблице «Товары» изменится значение поля KOD_TOVAR, то в дочерней таблице «Расход» будет выполнено каскадное обновление согласно объявленному ограничению ссылочной целостности. Это обновление инициирует запуск всех UPDATE-триггеров таблицы RASXOD... для каждой обновляемой её строки... Далее можно просчитать, что такой косвенный вызов не должен повлечь перерасчета количества товаров на складе в таблице «Товары»... а он произойдет и даст неправильный результат...
SELECT-оператор и последующее IF-условие позволяют проверить, имеется ли OLD.KOD_TOVAR в таблице TOVARY... отсутствовать он может только в случае косвенного вызова триггера AU_RASXOD0, инициированного тем самым каскадным обновлением. Возможно, это не самое лучшее решение проблемы, но решение...
Заполнение данных в таблице может быть выполнено в двух режимах:
1. Заполнение записей таблицы в интерактивном режиме с использованием средств SQL SERVER Management Studio. Для этого необходимо вызвать на экран интерактивное окно свойств соответствующей таблицы и выбрать «Изменить первые 200 строк»
Далее можно начать набор данных. При вводе данных отрабатывают все триггера, определенные в базе данных.
2. Другим вариантом заполнения таблицы является использование операторов языка SQL. Для этого (используя любой редактор текстов, например WordPad) подготовим SQL-программу (SQL-Script) и выполним ее в окне редактора запросов.
Ниже приведен протокол работы этой программы:
USE MYBASE;
DELETE FROM RASXOD;
DELETE FROM POKUPATELI;
DELETE FROM TOVARY;
SET IDENTITY_INSERT POKUPATELI ON;
INSERT INTO POKUPATELI (KOD_POKUP,POKUP,GOROD,ADRES,TEL)
VALUES (1,'Алиса','Казань','ул.Латышских стрелков','92-45-67');
INSERT INTO POKUPATELI (KOD_POKUP,POKUP,GOROD,ADRES,TEL)
VALUES (2,'Буратино','Рим','Италия','23-45-35');
INSERT INTO POKUPATELI (KOD_POKUP,POKUP,GOROD,ADRES,TEL)
VALUES (5,'Тротилла','Пруд','Италия',NULL);
INSERT INTO POKUPATELI (KOD_POKUP,POKUP,GOROD,ADRES,TEL)
VALUES (6,'Шекспир','Лондон','Англия',NULL);
SET IDENTITY_INSERT POKUPATELI OFF;
SELECT * FROM POKUPATELI;
KOD_POKUP POKUP GOROD ADRES TEL
=========== ============================== ==================== ========================= ========
1 Алиса Казань ул.Латышских стрелков 92-45-67
2 Буратино Рим Италия 23-45-35
5 Тротилла Пруд Италия <null>
6 Шекспир Лондон Англия <null>
SET IDENTITY_INSERT TOVARY ON;
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (1,'Сахар','кг',12,1442);
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (2,'Макароны','кг',5,86);
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (3,'Огурцы весовые','кг',6,250);
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (4,'Огурцы баночные','банки',10,475);
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (5,'Крупа манная','кг',8,1010);
INSERT INTO TOVARY (KOD_TOVAR,TOVAR,ED_IZM,ZENA,COUNT_TOV)
VALUES (2,'Масло подсолнечное','л',10,1977);
Дата добавления: 2015-09-12; просмотров: 98 | Поможем написать вашу работу | Нарушение авторских прав |
| <== предыдущая лекция | | | следующая лекция ==> |
| База данных (условной) предметной области. | | | Сообщение 547, уровень 16, состояние 0, строка 16 |