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

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);