Разработка и администрирование базы данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академии

КУРСОВОЙ ПРОЕКТ

Разработка и администрирование базы данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академий имени академика

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

Актуальность темы

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

Объектом исследования курсовой работы является факультет прикладной информатики Пермской государственной сельскохозяйственной академий имени академика Д. Н. Прянишникова.

Предметом исследования курсовой работы является система обработки данных об учащихся студентах факультета прикладной информатики.

Целью курсовой работы является разработка и администрирование базы данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академий имени академика Д. Н. Прянишникова, для автоматизации поиска информации об учащихся студентах Задачами курсовой работы являются:

· анализ организационной структуры академии

· анализ информационной системы академии

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

· проектирование базы данных

· разработка базы данных

· тестирование, апробация, внедрение

· расчет экономической эффективности В ходе выполнения курсовой работы предполагается разработать базу данных для факультета прикладной информатики с использованием программного продукта Microsoft SQL Server 2008R2. Где будут использованы данные о студентах, лекторах, а так же расписании занятий и посещаемость занятий.

1. Описание предметной области

информационный база академия При разработке базы данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академии имени Д. Н. Прянишникова было проведено обследование предметной области.

Пермская государственная сельскохозяйственная академия имени Д. Н. Прянишникова — это высшее учебное заведение, где готовятся специалисты по различным фундаментальным и многим прикладным наукам.

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

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

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

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

Организационная структура — один из основных элементов управления организацией. Она характеризуется распределением целей и задач управления между подразделениями и работниками организации. (Рисунок 1)

Рисунок 1. Организационная структура

1.1 Описание программных продуктов, используемых при проектировании БД

Microsoft SQL Server 2008 R2

Разработаем базу данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академии имени Д. Н. Прянишникова с помощью программного продукта Microsoft SQL Server 2008 R2.

Microsoft SQL Server 2008 R2 — это надежная, продуктивная и интеллектуальная платформа данных, она позвoляет сократить время и затраты на разработку и сопровождение программ.

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

AllFusion ERwin Data Modeler

AllFusion ERwin Data Modeler — это CASE-средство для проектирования и документирования баз данных, которое позволяет создавать, документировать и сопровождать базы данных, хранилища и витрины данных. Модели данных помогают визуализировать структуру данных, обеспечивая эффективный процесс организации, управления и администрирования таких аспектов деятельности предприятия, как уровень сложности данных, технологий баз данных и среды развертывания. [1]

1.2 Создание структуры БД, нормализация БД

Программа AllFusion ERwin Data Modeler позволяет наглядно отображать сложные структуры данных. Он облегчает проектирование баз данных. Для этого достаточно создать графическую ERD модель удовлетворяющую всем требованиям к данным и ввести правила для создания логической модели (Рисунок 2), которая отображает все элементы, атрибуты, отношения и группировки.

ERwin поддерживает автоматическую генерацию физической модели данных. При этом логическая модель трансформируется в физическую (Рисунок 3): сущности становятся таблицами, атрибуты становятся столбцами, а ключи становятся индексами.

Рисунок 2. Логическая модель данных Рисунок 3. Физическая модель данных Нормализация отношений — это формальный аппарат ограничений, накладываемых на формирование таблиц, который позволяет устранить дублирование информации, уменьшить время на ее ввод и корректировку, отследить противоречивости в данных.

Всего рассматривают 3 формы нормализации:

Первая нормальная форма: значения всех атрибутов отношения неделимы. Данное требование является базовым в классической реляционной модели, поэтому любая реляционная таблица, по определению, уже находится в 1 Н. Ф Вторая нормальная форма: выполняются требования первой нормальной формы и каждый не ключевой атрибут находится в полной функциональной зависимости от ключа (зависит от всех его частей).

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

2. Проектирование базы данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академии имени Д. Н Прянишникова

Для создания базы данных, необходимо на панели Object Explorer выбрать правой кнопкой мыши папку Databases, затем нажать в сплывающем окне New Databases, где необходимо ввести название будущей базы данных, назовем ее «PGSHA» (Рисунок 4).

Рисунок 4. Создание базы данных PGSHA

2.1 Создание таблиц, индексов, триггеров, файловых групп

Создание таблиц

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

— Студенты

— Успеваемость

— Предмет

— Преподаватели На примере таблицы «Студенты» рассмотрим ее создание и заполнение названия столбцов (Рисунок 5).

Рисунок 5. Создание таблицы студенты

Создание триггера

Триггеры в базе данных необходим для того, чтобы не допустить случайного изменения в базе. Это один из способов обезопасить данные находящиеся в базе.

Триггер — это запоминающий элемент с двумя (или более) устойчивыми состояниями, изменение которых происходит под действием входных сигналов и предназначен для хранения одного бита информации, то есть 0 или 1.

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

С помощью триггеров достигаются следующие цели:

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

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

· накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

· поддержка репликации.

Создадим триггер, который будет запрещать удаление таблицы, содержащую данные о студентах (Рисунок 6).

Рисунок 6. Код запрещающий удаление таблицы Проверим правильность работы триггера, для этого попробуем удалить таблицу «Студенты», на что программа выдает ошибку, что удаление данной таблиц невозможно (Рисунок 7).

Рисунок 7. Результат выполнения триггера на удаление таблицы

Создание файлов и файловых групп

Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.

