Файл: Разработка базы данных Военная часть.docx

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

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

Определим права доступа в каждой из схем для пользователей.

Используются следующие сокращения:

S – чтение данных (select);

I – добавление данных (insert);

U – модификация данных (update);

D – удаление данных(delete)

Таблица 1 – Права доступа пользователей к схемам сущностей




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

3. 1   2   3   4   5   6   7

---

РАЗРАБОТКА БАЗЫ ДАННЫХ

1. СОЗДАНИЕ БАЗЫ ДАННЫХ

Для реализации поставленной задачи воспользуемся серверным программным обеспечением Microsoft SQL Server и средой разработки SQL Server Management Studio (в дальнейшем SSMS).

Переходим в среду разработки SSMS и создаем базу данных:



Рисунок 4 – Создание базы данных «MilitaryUnit»

На рисунке 4 указываем наименование новой базы данных, и владельца (корневого администратора сервера).

2. СОЗДАНИЕ ПОЛЬЗОВАТЕЛЕЙ

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

Создаем пользователя «Администратор»:

create login MAdmin

with password ='qwerty',

default_database = MilitaryUnit

create user MAdmin for login MAdmin

В данном коде создаем логин «MAdmin» с паролем «qwerty» у которого стандартная база данных будет MilitaryUnit. Также создаем пользователя «MAdmin» внутри созданной базы данных для логина «MAdmin».

Пользователь «Инженер»:

create login MEngineer

with password ='qwerty',

default_database = MilitaryUnit

create user MEngineer for login MEngineer

Пользователь «Врач»:

create login MDoctor

with password ='qwerty',

default_database = MilitaryUnit

create user MDoctor for login MDoctor

Пользователь «Командир»:

create login MCommander

with password ='qwerty',

default_database = MilitaryUnit

create user MCommander for login MCommander

2.3.3 СОЗДАНИЕ СУЩНОСТЕЙ

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

---

В первую очередь создадим сущность «Объекты» в которой будут находится следующий типы данных:

- Код_Объекта (Тип данных: integer)

- Код_Вооружения (Тип данных: integer)

- Код_Техники (Тип данных: integer)

- Личный_Номер (Тип данных: integer)

Установим, что первичный ключ будет «Код_Объекта»

Выполним код-запрос:

create table Объекты(

Код_Объекта integer not null,

Код_Вооружения integer,

Код_Техники integer,

Личный_Номер integer,

Primary Key(Код_Объекта))



Следующая сущность «Личный_Состав» для нее определим данные и типы данных:

- Личный_Номер (Тип данных: integer)

- Код_Объекта (Тип данных: integer)

- Код_Должности (Тип данных: integer)

- Код_Взвода (Тип данных: integer)

- Фамилия (Тип данных: varchar(45))

- Имя (Тип данных: varchar(45))

- Отчество (Тип данных: varchar(45))

- Звание (Тип данных: varchar(45))

Выполним код-запрос:

create table Личный_Состав(

Личный_Номер integer,

Код_Объекта integer,

Код_Должности integer,

Код_Взвода integer,

Фамилия varchar(45),

Имя varchar(45),

Отчество varchar(45),

Звание varchar(45))

Для сущности «Техника» определим данные и типы данных:

- Код_Техники (Тип данных: integer)

- Номер_Объекта (Тип данных: integer)

- Наименование (Тип данных: varchar(45))

- Номер (Тип данных: integer)

- Наличие_Техосмотра (Тип данных: varchar(45))

Выполним код-запрос:

create table Техника(

Код_Техники integer not null,

Номер_Объекта integer,

Наименование varchar(45),

Номер integer,

Наличие_Техосмотра varchar(45),

Primary Key(Код_Техники))

В данном запросе напротив базовых типов указан параметр «not null», означающий, что данные в этом параметре не должны быть равны значению null.

Для сущности «Оружие» определим данные и типы данных:

- Код_Вооружения (Тип данных: integer)

- Номер_Объекта (Тип данных: integer)

- Наименование (Тип данных: varchar(45))

Выполним код-запрос:

create table Оружие(

Код_Вооружения integer not null,

Номер_Объекта integer,

Наименование varchar(45),

Primary Key(Код_Вооружения))

В данном запросе напротив базовых типов указан параметр «not null», означающий, что данные в этом параметре не должны быть равны значению null.

Для сущности «Должность» определим данные и типы данных:

- Код_Должности (Тип данных:integer)

- Должность (Тип данных: varchar(45))

- Оклад (Тип данных: integer)

Выполним код-запрос:

create table Должность(

---

Код_Должности integer not null,

Должность varchar(45),

Оклад integer,

Primary Key(Код_Должности))

В данном запросе напротив базовых типов указан параметр «not null», означающий, что данные в этом параметре не должны быть равны значению null.

