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

(1)

СИСТЕМЫ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ

Методические указания по выполнению расчетно-графических работ для студентов специальности 5В070200 – Автоматизация и управление

Некоммерческое акционерное общество

Кафедра инженерной кибернетики

АЛМАТИНСКИЙ УНИВЕРСИТЕТ ЭНЕРГЕТИКИ И СВЯЗИ

(2)

Алматы 2017

СОСТАВИТЕЛИ: К.Т.Сауанова. Системы управления базами данных.

Методические указания по выполнению расчетно-графических работ для студентов специальности 5В070200 – Автоматизация и управление. – Алматы:

АУЭС, 2017. – 33 с.

Методические указания содержат указания по выполнению расчетно- графических работ, в них приведены описания каждой расчетно-графической работы, дана поэтапная методика построения базы данных, перечень рекомендуемой литературы и контрольные вопросы.

Методические указания предназначены для студентов всех форм обучения специальности 5В070200 –Автоматизация и управление.

Ил.23, табл.17, библиогр. – 7 назв.

Рецензент: доц. А.А.Аманбаев.

Печатается по плану издания некоммерческого акционерного общества

«Алматинский университет энергетики и связи» на 2017 г.

(3)

©НАО «Алматинский университет энергетики и связи», 2017 г.

Введение

Задача ваших расчетно-графических работ –поэтапное проектирование базы данных. Проектирование базы данных (БД) – одна из важных и сложных задач, связанных с созданием информационной системы (ИС).

В результате её решения вами должны быть определены содержание БД, способ организации данных и средства управления данными.

Спроектированная БД должна удовлетворять следующим требованиям:

1) Схема БД должна быть корректна.

2) Обеспечение ограничений.

3) Эффективность функционирования.

4) Защита данных.

5) Простота и удобство эксплуатации.

6) Гибкость, т.е. возможность развития и адаптации к изменениям предметной области и/или требований пользователей.

Первые четыре требования должны быть выполнены обязательно.

(4)

Сотрудник

Таб№

Должность ФИО

1 Расчетно-графическая работа № 1. Инфологическое проектирование

Цель: анализ предметной области, информационных задач и круга пользователей, определение требований к операционной обстановке.

Работа с базой данных начинается с построения модели предметной области. Наиболее распространенной является ER-модель (entity-relationship model) – модель «Сущность-связь».

Для построения ER-модели на практике будем использовать простую систему обозначений, предложенную Питером Ченом.

Базовые понятия.

Таблица 1-Сущность «Сотрудник»

Сущность (объект) Сотрудник

Атрибут сущности (свойства характеризующие объект)

ФИО, должность Ключевой атрибут (атрибут,

входящий в первичный ключ)

Таб№

Связъ M:1

Первичный ключ (primary key) – это атрибут или группа атрибутов, однозначно идентифицирующих объект.

Первичный ключ может состоять из нескольких атрибутов, тогда подчеркивается каждый из них.

Объект и его атрибуты соединяются ненаправленными дугами.

Рисунок 1- Объект и его атрибуты Связи между объектами могут быть 3-х типов:

Один – к одному. Этот тип связи означает, что каждому объекту первого вида соответствует не более одного объекта второго вида, и наоборот.

Например: сотрудник может руководить только одним отделом, и у каждого отдела есть только один руководитель.

(5)

Рисунок 2 - Связь один- к одному

Один – ко многим (или в обратную сторону Многие – к одному). Этот тип связи означает, что каждому объекту первого вида может соответствовать более одного объекта второго вида, но каждому объекту второго вида соответствует не более одного объекта первого вида.

Рисунок 3 - Связь один-ко многим

Например: в каждом отделе может быть множество сотрудников, но каждый сотрудник работает только в одном отделе.

Многие – ко многим. Этот тип связи означает, что каждому объекту первого вида может соответствовать более одного объекта второго вида, и наоборот. У этого типа связи иногда бывают собственные атрибуты.

Например: каждый счет может включать множество товаров, и каждый товар может входить в разные счета.

Рисунок 4 - Связь многие-ко многим

Связь может соединять сущность саму с собой, например:

(6)

Рисунок 5 - Связь сама с собой

Иногда используют такое понятие, как слабая сущность. Это сущность, которая не может быть однозначно идентифицирована с помощью собственных атрибутов, а только через связь с другой сущностью.

Пусть, например, номер сотрудника является уникальным только в пределах отдела, т.е. в разных отделах могут быть сотрудники с одинаковыми номерами. Уникальной в данном случае будет комбинация атрибутов

«НомерСотрудника, НомерОтдела». Сущность «Сотрудник» является слабой.

На схеме слабые сущности и их идентифицирующие связи обозначаются двойными линиями.

Рисунок 6 - Слабая сущность

