VIII Международная студенческая научная конференция Студенческий научный форум - 2016

ВВЕДЕНИЕ

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

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

Система управления базами данных (СУБД) – это программа, с помощью которой реализуется централизованное управление БД, осуществляется доступ к данным в них, поддерживая их в актуальном виде.

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

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

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

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

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

1 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

При отправлении на отдых за границу или в пределах своей страны приходиться выбирать гостиницу и резервировать подходящие по «комплектации» и стоимости номера.

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

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

База данных должна в полной мере осуществлять все задуманные процессы: регистрация приходящих клиентов, запись даты их заезда и выезда; прибавление в штате сотрудников; поиск нужных номеров для простых пользователей (клиентов). А также быть полезна и предоставлять полную информационную систему для групп пользователей: директора гостиницы, менеджера и потенциального клиента.

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

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

2. Информация, характеризующая поступающих клиентов – фамилия, имя, отчество, дата рождения, данные паспорта, адрес клиента, телефон;

3. Информация, описывающая сотрудников в штате: фамилия, имя, отчество сотрудника, дата рождения, пол, телефон, данные паспорта и занимаемая должность;

4. Информация об обслуживании в гостинице: информация о клиенте, заезд, выезд из гостиницы, предоставленные услуги;

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

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

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

 

2 РАЗРАБОТКА ИНФОЛОГИЧЕСКОЙ МОДЕЛИ

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

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

2.1 Построение ER-диаграммы

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

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

1. Номер (id_nomer (id_номера), naim (наименование), rasshifr (расшифровка), stoimost (стоимость), id_sotr (id_сотрудника, который обслуживает данный номер));

2. Клиенты (id_kl (id_клиента), fam_kl (фамилия клиента), naim_kl (имя клиента), otch_kl (отчество клиента), passport (паспортные данные), adr (адрес проживания), tel (телефонный номер));

3. Услуги (id_ysl (id_услуги), naim_ysl (наименование услуги), opis (описание той или иной услуги), stoimost_ysl (стоимость услуги)).

Проанализировав представленную предметную область, можно выделить из сущности Номер следующую сущность-обозначение: Сотрудники (id_sotr (id_сотрудника), fam_sotr (фамилия сотрудника), naim_namm (имя сотрудника), otch_namm (отчество сотрудника), date_roj (дата рождения), pol (пол), telef (номер телефона), passport (паспортные данные), id_doljn (id_должности)).

Изобразим это в виде диаграммы «Сущность-Связь» (ER-диаграммы).

Рисунок 1 – Связь между сущностями «Номер» и «Сотрудники»

К сущности Сотрудники относится сущность-обозначение Должности, которая включает в себя следующие поля: id_doljn (id_должности), naim_dolj (наименование должности), oklad (оклад).

Изобразим это в виде диаграммы «Сущность-Связь» (ER-диаграммы), представленной на рисунке 2.

Между сущностью Обслуживание, сущностью Услуги и сущностью Номера есть смысловая связь: «Услуги и номера продаются клиентам». Поэтому выделяем ассоциацию «Обслуживание» с атрибутами «Услуги» и «Номер», представленную на рисунке 3.

Рисунок 2 – Связь между сущностями «Должности» и «Сотрудники»

Рисунок 3 – Ассоциация между «Обслуживание» с атрибутами «Услуги» и «Номер»

Получаем окончательный вариант ER-диаграммы, представленный на рисунке 4.

Рисунок 4 – Окончательный вариант ER-диаграммы

3 РАЗРАБОТКА ЛОГИЧЕСКОЙ МОДЕЛИ

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

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

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

1. Первая нормальная форма (1НФ) очень проста. Все таблицы базы данных должны удовлетворять единственному требованию – каждая ячейка в таблицах должна содержать атомарное значение, другими словами, хранимое значение в рамках предметной области приложения БД не должно иметь внутренней структуры, элементы которой могут потребоваться приложению.

2. Вторая нормальная форма (2НФ) создается тогда, когда удалены все частичные зависимости из отношений БД. Если в отношениях не имеется никаких составных ключей, то этот уровень нормализации легко достигается.

3. Третья нормальная форма (3НФ) БД требует удаления всех транзитивных зависимостей. В отличие от первой нормальной формы, так же как и вторая нормальная форма, третья – подразумевает задание вместе с отношением системы функциональных зависимостей.

4. Четвертая нормальная форма (4НФ) создается при удалении всех многозначных зависимостей.

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

Для разработки БД выбрана СУБД Microsoft SQL Server.

Таблица 1 – Атрибуты и отношения в составе таблиц БД «gostinitsa»

Имя поля	Тип поля	Ключи		Ограничения
		Первичный	Внешний
