Содержание

 

ВВЕДЕНИЕ

 

     Приложение  «Providers» было разработано по заказу ставропольского филиала организации «StavropololoProvider», которое предоставляет услуги связи. Данное приложение разработано для мониторинга конкуренции на рынке интернет-провайдеров. База данных занимает одно из главенствующих мест в  системе учета и контроля данного предприятия и требует особо внимания к своей работоспособности, при ее разработке учитывались мнение, как начальников данной организации, так и простых операторов ПК, непосредственно работающих  с базой данных. Данное приложение использует технологии .Net Framework 2.0 в алгоритмически-логическом аспекте приложения. В качестве хранилища данных была использована реляционная база, управляемая языком SQL, из наличия использованных средств следует, что данное приложение будет использовано на операционных системах семейства Windows.

 

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

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

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

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

     Кроме доступа в интернет, провайдеры могут предоставля дополнительные услуги, такие как IP-телевидение, электронная почта и т.д.

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

2.2  Описание входных документов и сообщений 

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

 

      2.3  Описание выходных документов и сообщений

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

2.4  Список ограничений 

     

1. Ограничения, связанные с типом данных полей.
2. Максимальные длины строковых данных: названия регионов, организаций, тарифных планов и технологий – 50 символов, адреса – 100 символов, номера телефонов – 12 символов.
3. Ограничение связанное с форматом e-mail.
4. Названия организаций должны быть уникальными.

 

3 ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

 

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

3.1  Инфологическая модель базы данных

     При концептуальном проектировании были выбраны основные сущности:

     1) «провайдер»;

     2) «филиал»; 

     3) «услуга»;

     4) «тариф»; 

     5) «технология»;

     6) «регион».  

3.1.1  Описание сущностей 

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

     1) сущность «провайдер»

Id

Название

Адрес

Телефон

Веб-сайт

E-mail

     2) сущность «филиал»

Id

Название

Количество  пользователей

Адрес

Телефон

Веб-сайт

E-mail

Провайдер

Регион

Дата  открытия

     3) сущность «услуга»

Id

Название

     4) сущность «тариф»

Id

Название

Скорость

Месячная  плата

Цена  за мегабайт

Цена  за час

Технология

Филиал

     5) сущность «технология»

Id

Название

Максимальная  скорость

Признак беспроводности

     6) сущность «регион»

Id

Название

Население 

3.1.2  Описание связей 

      Классификация связей приведена в таблице 2.1

     Таблица 2.1 «Классификация связей»

 

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

 

3.1.3  ER-диаграмма

Рисунок 3.1 – ER-диаграмма 
 

 

3.2  Даталогическая модель

3.2.1  Диаграмма связи по  полям 

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

     Таблица 2.2– состав таблицы Providers

 
 

     Таблица 2.3– состав таблицы Affiliates

 
 

 

      Таблица 2.4 – состав таблицы Tariffs

 
 

     Таблица 2.5 – состав таблицы Technology

 
 

     Таблица 2.5 – состав таблицы Services

 
 

 

      Таблица 2.5 – состав таблицы AffiliateServices

 
 

     Таблица 2.5 – состав таблицы Regions

4 Организация выборки информации из базы данных

 

     В данной работе используются следующие  запросы:

1. Выборка тарифов по технологиям xDSL

SELECT DISTINCT dbo.Regions.Name, dbo.Providers.Name AS Expr1, dbo.Tariffs.Name AS Expr2

FROM         dbo.Regions INNER JOIN

                      dbo.Affiliates INNER JOIN

                      dbo.Providers ON dbo.Affiliates.Provider_id = dbo.Providers.id INNER JOIN

                      dbo.Tariffs ON dbo.Affiliates.id = dbo.Tariffs.Affiliate_id INNER JOIN

                      dbo.Technology ON dbo.Tariffs.Technology_id = dbo.Technology.id ON dbo.Regions.id = dbo.Affiliates.Region_id

WHERE     (dbo.Technology.Name LIKE '%DSL')

2. Выборка тарифов с высокой скоростью (более 512 Мбит/сек)

SELECT DISTINCT

                      dbo.Regions.Name AS Регион, dbo.Providers.Name AS Провайдер, dbo.Tariffs.Name AS Тариф, dbo.Tariffs.MonthFee,

                      dbo.Technology.Name AS Технология, dbo.Tariffs.Speed

FROM         dbo.Regions INNER JOIN

                      dbo.Affiliates INNER JOIN

                      dbo.Providers ON dbo.Affiliates.Provider_id = dbo.Providers.id INNER JOIN

                      dbo.Tariffs ON dbo.Affiliates.id = dbo.Tariffs.Affiliate_id INNER JOIN

                      dbo.Technology ON dbo.Tariffs.Technology_id = dbo.Technology.id ON dbo.Regions.id = dbo.Affiliates.Region_id

