База данных интернет-провайдеров
Курсовая работа, 09 Сентября 2011, автор: пользователь скрыл имя
Описание работы
Приложение «Providers» было разработано по заказу ставропольского филиала организации «StavropololoProvider», которое предоставляет услуги связи. Данное приложение разработано для мониторинга конкуренции на рынке интернет-провайдеров. База данных занимает одно из главенствующих мест в системе учета и контроля данного предприятия и требует особо внимания к своей работоспособности, при ее разработке учитывались мнение, как начальников данной организации, так и простых операторов ПК, непосредственно работающих с базой данных.
Содержание работы
ВВЕДЕНИЕ 4
2 Описание предметной области 5
3 ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ 7
4 Организация выборки информации из базы данных 13
5 Разработка представлений для отображения результатов выборки 18
6 Проектирование хранимых процедур 20
7 Разработка механизмов управления данными в базе при помощи триггеров 22
7.1 Триггер для добавления данных 22
7.2 Триггер для удаления данных 24
8 Разработка технологий доступа к базе данных 27
8.1 Выбор пользователей базы данных 27
8.2 Выбор пользователей базы данных 27
9 Организация обмена данными между серверной частью и клиентским приложением 27
10 Организация обмена данными между серверной частью и клиентским приложением 28
11 Экономическое обоснование результатов внедрения программного продукта 28
12 Требования к техническому обеспечению 30
13 Инструкция по эксплуатации базы данных и клиентского приложения 31
ЗАКЛЮЧЕНИЕ 32
СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ 33
ПРИЛОЖЕНИЕ 1 Экранные формы приложения 34
ПРИЛОЖЕНИЕ 2 Листинг программы 37
Файлы: 1 файл
SQL-пояснительная.doc
— 1.30 Мб (Скачать файл)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.
dbo.Services ON dbo.AffiliateServices.Service_
WHERE (dbo.Providers.Name = 'RosTelecom')
- Выборка, показывающая какие провайдеры есть в каждом регионе, количество их филиалов и долю пользователей
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
- Выборка, показывающая интернетификацию регионов (отношение пользователей интернета к населению)
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 [Кол. Интернетов]
- Выборка безлимитных тарифов
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)
- Выборка тарифов по беспроводным технологиям
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 и вызывать хранимые процедуры, не вносящие изменений в БД. Администраторы имеют полный доступ ко всем элементам БД.
Клиентское
приложение в зависимости от роли
пользователя позволяет ему редактировать
и добавлять записи, просматривать
отчеты, выполнять запросы и