Построение реляционной модели данных

От модели предметной области очень легко перейти к реляционной модели данных. Для этого нужно выполнить несколько последовательных действий:

1. Построить схемы отношения для классов объектов.

a) Для каждого класса объектов сформировать отдельное отношение, атрибутами которого будут свойства этого класса. Например, для объекта Студент можно составить следующую схему отношения:

Студент(ФИО, Курс, Год_рождения, Номер).

b) Далее, для каждого атрибута определить домен и привести отношение к первой нормальной форме. В данном примере необходимо заменить атрибут ФИО тремя атрибутами Фамилия, Имя, Отчество.

c) Затем, нужно выделить в отношении ключ. В данном случае ключом будет атрибут Номер, так как номера зачетных книжек не дублируются.

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

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

a) Для связи схема отношения строится следующим образом: для каждого класса, участвующего в связи, из соответствующей ему схемы отношения ключевые атрибуты копируются в новое отношение; если связь имеет свойства, то они также включаются в схему как атрибуты.

b) Ключом такого отношения, скорее всего, будут либо ключ одного из классов, входящих в связь, либо оба ключа вместе (для бинарной связи). Иногда бывает удобно ввести искусственный ключ.

Например, связь между классами Студент и Группа выльется в следующее отношение:

Группы(Студент, Номер_группы),

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

Во втором примере для связи между классами Товар и Поставщик схема отношения будет иметь следующий вид:

Поставки(Товар, Поставщик, Дата, Количество).

Это связь вида «многие-ко-многим», следовательно, в ключ должны входить и атрибут Товар и атрибут Поставщик. Но один и тот же поставщик может поставлять один и тот же товар несколько раз, следовательно, двух атрибутов для ключа недостаточно. Если в предметной области определено такое ограничение, что в один и тот же день поставщик не может поставить один и тот же товар, то тогда ключом будут три атрибута {Товар, Поставщик, Дата}. Если же этого ограничения нет, то в этом случае даже всех атрибутов будет недостаточно для определения ключа и необходимо ввести искусственный ключ (например, Номер_поставки или т. п.).

c) Схемы отношений, соответствующие связям между классами, будут иметь внешние ключи – атрибуты, соответствующие ключам схем классов, входящих в связь. Например, в отношении Поставки атрибут Товар будет внешним ключом, ссылающимся на отношение Товар, а атрибут Поставщик будет внешним ключом, ссылающимся на отношение Поставщик.

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

3) При таком построении модели данных – из модели предметной области – отношения чаще всего будут сразу находиться в 3НФ. Исключением могут быть только составные атрибуты, нарушающие условия 1НФ, но, исправив их, вы получите 3НФ.

4) Конечно, схема может получиться неоптимальной; например, связь, относящуюся к типу «один-ко-многим», в некоторых случаях бывает удобным не оставлять как отдельное отношение, а включить в схему отношения для класса объекта, который входил в связь со стороны «многие». Однако при перестроении схемы будьте внимательны, могут быть нарушены условия одной из нормальных форм.

4. Физическая модель данных

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

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

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

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

Сначала словесно опишем предметную область для того, чтобы точнее определить классы объектов и отношения между ними.

Университет ведет прием на 14 факультетов по 30 специальностям. По каждой специальности определены размер приема и перечень вступительных экзаменов с указанием формы проведения (устно или письменно). Абитуриент подает заявление на один или несколько факультетов на определенные специальности и затем сдает соответствующие экзамены. Абитуриенты для проведения экзаменов разбиваются на группы по 25 человек. Все группы, относящиеся к одной специальности, сдают экзамен по предмету в один день. На экзамене абитуриент получает оценку: 2, 3, 4, 5, 6. По результатам экзаменов проводится зачисление абитуриентов на специальность.

Выделим классы объектов – Абитуриент, Факультет, Специальность, Экзамен – и определим свойства этих классов.

Класс Абитуриент имеет свойства: ФИО, дата рождения, паспортные данные (серия, номер, кем и когда выдан), наличие медали (нет, сереб., зол.).

Класс Экзамен имеет свойства: дата проведения экзамена, название предмета, форма экзамена (устно или письменно).

