Проектирование сетевой базы данных для решения экономических задач «Паспортный стол»

Министерство образования и науки Российской Федерации Северо-Кавказский государственный технический университет Кафедра информационных систем и технологий Проектирование сетевой базы данных для решения экономических задач «Паспортный стол»

Пояснительная записка к курсовому проекту по дисциплине «Программирование в компьютерных сетях»

Специальность 80 801 (230 200.62) «Информационные системы и технологии»

Группа ИС — 071

Студент Мищенко Е. В Руководитель Крахоткина Е. В Ставрополь, 2011

• ВВЕДЕНИЕ
• 1. ОБСЛЕДОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ
• 2. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ
• 2.1 Описание входной информации
• 2.2 Описание выходной информации
• 2.3 Перечень сущностей
• 2.4 Перечень атрибутов
• 2.5 Инфологическое проектирование БД
• 2.6 Реляционная модель БД
• 2.6.1 Функциональные зависимости между атрибутами
• 2.6.2 Выбор ключей
• 2.6.3 Нормализация отношений
• 3. ОРГАНИЗАЦИЯ ВЫБОРКИ ИНФОРМАЦИИ ИЗ БАЗЫ ДАННЫХ
• 4. Разработка представлений для отображения результатов выборки
• 5. ПРОЕКТИРОВАНИЕ ХРАНИМЫХ ПРОЦЕДУР
• 6. РАЗРАБОТКА МЕХАНИЗМОВ УПРАВЛЕНИЯ ДАННЫМИ В БАЗЕ ПРИ ПОМОЩИ ТРИГГЕРОВ
• 7. РАЗРАБОТКА ТЕХНОЛОГИЙ ДОСТУПА К БАЗЕ ДАННЫХ
• 8. Экономическое обоснование результатов внедрения программного продукта
• 9. Организация обмена данными между приложениями
• 10. ТРЕБОВАНИЯ К ТЕХИЧЕСКОМУ ОБЕСПЕЧЕНИЮ
• 11. ИНСТРУКЦИЯ ПО ИСПОЛЬЗОВАНИЮ БД
• 11.1 Вызов программы
• 11.2 Экранные формы
• ЗАКЛЮЧЕНИЕ
• СПИСОК ЛИТЕРАТУРЫ
• ПРИЛОЖЕНИЕ А
• ВВЕДЕНИЕ

В данном курсовом проекте была разработана база данных в СУБД Microsoft SQL Server 2008, программная оболочка в Microsoft Access для автоматизации работы паспортного стола.

Согласно требованиям, предъявляемым к программе, она была создана в соответствии с нормативными документами.

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

1. ОБСЛЕДОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ

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

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

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

Для решения этой задачи является целесообразным применение баз данных и средств их обработки.

Представленное в данном курсовом проекте приложение позволяет вести электронный учет данных о гражданах, имеющих паспорт. В данном приложении учтены основные параметры, которыми могут обладать владельцы паспортов (ФИО, дата рождения, место жительства, дата выдачи документа, район проживания).

2. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ

2.1 Описание входной информации

Разрабатывая базу данных «Паспортный стол» необходимо было провести обследование предметной области. В результате в БД «Паспортный стол» используются следующие входные данные:

— информация о паспорте;

— информация о заявлении клиента;

— дополнительные документы.

2.2 Описание выходной информации

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

2.3 Перечень сущностей

В курсовом проекте «Паспортный стол» основной таблицей является таблица «Паспорт». Избыточность данных можно наблюдать, если не разбивать эту таблицу на подтаблицы. Этого допускать нельзя. Вследствие этого добавляем в разрабатываемую БД следующие таблицы:

«Гражданин» — содержит информацию о гражданах.

«Вид» — содержит информацию о виде выдаваемого паспорта.

2.4 Перечень атрибутов

Таблица 2.1 — атрибуты отношения «Паспорт»

Таблица 2.2 — атрибуты отношения «Гражданин»

Таблица 2.3 — атрибуты отношения «Вид»

2.5 Инфологическое проектирование БД

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

К основным понятиям ER-модели можно отнести: сущность, связь и атрибут. Рассмотрим их более подробно.

Сущность (объект) — это реальный или представляемый объект предметной области, информация о котором должна сохраняться и быть доступна. Различают такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных предметов, событий, личностей, выступающих как единое целое. Экземпляр сущности относится к конкретной вещи в наборе. В диаграммах ER-модели сущность представляется в виде прямоугольника, который содержит имя сущности.

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

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

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

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

