Курсовая по БД. Курсовая работа проектирование реляционной базы данных с помощью теории нормализации. Вариант 25 тема
 Скачать 1.1 Mb.
|
3 Проектирование реляционной базы данных «Центр занятости». Создание информационно логической моделиНа первом этапе разработки структуры БД целесообразно построение информационно-логической модели, которая будет отражать логическую структуру информации предметной области. Такая модель, отвечающая требованиям нормализации данных, является основой создания реляционных баз данных. Нормализация — это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости [1]. Это необходимо для того, чтобы привести данные к продуктивному расходованию доступного места на жестком диске и облегчению обслуживания баз данных. Также обеспечение целостности данных является важнейшей задачей при проектировании и эксплуатации систем обработки данных. Целостность — актуальность и непротиворечивость информации, ее защищенность от разрушения и несанкционированного изменения [2]. Спроектируем реляционную базу данных, основанную на нормализации схем отношений с использованием здравого смысла. Для начала определим все данные, которые будут задействованы в базе данных «Центр занятости». Создадим общую таблицу данных в первой нормальной форме.Данная таблица представлена в приложении А, на рисунке А.1. Сущность находится в первой нормальной форме, когда все ее атрибуты имеют единственное значение. Чтобы признать сущность находящейся в первой нормальной форме, нужно удостовериться в том, что каждый атрибут сущности имеет единственное значение для каждого экземпляра сущности. Если в каком-либо атрибуте есть повторяющиеся значения, сущность не находится в первой нормальной форме. Атрибут – описывает данные о сущности, которые нужно сохранить. Сущность – это любой различимый объект, информация о котором хранится в базе данных [3]. Создав таблицу в первой нормальной форме, её следует разбить на несколько таблиц, которые нужно связать между собой, приводя данные таблицы к одной из нескольких нормальных форм. Сущность находится во второй нормальной форме, если она уже находится в первой нормальной форме, и каждый неидентифицирующий атрибут зависит от всего уникального идентификатора сущности. Если некий атрибут не зависит полностью от уникального идентификатора сущности, значит, он внесен ошибочно и должен быть удален. Следовательно, необходимо нормализовать такой атрибут либо найдя сущность, к которой он относится, либо создав новую сущность, в которую он должен быть помещен [3]. Проанализировав предметную область определим следующие сущности: безработный, пособие, профессиональная подготовка, вакансии. Информация о каждой сущности должна храниться в отдельной таблице, поэтому для каждого объекта создадим отдельную таблицу, которая будет содержать информацию о нем. Для того чтобы привести сущность ко второй нормальной форме, необходимо выделить в ней атрибут, который бы однозначно определял значение других атрибутов. Атрибуты фамилия, имя отчество, пол, кем выдан паспорт, дата выдачи паспорта, образование, общий стаж, семейное положение, специальность с последнего места работы, последнее место работы, средняя заработная плата за 3 месяца до увольнения, дата увольнения, причина увольнения, город проживания, дата рождения, улица, дом, квартира зависят от атрибутов серия паспорта, номер паспорта. Данную группу функциональных зависимостей выделили в отдельную таблицу «Безработный». Как показывает практика универсальным полем является код или id, оно имеет тип из семейства INT. Предпочтительно – UNSIGNED, так как в этом случае значение не может быть отрицательным. Даже если в таблице есть поля с уникальным именем пользователя, они всё равно уступают ему в скорости. Например, поля с типом VARCHAR медленно работают в качестве основных ключей. Также структура базы данных будет лучше, если в ней внутри использовать ссылки на записи на основании id. Кроме того, механизм многих СУБД использует основные ключи для своих внутренних задач, и использование поля id создает оптимальные условия для их решения [4]. Учитывая это, введем новое уникальное поле код безработного, которое будет первичным ключомдля таблицы "Безработный". Значения первичного ключа уникальны и не должны повторяться. Данная таблица представлена на рисунке 3.1.  Рисунок 3.1 – Фрагмент таблицы «Безработный», лист 1  Рисунок 3.1, лист 2  Рисунок 3.1, лист 3 В таблице «Безработный» присутствуют такие неключевые поля, как Серия паспорта и Номер паспорта, которые имеют уникальное значение, и соответственно могут определять другие поля в данной таблице. Следовательно, в даннойтаблице присутствует транзитивная зависимость. Транзитивная зависимость — это зависимость между неключевыми атрибутами. Если выделить данные атрибуты в отдельную таблицу, то получим связь между таблицами один к одному. Выделение паспортных данных в отдельную таблицу будет неэффективно, так как скорость выполнения запросов и отчетов зависят от количества связанных таблиц, для увеличения вышеуказанной скорости нет необходимости создавать новую таблицу с паспортными данными и правильней будет оставить их в таблице "Безработный", поскольку безработный имеет только один паспорт. Следовательно, максимальной формой таблицы «Безработный» является вторая нормальная форма и на этом нормализация данной таблицы заканчивается. Пришедший в центр занятости безработный указывает информацию о принадлежности своего пола. Так как гендерной принадлежности всего две – мужской или женский – вынесем её в отдельную таблицу и свяжем с таблицей «Безработный» с помощью ключевого поля код пола. Такие таблицы называются справочниками. Справочники необходимы для хранения часто вводимых данных. Полученная таблица представлена на рисунке 3.2, а его связь с таблицей «Безработный» на рисунке 3.3.  Рисунок 3.2 – Таблица–справочник «Пол»  Рисунок 3.3– Фрагмент связи между таблицей «Безработный» и справочником «Пол» Таблица «Пол» имеет вторую нормальную форму, так как имеет полную функциональную зависимость, но в ней также отсутствуют транзитивные зависимости, поэтому она находится и в третьей нормальной форме. Таблица находится в третьей нормальной форме, если она уже находится во второй нормальной форме и ни один неидентифицирующий неключевой атрибут не зависит от каких-либо других неидентифицирующих атрибутов. Атрибуты, зависящие от других неидентифицирующих атрибутов, нормализуются путем перемещения зависимого атрибута и атрибута, от которого он зависит, в новую сущность [3]. Данные о семейном положении безработного имеют часто повторяющиеся вводимые данные – замужем, женат, холост, не замужем. Следовательно, данные атрибуты необходимо вынести в отдельную таблицу-справочник и соединить её с таблицей «Безработный» с помощью ключевого полякод семейного положения. Созданная таблица изображена на рисунке 3.4, а связь с таблицей «Безработный» на рисунке 3.5.  Рисунок 3.4 – Таблица-справочник «Семейное положение»  Рисунок 3.5 – Фрагмент связи между таблицей «Безработный» и справочником «Семейное положение» Таблица «Семейное положение» имеет полную функциональную зависимость, а также в нем отсутствует транзитивная зависимость, поэтому она находится во второй и, соответственно, в третьей нормальной форме. Безработный может иметь различного уровня образование – Среднее (школьное) образование, среднее профессиональное образование, высшее профессиональное образование (магистр), высшее профессиональное образование (бакалавр). Выделим данные атрибуты в отдельную таблицу «Образование» с соответствующими полями. Свяжем данную таблицу с таблицей «Безработный» через ключевое поле Код образования. В итоге получили справочник для данных об образовании. Созданная таблица представлена на рисунке 3.6, а также связь между таблицами изображена на рисунке 3.7.  Рисунок 3. 6 – Таблица-справочник «Образование»  Рисунок 3.7 – Фрагмент связи между таблицей «Безработный» и справочником «Образование» Таблица «Образование» имеет вторую нормальную форму, но её можно отнести и к третьей нормальной форме, так как в ней нет транзитивных зависимостей. Таблица «Безработный», в процессе вынесения из нее справочников, претерпела некоторые изменения в своей структуре. Например, удалили такие атрибуты как – Образование, Семейное положение и Пол, но были добавлены поля –Код образования, Код семейного положения и Код пол, адля связи со справочниками. Преобразованная таблица представлена на рисунке 3.8.  Рисунок 3.8 – Фрагмент таблицы «Безработный», лист 1  Рисунок 3.8, лист 2  Рисунок 3.8, лист 3 Следующие атрибуты заработная плата, требуемый стаж, должностные обязанности, требования, дополнительные пожелания, режим работы, характер работы, адрес предприятия, профессиональная область, номер телефона, контактное лицо, электронная почтазависятот атрибутов специальностьи наименование компании. Данную группу функциональных зависимостей выделили в отдельную таблицу «Вакансия». Но использование составного ключа не числового типа не эффективно, так как замедлит скорость выполнения запросов, поэтомудобавим в таблицу уникальное поле код вакансии, которое будет первичным ключом и однозначно определит значения других полей.В таблицеприсутствует полная функциональная зависимость, следовательно, она находится во второй нормальной форме.Данная таблица изображена на рисунке 3.9.  Рисунок 3.9 – Фрагмент таблицы «Вакансия», лист 1  Рисунок 3.9, лист 2  Рисунок 3.9, лист 3 В таблице «Вакансия» присутствуют атрибуты: адрес предприятия, наименование компании, контактное лицо, номер телефона, которые зависят от атрибута электронная почта. Данную группу функциональных зависимостей нужно выделить в отдельную таблицу «Предприятие», так как на одном предприятии может быть несколько вакансий. Использование атрибута электронная почта, как первичного ключа данной таблицы, неэффективно, так как электронная почта имеет слишком длинное значение и является не числовым типом, поэтому написание запросов будет сложным. Поэтому добавимв таблицу "Предприятие" ключевое поле код предприятия. Полученная таблица находится во второй нормальной форме. В данной таблице присутствует транзитивная зависимость, поскольку атрибуты номер телефона и электронная почта также имеет уникальное значение, и могут определять другие поля.Если выделить данные атрибуты в отдельную таблицу, то получим связь между таблицами один к одному, что будет неэффективно. Таким образом, таблица "Предприятие" останется во второй нормальной форме. Созданная таблица представлена на рисунке 3.10, а также необходимо создать поле код предприятия в таблице «Вакансия» для связи с таблицей «Предприятие». Одно предприятие может предоставить несколько вакансий для трудоустройства, поэтому связь между таблицами один ко многим, и она представлена на рисунке 3.11.  Рисунок 3.10 – Фрагмент таблицы «Предприятие»  Рисунок 3.11 – Связь между таблицами «Вакансия» и «Предприятие» Вернемся к таблице «Вакансия». У каждой вакансии есть требование к образованию и эта информация может часто повторяться в базе данных. В связи с этим необходимо связать сущность «Вакансия» с уже созданным справочником «Образование», создав ключевое поле код образования. Полученная связь изображена на рисунке 3.12.  Рисунок 3.12 – Связь между таблицей «Вакансия» и справочником «Образование» При выборе вакансии часто повторяются данные о специальности и профессиональной области. Поэтому необходимо выделить атрибуты Специальность и Профоблатьв отдельную таблицу и связать ее с таблицей «Вакансия» с помощью ключевого поля код специальности. Но и в этой таблице прослеживаются повторяющиеся данные, так как в одной профессиональной области может быть несколько специальностей, следовательно, необходимо выделить атрибут профобластьв отдельную таблицу и с помощью ключевого поля код профобластисвязать её с сущностью «Специальность». Созданные справочники изображены на рисунках 3.13, 3.14, а связь между ними представлены на рисунке 3.15.  Рисунок 3.13 – Фрагмент таблицы «Специальность»  Рисунок 3.14 – Фрагмент таблицы «Профобласть»  Рисунок 3.15 – Связь таблицы «Вакансия» со справочниками «Специальность» и «Профобласть» Итак, полученные таблицы можно отнести к третьей нормальной форме, поскольку отсутствует транзитивная зависимость. При предоставлении вакансии указывают график работы. Режим работы может быть всего нескольких видов – полный день, вахтовый метод, сменный график, гибкий график, удаленная работа. Для того чтобы избавиться от часто повторяющихся данных вынесем их в отдельную таблицу «Режим работы» и свяжем с сущностью «Вакансия» с помощью ключевого поля Код режима работы. Полученная таблица представлена на рисунке 3.16.  Рисунок 3.16 – Таблица «Режим работы» Справочник имеет третью нормальную форму, так как в нем нет транзитивной зависимости. Каждое предприятие, предлагающее рабочее место, указывает тип занятости. В характере занятости чаще всего указывают – Полная занятость, Частичная занятость, Проектная работа, Стажировка. Вынесем эти данные в отдельную таблицу «Характер занятости» для того, чтобы убрать повторяющиеся данные. Связь с таблицей «Вакансия» реализуем при помощи ключевого поля Код характера занятости. Полученный справочник находится в третьей нормальной форме, так как в нем отсутствует транзитивная зависимость. Созданная таблица представлена на рисунке 3.17.  Рисунок 3.17 – Таблица «Характер работы» Таблица «Вакансия», изменилась в своей структуре, это связано с тем, что было создано несколько справочников и вынесены данные о предприятии. Также данная таблица относится к третьей нормальной форме, поскольку в таблице отсутствует транзитивная зависимость. Преобразованная таблица представлена на рисунке 3.18.  Рисунок 3.18 – Фрагмент таблицы «Вакансия», лист 1  Рисунок 3.18, лист 2 Центр занятости предлагает безработному различные вакансии для трудоустройства. Одному клиенту могут предложить несколько вакансий, а также на одно вакантное место могут претендовать некоторое количество безработных, из этого следует что, присутствует многозначная зависимость. Многозначная зависимость — это разновидность функциональной зависимости. Атрибут А находится в многозначительной зависимости от атрибута В, тогда, когда коду одного значения атрибута В отвечает много значений атрибута А [3]. Атрибуты Дата посещения, Результат посещения и Замечания зависят от атрибутов Код безработного и Код вакансии. Создадим связующую таблицу для того, чтобы избавиться от многозначной зависимости между таблицами "Вакансия" и "Безработный". Благодаря этому можно проследить, когда безработный посетил предлагаемое ему место работы и результат посещения, а также избавимся от многозначной зависимости. Созданная таблица представлена на рисунке 3.19, а связь между таблицами «Безработный» и «Вакансия» показаны на рисунке 3.20.  Рисунок 3.19 – Фрагмент таблицы «Посещение»  Рисунок 3.20 – Связи между таблицами «Безработный», «Вакансия» и таблицей-ассоциацией «Посещение» Атрибутынаименование организации, название курсовявляются составным ключом от которых зависят такие атрибуты кактип курсов, количество часов, адрес образовательной организации, дата начала курсов, дата окончания курсов.Данную группу функциональных зависимостей выделим в отдельную таблицу «Профессиональная подготовка». Но использование составного ключа не эффективно, поэтому добавим в таблицу уникальное поле, которое будет однозначно определять значения других полей – код курсов. В таблице, отсутствует транзитивная зависимость, следовательно, она находится в третьей нормальной форме.Данная таблица представлена на рисунке 3.21.  Рисунок 3.21 – Фрагмент таблицы «Профессиональная подготовка» В базе данных «Центр занятости» один из основных объектов «Пособие» содержит следующие атрибуты:размер пособия, начало выплаты, конец выплаты.Перечисленные атрибуты для одного безработного повторяются, поэтому эти атрибуты выделим в отдельную таблицу, а атрибут Код безработного добавим для связи с таблицей «Безработный».Для перехода ко второй нормальной форме, следует выделить в ней атрибут, который бы однозначно определял значение других атрибутов.В данной таблице нет атрибута, который бы однозначно определял значение других атрибутов, здесь набор всех атрибутов образует ключ или как его называют – составной ключ. Как показывает практика использование составного ключа не эффективно, поэтому, в таблицу необходимо добавить уникальное поле, которое будет однозначно определять значения других полей – код пособия. Так как в таблице отсутствует транзитивная зависимость, то данную таблицу можно отнести к третьей нормальной форме. Таблица отображена на рисунке 3.22, а также связь между двумя таблицами «Безработный» и «Пособие» представлена на рисунке 3.23.  Рисунок 3.22 – Фрагмент таблицы «Пособие»  Рисунок 3.23 – Связь между таблицами «Безработный» и «Пособие» Курсы, на которые направляется безработный, бывают трех видов – Обучение профессии, Повышение квалификации, Переквалификация. Для того чтобы избавиться от часто повторяющихся данных, выделим эти данные в отдельную таблицу «Тип курсов», который будет являться справочником и свяжем его с таблицей «Профессиональная подготовка» с помощью ключевого поля код типа курсов. Полученная таблица представлена на рисунке 3.24, а связь между двумя таблицами на рисунке 3.25.  Рисунок 3.24 – Таблица-справочник «Образование»  Рисунок 3.25 – Связь между таблицей «Профессиональная подготовка» и справочником «Тип курсов» В таблице «Тип курсов» нет транзитивных зависимостей, а также присутствует полная функциональная зависимость,благодаря этому можно сделать вывод, что он находится во второй равным образом, как и в третьей нормальной форме. Теперь необходимо связать между собой таблицы «Безработный» и «Профессиональная подготовка». Есликлиент, обратившийся в центр занятости, не имеет специальности или его специальность не востребована на рынке труда он направляется на профессиональную подготовку. Безработный может посетить несколько курсов, а также на один курс могут прийти несколько безработных, таким образом, связывая таблицы между собой, возникает многозначная зависимость. Атрибуты Дата начала и Дата окончаниязависят от атрибутов Код безработного и Код курсов. Выделим в таблицу-ассоциацию «Период» атрибуты Дата начала, Дата окончанияиз таблицы «Профессиональная подготовка» и добавим связующие атрибутыкод безработного и код курсов, которые являются вторичными ключамиблагодаря этому, будет отчетливо прослеживаться, когда безработный начал и окончил курсыи какие курсы, а также избавимся от многозначной зависимости между таблицами. Полученные таблицы изображены на рисунках 3.26 и 3.27, а их связь на рисунке 3.28.  Рисунок 3.26 – Фрагмент таблицы «Профессиональная подготовка»  Рисунок 3.27 – Фрагмент таблицы «Период обучения»  Рисунок 3.28 – Связь между таблицами «Безработный», «Профессиональная подготовка» и таблицей-ассоциацией «Период обучения» Итак, преобразованные таблицы можно отнести к четвертой нормальной форме, так как были убраны многозначные зависимости. Исключением является таблица «Безработный», так как он находится во второй нормальной форме, а для четвертой нормальной формы необходимый аспект — это нахождение сущности в третьей нормальной форме. Итак,таблицы приведены к третьей нормальной форме, так как в них определены полные функциональные зависимости, а также убраны транзитивные зависимости. А также таблица «Профессиональная подготовка» находятся в четвертой нормальной форме. Переходим к построению информационно-логической модели, для этого воспользуемся программой MicrosoftExcel. Здесь объекты изобразим таблицами, а названия атрибутов впишем в их строки. Связи между таблицами обозначим линиями. Связь «1» к «∞» обозначает связь один ко многим. Полученная модель представлена в приложении Б, на рисунке Б.1. |