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

База данных системы управления проектами

Пояснительная записка к курсовой работе

По дисциплине: Базы данных

Руководитель И.Ю.Тыров

Студент Н.А.Сметанин

Группы Р-48032

Екатеринбург 2011

СОДЕРЖАНИЕ

1. ПОСТАНОВКА ЗАДАЧИ.. 3

2. ОПИСАНИЕ БД.. 4

2.1 ТАБЛИЦЫ... 4

2.2 ФУНКЦИИ.. 11

2.3 ХРАНИМЫЕ ПРОЦЕДУРЫ... 12

2.4 ТРИГГЕРЫ... 15

2.5 ОТЧЁТЫ... 16

2.6 ДЕЙСТВИЯ ПО РАСПИСАНИЮ... 17

2.7 ПОЛЬЗОВАТЕЛИ И РОЛИ.. 18

ЗАКЛЮЧЕНИЕ.. 20

ПРИЛОЖЕНИЕ 1 ДИАГРАММА БАЗЫ ДАННЫХ.. 21

ПРИЛОЖЕНИЕ 2 ФАЙЛ Functions.sql 22

ПРИЛОЖЕНИЕ 3 ФАЙЛ Tables.sql 24

ПРИЛОЖЕНИЕ 4 ФАЙЛ Triggers.sql 28

ПРИЛОЖЕНИЕ 5 ФАЙЛ Procedures.sql 30

ПРИЛОЖЕНИЕ 6 ФАЙЛ Reports.sql 37

ПРИЛОЖЕНИЕ 7 ФАЙЛ Jobs.sql 40

ПРИЛОЖЕНИЕ 8 ФАЙЛ Permissions.sql 41

ПРИЛОЖЕНИЕ 9 ФАЙЛ FillData.sql 42

ПРИЛОЖЕНИЕ 10 ФАЙЛ TestData.sql 43

ПРИЛОЖЕНИЕ 11 ФАЙЛ Drop.sql 45

ПОСТАНОВКА ЗАДАЧИ

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

База данных содержит следующие данные:

· Списки пользователей.

· Списки должностей пользователей.

· Категории задач – «Ошибка», «Аналитика» и д.р.

· Состояния задач.

· Переходы между состояниями задач, реализующие жизненные циклы задач.

· Проекты, включая списки участников и связанные файлы.

· Компоненты проектов, Вехи проектов.

· Задания, включая списки наблюдателей, чек-листы, связанные файлы.

· Комментарии, включая прикрепленные файлы.

В БД реализованы средства, позволяющие:

· создавать, изменять и удалять проекты

· создавать, изменять и удалять задачи

· создавать и удалять комментарии

· создавать и удалять вехи

· создавать и удалять компоненты

· просматривать полную информацию о проектах и задачах с учетом вложенных проектов и задач.

· генерировать отчеты по пользователям и проектам.

Автоматизированы следующие функции БД:

· передача задачи другому исполнителю в случае отсутсвия активности по задаче в течение определенного промежутка времени.

· удаление завершенных задач после определенного промежутка времени.

Все скрипты создания БД находятся в следующих файлах:

· Functions.sql – функции, необходимые для создания таблиц и процедур.

· Tables.sql – таблицы.

· Triggers.sql – триггеры.

· Procedures.sql – процедуры.

· Reports.sql – отчеты.

· Jobs.sql – плановые задания.

· Permissions.sql – роли и разрешения.

· FillData.sql – справочные данные.

· TestData.sql – тестовые данные.

· Drop.sql – данные для очистки базы данных.

ОПИСАНИЕ БД

ТАБЛИЦЫ

Всего предметную область проекта в базе данных представляют 17 таблиц.

1. User - таблица пользователей системы. Содержит учетные данные каждого пользователя системы – имя, электронную почту, телефон, должность, связь с системным пользователем MS SQL.

· Колонки:

1. Id -- ID пользователя

2. Email -- Адрес электронной почты

3. FirstName -- Имя

4. MiddleName -- Второе имя

5. LastName -- Фамилия

6. Phone -- Телефон

7. PositionId -- Должность

8. PhotoFileId -- Фотография

