Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Содержание

Введение

1. Назначение разработанного приложения

2. Операторы модификации данных Insert, Update, Delete в среде MS SQL Server

2.1 Операторы модификации данных

2.2 Оператор INSERT

2.3 Оператор UPDATE

2.4 Оператор DELETE

3. Структура базы данных приложения

4. Схема диалога пользователя с разработанным приложением

4.1 Корректировка таблиц-справочников

4.2 Основная функциональность приложения по ведению базы данных

4.3 Построение печатных форм

5. Использованные средства MS SQL Server

5.1 Использование представлений

5.2 Использование хранимых процедур

Заключение

Список использованных источников

Введение

На сегодняшний день существует множество различных систем управления базами данных. Они все используют разные средства и функции, но преимущественно у всех СУБД в основе лежат одинаковые понятия. Поэтому для обобщения этих понятий, приемов и методов на весь класс СУБД, я хотел бы взять программу, входящую в пакет Microsoft Office, Microsoft Access.

Целью данной курсовой работы была разработка автоматизированной системы управления оператора мобильной связи.

Разработанное мною приложение хранит информацию о начислениях и снятий со счёта по каждому из клиентов; позволяет оформлять и расторгать договора с оператором мобильной связи; производится заполнение всех звонков совершённых тем или иным клиентом. Имеется возможность получать отчеты и просмотра всех таблиц базы данных.

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

Второй раздел содержит теоретические сведения об операторах модификации данных Insert, Update, Delete в среде MS SQL Server.

Третий раздел содержит структуру базы данных приложения с подробным описанием разработанных таблиц.

Четвертый раздел описывает схему диалога пользователя с разработанным приложением.

В пятом разделе описываются используемые в приложении средства MS SQL Server.

1. Назначение разработанного приложения

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

Приложение даёт возможность быстро получить необходимую информацию, а также даёт возможность составить отчёты по предприятию. Кроме этого ? в приложении возможно посмотреть, а так же внести изменения, информацию о всех договорах, клиентах, сотрудниках, тарифных планах и предоставляемых дополнительных услуг оператором.

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

Схему общения пользователя с программным приложением можно увидеть на рисунке 1.

Клиент

Запрос

Результат запроса

Обработка запроса

Сервер

Рисунок 1 - Схема работы приложения

2. Операторы модификации данных Insert, Update, Delete в среде MS SQL Server

2.1 Операторы модификации данных

