Разработка базы данных для решения задач городской телефонной сети с использованием СУБД Access

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

Базы данных создаются и используются с помощью специальных программных и языковых средств, называемых Системами Управления Базами Данных (СУБД). Существует большое разнообразие типов СУБД. Наиболее распространенными в настоящее время являются реляционные СУБД, к которым и относится Access.

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

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

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

В моем курсовом проекте поставлены задачи по выводу с помощью запросов следующей информации:

1. Перечень и общее число абонентов по указанной АТС.

2. Перечень и общее число абонентов по всей ГТС.

3. Перечень абонентов, имеющих параллельный телефон.

4. Перечень должников по указанной АТС.

5. Перечень и общее число должников по всей ГТС.

6. Перечень льготников по указанной АТС.

7. Перечень и общее число льготников по всей ГТС.

8. Данные об абоненте по указанному адресу.

9. Данные об абоненте по указанному номеру телефона.

10. Данные об абоненте по фамилии.

11. Перечень свободных номеров по указанной АТС.

12. Перечень и общее число свободных номеров по всей ГТС.

13. Перечень таксофонов по указанной АТС.

14. Перечень и общее число таксофонов по всей ГТС.

15. Самую большую задолженность по всем АТС.

16. Самую минимальную задолженность по всем АТС.

При проектировании базы данных предполагается использование метода канонического проектирования, а для ее реализации — использование программы Microsoft Access 2002, а также языков SQL (Structured Query Language) — структурированный язык запросов и QBE (Quere-By-Example) — запросы по образцу.

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

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

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

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

1.2 Определение состава данных

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

Форма 1. Форма справочного документа «Сведения об абонентах»

Форма 2. Форма справочного документа «Список свободных номеров»

Форма 3. Форма справочного документа «Перечень таксофонов»

Форма 4. Форма справочного документа «Очередь на подключение»

Форма 5. Форма учетного документа «Оплата услуг»

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

Разработка концептуальной модели

Концептуальная модель является представлением точки зрения пользователя на предметную область и не зависит ни от программного обеспечения СУБД, ни от технических решений. Она стабильна.

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

Экземпляр сущности — конкретный объект.

Сущность принято определять атрибутами — поименованными характеристиками.

1.3 Выявление информационных объектов

Определим функциональные зависимости реквизитов справочного документа «Сведения об абонентах» и выделим информационный объект. Функциональная зависимость реквизитов имеет место только в том случае, если одному значению ключа соответствует только одно значение зависимого (описательного) реквизита. Функциональную зависимость реквизитов можно изобразить графически в виде линий со стрелками, идущими от ключевого реквизита к зависимому реквизиту. Ключевой реквизит подчеркивается.

Установим функциональные зависимости между реквизитами всех справочных документов. Сначала для документа «Сведения об абонентах».

Таблица 2.1. Функциональная зависимость реквизитов

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

Таблица 2.2

Рассмотрим схему отношений: КАТС, ТЕЛ, АД, ПД, Ф.И.О., ПОЛ, ДР, ЛГ, ТТЕЛ, ТАР.

Первичный ключ: КАТС, ПД.

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

— информационный объект должен содержать уникальный идентификатор — первичный ключ;

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

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

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

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

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

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

Таблица 2.3. Группировка реквизитов ИО

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

Аналогично выполняем анализ документа «Список общественных телефонов и таксофонов».

Таблица 2.4. Функциональная зависимость реквизитов

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

Таблица 2.5

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

Таблица 2.6. Группировка реквизитов ИО

Анализ документа «Список свободных номеров».

Таблица 2.7

Анализ документа «Очередь на подключение».

Таблица 2.8

Таблица 2.9

Проанализируем учетный документ «Оплата услуг»:

Таблица 2.10

1.4 Выявление связей информационных объектов

Существуют следующие типы отношений между объектами:

— Один — к — одному (1:1), когда каждому экземпляру первого информационного объекта соответствует только один экземпляр второго информационного объекта

— Один — ко — многим (1:М), когда каждому экземпляру одного информационного объекта соответствует несколько экземпляров другого информационного объекта, а каждому экземпляру второго информационного объекта соответствовать не более одного экземпляра первого ИО.

— Многие — ко многим (М:М), когда каждому экземпляру одного информационного объекта соответствует несколько экземпляров другого информационного объекта и каждому экземпляру второго информационного объекта может соответствовать несколько экземпляров первого.

Итак, мы выделили следующие информационные объекты или сущности: АТС, АБОНЕНТ, ТАКСОФОН, СВОБОДНЫЙ НОМЕР, ОЧЕРЕДНИК, ОПЛАТА.

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

Таблица 2.11

1.5 Построение информационно-логической модели БД

Для построения информационно-логической модели сначала построим матрицу смежности — это квадратная матрица по числу ИО, которая заполняется по строкам. Элемент матрицы на пересечении строки и столбца равен 1, если ИО, стоящий в строке, связан с ИО, стоящим в столбце, отношением один ко многим, тип функциональной связи во внимание не принимается.

