Создание структуры базы данных

Разработаем структуру базы данных (БД) Библиотека (Library), используя CASE-средство AllFusion ERwin Data Modeler (ERwin).

ERwin позволяет создавать логическую, физическую модели и модель, совмещающую логический и физический уровни.

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

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

Физический уровень зависит от конкретной СУБД. В физической модели содержится информация обо всех объектах БД. Физическая модель зависит от конкретной реализации СУБД.

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

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

Существуют следующие виды нормальных форм:

• первая нормальная форма (1NF). Сущность Е находится в первой нормальной форме, если и только если все атрибуты содержат только атомарные значения. Среди атрибутов не должно встречаться повторяющихся групп, т. е. нескольких значений для каждого экземпляра;

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

• третья нормальная форма (3 NF). Сущность Е находится в третьей нормальной форме, если она находится во второй нормальной форме и неключевые атрибуты сущности Е зависят от других атрибутов Е.

После третьей нормальной формы существуют нормальная форма Бойсса - Кодда, четвертая и пятая нормальные формы. На практике ограничиваются приведением к третьей нормальной форме.

Создадим логическую (см.рисунок 1) и физическую (см. рисунок 2) модели согласно третьей нормальной форме.

 

Рисунок 1 Рисунок 2

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

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

Создадим базу данных при помощи графического интерфейса SQL Server Management Studio. Щелкнем правой кнопкой мыши по контейнеру Database в Object Explorer и в контекстном меню выберем New Database (Новая база). Откроется диалоговое окно New Database, в левой части этого диалогового окна видим три вкладки: General, Filegroups, Options.На вкладке General зададим имя базы данных Library.На вкладке Filegroups, определим, к какой файловой группе будет относиться файл базы данных. Файловая группа (Filegroup) – это способ организации файлов БД. По умолчанию для любой базы данных создается файловая группа PRIMARY, и все создаваемые файлы будут относиться именно к ней. В создаваемой БД все таблицы можно условно поделим на две группы:• пользовательские таблицы, которые постоянно изменяются пользователями;• таблицы справочника, которые меняются очень редко.

Таким образом, при создании БД создадим дополнительную файловую группу USERS (вкладка Filegroups). Создадим новый файл данных USERS, и определим, что он будет относиться к этой файловой группе. Таблицы справочника оставим в файловой группе PRIMARY.

 

Создание таблиц

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

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

· PRIMARY KEY (первичный ключ) - уникально идентифицирует каждую строку таблицы. Значение в этом столбце либо в упорядоченном наборе столбцов не могут повторяться в более чем одной строке. Столбец PRIMARY KEY определен только с атрибутом NOT NULL. Таблица может иметь только один PRIMARY KEY, который может быть определен на одном или более столбцов;

· ограничения FOREIGN KEY задают и обеспечивают связи между таблицами. Справочные ограничения гарантируют, что значения в наборе столбцов, которые определены в FOREIGN KEY принимают те же самые значения, которые присутствуют в столбце PRIMARY KEY в справочной таблице.

При создании пользовательских таблиц определим, что они будут принадлежать к файловой группе USERS (по умолчания файловая группа PRIMARY). Для этой цели в команде CREATE TABLE используется ключевое слово ON с указанием имени файловой группы.

Далее рассмотрим пример создания таблицы Books:

CREATE TABLE Books --имя таблицы