WHERE     (dbo.Tariffs.Speed > 512)

Выбор услуг, предоставляемых провайдером  RosTelecom в различных регионах

SELECT DISTINCT dbo.Regions.Name AS Регион, dbo.Services.Name AS Услуга

FROM         dbo.Regions INNER JOIN

                      dbo.Affiliates INNER JOIN

                      dbo.Providers ON dbo.Affiliates.Provider_id = dbo.Providers.id ON dbo.Regions.id = dbo.Affiliates.Region_id INNER JOIN

                      dbo.AffiliateServices ON dbo.Affiliates.id = dbo.AffiliateServices.Affiliate_id INNER JOIN

                      dbo.Services ON dbo.AffiliateServices.Service_id = dbo.Services.id

WHERE     (dbo.Providers.Name = 'RosTelecom')

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

SELECT DISTINCT TOP (100) PERCENT Regions_1.Name AS Регион, Providers_1.Name AS Провайдер,

                          (SELECT     COUNT(*) AS Expr1

                            FROM          dbo.Affiliates

                            WHERE      (Region_id = Regions_1.id) AND (Provider_id = Providers_1.id)) AS [Количество филиалов],

                          (SELECT     STR(SUM(Users)) AS Expr1

                            FROM          dbo.Affiliates AS Affiliates_2

                            WHERE      (Region_id = Regions_1.id) AND (Provider_id = Providers_1.id)) + '/' +

                          (SELECT     STR(SUM(Users)) AS Expr1

                            FROM          dbo.Affiliates AS Affiliates_2

                            WHERE      (Region_id = Regions_1.id)) AS [Доля пользователей в регионе]

FROM         dbo.Providers AS Providers_1 INNER JOIN

                      dbo.Affiliates AS Affiliates_1 ON Providers_1.id = Affiliates_1.Provider_id INNER JOIN

                      dbo.Regions AS Regions_1 ON Regions_1.id = Affiliates_1.Region_id

4. Выборка, показывающая интернетификацию регионов (отношение пользователей интернета к населению)

SELECT DISTINCT TOP (100) PERCENT Name AS Регион,

                          (SELECT     SUM(Users) AS Expr1

                            FROM          dbo.Affiliates AS Affiliates_2

                            WHERE      (Region_id = Regions_1.id)) AS [Пользователей в регионе], Population * 1000 AS Население,

                          (SELECT     CAST(SUM(Users) AS float) AS Expr1

                            FROM          dbo.Affiliates AS Affiliates_2

                            WHERE      (Region_id = Regions_1.id)) / (Population * 1000) AS [Кол. Интернетов]

FROM         dbo.Regions AS Regions_1

ORDER BY [Кол. Интернетов]

5. Выборка безлимитных тарифов

SELECT DISTINCT

                      dbo.Regions.Name AS Регион, dbo.Providers.Name AS Провайдер, dbo.Tariffs.Name AS Тариф, dbo.Tariffs.MonthFee,

                      dbo.Technology.Name AS Технология

FROM         dbo.Regions INNER JOIN

                      dbo.Affiliates INNER JOIN

                      dbo.Providers ON dbo.Affiliates.Provider_id = dbo.Providers.id INNER JOIN

                      dbo.Tariffs ON dbo.Affiliates.id = dbo.Tariffs.Affiliate_id INNER JOIN

                      dbo.Technology ON dbo.Tariffs.Technology_id = dbo.Technology.id ON dbo.Regions.id = dbo.Affiliates.Region_id

WHERE     (dbo.Tariffs.TrafficPrice = 0)

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

SELECT DISTINCT

                      dbo.Regions.Name AS Регион, dbo.Providers.Name AS Провайдер, dbo.Tariffs.Name AS Тариф, dbo.Tariffs.MonthFee,

                      dbo.Technology.Name AS Технология

FROM         dbo.Regions INNER JOIN

                      dbo.Affiliates INNER JOIN

                      dbo.Providers ON dbo.Affiliates.Provider_id = dbo.Providers.id INNER JOIN

                      dbo.Tariffs ON dbo.Affiliates.id = dbo.Tariffs.Affiliate_id INNER JOIN

                      dbo.Technology ON dbo.Tariffs.Technology_id = dbo.Technology.id ON dbo.Regions.id = dbo.Affiliates.Region_id