9. SysUserId -- Связь с пользователем MS SQL.

· Ограничения:

1. CK_User_Name – Проверка на корректность имени

2. CK_User_EMail -- Проверка на корректность почтового адреса

3. CK_User_Phone -- Проверка на корректность телефона

4. FK_User_Position – Внешний ключ к Должности

5. FK_User_PhotoFile – Внешний ключ к Файлам – фотография.

2. Position – таблица должностей пользователей системы. Содержит название должностей, к которым относятся пользователи.

· Колонки:

1. Id -- ID должности

2. Title – Наименование должности

· Ограничения:

1. Title UNIQUE – ограничение уникальности наименования должности.

3. Category – таблица категорий задач. Содержит название, начальное и конечное состояния.

· Колонки:

1. Id -- ID категории.

2. Title – Наименование категории.

3. InitialStateId – Начальное состояние.

4. FinalStateId – Конечное состояние.

· Ограничения:

1. Title UNIQUE – ограничение уникальности наименования категории.

2. CK_Category_States – начальное состояние не должно быть равно конечному.

3. FK_Category_InitialState – внешний ключ к таблице состояний.

4. FK_Category_FinalState – внешний ключ к таблице состояний.

4. State - таблица состояний задач системы. Содержит название состояний, в которых могут находиться задачи.

· Колонки:

1. Id -- ID состояния.

2. Title – Наименование состояния.

· Ограничения:

1. Title UNIQUE – ограничение уникальности наименования состояния.

5. StateTransition – таблица переходов между состояниями в категории задачи. Содержит ID категории, начального и конечного состояний.

· Колонки:

1. CategoryId – ID категории.

2. InitialStateId – ID начального состояния.

3. FinalStateId – ID конечного состояния.

· Ограничения:

1. CK_Category_States – начальное состояние не равно конечному.

2. FK_Category_InitialState – внешний ключ к таблице состояний.

3. FK_Category_FinalState – внешний ключ к таблице состояний.

6. File – таблица файлов, используемых в проектах, задачах и комментариях. Содержит имя и содержимое файлов.

· Колонки:

1. Id – ID файла.

2. Name – Имя файла.

3. Content – Содержимое.

· Ограничения:

1. Name <> ‘’ - имя не должно быть пустым.

2. Name UNIQUE – имя должно быть уникальным.

7. Project – таблица проектов, содержащих подпроекты и задачи.

· Колонки:

1. Id – ID проекта.

2. ParentId - ID родительского проекта.

3. Title – Наименование проекта.

4. CreatorId – ID пользователя – создателя проекта.

5. ManagerId – ID пользователя – менеджера проекта.

6. StartDate – дата начала проекта.

7. EndDate – дата окончания проекта.

8. Content – содержимое (описание) проекта.

9. IsDone – завершен ли проект.

10. ModifyDate – дата изменения проекта.

· Ограничения:

1. U_Project_ParentTitle – в пределах одного родительского проекта наименование должно быть уникально.

2. CK_Project_EndDate – дата окончания проекта не должна быть до даты начала проекта.

3. FK_Project_Parent – внешний ключ к таблице проектов – родительский.

4. FK_Project_Creator – внешний ключ к таблице пользователей.

5. FK_Project_Manager – внешний ключ к таблице пользователей.

8. ProjectUser – таблица пользователей - участников проектов.

· Колонки:

1. ProjectId – ID проекта.

2. UserId – ID пользователя.

· Ограничения:

1. PK_ProjectUser – первичный ключ (ProjectId, UserId)

2. FK_ProjectUser_Project – внешний ключ к таблице проектов.

3. FK_ProjectUser_User – внешний ключ к таблице пользователей.

9. ProjectFile – таблица файлов, связанных с проектом.

· Колонки:

1. ProjectId – ID проекта.

2. FileId – ID файла.

· Ограничения:

1. PK_ProjectFile – первичный ключ (ProjectId, FileId)

2. FK_ProjectFile_Project – внешний ключ к таблице проектов.

3. FK_ProjectFile_File – внешний ключ к таблице файлов.

