Разработка базы данных «Проектная организация»

 

Сущность «Преподаватели»:

 

Каждый экземпляр – один преподаватель  зарегистрированный в библиотеке - то есть связан с одним экземпляром  сущности «Читатели».

 

Атрибут	Внешний ключ	Описание атрибута
id_Преподавателя	+	Уникальный номер преподавателя
Учебное заведение		Учебное заведение
id_Читателя		Номер читателя

 

 

Сущность «Пенсионеры»:

 

Каждый экземпляр – один пенсионер  зарегистрированный в библиотеке - то есть связан с одним экземпляром  сущности «Читатели».

 

Атрибут	Внешний ключ	Описание атрибута
id_Пенсионера	+	Уникальный номер пенсионера
Номер пенсионного		Номер пенсионного удостоверения
id_Читателя		Номер читателя

 

Сущность «Научные работники»:

 

Каждый экземпляр – один научный  работник зарегистрированный в библиотеке - то есть связан с одним экземпляром  сущности «Читатели».

 

Атрибут	Внешний ключ	Описание атрибута
id_Научного работника	+	Уникальный номер научного работника
Название организации		Название организации
Научная тема		Научная тема
id_Читателя		Номер читателя

 

Сущность «Рабочие»:

 

Каждый экземпляр – один рабочий  зарегистрированный в библиотеке - то есть связан с одним экземпляром  сущности «Читатели».

 

Атрибут	Внешний ключ	Описание атрибута
id_Рабочего	+	Уникальный номер рабочего
Место работы		Место работы
id_Читателя		Номер читателя

 

Сущность «Движение книги»:

 

Каждый экземпляр – сочетание  экземпляров сущностей «Сотрудники»,  «Книги», «Читатели» и «Библиотека»  таким образом хранится информация о том, какая книга из какой библиотеки и каким сотрудником была выдана и каким читателем была взята.

 

Атрибут	Внешний ключ	Описание атрибута
id_Сотрудника	+	Номер сотрудника - внешний ключ из сущности «Сотрудники»
id_Книги	+	Номер книги - внешний ключ из сущности «Книги»
id_Читателя	+	Номер читателя - внешний ключ из сущности «Читатели»
id_Зала	+	Номер зала - внешний ключ из сущности «Библиотека»
Дата получения		Дата сдачи книги
Дата когда надо сдать		Дата когда надо сдать книгу
Состояние		Сдана или не сдана книга

 

Сущность «Расположение книги»:

 

Каждый экземпляр – сочетание  экземпляров сущностей «Книги»  и «Библиотека»  таким образом  хранится информация о том, какая  книга и в какой библиотеке на каком стеллаже и на какой полке находится.

 

Атрибут	Внешний ключ	Описание атрибута
id_Книги	+	Номер книги - внешний ключ из сущности «Книги»
id_Зала	+	Номер зала - внешний ключ из сущности «Библиотека»
id_Стеллажа		Номер стеллажа где находится книга
id_Полки		Номер полки где находится книга

 

ОПИСАНИЕ СВЯЗЕЙ МЕЖДУ  СУЩНОСТЯМИ:

 

1. Связь между сущностями «Библиотека» и «Сотрудники» ставит в соответствие каждому сотруднику библиотеку, в который он работает посредством передачи ключевого атрибута «id_Зала» в сущность «Сотрудники», где он уже не является ключевым, поскольку разные сотрудники могут числиться в Библиотеке.
2. Связь между сущностями «Категории» и «Книги» ставит в соответствие каждой книги свою категорию, которой она принадлежит посредством передачи ключевого атрибута «id_Категории» в сущность «Книги», где он уже не является ключевым, поскольку разные книги могут принадлежать одной категории.
3. Связь между сущностями «Читатели» и «Студенты» ставит в соответствие каждому студенту свою карточку читателя, которой он принадлежит посредством передачи ключевого атрибута «id_Читателя» в сущность «Студенты», где он уже не является ключевым.
4. Атрибут «id_Читателя» аналогично передается в сущности «Школьники», «Пенсионеры», «Рабочие», «Научные работники», «Преподаватели».
5. Связи между сущностью «Движение книги» и сущностями «Сотрудники», «Читатели», «Книги», «Библиотека» ставят в соответствие какая книга из какой библиотеки и каким сотрудникам была выдана и каким читателем была взята посредствам передачи ключевых атрибутов в сущность «Движение книги», где они так же являются ключевыми, так как одну книгу в определенный момент времени может выдать только один сотрудник, взять один читатель и из одной библиотеки.
6. Связи между сущностью «Расположение книги» и сущностями «Книги», «Библиотека» ставят в соответствие где определенная книга находится посредствам передачи ключевых атрибутов в сущность «Расположение книги», где они так же являются ключевыми, так как одну книгу в определенный момент может находится только в одной библиотеке.

 

 

