В таблице 2.7 хранятся данные о блокировках.

Таблица 2.7 TBL_Blok

В таблице 2.8 хранятся данные о вероятности блокировки после оплаты.

Таблица 2.8 TBL_Blok_posle_opl

В таблице 2.9 хранится совокупность данных всех таблиц и данных о АТС сервере.

Таблица 2.9 TBL_ATS

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

3.1.2 Представление Владелец

SELECT Firma AS [Фирма владелец], Adress_F AS [Адрес фирмы], Nomer_licenzii AS [Номер лицензии]

FROM dbo.TBL_Vlad

Рисунок 3.1 - Представление Владелец.

3.1.2 Представление Абонент

SELECT dbo.TBL_Abonent.Nomer_Tel AS [Номер телефона], dbo.TBL_Abonent.Fam AS Фамилия, dbo.TBL_Abonent.Name AS Имя, dbo.TBL_Abonent.Otch AS Отчество,

dbo.TBL_Abonent.Adress AS Адрес, dbo.TBL_Kod_rna.Nomer_rna AS [Номер района], dbo.TBL_Kod_rna.Nazvanie AS [Название района]

FROM dbo.TBL_Abonent INNER JOIN

dbo.TBL_Kod_rna ON dbo.TBL_Abonent.ID_Kod_rna = dbo.TBL_Kod_rna.ID_Kod_rna

Рисунок 3.2 - Представление Абонент.

3.1.3 Представление Тариф

SELECT dbo.TBL_Tarif.Nazvanie_Tarifa AS [Название тарифа], dbo.TBL_Tarif.Stoim_podkl AS [Стоимость подключения], dbo.TBL_Tarifikac.Tip AS [Тип тарификации]

FROM dbo.TBL_Tarif INNER JOIN

dbo.TBL_Tarifikac ON dbo.TBL_Tarif.ID_Tarifikac = dbo.TBL_Tarifikac.ID_Tarifikacdbo.tblpostoyankl ON dbo.tblbronstolov.id_postoyankl = dbo.tblpostoyankl.id_postoyankldbo.tblpostoyankl.id_postoyankl

Рисунок 3.3- Представление Тариф.

3.1.3 Представление Счет

SELECT dbo.TBL_Schet.Balans AS Баланс, dbo.TBL_Blok.Blok AS Блокировка, dbo.TBL_Schet.Dat_spis AS [Дата списания],

dbo.TBL_Schet.Kol_spisan AS [Сумма списания], dbo.TBL_Blok_posle_opl.Blok_posle_opl AS [Блокировка после списания],

dbo.TBL_Schet.Data_podkl AS [Дата подключения]

FROM dbo.TBL_Schet INNER JOIN

dbo.TBL_Blok ON dbo.TBL_Schet.ID_Blok = dbo.TBL_Blok.ID_Blok INNER JOIN

dbo.TBL_Blok_posle_opl ON dbo.TBL_Schet.ID_Blok_posle_opl = dbo.TBL_Blok_posle_opl.ID_Blok_posle_opl

Рисунок 3.4- Представление Счет.

3.2 SQL - ЗАПРОСЫ

Запрос 3.2.1 - Создание таблицы tblset

(tip nchar (30))

Запрос 3.2.2- Создание таблицы tblnagruzka

(nagruzka nchar (30))

Запрос 3.2.3- Удаление таблицы tblnagruzka

Drop table tblnagruzka

Запрос 3.2.4- Добавление полей в таблицу tblnagruzka

1) ALTER TABLE tblnagruzka ADD den CHAR(20)

Запрос 3.2.5- Удаление поля kto из таблицы tblnagruzka

1) ALTER TABLE tblnagruzka drop column den

Запрос 3.2.6 -Выборка с условием between

Вывести список с названием тарифов, где стоимость подключения от 100 до 300.

SELECT Nazvanie_Tarifa, Stoim_podkl

FROM TBL_Tarif