1	2	3	4	5
Таблица «Должности» - dbo.doljnosti
id_dolj	int	+		not NULL
name_doljn	сhar(20)	-		not NULL
oklad	inr	-		NULL
Таблица «Клиенты» - dbo.kienti
id_kl	int	+		not NULL
fam_kl	сhar(20)	-		not NULL
naim_kl	сhar(20)	-		not NULL
otch_kl	сhar(20)	-		not NULL
pasport	сhar(10)	-		not NULL
adr	сhar(100)	-		not NULL
tel	сhar(20)	-		NULL
Таблица «Номера» - dbo.nomer
id_nomer	int	+		not NULL
naim	сhar(20)	-		not NULL
rashifr	сhar(90)	-		not NULL
stoimost	int	-		NULL
id_sotr	int		+	not NULL
Таблица «Обслуживание» - dbo.obsluz
id_obsl	int	+		not NULL
id_kl	int		+	not NULL
zaezd	date	-		NULL
viezd	date	-		NULL
id_nomer	int		+	not NULL
id_ysl1	int		+	NULL

Продолжение таблицы 1

1	2	3	4	5
id_ysl2	int		+	NULL
id_ysl3	int		+	NULL
Таблица «Сотрудники» - dbo.sotrudn
id_sotr	int	+		not NULL
fam_sotr	сhar(30)	-		not NULL
name_namm	сhar(30)	-		not NULL
otch_namm	сhar(30)	-		not NULL
date_roj	date	-		NULL
pol	char(1)	-		NULL
telef	сhar(15)	-		NULL
pasport	сhar(10)	-		NULL
id_doljn	int		+	not NULL
Таблица «Услуги» - dbo.yslugi
id_ysl	int	+		not NULL
naim_ysl	сhar(50)	-		not NULL
opis	сhar(100)	-		NULL
stoimost_ysl	int	-		NULL

Разработанная модель базы данных находится в третьей нормальной форме. Дальнейшая нормализация не требуется.

 

4 РАЗРАБОТКА ФИЗИЧЕСКОЙ МОДЕЛИ

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

4.1 Создание базы данных

База данных создается при помощи следующего запроса:

create database gostinitsa

4.2 Создание таблиц

Создание таблицы №1 – сотрудники.

