ВЗАИМОДЕЙСТВИЕ С БД
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Ивановский государственный энергетический университет имени В.И. Ленина»
Кафедра ПОКС Отчет по курсу «Базы Данных» «Проектная организация»
Выполнил: студент группы 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 Все права принадлежат авторам размещенных материалов.
|