Структура реляционной базы данных

1. Инфологическая модель

В инфологической модели представлены семь таблиц. В таблице Employe содержится 18 сущностей, в таблице Departament 3 сущности, в таблице Post 3 сущности, в таблице BuildObject 7 сущностей, в таблице Customer 3 сущности, в таблице Purchases 8 сущностей, в таблице Supplier 3 сущности, в таблице EmpEducation 4 сущности, в таблице Sklad 4 сущности .

Большинство связей между таблицами типа «один к многим», есть одна связь типа «один к одному».

Рис. 1.1. Диаграмма связей между таблицами.

Описание каждой сущности представлено в логической модели.

трригер инфологический запрос реляционный

2. Логическая модель

Таблица «Сотрудники» — Employe

Таблица «Отделы» — Departament

Таблица «Должности» — Post

Таблица «Объекты строительства» — BuildObject

Таблица «Заказчики» — Customer

Таблица «Закупленные cырье и материалы» — Purchases

Таблица «Поставщики» — Supplier

Таблица «Образование» — EmpEducation

Таблица «Склад» — Sklad

3. Физическая модель

Логическая структура реляционной базы разработана на основе инфологической модели и реализована в программе Microsoft SQL Server 2000. SQL — язык, который дает вам возможность создавать и работать в реляционных базах данных, которые являются наборами связанной информации сохраняемой в таблицах.

Базы данных и таблицы созданы с помощью интерфейса Enterprise Manager в программе Microsoft SQL Server 2000, программный код которых:

CREATE DATABASE [BuildFirm] ON (NAME = N’BuildFirm', FILENAME=N'G:Program FilesMicrosoft SQL ServerMSSQLdataBuildFirm. mdf', SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N’BuildFirm_log', FILENAME = N’G:Program FilesMicrosoft SQL ServerMSSQLdataBuildFirm_log.LDF', FILEGROWTH = 10%)

GO

CREATE 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 ,

CONSTRAINT [PK_BuildObject] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY] ,

CONSTRAINT [FK_BuildObject_Customer] FOREIGN KEY

(

[Customer_ID]

) REFERENCES [Customer] (

[ID]

)

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Departaments] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_EmpEducation] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Employe] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY] ,

CONSTRAINT [FK_Employe_BuildObject] FOREIGN KEY

(

[BuildObj_ID]

) REFERENCES [BuildObject] (

[ID]

),

CONSTRAINT [FK_Employe_Departaments] FOREIGN KEY

(

[Dep_ID]

) REFERENCES [Departament] (

[ID]

),

CONSTRAINT [FK_Employe_Post] FOREIGN KEY

(

[Post_ID]

) REFERENCES [Post] (

[ID]

)

) ON [PRIMARY]

GO

CREATE TABLE [Post] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[Name] [char] (80) COLLATE Cyrillic_General_CI_AS NULL ,

[Salary] [money] NULL ,

CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY] ,

CONSTRAINT [FK_Material_BuildObject] FOREIGN KEY

(

[BuildObj_ID]

) REFERENCES [BuildObject] (

[ID]

),

CONSTRAINT [FK_Material_Employe] FOREIGN KEY

(

[Employe_ID]

) REFERENCES [Employe] (

[ID]

),

CONSTRAINT [FK_Material_Supplier] FOREIGN KEY

(

[Supplier_ID]

) REFERENCES [Supplier] (

[ID]

),

CONSTRAINT [FK_Purchases_Sklad] FOREIGN KEY

(

[Mat_ID]

) REFERENCES [Sklad] (

[ID]

)

) ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Warehouse] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE 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 ,

CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Добавим одну запись в таблицу Employe с помощью интерфейса QueryAnalyzer:

INSERT INTO Employe (SrName, Name, PtName, Passport, INN, SSGPS, BrithDate, Address, HomePhone, MobPhone, Sex, BeginWDate, Dep_ID, Post_ID, BuildObj_ID)

VALUES ('Каганович', 'Лазар', 'Моисеевич', '97 97 98 743', '267 095 424 567', '678−786−675−98', '07.05.1981', 'пр. Тракторостроителей 34−56', '235 687', '89 179 876 534', 'муж', '09.10.2001', 1, 1, 1)

4. Разработка запросов, процедур, триггеров

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 Отдел,

dbo.Post.Name AS Должность, dbo.EmpEducation.EduType AS образование, dbo.EmpEducation.EduName AS [Учебное заведение],

dbo.BuildObject.Name AS Объект, dbo.Customer.Name AS [Заказчик объекта]

FROM dbo. Employe INNER JOIN

dbo.Departament ON dbo.Employe.Dep_ID = dbo.Departament.ID INNER JOIN

dbo.Post ON dbo.Employe.Post_ID = dbo.Post.ID INNER JOIN

dbo.EmpEducation ON dbo.Employe.Educ_ID = dbo.EmpEducation.ID INNER JOIN

dbo.BuildObject ON dbo.Employe.BuildObj_ID = dbo.BuildObject.ID INNER JOIN

dbo.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 [Дата закупки],

dbo.Supplier.Name AS Поставщик, dbo.Employe.SrName AS Фамилия, dbo.Employe.Name AS Имя, dbo.Employe.PtName AS Отчество,

