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

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

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

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