Иногда для более удобной классификации используются так называемые подтипы сущностей. Их обозначают с помощью треугольника, направленного от подтипа к надтипу. Этот треугольник может сопровождаться надписью

«есть» или «is a» (т.е. «является»).

(7)

Рисунок 7- Подтипы сущностей

Пусть, например, среди контрагентов могут быть как физические, так и юридические лица. Поскольку они имеют разные атрибуты, то удобно создать для них подтипы.

Сущность «Контрагент» является надтипом для своих подтипов.

Обратите внимание, что у подтипов обычно не бывает собственных первичных ключей.

Если связь соединяет две сущности, она называется бинарной.

Связь может соединять более двух сущностей, например, связь, соединяющая три сущности, называется тернарной.

Рисунок 8 - Тернарная связь

Связь с арностью более 2 обычно имеет тип «многие – ко многим» по отношению ко всем связанным сущностям.

Задание для расчетно-графической работы № 1

Выберите любую предметную область, для которой вы будете создавать базу данных и разработайте для нее ER-модель. В ER-модели должно содержаться не менее 5 разных сущностей и связи между ними. Постарайтесь использовать также слабые сущности и/или подтипы сущностей.

(8)

2 Расчетно-графическая работа №2. Логическое проектирование реляционной БД. Преобразование ER-модели в реляционную модель

Цель: преобразование ER–диаграммы в схему базы данных, составление реляционных отношений, нормализация отношений, определение ограничения целостности, описание группы пользователей и их права.

Первичным ключом (primary key), как и в ER-модели, называется атрибут или группа атрибутов, однозначно идентифицирующих объект. Первичные ключи будем подчеркивать.

Имена атрибутов в масштабе ER-модели удобно делать уникальными, тогда при построении реляционной модели их (почти никогда) не придется переименовывать.

Внешним ключом (foreign key) называют ссылку на родительский объект.

Обычно внешние ключи появляются в таблицах в результате преобразования связей. Будем выделять внешние ключи курсивом.

Для краткости в некоторых примерах пропущены несущественные неключевые атрибуты.

I. Преобразование сущностей.

1. Преобразование обычной сущности.

Рисунок 9 - Объект «Сущность»

Обычная сущность преобразуется в отдельную таблицу, столбцами таблицы будут все атрибуты сущности:

Сущность (Ключ, Атрибут1, Атрибут2)

Рисунок 10 - Преобразование слабой сущности

(9)

Слабая сущность преобразуется в отдельную таблицу, столбцами таблицы будут все атрибуты сущности плюс ключевые атрибуты всех сильных сущностей, с помощью которых данная слабая сущность идентифицируется.

Ключевые поля всех сильных сущностей таблиц войдут в первичный ключ слабой сущности.

Для слабой сущности они будут являться внешними ключами.

Сущность1 (Ключ1, Ключ2, Атрибут1, Атрибут2)

Рисунок 11- Преобразование подтипов сущностей

1 способ. Создается одна таблица, в которую помещают все атрибуты.

Для того чтобы указать, к какому подтипу относится объект, приходится вводить дополнительное поле-признак.

Сущность1 (Ключ, Атрибут1, Атрибут2, Атрибут3, Атрибут4, Атрибут5, Признак)

2 способ. Создается отдельная таблица для каждого подтипа. В нее включаются все атрибуты этого подтипа и все атрибуты надтипа.

Подтип1 (Ключ, Атрибут1, Атрибут2, Атрибут3) Подтип2 (Ключ, Атрибут1, Атрибут4, Атрибут5)

3 способ. Создается одна таблица для надтипа и по одной таблице для каждого подтипа, в которую включаются ключевые поля надтипа:

Сущность1 (Ключ, Атрибут1)

Подтип1 (Ключ, Атрибут2, Атрибут3) Подтип2 (Ключ, Атрибут4, Атрибут5) Преобразование связей.

Для связей- двойных ромбов ничего делать не нужно, вся информация уже хранится в таблице слабой сущности.

Рисунок 12- Связь М:М

(10)

По правилам преобразования обычной сущности, как мы видели выше, для каждой сущности создается отдельная таблица, содержащая все её атрибуты:

Сущность1 (Ключ1, Атрибут1, Атрибут2) Сущность2 (Ключ2, Атрибут3, Атрибут4)

Для связи создается отдельная таблица, содержащая ключевые поля каждой сущности, участвующей в связи, и собственные атрибуты связи, если таковые имеются. В названии обычно отражают, какие именно сущности связываются, или называют новую таблицу именем связи.

Сущ1Сущ2 (Ключ1, Ключ2, Атрибут5)

Рисунок 13- Связь 1:М

1 способ. Точно так же, как и в случае М:М, создаются отдельные таблицы для сущностей и отдельная таблица для связи, содержащая ключевые поля каждой сущности, участвующей в связи. Первичным ключом будет ключ второй сущности.

