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

Размещено на http://allbest.ru/

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%)

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 098743', '267095424567', '678-786-675-98', '07.05.1981', 'пр. Тракторостроителей 34-56', '235687', '89179876534', 'муж', '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>=30000) AND (Post.ID=Employe.Post_ID))

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



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

Размещено на Allbest.ru