WHERE Stoim_podkl Between 100 And 300

Рисунок 3.7 - Выборка с условием between.

Запрос 3.2.7 -Выборка с условием Not between

Вывести список тарифов, где стоимость подключения не более 100, не менее 300.

SELECT Nazvanie_Tarifa, Stoim_podkl

FROM TBL_Tarif

WHERE Stoim_podkl Not Between 100 And 300

Рисунок 3.8 - Выборка с условием Not between.

Запрос 3.2.8 -Выборка с условием in

Вывести список районов, чей код 1 и 5.

SELECT Nazvanie, Nomer_rna

FROM TBL_Kod_rna

WHERE Nomer_rna in ('1', '5')

Рисунок 3.9 - Выборка с условием in.

Запрос 3.2.9 -Выборка с условием not in

Вывести список районов, чей код не 1 и 5.

SELECT Nazvanie, Nomer_rna

FROM TBL_Kod_rna

WHERE Nomer_rna Not in ('1', '5')

Рисунок 3.10 - Выборка с условием not in.

Запрос 3.2.10 - Выборка с условием like по символьному шаблону «..%».

Вывести список телефонов, у которых вторая цифра 1.

SELECT Fam, Nomer_Tel

FROM TBL_Abonent

WHERE Nomer_Tel Like '_1%'

Рисунок 3.11 - Выборка с условием like по символьному шаблону «..%».

Запрос 3.2.11 - Выборка с условием like по символьному шаблону «_», «..%», «[,]». Найти номера, где вторая цифра- 8 или 5.

Рисунок 3.12- Выборка с условием like по символьному шаблону «_», «..%», «[,]».

Запрос 3.2.12 - Выборка с условием like по символьному шаблону «..%», «[-]».

Найти телефоны абонентов, у которых телефон не начинается на диапазон цифр -1-6.

SELECT Fam, Nomer_Tel

FROM TBL_Abonent

WHERE Nomer_Tel Not Like '[1-6]%'

Рисунок 3.13- Выборка с условием like по символьному шаблону «..%», «[-]».

Запрос 3.2.13 - Выборка с условием like по символьному шаблону «%..%».

Вывести список абонентов, женского пола (отчества, содержащие слог “на”). SELECT Fam, Name, Otch

FROM TBL_Abonent

WHERE Otch Like '%на%'

Рисунок 3.14 -Выборка с условием like по символьному шаблону «%..%».

Запрос 3.2.14 - Выборка с условием Is Null

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

SELECT dbo.TBL_Abonent.Fam, dbo.TBL_Schet.Balans, dbo.TBL_Schet.Dat_spis

FROM dbo.TBL_Schet INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Schet.ID_Schet = dbo.TBL_ATS.ID_Schet INNER JOIN

dbo.TBL_Abonent ON dbo.TBL_ATS.ID_Abonent = dbo.TBL_Abonent.ID_Abonent

WHERE Dat_spis Is Null

Рисунок 3.15- Выборка с условием Is Null.

Запрос 3.2.15 - Выборка с условием Is Not Null

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

SELECT dbo.TBL_Abonent.Fam, dbo.TBL_Schet.Balans, dbo.TBL_Schet.Dat_spis

FROM dbo.TBL_Abonent INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Abonent.ID_Abonent = dbo.TBL_ATS.ID_Abonent INNER JOIN

dbo.TBL_Schet ON dbo.TBL_ATS.ID_Schet = dbo.TBL_Schet.ID_Schet

WHERE Dat_spis Is Not Null

Рисунок 3.16 -Выборка с условием Is Not Null.

Запрос 3.2.16 - Выборка с сортировкой по возрастанию

Отсортировать фамилии абонентов в алфавитном порядке по возрастанию.

SELECT Fam

FROM TBL_Abonent

ORDER BY Fam

Рисунок 3.17 - Выборка с сортировкой по возрастанию.

Запрос 3.2.17 - Выборка с сортировкой по убыванию