Сущность1 (Ключ1, Атрибут1, Атрибут2) Сущность2 (Ключ2, Атрибут3, Атрибут4) Сущ1Сущ2 (Ключ, Ключ2)

2 способ. Новая таблица для связи не создается, а в таблицу дочерней сущности добавляют ключевые поля родительской сущности (в первичный ключ дочерней сущности они входить не будут). Ключевые поля родительской сущности представляют собой внешний ключ для дочерней сущности.

Сущность1 (Ключ1, Атрибут1, Атрибут2)

Сущность2 (Ключ2, Атрибут3, Атрибут4, Ключ1) Связь 1:1

(11)

1 способ. Точно так же, как и в случае М:М, создаются отдельные таблицы для сущностей и отдельная таблица для связи, содержащая ключевые поля каждой сущности, участвующей в связи.

Первичным ключом этой таблицы будет ключ любой сущности.

Рисунок 14 - Связь 1:1 Сущность1 (Ключ1, Атрибут1, Атрибут2)

Сущ1Сущ2 (Ключ1, Ключ2) или Сущ1Сущ2 (Ключ1, Ключ2)

2 способ. Точно так же, как и во 2 случае 1:М, новая таблица для связи не создается, а в таблицу одной из сущностей (будем считать ее дочерней) добавляют ключевые поля другой сущности (будем считать ее родительской).

Сущность2 (Ключ2, Атрибут3, Атрибут4, Ключ1)

3 способ. Две таблицы для сущностей, связанных соотношением 1:1, объединяются в одну. Ключом новой таблицы может быть комбинация ключей обеих таблиц. Если хотя бы в одном направлении связь «ровно к одному», то ключ этой сущности можно считать ключом объединенной таблицы.

Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2)

или, возможно, Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2), или Сущ1Сущ2(Ключ1, Атрибут1, Ключ2, Атрибут2).

Для связи сущности с самой собой применяются те же правила, но так как одна и та же сущность участвует в связи дважды, ключевые поля должны войти в одну и ту же таблицу два раза. Поэтому приходится переименовывать один из ключей.

(12)

Рисунок 15- Связь 1:M

Рассмотрим связь 1:M, способ 2. Переименован внешний ключ.

Сущность1 (Ключ1, Атрибут1, Атрибут2, ЕщеОдинКлюч1).

Примечание 2:

Рисунок 16

Для связей с арностью более 2 обычно применяется тот же способ, что и для бинарной связи M:M, создается новая таблица, содержащая ключевые поля всех связанных таблиц.

Сущ1Сущ2Сущ3 (Ключ1, Ключ2, Ключ3)

Задание для расчетно-графической работы №2

Преобразуйте вашу ER-модель в реляционную модель. В получившихся таблицах не забудьте отметить первичные и внешние ключи.

3 Расчетно-графическая работа №3. Физическое проектирование БД

Цель: реализация своей базы посредством SQL Server 2012.

(13)

Используя средства DDL (Data Definition Language) – языка описания данных, создайте базу данных и таблицы.

Сначала создайте родительские таблицы, затем дочерние, т.е. такие, которые содержат ограничения внешних ключей.

В процессе отладки сценария создания базы данных вам, наверняка, не раз придется удалять таблицы и создавать их заново. Поэтому для удаления таблиц удобно написать отдельный сценарий.

Удаляются сначала дочерние таблицы, затем родительские.

Используя средства DML (Data Manipulation Language) – языка манипулирования данными, постройте команды – команды добавления, изменения и удаления данных INSERT, UPDATE и DELETE.

Используя средства DQL (Data Query Language) – языка запросов, состоящего из единственной команды SELECT, постройте запросы.

Задание для расчетно-графической работы №3

Напишите и отладьте SQL-сценарий создания вашей базы данных и таблиц для нее.

4 Варианты заданий для РГР 1. БД «Деканат».

Задача – информационная поддержка деятельности деканата вуза:

 ведение расписания сессии, хранение результатов сессии;

 составление зачётных и экзаменационных ведомостей;

 составление расписаний экзаменов по группам, кафедрам, для отдельных преподавателей;

 проверка корректности расписания экзаменов (уникальность комбинации «время – дата – аудитория»; между экзаменами в одной группе должно пройти не менее трёх дней);

 подсчёт по результатам зачётов и экзаменов итоговых значений (количество оценок '5', '4', '3', '2', количество неявок, средний балл по группе);

 получение списка экзаменов на текущую дату.

2. БД «Отдел кадров» (университета).

Задача – информационная поддержка деятельности отдела кадров.

Различают три группы сотрудников:

а) администрация;

б) преподавательский и инженерно-технический состав (по кафедрам);

в) технический персонал.