Класс Факультет имеет свойства: название факультета.

Класс Специальность имеет свойства: название специальности, количество мест.

Определим связи между классами объектов:

1. Абитуриент подает заявление на специальность. Это связь между классами Абитуриент и Специальность. Абитуриент может подать заявление на одну или несколько специальностей. На каждую специальность может быть подано несколько заявлений. Это означает, что связь имеет тип «многие-ко-многим». Абитуриент существует, только если он подал заявление на какую-либо специальность, существование же специальности не зависит от того, есть ли хотя бы одно заявление на эту специальность. Следовательно, связь является обязательной по отношению к классу Абитуриент и необязательной по отношению к классу Специальность. После подачи заявления абитуриент получает экзаменационный лист и приписывается к одной из групп, формируемых по этой специальности, для сдачи вступительных экзаменов. Поэтому связь будет иметь свойства: номер экзаменационного листа и номер группы.

2. Каждый факультет предлагает одну или несколько специальностей, разные факультеты не могут предлагать одинаковые специальности. Эта связь имеет тип «один-ко-многим». Не может быть специальности, не принадлежащей ни к одному факультету, так же как факультета без хотя бы одной специальности. Поэтому связь является обязательной по отношению к обоим классам.

3. По каждой специальности проводится несколько экзаменов. Даже если два экзамена проводятся по одному и тому же предмету в один и тот же день, но на разные специальности, экзамены считаются различными. Связь имеет тип «один-ко-многим». Экзамен существует, только если он проводится по какой-либо специальности. Однако возможна ситуация, когда прием ведется без экзаменов. Поэтому связь является обязательной по отношению к классу Экзамен и необязательной по отношению к классу Специальность.

4. Абитуриент сдает несколько экзаменов. Каждый экзамен сдают несколько абитуриентов. Связь имеет тип «многие-ко-многим». Абитуриент может не сдавать ни одного экзамена (зачислен без сдачи экзаменов). Экзамен может не сдавать ни один абитуриент (все уже зачислены по итогам предыдущих экзаменов). Поэтому связь является необязательной по отношению к обоим классам. При сдаче экзамена абитуриент получает оценку. Оценка будет свойством связи.

После построения концептуальной модели предметной области переходим к проектированию реляционной модели данных. Отношения будем строить по алгоритму, приведенному в разделе 3.5. Для каждого класса объектов и для каждой связи между классами сформируем отдельные отношения. Названия отношений будут соответствовать названиям классов и связей. Атрибутами отношений будут свойства классов и связей.

Первый класс объектов – Абитуриент. Дадим такое же название и отношению. Атрибутами отношения станут свойства этого класса:

Абитуриент(ФИО, Паспорт, Дата_рождения, Медаль).

Отношение не находится в 1НФ, так как атрибуты ФИО и Паспорт являются составными. Атрибут ФИО разобьем на три атрибута: Фамилия, Имя, Отчество. Атрибут Паспорт разобьем на два атрибута: Серия, Номер (информацию о том, кем и когда был выдан паспорт, хранить в базе данных не будем).

Ключом отношения будет совокупность атрибутов {Серия, Номер}, так как они являются уникальными для каждого человека.

Абитуриент(Фамилия, Имя, Отчество, Серия, Номер, Дата_рождения, Медаль).

Домены:

dom(Фамилия) = {строка символов длиной 25};

dom(Имя) = {строка символов длиной 25};

dom(Отчество) = {строка символов длиной 30};

dom(Серия) = {строка символов длиной 5};

dom(Номер) = {целое шестизначное число};

dom(Дата_рождения) = {дата};

dom(Медаль) = {множество {золотая, серебряная, нет}}.

Следующий класс – Факультет. Этот класс имеет только один атрибут – Название, поэтому отношение Факультет будет состоять из одного атрибута, который и будет ключом отношения:

Факультет(Название) .

Домены:

dom(Название) = {строка символов длиной 100}.

Для класса Специальность сформируем отношение Специальностьс двумя атрибутами:

Специальность(Название, Набор).