create table sotrudn (

id_sotr int primary key identity (1,1),

fam_sotr char (30) not null,

name_namm char (30) not null,

otch_namm char (30) not null,

date_roj date,

pol char (1),

telef char (15),

pasport char(10),

id_doljn int not null

Создание таблицы №2 – должности.

create table doljnosti (

id_doljn int primary key identity (1,1),

name_dolj char (20) not null,

oklad int,

Создание таблицы №3 – номера.

create table nomer (

id_nomer int primary key identity (1,1),

naim char (20) not null,

vmest char (10) not null,

stoimost int,

id_sotr int not null

Создание таблицы №4 – услуги.

create table yslugi (

id_ysl int primary key identity (1,1),

naim_ysl char (20) not null,

opis char (10),

stoimost_ysl int

Создание таблицы №5 – клиенты.

create table kienti (

id_kl int primary key identity (1,1),

fam_kl char (20) not null,

naim_kl char (20) not null,

otch_kl char (20) not null,

pasport char (10) not null,

adr char (100) not null,

tel int)

Создание таблицы №6 – обслуживание.

create table obsluz (

id_obsl int primary key identity (1,1),

id_kl int not null,

zaezd date,

viezd date,

id_nomer int not null,

id_ysl1 int,

id_ysl2 int,

id_ysl3 int,

id_sotr int not null

4.3 Создание внешних ключей

alter table nomer add constraint a1 foreign key (id_sotr) references sotrudn (id_sotr)

alter table obsluz add constraint b1 foreign key (id_sotr) references sotrudn (id_sotr)

alter table obsluz add constraint c1 foreign key (id_kl) references kienti (id_kl)

alter table obsluz add constraint d1 foreign key (id_nomer) references nomer (id_nomer)

alter table obsluz add constraint e1 foreign key (id_ysl1) references yslugi (id_ysl)

alter table obsluz add constraint f1 foreign key (id_ysl2) references yslugi (id_ysl)

alter table obsluz add constraint g1 foreign key (id_ysl3) references yslugi (id_ysl)

alter table sotrudn add constraint h1 foreign key (id_doljn) references doljnosti (id_doljn)

4.4 Заполнение таблиц

Заполнение таблицы «Должности».

insert into doljnosti values ('Портье', '6000')

insert into doljnosti values ('Директор', '30000')

insert into doljnosti values ('Агент по брон.', '6000')

insert into doljnosti values ('Телеф. оператор', '6000')

insert into doljnosti values ('Горничная', '6000')

insert into doljnosti values ('Уборщик служ. пом.', '6000')

insert into doljnosti values ('Менеджер обсл. мер.', '10000')

insert into doljnosti values ('Оператор', '7000')

insert into doljnosti values ('Программист', '6000')

insert into doljnosti values ('Администратор', '25000')

Рисунок 5 – Табличное представление таблицы «Должности»

Заполнение таблицы «Сотрудники».

insert into sotrudn values ('Достоевский', 'Сергей', 'Михайлович', '25.12.1956', 'М', '89564731234', '456387956', '1')

insert into sotrudn values ('Боков', 'Михаил', 'Андреевич', '18.06.1986', 'М', '89563735629', '978446583', '2')

insert into sotrudn values ('Новиков', 'Андрей', 'Максимович', '5.12.1987', 'М', '87541764563', '675825463', '3')

insert into sotrudn values ('Аргентинова', 'Лариса', 'Сергеевна', '20.10.1987', 'Ж', '88795678123', '345612345', '4')

insert into sotrudn values ('Корнилова', 'Наталья', 'Алексеевна', '15.04.1988', 'Ж', '86578375627', '676637282', '5')

insert into sotrudn values ('Каленов', 'Игорь', 'Игоревич', '14.03.1988', 'М', '86578465678', '456745678', '6')

insert into sotrudn values ('Григорян', 'Анастасия', 'Олеговна', '21.04.1978', 'Ж', '86572341234', '876945673', '9')

insert into sotrudn values ('Негорова', 'Оксана', 'Николаевна', '6.10.1977', 'Ж', '89785674567', '567845632', '10')

insert into sotrudn values ('Михеев', 'Иван', 'Васильевич', '21.04.1991', 'М', '85678965367', '879512345', '11')

insert into sotrudn values ('Казахов', 'Захар', 'Игнатович', '13.07.1988', 'М', '84968462468', '974512347', '12')

Рисунок 6 – Табличное представление таблицы «Сотрудники»

Рисунок 7 – Табличное представление таблицы «Клиенты»

Рисунок 8 – Табличное представление таблицы «Номера»

Рисунок 9 – Табличное представление таблицы «Обслуживание»

Рисунок 10 – Табличное представление таблицы «Услуги»

4.5 Создание представлений

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

Представление #1 – общий доступ для клиентов.

create view asc1 as select naim as наименование, rashifr as полное_название, stoimost as стоимость from nomer

select * from asc1

Рисунок 11 – Результат выполнения представления «asc1»

Представление #2 – общий доступ для администратора.

create view asc2 as select fam_sotr as фамилия, name_namm as имя, otch_namm as отчество, telef as телефон, naim as наименование_номера, rashifr as полное_название from sotrudn inner join nomer on (sotrudn.id_sotr=nomer.id_sotr)

select * from asc2

Рисунок 12 – Результат выполнения представления «asc2»

4.6 Создание процедур

Хранимые процедуры – предварительно скомпилированная группа команд SQL. Процедуры имеют имена и обрабатываются как единый рабочий блок. Они принимают входные параметры и возвращают значения в вызывающую процедуру или команду.

#1 – поиск по заданному значению.

create procedure pr @a char(30) as

select * from nomer where naim=@a

execute pr 'CH'

Рисунок 13 – Результат выполнения процедуры «pr»

#2 – если оклад меньше 6500 - увеличиваем его в 1,1.

create procedure sto @b int, @c char(30) as

declare @z int

set @z= (select oklad from doljnosti where name_dolj=@c)

if @z @okladd * 1.1

begin

print 'введено недопустимое значение'

rollback

end

4.9 Создание запросов

1) Выбираем указанные поля (для всех столбцов указывается определенное новое имя)

select fam_kl as фамилия_клиента, naim_kl as имя_клиента, otch_kl as оклад, tel as телефон from kienti

Рисунок 17 – Результат выполнения запроса №1

2) При помощи «or» выводим нужную нам информацию из нескольких полей таблицы «Должности»

select * from doljnosti where oklad=6000 or oklad=7000

Рисунок 18 – Результат выполнения запроса №2

3) Задаем маску поиска с помощью оператора like (осуществляется поиск фамилии клиента по первой букве (в данном случае по «н»)

select * from kienti where fam_kl like('н%')

Рисунок 19 – Результат выполнения запроса №3

4) Выводим записи из разных таблиц («номера» и «сотрудники») по связям (ключам)

select naim, stoimost, fam_sotr, telef from nomer inner join sotrudn on (nomer.id_sotr=sotrudn.id_sotr)

Рисунок 20 – Результат выполнения запроса №4

5) Сортировка по возрастанию указанного поля (в таблице «сотрудники» по полю «фамилия клиента»)

select fam_sotr, id_sotr as id from sotrudn order by fam_sotr asc

Рисунок 21 – Результат выполнения запроса №5

6) Вывод минимального оклада