БД должна содержать штатное расписание по отделам (кафедрам) с указанием количества ставок по должностям, включать архив сотрудников и учитывать сотрудников, находящихся в отпуске по уходу за ребенком.

(14)

БД должна предоставлять возможность составления должностных (штатных) расписаний по кафедрам и отделам и следующих списков:

 вакансий (с учётом сотрудников, находящихся в отпуске по уходу за ребенком, т.е. с указанием даты, до которой ставка свободна);

 пенсионеров;

 людей предпенсионного возраста (не более 2-х лет до пенсии);

 бездетных сотрудников;

 юбиляров текущего года;

 многодетных сотрудников (трое и более детей);

 ветеранов (работающих в институте не менее тридцати лет);

 сотрудников, работающих более чем на одной ставке.

3. БД «Плановый отдел».

Задача – информационная поддержка деятельности планового отдела (выбрать конкретное производство).

БД должна осуществлять:

 ведение плановой документации по основному и вспомогательному производствам (план и факт);

 составление заказов на поставку сырья и комплектующих (в соответствии с планом выпуска продукции);

 составление планов работы вспомогательных производств для обеспечения потребностей основного производства;

 подсчёт энергозатрат;

 определение соответствия результатов работы плану (в процентах).

4. БД «Кафедра».

Задача – информационная поддержка учебного процесса и организационной деятельности на кафедре вуза. БД должна содержать учебный план, расписание занятий, списки групп, выпускаемых кафедрой, и списки аспирантов (с руководителями и темами исследований). БД должна обеспечивать составление:

 расписания занятий на семестр (по группам);

 учебного плана (по семестрам) для каждого курса;

 расписания занятий для преподавателей;

 списка телефонов сотрудников;

 нагрузки по часам для преподавателей;

 списка научных кадров по научным направлениям;

 списков студентов-дипломников (по группам и по преподавателям).

5. БД «Библиотека».

Задача – информационная поддержка деятельности научно-технической библиотеки.

БД должна включать два раздела: «Научная литература» и «Журнальные публикации». БД должна обеспечивать:

(15)

 ведение автоматизированного учёта выдачи/приёма литературы;

 ведение очередей на литературу (по заказам);

 учёт рейтинга изданий (количество читателей и дата последней выдачи);

 поиск литературы по требуемым разделу, теме, автору, ключевому слову (с заданием интересующего периода);

 составление списков должников по годам.

6. БД «Больница».

Задача – информационная поддержка деятельности регистратуры больницы. БД должна осуществлять:

 учёт поступления пациентов (по отделениям);

 учёт проведённого лечения;

 учёт платных услуг с выдачей счетов на оплату;

 ведение архива выписанных пациентов.

Необходимо предусмотреть определение (по отделениям):

 пропускной способности больницы;

 среднего времени пребывания больных в стационаре;

 наличия свободных мест в палатах (отдельно для мужчин и для женщин);

 количества прооперированных пациентов (из них – с осложнениями и умерших);

 смертности.

7. БД «Магазин» (выбрать конкретный профиль).

Задача – информационная поддержка деятельности магазина выбранного профиля. БД должна осуществлять:

 учёт поставщиков и поставок;

 учёт продаж по отделам;

 подсчёт остатков товаров (по отделам);

 оформление заказов на товары, запасы которых подходят к концу;

 подведение финансовых итогов дня (по отделам и в целом по магазину);

 анализ результативности работы продавцов (для премирования);

 анализ объёмов продаж по дням недели и по месяцам.

8. БД «Поликлиника».

Задача – информационная поддержка деятельности поликлиники. БД должна осуществлять:

 ведение медицинских карт пациентов;

 учёт рецептов, направлений на анализы, процедур;

 учёт платных услуг с выдачей счёта на оплату;

 ведение очередей на приём к специалистам по направлениям лечащих врачей.

Необходимо предусмотреть:

(16)

 определение посещаемости отдельных кабинетов (нагрузки врачей);

 подсчёт количества заболевших за день для определения наступления эпидемии.

9. БД адвоката.

Задача – информационная поддержка деятельности адвокатской конторы. БД должна осуществлять:

 ведение списка адвокатов;

 ведение списка клиентов;

 ведение архива законченных дел.

 получение списка текущих клиентов для конкретного адвоката;

 определение эффективности защиты (максимальный срок минус полученный срок) с учётом оправданий, условных сроков и штрафов;

 определение неэффективности защиты (полученный срок минус минимальный срок);

 подсчёт суммы гонораров (по отдельных делам) в текущем году;

 получение для конкретного адвоката списка текущих клиентов, которых он защищал ранее (из архива, с указанием полученных сроков и статей).

10. БД по недвижимости.

Задача – информационная поддержка деятельности фирмы, занимающейся продажей и арендой жилых и нежилых помещений. БД должна:

 осуществлять ведение списков жилых и нежилых помещений, предназначенных для аренды и/или продажи;

 поддерживать архив проданных и сданных в аренду помещений;

 производить поиск вариантов в соответствии с требованиями клиента.