Отсортировать фамилии и типы подключенных тарификации по убыванию.

SELECT dbo.TBL_Abonent.Fam, dbo.TBL_Tarifikac.Tip

FROM dbo.TBL_ATS INNER JOIN

dbo.TBL_Abonent ON dbo.TBL_ATS.ID_Abonent = dbo.TBL_Abonent.ID_Abonent INNER JOIN

dbo.TBL_Tarif ON dbo.TBL_ATS.ID_Tarif = dbo.TBL_Tarif.ID_Tarif INNER JOIN

dbo.TBL_Tarifikac ON dbo.TBL_Tarif.ID_Tarifikac = dbo.TBL_Tarifikac.ID_Tarifikac

ORDER BY Fam, Tip desc

Рисунок 3.18 - Выборка с сортировкой по убыванию.

Запрос 3.2.18 - Выборка с условием групповых операций

Вывести общую сумму списания со всех счетов на указанную дату.

SELECT SUM(Kol_spisan) as Списанно

FROM TBL_Schet

WHERE (Dat_spis > CONVERT(DATETIME, '2010-02-12 00:00:00', 102) AND Dat_spis < CONVERT(DATETIME, '2014-12-02 00:00:00', 102))

Рисунок 3.19- Выборка с вычислениями.

Запрос 3.2.19 -Выборка с условиями Left

Вывести список абонентов с именем и инициалами.

SELECT Fam +' '+

Left(Name,1)+'.'AS ФамилияИмя

FROM TBL_Abonent

Рисунок 3.20 - список абонентов с именем и инициалами.

Запрос 3.2.20 - Выборка с условиями вычисляемых полей

Вывести фамилию абонента, год и месяц даты подключения.

SELECT dbo.TBL_Abonent.Fam, dbo.TBL_Schet.Data_podkl AS Год, Month(Data_podkl) AS Месяц

FROM dbo.TBL_Abonent INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Abonent.ID_Abonent = dbo.TBL_ATS.ID_Abonent INNER JOIN

dbo.TBL_Schet ON dbo.TBL_ATS.ID_Schet = dbo.TBL_Schet.ID_Schet

Рисунок 3.21- Выборка с условиями вычисляемых полей.

Запрос 3.2.21 - Запросы на выборку с вычислениями с использованием итоговых функций Min

Определить первый по алфавиту тариф.

SELECT Min(Nazvanie_Tarifa) AS Min_Название

FROM TBL_Tarif

Рисунок 3.22 - первый по алфавиту тариф.

Запрос 3.2.22 - Запросы на выборку с вычислениями с использованием итоговых функций Count

Вывести количество пользователей каждого тарифа.

SELECT dbo.TBL_Tarif.Nazvanie_Tarifa, COUNT(dbo.TBL_Abonent.Nomer_Tel) as Количество

FROM dbo.TBL_Abonent INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Abonent.ID_Abonent = dbo.TBL_ATS.ID_Abonent INNER JOIN

dbo.TBL_Tarif ON dbo.TBL_ATS.ID_Tarif = dbo.TBL_Tarif.ID_Tarif

group by dbo.TBL_Tarif.Nazvanie_Tarifa

Рисунок 3.23 - количество пользователей каждого тарифа.

Запрос 3.2.23 - Запросы на выборку с вычислениями с использованием итоговых функций Avg.

Вывести среднюю стоимость подключения тарифного плана.

SELECT AVG(Stoim_podkl) as Средняя_Цена

From TBL_Tarif

Рисунок 3.24 - средняя стоимость подключения тарифного плана.

Запрос 3.2.24 - Выборка с условием итоговой функции «max»

Найти максимальную зарплату персонала.

SELECT max(Stoim_podkl) AS max_Стоимость_Подключения

FROM TBL_Tarif

Рисунок 3.25 - Выборка с условием итоговой функции «max».

Запрос 3.2.25 - Выборка с условием итоговой функции Sum

