Реляционная модель данных

ПРОЕКТИРОВАНИЕ

РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ

Методические указания к лабораторной работе

по курсу «Базы данных»

для студентов направления 230100

КУРСК 2008

УДК 004.652

Составитель: Е.Ю. Емельянова

Рецензент

Кандидат технических наук И.Н. Глухарев

Проектирование реляционной базы данных [Текст]: методические указания к лабораторной работе по курсу «Базы данных» для студентов направления 230101/ Курск. гос. техн. ун-т; сост.: Е.Ю. Емельянова. Курск, 2008. 62 с.: ил. 11, табл.1. Библиогр.: с.62.

Описываются этапы проектирования реляционной базы данных: моделирование «сущность-связь», переход к реляционной модели, нормализация. Поэтапное проектирование базы данных разобрано на примере. Приводятся SQL-операторы создания базы данных, таблиц и ограничений целостности в СУБД MySQL 3.23, Firebird 1.5, Microsoft SQL Server 2000.

Для студентов направления 230100.

.

Текст печатается в авторской редакции.

Подписано в печать. Формат 60´84 1/16.

Усл.печ.л.. Уч.-изд.л.. Тираж 40 экз. Заказ. Бесплатно.

Курский государственный технический университет.

Издательско-полиграфический центр Курского государственного технического университета. 305040 Курск, ул. 50 лет Октября, 94.

СОДЕРЖАНИЕ

1. Цель работы …………………………………………………………… 4

Теоретическая часть:

2. Общие положения ……..……………………………………………… 4

2.1. Понятия и определения……………..…………………………… 4

2.2. Этапы проектирования баз данных ……………………………. 5

3. Модель «сущность-связь»…………………………………………..… 6

3.1. Основные понятия модели «сущность-связь»…..……………… 6

3.2. Пример проектирования модели "сущность-связь" …………… 13

4. Реляционная модель данных………………………………………..… 16

4.1. Основные понятия реляционной модели …..…………………… 16

4.2. Ограничения целостности ……………………………..………… 19

4.3. Правила преобразования ER-модели в реляционную.………… 21

5. Нормализация………………………………………………………..… 24

5.1. Аномалии модификации …..…………………………………….. 24

5.2. Нормальные формы ……………………………………………… 26

5.3. Процедура нормализации и денормализации ………………….. 34

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

6. Реализация баз данных……………………………………………..…. 41

6.1. Стандартные типы данных …..…………………………………... 41

6.2. Домены и пользовательские типы данных……………………… 41

6.3. Создание баз данных и таблиц на SQL…………………………. 47

6.4. Создание автоинкрементных столбцов…………………………. 55

Содержательная часть:

7. Порядок выполнения работы…………………………………………60

8. Содержание отчета…………………………………………………… 60

9. Контрольные вопросы……………………………………………….. 61

Библиографический список………………………………………………62

ЦЕЛЬ РАБОТЫ

Научиться проектировать реляционные базы данных; изучить операторы языка SQL для создания баз данных и таблиц; создать таблицы учебной базы данных под управлением одной из стандартных реляционных СУБД.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

ОБЩИЕ ПОЛОЖЕНИЯ

2.1. Понятия и определения

База данных (database) – электронное хранилище информации. Самые распространенные сейчас – реляционные базы данных – хранят данные в таблицах. Физически база данных представляет собой один или несколько файлов специального формата.

СУБД, Система Управления Базами Данных (DBMS – database management system) – комплекс программ, предназначенный для создания и сопровождения баз данных. СУБД способна одновременно управлять несколькими базами данных, с которыми в одно и то же время, параллельно, работают многие пользователи. Обычно СУБД включает в себя следующие программы:

· ядро СУБД – программа или служба, которая постоянно «висит» в памяти и занимается обслуживанием поступивших от пользователей запросов на обработку данных (найти данные по критерию поиска, вставить, удалить или изменить строки таблицы и т.д.);

· программы для создания и сопровождения баз данных – интерактивные программы, где можно просмотреть содержимое баз данных, находящихся под управлением данной СУБД, отредактировать его, зарегистрировать новых пользователей базы данных и разрешить им работать с определенными таблицами, создавать и удалять базы данных и др.

· мониторы производительности – программы, с помощью которых можно просмотреть в реальном времени загрузку СУБД, статистику наиболее часто выполняющихся запросов, выявить запросы, требующие большого времени на выполнение и их оптимизировать.