Необходимо предусмотреть получение разнообразной статистики:

 наличие помещений разных типов;

 изменение цен на рынке;

 уровни спроса и предложения;

 средние показатели (среднее время нахождения помещения в БД (по типам помещений), среднюю стоимость аренды/продажи помещений и т.п.

11. БД «Гостиница».

Задача – информационная поддержка деятельности гостиницы.

 ведение списка постояльцев;

 учёт забронированных мест;

 ведение архива выбывших постояльцев за последний год.

 получение списка свободных номеров (по количеству мест и классу);

 получение списка номеров (мест), освобождающихся сегодня и завтра;

(17)

 выдачу информации по конкретному номеру;

 автоматизацию выдачи счетов на оплату номера и услуг;

 получение списка забронированных номеров;

 проверку наличия брони по имени клиента и/или названию организации.

12. БД «Продажа билетов».

Задача – информационная поддержка деятельности транспортных касс (выбрать вид транспорта). БД должна осуществлять:

 ведение списка рейсов и билетов на них с указанием класса;

 учёт забронированных мест;

 ведение архива пассажиров за последний месяц.

 продажу билетов в оба конца;

 поиск места на рейс в соответствии с требованиями заказчика;

 получение списка свободных мест на рейс;

 выдачу информации по конкретному рейсу;

 получение списка проданных мест;

 проверку наличия брони по имени клиента и/или названию организации.

13. БД «Спортивный клуб».

Задача – информационная поддержка деятельности спортивного клуба.

 ведение списков спортсменов и тренеров;

 учёт проводимых соревнований (с ведением их архива);

 учёт травм, полученных спортсменами.

 возможность перехода спортсмена от одного тренера к другому;

 составление рейтингов спортсменов;

 составление рейтингов тренеров;

 выдачу информации по соревнованиям;

 выдачу информации по конкретному спортсмену;

 подбор возможных кандидатур на участие в соревнованиях (соответствующего уровня мастерства, возраста и без травм).



5 Пример проектирования реляционной базы данных

В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью.

Инфологическое проектирование.

1. Анализ предметной области.

База данных создаётся для информационного обслуживания редакторов, менеджеров и других сотрудников компании. БД должна содержать данные о

(18)

сотрудниках компании, книгах, авторах, финансовом состоянии компании и предоставлять возможность получать разнообразные отчёты.

В соответствии с предметной областью система строится с учётом следующих особенностей:

 каждая книга издаётся в рамках контракта;

 книга может быть написана несколькими авторами;

 контракт подписывается одним менеджером и всеми авторами книги;

 каждый автор может написать несколько книг (по разным контрактам);

 порядок, в котором авторы указаны на обложке, влияет на размер гонорара;

 если сотрудник является редактором, то он может работать одновременно над несколькими книгами;

 у каждой книги может быть несколько редакторов, один из них – ответственный редактор;

 каждый заказ оформляется на одного заказчика;

 в заказе на покупку может быть перечислено несколько книг.

Выделим базовые сущности этой предметной области:

1) Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах.

2) Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах.

3) Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар.

4) Контрактыбудем рассматривать как связь между авторами, книгами и менеджерами. Атрибуты контракта – номер, дата подписания и участники.

5) Для отражения финансового положения компании в системе нужно учитыватьзаказына книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров.

ER–диаграмма издательской компании приведена на рисунке 17 (базовые сущности на рисунках выделены полужирным шрифтом).

(19)

Рисунок 17 - ER–диаграмма издательской компании

2. Анализ информационных задач и круга пользователей системы.

Система создаётся для обслуживания следующих групп пользователей:

 администрация (дирекция);

 менеджеры;

 редакторы;

 сотрудники компании, обслуживающие заказы.

Определим границы информационной поддержки пользователей:

1) Функциональные возможности:

 ведение БД (запись, чтение, модификация, удаление в архив);

 обеспечение логической непротиворечивости БД;

 обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа);

 реализация наиболее часто встречающихся запросов в готовом виде;

 предоставление возможности сформировать произвольный запрос на языке манипулирования данными.

2) Готовые запросы:

 получение списка всех текущих проектов (книг, находящихся в печати и в продаже);

 получение списка редакторов, работающих над книгами;

 получение полной информации о книге (проекте);

 получение сведений о конкретном авторе (с перечнем всех книг);

 получение информации о продажах (по одному или по всем проектам);

(20)

 определение общей прибыли от продаж по текущим проектам;

 определение размера гонорара автора по конкретному проекту.

3. Выбор СУБД и других программных средств.

