Структурирование предметной области и создание реляционной базы данных

Содержание

Стр.

Введение . . . . . . . . . . 3

Структурирование предметной области и создание реляционной базы данных 5

Заключение . . . . . . . . . . 18

Введение

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

Задание: Учет расчетов за услуги, оказываемые оператором связи.

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

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

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

База данных должна реализовывать следующие функции:

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

Структурирование предметной области и создание реляционной базы данных

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

Заполним матрицу отношений типов объектов (таблица 1.1). В таблице 1.1 представлены только прямые зависимости типа «один ко многим».

Таблица 1.1. Матрица отношений типов объектов предметной области

«Учет расчетов за услуги, оказываемые оператором связи»

Согласно матрице отношений построим схему структуры предметной области и представим ее на рисунке 1.1.

Рисунок 1.1 Схема структуры предметной области

«Учет расчетов за услуги, оказываемые оператором связи»

Выше на схеме изображены родительские типы объектов, ниже – дочерние.

Все отношения между типами объектов, представленные на рисунке 1.1 на схеме структуры предметной области, имеют вид «один ко многим». Одному абоненту может принадлежать несколько телефонных номеров. По одному телефонному номеру может быть несколько оплат, несколько начислений. Отношение между типами объектов «Тарифы» и «Телефонные номера» имеет вид «многие ко многим», т. к. у одного тарифа может быть несколько номеров, и у одного телефонного номера может быть несколько тарифов. Отношение между типами объектов «Тарифы» и «Телефонные номера» является существенным и должно быть отражено на схеме структуры предметной области. Чтобы это сделать, в структуре предметной области выделим еще один тип объектов – «История тарифа», и отношением типа «многие ко многим» между типами объектов «Тарифы» и «Телефонные номера» отображается при помощи двух отношений типа «один ко многим», а именно: отношения между типами «Тарифы» и «История тарифов», а также отношения между типами объектов «Телефонные номера» и «История тарифов».

Определим набор таблиц базы данных. Каждому объекту предметной области будет соответствовать линейная таблица, т. е. база данных будет состоять из шести таблиц: Абоненты, Тарифы, ТелефонныеНомера, Оплата, Начисления, ИсторияТарифа.

Таблица 1.2 Словарь имен базы данных

«Учет расчетов за услуги, оказываемые оператором связи»

Составим словарь имен. Результаты этого шага проектирования базы данных представим в таблице 1.2 (отсортируем по алфавиту для удобства нахождения).

Определим состав, типы и размер полей для каждой из таблиц базы данных. При назначении полям системных имен обратимся к  сокращениям, принятым в словаре имен. Состав, типы полей, их системные имена и размеры приведены в таблице 1.3. Жирным шрифтом в каждой из таблиц выделены ключевые поля.

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

Таблица 1.3 Состав полей таблиц базы данных

«Учет расчетов за услуги, оказываемые оператором связи»

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

После создания структуры таблиц, установим между ними связи. Для этого добавим все таблицы базы и установим соответствующие связи. При этом обеспечим целостность данных, а также каскадное обновление. На рисунке 1.2 представлена схема данных базы «Учет расчетов за услуги, оказываемые оператором связи».

Рисунок 1.2 Схема данных базы

«Учет расчетов за услуги, оказываемые оператором связи»

Для облегчения процесса ввода данных создадим поля со списками (соответствуют части «ко многим» отношения «один ко многим»).

Следующий шаг – создание форм. Создадим для каждой из таблиц при помощи мастера форм по одной форме. На рисунке 1.3 представлена форма ввода данных для таблицы «Абоненты».

По завершении разработки форм заполнит таблицы: сначала родительские, затем дочерние (для обеспечения целостности данных).

В таблицах 1.4 – 1.9 представлены исходные данные базы «Учет расчетов за услуги, оказываемые оператором связи».

Рисунок 1.3 Форма ввода данных для таблицы «Абоненты»

Таблица 1.4 – Таблица «Абоненты»

Таблица 1.5 – Таблица «Тарифы»

Таблица 1.6 – Таблица «Телефонные номера»

Таблица 1.7 – Таблица «Оплата»

Таблица 1.8 – Таблица «Начисления»

Таблица 1.9 – Таблица «История тарифов»

Следующий шаг – создание запросов.

1. Вывести список всех телефонных номеров, зарегистрированных на определенного абонента.

Запрос на выборку: требуется вывести все номера телефонов, зарегистрированных на одного абонента (например, с фамилией «Зуев»).

Скрин-шот конструктора запроса 1 представим на рисунке 1.4.

Рисунок 1.4 Скрин-шот конструктора запроса

«Список всех телефонных номеров, зарегистрированных на определенного абонента»

Текст запроса на языке SQL будет иметь следующий вид:

SELECT Абоненты.НомПасп, Абоненты.ФамАб, Абоненты.ИмяАб, Абоненты.ОтчАб, [Телефонные номера].НомТел

FROM Абоненты INNER JOIN [Телефонные  номера]

ON Абоненты.НомПасп = [Телефонные номера].НомПасп

WHERE (((Абоненты.ФамАб)="Зуев"));

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

Таблица 1.10 – Результаты выполнения запроса

«Список всех телефонных номеров, зарегистрированных на определенного абонента»

2. Определить суммы начислений за оказанные услуги по каждому абоненту.

Запрос на выборку с группировкой: необходимо определить сумму начислений по всем номерам, принадлежащим абоненту, и вывести на экран сумму начислений по всем абонентам.