10. Component – таблица компонентов проекта. Каждый компонент имеет также и вложенные компоненты. К компонентам привязываются задачи.

· Колонки:

1. Id – ID компонента.

2. ParentId – ID родительского компонента.

3. ProjectId – ID проекта.

4. Title – Наименование компонента.

· Ограничения:

1. U_Component_ProjectTitle - в пределах одного проекта наименование должно быть уникально.

2. FK_Component_Parent – внешний ключ к таблице компонентов – родительский.

3. FK_Component_Project – внешний ключ к таблице проектов.

11. Milestone – таблица вех проектов. Каждая веха имеет срок – дату, к которой связанные с ней задачи должны быть выполнены.

· Колонки:

1. Id – ID компонента.

2. ProjectId – ID проекта.

3. Title – Наименование компонента.

4. Due – Дата вехи.

· Ограничения:

1. U_Milestone_ProjectTitle - в пределах одного проекта наименование должно быть уникально.

2. FK_Milestone_Project – внешний ключ к таблице проектов.

12. Task – таблица задач. Задачи привязаны к проекту и категории, могут иметь собственные подзадачи. У задачи есть создатель и исполнитель, даты начала и завершения, оцененное и затраченное время.

· Колонки:

1. Id – ID задачи.

2. ParentId – ID родительской задачи.

3. ProjectId – ID проекта.

4. Title – Наименование задачи.

5. CategoryId – ID категории задачи.

6. ComponentId – ID компонента задачи.

7. StateId – ID состояния задачи.

8. StartDate – Дата начала.

9. EndDate – Дата окончания.

10. CreatorId – ID создателя задачи.

11. PerformerId – ID исполнителя задачи.

12. MilestoneId – ID вехи.

13. EstimatedTime – Оцененное время, часов.

14. SpentTime – Затраченое время, часов.

15. Progress – Прогресс задачи.

16. Content – Содержимое (Описание) задачи.

17. ModifyDate – Дата изменения задачи.

· Ограничения:

1. U_Task_ProjectParentTitle - в пределах одного проекта и одной родительской задачи наименование должно быть уникально.

2. CK_Task_CheckTask – задача должна удоволетворять требованиям функции CheckTask.

3. CK_Task_EndDate – дата окончания задачи не должна быть до даты начала.

4. FK_Task_Parent – внешений ключ к таблице заданий – родительский.

5. FK_Task_Project – внешний ключ к таблице проектов.

6. FK_Task_Category – внешний ключ к таблице категорий.

7. FK_Task_Component – внешний ключ к таблице компонентов.

8. FK_Task_CreatorUser – внешний ключ к таблице пользователей.

9. FK_Task_PerformerUser – внешний ключ к таблице пользователей.

10. FK_Task_Milestone – внешний ключ к таблице вех.

13. TaskAuditor - таблица аудиторов (наблюдателей) задачи.

· Колонки:

1. TaskId – ID задачи.

2. UserId – ID пользователя - аудитора.

· Ограничения:

1. PK_TaskAuditor – первичный ключ (TaskId, UserId)

2. FK_ TaskAuditor_Task – внешний ключ к таблице задач.

3. FK_ TaskAuditor_User – внешний ключ к таблице пользователей.

14. TaskCheckItem – таблица пунктов задачи. Пункты отражают чек-лист – список действий, которые должны быть выполнены.

· Колонки:

1. Id – ID компонента.

2. TaskId – ID задачи.

3. Title – Наименование компонента.

4. IsDone – Завершена ли задача.

· Ограничения:

1. U_TaskCheckItem_TaskTitle - в пределах одной задачи наименование должно быть уникально.

2. FK_TaskCheckItem_Task – внешний ключ к таблице задач.

15. TaskFile - таблица файлов, связанных с задачей.

· Колонки:

1. TaskId – ID задачи.

2. FileId – ID файла.

· Ограничения:

1. PK_TaskFile – первичный ключ (TaskId, FileId)

2. FK_TaskFile_Task – внешний ключ к таблице задач.

3. FK_TaskFile_File – внешний ключ к таблице файлов.

16. Comment - таблица комментариев к задачам.