Анализ информационных задач показывает, что для реализации требуемых функций подходят почти все СУБД для ПЭВМ (FoxPro, Clipper, MS Access и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными.

Объём внешней и оперативной памяти, требующийся для функционирования СУБД, обычно указывается в сопроводительной документации.

Для того чтобы в учебном примере не привязываться к конкретной СУБД, выполним описание логической схемы БД на SQL-92.

Логическое проектирование реляционной БД.

1. Преобразование ER–диаграммы в схему базы данных.

База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER–

диаграмму, содержащая атрибуты сущностей (рисунке 18).

(21)

Рисунок 18- ER–диаграмма издательской компании

Многозначные атрибуты на рисунке выделены подчеркиванием.

Преобразование ER–диаграммы в схему БД выполняется путем сопоставления каждой сущности и каждой связи, имеющей атрибуты, отношения (таблицы БД). Будем использовать обозначения, представленные на рисунке 19.

(22)

Рисунок 19 - Обозначения, используемые на схеме базы данных

Полученная схема реляционной базы данных (РБД) приведена на рисунке 20.

Рисунок 20- Схема РБД, полученная из ER–диаграммы издательской компании

На схеме (рисунок 20) есть связь типа 1:1 – обязательная связь между КНИГАМИ и КОНТРАКТАМИ. Такие отношения следует объединять в одно. Дополнительный эффект от объединения этих отношений – слияние связей «авторы–контракты» и «авторы–книги»: ведь в нашем случае контракт заключается именно для написания книги.

Исключение для связи типа 1:1 составляют ситуации, когда для увеличения производительности системы в отдельную таблицу выделяются редко используемые данные большого объёма.

(23)

Связь типа 1:М (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь (КНИГИ).

Связь редактировать между отношениями КНИГИ и СОТРУДНИКИ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение, которое является соединением первичных ключей соответствующих отношений.

Бинарная связь между отношениями не может быть обязательной для обоих отношений. После объединения сущностей КНИГИ и КОНТРАКТЫ остаётся три связи, обязательные для всех участников: между авторами и книгами и между заказами и строками заказов. Такой тип связи означает, что, например, прежде чем добавить новый заказ в отношение ЗАКАЗЫ, нужно добавить новую строку в отношение СТРОКИ ЗАКАЗА, и наоборот. Поэтому для такой связи необходимо снять с одной стороны условие обязательности. Так как все эти связи будут реализованы с помощью внешнего ключа, снимем условие обязательности связей для отношений, содержащих первичные ключи.

Уточнённая схема РБД издательской компании приведена на рисунке 21.

Рисунок 21- Уточнённая схема РБД издательской компании

Схема на рисунке 21 содержит цикл «сотрудники–книги–сотрудники».

Цикл допустим только в том случае, если связи, входящие в него, независимы друг от друга. Примем для нашей ПО, что ответственный редактор книги может являться также просто редактором этой же книги или не входить в

(24)

число редакторов. При этом цикл не приводит к нарушению логической целостности данных.

Рисунок 22 - Некоторые способы разрешения циклов в схеме базы данных

2. Составление реляционных отношений.

Каждое реляционное отношение соответствует одной сущности (объекту ПО), и в него вносятся все атрибуты сущности. Для каждого отношения необходимо определить первичный ключ и внешние ключи (если они есть). В том случае, если базовое отношение не имеет потенциальных ключей, вводится суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только для идентификации записей.

Потенциальными ключами вспомогательных отношений являются комбинации первичных ключей соответствующих базовых отношений.

(25)

Отношения приведены в таблицах 1-7. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный, D – дата (последний имеет стандартную длину, зависящую от СУБД, поэтому она не указывается).

Таблица 1- Схема отношения Сотрудники (Employees)

Содержание поля Имя поля Тип, длина Примечания

Табельный номер E_ID int первичный ключ

Фамилия, имя, отчество

E_NAME Char(50) обязательное

поле Дата рождения E_BORN Datetime

Пол E_SEX char (1) обязательное

поле Паспортные

данные

E_PASSP char(50) обязательное

поле

ИНН E_INN int обязательное

уникальное поле

Должность E_POST char(30) обязательное

поле

Оклад E_SALARY money обязательное

поле

Адрес E_ADDR Char(50)

Телефоны E_TEL Char(30) многозначное

поле

Таблица 2- Схема отношения Books

Содержание поля Имя поля Тип, длина Примечания Номер контракта B_CONTRACT int первичный ключ Дата подписания

контракта

B_DATE Datetime обязательное

поле

Менеджер B_MAN int внешний ключ (к

Employees) Название книги B_TITLE Char(40) обязательное

поле

Цена B_PRICE money цена экземпляра

книги

(26)

Затраты B_ADVANCE money общая сумма затрат на книгу Авторский

гонорар

B_FEE money общая сумма

гонорара

Дата выхода B_PUBL Datetime

Тираж B_CIRCUL int

Ответственный редактор

B_EDIT char(4) внешний ключ (к

Employees)

Таблица 3- Схема отношения АВТОРЫ (Authors)

Код автора A_ID int суррогатный

первичный ключ Фамилия, имя,

отчество

A_NAME Char(50) обязательное

поле Паспортные

данные

A_PASSP Char(50) обязательное

поле

ИНН A_INN int уникальное поле

Адрес A_ADDR Char(50) обязательное

поле

Телефоны A_TEL Char(30) многозначное

поле

Таблица 4- Схема отношения ЗАКАЗЫ (Orders)

Номер заказа O_ID int первичный ключ

Заказчик O_COMPANY Сhar(40) обязательное

поле Дата

поступления заказа

O_DATE Datetime обязательное

поле

Адрес заказчика O_ADDR Char(50) обязательное поле

Дата выполнения заказа

O_READY Datetime

(27)

Таблица 5- Схема отношения КНИГИ–АВТОРЫ (Titles)

Содержание поля Имя поля Тип, длина Примечания Код книги (№

контракта)

B_ID int внешний ключ (к

Books)

Код автора A_ID int внешний ключ (к

Authors)

Номер в списке A_NO int обязательное

поле

Гонорар A_FEE money процент от

общего гонорара

Таблица 6- Схема отношения КНИГИ–РЕДАКТОРЫ (Editors) Содержание поля Имя поля Тип, длина Примечания Код книги (№

контракта)

Books)

Код редактора E_ID int внешний ключ (к

Employees)

Таблица 7- Схема отношения СТРОКИ ЗАКАЗА (Items)

Номер заказа O_ID int внешний ключ (к

Orders) Код книги (№

контракта)

Books)

