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 и вызывать хранимые процедуры, не вносящие изменений в БД. Администраторы имеют полный доступ ко всем элементам БД.

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