База данных фирмы, предоставляющей телекоммуникационные услуги

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

Оглавление

Введение

1. Постановка задачи и разработка бизнес-правил

2. Построение логической модели

3. Построение физической модели данных

4. Код схемы базы данных

5. Диаграмма базы данных в MS SQL Server

6. Описание информационных потребностей пользователя

7. Хранимые процедуры

8. Триггеры

Заключение

Список использованной литературы

Введение

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

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

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

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

практических навыков проектирования баз данных с помощью СУБД MS SQL SERVER. На мой взгляд, предоставление телекоммуникационных услуг - это перспективный и быстро развивающийся вид деятельности, требующий большого количества данных и как нельзя лучше подходящего для примера проектирования информационной базы данных.

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

1. Постановка задачи и разработка бизнес-правил

2. Построение логической и физической модели данных

3. Описание информационных потребностей пользователей

4. Создание хранимых процедур

5. Создание триггеров

1. Постановка задачи и разработка бизнес-правил

база данные триггер

Фирма "Телеком" занимается предоставлением телекоммуникационных услуг. Процесс предоставления услуг происходит следующим образом:

Клиент производит заказ на услугу, пользуясь предоставленным фирмой каталогом услуг.

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

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

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

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

Рис.1.1 Упрощенная схема бизнес-процессов

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

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

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

наиболее важные для данного бизнеса компоненты.

В нашем случае это будут:

сотрудники;

клиенты;

услуги;

оборудование;

заказы;

Далее следует составить список основных бизнес-процессов, происходящих в фирме. А это следующие:

анализ рынка;

разработка и распространение каталога;

предоставление услуг по аренде вычислительных мощностей;

оформление заказов и выписка счетов на оплату;

решение бухгалтерских задач;

Для того чтобы знать, кто выполняет эти процессы, необходимо понимать организационную структуру фирмы. Упрощенная организационная структура фирмы «Телеком» представлена на рис.1.2.

Рис.1.2. Организационная структура фирмы

Периодичность осуществляемых бизнес-процессов позволит правильно

расставить акценты в будущей прикладной программе. В данной курсовой

работе примем такую временную последовательность выполняемых процессов:

подведение итогов в конце каждого месяца;

годовой отчет (к 20 января);

Бизнес задачи фирмы определим так:

Предоставление клиентам качественных услуг;

Ведение информации услугах и клиентах;

Получение приемлемой прибыли;

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

взаимосвязей между бизнес-компонентами и бизнес-процессами, как это показано на рис. 1.3.

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

Рис. 1.3 Взаимосвязи между бизнес-компонентами и бизнес-процессами

Максимально формализованное описание задачи будет выглядеть следующим образом:

Наименование задачи:

Автоматизация управления работой обслуживающего персонала в фирме по предоставлению телекоммуникационных услуг «Телеком».

Цель работы обслуживающего персонала:

Предоставление телекомуникационных услуг на заказ по каталогу.

Функции обслуживающего персонала:

ведение каталога услуг;

ведение справочника клиентов;

прием заказов у клиентов предоставление услуг;

обслуживание поисковых запросов

ведение расчетов за предоставленные услуги;

Бизнес правила:

Невозможно оформить заказ, если клиент не оплатил счет за предыдущую аренду

Перечень вводимой информации.

Наименование организации;

Адрес клиента;

Телефон клиента;

Факс клиента;

Длительность аренды;

Наименование оборудования;

Процессор;

Тактовая частота процессора

Оперативная память;

Объем оперативно памяти;

Дисковый массив

Общий объем физической памяти

Сетевая карта

Видеокарта

Операционная система

Тип услуги

Перечень печатных отчетов: номенклатура предлагаемых услуг и оборудования; список клиентов; список заказов; счет.

2. Построение логической модели

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

Рис. 2.1. Логическая модель базы данных «Телекоммуникационные услуги»

Логическая модель строится без привязки к конкретной СУБД. Для её построения требуется:

Определить сущности исходя из предметной области. В нашем случае это: «Клиент», «Заказ», «Услуги», «Оборудование» и т.п

Определить свойства (атрибуты) сущностей. Каждой сущности присущи свои атрибуты. Например, сущность «Заказ» должна иметь такие атрибуты: ID Клиента, ID Услуги, ID Сотрудника, Дата заключения заказа, Длительность.

Определить связи между сущностями

3. Построение физической модели данных