Для примера создадим 2 файловые группы (Рисунок 8)

Рисунок 8. Создание файлов и файловых групп

Индексы в sql server

Индекс — это объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска.

SQL Сервер поддерживает следующие виды индексов:

· Составные (сложные) индексы — включающие более одного табличного столбца. Эти индексы используются, когда данные в нескольких столбцах логически взаимосвязаны;

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

· Кластеризованные и некластеризованные индексы — позволяют связывать физическое и логическое расположение данных. При наличии кластеризованного индекса физическое расположение строк таблицы на запоминающем устройстве соответствует их логическому (индексированному) расположению. У таблицы может быть лишь один кластеризованный индекс. Некластеризованный индекс не обеспечивает такого соответствия и данные могут располагаться в любом порядке.

2.2 Заполнение таблиц данными

Перед тем как начать заполнять таблицы необходимо их связать (Рисунок 9)

Рисунок 9. Связывание таблиц Теперь необходимо заполнить содержимое таблиц данными. Пример заполнения таблицы «Студенты» (Рисунок 10).

Рисунок 10. Заполнение таблицы «Студенты»

2.3 Cоздание автоматических задач по резервному копированию

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

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

· сбой носителя;

· ошибки пользователей (например, удаление таблицы по ошибке);

· сбои оборудования (например, поврежденный дисковый накопитель или безвозвратная потеря данных на сервере);

· стихийные бедствия.

Для данной баз данных установим условия, когда будет проводиться резервное копирование (Рисунок 11)

Рисунок 11. Резервное копирование

2.4 Создание репликации

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

Создание репликации изображено на рисунке 12.

Рисунок 12. Создание репликации

3. Описание языка T-SQL

Transact-SQL (T-SQL) — процедурное расширение языка SQL, созданное компанией Microsoft (для Microsoft SQL Server) и Sybase.

SQL был расширен такими дополнительными возможностями как:

· управляющие операторы,

· локальные и глобальные переменные,

· различные дополнительные функции для обработки строк, дат, математики и т. п.,

· поддержка аутентификации Microsoft Windows

Язык Transact-SQL является ключом к использованию MS SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL. [2]

SQL (Structured Query Language) — Структурированный Язык Запросов — стандартный язык запросов по работе с реляционными базами данных. Язык был предложен компанией IBM в начале1970;х гг. для проверки возможностей реляционной модели. SQL в чистом (базовом) виде является информационно-логическим языком, а не языком программирования. Однако стандарт языка спецификацией SQL/PSM предусматривает возможность его процедурных расширений, с учетом которых язык уже может рассматриваться в качестве языка программирования. Первый вариант языка SQL был разработан и частично реализован в рамках проекта экспериментальной реляционной СУБД SystemR. Исходным названием языка было SEQUEL — Structured English Query Language — структурированный английский язык запросов. [3]

3.1 Создание проекта и сохранение всех запросов внутри текущего проекта

Создание проекта позволяет объединять всю информацию, все данные которые создавались в течении проектирования БД. На рисунке 13 показана папка проекта с данными.

Рисунок 13. Проект

3.2 Создание запросов к БД

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

Рисунок 14. Запрос студенты

Рисунок 15. Запрос успеваемость

3.3 Формирование отчетов

Для формирование отчетов используем мастера по созданию отчетов (Рисунок 16)

Рисунок 16. Мастер отчетов

3.4 Выборка по определенным критериям

Для того чтобы было проще находить интересующую информацию необходимо ее упорядочить. На рисунке 15 создана выборка которая сортирует данные по дате рождения учащихся, а так же на рисунке 16 показана выборка, которая сортирует по дате поступления в ВУЗ, тем самым мы можем проследить как меняются данные в зависимости от сортировки информации.

Рисунок 15. Сортировка по дате рождения

Рисунок 16. Сортировка по дате поступления

3.5 Расчет экономической эффективности

Экономический эффект (?Р4) от роста производительности труда (за счёт лучшей организации труда, сокращения непроизводительного времени, сокращения текучести кадров и т. д.)

Zj =

Zjизменение численности студентов (условное высвобождение численности студентов), чел.

O2 — плановый объём выручки А1 — выработка на 1 студента в отчётном периоде (тыс. руб.)

А2 — плановая выработка 1 студента (тыс. руб.)

Эффект будет равен: Р4 = Z? средний объем затрат на 1 студента- - ?З (дополнительные затраты на мероприятия по росту производительности труда).

Заключение

В данной работе была разработана база данных для факультета прикладной информатики Пермской государственной сельскохозяйственной академии имени Д. Н Прянишникова. Был проведен анализ поставленной задачи с последовательным её решением.

· Изучена предметная область;

· Осуществлён выбор основного набора данных, необходимых для составления моделей базы данных.

· Практическая реализация базы данных в среде Microsoft SQL Server;

· Создали вспомогательные элементы для работы с базой данных (запросы, триггеры);

· Произведена репликация БД;

· Осуществлено резервное копирование БД

1. http://ru.wikipedia.org/wiki/ERwin_Data_Modeler

2. http://ru.wikipedia.org/wiki/Transact-SQL

3. Казакова, И.А. К14 Основы языка TransactSQL: учеб. пособие / И. А. Казакова. — Пенза: Изд-во ПГУ, 2010. — 164 с.