Приложение базы данных (database application) – это программа, с помощью которой пользователи работают с базой данных. Приложение обычно пишется на языках высокого уровня (Java, Delphi, С++) или с применением Web-технологий (HTML+язык web-сценариев: PHP, Python, Java Script, Perl). Приложение направляет SQL-запросы к СУБД, в ответ получает данные и отображает их в удобном для пользователя виде.

2.2. Этапы проектирования баз данных

Методика нисходящего проектирования баз данных предполагает создание базы данных за пять этапов:

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

2. объектное моделирование [1] – частичная формализация предметной области. Из словесного описания выделяются объекты, информация о которых будет храниться в базе данных, и логические связи между ними в терминах "реального мира".

3. выбор СУБД – каждая СУБД хранит данные в форме одной из из даталогических моделей. Самой распространенной моделью сейчас является реляционная модель данных. Кроме того, каждая СУБД имеет специфические особенности реализации модели данных, которые необходимо учитывать на последующих этапах проектирования.

4. даталогическое проектирование. Если речь идет о реляционной базе данных, на этом этапе определяется структура таблиц, ограничений целостности и способы их поддержания средствами выбранной СУБД.

5. реализация. Создание базы данных на ЭВМ.

3. МОДЕЛЬ "СУЩНОСТЬ-СВЯЗЬ"

3.1. Основные понятия модели «сущность-связь»

Для второго этапа проектирования баз данных – объектного моделирования – наибольшее распространение получила модель "сущность-связь" (Entity-Relationship)[2], или ER-модель.

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

Сущность – объект, информация о котором сохраняется в базе данных. Этот объект имеет свойства – атрибуты сущности. Сущностью может быть сотрудник, заказ, товар и т.п. По аналогии с ООП, следует различать класс сущности и экземпляр сущности. Класс сущности моделирует набор однотипных объектов. Экземпляр сущности представляет конкретный физический объект. Каждому классу сущности дается уникальное в пределах ER-модели имя. Имя класса принято записывать заглавными буквами. Например, в базе данных библиотеки класс сущности ЧИТАТЕЛЬ может быть представлен множеством экземпляров, где каждый экземпляр – это пользователь библиотеки (Иванов, Петров, Сидоров и пр.).

Связи моделируют смысловые ассоциации между сущностями. Различают класс связи и экземпляр связи. Каждому классу связи дается уникальное имя. Например, связь АБОНЕМЕНТ между сущностями ЧИТАТЕЛЬ и КНИГА (рис.1) означает, что книга в настоящее время находится на руках у читателя. Сущности, включенные в данную связь, называются участниками связи, а количество участников связи называется её степенью. В данном примере связь АБОНЕМЕНТ имеет степень «2».

Рис. 1. Сущности и связи изображаются в виде диаграммы "сущность-связь" (ER-диаграммы). Сущности обозначаются прямоугольниками, связи – линиями. Внутри ромба пишется максимальная кардинальность связи.

Связи второй степени (их еще называют бинарными связями) являются самыми распространенными. Известно три типа бинарных связей: 1:1 ("один-к-одному"); 1:М ("один-ко-многим") или обратная ей М:1 ("многие-к-одному"); и M:N ("многие-ко-многим")[3]. Тип связи устанавливается по количеству связанных между собой экземпляров сущностей.

На рис.2 приведен пример детализации связи АБОНЕМЕНТ. Эта связь имеет тип 1:М, поскольку каждый читатель может взять несколько книг, но экземпляр книги может быть записан только за одним человеком.

Рис.2. Детализация ER-диаграммы

Максимальное количество экземпляров сущностей, которые могут участвовать в связи, называется максимальным кардинальным числом связи. Со стороны сущности ЧИТАТЕЛЬ максимальное кардинальное число равно 1, а со стороны сущности КНИГА определяется максимальным количеством книг, которые читатель может одновременно держать на руках. Так, в Курской областной библиотеке имени Асеева – это 5 книг, в библиотеке КГТУ – М (не ограничено).