Вывести общую стоимость подключений.

SELECT Sum(Stoim_podkl)

AS Общая_Стоимость_Подключений

FROM TBL_Tarif

Рисунок 3.26 - Вывести общую стоимость подключений.

Запрос 3.2.26 - Запросы на выборку с групповыми операциями

Вывести количество абонентов проживающих в каждом районе.

SELECT dbo.TBL_Tarif.Nazvanie_Tarifa, COUNT( dbo.TBL_Abonent.ID_Abonent) as Количество_абонентов

FROM dbo.TBL_Abonent INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Abonent.ID_Abonent = dbo.TBL_ATS.ID_Abonent INNER JOIN

dbo.TBL_Tarif ON dbo.TBL_ATS.ID_Tarif = dbo.TBL_Tarif.ID_Tarif

group by dbo.TBL_Tarif.Nazvanie_Tarifa

Рисунок 3.27 - количество абонентов проживающих в каждом районе.

Запрос 3.2.27 - Запрос с условиями GROUP BY

Вывести общие суммы подключения тарифов.

SELECT dbo.TBL_Tarif.Nazvanie_Tarifa, Sum(dbo.TBL_Tarif.Stoim_podkl) as Сумма, Month(dbo.TBL_Schet.Data_podkl) as Месяц

FROM dbo.TBL_Tarif INNER JOIN

dbo.TBL_Tarifikac ON dbo.TBL_Tarif.ID_Tarifikac = dbo.TBL_Tarifikac.ID_Tarifikac INNER JOIN

dbo.TBL_ATS ON dbo.TBL_Tarif.ID_Tarif = dbo.TBL_ATS.ID_Tarif INNER JOIN

dbo.TBL_Schet ON dbo.TBL_ATS.ID_Schet = dbo.TBL_Schet.ID_Schet

GROUP BY dbo.TBL_Tarif.Nazvanie_Tarifa, Month(dbo.TBL_Schet.Data_podkl)

Рисунок 3.28 Запрос с условиями GROUP BY.

Запрос 3.2.28 - Запрос с условиями GROUP BY

Вывести общее количество заблокированных и не заблокированных абонентов.

SELECT dbo.TBL_Blok.Blok, Count(dbo.TBL_ATS.ID_Abonent)as Количество

FROM dbo.TBL_ATS INNER JOIN

dbo.TBL_Schet ON dbo.TBL_ATS.ID_Schet = dbo.TBL_Schet.ID_Schet INNER JOIN

dbo.TBL_Blok ON dbo.TBL_Schet.ID_Blok = dbo.TBL_Blok.ID_Blok

GROUP BY dbo.TBL_Blok.Blok

Рисунок 3.29 - Запрос с условиями GROUP BY.

Запрос 3.2.29 - Запросы на выборку с условиями в групповых операциях

Вывести количество абонентов, которые содержаться в базе АТС.

SELECT Kod_ATS, Count(ID_Abonent) as Количество

FROM dbo.TBL_ATS

GROUP BY Kod_ATS

Рисунок 3.30- количество абонентов, которые содержаться в базе АТС.

Запрос 3.2.30 - Запрос на выборку с условием HAVING

Вывести районы человека, в которых проживает более 3-х абонентов.

SELECT dbo.TBL_Kod_rna.Nazvanie, Count(dbo.TBL_Abonent.ID_Abonent) AS Количестов_абонентов

FROM dbo.TBL_Kod_rna INNER JOIN

dbo.TBL_Abonent ON dbo.TBL_Kod_rna.ID_Kod_rna = dbo.TBL_Abonent.ID_Kod_rna

GROUP BY dbo.TBL_Kod_rna.Nazvanie

HAVING Count(dbo.TBL_Abonent.ID_Abonent)>3

Рисунок 3.31- районы человека, в которых проживает более 3-х абонентов.

Запрос 3.2.31 - Запросы на выборку на основе запроса (с использованием подзапросов)

Найти максимальный баланс до даты списания.