dbo.BuildObject.Name AS Объект

FROM dbo. Purchases INNER JOIN

dbo.Sklad ON dbo.Purchases.Mat_ID = dbo.Sklad.ID INNER JOIN

dbo.Supplier ON dbo.Purchases.Supplier_ID = dbo.Supplier.ID INNER JOIN

dbo.Employe ON dbo.Purchases.Employe_ID = dbo.Employe.ID INNER JOIN

dbo.BuildObject ON dbo.Purchases.BuildObj_ID = dbo.BuildObject.ID

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

Данный триггер отвечает за автоматическое увеличение количества материалов на складе при их закупках, т. е при внесении данных в таблицу Purchases (Закупки) данный триггер увеличивает имеющееся количество материала в таблице Sklad на величину закупленного количества.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'IncSkladAmount' and type = 'TR')

DROP TRIGGER IncSkladAmount

GO

CREATE TRIGGER IncSkladAmount

ON Purchases FOR INSERT

AS

DECLARE @amt int

SET @amt = (SELECT inserted. Amount FROM inserted)

SET @amt = (SELECT Sklad. Amount FROM Sklad WHERE

Sklad.ID = ALL (SELECT inserted. Mat_ID FROM inserted))

+ @amt

UPDATE Sklad

SET Sklad. Amount = @amt

WHERE Sklad. ID = ALL (SELECT inserted. Mat_ID FROM inserted)

4.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

AS

DECLARE @tmp_id int

IF NOT EXISTS (SELECT * FROM Sklad WHERE Sklad.Name = @Name)

BEGIN

INSERT INTO Sklad (Name, Amount, Unit)

VALUES (@Name, 0, @Unit)

SET @tmp_id = (SELECT Sklad. ID FROM Sklad WHERE Sklad.Name = @Name)

INSERT INTO Purchases (Mat_ID, Price, Amount, BDate,

Supplier_ID, Employe_ID, BuildObj_ID)

VALUES (@tmp_id, @Price, @Amount, @BDate, @Supplier_ID,

@Employe_ID, @BuildObj_ID)

END

ELSE

BEGIN

SET @tmp_id = (SELECT Sklad. ID FROM Sklad WHERE Sklad.Name = @Name)

INSERT INTO Purchases (Mat_ID, Price, Amount, BDate,

Supplier_ID, Employe_ID, BuildObj_ID)

VALUES (@tmp_id, @Price, @Amount, @BDate, @Supplier_ID,

@Employe_ID, @BuildObj_ID)

END

Следующая процедура использует механизм курсоров T-SQL. Она возвращает список сотрудников, имеющих высшее образование.

CREATE PROCEDURE GetTopEducEmp

AS

DECLARE m_cursor CURSOR FOR

SELECT Employe. SrName, Employe.Name, Employe. PtName,

EmpEducation.EduType

FROM Employe INNER JOIN EmpEducation ON Employe. Educ_ID=EmpEducation.ID

GROUP BY Employe. SrName, Employe.Name, Employe. PtName, EmpEducation. EduType

OPEN m_cursor

DECLARE @m_srname VARCHAR (15), @m_name VARCHAR (15), @m_ptname VARCHAR (15), @m_var VARCHAR (10)

FETCH NEXT FROM m_cursor INTO @m_srname, @m_name, @m_ptname, @m_var

WHILE @@fetch_status=0

BEGIN

IF (@m_var = 'высшее')

PRINT @m_srname+' '+@m_name+' '+@m_ptname

FETCH NEXT FROM m_cursor INTO @m_srname, @m_name, @m_ptname, @m_var

END

CLOSE m_cursor

5. Запросы на выборку записей

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

SELECT * FROM Employe

WHERE YEAR (BrithDate) BETWEEN 1980 AND 1990

Возвращает сотрудников родившихся в период с 1980 по 1990 годы.

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

SELECT Employe. SrName, Employe.Name, Employe. PtName, Departament.Name as DepName

FROM Employe INNER JOIN Departament

ON Employe. Dep_ID=Departament.ID

WHERE Departament.Name IN ('Управление', 'Отдел снабжения', 'СУ-1')

Возвращает список сотрудников работающих в соответствующих отделах

5.3 Запрос с использованием агрегатных функций с применением группировки + запрос на выборку записей с условием сортировки

SELECT Departament.Name AS [Отдел], COUNT (Employe.Dep_ID) AS [Количество сотрудников]

FROM Departament INNER JOIN Employe ON

Departament.ID = Employe. Dep_ID

GROUP BY Departament.Name

ORDER BY Departament.Name

Возвращает список отделов и количество сотрудников работающих на каждом отделе с сортировкой отделов по убыванию.

5.4 Вложенный запрос на выборку записей, в том числе с использованием предикатов EXIST, ANY, ALL

SELECT Employe. PtName, Employe.Name, Employe. SrName, Post.Name

FROM Employe INNER JOIN Post ON (Employe.Post_ID = Post. ID)

WHERE EXISTS (SELECT Post. Salary

FROM Post

WHERE (Salary>=30 000) AND (Post.ID=Employe.Post_ID))

Выводит сотрудников, оклад которых превышает либо равен 30 000.