· Колонки:

1. Id – ID комментария

2. TaskId – ID задачи.

3. UserId – ID пользователя - автора.

4. Content – содержимое (текст) комментария.

· Ограничения:

1. FK_Comment_Task – внешний ключ к таблице задач.

2. FK_Comment_User – внешний ключ к таблице пользователей.

17. CommentFile - таблица файлов, связанных с комментарием.

· Колонки:

1. CommentId – ID комментария.

2. FileId – ID файла.

· Ограничения:

1. PK_CommentFile – первичный ключ (CommentId, FileId)

2. FK_CommentFile_Comment – внешний ключ к таблице комментариев.

3. FK_CommentFile_File – внешний ключ к таблице файлов.

Кроме того, для увеличения скорости выборки добавлены некластерные индексы на некоторые поля таблиц, для которых MS SQL еще не создал их автоматически.

ФУНКЦИИ

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

18. FormatUserName – функция составляет полное имя пользователя вида «Имя Отчество Фамилия» из трех отдельных кусков. Параметры: @FirstName NVARCHAR(256), @MiddleName NVARCHAR(256), @LastName NVARCHAR(256).

19. GetProjectManager – вычисляет нового менеджера проекта при его отсутствии или замене предыдущего. Ищет вверх по иерархии проектов подходящего менеджера и возвращает его Id. Параметры: @ProjectId INT, @ExceptId INT.

20. GetTaskPerformer – вычисляет нового исполнителя задачи при его отсутствии или замене предыдущего. Ищет вверх по иерархии задач подходящего исполнителя, если такого нет – выбирает менеджера проекта с помощью GetProjectManager, и возвращает его Id. Параметры: @TaskId INT, @ExceptId INT.

21. GetProjectEndDate – вычисляет дату окончания проекта. Если она не задана у проекта, ищет вверх по иерархии проектов и возвращает первую непустую. Параметры: @ProjectId INT.

22. GetTaskEndDate – вычисляет дату окончания задачи. Если она не задана у задачи, пытается использовать дату у вехи. Если вехи нет, то идет вверх по иерархии задач, а затем по иерархии проектов с помощью GetProjectEndDate. Параметры: @TaskId INT.

23. IsValue – действует аналогично ISNULL, но при этом есть возможность реагировать не только на NULL, но и на переданное в качестве параметра @Except значение.

24. CheckTask – проверяет корректность объекта задачи (Task) – т.е. задача привязана к тому же проекту, что и её родительская задача, а также её компонент и веха. А также что состояние принадлежит заданной категории задачи.

25. GetCurrentUserId – Получает UserId по текущему пользователю MS SQL. Если такого нет, то возвращает NULL.

ХРАНИМЫЕ ПРОЦЕДУРЫ

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

26. GetProject – процедура, выводящая полную информацию о проекте с учетом всех данных его подпроектов и связанных записей. Входные параметры: @ProjectId INT.
Вывод – [Project], [Parent project], [Creator], [Manager], [Start date], [End date], [Modify date], [Managers], [Users].

27. GetTask – процедура, выводящая полную информацию о задаче с учетом всех данных её подзадач и связанных записей. Входные параметры: @TaskId INT.
Вывод - [Task], [Parent task], [Project], [Component], [Milestone], [Category], [State], [Creator], [Performer], [Estimated hours], [Spent hours], [Progress], [Start date], [End date], [Modify date], [Performers], [Comments], [Files].

28. GetCategoryTransitions – процедура, выводящая жизненные циклы категорий задач в удобном виде. Входные параметры: @CategoryId INT.
Вывод – [Category], [Initial state], [Final state].

29. GetProjectUsers – процедура, выводящая списки участников проекта в удобном виде. Входные параметры: @ProjectId INT.
Вывод – [Project], [User]

30. GetTaskAuditors – процедура, выводящая списки аудиторов задачи в удобном виде. Входные параметры: @TaskId INT.
Вывод – [Task], [Auditor]