3. СОЗДАНИЕ СХЕМ И РАСПРЕДЕЛЕНИЕ ПРАВ ДОСТУПА

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

Начнем с создания схем. В этапе проектировки, выделили 3 схемы: «ENGINEER», «DOCTOR» и «COMMANDER». Создадим их:

Выполним код-запрос на создание схем:

CREATE SCHEMA Engineer AUTHORIZATION MAdmin

CREATE SCHEMA Doctor AUTHORIZATION MAdmin

CREATE SCHEMA Commander AUTHORIZATION MAdmin

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

alter schema Engineer transfer dbo.Техника

alter schema Engineer transfer dbo.Оружие


alter schema Doctor transfer dbo.Личный_Состав

alter schema Commander transfer dbo.Объекты

alter schema Commander transfer dbo.Должность

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

Для пользователя «Командир» выдадим на схему «COMMANDER» права SUID.

Выполним код-запрос:

GRANT SELECT,INSERT,UPDATE,DELETE

ON SCHEMA::Commander TO MCommander

Так же для этого пользователя выдадим права на просмотр схемы «DOCTOR»:

GRANT SELECT

ON SCHEMA::Doctor TO MCommander

Так же для этого пользователя выдадим права на просмотр схемы «ENGINEER»:

GRANT SELECT

ON SCHEMA::Engineer TO MCommander

Для пользователя «Инженер» выдадим на схему «ENGINEER» права SUID.

Выполним код-запрос:

GRANT SELECT,INSERT,UPDATE,DELETE

ON SCHEMA::Engineer TO MEngineer

Для пользователя «Доктор» выдадим на схему «DOCTOR» права SUID.

Выполним код-запрос:

GRANT SELECT,INSERT,UPDATE,DELETE

ON SCHEMA::Doctor TO MDoctor

4. СОЗДАНИЕ ТРИГГЕРОВ

Триггеры – скрипты, которые при условии определенного действия начинают выполнение заданного цикла. В базе данных будет 3 триггера, имеющих свойства «INSERT, UPDATE, DELETE»

Первый триггер со свойством «DELETE» будет выполнятся при удалении объекта из сущности «Объекты» удаляя следом данные по «Код_Объекта» в таблице «Личный_Состав», по «Код_Вооружения» в таблице «Оружие», по «Код_Техники» в таблице «Техника». Код выглядит следующим образом:

---

create trigger DEL on Commander.Объекты FOR DELETE as

declare @co int

declare @cv int

declare @ct int

begin

select @co = Код_Объекта, @cv = Код_Вооружения, @ct = Код_Техники from deleted

delete from Engineer.Оружие where Код_Вооружения = @cv

delete from Engineer.Техника where Код_Техники = @ct

delete from Doctor.Личный_Состав where Код_Объекта = @co

end

В данном триггере объявляются переменные «@co», хранимая удаленные данные из столбца «Код_Объекта», «@cv», хранимая данные из столбца «Код_Вооружения», «@ct», хранимая данные из столбца «Код_Техники». И по этим данным удаляет строки из других таблиц.

Второй триггер со свойством «INSERT» выполняется при добавлении данных в таблицу «Объекты». Введенные параметры добавляет в таблицы «Оружие», «Техника», «Личный_Состав». Код триггера:

create trigger INS on Commander.Объекты FOR INSERT as

declare @co int

declare @cv int

declare @ct int

begin

select @co = Код_Объекта, @cv = Код_Вооружения, @ct = Код_Техники from inserted

INSERT INTO Engineer.Оружие

VALUES (@cv, NULL, NULL)

INSERT INTO Engineer.Техника

VALUES (@ct, NULL, NULL, NULL, NULL)

INSERT INTO Doctor.Личный_Состав

VALUES (NULL, @co, NULL, NULL, NULL, NULL, NULL, NULL)

End

В данном триггере объявляются 3 переменные, хранящие в себе данные из таблицы «Объекты» и на основе этих данных создаются данные в таблицах «Оружие», «Техника», «Личный_Состав».

Третий и последний триггер со свойством «» при выполнении которого обновляются строки в таблицах: «Оружие», «Техника», «Личный_Состав».

Код триггера:

create trigger UPD on Commander.Объекты FOR UPDATE as

declare @co int

declare @cv int

declare @ct int

begin

select @co = Код_Объекта, @cv = Код_Вооружения, @ct = Код_Техники from inserted

UPDATE Engineer.Оружие

set Код_Вооружения = @cv

UPDATE Engineer.Техника

set Код_Техники = @ct

UPDATE Doctor.Личный_Состав

set Код_Объекта = @co

End

В данном триггере объявлены 3 переменные при заполнении которых, обновляются данные в таблицах «Оружие», «Техника», «Личный_Состав».

3. 1   2   3   4   5   6   7

---