Специальности имеют неповторяющиеся названия, следовательно, атрибут Название можно сделать ключом отношения.

Специальность(Название, Набор).

Домены:

dom(Название) = {строка символов длиной 200};

dom(Набор) = {целое трехзначное число}.

Ключом отношения является атрибут Название. Домен этого атрибута представляет собой строку символов длиной 200. Атрибут с таким доменом лучше не использовать в качестве ключа, так как значения его слишком велики. В данной ситуации рациональнее будет ввести дополнительный атрибут Номер_специальности и сделать его ключом отношения (суррогатный ключ):

Специальность(Номер_специальности, Название, Набор).

Домены:

dom(Номер_специальности) = {целое число};

dom(Название) = {строка символов длиной 200};

dom(Набор) = {целое трехзначное число}.

Для класса Экзамен сформируем отношение, состоящее из трех атрибутов:

Экзамен(Предмет, Дата, Форма).

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

Экзамен(Номер, Предмет, Дата, Форма).

Домены:

dom(Номер) = {целое трехзначное число};

dom(Предмет) = {строка символов длинной 30};

dom(Дата) = {дата};

dom(Форма) = {множество {устно, письменно}}.

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

Отношение, соответствующее связи между классами Абитуриент и Специальность, назовем Заявление. Схема этого отношения будет включать в себя ключ отношения Абитуриент (атрибуты {Серия, Номер}) и ключ отношения Специальность (атрибут Номер_специальности), а также атрибуты Группа и Лист, соответствующие свойствам связи:

Заявление(Серия, Номер, Номер_специальности, Группа, Лист).

Абитуриент может подать заявления на несколько специальностей, поэтому ключом отношения будут атрибуты {Серия, Номер, Номер_специальности}:

Заявление(Серия, Номер, Номер_специальности, Группа, Лист).

Домены:

dom(Группа) = {целое двухзначное число};

dom(Лист) = {целое трехзначное число}.

Совокупность атрибутов {Серия, Номер} является внешним ключом, ссылающимся на ключ отношения Абитуриент.

Атрибут Специальность является внешним ключом, ссылающимся на ключ отношения Специальность.

Отношение, соответствующее связи между классами Факультет и Специальность, назовем СпециальностиФакультета. Схема этого отношения будет включать в себя ключ отношения Факультет (атрибут Факультет) и ключ отношения Специальность (атрибут Специальность). Ключом отношения будет атрибут Специальность. Значения этого атрибута повторяться не будут, так как каждая специальность относится только к одному факультету (связь имеет тип «один-ко-многим»):

СпециальностиФакультета(Факультет, Специальность).

Схема отношения Специальность и схема отношения СпециальностиФакультета имеют одинаковые ключи и содержат информацию об одних и тех же объектах – специальностях. Эти две схемы можно объединить в одну под названием Специальность:

Специальность(Номер_специальности, Название, Набор, Факультет).

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

Отношение, соответствующее связи между классами Специальность и Экзамен, назовем ЭкзаменыПоСпециальности. Схема этого отношения будет включать в себя ключ отношения Специальность (атрибут Специальность) и ключ отношения Экзамен (атрибут Экзамен). Ключом отношения будет атрибут Экзамен. Значения этого атрибута повторяться не будут, так как каждый экзамен проводится только по одной специальности (связь имеет тип «один-ко-многим»):

ЭкзаменыПоСпециальности(Специальность, Экзамен).

Схема отношения Экзамен и схема отношения ЭкзаменыПоСпециальности имеют одинаковые ключи и содержат информацию об одних и тех же объектах – экзаменах. Эти две схемы можно объединить в одну схему под названием Экзамен:

Экзамен(Номер, Предмет, Дата, Форма, Специальность).

Атрибут Специальность является внешним ключом, ссылающимся на ключ отношения Специальность.

Последняя связь – связь между классами Абитуриент и Экзамен. Дадим соответствующей ей схеме отношения название Оценки. Схема этого отношения будет включать ключ отношения Абитуриент (атрибуты {Серия, Номер}), ключ отношения Экзамен (атрибут Экзамен) и атрибут Оценка, соответствующий свойству связи:

