Система управления базами данных

sostzasADD INDEX IX_Relationship5 (id_zs);ALTER TABLE zasedanie.sostzasADD INDEX IX_Relationship6 (tabNo);ALTER TABLE zasedanie.sostzasADD CONSTRAINT Relationship5 FOREIGN KEY (id_zs)REFERENCES zasedanie.zasedanie (id_zs);ALTER TABLE zasedanie.sostzasADD CONSTRAINT Relationship6 FOREIGN KEY (tabNo)REFERENCES zasedanie.person (tabNo);Рисунок . – Проектирование таблицыsostzasПостроим проект таблицыzasedanie (Рисунок 4.7).---- Создатьтаблицу `zasedanie`--CREATE TABLE zasedanie.zasedanie ( id_zs int(11) NOT NULL AUTO_INCREMENT, zsDate date DEFAULT NULL, zsPovestka text DEFAULT NULL, zsReshenie text DEFAULT NULL, id_km int(11) DEFAULT NULL, PRIMARY KEY (id_zs))ENGINE = INNODB,CHARACTER SET utf8mb4,COLLATE utf8mb4_unicode_ci;Рисунок . – Проектирование таблицыzasedanieПосле завершения создания таблиц установим между ними связи и отношения. Модель базы данных представлена на рисунке (Рисунок 4.8).Рисунок . – Модель базы данныхПостроение запросов к базе данныхСписок зарегистрированных существующих комиссийПоказать список комиссий, для каждой - ее состав и председателя.SELECT komissya.id_km ,komissya.kmName ,kSostav.tabNo ,person.fio ,dolgn.dlName ,kSostav.predsFROM dbo.kSostavINNER JOIN dbo.komissya ON kSostav.id_km = komissya.id_kmINNER JOIN dbo.person ON kSostav.tabNo = person.tabNoINNER JOIN dbo.dolgn ON person.id_dl = dolgn.id_dlWHERE kSostav.dIskl IS NULLРезультат выполнения запроса (Рисунок 5.1).Рисунок . – Запрос списка комиссийСписок председателей комиссииДля введенного пользователем интервала дат и названия комиссии показать в хронологическом порядке всех ее председателей. Для выделенного интервала используем промежуток с 01-10-2017 по 01-10-2018 в качестве комисси – Жилищную.SELECT s.tabNo ,s.preds ,p.fio ,k.kmName FROM kSostav s LEFT JOIN person p ON s.tabNo = p.tabNo LEFT JOIN komissya k ON k.id_km = s.id_km WHERE s.dVkluch BETWEEN '2017-10-01' AND '2018-10-01' AND s.preds=1 AND k.kmName = 'Жилищная';Результат выполнения запроса показан на рисунке (Рисунок 5.2).Рисунок . – Запрос о председателях комиссииУчастники комиссийПоказать список членов мэрии, для каждого из них - список комиссий, в которых он участвовал и/или был председателем.SELECT person.tabNo ,person.fio ,kSostav.dVkluch ,kSostav.pVkluch ,kSostav.dIskl ,kSostav.pIskl ,kSostav.preds ,komissya.kmNameFROM dbo.kSostavINNER JOIN dbo.person ON kSostav.tabNo = person.tabNoINNER JOIN dbo.komissya ON kSostav.id_km = komissya.id_kmРезультат выполнения запроса показан на рисунке (Рисунок 5.3).Рисунок . – Список членов мэрииХронология списка заседанийВывести список заседаний в указанный интервал в хронологическом порядке, для каждого заседания - список присутствующих. Для выделенного интервала используем промежуток с 01-10-2018 по 30-11-2018.SELECT zasedanie.id_km ,zasedanie.dZas ,komissya.kmName ,person.fio FROM dbo.zSostav INNER JOIN dbo.zasedanie ON zSostav.id_km = zasedanie.id_km AND zSostav.dZas = zasedanie.dZas INNER JOIN dbo.person ON zSostav.tabNo = person.tabNo INNER JOIN dbo.komissya ON zasedanie.id_km = komissya.id_km WHERE zasedanie.dZas BETWEEN '2018-10-01' AND '2018-11-30' ORDERBYdZasРезультат выполнения запроса показан на рисунке (Рисунок 5.4).Рисунок . – Cписок заседаний в указанный интервалКоличество заседаний по комиссиямПо каждой комиссии показать количество проведенных заседаний в указанный период времени.SELECT komissya.id_km ,komissya.kmName ,COUNT(zasedanie.dZas) AS expr1FROM dbo.zasedanieINNER JOIN dbo.komissya ON zasedanie.id_km = komissya.id_kmWHERE dZas BETWEEN '2018-10-01' AND '2018-11-30'GROUP BY komissya.id_km,komissya.kmNameРезультат выполнения запроса показан на рисунке (Рисунок 5.5).Рисунок . – Количествопроведенных заседаний по комиссиямГрафик заседания комиссииПостроим отображение графика заседаний для определенной комиссии в течение определенного периода. В качестве периода примем временной промежуток, ограниченный рамками календарного месяца. В качестве выходной информации выведем наименование комиссии, дату заседания, повестку и ФИО председателя.Для формирования данного графика потребуется выборка данных из 3х таблиц данных, учитывая и устанавливая связи между ними. Схема связей запроса показана на схеме (Рисунок 5.6).Рисунок . – Схема связей запросаПример выполнения запроса по построению графика заседания комиссий (Рисунок 5.7).Рисунок . – Выполнение запросаSQLзапрос выборки данных:SELECT z.id_zs, k.kmName, z.zsDate, z.zsPovestka, p.FIO FROM zasedanie z LEFT JOIN komissya k ON z.id_km = k.id_km LEFT JOIN sostavkomiss s ON k.id_km = s.id_km LEFT JOIN person p ON s.tabNo = p.tabNo WHERE s.skPreds IS TRUE AND YEAR(z.zsDate) = _year AND MONTH(z.zsDate) = _month ORDER BY k.kmName, z.zsDate ;ЗаключениеВ ходе данной работы была построена база данных для автоматизации учета заседаний городской мэрии.В ходе данной работы были получены навыки проектирования базы данных, нормализации таблиц в базе данных, создания схем базы данных, установки связей между объектами базы данных.На основе проделанной работы и получены навыки проектирования баз данных, создания хранимых процедур и триггеров в MYSQLс использованием языка SQL.Конечным результатом всей проделанной работы является база данных для применения в городских управленческих структурах.Список использованных источниковАтре Ш. Структурный подход к организации баз данных / Под ред. Ш. Атре. - М.: Финансы и статистика, 2006. - 320 с.КонноллиТ., Базы данных: проектирование, реализация, сопровождение. Теория и практика., М.: Изд. дом «Вильямс», 2014. – 460 с.Шварц, Зайцев, Ткаченко, Заводны. MySQL. Оптимизация производительности. Символ-Плюс, 2016. – 410 с.Пирогов, В.Ю. Информационные системы и базы данных: организация и проектирование: Учебное пособие / В.Ю. Пирогов. - СПб.: БХВ-Петербург, 2013. - 528 c.Коцюба И.Ю., Чунаев А.В., Шиков А.Н. Основы проектирования информационных систем. Санкт-Петербург. 2015.Бойко В.В., Савинков В.М. Проектирование баз данных информационных систем / Под ред. В.В. Бойко. - М.: Финансы и статистика, 2007. - 351 с.Гвоздева В.А. Автоматизированные информационные системы: учеб пособие / Под ред. В.А. Гвоздевой. - М.: «ФОРУМ»: ИНФРА-М, 2007. - 320 с.Кобаяси Н. Введение в информационные технологии: учеб. пособие / Под ред. Н. Кобаяси. - М.: Норма, 2009. - 267 с.Энди Оппель. Изучаем SQL. НТ Пресс, 2015. – 380 с.Кириллов, В.В. Структурированный язык запросов (SQL). – Санкт-Петербург: ИТМО, 2014. – 80 с. Дюбуа Поль. MySQL. Сборник рецептов. Символ-Плюс, 2014 год, –1058 с.Ульман Л. MySQL, LVR Москва 2013. – 249 с.Интернет ресурс http://www.mysql.ru/Интернет ресурс https://metanit.comПриложение. SQL дамп базы данных/*Created: 01.11.2019Modified: 01.11.2019Model: zasedanieDatabase: MySQL 5.7*/-- Create tables section --------------------------------------------------- Table dolgnCREATE TABLE `dolgn`( `id_dl` Int NOT NULL AUTO_INCREMENT, `dlName` Varchar(50), PRIMARY KEY (`id_dl`));-- Table komissyaCREATE TABLE `komissya`( `id_km` Int NOT NULL AUTO_INCREMENT, `kmName` Varchar(50), `kmProfile` Varchar(50), `kmDate` Date, `ordNo` Varchar(20), `kmDism` Date, `ordDismNo` Varchar(20), PRIMARY KEY (`id_km`));-- Table zasedanieCREATE TABLE `zasedanie`( `id_zs` Int NOT NULL AUTO_INCREMENT, `zsDate` Date, `zsPovestka` Text, `zsReshenie` Text, `id_km` Int, PRIMARY KEY (`id_zs`));CREATE INDEX `IX_Relationship4` ON `zasedanie` (`id_km`);-- Table sostavKomissCREATE TABLE `sostavKomiss`( `id_sk` Int NOT NULL AUTO_INCREMENT, `skDate` Date, `skOrd` Varchar(20), `skDismDate` Date, `ordDism` Varchar(20), `skPreds` Bit(1), `tabNo` Int, `id_km` Int, PRIMARY KEY (`id_sk`));CREATE INDEX `IX_Relationship2` ON `sostavKomiss` (`tabNo`);CREATE INDEX `IX_Relationship3` ON `sostavKomiss` (`id_km`);-- Table sostZasCREATE TABLE `sostZas`( `id_kz` Int NOT NULL AUTO_INCREMENT, `id_zs` Int, `tabNo` Int, PRIMARY KEY (`id_kz`));CREATE INDEX `IX_Relationship5` ON `sostZas` (`id_zs`);CREATE INDEX `IX_Relationship6` ON `sostZas` (`tabNo`);-- Table personCREATE TABLE `person`( `tabNo` Int NOT NULL AUTO_INCREMENT, `FIO` Varchar(50), `dPriem` Date, `dUvol` Date, `adres` Varchar(150), `phone` Varchar(20), `id_dl` Int, PRIMARY KEY (`tabNo`));CREATE INDEX `IX_Relationship1` ON `person` (`id_dl`);-- Create foreign keys (relationships) section ------------------------------------------------- ALTER TABLE `person` ADD CONSTRAINT `Relationship1` FOREIGN KEY (`id_dl`) REFERENCES `dolgn` (`id_dl`) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE `sostavKomiss` ADD CONSTRAINT `Relationship2` FOREIGN KEY (`tabNo`) REFERENCES `person` (`tabNo`) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE `sostavKomiss` ADD CONSTRAINT `Relationship3` FOREIGN KEY (`id_km`) REFERENCES `komissya` (`id_km`) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE `zasedanie` ADD CONSTRAINT `Relationship4` FOREIGN KEY (`id_km`) REFERENCES `komissya` (`id_km`) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE `sostZas` ADD CONSTRAINT `Relationship5` FOREIGN KEY (`id_zs`) REFERENCES `zasedanie` (`id_zs`) ON DELETE RESTRICT ON UPDATE RESTRICT;ALTER TABLE `sostZas` ADD CONSTRAINT `Relationship6` FOREIGN KEY (`tabNo`) REFERENCES `person` (`tabNo`) ON DELETE RESTRICT ON UPDATE RESTRICT;