Здавалка
Главная | Обратная связь

ВЗАИМОДЕЙСТВИЕ С БД

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Ивановский государственный энергетический университет имени В.И. Ленина»

 

Кафедра ПОКС

Отчет по курсу «Базы Данных»

«Проектная организация»

 

Выполнил:

студент группы III-42

Сенченко А.А.

Проверил:

Игнатьева Е.Е.

 

 

Иваново 2011

Оглавление

ПРЕДМЕТНАЯ ОБЛАСТЬ. 3

ОПИСАНИЕ. 3

МОДЕЛЬ БАЗЫ ДАННЫХ. 4

ВЗАИМОДЕЙСТВИЕ С БД.. 7

SQL запросы.. 7

Хранимые процедуры.. 8

Триггеры.. 9

ПРОГРАММА ДЛЯ РАБОТЫ.. 10

 

ПРЕДМЕТНАЯ ОБЛАСТЬ

Проектная организация представлена следующими категориями сотрудников: конструкторы, инженеры, техники; каждая категория может иметь свойственные только ей атрибуты. Конструктор характеризуется числом авторских свидетельств, инженер – сроком сертификата на выполнение проектных работ, техники – оборудованием, которое они могут обслуживать. Сотрудники разделены на отделы, так что каждый сотрудник числиться только в одном отделе.

В рамках заключаемых проектной организацией договоров с заказчиками выполняются различного рода проекты, при чем по одному договору может выполняться более одного проекта, и один проект может выполняться для нескольких договоров. Суммарная стоимость договора определяется стоимостью всех проектных работ, выполняемых для этого договора. Договоры и проекты характеризуются датами своего начала и окончания. Каждый договор имеет своего руководителя, в роли которого не могут выступать техники. Проекты выполняются группами сотрудников, причем это могут быть сотрудники разных отделов.

ОПИСАНИЕ

Данную базу данных можно использовать в действующей проектной организации, которая занимается строительством, ремонтом и созданием различных объектов. Управлять этой базой могут обычные операторы, которым не требуются глубокие знания в области администрирования СУБД.


МОДЕЛЬ БАЗЫ ДАННЫХ

Концептуальная модель

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

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


ВЗАИМОДЕЙСТВИЕ С БД

Начальные значения:

· таблица Dogovor, начальное значение столбца Sum_stoimost – равно 0

· таблица Otdel, начальное значение столбца KolichSert – равно 1

· таблица Proekt, начальное значение столбца Stoimost – равно 0

Проверочные ограничения:

· таблица Dogovor, проверочное ограничение столбца Sum_stoimost - [sum_stoimost]>(0)

· таблица Sotrudniki, проверочное ограничение столбца KolichAvtSved - [KolichAvtSvid]>(0)

· таблица Sotrudniki, проверочное ограничение столбца Pasport_number - Pasport_number > 0

Отношения внешнего ключа:

· Отношение Sotrudniki.Sotrudnik_id => Rukovoditel.Sotrudnik_id, удалить правило: каскадом

· Отношение Vidy_oborudovaniya.Oborud_id => Sotrudniki.Oborudovanie_id, удалить правило: задать NULL

· Отношение Otdel.Otdel_ID => Sotrudniki.Otdel_ID, обновить правило: каскадом

SQL запросы

INSERT:

· Добавление типа сотрудника: INSERT INTO tipi_sotrudnikov(name) VALUES ('Инженер')

· Добавление вида оборудования: INSERT INTO vidy_oborudovaniya(Oborud_Name) VALUES ('Дрель')

· Добавление заказчика: INSERT INTO Zakazchik(Full_name, Pasport_number) VALUES ('Кимов Ю.В.', 57284512)

UPDATE:

· Обновление имени сотрудника по его Id: UPDATE Sotrudniki SET Full_Name = 'Петров И. В.' WHERE Sotrudniki = 6

· Обновление имени заказчика по его номеру паспорта: UPDATE Zakazchik SET Full_Name = 'Синий В.М.' WHERE Pasport_number = 77190123

· Обновление сотрудника по Id:UPDATE tipi_sotrudnikov SET name = 'Зам. директора' WHERE tip_sotrudnika_id = 1

DELETE:

· Удаление заказчика по его имени DELETE Zakazchik WHERE Full_Name = 'Синий В.М.'

· Удаление сотрудника по его id: DELETE Sotrudnik WHERE Full_Name Strudniki_ID = 1

· Удаление договора по его id: DELETE Dogovor WHERE Dogovor_id = 1

SELECT:

· Выбрать всех сотрудников по алфавиту: SELECT * FROM Sotrudniki ORDER BY Full_name

· Выбрать всех заказчиков: SELECT * FROM Zakazchik ORDER BY Full_name

· Выбрать всех агентов: SELECT * FROM agents

· Выбрать договора с суммарной стоимостью более 50000: SELECT * FROM Dogovor WHERE Sum_stoimost > 50000