select min(oklad) as минимальный_оклад, max(oklad) as максимальный_оклад from doljnosti

Рисунок 22 – Результат выполнения запроса №6

7) Общая стоимость, если заказать все предоставленные гостиничным комплексом услуги

select SUM(stoimost_ysl) as общая_сумма_всех_услуг from yslugi

Рисунок 23 – Результат выполнения запроса №7

8) Выбираем те номера, которые обслуживает сотрудник с полем «id» равным трем

select naim as название_номера, rashifr as описание, stoimost as стоимость from nomer where id_sotr='3'

Рисунок 21 – Результат выполнения запроса №8

9) Группировка сотрудников по количеству номеров

select fam_sotr as фамилия_сотрудника, count(naim) as наименование_номера from sotrudn inner join nomer on (sotrudn.id_sotr=nomer.id_sotr) group by fam_sotr

Рисунок 22 – Результат выполнения запроса №9

10) Вывод суммы номеров

select fam_sotr, naim from sotrudn inner join nomer on (sotrudn.id_sotr=nomer.id_sotr) compute count(fam_sotr)

Рисунок 23 – Результат выполнения запроса №10

11) Производится работа с датой (вывод тех клиентов, которые заехали после 2014-10-23

select fam_kl, naim_kl, otch_kl, zaezd from obsluz inner join kienti on obsluz.id_kl=kienti.id_kl where zaezd>'2014-10-23'

Рисунок 24 – Результат выполнения запроса №11

12) Высчитываем то число, сколько дней клиент проживает в гостинице с момента заезда

select DATEDIFF (DD, zaezd, viezd) as время_прибывания, fam_kl, naim_kl, otch_kl, tel from obsluz inner join kienti on (obsluz.id_kl=kienti.id_kl)

Рисунок 25 – Результат выполнения запроса №12

4.10 Создание транзакции

Транзакция – это одна или несколько последовательных команд языка Transact – SQL, образующих логически завершенный пакет и выполняемых как единое целое. Если по какой-либо причине хотя бы одна из команд пакета не выполняется, то происходит откат системы к состоянию, в котором она была до начала транзакции, и транзакция считается не выполненной.

Добавление записи в таблицу «Сотрудники» и обновление в таблице «Номер».

begin tran

insert into sotrudn values ('Шилов', 'Андрей', 'Николаевич', '1987-12-04', 'М', '89766574567', '879675489', 3)

update nomer set id_sotr=(select id_sotr from sotrudn where fam_sotr='Шилов') where id_nomer=15

if (@@ERROR = 0) begin

commit tran

end

else begin

print 'ошибка'

rollback tran

end

4.11 Генерация ролей

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

#2 – Создание ролей

create role klient

create role adminn

create role dir

#3 – Создание разрешения на изменение таблицы «Сотрудники» директором

grant select, update on sotrudn to dir;

#4 – Запрет изменение данных для клиентов таблицы «Должности»

deny update, insert, alter, delete on doljnosti to klient

Рисунок 26 – Созданные роли «adminn», «dir», «klient»

 

ЗАКЛЮЧЕНИЕ

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

В процессе разработки курсового проекта была разработана реляционная база данных по предметной области «Гостиница».

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

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

 

СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ

1. Вендров, А.М. CASE-технологии. Современные методы и средства проектирования информационных систем. / А.М. Вендров. – М.: Финансы и статистика, 1998. -176 с.: ил.

2. Дейт, К. Введение в системы баз данных. / К. Дейт М., –СПб., Киев: Издательский дом «Вильямс». 2000. –950 с.: ил.

3. Мамаев, Е. Microsoft SQL Server 2000. / Е. Мамаев –СПб.:БХВ-Петербург, 2002. –1200 с.

4. Тиори, Т. Проектирование структур баз данных, т. 1. / Т. Тиори, Дж. Фрай –М.: Мир, 1985.

5. Тихомиров, Ю. Microsoft SQL Server 2000. Разработка приложений. / Ю. Тихомиров –СПб.: БХВ-Петербург, 2000.

6. Харрингтон, Д.Л. Проектирование реляционных баз данных. / Д.Л. Харрингтон –М.: Лори. 2000.

7. Хомоненко, А.Д. Базы данных: учебник для высших учебных заведений. / А.Д. Хомоненко, М.Г.Мальцев, В.М. Цыганков –СПб: КОРОНА принт, 2000.

8. Корнеев В.В. Базы данных. Интеллектуальная обработка информации / В.В. Корнеев – М.:Нолидж, 2000. – 352 с.

9. Ханcен Г., Ханcен Д. Базы данных. Разработка и управление / Г. Хансен, Д. Хансен – М.: Бином, 2012. – 704 с.

Код для цитирования: Скопировать