Логическая модель базы данных. Нормализация
Логическая структура реляционной базы данных определяется совокупностью логически связанных реляционных таблиц.
Логические связи соответствуют структурным связям между объектами в концептуальной модели, каждый объект в логической модели отображается соответствующей реляционной таблицей.
Связи между таблицами осуществляются посредством общих атрибутов.
При проектировании логической модели базы данных необходимо ориентироваться на конкретную СУБД. На этом этапе нужно определить состав и структуру базы данных, назначить имена файлов и отдельных полей, а также определить реквизиты каждого поля.
Одно из ключевых требований к информационной модели, передаваемой из этапа анализа на этап проектирования, состоит в том, что она должна быть как минимум в третьей нормальной форме (3НФ).
Нормализация является основой для удаления из сущностей нежелательных функциональных зависимостей (ФЗ). ФЗ подразумевается, если мы можем определить значение атрибута, просто зная значение некоторого другого атрибута. Например, если мы знаем название страны, то можем определить название ее столицы. Следовательно, между страной и ее столицей имеется функциональная зависимость.
Существует еще один вариант зависимости, известный как многозначная зависимость (МЗЗ). Она означает, что если мы знаем значение одного атрибута, то можем определить набор значений другого атрибута. Например, зная название страны, можно определить названия всех ее аэропортов; следовательно, между страной и аэропортами существует многозначная зависимость. ФЗ и МЗЗ иногда обозначаются следующим образом:
ФЗ: А • В (А определяет В)
МЗЗ: А • • В (А определяет набор В)
Почему нормализованная информационная модель так важна в реляционном проектировании? Многочисленные испытания доказали, что процесс нормализации дает наилучший результат при моделировании мира с использованием двумерных объектов (таблиц) без установления слишком большого числа ограничений или искажения фактов (данных), для сбора которых мы пользуемся базой данных. С практической точки зрения, нормальные формы помогают проектировать базы данных, в которых нет ненужных избыточных данных и противоречий, которые могут повлечь за собой проблемы производительности или потерю информации при последующем выполнении операций вставки, обновления и удаления. Чтобы подытожить сказанное, отметим, что нормальные формы позволяют избежать искажения данных путем создания ложных данных или разрушения истинных.
Первая нормальная форма (1НФ). Сущность находится в первой нормальной форме, если значения всех ее атрибутов атомарные. Все повторяющиеся группы должны быть удалены и помещены в новую (связанную) сущность.
Вторая нормальная форма (2НФ). Сущность находится во второй нормальной форме, если она находится в первой нормальной форме, а каждый ее неключевой атрибут функционально полно зависит ключа (или от каждого компонента первичного ключа — для сущностей с составными ключами, состоящими из двух и более атрибутов). Вторая нормальная форма требует, чтобы не было неключевых атрибутов, которые зависят только от части первичного ключа.
Третья нормальная форма (ЗНФ). Сущность находится в третьей нормальной форме, если она находится во второй нормальной форме и все ее неключевые атрибуты зависят только от первичного ключа. То есть при этом они не должны зависеть и от других неключевых атрибутов.
Преобразование в 3НФ. Чтобы преобразовать информационную модель в третью нормальную форму, нужно помнить: «Все атрибуты сущности должны зависеть от ключа, только от ключа и ни от чего, кроме ключа».
Пример нормализации
Если построить отношение, которое включают все интересующие нас атрибуты, но оно обладает огромным количеством недостатков. Например, это аномалии удаления и аномалии вставки. Т.е., если мы, например, хотим удалить информацию о каком-либо поставщике, то мы удалим и информацию, относящуюся к другим сущностям (информацию о поставленной поставщиком ценности, о списании этой ценности и т. д.). В то же время, если мы просто захотим ввести в базу информацию о какой-либо ценности, то мы не сможем этого сделать, пока ценность не будет списана, и мы не введем туда же и информацию о списанной ценности. Налицо бессмысленность такой базы данных.
Следует прибегнуть к нормализации, т.е. к разбиению большого отношения на несколько маленьких с целью устранения аномалий модификации.
Необходимо создать отдельные таблицы для Ценностей, Поставщиков, Материально-ответственных лиц и Причин списания. Здесь нужно задать для каждой строки каждой таблицы код, который будет однозначно идентифицировать строку в таблице. Эти поля впоследствии станут первичными ключами.
Таблица «Поставщики» будет состоять из Кода, Наименования поставщика и Города его регистрации. Все данные, указанные здесь, относятся только к поставщику. Их (кроме кода) нет смысла упоминать в последующих таблицах, иначе мы столкнемся с избыточностью данных.
Таблица «Ценности» будет также включать Код и Наименование, а также ряд специфических атрибутов — «Единицы измерения», «Цена» и «Срок службы». Поле «Единицы измерения» - чисто символическое, поскольку наша база не содержит никаких «весовых» ценностей, в основном всё будет исчисляться «штуками». Но есть случаи, когда разумней упомянуть в качестве единиц «Комплекты». Поле «Цена» носит информационный характер. Что касается «Срока службы», то будет правильным исчислять его в месяцах. На основании этого поля товар может подлежать списанию по причине «Окончания срока службы». Однако за владельцем остается право не списывать ценность - все зависит только от его желания.
В таблицу «Материально-ответственные лица» в качестве информации введем Код, ФИО, Должность и Место работы.
Теперь необходимо создать таблицу, которая будет содержать информацию о поставках ценностей - что поставлено, кем, когда и в каком количестве. Так как уже есть информация о ценностях и поставщиках, то вместо их названий будем использовать их коды. Поле «количество» будет числовым. Здесь в качестве ключа одним полем уже не обойтись - в каждом столбце могут повторяться как Код поставщика, так и Код ценности. Наверное, следовало бы сделать ключом эти два поля... Но разумней будет создать дополнительный атрибут с типом «счетчик», и сделать его ключевым. Таким образом, избежим необходимости назначения составного (композитного) ключа.
Следующая таблица будет рассказывать о том, за кем из материальных лиц закреплена определенная ценность. Аналогично будем вводить код М-0 лица, ценности и «дату закрепления» (тип поля - «Дата/время»). Здесь тоже применим суррогатный ключ - добавим поле «Номер ответственности» с типом «Счетчик».
Причины списания могли вписывать в структуру таблицы «Списание», не используя отдельной таблицы. Однако это связано с определенной трудностью – тогда невозможно ввести информацию о какой-либо причине списания, пока не появится списанная по этой причине вещь.
Таблица «Списание» будет состоять из 4 атрибутов - Код ценности, Код причины списания, Количество и Даты списания. В качестве ключа добавим поле «Номер списания» с типом данных «Счетчик».
Теперь по возможности избавились от аномалий вставки и удаления.
Таблицы находятся в первой нормальной форме, т.к. они удовлетворяют определению отношения - ячейки содержат одиночные значения, все записи в одном атрибуте имеют один и тот же тип, каждый столбец имеет уникальное имя, нет одинаковых строк.
Также база находится и во второй нормальной форме, поскольку все его неключевые атрибуты зависят от всего ключа. А поскольку отношения еще и не имеют транзитивных зависимостей, то база находится в третьей нормальной форме.
Физическое проектирование
Этап физического проектирования заключается в увязке логической структуры БД и физической среды хранения с целью наиболее эффективного размещения данных, т.е. отображении логической структуры БД в структуру хранения. Решается вопрос размещения хранимых данных в пространстве памяти, выбора эффективных методов доступа к различным компонентам "физической" БД. Результаты этого этапа документируются в форме схемы хранения на языке определения данных (DDL). Принятые на этом этапе решения оказывают определяющее влияние на производительность системы.
Одной из важнейших составляющих проекта базы данных является разработка средств защиты БД. Защита данных имеет два аспекта: защита от сбоев и защита от несанкционированного доступа. Для защиты от сбоев разрабатывается стратегия резервного копирования. Для защиты от несанкционированного доступа каждому пользователю доступ к данным предоставляется только в соответствии с его правами доступа.
Физическое проектирование — создание схемы базы данных для конкретной СУБД. Специфика конкретной СУБД может включать в себя ограничения на именование объектов базы данных, ограничения на поддерживаемые типы данных и т.п. Кроме того, специфика конкретной СУБД при физическом проектировании включает выбор решений, связанных с физической средой хранения данных (выбор методов управления дисковой памятью, разделение БД по файлам и устройствам, методов доступа к данным), создание индексов и т.д.
Далее – получение физической модели БД. Каждая схема отношения должна быть описана в следующем виде:
| Имя столбца | Тип столбца | Длина | Точность | Тип Ключа | Опциональность | Логические ограничения |
В этой таблице логические ограничения записываются в терминах выбранной СУБД.
Например: Dat_rogd < date(), т.е. значение столбца «дата рождения» не может превышать текущую дату.
Должно быть создано приложение, которое обеспечивает следующие действия:
- ввод и корректировка данных для всех таблиц с реализацией ограничений целостности для каждой таблицы и каждого поля (первичный ключ, неопределенное значение, внешний ключ, ссылочная целостность, бизнес-правила);
- просмотр данных;
- реализация нескольких запросов к БД.
Варианты заданий для проектирования баз данных
1. БД "Деканат".
Задача – информационная поддержка деятельности деканата вуза:
- ведение расписания сессии, хранение результатов сессии;
- составление зачётных и экзаменационных ведомостей;
- составление расписаний экзаменов по группам, кафедрам, для отдельных преподавателей;
- проверка корректности расписания экзаменов (уникальность комбинации "время – дата – аудитория"; между экзаменами в одной группе должно пройти не менее трёх дней);
- подсчёт по результатам зачётов и экзаменов итоговых значений (количество оценок '5', '4', '3', '2', количество неявок, средний балл по группе);
- получение списка экзаменов на текущую дату.
2. БД "Отдел кадров" (института).
Задача – информационная поддержка деятельности отдела кадров.
Различают три группы сотрудников: а) администрация; б) преподавательский и инженерно-технический состав (по кафедрам); в) технический персонал. БД должна содержать штатное расписание по отделам (кафедрам) с указанием количества ставок по должностям, включать архив сотрудников и учитывать сотрудников, находящихся в отпуске по уходу за ребенком.
БД должна предоставлять возможность составления должностных (штатных) расписаний по кафедрам и отделам и следующих списков:
- вакансий (с учётом сотрудников, находящихся в отпуске по уходу за ребенком, т.е. с указанием даты, до которой ставка свободна);
- пенсионеров;
- людей предпенсионного возраста (не более 2-х лет до пенсии);
- бездетных сотрудников;
- юбиляров текущего года;
- многодетных сотрудников (трое и более детей);
- ветеранов (работающих в институте не менее тридцати лет);
- сотрудников, работающих более чем на одной ставке.
3. БД "Плановый отдел".
Задача – информационная поддержка деятельности планового отдела (выбрать конкретное производство).
БД должна осуществлять:
- ведение плановой документации по основному и вспомогательному производствам (план и факт);
- составление заказов на поставку сырья и комплектующих (в соответствии с планом выпуска продукции);
- составление планов работы вспомогательных производств для обеспечения потребностей основного производства;
- подсчёт энергозатрат;
- определение соответствия результатов работы плану (в процентах).
4. БД "Кафедра".
Задача – информационная поддержка учебного процесса и организационной деятельности на кафедре вуза. БД должна содержать учебный план, расписание занятий, списки групп, выпускаемых кафедрой, и списки аспирантов (с руководителями и темами исследований). БД должна обеспечивать составление:
- расписания занятий на семестр (по группам);
- учебного плана (по семестрам) для каждого курса;
- расписания занятий для преподавателей;
- списка телефонов сотрудников;
- нагрузки по часам для преподавателей;
- списка научных кадров по научным направлениям;
- списков студентов-дипломников (по группам и по преподавателям).
5. БД "Библиотека".
5. БД "Библиотека".
Задача – информационная поддержка деятельности научно-технической библиотеки.
БД должна включать два раздела: "Научная литература" и "Журнальные публикации". БД должна обеспечивать:
- ведение автоматизированного учёта выдачи/приёма литературы;
- ведение очередей на литературу (по заказам);
- учёт рейтинга изданий (количество читателей и дата последней выдачи);
- поиск литературы по требуемым разделу, теме, автору, ключевому слову (с заданием интересующего периода);
- составление списков должников по годам.
6. БД "Больница".
Задача – информационная поддержка деятельности регистратуры больницы. БД должна осуществлять:
- учёт поступления пациентов (по отделениям);
- учёт проведённого лечения;
- учёт платных услуг с выдачей счетов на оплату;
- ведение архива выписанных пациентов.
Необходимо предусмотреть определение (по отделениям):
- пропускной способности больницы;
- среднего времени пребывания больных в стационаре;
- наличия свободных мест в палатах (отдельно для мужчин и для женщин);
- количества прооперированных пациентов (из них – с осложнениями и умерших);
- смертности.
7. БД "Магазин" (выбрать конкретный профиль).
Задача – информационная поддержка деятельности магазина выбранного профиля. БД должна осуществлять:
- учёт поставщиков и поставок;
- учёт продаж по отделам;
- подсчёт остатков товаров (по отделам);
- оформление заказов на товары, запасы которых подходят к концу;
- подведение финансовых итогов дня (по отделам и в целом по магазину);
- анализ результативности работы продавцов (для премирования);
- анализ объёмов продаж по дням недели и по месяцам.
8. БД "Поликлиника".
Задача – информационная поддержка деятельности поликлиники. БД должна осуществлять:
- ведение медицинских карт пациентов;
- учёт рецептов, направлений на анализы, процедур;
- учёт платных услуг с выдачей счёта на оплату;
- ведение очередей на приём к специалистам по направлениям лечащих врачей.
Необходимо предусмотреть:
- определение посещаемости отдельных кабинетов (нагрузки врачей);
- подсчёт количества заболевших за день для определения наступления эпидемии.
9. БД адвоката.
Задача – информационная поддержка деятельности адвокатской конторы. БД должна осуществлять:
- ведение списка адвокатов;
- ведение списка клиентов;
- ведение архива законченных дел.
Необходимо предусмотреть:
- получение списка текущих клиентов для конкретного адвоката;
- определение эффективности защиты (максимальный срок минус полученный срок) с учётом оправданий, условных сроков и штрафов;
- определение неэффективности защиты (полученный срок минус минимальный срок);
- подсчёт суммы гонораров (по отдельных делам) в текущем году;
- получение для конкретного адвоката списка текущих клиентов, которых он защищал ранее (из архива, с указанием полученных сроков и статей).
10. БД по недвижимости.
Задача – информационная поддержка деятельности фирмы, занимающейся продажей и арендой жилых и нежилых помещений. БД должна:
- осуществлять ведение списков жилых и нежилых помещений, предназначенных для аренды и/или продажи;
- поддерживать архив проданных и сданных в аренду помещений;
- производить поиск вариантов в соответствии с требованиями клиента.
Необходимо предусмотреть получение разнообразной статистики:
- наличие помещений разных типов;
- изменение цен на рынке;
- уровни спроса и предложения;
- средние показатели (среднее время нахождения помещения в БД (по типам помещений), среднюю стоимость аренды/продажи помещений и т.п.
11. БД "Гостиница".
Задача – информационная поддержка деятельности гостиницы.
БД должна осуществлять:
- ведение списка постояльцев;
- учёт забронированных мест;
- ведение архива выбывших постояльцев за последний год.
Необходимо предусмотреть:
- получение списка свободных номеров (по количеству мест и классу);
- получение списка номеров (мест), освобождающихся сегодня и завтра;
- выдачу информации по конкретному номеру;
- автоматизацию выдачи счетов на оплату номера и услуг;
- получение списка забронированных номеров;
- проверку наличия брони по имени клиента и/или названию организации.
12. БД "Продажа билетов".
Задача – информационная поддержка деятельности транспортных касс (выбрать вид транспорта). БД должна осуществлять:
- ведение списка рейсов и билетов на них с указанием класса;
- учёт забронированных мест;
- ведение архива пассажиров за последний месяц.
Необходимо предусмотреть:
- продажу билетов в оба конца;
- поиск места на рейс в соответствии с требованиями заказчика;
- получение списка свободных мест на рейс;
- выдачу информации по конкретному рейсу;
- получение списка проданных мест;
- проверку наличия брони по имени клиента и/или названию организации.
13. БД "Спортивный клуб".
Задача – информационная поддержка деятельности спортивного клуба. БД должна осуществлять:
- ведение списков спортсменов и тренеров;
- учёт проводимых соревнований (с ведением их архива);
- учёт травм, полученных спортсменами.
Необходимо предусмотреть:
- возможность перехода спортсмена от одного тренера к другому;
- составление рейтингов спортсменов;
- составление рейтингов тренеров;
- выдачу информации по соревнованиям;
- выдачу информации по конкретному спортсмену;
- подбор возможных кандидатур на участие в соревнованиях (соответствующего уровня мастерства, возраста и без травм).
Задания для заочно-сокращенной формы обучения
1. Создать БД с перечнем видов спорта, присваиваемых разрядов и званий по каждому из них. БД содержит сведения о спортсменах, включающих условный номер вида спорта, которому отдает предпочтение каждый спортсмен. Спортсмен может заниматься несколькими видами спорта.
2. Создать БД для учета движения товара на оптовом складе. Описать поставщиков товара. Реализация товара может происходить как на самом складе, так и через сеть магазинов.
3. У вас есть коллекция картин, содержащая сведения о названии картины, ее авторе, дате создания, местонахождении. Если это музей, то указана страна, город, почтовый адрес, адрес электронной почты, факс, телефон. Если картина храниться в частной коллекции, то указаны адрес, телефон, фамилия владельца. Вы хотите построить базу данных, которая позволит находить сведения о картине, а также определять ее местонахождение и владельца.
4. Спроектируйте базу данных поставщиков, деталей и проектов. Если известно, что определенный поставщик поставляет определенную деталь для определенного проекта в определенном количестве. Поставщики определяются статусом и городом, в котором они находятся. Детали классифицируются по весу, цвету. Проекты выполняются в различных городах.
5. Спроектируйте базу данных винный погреб, для выполнения следующих операций в этой базе: а) выберите номер отсека, наименование вина и количество бутылок для всех вин Geyser Peak; б) выберите номер отсека для всех красных вин; в) выберите номер отсека, наименование вина для всех вин, запас которых составляет более пяти бутылок; г) добавьте данные нового поступления (12 бутылок) Gary Farell Merlot: отсек номер 55, год 91, готово в 96.
6. Учебная база данных включает информацию о системе процесса обучения внутри компании. Для каждого курса обучения база данных содержит подробности обо всех необходимых условиях и предложениях такого курса; а для каждого предложения она содержит подробности обо всех преподавателях и студентах, зарегистрированных для такого предложения. База данных также содержит информацию о сотрудниках.
7. У вас есть коллекция классической музыки, содержащаяся на компакт-дисках, пластинках и аудиокассетах, вы хотите построить базу данных, которая позволит находить записи определенного композитора, дирижера, солиста, произведения, оркестра, вида произведения или камерной группы.
8. Разработайте схему БД, содержащую информацию об автомобильном парке организации. БД содержит сведения: о водителях машин (ФИО, категория (A,B,C,D,E B и. т.п.), водительский стаж, закрепленные автомобили и т.д.): об автомобилях автопарка (марка, год выпуска, класс автомобиля (грузовой, легковой), цвет, техническое состояние (аварийное, удовлетворительное, хорошее, отличное) и т.д.). В проектируемой БД за каждым водителем может быть закреплена не менее чем 1 машина, каждой машиной может управлять более 1-го водителя. Водители объединены в бригады, в каждой бригаде свой бригадир.