Количество B_COUNT int обязательное

поле

3. Нормализация полученных отношений (до 4НФ).

1НФ.

(28)

Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения.

В реальных БД сложные атрибуты разбиваются на простые, если:

а) этого требует внешнее представление данных;

б) в запросах поиск может осуществляться по отдельной части атрибута.

Разделим атрибуты Фамилия, имя, отчество на два атрибута Фамилия и Имя, Отчество и Паспортные данные на атрибуты Номер паспорта (уникальный), Дата выдачи и Кем выдан.

Многозначный атрибут Телефоны для сотрудников компании следует сначала разделить на два – Домашние телефоны и Рабочие телефоны. (Для авторов мы не будем различать домашние и рабочие телефоны). Затем нужно создать отдельные отношения с (нерабочими) телефонами для сотрудников (ТЕЛЕФОНЫ СОТРУДНИКОВ) и для авторов (ТЕЛЕФОНЫ АВТОРОВ).

Атрибут Рабочие телефоны отношения СОТРУДНИКИ имеет не- однородные значения. Один из номеров телефонов – основной – определяется рабочим местом сотрудника (рассматриваются только стационарные телефоны). Наличие других номеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники, имеющие стационарные телефоны.

Можно добавить в отношение СОТРУДНИКИ атрибут Номер комнаты, а в атрибуте Рабочие телефоны хранить номер того телефона, который стоит на рабочем месте сотрудника. Дополнительные номера телефонов можно будет вычислить из других кортежей с таким же номером комнаты. Но в случае увольнения сотрудника мы потеряем сведения о номере рабочего телефона.

Поэтому создадим новое отношение КОМНАТЫ и включим в него атрибуты Номер комнаты и Телефон. Так как в комнате может не быть телефона, первичный ключ нового отношения не определен (ПК не может содержать null–значения), но на этих атрибутах можно определить составной уникальный ключ. Связь между отношениями СОТРУДНИКИ и КОМНАТЫ реализуем через составной внешний ключ (Номер комнаты, Телефон).

Значение внешнего ключа для каждого сотрудника будем брать из того кортежа, в котором хранится основной рабочий телефон этого сотрудника.

(29)

2НФ. В нашем случае составные первичные ключи имеют отношения СТРОКИ ЗАКАЗА, КНИГИ–АВТОРЫ и КНИГИ–РЕДАКТОРЫ.

Неключевые атрибуты этих отношений функционально полно зависят от первичных ключей.

3НФ. В отношении ЗАКАЗЫ атрибут Адрес заказчика зависит от атрибута Заказчик, а не от первичного ключа, поэтому адрес следует вынести в отдельное отношение ЗАКАЗЧИКИ. Но при этом первичным ключом нового отношения станет атрибут Заказчик, т.е. длинная символьная строка.

Целесообразнее перенести в новое отношение атрибуты Заказчик и Адрес заказчика и ввести для него суррогатный ПК. Так как каждый заказчик

может сделать несколько заказов, связь между

