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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ ТАДЖИКИСТАН

ТАДЖИКСКИЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

имени академика М.С. Осими

Кафедра: «АСОИ и У»

ОТЧЕТ

по лабораторной работе №4

на тему:

«Разработка базы данных»

Выполнил:

Принял:

Душанбе - 2009 г.

Теоретическое введение

В этой лабораторной работе будет описана база данных компании «Tajikunicom», которую я при поддержке своих коллег разработал и реализовал. База данных состоит из 10 функций, 70 процедур, 97 таблиц и многих других объектов административного уровня базы данных. Здесь я рассмотрю только некоторые из них.

Tajikunicom - это компания предоставляющая услуги фиксированной и стационарной связи.

Функции выполнения БД

Данная БД выполняет функции:

1. загона звонков в БД из китайских цифровых телефонных станций;

2. распределение информации по таблицам;

3. тарификация звонков исходя из тарифных планов;

4. подсчет стоимости разговора;

5. определение направления звонка и дальнейшая тарификация;

6. обработка данных, внесенные сотрудниками абонентского отдела и кассы;

7. обработка платежей и т.д.

В базе данных есть таблица сбора информации об абонентах, в нашей базе она называется Subscribers, таблица сбора всех произведенных звонков - calls200712, последние цифры означают год и месяц звонков которые содержит таблица, таблица тарифов - Tarifs, таблица существующих тарифных планов - PA_TP, таблица содержащая тарифный план каждого абонента - TP_SUB, таблица принятых платежей - Payments. Именно их я и опишу.

Таблица сбора информации об абонентах - Subscribers

NAME	ОПИСАНИЕ
RN	Номер записи
SNAME	Имя абонента
SSURNAME	Фамилия абонента
SFNAME	Отчество абонента
SADDRESS	Адрес абонента
WORKERID	Номер монтера, ответственного за абонента
ISTOPPED	Значение колонки показывает, остановлен телефон в настоящее время или нет
STOPTIME	Не используется
LINKINFO	Линейные данные абонента
INSTALLDOC	Документ, подтверждающий установку телефона
INSTALLDATE	Дата установки телефона
INSTALLREMARK	Замечания по установке телефона
STOPDOC	Документ, подтверждающий отключение телефона
STOPDATE	Дата отключения телефона, в случае если он отключен
STOPREMARK	Комментарий касательно отключения телефона
TELNUMBER	Номер телефона
PASSNUMBER	Номер паспорта
PASSISSBY	Кем выдан паспорт
PASSCITY	Место рождения
PASSDATE	Дата выдачи паспорта
PROPISKA	Прописка
CONTNUMBER	Контактный телефон абонента
CATEGORY	категория абонента
FACEACCNT	Лицевой счет абонента
SUBSGROUP	Группа, в которую входит абонент

Таблица звонков callsyyyydd

NAME	ОПИСАНИЕ
TELFROM	Номер телефона, с которого был произведен звонок
TELTO	Номер телефона, на который был произведен звонок
CALLMARK	Не используется
CAT1	Станционная категория звонка
CAT2	Категория звонка, связанная с таблицей CATEGORIES
STARTTIME	Время начала разговора
ENDTIME	Время окончания разговора
CALLTYPE	Не используется
TARIF	Тариф
DURATION	Длительность (в минутах)
COST	Стоимость (в сомони)
INTRK	Входящий транк
OUTTRK	Исходящий транк
TELFROMADR	Не используется
TELTOADR	Не используется

Таблица тарифных планов - PA_TP

NAME	ОПИСАНИЕ
TP_ID	Номер тарифного плана
TP_NAME	Наименование тарифного плана
DATE_FROM	Начало действия тарифного плана
DATE_TO	Конечная дата действия тарифного плана
ABON_PAY	Абонентская плата
ACTIVE	Индикатор действия тарифного плана
AB_DISCOUNT	Скидка на абонплату

Таблица тарифов - Tarifs

NAME	ОПИСАНИЕ
RN	Номер записи
SDIRECTION	Направление звонка
TARIFMASK	Маска тарифа
TARIFCOST	Стоимость минуты разговора
NGROUP	Группа
NCATEGORY	Категория
TP_ID	Номер тарифного плана

Таблица, содержащая тарифный план каждого абонента - TP_SUB

NAME	ОПИСАНИЕ
RN_TP	Номер записи
TELNUMBER	Номер абонента
TP_ID	Номер тарифного плана
DATE_FROM	Дата начала перехода на тарифный план
DATE_TO	Дата окончания действия тарифного плана
PERIOD_ID	Номер периода

Таблица принятых платежей - Payments