Среди бинарных связей существуют три фундаментальных вида связи: один-к-одному (1:1), один-ко-многим (1:M), многие-ко-многим (M:M).

1. Отношение один-к-одному (1:1) существует, когда один экземпляр одной сущности связан с единственным экземпляром другой сущности.

2. Связь один-ко-многим (1:M) имеет место, когда один экземпляр одной сущности связан с одним или более экземпляром другой сущности, и каждый экземпляр второй сущности связан только с одним экземпляром первой сущности.

3. Связь многие-к-одному (М:1) — многие записи связаны с одной.

4. Связь многие-ко-многим (М:N) существует, когда один экземпляр одной сущности связан с одним или более экземпляром другой сущности, и каждый экземпляр второй сущности связан с одним или более экземпляром первой сущности.

Недостатком данной модели является то, что одни и те же элементы могут выступать одновременно и в качестве сущности, и в качестве атрибута, и в качестве связи. В данном случае, будем считать, что каждый объект может выступать только в качестве одного конструктивного элемента.

2.6 Реляционная модель БД

Реляционная модель данных — логическая модель данных, впервые была предложена британским учёным сотрудником компании IBM Эдгаром Франком Коддом (E. F. Codd) в 1970 году. Эта модель позволила решить одну из важнейших задач в управлении базами данных — обеспечить независимость представления и описания данных от прикладных программ.

В структурной части модели фиксируется, что единственная структура данных, которая используется в реляционных БД, является так называемое нормализованное N-арное отношение. Два фундаментальных механизма манипулирования реляционными БД утверждаются в манипуляционной части модели:

— реляционная алгебра, которая базируется в основном на классической теории множеств (с некоторыми уточнениями);

— реляционное исчисление — базируется на классическом логическом аппарате исчисления предикатов первого порядка.

2.6.1 Функциональные зависимости между атрибутами

В разработанной базе данных «Паспортный стол» существуют следующие функциональные зависимости между атрибутами:

Таблица 2.6.1 — Функциональные зависимости между атрибутами сущности «Паспорт»

Таблица 2.6.2 — Функциональные зависимости между атрибутами сущности «Гражданин»

Таблица 2.6.3 — Функциональные зависимости между атрибутами сущности «Вид»

2.6.2 Выбор ключей

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

Использование ключей и индексов позволяет:

— однозначно идентифицировать записи;

— избегать повторения значений в ключевых полях;

— выполнять сортировку таблиц;

— увеличить операции поиска в таблицах;

— определить связи между отдельными таблицами БД.

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

Таблица 2.6.6 — Ключи сущностей «Паспорт», «Гражданин», «Вид»

2.6.3 Нормализация отношений

Процесс разбивки информации, которая должна храниться в БД, на отдельные таблицы называют — нормализацией.

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

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

1. Каждый не ключевой атрибут в таблицах не транзитивно зависит от первичного ключа.

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

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

После анализа функциональных зависимостей между атрибутами, видно, что все они соответствуют третьей нормальной форме. Отсюда следует, что первичными ключами будут: таблица «Паспорт» — Код паспорта; таблица «Гражданин» — Код_ гражданина; таблица «Вид» — Код вида.

3. ОРГАНИЗАЦИЯ ВЫБОРКИ ИНФОРМАЦИИ ИЗ БАЗЫ ДАННЫХ

Для организации выборки информации из базы данных в клиентском приложении были созданы следующие запросы:

1) Выборка данных с условием:

SELECT Гражданин. ФИО, Гражданин. Дата_рождения, Гражданин. Прописка,

FROM Гражданин

WHERE (((Гражданин. Дата_рождения)>01.01.1990

And (Гражданин. Прописка) «Россия»));

Результат представлен на рисунке 3.1.

Рисунок 3.1 — Выборка данных по условию

2) Выборка данных по дате:

SELECT * FROM Паспорт

