Код мероприятияВремя началаВремя конца400015000116.0022.00400025000210.0018.00400035000305.0010.00400045000415.0020.00400055000516.0021.00400065000616.3018.00
2. Глава. Разработка базы данных
.1 Создание объектов базы данных
Далее представлен код создания базы данных:
--создание базы данных Мероприятия
create database activiti
-определяем используемую БДactiviti
Рисунок 3 Созданная БД
--создание таблицы Сотрудник (см.Рисунок 4)
create table worker(worker_ID int primary key not null, fioworker varchar (50) not null, phoneworker bigint not null check (phoneworker like('_______') or phoneworker like('___________')))
Рисунок 4 Созданная таблица Сотрудник
-заполнение таблицы Сотрудник (см. Рисунок 5)into [worker]values(101,'Човгун Роман Олегович', 89139037850)into [worker]values(102,'Мозголина Анна Александровна',89134886781)into [worker]values(103,'Багдасарян София Эдуардовна',89520000000)into [worker]values(104,'Иванов Антон Александрович',89230000500)into [worker]values(105,'Силина Ксения Александровна',89230010500)into [worker]values(106,'Сазонов Андрей Юрьевич',89830000000)into [worker]values(107,'Клименков Эльдар Арнольдович',89520001111)into [worker]values(108,'Козлов Дмитрий Геннадьевич',89139139113)
Рисунок 5 Заполненная таблица Сотрудник
-создание таблицы Подразделение (см.Рисунок 6)
create table subdivision(subdivision_ID int primary key not null, subdivisionname varchar (30) not null)
Рисунок 6 Созданная таблица Подразделение
--заполнение таблицы Подразделение (см.Рисунок 7)
insert into [subdivision]values(1001,'Отдел продаж')into [subdivision]values(1002,'Отдел снабжения')into [subdivision]values(1003,'Отдел закупок')into [subdivision]values(1004,'Складское хозяйство')into [subdivision]values(1005,'Отдел бухгалтерии')into [subdivision]values(1006,'Отдел безопасности')
Рисунок 7 Заполненная таблица Подразделение
-создание таблицы Сотрудник-Подразделение (см.Рисунок 8)
create table workersubdivision(workersubdivision_ID int primary key not null, worker_ID int foreign key references worker(worker_ID) not null,subdivision_ID int foreign key references subdivision(subdivision_ID) not null)
Рисунок 8 Созданная таблица Сотрудник - Подразделение
-заполнение таблицы Сотрудник-Подразделение (см.Рисунок 9)
insert into [workersubdivision]values(11001,102,1001)into [workersubdivision]values(11002,101,1002)into [workersubdivision]values(11003, 103,1005)into [workersubdivision]values(11004,104,1002)into [workersubdivision]values(11005,105,1004)into [workersubdivision]values(11006,106,1003)into [workersubdivision]values(11007,107,1001)into [workersubdivision]values(11008,108,1002)
Рисунок 9 Заполненная таблица Сотрудник - Подразделение
-создание таблицы Дата проведения (см.Рисунок 10)
create table data(data_ID int primary key not null, den int not null check(den>0 and den<=31), mesiac int not null check(mesiac>0 and mesiac<=12), god int not null check(god>1900 and god<=2090))
Рисунок 10 Созданная таблица Дата проведения
-заполнение таблицы Дата проведения (см.Рисунок 11)
insert into [data] values (3001,01,01,2012)into [data] values (3002,15,10,2012)into [data] values (3003,01,10,2012)into [data] values (3004,12,10,2012)into [data] values (3005,01,11,2012)into [data] values (3006,04,12,2012)
Рисунок 11 Заполненная таблица Дата проведения
-создание таблицы Место проведения (см.Рисунок 12)
create table place(place_ID int primary key not null, city varchar(30) not null, street varchar (50) not null, number int not null)
Рисунок 12 Созданная таблица Место проведения
-заполненная таблица Место проведения (см.Рисунок 13)
insert into [place] values (4001,'Новосибирск','Троллейная',22)into [place] values (4002,'Новосибирск','Жуковского',107)into [place] values (4003,'Новосибирск','Каменская',56)into [place] values (4004,'Новосибирск','Ломоносова',56)into [place] values (4005,'Бердск','Ленина',1)into [place] values (4006,'Искитим','Безымянная',100)
Рисунок 13 Заполненная таблица Место проведения--создание таблицы Участник (см.Рисунок 14)
table member(member_ID int primary key not null, fiomember varchar(50) not null, phonemember bigint not null check (phonemember like('_______') or phonemember like('___________')), position varchar(40) not null)
Рисунок 14 Созданная таблица Участник
-заполнение таблицы Участник (см.Рисунок 15)into [member] values (6001,'Подголин Дмитрий Александрович',89137500135,'Менеджер по продажам')into [member] values (6002,'Комаров Алексей Валерьевич',89130000000,'Менеджер по закупкам')into [member] values (6003,'Румянкин Александр Александрович',89134000000,'Менеджер по снабжению')into [member] values (6004,'Комарова Анастасия Юрьевна',89130001111,'Заведующий складом')into [member] values (6005,'Поляков Сергей Леонидович',89239990011,'Главный бухгалтер')
insert into [member] values (6006,'Ананьев Юрий Михайлович',89990009900,'Охранник')
Рисунок 15 Заполненная таблица Участник --создание таблицы Цель (см.Рисунок 16)
create table purpose(purpose_ID int primary key not null, namepurpose varchar(80) not null)
Рисунок 16 Созданная таблица Цель
-заполнение таблицы Цель (см.Рисунок 17)into [purpose] values (20001,'Исследование новых рынков сбыта продукции')into [purpose] values (20002,'Оптимизация деятельности отдела закупок')into [purpose] values (20003,'Исследование более выгодных условий доставки продукции')into [purpose] values (20004,'Нахождение новых способов ведения складского хозяйства')into [purpose] values (20005,'Нахождение новых способов ведения бухгалтерского хозяйства')into [purpose] values (20006,'Оптимизация охранной деятельности')
Рисунок 17 Заполненная таблица Цель
-создание таблицы Участник-Цель (см.Рисунок 18)
create table purposemember(purposemember_ID int primary key not null, member_ID int foreign key references member(member_ID) not null,purpose_ID int foreign key references purpose(purpose_ID) not null)
Рисунок 18 Созданная таблица Участник-Цель
-заполнение таблицы Участник-Цель (см.Рисунок 19)
insert into [purposemember] values (20500,6001,20001)into [purposemember] values (20501,6002,20002)into [purposemember] values (20502,6003,20003)into [purposemember] values (20503,6004,20004)into [purposemember] values (20504,6005,20005)into [purposemember] values (20505,6006,20006)
Рисунок 19 Заполненная таблица Участник-Цель
-создание таблицы Мероприятие (см.Рисунок 20)
create table meropriyatie(meropriyatie_ID int primary key not null, member_ID int foreign key references member(member_ID) not null,purpose_ID int foreign key references purpose(purpose_ID) not null, tip varchar(25) not null, stoimost money not null)
Рисунок 20 Созданная таблица Мероприятие
-заполнение таблицы Мероприятие (см.Рисунок 21)
insert into [meropriyatie] values (50001,6001,20001,'Конференция',100000)into [meropriyatie] values (50002,6002,20002,'Семинар',200000)into [meropriyatie] values (50003,6003,20003,'Конференция',150000)into [meropriyatie] values (50004,6004,20004,'Конференция',300000)into [meropriyatie] values (50005,6005,20005,'Конференция', 100000)into [meropriyatie] values (50006,6006,20006,'Семинар',50000)
Рисунок 21 Заполненная таблица Мероприятие
-создание таблицы Дата мероприятия - Мероприятие (см.Рисунок 22)
create table datameropriyatie(datameropriyatie_ID int primary key not null, data_ID int foreign key references data(data_ID) not null, meropriyatie_ID int foreign key references meropriyatie(meropriyatie_ID) not null)
Рисунок 22 Созданная таблица Дата мероприятия - Мероприятие
-заполнение таблицы Дата мероприятия - Мероприятие (см.Рисунок 23) insert into [datameropriyatie] values (2001,3001,50001)
insert into [datameropriyatie] values (2002,3002,50002)into [datameropriyatie] values (2003,3003,50003)into [datameropriyatie] values (2004,3004,50004)into [datameropriyatie] values (2005,3005,50005)into [datameropriyatie] values (2006,3006,50006)
Рисунок 23 Заполненная таблица Дата проведения - Мероприятие
-создание таблицы Место проведения - Мероприятие (см.Рисунок 24)
create table placemeropriyatie(placemeropriyatie_ID int primary key not null, place_ID int foreign key references place(place_ID) not null, meropriyatie_ID int foreign key references meropriyatie(meropriyatie_ID) not null)
Рисунок 24 Созданная таблица Место проведения - Мероприятие
-заполнение таблицы Место проведения - Мероприятие (см.Рисунок 25)
insert into [placemeropriyatie] values (5001,4001,50001)into [placemeropriyatie] values (5002,4002,50002)into [placemeropriyatie] values (5003,4003,50003)into [placemeropriyatie] values (5004,4004,50004)into [placemeropriyatie] values (5005,4005,50005)into [placemeropriyatie] values (5006,4006,50006)
Рисунок 25 Заполненная таблица Место проведения - Мероприятие
-создание таблицы Сотрудник-Мероприятие (см.Рисунок 26)
Рисунок 26 Созданная таблица Сотрудник - Мероприятие
-заполнение таблицы СОтрудник-Мероприятие (см.Рисунок 27)
insert into [workermeropriyatie] values (501,101,50001)into [workermeropriyatie] values (502,102,50002)into [workermeropriyatie] values (503,103,50003)into [workermeropriyatie] values (504,104,50004)into [workermeropriyatie] values (505,105,50005)into [workermeropriyatie] values (506,106,50006)
Рисунок 27 Заполненная таблица Сотрудник-Мероприятие
--создание таблицы Время проведения (см.Рисунок 28)
create table timeofmerop(timeofmerop_ID int primary key not null, meropriyatie_ID int foreign key references meropriyatie(meropriyatie_ID) not null, nachalo time not null, okonchanie time not null)
Рисунок 28 Созданная таблица Время проведения
-заполнение таблицы Время проведения (см.Рисунок 29)
insert into [timeofmerop] values (40001,50001,'16:00','22:00')into [timeofmerop] values (40002,50002,'10:00','18:00')into [timeofmerop] values (40003,50003,'05:00','10:00')into [timeofmerop] values (40004,50004,'15:00','20:00')into [timeofmerop] values (40005,50005,'16:00','21:00')into [timeofmerop] values (40006,50006,'16:30','18:00')
Рисунок 29 Заполненная таблица Время проведения
Правила
Правила - это объекты MS SQL Server, которые применяются для отдельных столбцов, для того, чтобы контролировать ввод данных в этот столбец.
-1.Создание правила на ограничение количество цифр в телефоне
1) check (phoneworker like('_______') or phoneworker like('___________'))
) check (phonemember like('_______') or phonemember like('___________'))
--2.Создание правила на ограничение ввода данных в типе оплаты.
1) check(den>0 and den<=31)
) check(mesiac>0 and mesiac<=12)
) check(god>1900 and god<=2090)
.2 Схема базы данных
На рисунке ниже представлена схема базы данных из Microsoft SQL (см. Рисунок 30).
Рисунок 30 Схема в Microsoft SQL
На данной схеме видны отношения и связи между таблицами и ключевые поля.
2.3 Разработка представлений
--1. Создание представления информация о должности участников (см. Рисунок 31)
create view posit asfiomember, positiondbo.member
Рисунок 31 Представление Должность участников
-2. Создание представления информация Какой сотрудник в каком отделе работает (см. Рисунок 32)
create view otdel asfioworker, subdivisionnamedbo.subdivision, dbo.workersubdivision, dbo.workerdbo.workersubdivision.worker_ID=dbo.worker.worker_ID and.workersubdivision.subdivision_ID=dbo.subdivision.subdivision_ID
Рисунок 32 Представление Какой сотрудник в каком отделе работает
-3. Создание представления информация о мероприятии (см. Рисунок 33)
create view meropr asfiomember, namepurpose, tip, stoimostdbo.meropriyatie, dbo.member, dbo.purposedbo.meropriyatie.member_ID=dbo.member.member_ID and.meropriyatie.purpose_ID=dbo.purpose.purpose_ID
go
Рисунок 33 Представление Информация о мероприятии
Представления очень удобно использовать, чтобы не видеть коды участников, целей, отделов и должностей, а видеть только ту информацию по запросу, которая понятна и легко воспринимается.
3. Разработка запросов
.1 Разработка простых запросов
-1.Запрос Показать все места проведения мероприятия в городе Новосибирск (см.Рисунок 34)
select *dbo.placecity like ('Новосибирск')
Рисунок 34
-2.Запрос Показать всех сотрудников, у кого сотовый оператор МТС (913, 983) (см.Рисунок 35)
select *dbo.workerphoneworker like ('8913%') or phoneworker like ('8983%')
Рисунок 35
-3.Запрос Показать всех участников мероприятия в должности менеджер (см.Рисунок 36)
select *dbo.memberposition like ('Менеджер%')
Рисунок 36
-4.Запрос Показать всех сотрудников у кого вторая буква в фамилии О (см.Рисунок 37)
select *dbo.workerfioworker like ('_о%')
Рисунок 37
-5 Запрос Показать адреса домов на улице Троллейная в Новосибирске (см.Рисунок 38)
select *dbo.placestreet like ('Троллейная') and city like ('Новосибирск')
Рисунок 38
--6 Запрос Показать ФИО заведующего складом (см.Рисунок 39)
select fiomemberdbo.memberposition like ('Заведующий складом')
Рисунок 39
-7 Запрос Показать код мероприятия, тип которого Конференция и Стоимость более 140000 (см.Рисунок 40)
select meropriyatie_ID, tip, stoimostdbo.meropriyatietip like ('Конференция') and stoimost > 140000
Рисунок 40
-8 Запрос Показать код мероприятия, начавшегося ранее 15:00 (см.Рисунок 41)
select meropriyatie_ID, nachalodbo.timeofmeropnachalo < '15:00:00'
Рисунок 41
--9 Запрос Наименования целей, связанных с бухгалтерией (см.Рисунок 42)
select namepurposedbo.purposenamepurpose like ('%бухгалтер%')
Рисунок 42
-10 Запрос Показать сотрудников, чьи отчества Александровна (см.Рисунок 43)
select fioworker, phoneworkerdbo.workerfioworker like ('%Александровна')
Рисунок 43
.2 Разработка многотабличных запросов
-1 Запрос Показать всех сотрудников отдела продаж (см.Рисунок 44)
select fioworker,subdivisionnamedbo.subdivision,dbo.worker,dbo.workersubdivision where dbo.workersubdivision.worker_ID=dbo.worker.worker_ID and dbo.workersubdivision.subdivision_ID=dbo.subdivision.subdivision_ID and subdivisionname like ('Отдел продаж')
-2 Запрос Показать коды мероприятий проводимых в октябре (см.Рисунок 45)
select dbo.meropriyatie.meropriyatie_ID, mesiacdbo.meropriyatie, dbo.data, dbo.datameropriyatiedbo.datameropriyatie.data_ID=dbo.data.data_ID and.datameropriyatie.meropriyatie_ID=dbo.meropriyatie.meropriyatie_ID and mesiac=10
Рисунок 45
-3 Запрос Выдать ФИО сотрудника проводящего мероприятие под кодом 50004 (см.Рисунок 46)
select fioworker, dbo.meropriyatie.meropriyatie_IDdbo.meropriyatie, dbo.worker, dbo.workermeropriyatiedbo.workermeropriyatie.worker_ID=dbo.worker.worker_ID and.workermeropriyatie.meropriyatie_ID=dbo.meropriyatie.meropriyatie_ID and dbo.meropriyatie.meropriyatie_ID=50004
Рисунок 46
--4. Запрос Выдать цель мероприятий стоимостью более 140000 (см.Рисунок 47)
select namepurpose, stoimostdbo.meropriyatie, dbo.purposedbo.meropriyatie.purpose_ID=dbo.purpose.purpose_ID and stoimost > 140000
Рисунок 47
-5. Запрос Выдать список участников и их цели мероприятий (см.Рисунок 48)
select fiomember, namepurposedbo.member, dbo.purpose, dbo.purposememberdbo.purposemember.member_ID=dbo.member.member_ID and.purposemember.purpose_ID=dbo.purpose.purpose_ID
Рисунок 48
-6. Запрос Выдать семинары, которые начинаются позже 16:00 (см.Рисунок 49)
select tip, nachalodbo.timeofmerop, dbo.meropriyatiedbo.meropriyatie.meropriyatie_ID=dbo.timeofmerop.meropriyatie_ID and nachalo > '15:30' and tip like ('Семинар')
Рисунок 49
-7. Запрос Выдать ФИО участников семинаров (см.Рисунок 50)
select fiomember, tipdbo.meropriyatie,dbo.memberdbo.meropriyatie.member_ID=dbo.member.member_ID and tip like ('Семинар')
Рисунок 50
-8. Запрос Выдать мероприятие, которое проводила Багдасарян (см.Рисунок 51)
select fioworker, dbo.meropriyatie.meropriyatie_IDdbo.meropriyatie, dbo.worker, dbo.workermeropriyatiedbo.workermeropriyatie.worker_ID=dbo.worker.worker_ID and.workermeropriyatie.meropriyatie_ID=dbo.meropriyatie.meropriyatie_ID and fioworker like ('Багдасарян%')
Рисунок 51
-9 Запрос Выдать время всех конференций (см.Рисунок 52)
select tip, nachalo, okonchaniedbo.meropriyatie, dbo.timeofmeropdbo.meropriyatie.meropriyatie_ID=dbo.timeofmerop.meropriyatie_ID and tip like ('Конференция')
Рисунок 54
-10 Запрос Какие мероприятия проводились в городе Новосибирск (см.Рисунок 53)
select dbo.meropriyatie.meropriyatie_ID, citydbo.meropriyatie, dbo.place, dbo.placemeropriyatiedbo.place.place_ID=dbo.placemeropriyatie.place_ID and.placemeropriyatie.meropriyatie_ID=dbo.meropriyatie.meropriyatie_ID and
city like ('Новосибирск')
Рисунок 53
.3 Разработка запросов со встроенными функциями
-1.запрос с функцией Какова стоимость самого мероприятия? (см.Рисунок 54)MAX(stoimost) dbo.meropriyatie
Рисунок 54
-2. запрос с функцией Какова стоимость самого дешевого мероприятия? (см.Рисунок 55)MIN(stoimost) dbo.meropriyatie
Рисунок 55
-3. запрос с функцией Каково количество сотрудников на предприятии? (см. Рисунок 56)COUNT(DISTINCT worker_ID) dbo.worker
Рисунок 56
-4.запрос с функцией Какова средняя стоимость мероприятий? (см. Рисунок 57)AVG(stoimost) dbo.meropriyatie
Рисунок 57
-5. запрос с функцией Какова общая стоимость проведенных мероприятий? (см. Рисунок 58)SUM (stoimost) dbo.meropriyatie
Рисунок 58
Запросы со встроенными функциями так же очень удобны для работы с базой данных, они позволяют быстро подсчитывать необходимую информацию.
Заключение
После выполнения данного курсового проекта мы достигли целей поставленных вначале. Создали базу данных по ведению информации о проведенных организацией мероприятиях. Теперь мы можем быстро вносить данные в базу, хранить ее, обрабатывать и искать необходимую нам информацию с помощью запросов. Также решили такие задачи как: хранение информации о сотрудниках и участниках мероприятий, хранение информации о местах и датах проведения мероприятий. Также хранение информации о стоимости того или иного мероприятия.
В данном курсовом проекте целью являлось разработать такую базу данных, которая необходима для ведения и хранения информации о мероприятии ООО «С нами выгодно».
Теперь база данных обеспечивает оперативную выдачу фактической информации, которую пользователь запрашивает в информационном запросе. Также выполнили ещё одну цель данного курсового проекта - закрепили навыки и знания в проектировании баз данных.
Список литературы
1.Дунаев В.В. Базы данных язык SQL - СПб.: БХВ Петербург, 2014. -288с.
2.Дейт, К. Дж. Введение в системы баз данных : [пер. с англ.] / К. Дж. Дейт .- 7-е изд .- М. : Вильямс, 2011 .- 1071 с .
3.Электронная документация по SQL Server