NAME	ОПИСАНИЕ
RN	номер записи
TELRN	Номер записи телефона
NMONTH	Месяц за который начисляется оплата
NYEAR	Год, за который начисляется оплата
PDATE	Дата платежа
NSUM	Сумма платежа
NUSERID	Userid пользователя, который регистрировал (вносил) платеж, из таблицы billusers, который соответствует user_id из системного представления all_users

Процедура тарификации

create or replace function GETTARIFCOST3(numberto varchar2

, stelfromg varchar2, calldate date

, ndur number, ncost out number

, ncat out number) return number is

Result number;

nrn TARIFS.rn%type;

ntp_id number;

ffc_from number;

ffc_to number;

sub_gr number;

sstelfromg varchar2(40);

nnumberto varchar2(40);

ccalldate date;

begin

ccalldate:=to_date(calldate, 'dd.mm.yyyy');

sstelfromg:=stelfromg;

nnumberto:=numberto;

--расчет звонков для корпоративных клиентов, также признак корпоративности возможно пересмотреть

select su.subsgroup into sub_gr from subscribers su

where su.telnumber=sstelfromg and su.faceaccnt is not null;

if numberto like '4%' and stelfromg like '4%' and sub_gr='777' then

select sb.faceaccnt into ffc_from from subscribers sb

where sb.telnumber=sstelfromg and sb.faceaccnt is not null;

select ss.faceaccnt into ffc_to from subscribers ss

where ss.telnumber=nnumberto and ss.faceaccnt is not null;

select ts.rn into nrn from tarifs ts where ts.sdirection='Корпоративный звонок';

if ffc_from=ffc_to and sub_gr='777' then

ncost:=0;

ncat:=0;

result:=nrn;

return(Result);

end if;

end if;

--расчет звонков исходящих звонков в районах

if (stelfromg like '4%' and length(stelfromg)=7)

or (stelfromg like '5%' and length(stelfromg) in (3,6)) then

--select s.tp_id into ntp_id from tp_sub s where s.telnumber=stelfromg and s.date_to is null;

select s.tp_id into ntp_id from tp_sub s where s.telnumber=stelfromg

and to_date(s.date_from, 'dd.mm.yyyy') <= ccalldate

and (to_date(s.date_to, 'dd.mm.yyyy') > ccalldate or s.date_to is null);

if substr(numberto,1,1) in ('2','3','4','5','6') and length(numberto)=5 then

select t.rn,t.tarifcost*ndur,t.ncategory

into nrn, ncost, ncat

from tarifs t, tp_sub s where t.sdirection ='Душанбе (ДГТС)'

and s.telnumber=stelfromg and t.tp_id=s.tp_id and s.tp_id=ntp_id;

result:=nrn;

return(Result);

end if;

--расчет звонков для всех осталиных исходящих звонков

select rn, tarifcost*ndur, ncategory into nrn, ncost, ncat from

(select t.rn, t.ncategory, nvl(length(replace(replace(t.tarifmask,'_',''),'%','')),-1) len

, sdirection, tarifmask, tarifcost from TARIFS t

where numberto like t.tarifmask and t.tp_id=ntp_id

) r where r.len=

(select max(nvl(length(replace(replace(t.tarifmask,'_',''),'%','')),-1))

from TARIFS t where numberto like t.tarifmask and t.tp_id=ntp_id)

and rownum=1;

result:=nrn;

return(Result);

else

nrn:=0;

ncost:=0;

ncat:=0;

result:=nrn;

return(Result);

end if;

exception

when no_data_found then

nrn:=0;

ncost:=0;

ncat:=0;

result:=nrn;

return(Result);

end GETTARIFCOST3;

Процедура добавления тарифного плана

create or replace procedure ADD_TP

(STP_NAME varchar2,

SDATE_FROM date,

SDATE_TO date,

SABON_PAY number,

SACTIVE number,

SAB_DISCOUNT number)

As

STP_ID number;

srn number;

sdirection varchar2(40);

tarifmask varchar2(40);

tarifcost number(10,2);

ngroup number;

ncategory number;

begin

select max(p.tp_id) into STP_ID from pa_tp p;

STP_ID:=STP_ID+1;

insert into pa_tp values (STP_ID,STP_NAME,SDATE_FROM,SDATE_TO,SABON_PAY,SACTIVE,SAB_DISCOUNT);

select max(t.rn) into srn from tarifs t;

for i in (select rn, sdirection, tarifmask, tarifcost, ngroup, ncategory, tp_id from tarifsmain) loop

srn:=srn+1;

insert into tarifs t values (srn, i.sdirection, i.tarifmask, i.tarifcost, i.ngroup, i.ncategory, STP_ID);

end loop;

end;

Частичная структура БД

Заключение

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

Литература

1. Курс лекций по программированию на Visual Basic 6.0

2. Курс лекций по «Теории Принятия Решений»

3. Курс лекций по «Теоретические Основы Автоматизированного Управления»