Скрин-шот конструктора запроса на выборку с группировкой представим на рисунке 1.5.

Рисунок 1.5 Скрин-шот конструктора запроса

«Суммы начислений за оказанные услуги по каждому абоненту»

Текст запроса на выборку с группировкой на языке SQL:

SELECT Абоненты.ФамАб, Абоненты.ИмяАб, Абоненты.ОтчАб, Sum(Начисления.СумНачисл) AS [Sum-СумНачисл]

FROM (Абоненты INNER JOIN [Телефонные  номера]

ON Абоненты.НомПасп = [Телефонные номера].НомПасп)

INNER JOIN Начисления

ON [Телефонные номера].НомТел = Начисления.НомТел

GROUP BY Абоненты.НомПасп, Абоненты.ФамАб, Абоненты.ИмяАб, Абоненты.ОтчАб;

При создании запроса на выборку с группировкой была использована агрегатная функция Sum: для определения суммы по полю [СумНачисл] для каждого абонента.

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

Таблица 1.11 – Результаты выполнения запроса

«Суммы начислений за оказанные услуги по каждому абоненту»

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

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

Скрин-шот конструктора дополнительного запроса представим на рисунке 1.6.

Рисунок 1.6 Скрин-шот конструктора дополнительного запроса

«Последний активированный тариф на номере телефона»

Текст запроса на языке SQL:

SELECT [История тарифов].НомТел,

Last([История тарифов].ДатаТариф) AS [Last-ДатаТариф],

Last(Тарифы.НаимТариф) AS [Last-НаимТариф]

FROM Тарифы INNER JOIN [История  тарифов] ON Тарифы.КодТариф = [История тарифов].КодТариф

GROUP BY [История тарифов].НомТел;

При создании дополнительного запроса (на выборку с группировкой) была использована агрегатная функция Last: для определения последнего значения по полю [ДатаТариф] для каждого телефона.

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

Таблица 1.12 – Результаты выполнения дополнительного запроса

«Последний активированный тариф на номере телефона»

Теперь реализуем на основании дополнительного запроса запрос 3: «Количество телефонных номеров, обслуживающихся на каждом тарифе в настоящее время».

Скрин-шот конструктора запроса 3 представим на рисунке 1.7.

Рисунок 1.7 Скрин-шот конструктора запроса «Количество телефонных номеров,

обслуживающихся на каждом тарифе в настоящее время»

Текст запроса 3 на языке SQL:

SELECT [Запрос 3 доп].[Last-НаимТариф],

Count([Запрос 3 доп].НомТел) AS [Count-НомТел]

FROM [Запрос 3 доп]

GROUP BY [Запрос 3 доп].[Last-НаимТариф];

При создании запроса (на выборку с группировкой) была использована агрегатная функция Count: для определения количества по полю [НомТел] для каждого тарифа.

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

Таблица 1.13 – Результаты выполнения запроса «Количество телефонных номеров,

обслуживающихся на каждом тарифе в настоящее время»

4. Определить текущий платежный баланс по каждому телефонному номеру.

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

Скрин-шот конструктора запроса 4 представим на рисунке 1.8.

Рисунок 1.8 Скрин-шот конструктора запроса

«Текущий платежный баланс по номеру телефона»

Текст запроса 4 на языке SQL:

SELECT Абоненты.ФамАб, [Телефонные номера].НомТел,

Sum(Начисления.СумНачисл) AS [Сумма начисления],

Sum(Оплата.СумОпл) AS [Сумма оплата],

Sum([СумОпл]-[СумНачисл]) AS Баланс

FROM ((Абоненты INNER JOIN [Телефонные  номера] ON Абоненты.НомПасп = [Телефонные номера].НомПасп) INNER JOIN Начисления ON [Телефонные номера].НомТел = Начисления.НомТел) INNER JOIN Оплата ON [Телефонные номера].НомТел = Оплата.НомТел

GROUP BY Абоненты.ФамАб, [Телефонные номера].НомТел

ORDER BY Абоненты.ФамАб;

При создании запроса на выборку с группировкой была использована агрегатная функция Sum: для определения суммы по полю [СумНачисл] и [СумОпл] для каждого абонента (начислений может быть несколько, так же как и оплат). Та же функция использована и для расчета баланса.

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

Таблица 1.14 – Результаты выполнения запроса

«Текущий платежный баланс по номеру телефона»

На основе запроса 4 создадим отчет (с использованием мастера, а затем отредактируем в режиме конструктора).

Скрин-шот конструктора отчета представим на рисунке 1.9.

Рисунок 1.9 Скрин-шот конструктора отчета «Текущий платежный баланс»

Готовый отчет представлен на рисунке 1.10.

Рисунок 1.10 Отчет «Текущий платежный баланс»

Заключение

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

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

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

• сбор, обработка и ввод первичной информации об абонентах и предоставленных им телефонных номерах (формы «Абоненты», «Телефонные номера»);
• регистрация и контроль платежей (формы «Начисления», «Оплата», запрос «Платежный баланс»);
• ведение справочной информации по услугам, тарифам, и пр. (формы «История тарифов», «Начисления», запросы на выборку с группировкой 1, 3);
• тарификация и расчет платежей по предоставленным услугам связи (форма «Начисления», запросы на выборку с группировкой 2, 4);
• информационно-справочное обслуживание абонентов и пользователей системы (запросы на выборку с группировкой 1 - 4);
• формирование отчетности по оказанным услугам, категориям абонентов и пр. (запросы на выборку с группировкой 1 - 4);