Аналогично определяется минимальное кардинальное число связи – это минимальное количество экземпляров сущностей, объединенных одним экземпляром связи. Для сущностей ЧИТАТЕЛЬ и КНИГА минимальные кардинальные числа равны 0, потому что читатель может не брать ни одной книги, а книга может находиться в библиотеке, не будучи взятой ни одним читателем. Связь с минимальным кардинальным числом 0 называется необязательной (по отношению к данной сущности), связь с кардинальным числом ³1 является обязательной (тоже по отношению к данной сущности). На ER-диаграмме минимальные кардинальные числа пишутся над линиями связи (рис.3,а) или обозначаются: вертикальной чертой – обязательная связь, овалом – необязательная (рис.3,б).

(а)

(б)

Рис. 3. Различные способы изображения кардинальных чисел. Связь БИБЛИОТЕЧНАЯ_КАРТОЧКА показывает, в каких разделах систематического каталога есть ссылка на книгу. Связь имеет тип M:N, поскольку одну книгу может быть несколько ссылок из разных разделов каталога. В то же время один раздел каталога содержит ссылки на несколько разных книг. Раздел каталога может быть пустым (с его стороны связь необязательная), а книга должна где-то упоминаться (со стороны книги связь обязательная).

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

Объекты реального мира, описываемые сущностями и связями, могут иметь некоторые свойства, или характеристики, которые требуется хранить в БД. В ER-модели подобные свойства моделируются атрибутами. Атрибуты могут быть как у сущностей, так и у связей. Например, класс сущности ЧИТАТЕЛЬ может иметь атрибуты НомерЧитательскогоБилета, ФИО, ДатаРождения, ДомашнийАдрес, Телефон; а класс связи. АБОНЕМЕНТ (рис.1) – атрибут СрокВозврата.

Различают следующие виды атрибутов:

Простой или составной (композитный) атрибут. Составной атрибут состоит из набора простых атрибутов. Например, НомерЧитательскогоБилета – это простой атрибут; ДомашнийАдрес – составной, он включает простые атрибуты Город, Улица, Дом, Квартира.

Однозначный или многозначный атрибут. Многозначный атрибут – это список (массив) однотипных значений. Многозначным атрибутом может быть атрибут СписокКлючевыхСлов сущности КНИГА.

Базовый или производный (вычислимый) атрибут. Базовый атрибут хранит (в памяти) присвоенное ему значение, производный – вычисляется по формуле из других атрибутов (в памяти не хранится). Например, ДатаРождения читателя – это базовый атрибут, а ВозрастЧитателя – вычислимый, он может изменяться со временем, а базовый остается неизменным.

Обязательный или необязательный (отсутствующий) атрибут. Обязательный атрибут должен быть заполнен у каждого экземпляра сущности. Необязательный атрибут может иметь "пустое" значение (NULL), например, Телефон читателя.

Ключевой атрибут (идентификатор) – атрибут, который может использоваться для различения (идентификации) экземпляров сущностей. У каждого экземпляра сущности своё неповторимое значение идентификатора. У сущности ЧИТАТЕЛЬ идентификатором является НомерЧитательскогоБилета Идентификатор может быть составным (например, { СерияПаспорта, НомерПаспорта}). Ключевые атрибуты – всегда обязательные (NOT NULL).

Изображение атрибутов на ER-диаграмме показано на рис.4.

Рис. 4. Атрибуты сущности перечисляются под заголовком, отделённые чертой. Ключевые атрибуты подчеркиваются сплошной линией, вычислимые – пунктиром. Атрибуты связи изображаются в прямоугольнике под связью.

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

Ключевой атрибут сущности ТРЕБОВАНИЕ_НА_КНИГУ составной, он включает идентификатор сущности КНИГА – поле ИнвентарныйНомерКниги. Слабая сущность, зависящая от сильной по ключевому полю, как в данном примере, называется идентификационно-зависимой, а связь – идентификационной (identifying relationship).

На вопрос о том, являются ли две сущности одинаково «сильными» или образуют пару «сильная-слабая», может быть ответ, теряет ли первая сущность свой логический смысл, если из модели исключить сущность другого класса. Если да, то сущности зависимы.

Рис. 5. Слабая сущность ТРЕБОВАНИЕ_НА_КНИГУ обозначается прямоугольником со скругленными углами. На линии связи между сильной и слабой сущностью, рядом со слабой сущностью ставится ромб.