(

Book_ID nchar(6) PRIMARY KEY, /*символьный тип данных длиной в 6 символов, первичный ключ*/

Theme nchar(3) not null /*символьный тип данных длиной в 6 символов, не может принимать значение null*/

FOREIGN KEY REFERENCES Theme(Theme_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Autor text, --текстовый тип данных

Title text, --текстовый тип данных

Mockery int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Mockery(Mockery_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Year_Edition date, --тип данных дата

Page int, --числовой тип данных

Cost money, --денежный тип данных

Edition int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Type_Edition(Edition_ID)

ON DELETE NO ACTION, --установление вторичного ключа

Quantity int, --целочисленный тип данных

Storage int not null --целочисленный тип данных, не может принимать значение null

FOREIGN KEY REFERENCES Storage(Storage_ID)

ON DELETE NO ACTION --установление вторичного ключа

)

ON USERS --принадлежность таблицы к файловой группе USERS

 

Заполнение таблиц

Заполнение таблиц осуществим с помощью оператора INSERT INTO. Таблицу Books в базе данных Library заполним следующим образом:

INSERT INTO Books

VALUES

('3297P8','A20','ProidakovI.V.','Englishvocabulary','145','2004','864','236.60','3','30','1'),

('6332H4','H91','Zyev M.N','History of Russia','308','2007','634','160.90','2','75','1'),

('1237F4','F12','AlekseevP.V.','The textbook on philosophy','546','2010','328','129.50','3','10','4'),

('4519L9','L45','Eremin N.P.','Latin language','546','2003','498','210.60','2','5','4'),

('1359S1','S92','KravchenkoA.I.','Sociology','800','2005','136','110.00','3','70','1'),

('0047S3','I41','SmirnovaG.N.','Designing of information systems','211','2001','512','156.00','2','50','1'),

('8830P4','P10','Shebetko A.I.','Test','422','1995','198','39.20','4','10','2'),

('3385A0','A20','LvovV.M.','Computer&InternetDictionary','145','1995','574','320.50','3','12','1'),

('6339I5','H91','Orlov A.S.','History of Russia','789','2002','520','174.00','2','30','1'),

('4512I0','I41','Leontev V.I.','Personal Conputer','789','2008','800','549','5','3','2'),

('8854P0','P10','Leontev A.A.','Psychology of dialogue','800','1997','366','30.00','3','15','2'),

('1212G8','F12','Grek O.V.','Philosophy','985','2000','230','149.50','1','10','3'),

('4120G1','I41','Grekyla A.N.','Informatics','800','2010','20','56.00','6','5','2'),

('1357S0','L45','Somov O.A.','Latin language','789','2007','156','200.00','3','2','4'),

('1134S6','S92','Soley N.A.','Sociology','926','1998','348','410.00','4','15','1');

 

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

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

Для базы данных Library создадим три триггера:

1) триггер типа UPDATE печатает определенный текст при каждой модификации таблицы Books:

--создание триггера TR_Print_Update

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'Print_Update' AND type = 'TR')

DROP TRIGGER Print_Update

GO

CREATE TRIGGER TR_Print_Update

ON Books

FOR UPDATE

AS

PRINT 'The Books table was updated'

GO

Чтобы проверить работу триггера, выполним модификацию строки:

UPDATE Books

SET Mockery=145

WHERE Year_Edition='2005'

Будет возвращено сообщение The Books table was updated (1 row(s) affected), так как в результате выполнения оператора UPDATE был запущен триггер. В данном триггере мы задали вывод сообщения, чтобы можно было увидеть работу триггера;

2) триггер типа DELETE будет сохранять все строки, удаленные из таблицы Books_Delivery, в таблицу Books_Delivery_Backup для последующего анализа данных. Для реализации триггера используем программу приведенную ниже:

-- создание таблицы Books_Delivery_Backup

CREATE TABLE Books_Delivery_Backup

(Subscriber int

FOREIGN KEY REFERENCES Subscriber(Subscriber_ID)ON DELETE NO ACTION,

Book_ID nchar(6) not null

FOREIGN KEY REFERENCES Books(Book_ID) ON DELETE NO ACTION,

Date_delivery date not null,

Date_return date not null,

Librarian int not null

FOREIGN KEY REFERENCES Librarian(Librarian_ID) ON DELETE NO ACTION

)

ON USERS

--создание триггера

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'TR_Books_Delivery_Backup' AND type = 'TR')

DROP TRIGGER TR_Books_Delivery_Backup

GO

CREATE TRIGGER TR_Books_Delivery_Backup

ON Books_Delivery

FOR DELETE

AS

INSERT INTO Books_Delivery_Backup

SELECT * FROM deleted

GO

Отметим, что резервной таблице присвоены те же имена колонок и те же типы данных, что и в исходной таблице. Изначально таблица Books_Delivery_Backup не содержит записей, она будет заполняться по мере удаления записей из таблицы Books_Delivery;

3) триггер DELETE для таблицы Subscriber будет выводить информацию о попытках удаления и количестве удаляемых строк:

CREATE TRIGGER TR_Subscriber_Del

ON Subscriber

FOR DELETE AS

PRINT 'Popitka udalenia '+STR(@@ROWCOUNT)+' strok in table Subscriber'

PRINT 'User '+CURRENT_USER

IF CURRENT_USER<>'dbo'

BEGIN

PRINT 'Udalenie zapresheno'

ROLLBACK TRANSACTION

END

ELSE

PRINT 'Udalene razresheno'

Данный триггер будет выводить информацию о количестве строк, которое пытается удалить пользователь, и имя пользователя, выполнившего команду DELETE. Если пользователь не 'dbo', то удаление запрещается и выдается соответствующее предупреждение.

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

--добавление строки

INSERT INTO Subscriber

VALUES ('13000','Aglullina','Liliya','Rafikonvna','02-02-1990','Gremychinsk, Vostochnaya 6-3','570402','Perm Agricultural Academy');

--удаление строки

DELETE FROM subscriber WHERE Subscriber_ID='13000'

В результате выполнения триггера будет выведена следующая информация:

Popitka udalenia 1 strok in table Subscriber

User dbo

Udalene razresheno

(1 row(s) affected)

 

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