WHERE (((Паспорт.Дата_выдачи)

Between #01.01.2004# And #01.01.2005#));

Результат представлен на рисунке 3.2.

3) Выборка данных из связанных таблиц:

SELECT Вид. Вид_Паспорта, Гражданин. ФИО, Гражданин. Дата_рождения, Гражданин. Пол, Гражданин. Прописка, Гражданин. Телефон, Паспорт. Дата_выдачи, Паспорт. Серия_номер

FROM Вид INNER JOIN (Гражданин INNER JOIN Паспорт ON Гражданин. Код_гражданина = Паспорт. Код_паспорта)

ON Вид. Код_вида = Паспорт. Код_вида

WHERE (((Гражданин.ФИО)="Мищенко Е.В."));

Рисунок 3.2 — Выборка данных по дате условия Рисунок 3.3 — Выборка данных из связанных таблиц

4. Разработка представлений для отображения результатов выборки

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

Пример одного из представлений приведен ниже.

Рисунок 4.1 — Представление, отражающее информацию о гражданах получивших паспорт

5. ПРОЕКТИРОВАНИЕ ХРАНИМЫХ ПРОЦЕДУР

Хранимые процедуры — это процессы, выполняемые непосредственно на сервере баз данных. Для этого используется среда SQL Server Management Studio. Все хранимые процедуры в базе данных находятся в специально отведенном списке — хранимые процедуры. В представленном курсовом проекте они используются для случая изменения серии и номера паспорта.

Для этой реализации, новые серия и номер паспорта хранятся в @new_sn. Далее производится запуск процедуры и выполнение изменения серии и номера паспорта. Фрагмент процедуры приведен ниже:

CREATE PROCEDURE seria_nomer

(@new_ sn Char)

AS

UPDATE Паспорт

SET Серия_номер = @new_sn

Хранимые процедуры незаменимы при «плановом» изменении серии и номеров паспорта, при достижении лицами возраста, при котором требуется замена паспорта.

6. РАЗРАБОТКА МЕХАНИЗМОВ УПРАВЛЕНИЯ ДАННЫМИ В БАЗЕ ПРИ ПОМОЩИ ТРИГГЕРОВ

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

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

CREATE TRIGGER Delete_all

ON Гражданин FOR DELETE

AS

DELETE Код_гражданина

FROM Гражданин, Паспорт

WHERE Гражданин. Код_гражданина = Паспорт. Код_гражданина Триггеры — это мощное средство, способное поддерживать целостность баз данных, которые имеют довольно большие масштабы, когда предусмотреть все возможные варианты вручную является невозможным, либо приводит к значительным затратам сил, времени, денежных средств.

Разработанное приложение «Паспортный стол» является небольшой программой и не имеет большого числа триггеров. Однако в будущем, при внесении изменений в программу, совершенствования ее базы данных их количество возрастет.

7. РАЗРАБОТКА ТЕХНОЛОГИЙ ДОСТУПА К БАЗЕ ДАННЫХ

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

— уровень сервера;

— уровень базы данных.

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

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

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

8. Экономическое обоснование результатов внедрения программного продукта

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

— подготовка отчетной документации;

— составление табеля рабочего времени;

— поиск необходимой информации для передачи в другие организации.

Цель внедрения программного продукта на предприятии:

— получение экономического эффекта, то есть снижения времени на выполнение однотипных операций;

— увеличения объема выполняемых работ;

— повышения качества.

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

Рассчитаем экономический эффект от использования программного продукта за период внедрения.

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

Эу = Зр-ЗА — экономия от замены ручной обработки информации на автоматизированную обработку.

Зр = Он Ч Ц Ч Гд / Нр — затраты на ручную обработку информации.

Он = 2000Ч25Ч80 = 4 Мбайт — объем информации, обрабатываемой вручную (2000 страниц в неделю).

Ц = 9000/160 = 56,25 — стоимость одного часа при окладе 9000 рублей в месяц и 40 часовой рабочей недели.

Гд = 1,2 — коэффициент, учитывающий дополнительные затраты времени на логические операции при ручной обработке информации.

Нр = 15*25*80=0,029 Мбайт/час — норма выработки: 15 страниц в час (80 символов на 25 строк).

Зр = 5159,49.

ЗА = tа*Цм+t0*(Цм+Ц0) — затраты на автоматизированную обработку информации.

tА = 2 (час) — время автоматической обработки.

Цм = 3 (руб/час) — стоимость одного часа машинного времени.

t0 = 8 — время работы оператора.

Ц0 = 43,75 — стоимость одного часа работы оператора.

ЗА = 380 Эу=5159,49−380=4779,49

Эг=Эу-Зк*5/365

Зк = 299 827,2727 — калькуляция расходов на разработку БД.

Эг = 4779,49−4107,23=672,26

Эр=(Эг*0,4)/Зк Эр = 0,22 — эффективность разработки базы данных.

9. Организация обмена данными между приложениями

Microsoft Universal Data Access представляет собой «стратегию обеспечения доступа ко всем типам информации, используемой в масштабах предприятия (фирмы). Она обеспечивает высокопроизводительный доступ к различным информационным источникам (включая как реляционные, так и не реляционные данные), в том числе к базам данных мэйнфреймов ISAM/VSAM, а также к иерархическим базам данных; файлам электронной почты и файловой системе; текстовым, графическим и географическим данным и так далее». Более того, поскольку хранение информации продолжает совершенствоваться, появляются новые форматы данных и это должно учитываться при разработке приложений.

Таким образом, нам действительно необходим некий универсальный метод доступа к данным, предоставляющий интерфейс, как существующим форматам данных, так и тем, которые могут появиться в будущем. Главная цель Microsoft Universal Data Access состоит в обеспечении доступа ко всем вышеупомянутым типам данных с помощью единой модели. Можно представить себе Universal Data Access как «ODBC для всех типов данных — реляционных и не реляционных». В настоящее время Universal Data Access взаимодействует со всеми оcновными платформами баз данных, а также с некоторыми источниками данных, не относящимися к СУБД, облегчая разработку приложений с использованием баз данных посредством общих интерфейсов. Архитектура Universal Data Access включает в себя элемент Microsoft ActiveX Data Objects (ADO), который используется для обмена данными между приложениями в данном курсовом проекте.

10. ТРЕБОВАНИЯ К ТЕХИЧЕСКОМУ ОБЕСПЕЧЕНИЮ

Для работы с приложением «Паспортный стол» необходим персональный компьютер со следующими минимальными характеристиками:

— микропроцессор Intel или AMD с тактовой частотой 1 ГГц и выше;

— ОЗУ — 512 Мбайт;

— свободное пространство на жестком диске — не менее 10 Мбайт;

— видеокарта — 128 Мбайт;

— монитор с диагональю не менее 17″ ;

— дисковод для записи/чтения данных;

— клавиатура;

— мышь;

— операционная система Windows 95/98/NT/ME/2000/XP/2003/Vista/7.

11. ИНСТРУКЦИЯ ПО ИСПОЛЬЗОВАНИЮ БД

11.1 Вызов программы

Программа не требует установки: необходимо скопировать на винчестер папку «Паспортный стол» и из нее запустить файл «Паспортный стол. accdb». На экране появится окно, в котором пользователю необходимо авторизоваться.

Рисунок 9.1 — Вход в систему база данные реляционный выборка По умолчанию в системе зарегистрировано 2 пользователя: «Пользователь» (пароль «1»), «Администратор» (пароль «2»). Каждый раз при запуске программы необходима авторизация пользователя.

11.2 Экранные формы

Работа программы основана на диалоге с пользователем через специальные экранные формы. Добавление и просмотр информации происходит с помощью форм и отчетов, показанных на рисунках 9.2 — 9.5.

Рисунок 9.2 — Главное меню программы Рисунок 9.3 — Форма «Паспорт»

Рисунок 9.4 — Отчет «Информация о гражданах»

Рисунок 9.5 — Отчет «Информация о паспортах»

ЗАКЛЮЧЕНИЕ

Реляционная модель данных в настоящее время приобрела большую популярность и практически все современные СУБД ориентированы именно на такое представление данных.

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

В данном проекте была создана реляционная база данных «Паспортный стол», разработанная с помощью СУБД Microsoft SQL Server 2008 и программной оболочки в Microsoft Access.

1. Карпова Т. С. Базы данных. Модели, разработка, реализация / СПб.: Питер, 2002. — 304 с.

2. Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных. Учебник для ВУЗов /под ред. проф. А. Д. Хомоненко // СПб.:КОРОНАпринт, 2000. — 416 с.

3. Корнеев В. В. и др. Базы данных. Интеллектуальная обработка информации // М.: Нолидж, 2000. — 352 с.

4. Дроздова В. И., Крахоткина Е. В., Федоров С. О. Базы данных. Методические указания к лабораторным работам для студентов специальности 351 400. Ставрополь, СевКавГТИ, 2002.

5. Дроздова В. И., Крахоткина Е. В. Методические указания к выполнению курсового проекта по дисциплине «Базы данных» для студентов специальности 351 400. Ставрополь, СевКавГТУ, 2004.

6. Каратыгин С. А., Тихонов А. Ф., Тихонова Л. Н. Visual FoxPro 6.0 // М.: Бином, 1999 — 784 с.

7. Ханcен Г., Ханcен Д. Базы данных. Разработка и управление / М.: Бином, 1999 — 704 с.

8. Баженова И. Ю. Visual Fox Pro 5.0 // М.: Диалог МИФИ, 1997 — 320 с.

9. Глушаков С. В., Ломотько Д. В. Базы данных. Учебный курс // Харьков: Фолио; Ростов н/Д: Феникс; Киев: Абрис, 2000. — 504 с.

ПРИЛОЖЕНИЕ А