По аналогии с наследованием в объектно-ориентированном программировании, в ER-модели допускается иерархия классов сущностей. Базовый класс сущностей называется супертипом (supertype) или надтипом. У супертипа может быть любое количество дочерних классов – подтипов (subtype). Подтип наследует все атрибуты и связи базового класса и при необходимости добавляет к ним свои атрибуты и связи. Например, от класса сущности КНИГА можно создать подтип МНОГОТОМНОЕ_ ИЗДАНИЕ (рис.6). Многотомные издания наследуют все атрибуты базового класса КНИГА, но в тоже время появляются новые свойства, не применимые к обычным, однотомным, книгам: количество томов, номер тома, название тома, количество страниц в томе и др. Аналогично можно создать еще один подтип сущности КНИГА – класс УЧЕБНИК с новыми атрибутами: специальность и название предмета (рис.6).

Подтипы МНОГОТОМНОЕ_ИЗДАНИЕ и УЧЕБНИК являются не взаимоисключающими (not exclusive). То есть могут существовать такие книги, которые одновременно являются и учебниками, и многотомными изданиями. В отличие от принципов ООП, где экземпляр объекта принадлежит ровно одному классу, в модели "сущность-связь" экземпляр сущности может одновременно принадлежать нескольким подтипам одного супертипа. Максимальное число этих подтипов записывается рядом с дугой (m на рис.6). Для нашего примера это число ограничено только числом подтипов.

Рис. 6. Иерархия типов сущностей. Значок Î показывает, что подтипы наследуют все атрибуты и связи супертипа. Пустые овалы на связях со стороны класса КНИГА означают, что подтипы не являются обязательными: то есть существуют книги, которые нельзя отнести ни к многотомным изданиям, ни к учебникам, а только к надтипу КНИГА.

Существуют также взаимоисключающие (exclusive) подтипы, когда экземпляр сущности может принадлежать только одному из подтипов. Поясним на примере. Читателями вузовской библиотеки могут быть студенты, аспиранты и преподаватели. Каждой категории читателей соответствует подтип, различающийся набором атрибутов (рис.7). Подтипы СТУДЕНТ, АСПИРАНТ, ПРЕПОДАВАТЕЛЬ являются взаимоисключающими, поскольку читатель библиотеки выступает только в одной из ролей.

Рис.7. Иерархия взаимоисключающих подтипов (дуга помечается цифрой 1). Поперечные черточки на связях со стороны сущности ЧИТАТЕЛЬ означают, что подтипы обязательные, то есть каждый читатель должен быть либо студентом, либо аспирантом, либо преподавателем, а экземпляров базового класса ЧИТАТЕЛЬ не существует.

3.2. Пример проектирования модели "сущность-связь"

Пример 1

Описание предметной области: транспортное предприятие выполняет грузовые перевозки по заявкам организаций и населения. Каждый автомобиль обслуживают два водителя, работающие посменно. Расписание работы каждого водителя хранится в базе данных. Грузоперевозки выполняются по предварительным заявкам. Заявка включает: марку автомобиля, пункт назначения, время начала и окончания работ, задание, информацию о заказчике. Если заказчиком является организация, хранится ее название, ИНН, юридический адрес, телефон. Если заказчик – частное лицо, то хранятся паспортные данные и контактный телефон. В базе учитывается, какой именно автомобиль фактически обслужил заявку.

Одно из возможных решений задачи показано на рис.8. Имеются сущности АВТОМОБИЛЬ и ВОДИТЕЛЬ, связанные связью с кардинальностью 1:2, поскольку один автомобиль обслуживают два водителя. Связь со стороны автомобиля и со стороны водителя обязательная, поскольку к каждому исправному автомобилю прикреплен хотя бы один водитель, и водители, сами по себе, без автомобилей, не предусмотрены.

Расписание работы каждого водителя отражает сущность РАСПИСАНИЕ, она является слабой по отношению к сущности ВОДИТЕЛЬ. Атрибуты ВремяНачалаСмены и ВремяОкончанияСмены определяют время работы соответствующего водителя.

Сущность ЗАКАЗЧИК имеет два обязательных взаимоисключающих подтипа: ОРГАНИЗАЦИЯ и ЧАСТНОЕ_ЛИЦО, которые отличаются наборами атрибутов. Идентификация сущностей этих подтипов происходит разными способами: у организаций ключевым атрибутом является ИНН, а у граждан – паспортные данные. Каждый заказчик может делать несколько заказов, но заявка принадлежит ровно одному заказчику, поэтому связь ЗАКАЗЧИКа с сущностью ЗАЯВКА имеет тип 1:М. Связь обязательная с обеих сторон, потому что заявка обязательно чья-то, а заказчики регистрируются в базе только после того, как они подали заявку.

