Структура реляционной базы данных
1. Инфологическая модель
В инфологической модели представлены семь
таблиц. В таблице Employe
содержится 18 сущностей, в таблице Departament
3 сущности, в таблице Post
3 сущности, в таблице BuildObject
7 сущностей, в таблице Customer
3 сущности, в таблице Purchases
8 сущностей, в таблице Supplier
3 сущности, в таблице EmpEducation
4 сущности, в таблице Sklad
4 сущности .
Большинство связей между таблицами типа «один к
многим», есть одна связь типа «один к одному».
Рис. 1.1. Диаграмма связей между таблицами.
Описание каждой сущности представлено в
логической модели.
трригер инфологический запрос реляционный
2. Логическая
модель
Таблица «Сотрудники» - Employe
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
сотрудника
|
2
|
SrName
|
char
|
15
|
Фамилия
|
3
|
Name
|
char
|
15
|
Имя
|
4
|
PtName
|
char
|
15
|
Отчество
|
5
|
Passport
|
char
|
12
|
Номер
паспорта
|
6
|
INN
|
char
|
12
|
ИНН
|
7
|
SSGPS
|
char
|
14
|
Номер
страх. свидетельства
|
8
|
BrithDate
|
datetime
|
8
|
Дата
рождения
|
9
|
Address
|
char
|
30
|
Адрес
|
10
|
HomePhone
|
char
|
7
|
Домашний
телефон
|
11
|
MobPhone
|
char
|
11
|
Мобильный
телефон
|
12
|
Sex
|
char
|
3
|
Пол
|
13
|
BeginWDate
|
datetime
|
8
|
Дата
поступления на работу
|
14
|
Dep_ID
|
int
|
4
|
Код
отдела
|
15
|
Educ_ID
|
int
|
4
|
Код
записи в таблице об образовании
|
16
|
Post_ID
|
int
|
4
|
Код
должности
|
17
|
BuildObj_ID
|
int
|
4
|
Код
обьекта строительства
|
18
|
EndWDate
|
datetime
|
8
|
Дата
увольнения с работы
|
Таблица «Отделы» - Departament
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
отдела
|
2
|
Name
|
char
|
80
|
Название
отдела
|
3
|
Address
|
char
|
30
|
Адрес
отдела
|
Таблица «Должности» - Post
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
должности
|
2
|
Name
|
char
|
80
|
Название
должности
|
3
|
Salary
|
money
|
8
|
Оклад
|
Таблица «Объекты строительства» - BuildObject
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
объекта
|
2
|
SrName
|
char
|
200
|
Название
объекта
|
3
|
Discription
|
text
|
16
|
Описание
объекта
|
4
|
Address
|
char
|
30
|
Адрес
объекта
|
5
|
BeginBDate
|
datetime
|
8
|
Дата
начала строительсва
|
6
|
EndBDate
|
datetime
|
8
|
Дата
окончания строительства
|
7
|
Customer_ID
|
int
|
4
|
Код
заказчика
|
Таблица «Заказчики» - Customer
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
заказчика
|
2
|
Name
|
char
|
15
|
Название
|
3
|
EssElem
|
text
|
16
|
Реквизиты
заказчика
|
Таблица «Закупленные cырье
и материалы» - Purchases
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
зделки
|
2
|
Mat_ID
|
int
|
15
|
Код
материала на складе
|
3
|
Price
|
money
|
8
|
Цена
за единицу
|
4
|
Amount
|
int
|
4
|
Количество
куплено
|
5
|
BDate
|
datetime
|
8
|
Дата
зделки
|
6
|
Supplier_ID
|
int
|
4
|
Код
поставщика
|
7
|
Employe_ID
|
int
|
4
|
Код
сотрудника
|
8
|
BuildObj_ID
|
int
|
4
|
Код
обьекта
|
Таблица «Поставщики» - Supplier
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
поставщика
|
2
|
Name
|
char
|
100
|
Название
|
3
|
EssElem
|
text
|
16
|
Реквизиты
|
Таблица «Образование» - EmpEducation
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
записи
|
2
|
EduType
|
char
|
10
|
Уровень
образования
|
3
|
EduName
|
char
|
80
|
Название
учебного заведения
|
4
|
DiplomNum
|
char
|
20
|
Номер
диплома
|
5
|
TermYear
|
char
|
4
|
Год
окончания
|
Таблица «Склад» - Sklad
№
|
Имя
сущности
|
Тип
|
Длина
|
Описание
|
1
|
ID
|
Int
|
4
|
Код
маиериала
|
2
|
Name
|
char
|
200
|
Наименование
|
3
|
Amount
|
Int
|
4
|
Количество
|
4
|
Unit
|
char
|
10
|
Единицы
измерения
|
3. Физическая модель
Логическая структура реляционной базы
разработана на основе инфологической модели и реализована в программе Microsoft
SQL Server 2000. SQL - язык, который дает вам возможность создавать и работать
в реляционных базах данных, которые являются наборами связанной информации
сохраняемой в таблицах.
Базы данных и таблицы созданы с помощью
интерфейса Enterprise Manager в программе Microsoft SQL Server 2000,
программный код которых:
CREATE DATABASE [BuildFirm] ON (NAME
= N'BuildFirm', FILENAME=N'G:\Program Files\Microsoft SQL
Server\MSSQL\data\BuildFirm.mdf' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME =
N'BuildFirm_log', FILENAME = N'G:\Program Files\Microsoft SQL
Server\MSSQL\data\BuildFirm_log.LDF' , FILEGROWTH = 10%)TABLE [BuildObject] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (200) COLLATE
Cyrillic_General_CI_AS NULL ,
[Discription] [text] COLLATE
Cyrillic_General_CI_AS NULL ,
[Address] [char] (30) COLLATE
Cyrillic_General_CI_AS NULL ,
[BeginBDate] [datetime] NULL ,
[EndBDate] [datetime] NULL ,
[Customer_ID] [int] NULL
,[PK_BuildObject] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
,[FK_BuildObject_Customer] FOREIGN KEY
(
[Customer_ID]
) REFERENCES [Customer] (
[ID]
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]TABLE [Customer] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (80) COLLATE
Cyrillic_General_CI_AS NULL ,
[EssElem] [text] COLLATE
Cyrillic_General_CI_AS NULL ,[PK_Customer] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]TABLE [Departament] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (80) COLLATE
Cyrillic_General_CI_AS NULL ,
[Address] [char] (30) COLLATE
Cyrillic_General_CI_AS NULL ,[PK_Departaments] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]TABLE [EmpEducation] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[EduType] [char] (10) COLLATE
Cyrillic_General_CI_AS NULL ,
[EduName] [char] (80) COLLATE
Cyrillic_General_CI_AS NULL ,
[DiplomNum] [char] (20) COLLATE
Cyrillic_General_CI_AS NULL ,
[TermYear] [char] (4) COLLATE
Cyrillic_General_CI_AS NULL ,[PK_EmpEducation] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]TABLE [Employe] (
[ID] [int] NOT NULL ,
[SrName] [char] (15) COLLATE
Cyrillic_General_CI_AS NULL ,
[Name] [char] (15) COLLATE
Cyrillic_General_CI_AS NULL ,
[PtName] [char] (15) COLLATE
Cyrillic_General_CI_AS NULL ,
[Passport] [char] (12) COLLATE
Cyrillic_General_CI_AS NULL ,
[INN] [char] (12) COLLATE
Cyrillic_General_CI_AS NULL ,
[SSGPS] [char] (14) COLLATE
Cyrillic_General_CI_AS NULL ,
[BrithDate] [datetime] NULL ,
[Address] [char] (30) COLLATE
Cyrillic_General_CI_AS NULL ,
[HomePhone] [char] (7) COLLATE
Cyrillic_General_CI_AS NULL ,
[MobPhone] [char] (11) COLLATE
Cyrillic_General_CI_AS NULL ,
[Sex] [char] (3) COLLATE
Cyrillic_General_CI_AS NULL ,
[BeginWDate] [datetime] NULL ,
[Dep_ID] [int] NULL ,
[Post_ID] [int] NULL ,
[Educ_ID] [int] NULL ,
[BuildObj_ID] [int] NULL ,
[EndWDate] [datetime] NULL
,[PK_Employe] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
,[FK_Employe_BuildObject] FOREIGN KEY
(
[BuildObj_ID]
) REFERENCES [BuildObject] (
[ID]
),[FK_Employe_Departaments] FOREIGN
KEY
(
[Dep_ID]
) REFERENCES [Departament] (
[ID]
),[FK_Employe_Post] FOREIGN KEY
(
[Post_ID]
) REFERENCES [Post] (
[ID]
)
) ON [PRIMARY]TABLE [Post] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (80) COLLATE
Cyrillic_General_CI_AS NULL ,
[Salary] [money] NULL ,[PK_Post]
PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]TABLE [Purchases] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Mat_ID] [int] NULL ,
[Price] [money] NULL ,
[Amount] [int] NULL ,
[BDate] [datetime] NULL ,
[Supplier_ID] [int] NULL ,
[Employe_ID] [int] NULL ,
[BuildObj_ID] [int] NULL ,[PK_Material]
PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
,[FK_Material_BuildObject] FOREIGN KEY
(
[BuildObj_ID]
) REFERENCES [BuildObject] (
[ID]
),[FK_Material_Employe] FOREIGN KEY
(
[Employe_ID]
) REFERENCES [Employe] (
[ID]
),[FK_Material_Supplier] FOREIGN KEY
(
[Supplier_ID]
) REFERENCES [Supplier] (
[ID]
),[FK_Purchases_Sklad] FOREIGN KEY
(
[Mat_ID]
) REFERENCES [Sklad] (
[ID]
)
) ON [PRIMARY]TABLE [Sklad] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (200) COLLATE
Cyrillic_General_CI_AS NULL ,
[Amount] [int] NULL ,
[Unit] [char] (10) COLLATE
Cyrillic_General_CI_AS NULL ,[PK_Warehouse] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]TABLE [Supplier] (
[ID] [int] IDENTITY (1, 1) NOT NULL
,
[Name] [char] (100) COLLATE
Cyrillic_General_CI_AS NULL ,
[EssElem] [text] COLLATE
Cyrillic_General_CI_AS NULL ,[PK_Supplier] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]
Добавим одну запись в таблицу Employe
с
помощью интерфейса QueryAnalyzer:
INSERT INTO Employe (SrName, Name,
PtName, Passport, INN, SSGPS, BrithDate, Address, HomePhone, MobPhone, Sex,
BeginWDate, Dep_ID, Post_ID, BuildObj_ID)('Каганович',
'Лазар',
'Моисеевич',
'97 97 098743', '267095424567', '678-786-675-98', '07.05.1981', 'пр.
Тракторостроителей
34-56', '235687', '89179876534', 'муж', '09.10.2001', 1, 1, 1)
4. Разработка
запросов, процедур, триггеров
.1 Создание представлений
Рис. 4.1. Представление из 6 таблиц
CREATE
VIEW dbo.VIEW1
AS SELECT dbo.Employe.SrName AS Фамилия,
dbo.Employe.Name AS Имя,
dbo.Employe.PtName AS Отчество,
dbo.Departament.Name AS Отдел,.Post.Name
AS Должность,
dbo.EmpEducation.EduType AS образование,
dbo.EmpEducation.EduName AS [Учебное
заведение],.BuildObject.Name
AS Объект,
dbo.Customer.Name AS [Заказчик
объекта]dbo.Employe
INNER JOIN.Departament ON dbo.Employe.Dep_ID = dbo.Departament.ID INNER
JOIN.Post ON dbo.Employe.Post_ID = dbo.Post.ID INNER JOIN.EmpEducation ON
dbo.Employe.Educ_ID = dbo.EmpEducation.ID INNER JOIN.BuildObject ON
dbo.Employe.BuildObj_ID = dbo.BuildObject.ID INNER JOIN.Customer ON
dbo.BuildObject.Customer_ID = dbo.Customer.ID
Рис. 4.2. Представление из 5 таблиц
CREATE
VIEW dbo.VIEW2
AS SELECT dbo.Sklad.Name AS Наименование,
dbo.Purchases.Price AS Цена,
dbo.Purchases.Amount AS Количество,
dbo.Purchases.BDate AS [Дата закупки],.Supplier.Name
AS Поставщик,
dbo.Employe.SrName AS Фамилия,
dbo.Employe.Name AS Имя,
dbo.Employe.PtName AS Отчество,.BuildObject.Name
AS Объектdbo.Purchases
INNER JOIN.Sklad ON dbo.Purchases.Mat_ID = dbo.Sklad.ID INNER JOIN.Supplier ON
dbo.Purchases.Supplier_ID = dbo.Supplier.ID INNER JOIN.Employe ON
dbo.Purchases.Employe_ID = dbo.Employe.ID INNER JOIN.BuildObject ON
dbo.Purchases.BuildObj_ID = dbo.BuildObject.ID
4.2 Создание триггеров
Данный триггер отвечает за автоматическое
увеличение количества материалов на складе при их закупках, т.е при внесении
данных в таблицу Purchases(Закупки)
данный триггер увеличивает имеющееся количество материала в таблице Sklad
на величину закупленного количества.
IF EXISTS(SELECT name FROM
sysobjectsname = 'IncSkladAmount' and type = 'TR')TRIGGER IncSkladAmount
CREATE TRIGGER IncSkladAmountPurchases FOR
INSERT DECLARE
@amt int@amt = (SELECT inserted.Amount FROM inserted)@amt = (SELECT
Sklad.Amount FROM Sklad WHERE.ID = ALL(SELECT inserted.Mat_ID FROM inserted))+
@amtSkladSklad.Amount = @amtSklad.ID = ALL(SELECT inserted.Mat_ID FROM
inserted)
.3 Создание
процедур
Процедура ниже выполняет вставку новой записи в
таблицу Purchase
(Закупки). В случае если покупаемый материал не найден на складе, в таблицу Sklad
(Склад) заносится название нового материала и единицы измерения его количества,
затем заносится информация в таблицу Purchases.
Далее за изменение количества материала на складе отвечает триггер IncSkladAmount.
CREATE PROCEDURE AddToPurchase
@Name char(200), @Amount int, @Unit
char(10),
@Price money, @BDate datetime,
@Supplier_ID int,
@Employe_ID int, @BuildObj_ID
int@tmp_id intNOT EXISTS (SELECT * FROM Sklad WHERE Sklad.Name = @Name)INTO
Sklad (Name, Amount, Unit)(@Name, 0, @Unit)@tmp_id = (SELECT Sklad.ID FROM
Sklad WHERE Sklad.Name = @Name)INTO Purchases (Mat_ID, Price, Amount,
BDate,_ID, Employe_ID, BuildObj_ID)(@tmp_id, @Price, @Amount, @BDate,
@Supplier_ID,
@Employe_ID, @BuildObj_ID)@tmp_id =
(SELECT Sklad.ID FROM Sklad WHERE Sklad.Name = @Name)INTO Purchases (Mat_ID,
Price, Amount, BDate,_ID, Employe_ID, BuildObj_ID)(@tmp_id, @Price, @Amount,
@BDate, @Supplier_ID,
@Employe_ID, @BuildObj_ID)
Следующая процедура использует механизм курсоров
T-SQL.
Она возвращает список сотрудников, имеющих высшее образование.
CREATE PROCEDURE
GetTopEducEmpm_cursor CURSOR FOREmploye.SrName, Employe.Name,
Employe.PtName,.EduTypeEmploye INNER JOIN EmpEducation ON
Employe.Educ_ID=EmpEducation.IDBY Employe.SrName, Employe.Name, Employe.PtName,
EmpEducation.EduTypem_cursor@m_srname VARCHAR(15), @m_name VARCHAR(15),
@m_ptname VARCHAR(15), @m_var VARCHAR(10)NEXT FROM m_cursor INTO @m_srname,
@m_name, @m_ptname, @m_var@@fetch_status=0(@m_var = 'высшее')@m_srname+'
'+@m_name+' '+@m_ptnameNEXT FROM m_cursor INTO @m_srname, @m_name, @m_ptname,
@m_var
CLOSE
m_cursor
5. Запросы на выборку записей
5.1 Запрос на выборку записей,
удовлетворяющих некоторому условию с использованием логической операции
проверки на вхождение в диапазон
SELECT * FROM EmployeYEAR(BrithDate)
BETWEEN 1980 AND 1990
Возвращает сотрудников родившихся в период с
1980 по 1990 годы.
5.2 Запрос на выборку записей,
удовлетворяющих некоторому условию с использованием логической операции
проверки на вхождение в множество + запрос на выборку записей из таблицы,
являющейся результатом соединения двух таблиц по некоторому условию
SELECT Employe.SrName, Employe.Name,
Employe.PtName, Departament.Name as DepNameEmploye INNER JOIN
DepartamentEmploye.Dep_ID=Departament.IDDepartament.Name IN ('Управление',
'Отдел
снабжения',
'СУ-1')
Возвращает список сотрудников работающих в
соответствующих отделах
5.3 Запрос с использованием
агрегатных функций с применением группировки + запрос на выборку записей с
условием сортировки
SELECT Departament.Name AS [Отдел],
COUNT(Employe.Dep_ID) AS [Количество
сотрудников]Departament
INNER JOIN Employe ON.ID = Employe.Dep_IDBY Departament.Name
ORDER BY Departament.Name
Возвращает список отделов и количество
сотрудников работающих на каждом отделе с сортировкой отделов по убыванию.
5.4 Вложенный запрос на выборку
записей, в том числе с использованием предикатов EXIST,
ANY,
ALL
SELECT Employe.PtName, Employe.Name,
Employe.SrName, Post.NameEmploye INNER JOIN Post ON (Employe.Post_ID =
Post.ID)EXISTS(SELECT Post.SalaryPost(Salary>=30000) AND
(Post.ID=Employe.Post_ID))
Выводит сотрудников, оклад которых превышает
либо равен 30000.
триггер инфологический запрос
реляционный