вариант 18

Реструктуризация строк с целью исключения повторяющихся групп данных, перенос их в новые таблицыПервая нормальная формаПравила построения первой нормальной формы требуют, чтобы все таблицы данных были плоскими и не содержали повторяющихся данных в различных строках. Под плоской понимается таблица, имеющая только два измерения: длина (число записей или строк) и ширина , (число полей или столбцов). Её ячейки не могут содержать больше одного значения. Если хотя бы одна ячейка таблицы содержит больше одного значения, для представления ее содержимого уже требуется третье измерение — глубина. Плоские таблицы и плоские файлы данных, упоминавшиеся в главе 3, очень похожи тем, что имеют только два измерения. наконец в плоском файле содержится лишь одна таблица и не накладываются ограничения на содержимое ее ячеек.Вторая нормальная формаДанные во всех не ключевых столбцах полностью зависят от первичного ключа. Проверка зависимости всех полей данных от первичного ключа. Если полная зависимость не выполняется, проводится разбиение таблицы.Для приведения таблиц ко второй нормальной форме необходимо обеспечить полную зависимость столбцов, которые не являются ключевыми, от первичного ключа, а если этот ключ составной, то от каждого его элемента. Под полной зависимостью понимается возможность однозначного определения значения каждого не ключевого поля с помощью значения первичного ключа. Если для однозначного определения используется составной первичный ключ, то это правило применяется к каждому значению из полей, входящих в составной ключ. Перед переходом ко второй нормальной форме необходимо привести данные к первой нормальной форме. В процессе создания второй нормальной формы большая часть повторяющихся данных, оставшихся в таблице после приведения её к первой нормальной форме, будет удалена.Третья нормальная формаВсе данные зависят от полей первичного ключа и не зависят от значений других полей. Исключение любых транзитивных зависимостей. Имеется в виду исключение зависимостей на поле, не являющееся ключевым.В третьей нормальной форме столбцы, не являющиеся ключевыми, зависят от первичного ключа таблицы и не зависят от всех остальных столбцов. Прежде чем перейти к третьей нормальной форме, приведите свои данные к первой, а затем — ко второй.Четвёртая нормальна формаЧтобы база данных находилась в четвертой нормальной форме, необходимо, чтобы независимые 'элементы данных, между которыми существует связь типа многие-ко-многим, не хранились в одной таблице. Дальше вы найдете подробное описание четвертой нормальной формы, поскольку это единственный этап нормализации, зависящий от типов устанавливаемых связей.Пятая нормальная форма и комбинированные элементыПятая нормальная форма требует обеспечения возможности точного восстановления исходной таблицы из таблиц, на которых она основана. Построение пятой нормальной формы требует удовлетворения требований третьей нормальной формы и, при наличии связей многие-ко-многим, соответствия правилам четвертой.Многие разработчики приложений баз данных игнорируют четвёртую и пятую нормальные формы в своих программных продуктах, поскольку считают их весьма специфическими. Результатом этого зачастую является создание базы данных неправильной структуры, хотя это совсем ещё не означает, что она не будет функционировать.Основное правило при создании таблиц сущностей – это каждой сущности желательно сопоставить отдельную таблицу. Поля таблиц сущностей могут быть ключевыми или не ключевыми. Введение ключей позволяет обеспечить уникальность значений в записи, ускорить обработку записи и выполнить обработку. Если в таблице есть значительное повторение по нескольким полям и их объем существенен, то лучше их выделить в отдельную таблицу. Новую сущность легко добавить и изменить, но при удалении следует уничтожить все ссылки на нее из таблиц связей, в противном случае возникает некорректность.В данном курсовом проекте была проведена нормализация базы данных были устранены функциональные зависимости и исключена явная избыточность в таблицах. Также удалось избавиться от транзитивных зависимостей. База данных была приведена к третьей нормальной форме.3.3 Определение характеристик атрибутовВ данной работе нужно создать базу данных, содержащую сведения о сотрудниках фирмы, работающих на разных должностях и окладах. Сотрудники могут иметь детей, быть пенсионерами или иметь инвалидность. Сотрудникам предоставляется очередной отпуск. Некоторые сотрудники могут быть в отпуске по уходу за ребенком. Определим запросы, на которые должна отвечать наша БД:Вывести текущий штат всех сотрудников фирмыДать информацию по конкретному сотруднику: ФИО, должность, оклад, отдел и т. п.Кто из сотрудников сейчас в отпуске?У кого из сотрудников есть дети?Кто из сотрудников явялется пенсионером\скоро выходит на пенсию?Кто из сотрудников имеет инвалидность?Сформировать список сотрудников по отделам.Кто из сотрудников в ближайшее время уйдёт в отпуск?У кого из сотрудников в ближайшие дни будет день рождения?Дать информацию о сотрудниках по отделам: какой отдел самый большой, какой не укомплектован (не хватает людей), и т. д.Кто из сотрудников находится на больничном?Дать информацию о сотрудниках по должностям: сколько людей работают в данной должности, какой оклад у данной должности и т.п.Понятно, что первичной сущностью нашей БД будет «Сотрудник». Отделу кадров будет полезно хранить следующую информацию о каждом сотруднике:ФИОДата рожденияПолАдресE-mailВнутренний телефонДата зачисления в штатДолжность, которую занимает сотрудникОтдел, в котором числится сотрудникЯвляется ли пенсионером?Семейное положениеНаличие и количество детейИмеет ли инвалидность?Каждый из пунктов будет отдельным аттрибутом сущности «Сотрудник».Следующей нужной сущностью будет «Отдел» с аттрибутом «Название». Чтобы отделу кадров было проще собирать информацию о сотрудниках в конкретном отделе или группировать по отделам.Ещё отдельная сущность - «Должность» с аттрибутами «Название» и «Оклад». Отдел кадров следит за профессиональным развитием сотрудников. Важно, чтобы человек не просто соответствовал занимаемой должности, но также и поднимался по карьерной лестнице. Для этого отдел кадров:Проводит периодическую проверку (квалификационный экзамен) сотрудников. Период времени между экзаменами будет аттрибутом сущности «Должность»Уведомляет сотрудника о плановом повышении в должности по истечении определённого срока. Если человек отработал «норму» в некоторой должности, кампания сама предложит повышение. Данный срок «Норма» будет аттрибутом сущности «Должность»Главная задача отдела кадров - правильно учитывать работу сотрудников, определять количество рабочих, выходных и больничных дней. Важно иметь возможность в любой момент узнать «рабочее состояние» сотрудника: работает ли, находится ли в отпуске или на больничном, или временно не работает по другой уважительной причине. «Отсутствие» будет ещё одной сущностью нашей модели со следующими аттрибутами:Причина: больничный, отпуск и т. п.Номер сотрудникаДата вступления в силу: начало отпуска, больничногоДата окончания: конец отпуска, больничногоОплачивается ли отсутствие?4. Создание БД в выбранной СУБДСначала скачаем и установим СУБД MySQL на локальную машину с официального сайта www.mysql.comДля создания небольшой базы данных достаточно составить небольшой SQL-скрипт. Но такие базы данных редко покидают категорию «учебных» и переходят в разряд «реальных». Базы данных, пусть даже в небольших проектах, состоят из десятков таблиц и представлений, с которыми очень сложно работать только с помощью SQL. Удержать в голове десятки сущностей и не запутаться — очень сложно. Одно из решений этой проблемы — MySQL Workbench.MySql Workbench — это программное обеспечение для создания и проектирования баз данных с помощью схем и других визуальных средств. Сегодня мы покажем, что это такое, как установить Workbench и подключиться к кластеру, как создавать таблицы и модели, как делать импорт и экспорт данных.Как установить MySQL WorkBenchДля установки необходимо перейти на официальный сайт и среди продуктов выбрать MySQL Enterprise Edition ->Workbench.Кликаем на «Download Now» и переходим на страницу с выбором параметров.Здесь мы выбираем операционную систему и её разрядность. В нашем случае это Windows 10 64-bit. После загрузки и установки приложение готово к работе. После установки запустим MySQL Workbench и создадим новую БД human_resource. Для этого:Откроем local instance нашего сервераЩелкнем правой кнопкой в окне Navigator.Выберем пункт «Create Schema…В открывшейся вкладке найдём поле Name и впишем туда название нашей БД — human_resourceНажмем кнопку ApplyОткроется мастер создания схем, нажмём ещё раз Apply и Finish.Перейдём в редактор SQL команд и откроем файл со скриптом создания БД (исходный код приложен в конце этой записки).Запустим скрипт5. Поддержка целостности данных. 5.1 Декларативная поддержка ограничений целостностиИсДекларативная поддержка ограничений целостности заключается в определении ограничений средствами языка определения данных (DDL - Data Definition Language). Обычно средства декларативной поддержки целостности (если они имеются в СУБД) определяют ограничения на значения доменов и атрибутов, целостность сущностей (потенциальные ключи отношений) и ссылочную целостность (целостность внешних ключей). Декларативные ограничения целостности можно использовать при создании и модификации таблиц средствами языка DDL или в виде отдельных утверждений (ASSERTION).Например, следующий оператор создает таблицу PERSON и определяет для нее некоторые ограничения целостности:CREATE TABLE PERSON(Pers_Id INTEGER PRIMARY KEY,Pers_Name CHAR(30) NOT NULL,Dept_Id REFERENCES DEPART(Dept_Id) ON UPDATE CASCADE ON DELETE CASCADE);После выполнения оператора для таблицы PERSON будут объявлены следующие ограничения целостности:Поле Pers_Id образует потенциальный ключ отношения.Поле Pers_Name не может содержать null-значений.Поле Dept_Id является внешней ссылкой на родительскую таблицу DEPART, причем, при изменении или удалении строки в родительской таблице каскаднодолжны быть внесены соответствующие изменения в дочернюю таблицу.Если используется декларативное ограничение целостности, то возможны два подхода:При декларировании (объявлении) ограничения текст ограничения хранится в виде некоторого объекта СУБД, а для реализации ограничения используются встроенные в СУБД функции, и тогда этот код представляет собой внутренние функции ядра СУБД.При декларировании ограничения СУБД автоматически генерирует триггеры, выполняющие необходимые действия по проверке ограничений.Виды декларативных ограничений целостности:Ограничения целостности атрибута:Определение 9. Ограничение целостности атрибута представляют собой ограничения, накладываемые на допустимые значения атрибута вследствие того, что атрибут основан на каком-либо домене. Ограничение атрибута в точности совпадают с ограничениями соответствующего домена. Отличие ограничений атрибута от ограничений домена в том, что ограничения атрибута проверяются.Если логика предметной области такова, что на значения атрибута необходимо наложить дополнительные ограничения, помимо ограничений домена, то такие ограничения переходят в следующую категорию.5.2 Процедурная поддержка ограничений целостностиИсходя из сформированных сущностей и их аттрибутов, мы выявили следующие связи между сущностями:«Сотрудник» связан с «Отделом» и «Должностью»«Отсутствие» связано с «Сотрудником»Дальнейший анализ позволил определить тип связей: один-ко-многим. Полная модель «сущность-связь» для нашей предметной области представлена ниже:6. Реализация операций над данными.Прикладываем исходный код скрипта для создания БД:-- MySQL Workbench Forward EngineeringSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';-- ------------------------------------------------------- Schema mydb-- ------------------------------------------------------- ------------------------------------------------------- Schema human_resource-- ------------------------------------------------------- ------------------------------------------------------- Schema human_resource-- -----------------------------------------------------CREATE SCHEMA IF NOT EXISTS `human_resource` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;USE `human_resource` ;-- ------------------------------------------------------- Table `human_resource`.`department`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `human_resource`.`department` (`id` INT NOT NULL AUTO_INCREMENT,`title` VARCHAR(255) NULL DEFAULT NULL,PRIMARY KEY (`id`))ENGINE = InnoDBAUTO_INCREMENT = 12DEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ci;-- ------------------------------------------------------- Table `human_resource`.`job`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `human_resource`.`job` (`id` INT NOT NULL AUTO_INCREMENT,`title` VARCHAR(255) NOT NULL,`salary` INT NOT NULL,`test_period_months` INT NULL DEFAULT NULL,`notify_after_months` INT NULL DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE INDEX `title_UNIQUE` (`title` ASC) VISIBLE)ENGINE = InnoDBAUTO_INCREMENT = 11DEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ci;-- ------------------------------------------------------- Table `human_resource`.`employee`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `human_resource`.`employee` (`id` INT NOT NULL AUTO_INCREMENT,`lastname` VARCHAR(255) NOT NULL,`firstname` VARCHAR(255) NOT NULL,`thirdname` VARCHAR(255) NOT NULL,`birthdate` DATE NOT NULL,`is_male` TINYINT(1) NOT NULL,`address` VARCHAR(255) NOT NULL,`email` VARCHAR(255) NOT NULL,`internal_phone` INT NOT NULL,`enrollment_date` DATE NOT NULL,`job_id` INT NOT NULL,`department_id` INT NOT NULL,`is_on_pension` TINYINT(1) NOT NULL DEFAULT '0',`family_status` VARCHAR(255) NOT NULL,`children` INT NOT NULL,`is_disabled` TINYINT(1) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),INDEX `employee_ibfk_1` (`job_id` ASC) VISIBLE,INDEX `employee_ibfk_2` (`department_id` ASC) VISIBLE,CONSTRAINT `employee_ibfk_1`FOREIGN KEY (`job_id`)REFERENCES `human_resource`.`job` (`id`),CONSTRAINT `employee_ibfk_2`FOREIGN KEY (`department_id`)REFERENCES `human_resource`.`department` (`id`))ENGINE = InnoDBAUTO_INCREMENT = 11DEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ci;-- ------------------------------------------------------- Table `human_resource`.`status`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `human_resource`.`status` (`id` INT NOT NULL AUTO_INCREMENT,`employee_id` INT NOT NULL,`description` VARCHAR(255) NOT NULL,`start_date` DATE NOT NULL,`end_date` DATE NULL DEFAULT NULL,PRIMARY KEY (`id`),INDEX `status_ibfk_1` (`employee_id` ASC) VISIBLE,CONSTRAINT `status_ibfk_1`FOREIGN KEY (`employee_id`)REFERENCES `human_resource`.`employee` (`id`))ENGINE = InnoDBAUTO_INCREMENT = 12DEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_0900_ai_ci;SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;6. Список литературыГринченко, Н. Н. Проектирование баз данных. Коннолли, Т. Базы данных. Проектирование, реализация и сопровождение.Лукин, В. Н. Введение в проектирование баз данных.