Оценки(Серия, Номер, Экзамен, Оценка).

Так как абитуриент не может дважды сдавать один и тот же экзамен, то ключом будут атрибуты {Серия, Номер, Экзамен}:

Оценки(Серия, Номер, Экзамен, Оценка).

Домены:

dom(Оценка) = {множество {2, 3, 4, 5, 6, зачет, незачет}}.

Совокупность атрибутов {Серия, Номер} является внешним ключом, ссылающимся на ключ отношения Абитуриент.

Атрибут Экзамен является внешним ключом, ссылающимся на ключ отношения Экзамен.

Таким образом, даталогическая модель будет состоять из пяти отношений. Все отношения уже находятся в 3НФ.

1. Абитуриент(Фамилия, Имя, Отчество, Серия, Номер, Дата_рождения, Медаль)

dom(Фамилия) = {строка символов длиной 25};

dom(Имя) = {строка символов длиной 25};

dom(Отчество) = {строка символов длиной 30};

dom(Серия) = {строка символов длиной 5};

dom(Номер) = {целое шестизначное число};

dom(Дата_рождения) = {дата};

dom(Медаль) = {множество {золотая, серебряная, нет}}.

2. Специальность(Номер_специальности, Название, Набор, Факультет)

dom(Номер_специальности) = {целое число};

dom(Название) = {строка символов длиной 200};

dom(Набор) = {целое трехзначное число};

dom(Факультет) = {строка символов длиной 30}.

3. Экзамен(Номер, Предмет, Дата, Форма, Специальность)

· Атрибут Специальность является внешним ключом, ссылающимся на ключ отношения Специальность.

dom(Номер) = {целое трехзначное число};

dom(Предмет) = {строка символов длинной 30};

dom(Дата) = {дата};

dom(Форма) = {множество {устно, письменно}};

dom(Специальность) = {целое число}.

4. Заявление(Серия, Номер, Специальность, Группа, Лист)

· Совокупность атрибутов {Серия, Номер} является внешним ключом, ссылающимся на ключ отношения Абитуриент.

· Атрибут Специальность является внешним ключом, ссылающимся на ключ отношения Специальность.

dom(Серия) = {строка символов длиной 5};

dom(Номер) = {целое пятизначное число};

dom(Специальность) = {целое число};

dom(Группа) = {целое двухзначное число};

dom(Лист) = {целое трехзначное число}.

5. Оценки(Серия, Номер, Экзамен, Оценка)

· Совокупность атрибутов {Серия, Номер} является внешним ключом, ссылающимся на ключ отношения Абитуриент.

· Атрибут Экзамен является внешним ключом, ссылающимся на ключ отношения Экзамен.

dom(Серия) = {строка символов длиной 5};

dom(Номер) = {целое пятизначное число};

dom(Экзамен) = {целое трехзначное число};

dom(Оценка) = {множество {2, 3, 4, 5, 6, зачет, незачет}}.

Модель данных готова.

Список использованной литературы

1. Ульман Дж. Основы систем баз данных. М.: Финансы и статистика, 1983. 334 с.

2. Тиори Т., Фрай Дж. Проектирование структур баз данных: В 2 т. М.: Мир, 1985.

3. Цикритзис Д., Лоховски Ф. Модели данных. М.: Финансы и статистика, 1985. 343 с.

4. Дейт К. Дж. Введение в системы баз данных: Пер. с англ. Киев, М., СПб.: Издательский дом "Вильямс", 2000. 848 с.

5. Карпова Т. С. Базы данных: модели, разработка, реализация. СПб.: Питер, 2001. 304 с.

Оглавление

Введение. 3

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

2. Модель предметной области. 6

2.1. Объекты и классы объектов. 7

2.2. Связи между классами объектов. 8

2.3. Типы связей в предметной области. 9

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

3.1. Структура. 13

3.2. Операции. 18

3.3. Ограничения целостности. 29

3.4. Нормализация отношений. 30

3.5. Построение реляционной модели данных. 39

4. Физическая модель данных. 41

5. Пример проектирования базы данных. 42

Список использованной литературы.. 51