· Выбрать всех руководителей из сотрудников: SELECT s.Full_name FROM sotrudniki s, rukovoditel r WHERE r.sotrudnik_id = s.sotrudniki_id

· Выбрать кол-во сотрудников в каждом отделе: SELECT o.otdel_number, COUNT(*) as kolichestvo FROM otdel o, sotrudniki s WHERE s.otdel_id = o.otdel_id

GROUP BY otdel_number

· Выбрать руководителя у отдела: SELECT o.otdel_number, s.full_name FROM otdel o, sotrudniki s, rukovoditel r WHERE s.otdel_id = o.otdel_id and r.sotrudnik_id = s.sotrudniki_id

· Выбрать договора подписнные не позднее: SELECT * FROM Dogovor WHERE Date_porpis > '05.12.2011'

· Выбрать кол-во работающих сотрудников и с группировать их по типу: SELECT MAX(t.name), COUNT(*) as kolvo FROM tipi_sotrudnikov t, sotrudniki s WHERE t.tip_sotrudnika_id = s.tip_sotrudnika GROUP BY tip_sotrudnika

· Выбрать сотрудников у которых кол-во авторских свидетельств больше 1: SELECT * FROM Sotrudniki WHERE KolichAvtSvid > 1

 

Хранимые процедуры

ПроцедураDogovorByZakazchik(@z_name varchar(MAX))– показывает договоры заключенные с заказчиком:

CREATE PROCEDURE DogovorByZakazchik(@z_name varchar(MAX))

AS

SELECT Dogovor_number, Date_porpis, Full_Name, Sum_stoimost

FROM dogovor d, zakazchik z

WHERE d.zakazchik_id = z.zakazchik_id and z.Full_name = @z_name

GO

Пример: EXECUTE DogovorByZakazchik ‘Петров Т.С.’ - показать договоры по заказчику Петров Т.С.

 

 

ПроцедураDListByDate (@dt_st datetime)– показывает информацию о договорах начиная с даты @dt_st

ALTER PROCEDURE DListByDate(@dt_st datetime)

AS

SELECT z.Full_name as zakazchik, s.Full_name as rukovoditel,

d.dogovor_number, sum_stoimost, d.date_porpis

FROM Dogovor d, zakazchik z, rukovoditel r, sotrudniki s

WHERE d.zakazchik_id = z.zakazchik_id AND

d.rukovoditel_id = r.rukovoditel_id AND

r.sotrudnik_id = s.sotrudniki_id AND

Date_porpis >= @dt_st

 

Пример: EXECUTE DListByDate '7.12.2011' - показать информацию по договорам начания с 7.12.2011

 

Процедура ShowSotrudniki(@tip_sotrudnika int OUTPUT, @count INT OUTPUT)– показывает какой тип работника преобладает в организации

CREATE PROCEDURE ShowSotrudniki(@tip_sotrudnika int OUTPUT, @count INT OUTPUT)

AS

SELECT @tip_sotrudnika=MAX(tip_sotrudnika), @count=COUNT(*)

FROM Sotrudniki s

GROUP BY s.tip_sotrudnika

ORDER BY COUNT(tip_sotrudnika)

GO

Пример: DECLARE @dt int, @cn int

EXECUTE ShowSotrudniki

@tip_sotrudnika = @dt OUTPUT,

@count = @cn OUTPUT

SELECT 'Преобладющий тип сотрудников: ' + t.name + ' в количестве ' + CAST(@cn AS VARCHAR) + ' чел.' FROM tipi_sotrudnikov t WHERE t.tip_sotrudnika_id = @dt

 

Триггеры

При удалении типа сотрудника, удаляются все записи в таблице Sotrudniki соответствующие удаляемому типу сотрудника

CREATE TRIGGER delete_tip_sotrudnika

ON tipi_sotrudnikov

AFTER DELETE

AS

DELETE Sotrudniki WHERE tip_sotrudnika = (SELECT tip_sotrudnika_id FROM DELETED)

 

При добавлении нового договора, создается новый проект и связывается с новым договором

CREATE TRIGGER dogovor_add

ON Dogovor

FOR INSERT

AS

DECLARE @d_id int, @stoim int, @p_id int;

SELECT @d_id = dogovor_id, @stoim = Sum_stoimost FROM INSERTED

INSERT INTO Proekt (Stoimost)

VALUES(@stoim)

SELECT @p_id = MAX(Proekt_ID) FROM Proekt

INSERT INTO Dogovor_proekt (Proekt_ID, Dogovor_ID)

VALUES(@p_id, @d_id)

 

При обновлении номера отдела в таблице Otdel, обновляется поле otdel_number в таблице Sotrudniki

CREATE TRIGGER update_o_num

ON Otdel

INSTEAD OF UPDATE

AS

DECLARE @id int, @num int;

SELECT @id = Otdel_id, @num = otdel_number FROM INSERTED

 

UPDATE Sotrudniki SET otdel_number = @num WHERE otdel_id = @id





©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.