Язык манипуляции данными (DML -- Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:

- INSERT - Добавление записей (строк) в таблицу БД

- UPDATE - Обновление данных в столбце таблицы БД

- DELETE - Удаление из таблицы БД

2.2 Оператор INSERT

Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.

Синтаксис оператора следующий:

INSERT INTO <имя_таблицы>[(<имя_столбца>, …)]

{VALUES (<значение_столбца>, …)}

| <выражение_запроса>

| (DEFAULT VALUES)

Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Должности, созданную следующим оператором CREATE TABLE:

CREATE TABLE Должности (

название varchar(50) NOT NULL,

категория varchar(50) NOT NULL)

Пусть требуется добавить в эту таблицу должность «Старший менеджер» и категория Первая. Это можно сделать следующим оператором:

INSETR INTO Должности VALUES ('Старший менеджер', 'Первая');

Если задать список столбцов, то можно изменить «естественный» порядок их следования:

INSERT INTO Должности (название, категория) VALUES ('Старший менеджер', 'Первая');

Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:

CREATE TABLE Должности (

название varchar(50) NULL,

категория varchar(50) NOT NULL DEFAULT `Вторая')

Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первое -- NULL, а второй столбец -- категория -- Вторая). Теперь мы могли бы написать:

INSERT INTO Должности (название) VALUES('Старший менеджер');

В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию -- Вторя. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL.

Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT:

INSERT INTO Должности VALUES ('Старший менеджер', DEFAULT);

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

INSERT INTO Должности VALUES (DEFAULT, DEFAULT);

Однако для этого случая предназначена специальная конструкция DEFAULT VALUES, с помощью которой вышеприведенный оператор можно переписать в виде

INSERT ITNO Должности DEFAULT VALUES;

Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Должности_А все строки из таблицы Должности, относящиеся к категории (категория = `Вторая'). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:

INSERT INTO Должности_А

SELECT * FROM Должности

WHERE категория = `Вторая';

Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT, либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов.

Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN!). Другими словами, если бы столбец категория в таблице Должности допускал бы NULL-значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Должности_А.

Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL.

Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.

2.3 Оператор UPDATE

Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис:

UPDATE <имя таблицы>

SET {<имя столбца> = {<выражение для вычисления значения столбца>

| NULL

| DEFAULT},...}

[ {WHERE <предикат>}]

С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.

Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.

Ссылка на «выражение для вычисления значения столбца» может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все цены тарифных планов на 10 процентов с помощью следующего оператора:

UPDATE ТП

SET стоимость = стоимость*0.9

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

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

UPDATE ТП

SET стоимость = (SELECT MIN(стоимость) FROM ТП)

2.4 Оператор DELETE

Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:

DELETE FROM <имя таблицы >

[WHERE <предикат>];

Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым).

3. Структура базы данных приложения

Созданная база данных «Оператор мобильной связи» состоит из 10 таблиц. Названия таблиц и их назначение описаны в таблице 3.1.

Таблица 3.1 - Описание таблиц базы данных «Оператор мобильной связи»

Название таблицы	Краткое описание
1 Договор	Список договоров
2 Должности	Справочник должностей
3 Дополнительные услуги	Список дополнительных услуг
4 Звонки	Список звонков клиента
5 Клиент	Справочник клиентов
6 Описание услуг	Справочник дополнительных услуг
7 Оплата	Список оплат
8 Сотрудники	Список сотрудников
9 Списывание	Список списываний с договора
10 Тарифный план	Справочник тарифных планов

В таблицах 3.2 - 3.11 описана структура таблиц базы данных.

Таблица 3.2 - Структура таблицы Договор

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Договора
ном_тел	Текстовый	Номер телефона
дата_рег	Дата/время	Дата регистрации
код_ТП	Числовой	Код тарифного плана
код_клиента	Числовой	Код клиента
код_сотрудника	Числовой	Код сотрудника

Таблица 3.3 - Структура таблицы Должности

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Должности
название	Текстовый	Название должности
категория	Текстовый	Категория должности

Таблица 3.4 - Структура таблицы Дополнительные услуги

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Дополнительной услуги
код_догов	Числовой	Код договора
код_опис	Числовой	Код описания услуги
стоим	Денежный	Стоимость доп. услуги
дата_подкл	Дата/время	Дата подключения
дата_откл	Дата/время	Дата отключения

Таблица 3.5 - Структура таблицы Звонки

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Звонка
код_догов	Числовой	Код договора
ном_тел_куда	Текстовый	Номер телефона на который звонили
время_разг	Дата/время	Время разговора
дата_звон	Дата/время	Дата звонка

Таблица 3.6 - Структура таблицы Клиент

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Клиента
ФИО	Текстовый	ФИО клиента
ном_пасп	Текстовый	Номер паспорта клиента
кем_выдан	Текстовый	Какой орган выдал паспорт клиенту

Таблица 3.7 - Структура таблицы Описание услуг

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Описания услуг
название	Текстовый	Название услуги

Таблица 3.8 - Структура таблицы Оплата

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Оплаты
код_догов	Числовой	Код договора
сумма	Денежный	Сумма оплаты
дата_опл	Дата/время	Дата оплаты

Таблица 3.9 - Структура таблицы Сотрудники

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Сотрудника
ФИО	Текстовый	ФИО сотрудника
код_должн	Числовой	Код должности

Таблица 3.10 - Структура таблицы Списывание

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Списывание
код_догов	Числовой	Код договора
дата_спис	Дата/время	Дата списывания
причина	Текстовый	Причина списывания
сумма	Денежный	Сумма списывания

Таблица 3.11 - Структура таблицы Тарифный план

Наименование поля	Формат поля	Содержимое поля
код	Числовой	Уникальный код Тарифного плана
название	Текстовый	Название тарифного плана
стоимость	Денежный	Стоимость тарифного плана

SQL-код, создающий таблицы и описание ограничений:

Alter table Сотрудники

drop k1

go

Alter table Доп_услуги

drop k2, k3

go

Alter table Звонки

drop k4

go

Alter table Списывание

drop k5

go

Alter table Оплата

drop k6

go

Alter table Договор

drop k7, k8, k9

go

DROP TABLE Договор

GO

DROP TABLE Должности

GO

DROP TABLE Доп_услуги

GO

DROP TABLE Звонки

GO

DROP TABLE Клиент

GO

DROP TABLE Опис_услуг

GO

DROP TABLE Оплата

GO

DROP TABLE Сотрудники

GO

DROP TABLE Списывание

GO

DROP TABLE ТП

GO

CREATE TABLE Договор (

код int primary key identity,

ном_тел varchar(14) NOT NULL,

код_ТП int NOT NULL,

код_клиента int NOT NULL,

код_сотрудника int NOT NULL

)

GO

CREATE TABLE Должности (

код int primary key identity,

название varchar(50) NOT NULL,

категория varchar(50) NOT NULL

)

GO

CREATE TABLE Доп_услуги (

код int primary key identity,

код_догов int NOT NULL,

код_опис int NOT NULL,

стоим money NOT NULL,

дата_подкл datetime NOT NULL,

дата_откл datetime NOT NULL

)

GO

CREATE TABLE Звонки (

код int primary key identity,

код_догов int NOT NULL,

ном_тел_куда varchar(14) NOT NULL,

время_разг time NOT NULL,

дата_звон datetime NOT NULL

)

GO

CREATE TABLE Клиент(

код int primary key identity,

ФИО varchar(50) NOT NULL,

ном_пасп varchar(12) NOT NULL,

кем_выдан varchar(50) NOT NULL

)

GO

CREATE TABLE Опис_услуг(

код int primary key identity,

название varchar(50) NOT NULL

)

GO

CREATE TABLE Оплата(

код int primary key identity,

код_догов int NOT NULL,

сумма money NOT NULL,

дата_опл datetime NOT NULL

) GO

CREATE TABLE Сотрудники(

код int primary key identity,

ФИО varchar(50) NOT NULL,

код_должн int NOT NULL

)GO

CREATE TABLE Списывание(

код int primary key identity,

код_догов int NOT NULL,

дата_спис datetime NOT NULL,

причина varchar(50) NOT NULL,

сумма money NOT NULL

)

GO

CREATE TABLE ТП(

код int primary key identity,

название varchar(50) NOT NULL,

стоимость money NOT NULL

)

GO

ALTER TABLE Сотрудники

ADD

CONSTRAINT k1 FOREIGN KEY (код_должн) references Должности(код)

GO

ALTER TABLE Доп_услуги

ADD

CONSTRAINT k2 FOREIGN KEY (код_опис) references Опис_услуг(код),

CONSTRAINT k3 FOREIGN KEY (код_догов) references Договор(код)

GO

ALTER TABLE Звонки

ADD

CONSTRAINT k4 FOREIGN KEY (код_догов) references Договор(код)

GO

ALTER TABLE Списывание

ADD

CONSTRAINT k5 FOREIGN KEY (код_догов) references Договор(код)

GO

ALTER TABLE Оплата

ADD

CONSTRAINT k6 FOREIGN KEY (код_догов) references Договор(код)

GO ALTER TABLE Договор

ADD

CONSTRAINT k7 FOREIGN KEY (код_ТП) references ТП(код),

CONSTRAINT k8 FOREIGN KEY (код_клиента) references Клиент(код),

CONSTRAINT k9 FOREIGN KEY (код_сотрудника) references Сотрудники(код)

Связи между таблицами базы данных отображены на рисунке 3.1

Рисунок 3.1 - Схема базы данных «Оператор мобильной связи»

4. Схема диалога пользователя с разработанным приложением

Приложение разработано средствами Microsoft Access 2010 и MS SQL Server 2008. При запуске появляется главное окно Access, изображенное на рисунке 4.1.

Рисунок 4.1 - Главное окно Access

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

4.1 Корректировка таблиц-справочников

Спроектированная база данных содержит четыре таблицы-справочника.

В данной курсовой работе рассмотрены следующие таблицы-справочники:

1. Должность

2. Клиент

3. Описание услуг

4. Тарифные планы

На рисунке 4.2 показано меню справочников в СУБД Acess.

Рисунок 4.2 - Меню справочников в СУБД Access

Все эти таблицы можно просмотреть, добавить, удалить или изменить данные для этого следует просто открыть эту таблицу из меню. На рисунке 4.3 показано редактирование таблицы «Должности».

Рисунок 4.3 - Изменение записи в таблице Должности

4.2 Основная функциональность приложения по ведению базы данных

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

На рисунке 4.4 показано меню с дочерними таблицами в базе данных. Это основные таблицы, т.к. в них хранится прочти вся информация, и эти таблицы используют информацию из таблиц справочников по средствам установленных связей через идентификационные ключи. Таблицы хранят и структуру базы данных.

Рисунок 4.4 - Меню таблиц в СУБД Access

В данной курсовой работе рассмотрены следующие таблицы:

1. Договор

2. Дополнительные услуги

3. Звонки

4. Оплата

5. Сотрудники

6. Списывание

Данные этих таблиц так же можно редактировать рисунок 4.5.



Рисунок 4.5 - Изменение записи в таблице Договор

На рисунке 4.5 показано изменение записи

4.3 Построение печатных форм

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

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

На рисунке 4.6 показано меню отчетов в СУБД Access.



Рисунок 4.6 - Меню отчетов в СУБД Access

На рисунке 4.7 показан вывод информации об оплате с группировкой результата по месяцам. Это значительно упрощает работу, т.к. удобно вести статистику о поступлении средств.

Рисунок 4.7 - Отчет об оплате

На рисунке 4.8 показан вывод информации о клиенте по указанному номеру в процедуре. Это поможет в поиске какой-либо информации о клиенте, если известен только его номер.



Рисунок 4.8 - Отчет о клиенте по номеру телефона

На рисунке 4.9 показан отчет о списываниях за год, который вводит пользователь. В этом отчете выводится на печать номер телефона, с которого были списания и группировка по номерам телефонов, причина и сумма списывания.

Рисунок 4.9 - Отчет о списываниях

Для того, чтобы вывести этот отчет, надо ввести год в качестве параметра. Для этого, после того как пользователь пытается его открыть, появляется диалоговое окно, показанное на рисунке 4.10

Рисунок 4.10 - Ввод параметра для отчета

база оператор мобильный связь

5. Использованные средства MS SQL Server

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

5.1 Использование представлений

Для формы Дополнительные услуги, Звонки, Списывание, Оплата, где вместо кода договора, отображается в выпадающем списке информация о фамилии клиента и его номере телефона, используется представление Номер и ФИО клиента (рисунок 5.1), в котором связаны таблицы Договор и Клиент.

Рисунок 5.1 - Представление Информация о гастролях

Также для формы Договор используется представление О сотрудниках (рисунок 5.2), где вместо кода сотрудника отображается в выпадающем списке фамилия сотрудника и его должность. В представлении связаны таблицы Сотрудники и Должности.

Рисунок 5.2 - Представление О сатрудниках

Ниже приведен текст SQL, создающий представления:

drop view Номер_и_ФИО

go

create view Номер_и_ФИО

as

SELECT Договор.код, ном_тел, ФИО

FROM Договор, Клиент

WHERE Клиент.код=код_клиента

go

drop view О_сатрудниках

go

create view О_сатрудниках

as

SELECT Сотрудники.код, ФИО, название

FROM Сотрудники, Должности

WHERE Сотрудники.код_должн=Должности.код

5.2 Использование хранимых процедур

Отчёт Информация о клиенте по номеру телефона сделан на результате выполнения хранимой процедуры, которая в качестве параметра принимает номер телефона и по этому телефону находит всю информацию о клиенте, за которым закреплён этот номер (рисунок 5.3).

Рисунок 5.1 - Представление Информация о гастролях

Ниже приведен текст SQL, создающий процедуры:

drop procedure [Информация о договоре по номеру клиента]

go

create procedure [Информация о договоре по номеру клиента] @номер_телефона varchar(14)

as

select

Клиент.ФИО, Клиент.ном_пасп as [Номер паспорта], ном_тел as Номер, ТП.название as [Тарифный палан], ТП.стоимость as [Стоимость тарифа]

from

Договор, Клиент, ТП

where

ном_тел = @номер_телефона and код_клиента = Клиент.код and код_ТП = ТП.код

Заключение

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

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

Был освещен теоретический вопрос «Операторы модификации данных Insert, Update, Delete в среде MS SQL Server», разработана база данных в среде MS SQL Server, оформлен текст курсовой работы.

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

Список использованных источников

1 Винкоп, С. Использование Microsoft SQL Server 7.0: специальное издание / С. Винкоп. - СПб.: Издательский дом «Вильямс», 2001. - 816 с.

2 Хоторн, Р. Разработка баз данных Microsoft SQL Server 2000 на примерах / Р. Хоторн. - М.: Бином, 2001. - 464 с.

3 Змитрович, А.И. Базы данных: учебное пособие для вузов / А.И. Змитрович. - Мн.: Университетское, 1991. - 271 с.

4 Риордан, Р. Программирование в Microsoft SQL Server 2000. Шаг за шагом / Р. Риордан. - М.: Эком, 2002. - 608 с.

5 Кренке, Д. Теория и практика построения баз данных / Д. Кренке. - 8-е изд. - СПб.: Питер, 2003. - 800 с.

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