отношениями ЗАКАЗЧИКИ и ЗАКАЗЫ будет 1:n и суррогатный ПК станет внешним ключом для отношения ЗАКАЗЫ.

В отношенииСОТРУДНИКИатрибутОкладзависит от атрибута Должность. Поступим с этой транзитивной зависимостью так же, как в предыдущем случае: создадим новое отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад и введём суррогатный первичный ключ.

В отношениях СОТРУДНИКИ и АВТОРЫ атрибуты Дата выдачи и Кем выдан зависят от атрибута Номер паспорта, а не от первичного ключа. Но если мы выделим их в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна.

4НФ. Отношения данного примера не нарушают 4НФ, т.к. не содержат нетривиальных многозначных зависимостей.

В реальных базах данных после нормализации может проводиться денормализация. Она проводится с одной целью – повышение производительности БД. Рассмотрим некоторые запросы к нашей базе данных.

Например, запрос на получение списка телефонов авторов или домашних телефонов сотрудников потребует в нормализованной БД соединения отношений. Пользователю безразлична форма представления этого списка: номера телефонов через запятую или в столбец. Поэтому мы откажемся от создания отдельных отношений с номерами телефонов, и вернёмся к варианту с многозначными полями (это не касается рабочих телефонов сотрудников).

Другой запрос: как определяется, можно ли выполнить очередной заказ?

Для каждой позиции заказа нужно просуммировать количество книг по

(30)

выполненным заказам, получить остаток (тираж минус полученная сумма) и сравнить остаток с объёмом заказа. Такой расчёт может потребовать много времени, поэтому предлагается добавить в отношение КНИГИ производный атрибут Остаток тиража. Значение этого атрибута должно автоматически пересчитываться при установлении даты выполнения заказа.

После проведённых преобразований схема БД выглядит так (рисунок 23):

Рисунок 23- Окончательная схема РБД издательской компании

Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности приведены в таблицах 8–17.

Таблица 8- Схема отношения ДОЛЖНОСТИ (Posts)

Код должности P_ID Numeric(3) Суррогатный

первичный ключ Название

должности

P_POST varchar(30) Обязательное поле

Оклад P_SAL Numeric (8,2) Обязательное

поле

Таблица 9- Схема отношения КОМНАТЫ (Rooms)

(31)

Номер комнаты R_NO Numeric(3) Первичный ключ

Телефон R_TEL varchar(10) Обязательное

поле

Таблица 10- Схема отношения СОТРУДНИКИ (Employees)

Табельный номер E_ID int Первичный ключ

Фамилия E_FNAME Char(20) Обязательное

поле

Имя, отчество E_LNAME Сhar(30) Обязательное поле

Дата рождения E_BORN Datetime Дата

Пол E_SEX Char(1) Обязательное

поле

Код должности E_POST int Внешний ключ (к

Posts)

Номер комнаты E_ROOM int Составной

внешний ключ Номер телефона E_TEL Char(10)

ИНН E_INN Сhar(12) Обязательное

поле

Номер паспорта E_PASSP Char(12) Обязательное поле

Кем выдан паспорт

E_ORG Сhar(30) Обязательное

поле Дата выдачи

паспорта

E_PDATE Datetime Обязательное

поле

Адрес E_ADDR Char(50)

Таблица 11- Схема отношения ЗАКАЗЧИКИ (Customers)

Код заказчика C_ID int Суррогатный

первичный ключ

Заказчик C_NAME Char(30) Обязательное

поле

(32)

Адрес заказчика C_ADDR Char(50) Обязательное поле

Таблица 12- Схема отношения АВТОРЫ (Authors)

Код автора A_ID int Суррогатный ключ

Фамилия A_FNAME Char(20) Обязательное поле

Имя, отчество A_LNAME Сhar(30) Обязательное поле

ИНН A_INN Сhar(12)

Номер паспорта A_PASSP Char(12) Обязательное поле Кем выдан

паспорт

A_ORG Сhar(30) Обязательное поле Дата выдачи

паспорта

A_PDATE Datetime Обязательное поле

Адрес A_ADDR Char(50) Обязательное поле

Телефоны A_TEL Char(30) Многозначное поле

Таблица 13- Схема отношения КНИГИ (Books) Содержание

поля

Имя поля Тип, длина Примечания Номер

контракта

B_CONTRACT int Первичный ключ

Дата

подписания контракта

B_DATE Datetime Обязательное поле

Менеджер B_MAN int Внешний ключ (к

Employees)

Название книги B_TITLE Char(40) Обязательное поле

Цена B_PRICE money Цена экземпляра книги

Затраты B_ADVANCE money общая сумма затрат на книгу

Авторский гонорар

B_FEE money Общая сумма гонорара

Дата выхода B_PUBL Datetime дата

Тираж B_CIRCUL int

Ответственный редактор

B_EDIT int Внешний ключ (к

Employees)