Таблица 2.12. Матрица смежности ИЛМ

Цифрами обозначены ИО: 1 — АТС; 2 — Таксофон, 3 — Свободный номер; 4 — Очередник; 5 — Абонент; 6 — Оплата.

Алгоритм расположения ИО по уровням иерархии:

1. Вычислить итоговые суммы элементов матрицы по столбцам.

2. Выделить ИО столбцов для которых итоговая сумма равна 0, отсутствие нулевых итоговых сумм свидетельствует об ошибке в определении типа соответствия.

3. Удалить строки матрицы смежности соответствующие ИО текущего уровня иерархии.

4. Для перехода к следующему уровню иерархии следует повторить пункт 3−4.

Информационно-логическая модель, расположенная в ПРИЛОЖЕНИИ 2, построена в соответствии с выявленными информационными объектами и связями между ними.

Информационно-логическая модель приведена к каноническому виду, и объекты расположены в ней по уровням. На нулевом уровне располагаются объекты не подчиненные никаким другим объектам, на 1 уровне подчиненные 0 уровню и т. д. Такое размещение объектов дает представление об иерархической подчиненности, делает модель более наглядной и облегчает понимание зависимостей 1: М.

Определение логической структуры БД

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

1.6 Описание работы с программой «Городская телефонная сеть»

Программа, с условным названием «Городская телефонная сеть», предназначена для автоматического поиска необходимых абонентов, свободных номеров, таксофонов, а также для выдачи различных отчетов, как по конкретной АТС, так и по всей ГТС.

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

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

Работа начинается с основного окна, на котором отображается заголовок, и четыре кнопки.

При нажатии на кнопки:

— «Выход из программы» работа с программой будет завершена,

— «Окно базы данных» — основное окно будет закрыто, зато появиться возможность работать со всей базой данных,

— «Просмотр и редактирование данных» — откроется форма с вариантами таблиц, которые мы будем редактировать,

— «Поиск» — откроется форма, на которой выбирается необходимый запрос.

1.7 Заполнение таблиц

Заполнение таблицы «Абоненты» происходит следующим образом: при открытии формы курсор стоит на коде АТС, его мы выбираем из списка, далее нажимаем «ENTER», и вводим фамилию, затем имя и т. д. Введя все необходимы данные, нажимаем на кнопку «Сохранить».

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

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

Аналогично заполняются и все остальные таблицы.

1.8 Выполнение запросов

На форме «Выполнение запросов» можно выбрать интересующий пользователя запрос нажатием на соответствующую кнопку. Рассмотрим на примере поиска абонента по фамилии. Нажимаем на кнопку и у нас появляется окошко с просьбой ввести фамилию.

После того как ввели фамилию, необходимо нажать на «OK». Тогда получим результат.

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

2. Оценка экономической эффективности

2.1 Смета затрат

Рассчитаем смету затрат на разработку БД «Городская телефонная сеть» и представим ее в таблице.

база телефонный смета городской Таблица 3.1

Определение экономической эффективности

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

На ручную обработку до внедрения ИС затрачивалось Т0=12 чел./ч. При использовании ИС Т1=4 чел./ч. Абсолютный показатель экономической эффективности Т_эк составляет:

Т_эк = Т₀ — Т₁ =12−4=8

Относительный индекс производительности труда вычисляется по формуле:

J _п.т.= Т₁ /Т₀=4/12=0,33

Он отражает экономию трудовых затрат на 0,33.

Рассчитаем стоимостной показатель, где С₀=20 000 руб. — затраты до внедрения ИС, С₁=8000 руб. — затраты после внедрения ИС:

С_эк = С₀ — С₁ =20 000−8000=12 000 руб.

Индекс стоимости затрат:

J _{ст.затр.}= С₁ /С₀ =8000/20 000=0,4

Срок окупаемости затрат, где З₀ =90 000 руб. — затраты на техническое оборудование; П₀=45 000 руб. — затраты на программное обеспечение; К_эф=0,4 — коэффициент эффективности:

Следовательно, через 4,5 года наш проект окупится.

Заключение

Результатом проделанной мной работы является программа «Городская телефонная сеть», функционально выполняющая основные задачи по выводу с помощью запросов следующей информации:

Перечень и общее число абонентов по указанной АТС.

Перечень и общее число абонентов по всей ГТС.

Перечень абонентов, имеющих параллельный телефон.

Перечень должников по указанной АТС.

Перечень и общее число должников по всей ГТС.

Перечень льготников по указанной АТС.

Перечень и общее число льготников по всей ГТС.

Данные об абоненте по указанному адресу.

Данные об абоненте по указанному номеру телефона.

Данные об абоненте по фамилии.

Перечень свободных номеров по указанной АТС.

Перечень и общее число свободных номеров по всей ГТС.

Перечень таксофонов по указанной АТС.

Перечень и общее число таксофонов по всей ГТС.

Самую большую задолженность по всем АТС.

Самую минимальную задолженность по всем АТС.

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

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