31. CreateProject – процедура для создания нового проекта. Доступна при наличии роли Manager. Поле CreatorId у созданного проекта устанавливается в соотвествии с текущим пользователем. Входные параметры: @Title NVARCHAR(256), @ParentId INT, @Content NVARCHAR(MAX), @StartDate DATE, @EndDate DATE, @ManageId INT.

32. UpdateProject – процедура для изменения существующего проекта. Доступна при наличии роли Manage и только если её создатель (CreatorId) – текущей пользователь. Входные параметры: @ProjectId INT, @Content NVARCHAR(MAX), @StartDate DATE, @EndDate DATE, @ManageId INT.

33. DeleteProject - процедура для удаления существующего проекта. Доступна при наличии роли Manage и только если её создатель (CreatorId) – текущей пользователь. Входные параметры: @ProjectId INT.

34. CreateComponent – процедура для создания компонента. Доступна при наличии роли Manage и только если создатель проекта Project (CreatorId) – текущей пользователь. Параметры: @ProjectId INT, @ParentId INT, @Title NVARCHAR(256).

35. DeleteComponent – процедура для удаления компонента. Доступна при наличии роли Manage и только если создатель проекта Project (CreatorId) – текущей пользователь. Параметры: @ComponentId INT.

36. CreateMilestone – процедура для создания вехи. Доступна при наличии роли Manage и только если создатель проекта Project (CreatorId) – текущей пользователь. Параметры: @ProjectId INT, @Title NVARCHAR(256), @Due DATE.

37. DeleteMilestone – процедура для удаления вехи. Доступна при наличии роли Manage и только если создатель проекта Project (CreatorId) – текущей пользователь. Параметры: @MilestoneId INT.

38. CreateTask – процедура для создания новой задачи. Доступна только при наличии роли Contributor и присутствии текущего пользователя в списках участников проекта. Параметры: @Title NVARCHAR(256), @ParentId INT, @ProjectId INT, @CategoryId INT, @Content NVARCHAR(MAX), @ComponentId INT, @StartDate DATE, @EndDate DATE, @PerformerId INT, @MilestoneId INT, @EstimatedTime DECIMAL(6, 1).

39. UpdateTask – процедура изменения задачи. Доступна только при наличии роли Contributor и если создатель задачи – текущий пользователь. Параметры: @TaskId INT, @StateId INT, @StartDate DATE, @EndDate DATE, @PerformerId INT, @MilestoneId INT, @EstimatedTime DECIMAL(6, 1), @SpentTime DECIMAL(6, 1), @Progress TINYINT, @Content NVARCHAR(MAX).

40. DeleteTask – процедура удаления задачи. Доступна только при наличии роли Contributor и если создатель задачи – текущий пользователь. Параметры: @TaskId INT.

41. CreateTaskCheckItem - процедура создания пункта задачи. Доступна только при наличии роли Contributor и если создатель задачи Task – текущий пользователь. Параметры: @TaskId INT, @Title NVARCHAR(256).

42. DeleteTaskCheckItem - процедура удаления пункта задачи. Доступна только при наличии роли Contributor и если создатель задачи Task – текущий пользователь. Параметры: @TaskCheckItemId INT.

43. CreateComment - процедура создания комментария. Доступна только при наличии роли Contributor и если создатель задачи Task – текущий пользователь. Параметры: @TaskId INT, @Content NVARCHAR(MAX).

44. DeleteComment - процедура удаления комментария. Доступна только при наличии роли Contributor и если создатель задачи Task – текущий пользователь. Параметры: @CommentId INT.

45. CheckUser – служебная процедура, проверящая текущего пользователя на соответствие заданному, и вызывающая ошибку в случае несоответствия. Входные данные - @UserId INT.

46. GetProjectInfo – служебная процедура, используется в GetProject для рекурсивного сбора данных о проекте и подпроектах. Параметры: @ProjectId INT. Выходные: @StartDate DATE, @EndDate DATE, @ModifyDate DATE, @Managers NVARCHAR(MAX), @Users NVARCHAR(MAX)

47. GetTaskInfo – служебная процедура, используется в GetTask для рекурсивного сбора данных о задаче и подзадачах. Параметры: @TaskId INT. Выходные: @Estimated INT, @Spent INT, @Progress INT, @StartDate DATE, @EndDate DATE, @ModifyDate DATE, @Performers NVARCHAR(MAX), @Comments INT, @Files INT.