Физическая модель описывает данные средствами конкретной СУБД. Отношения, разработанные на стадии формирования логической модели данных, преобразуются в таблицы, атрибуты становятся столбцами, а связи остаются связями. Для каждого атрибута определяется тип данных (строковый, целочисленный и т.п). Физическая модель базы данных «Телекоммуникационные услуги» представлена на рисунке 3.1

Рис. 3.1. Физическая модель базы данных «Телекоммуникационные услуги

4. Код схемы базы данных

CREATE TABLE Заказ

(

Номер_Заказа char(3) NOT NULL ,

Дата_заключения datetime NULL ,

Длительность datetime NULL ,

ID_Клиента char(3) NULL ,

ID_Услуги char(3) NULL ,

ID_Сотрудника char(3) NULL ,

PRIMARY KEY (Номер_Заказа ASC) ,

FOREIGN KEY (ID_Клиента)

REFERENCES Клиент(ID_Клиента),

FOREIGN KEY (ID_Услуги)

REFERENCES Услуги(ID_Услуги) ,

FOREIGN KEY (ID_Сотрудника)

REFERENCES Сотрудники(ID_Сотрудника)

)

go

CREATE TABLE Клиент

(

ID_Клиента char(3) NOT NULL ,

Наименование_организации char(30) NULL ,

Адрес char(50) NULL ,

Телефон char(12) NULL ,

Факс char(18) NULL ,

PRIMARY KEY (ID_Клиента ASC)

)

go

CREATE TABLE Оборудование

(

ID_Оборудования char(3) NOT NULL ,

Наименование char(50) NULL ,

Процессор char(50) NULL ,

Оперативная_память char(50) NULL ,

Дисковый_массив char(50) NULL ,

Сетевая_карта char(50) NULL ,

Видеокарта char(50) NULL ,

Операционная_система char(30) NULL ,

Тактовая_частота_процессора char(10) NULL ,

Общий_объем_физичской_памяти char(10) NULL ,

Объем_оперативной_памяти char(10) NULL ,

PRIMARY KEY (ID_Оборудования ASC)

)

go

CREATE TABLE Оплата

(

ID_Оплаты char(3) NOT NULL ,

Дата_оплаты datetime NULL ,

Сумма money NULL ,

Номер_Счета char(3) NULL ,

PRIMARY KEY (ID_Оплаты ASC) ,

FOREIGN KEY (Номер_Счета)

REFERENCES Счет(Номер_Счета)

)

go

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

(

ID_Сотрудника char(3) NOT NULL ,

Фамилия char(30) NULL ,

Имя char(30) NULL ,

Отчество char(30) NULL ,

Должность char(50) NULL ,

PRIMARY KEY (ID_Сотрудника ASC)

)

go

CREATE TABLE Счет

(

Номер_Счета char(3) NOT NULL ,

Дата_выписки datetime NULL ,

Сумма_к_оплате money NULL ,

Пометка_об_оплате bit NULL ,

Номер_Заказа char(3) NULL ,

PRIMARY KEY (ID_Сотрудника ASC) ,

FOREIGN KEY (Номер_Заказа)

REFERENCES Заказ(Номер_Заказа)

)

go

CREATE TABLE Услуги

(

ID_Услуги char(3) NOT NULL ,

Стоймость_за_месяц money NULL ,

Тип_услуги char(30) NULL ,

ID_Оборудования char(3) NULL ,

PRIMARY KEY (ID_Услуги ASC),

FOREIGN KEY (ID_Оборудования)

REFERENCES Оборудование(ID_Оборудования)

)

go

Создание таблицы осуществляется посредством оператора CREATE

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

5. Диаграмма базы данных в MS SQL Server

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

Рис. 5.1. Диаграмма базы данных «Телекоммуникационные услуги»

6. Описание информационных потребностей пользователя

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

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

Рассмотрим следующие выборки данных:

Список продавцов, которые оформили больше всего заказов за январь (рис.6.1.)

SELECT Сотрудники.Фамилия AS 'Фамилия' , Сотрудники.Имя AS 'Имя' ,

COUNT(Заказ.Номер_заказа) AS 'Количество заказов'

FROM Сотрудники

INNER JOIN Заказ

ON Сотрудники.ID_Сотрудника=Заказ.ID_Сотрудника

WHERE MONTH (Заказ.Дата_Заключения)=1

GROUP BY Сотрудники.Фамилия,Сотрудники.Имя

