Разработка базы данных «Служба АТС»

Курсовая работа Разработка базы данных «Служба АТС»

1 ТЕХНИЧЕСКОЕ ЗАДАНИЕ

1.1 Постановка задачи

1.2 Требования к СУБД

2 ТЕХНИЧЕСКИЙ ПРОЕКТ

2.1 Инфологическая модель «сущность-связь»

2.2Структура таблиц

2.3Диаграмма

3 РАБОЧИЙ ПРОЕКТ

3.1 Представления

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

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

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

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

3.2 SQL — запросы ЗАКЛЮЧЕНИЕ ЛИТЕРАТУРА

Целью курсовой работы является разработка базы данных «Служба АТС» в среде Microsoft SQL Server Management Studio и создание запросов на языке SQL (Structured Query Language-язык структурированных запросов).

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

Задачами курсовой работы являются:

1) Освоение сервисной системы управления базами данных Microsoft SQL Server Management Studio;

2) Проектирование баз данных

3) Разработка инфологической модели «сущность — связь»;

4) Разработка структуры таблиц и диаграмм ;

5) Изучение языка SQL;

6) Выполнение запросов SQL;

7) Получение навыков разработки баз данных;

8) Разработка пояснительной записки.

1 ТЕХНИЧЕСКОЕ ЗАДАНИЕ

1.1 Постановка задачи

Необходимо разработать базу данных «Служба АТС» в среде Microsoft SQL Server Заполнить базу данными. Создать представления и SQL-запросы.

· 7 таблиц

· 3 главных таблиц

· 30 записей

· 3 представлений SQL-запросы:

· На создание таблиц — не менее 2;

· На удаление таблиц — не менее 1;

· На добавление полей в таблицу — не менее 1;

· На удаление полей из таблицы — не менее 1;

· На выборку с условиями (between, in, like, с шаблоном, c символами заменителями) — не менее 10;

· На выборку с сортировкой — не менее 2;

· На выборку с вычислениями (в новом столбце, выделение года, месяца из даты, слияние строковых данных в новом столбце) — не менее 3;

· На выборку с вычислениями с использованием итоговых функций (Min, Count, Sum, Avg, Max) — не менее 5;

· На выборку с групповыми операциями — не менее 3;

· На выборку с условиями в групповых операциях — не менее 2;

· На выборку на основе запроса (с использованием подзапросов) — не менее 3;

· На добавление данных — не менее 2;

· На удаление данных — не менее 2;

· На обновление данных — не менее 2;

1.2 Требования к СУБД

При разработке базы данных «Служба АТС» использовалась среда Microsoft SQL Server.

Microsoft SQL Server — система управления реляционными базами данных (СУРБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка.

Система SQL Server 2008 позволяет обращаться к данным из любого приложения, разработанного с применением технологий Microsoft .NET и Visual Studio, а также в пределах сервисно-ориентированной архитектуры и бизнес-процессов — через Microsoft BizTalk Server. Сотрудники, отвечающие за сбор и анализ информации, могут работать с данными, не покидая привычных приложений, которыми они пользуются каждый день, например приложений выпуска 2007 системы Microsoft Office. SQL Server 2008 позволяет создать надежную, производительную, интеллектуальную платформу, отвечающую всем требованиям по работе с данными.

2 ТЕХНИЧЕСКИЙ ПРОЕКТ

2.1 Инфологическая модель «сущность-связь»

Рисунок 2.1-Инфологическая модель «сущность-связь»

2.2 Структура таблиц

База данных рестораны имеет реляционную модель, поэтому имеет несколько таблиц связанных между собой. Структура таблиц представлена в таблицах 2.1 — 2.8.

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

Таблица 2.1 TBL_Vlad

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

Таблица 2.2 TBL_Abonent

В таблице 2.3 хранятся данные о номерах и названиях районов.

Таблица 2.3 TBL_Kod_rna

В таблице 2.4 хранятся данные о тарифных планах.

Таблица 2.4 TBL_Tarif

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

Таблица 2.5 TBL_Tarifikac

В таблице 2.6 хранятся данные о счетах.

Таблица 2.6 TBL_Schet

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

Таблица 2.7 TBL_Blok

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

Таблица 2.8 TBL_Blok_posle_opl

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

Таблица 2.9 TBL_ATS

2.3Диаграмма

Рисунок 2.1 — Диаграмма базы данных «Служба АТС».

3 РАБОЧИЙ ПРОЕКТ

3.1 Представления

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

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

Create Table tblset

(tip nchar (30))

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

Create table 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.

SELECT Fam, Nomer_Tel

FROM TBL_Abonent

WHERE Nomer_Tel 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

DELETE

FROM TBL_Schet

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

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

DELETE

FROM TBL_Schet

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

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

UPDATE TBL_Abonent SET Nomer_tel= 1 453 411

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.