SELECT Balans, Dat_spis

FROM dbo.TBL_Schet

WHERE Dat_spis=(SELECT Max(Dat_spis) FROM dbo.TBL_Schet)

Рисунок 3.32- Найти максимальный баланс до даты списания.

Запрос 3.2.32 - Выборка с использованием подзапросов

Вывести дату, когда баланс абонента превышает средний баланс.

SELECT Data_podkl, Balans,

Balans-(SELECT Avg(Balans)

FROM dbo.TBL_Schet) AS Превышение

FROM dbo.TBL_Schet

WHERE Balans>

(SELECT Avg(Balans)

FROM dbo.TBL_Schet)

Рисунок 3.33 - Выборка с использованием подзапросов.

Запрос 3.2.33 - Выборка с условием подзапроса

Вывести количество списания, превышающее текущий баланс.

SELECT Kol_spisan

FROM TBL_Schet

WHERE Balans=

(SELECT Max(Balans)

FROM TBL_Schet)

Рисунок 3.34 Выборка с условием подзапроса.

Запрос 3.2.34 - Добавление данных в таблицу TBL_Abonent

Добавляем имя “Владимир “в таблицу абонент.

INSERT INTO TBL_Abonent (Name)

VALUES('Владимир')

Запрос 3.2.35 Добавление данных в таблицу TBL_Tarif

Добавляем название “Тестовый “в таблицу тариф.

INSERT INTO TBL_Tarif(Nazvanie_Tarifa)

VALUES('Тестовый')

Запрос 3.2.36 - Удаление данных в таблице TBL_Schet

Запрос 3.2.37- Удаление данных в таблице TBL_Schet

FROM TBL_Schet

WHERE Month(Dat_spis)=Month(GETDATE())-1

Запрос 3.2.38 - Обновление данных в таблице TBL_Abonent

UPDATE TBL_Abonent SET Nomer_tel= 1453411

WHERE Fam='Чех'

Запрос 3.2.39 - Обновление данных в таблице TBL_Schet

UPDATE TBL_Schet SET Balans *3

WHERE ID_Blok ='2'

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

ЗАКЛЮЧЕНИЕ

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

· Спроектирована реляционная база данных «Служба АТС»;

· Разработана структура таблиц;

· Построена диаграмма ;

· Созданы представления ;

· Разработаны SQL - запросы на выборку ;

· Разработаны SQL - запросы на выборку с условиями;

· Разработаны SQL - запросы на выборку с вычислениями;

· Разработаны SQL - запросы на выборку с групповыми операциями и сортировкой;

· Разработаны SQL - запросы на создание таблицы;

· Разработаны SQL - запросы на обновление таблиц;

· Разработаны SQL - запросы на удаление таблиц и записей;

· Разработаны SQL - запросы на добавление полей и записей;

· Написать пояснительную записку.

При разработки курсовой работы была использована СУБД Microsoft SQL Server 2008 R2 и конструкции языка SQL.

ЛИТЕРАТУРА

1)Риккарди Г. Системы баз данных. Теория и практика использования в Internet и среде Java.- М.: Изд. дом «Вильямс», 2001.

2)Базы данных. Интеллектуальная обработка информации/ Корнеев В.В. и др.- М.: «Нолидж», 2000.

3)Дунаев С. Доступ к базам данных и техника работы в сети: Практические приемы программирования.- М.: Диалог-Мифи, 1999.

4)Диго С.М. Проектирование и использование баз данных: Учебник.- М.: Финансы и статистика, 1995

5)Базы данных: Учебник/ Ред. А.Д. Хомоненко.- СПб.: Корона принт, 2000.

6)Глушаков С.В., Ломотько Д.В. Базы данных: Учебный курс.- Харьков: «АСТ», 2000.

7)Бойко В.В., Савинков В.М. Проектирование баз данных информационных систем.- М.: Финансы и статистика, 1989.

Размещено на Allbest.ru