HAVING COUNT (Заказ.Номер_заказа)>1

ORDER BY COUNT (Заказ.Номер_заказа)DESC

Рис.6.1. Список продавцов, которые оформили больше всего заказов за январь

Список клиентов, которых можно назвать постоянными, на основании того сколько раз они пользовались услугами в этом году (рис.6.2.)

SELECT Клиент.Наименование_организации

AS 'Наименание организации',

COUNT(Заказ.Номер_заказа) AS 'Количество заказов'

FROM Клиент

INNER JOIN Заказ

ON Клиент.ID_Клиента=Заказ.Номер_заказа

WHERE YEAR(Заказ.Дата_заключения)=YEAR(GETDATE())

GROUP BY Клиент.Наименование_организации

HAVING COUNT(Заказ.Номер_заказа)>=2

ORDER BY COUNT(Заказ.Номер_заказа)ASC

Рис.6.2 Список постоянных клиентов

Подведение итогов по выручке за прошедший месяц (рис.6.3.)

SELECT SUM (Оплата.Сумма)AS 'Прибыль'

FROM Оплата

INNER JOIN Счет

ON Счет.Номер_счета=Оплата.ID_оплаты

WHERE MONTH(Оплата.Дата_оплаты)=1

Рис.6.3. Общая сумма по всем услугам

7. Хранимые процедуры

Хранимые процедуры являются способом, с помощью которого можно создавать подпрограммы, работающие на сервере и управляющие его процессами.

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

1. Процедура, которая по идентификационному номеру сотрудника выдает оформленные им заказы (рис.7.1.)

CREATE PROCEDURE aaa (@id char(3))

AS

SELECT Сотрудники.ID_Сотрудника, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность, Заказ.Номер_заказа, Заказ.Дата_заключения

FROM Сотрудники INNER JOIN Заказ

ON Сотрудники.ID_Сотрудника=Заказ.ID_Сотрудника

WHERE Сотрудники.ID_Сотрудника=@id

Рис.7.1 Список заказов, оформленных сотрудником с id `001'

8. Триггеры

Триггер - это специальный тип хранимой процедуры, которую SQL Server выполняет при операциях добавления, модификации и удаления в данной таблице. Наиболее общее применение триггера- поддержка деловых правил в базах данных.

Триггер, запрещающий оформлять заказы клиентов, которые не оплатили счет за предыдущий заказ (рис.8.1.)

CREATE TRIGGER allow_order

ON Заказ

FOR INSERT

AS

DECLARE @klient_id char(3)

DECLARE @unpaid_order_amount int

SELECT @klient_id = I.ID_Клиента

FROM Заказ Z,Inserted I

WHERE Z.Номер_Заказа=I.Номер_Заказа

SELECT @unpaid_order_amount = count(Заказ.Номер_заказа)

FROM Заказ

INNER JOIN Счет

ON Заказ.Номер_заказа=Счет.Номер_заказа

WHERE Счет.Пометка_об_оплате='False'

AND Заказ.ID_Клиента=@klient_id

IF @unpaid_order_amount > 0

BEGIN

ROLLBACK TRAN

RAISERROR ('Невозможно оформить заказ, т.к клиент не оплатил предыдущий', 16, 10)

END

Рис.8.1 Попытка оформить заказ клиенту, не оплатившему предыдущий счет

2.Триггер, запрещающий оформлять заказы в последнюю неделю месяца (рис.8.2)

CREATE TRIGGER trig1

ON Zakaz

FOR INSERT, UPDATE

AS

DECLARE @DayOfMonth TINYINT

SELECT @DayOfMonth = DatePart(Day,I.Дата_заключения)

FROM Заказ S, Inserted I

WHERE S.Номер_заказа = I.Номер_заказа

AND S.ID_Клиента= I.ID_Клиента

AND S.ID_Сотрудника = I.ID_Сотрудника

AND S.ID_Услуги = I.ID_Услуги

IF @DayOfMonth >24

BEGIN

ROLLBACK TRAN

RAISERROR ('Невозможно оформить заказ', 16, 10)

END

Рис.8.2. Попытка некорректного ввода данных

Заключение

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

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

Разработанная база данных хорошо справляется со своими задачами и

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

Список использованной литературы

Бураков П.В., Петров В.Ю. Введение в системы базы данных - Санкт-Петербург:, 2010. - 129с. - экз.

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