ТРИГГЕРЫ

В базе данных создано 10 триггеров, 8 из которых отвечают за поддержание ссылочной и логической целостности базы данных:

48. UserSysUserTrigger – триггер, проверяющий корректность связи пользователя User предметной области с системным пользователем MS SQL. При попытке вставить или обновить неверное значение поля SysUserId таблицы User, вызывает ошибку.

49. FileDeleteTrigger – триггер, отвечающий за удаление данных из таблиц (ProjectFile, TaskFile, CommentFile), которые ссылаются внешним ключем на таблицу File, но не могут объявить правило ON DELETE CASCADE для такого ключа из-за появления циклов в графе вызова триггеров.

50. UserDeleteTrigger – триггер, отвечающий за удаление данных из таблиц (ProjectUser, TaskAuditor, Comment), которые ссылаются внешним ключем на таблицу User, а также за обновление полей CreatorId и PerformerId в таблице Task.

51. ProjectDeleteTrigger – триггер, отвечающий за удаление подпроектов в таблице Project при удалении родительского проекта.

52. TaskDeleteTrigger – триггер, отвечающий за удаление подзадач в таблице Task при удалении родительской задачи.

53. ComponentDeleteTrigger – триггер, отвечающий за удаление подкомпонентов в таблице Component при удалении родительского компонента, а также задач таблицы Task, связанных с этим компонентом.

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

55. MilestoneDeleteTrigger – триггер, отвечающий за очищение поля MilestoneId задач таблицы Task, связанных с этой вехой.

Оставшиеся два триггера используются для обновления даты модификации проектов таблицы Project и задач таблицы Task:

1. ProjectUpdateTrigger – триггер, обновляющий поле ModifyDate таблицы Project текущей датой при обновлении проекта.

2. TaskUpdateTrigger – триггер, обновляющий пол ModifyDate таблицы Task текущей датой при обновлении задания.

ОТЧЁТЫ

Отчёты – это набор хранимых процедур, позволяющих просматривать статистическую информацию о записях базы данных. Всего было реализовано 4 процедуры генерации отчетов.

1. UserReport – процедура, выводящая статистику по пользователям с разбиением по проектам. Также возможно ограничить вывод только одним пользователем, пользователями одной должности, либо одним проектом.
Отчет содержит следующие колонки:
Полное имя пользователя | Должность | Проект | Затрачено часов | Всего задач | Открытых задач | Просроченных задач | Созданных задач | Комментарив | Наблюдаемых задач
[User], [Position], [Project], [Spent hours], [Total tasks], [Opened tasks], [Expired tasks], [Created tasks], [Comments], [Audited tasks]
Входные параметры: @UserId INT, @PositionId INT, @ProjectId INT

2. ProjectReport – процедура, выводящая статистику по проектам. Также можно ограничить вывод только одним проектом.
Отчет содержит следующие колонки:
Проект | Затрачено часов | Оценено часов | Прогресс | Всего задач | Открытых задач | Просроченных задач | Пользователей | Комментариев | Файлов
[Project], [Spent hours], [Estimate hours], [Progress], [Total tasks], [Opened tasks], [Expired tasks], [Users], [Comments], [Files]
Входные параметры: @ProjectId INT

3. TasksByState – процедура, выводящая статистику распределения задач по состояниям с разбиением по категориям. Можно ограничить вывод одним состоянием, либо одной категорией состояний.
Отчет содержит следующие колонки:
Категория | Состояние | Проект | Задачи ([Category] | [State] | [Project] | [Tasks])
Входные параметры: @StateId INT, @CategoryId INT

4. TasksByMilestone – процедура, выводящая статистику распределения задач по вехам с разбиением по проектам. Можно ограничить вывод вехой или проектом.
Отчет содержит следующие колонки:
Проект | Веха | Срок | Задачи ([Project], [Milestone], [Due], [Tasks])
Входные параметры: @MilestoneId INT, @ProjectId INT

Дата добавления: 2015-10-31