С помощью CASE-средства ERWin была создана следующая модель (Рис.1):

 

Рисунок 1. Логическая схема данных

 

2. концептуальная моделЬ БАЗЫ ДАННЫХ

2.1. КОНЦЕПТУАЛЬНАЯ СХЕМА БАЗЫ ДАННЫХ

 

Между сущностями установлены все  необходимые связи. Связи многие-ко-многим разрешены с помощью дополнительных сущностей «Выполнение проектов», «Работа над проектами», «Использование оборудования». Определив тип данных для каждого атрибута, получили физическую модель данных:

 

Рисунок 2. Физическая модель данных

После этого по полученной схеме  генерируем базу данных в MS SQL Server. В результате получим базу данных со следующей схемой:

Рисунок 3. Схема БД в MS SQL Server

2.2. ОГРАНИЧЕНИЕ ЦЕЛОСТНОСТИ И СОГЛАСОВАННОСТИ БД

 

Определив количество и состав таблиц, следует продумать алгоритмы  контроля целостности и согласованности  БД. Основные ограничения на вводимые данные накладывают типы данных атрибутов и связи отношений.

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

Триггер, не позволяющий записать у сотрудника несуществующую библиотеку:

 

USE [Библиотечный_фонд]

GO

/****** Object:  Trigger [dbo].[sotrudniki]    Script Date: 12/26/2011 02:00:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[sotrudniki]

   ON  [dbo].[Сотрудники]

   AFTER INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @num int;

SELECT @num = id_Зала FROM inserted;

if not exists(SELECT * FROM Библиотека WHERE id_Зала = @num)

begin

DELETE FROM Сотрудники WHERE id_Зала = @num;  

end;

    -- Insert statements for trigger here

 

END

 

Триггер, не позволяющий записать несуществующую категорию книге:

 

 

 

USE [Библиотечный_фонд]

GO

/****** Object:  Trigger [dbo].[Knigi]    Script Date: 12/26/2011 02:02:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[Knigi]

   ON  [dbo].[Книги]

   AFTER INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @num int;

SELECT @num = id_Категории FROM inserted;

if not exists(SELECT * FROM Категория WHERE id_Категории = @num)

begin

DELETE FROM Книги WHERE id_Категории = @num;  

end;

    -- Insert statements for trigger here

 

END

Триггер, не позволяющий записать студентом несуществующего читателя:

 

 

USE [Библиотечный_фонд]

GO

/****** Object:  Trigger [dbo].[Studenti]    Script Date: 12/26/2011 02:04:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[Studenti]

   ON  [dbo].[Студенты]

   AFTER INSERT,UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @num int;

SELECT @num = id_Читателя FROM inserted;

if not exists(SELECT * FROM Читатели WHERE id_Читателя = @num)

begin

DELETE FROM Студенты WHERE id_Читателя = @num;  

end;

 

 

 

    -- Insert statements for trigger here

 

END

 

2.3. ОПЕРАЦИИ  РЕЛЯЦИОННОЙ АЛГЕБРЫ. SQL-ЗАПРОСЫ.

 

 

1. Список библиотекарей:

 

Z = π _{Название, Зал, ФИО_Сотр, id_Сотрудника, id_Зала}(σ _{(id_Зала = < id_Зала >)˄( Название = < Название >)} (Бибилиотека*Сотрудники))

 

SELECT  Название, Зал, ФИО_Сотр, id_Сотрудника, Библиотека.id_Зала

  FROM Библиотека, Сотрудники

  WHERE (Сотрудники.id_Зала like '+ Edit2.Text+') and (Библиотека.Зал like '''+ Edit3.Text+''') and (Библиотека.Название like '''+ Edit1.Text+''')

 

2. Список выданной литературы:

 

Z = π _{id_Книги, Тип_книги, Состояние, id_Полки} (σ _{(Состояние = < Состояние >)˄( id_полки = < id_полки >)} (Книги*Расположение книги*Движение книги))

 

SELECT Книги.id_Книги, Книги.Тип_книги,  Движение_книги.Состояние, Расположение_книг.id_Полки

  FROM Книги INNER JOIN Расположение_книг ON Книги.id_Книги = Расположение_книг.id_Книги  INNER JOIN Движение_книги ON Книги.id_Книги = Движение_книги.id_Книги

  WHERE (Движение_книги.Состояние like '''+ Edit2.Text+''') and (id_полки = '+ Edit1.Text+') 

3. Перечень читателей с выданной литературой по названию:

 

Z = π _{Тип_читателя, ФИО,  id_Книги, id_Читателя, Состояние,  Название, id_Произведения} (σ _{(Состояние = < Состояние >)˄( Название = < Название >)} (Книги*Движение книги*Читатели*Произведения))

 

SELECT Читатели.Тип_читателя, Читатели.ФИО,  Книги.id_Книги, Движение_книги.id_Читателя, Движение_книги.Состояние,  Произведение.Название, Книги.id_Произведения

  FROM Книги INNER JOIN Движение_книги ON Книги.id_Книги = Движение_книги.id_Книги INNER JOIN Читатели ON Движение_книги.id_Читателя = Читатели.id_Читателя INNER JOIN Произведение ON Книги.id_Произведения = Произведение.id_Произведения

  WHERE (Движение_книги.Состояние like '''+ Edit2.Text+''') and (Название like '''+ Edit1.Text+''')

 

 

4. Перечень читателей с выданной литературой по типу:

 

Z = π _{Тип_читателя, ФИО, id_Книги, id_Читателя, Состояние, Тип_книги} (σ _{(Состояние = < Состояние >)˄( Тип  книги= < Тип книги >)} (Книги*Движение книги*Читатели))

 

SELECT Читатели.Тип_читателя, Читатели.ФИО,  Книги.id_Книги, Движение_книги.id_Читателя, Движение_книги.Состояние, Книги.Тип_книги

  FROM Книги INNER JOIN Движение_книги  ON Книги.id_Книги =  Движение_книги.id_Книги  INNER JOIN Читатели ON  Движение_книги.id_Читателя = Читатели.id_Читателя'

  WHERE (Движение_книги.Состояние like '''+ Edit2.Text+''') and (Тип_книги like '''+ Edit1.Text+''') 

 

5. Перечень читателей с выданной литературой по дате:

 

Z = π _{Тип_читателя, ФИО, id_Книги, Состояние, Название, Тип_книги, Дата_когда_надо_сдать} (σ _{(<дата>=< Дата_когда надо сдать  >=<дата>)˄ (Название = <Название>)} (Книги*Движение книги*Читатели*Произведение))

 

SELECT Читатели.Тип_читателя, Читатели.ФИО,  Книги.id_Книги, Движение_книги.Состояние,  Произведение.Название, Книги.Тип_книги,  Движение_книги.Дата_когда_надо_сдать

  FROM Книги INNER JOIN Движение_книги ON Книги.id_Книги = Движение_книги.id_Книги INNER JOIN Читатели ON Движение_книги.id_Читателя =  Читатели.id_Читателя INNER JOIN Произведение ON Книги.id_Произведения = Произведение.id_Произведения'

  WHERE (Движение_книги.Дата_когда_надо_сдать BETWEEN  '''+ Edit2.Text+'''and '''+ Edit3.Text+''' ) and (Название like '''+ Edit1.Text+''')

 

6. Список читателей у определенного сотрудника:

 

Z= π _{Тип_читателя, ФИО, id_Сотрудника, id_Читателя, ФИО_Сотр, Дата_когда_надо_сдать} (σ _{(<дата>=< Дата_когда надо сдать  >=<дата>)˄ (ФИО Сотр = < ФИО Сотр >)} (Движение книги*Читатели*Сотрудники))

 

SELECT Читатели.Тип_читателя, Читатели.ФИО,  Движение_книги.id_Сотрудника, Движение_книги.id_Читателя, Сотрудники.ФИО_Сотр,Движение_книги.Дата_когда_надо_сдать

  FROM  Движение_книги INNER JOIN Читатели ON Движение_книги.id_Читателя = Читатели.id_Читателя INNER JOIN Сотрудники ON Движение_книги.id_Сотрудника = Сотрудники.id_Сотрудника

  WHERE (ФИО_Сотр like '''+Edit1.Text+''') and (Движение_книги.Дата_когда_надо_сдать BETWEEN  '''+ Edit2.Text+'''and '''+ Edit3.Text+''' )

 

7. Список должников:

 

Z= π _{Тип_читателя, ФИО, id_Читателя, Дата_когда_надо_сдать, Дата_получения} (σ _{(<дата>=< Дата_когда надо сдать  >=<Дата получения>)}(Движение книги*Читатели*Сотрудники))

 

SELECT Читатели.Тип_читателя, Читатели.ФИО,  Движение_книги.id_Читателя,Движение_книги.Дата_когда_надо_сдать,Движение_книги.Дата_получения

  FROM Движение_книги INNER JOIN Читатели ON  Движение_книги.id_Читателя = Читатели.id_Читателя

  WHERE Движение_книги.Дата_когда_надо_сдать BETWEEN '''+ Edit2.Text+''' and Движение_книги.Дата_получения'