Учет, была ли обслужена заявка или нет, и какой автомобиль обслужил данную заявку, отражает связь ОБСЛУЖИВАНИЕ_ ЗАЯВКИ. Она является необязательной с обеих сторон. Необязательность со стороны заявки показывает, что заявка может быть не обслужена вовсе. Необязательность связи со стороны автомобиля отражает случай, когда автомобиль (например, новый) еще не сделал ни одного выезда. Водитель, который обслужил данную заявку, определяется по времени своей работы в сущности РАСПИСАНИЕ.

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

Рис. 8. Диаграмма "сущность-связь" для примера 1.

РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ

Основные понятия реляционной модели

Модель «сущность-связь» не пригодна для машинного хранения данных. Большинство современных СУБД использует для внутреннего представления данных реляционную модель. В реляционной модели данные хранятся в форме таблиц. Столбцы таблицы описывают свойства (атрибуты) объекта, строка содержит описание экземпляра объекта.

Реляционная таблица называется отношением (relation). От «любой другой» таблицы отношение отличается тем, что: 1) ячейки содержат простые, неделимые (атомарные) значения, 2) порядок следования строк не важен, 3) не должно быть двух одинаковых строк. Первое положение показывает, что в отношении не допускаются объединения ячеек, и не допускается разбиения одной ячейки на несколько. Второе и третье положение говорят, что строки таблицы рассматриваются как элементы множества (set). Из математического понятия множества следует, что элементы (в данном случае строки) не могут повторяться и не имеют порядковых номеров. Столбцы таблицы-отношения называются атрибутами, строки – кортежами.

Для однозначной идентификации строк используется ключ. Ключ – это один или более столбцов отношения, значения ячеек которых уникальны у каждой строки. Если ключ состоит из одного столбца, он называется простым, если группой столбцов – составным. Ключевые столбцы выбираются при проектировании реляционной таблицы с таким расчетом, чтобы ключи сохраняли свойство уникальности независимо от количества строк в таблице.

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

Пример 2

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

Работники

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

Итак, в таблице Работники есть два нетривиальных ключа: Табельный номер и составной ключ (Серия паспорта, Номер паспорта). ■

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

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

Пример 3

Информация об отпусках работников организации сохраняется в таблице Отпуск:

Отпуск

Таблица Отпуск связана с таблицей Работники из примера 2 по столбцу ТабельныйНомер. Каждая строка таблицы Отпуск указывает на строку таблицы Работники, содержащую соответствующее значение табельного номера. Строки таблицы Отпуск, где ТабельныйНомер =406, относятся к работнику Полякову И.И.; строки ТабельныйНомер =409 – к работнику Котовой Н.П. ■

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

Ссылки между таблицами организуются путем добавления в ссылающуюся таблицу столбцов, в точности повторяющих формат первичного ключа базовой таблицы. Эти столбцы называются внешним ключом (Foreign Key, FK). Внешний ключ может содержать только реально существующие значения первичного ключа базовой таблицы. То есть не допускается ситуация, когда внешний ключ указывает на не существующую строку базовой таблицы (это называется ссылочной целостностью). Ссылочную целостность СУБД проверяют автоматически. При попытке ввода не действительного внешнего ключа СУБД генерируют ошибку.

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

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

Бывает, что все потенциальные ключи таблицы занимают довольно большой объем памяти. Чтобы не создавать такие же "тяжелые" внешние ключи, прибегают к использованию суррогатного ключа. Суррогатный ключ (ersatz key) – это новый, обычно числовой, столбец, добавляемый в целевую таблицу в качестве первичного ключа. Он используется только для нумерации строк, и никакого иного смысла не несёт. Суррогатный ключ обычно делают автоинкрементным[4] (auto increment), или выбирающим свои значения случайным образом из некоего диапазона. Для таблиц, которые впоследствии могут быть объединены в одну (например, в распределенных базах данных), суррогатный ключ можно заполнять 8-байтовыми значениями GUID (Global Universal ID), чтобы избежать совпадения ключей.