WHERE     (dbo.Technology.Wire = 'False')

 

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

 

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

     

     Рисунок 5.1 – представление результатов 1й выборки 

     

     Рисунок 5.2 – представление результатов 2й выборки 

     

     Рисунок 5.3 – представление результатов 3й выборки

     

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

     

     Рисунок 5.5 – представление результатов 5й выборки 

     

     Рисунок 5.6 – представление результатов 6й выборки 

     

     Рисунок 5.7 – представление результатов 7й выборки 

 

6  Проектирование хранимых процедур

 

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

     1. Для определения интернетификации региона:

CREATE PROCEDURE [dbo].[GET_INTERNETS]  @REGION varchar(50)

AS

BEGIN

SET NOCOUNT ON; 

SELECT  CAST(SUM(Users) AS float) / (SELECT Population * 1000 FROM REGIONS WHERE Name =@REGION) AS Internets

FROM          Affiliates

WHERE      Region_id = (SELECT id  FROM REGIONS WHERE Name =@REGION)

END

Рисунок 6.1 Результаты работы хранимой процедуры 1

     2. Для увеличения (или уменьшения) населения региона:

CREATE PROCEDURE [dbo].[INCREASE_POPULATION]

      @REGION varchar(50),

      @NUM int

AS

BEGIN

      SET NOCOUNT ON;

      UPDATE REGIONS

SET POPULATION=POPULATION+@NUM

WHERE RTRIM(LTRIM(NAME))=@REGION

END

Рисунок 6.2 Результаты работы хранимой процедуры 2

 

     7 Разработка механизмов управления данными в базе при помощи 
      триггеров

7.1  Триггер для добавления данных

     1. Триггер для вставки и обновления данных в таблице Providers,    не допускающий повторения названий провайдеров

CREATE TRIGGER [dbo].[CINSERTION_CHECK]

   ON  [dbo].[Providers]

   FOR INSERT, UPDATE

AS

BEGIN

      SET NOCOUNT ON;

      IF (SELECT Count(*) FROM [dbo].[Providers], [inserted]

                  WHERE  [dbo].[Providers].[Name] = [inserted].[Name])> 1

      BEGIN

            PRINT 'Такой провайдер уже есть'  ROLLBACK TRAN

      END

      IF @@ERROR != 0

      BEGIN

            PRINT 'Error occurred during related tables'  ROLLBACK TRAN

      END

      RETURN

END

Рисунок 7.1 Результаты работы триггера вставки таблице Providers 
 

     2. Тригер, проверяющий правильность формата e-mail

CREATE TRIGGER [dbo].[INSERTION_CHECK]

        ON  [dbo].[Affiliates]

        FOR INSERT, UPDATE

     AS

BEGIN

           SET NOCOUNT ON;

IF (SELECT Count(*) FROM [inserted] WHERE [Email] LIKE '%@%.%' AND NOT ([Email] IS NULL ) ) = 0

           BEGIN

                 PRINT 'Неверный формат e-mail' ROLLBACK TRAN

           END

           IF @@ERROR != 0

           BEGIN

PRINT 'Error occurred during related tables'  ROLLBACK TRAN

           END

           RETURN

END

Рисунок 7.2 Результаты работы триггера вставки таблице Affiliates 

7.2  Триггер для удаления данных

     Триггер для каскадного удаления данных из таблицы Affiliates при удалении записи из таблицы Providers

CREATE TRIGGER [dbo].[CASCADE_DEL]

   ON  [dbo].[Providers]

   FOR DELETE

AS

BEGIN

      SET NOCOUNT ON;

      DELETE Affiliates

                  FROM Affiliates a, deleted d  WHERE  a.Provider_id = d.id 

      IF @@ERROR != 0

      BEGIN

            PRINT 'Error occurred during related tables'  ROLLBACK TRAN

      END

      RETURN

END

Рисунок 7.3 Результаты работы триггера удаления таблице Providers 

     7.3  Триггер для обновления данных 

     Тригер запрещает изменение названия организации

CREATE TRIGGER [dbo].[UPDATE_CHECK]

  ON  [dbo].[Providers]

   FOR UPDATE AS

BEGIN

      SET NOCOUNT ON;

IF (NOT ( (SELECT [Name] FROM [inserted]) = (SELECT [Name] FROM [deleted]) ) )

      BEGIN

            PRINT 'Нельзя поменять  название'  ROLLBACK TRAN

      END

      IF @@ERROR != 0

      BEGIN

        PRINT 'Error occurred during related tables'  ROLLBACK TRAN

      END

      RETURN

END

Рисунок 7.1 Результаты работы триггера обновления в таблице Providers

 

8  Разработка технологий доступа к базе данных

8.1  Выбор пользователей базы данных

 

     В данном случае было выбрано 2 группы пользователей: администраторы и пользователи.

8.2  Выбор пользователей базы данных

 

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

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