Загрузка...
Учебная работа. Контрольная работа: Нормализация таблиц в реляционной модели базы данных
Государственное образовательное учреждение
Высшего проф образования
РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ ТОРГОВО-ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ
КЕМЕРОВСКИЙ ИНСТИТУТ (ФИЛИАЛ)
ФАКУЛЬТЕТ ЗАОЧНОГО ОБУЧЕНИЯ
Кафедра вычислительной техники и информационных технологий
Контрольная работа
по дисциплине
“Базы данных”
по теме: “Нормализация таблиц в реляционной модели базы данных”
Выполнил:
студент группы ПИс-061
(сокращенная форма обучения)
Жилкова Ольга Анатольевна
г. Кемерово 2007 г.
Содержание
1 Нормализация таблиц в реляционной модели БД
1.1 понятие “Нормализация»
1.2 1-ая обычная форма
1.3 2-ая обычная форма
1.4 3-я обычная форма
1.5 4-ая обычная форма
1.6 5-ая обычная форма.
2. Реляционная алгебра над учебной базой
3. база данных для предметной области “Учебные пособия»
Литература
1 Нормализация таблиц в реляционной модели БД
1.1 понятие “Нормализация
«
Нормализация — это формализованная процедура, в процессе выполнения которой атрибуты данных (поля) группируются в таблицы, а таблицы, в свою очередь, — в базы данных. Цели нормализации последующие:
Исключить дублирование инфы в таблицах.
Обеспечить возможность конфигураций в структуре таблиц.
Уменьшить воздействие структурных конфигураций базы данных на работу приложений, обеспечивающих юзерам доступ к данным.
процесс нормализации состоит из нескольких шагов. 1-ые три из их, выполняемых почаще всего, были описаны в 1972 году медиком Коддом.
1.2 1-ая обычная форма
Каждое поле таблицы обязано представлять неповторимый тип инфы. Это правило значит, что нужно избавиться от циклических полей и поделить составные поля на отдельные элементы данных. Правила построения первой обычной формы требуют, чтоб все таблицы данных были плоскими и не содержали циклических данных в разных строчках. Под плоской понимается таблица, имеющая лишь два измерения: длина (число записей либо строк) и ширина (число полей либо столбцов). Ее ячейки не могут содержать больше 1-го значения. Если хотя бы одна ячейка таблицы содержит больше 1-го значения, для представления ее содержимого уже требуется третье измерение — глубина. Плоские таблицы и плоские файлы баз данных весьма похожи тем, что имеют лишь два измерения. Но в плоском файле содержится только одна таблица и не накладываются ограничения на содержимое ее ячеек.
Примером ненормализованных данных могут выступать данные, выставленные в таблице 1.1 Таковым методом обычно составляется расписание движения транспорта.
Таблица 1.1 — Ненормализованные данные
Судно
Заглавие
Рейс
Погрузка
Прибытие из
Прибытие
порт
Отправление
Прибытие
Порт
Отправление
526
Japan Bear
9203W
5/31/92
SFO
6/6/92
HNL
6/8/92
7/15/92
OSA
7/18/92
603
Korea Bear
9203W
5/05/92
OAK
6/19/92
OSA
6/21/92
6/25/92
INC
6/28/92
531
China Bear
9204W
6/20/92
LAX
7/10/92
PAP
7/11/92
8/28/92
SYD
9/2/92
528
Japan Bear
9204W
8/20/92
SFO
8/27/92
HNL
8/29/92
9/30/92
OSA
10/2/92
Так как суда останавливаются в почти всех портах, столбцы Прибытие, порт и Отправление повторяются для каждой остановки. Таковая структура записи данных не подступает для реляционной базы данных. запись приведенной инфы не соответствует требованиям первой обычной формы, так как содержит повторяющуюся группу столбцов. Эту таблицу нужно поделить на две: Порты и рейсы судов, не содержащие циклических групп, как показано в таблицах 1.2 и 1.3
Таблица 1.2 — Таблица “Рейсы судов”
Судно
Заглавие
Рейс
Погрузка
Прибытие из
528
Japan Bear
9203W
5/31/92
SFO
603
Korea Bear
9203W
6/5/92
OAK
531
China bear
9204W
6/20/92
LAX
528
Japan bear
9204W
8/20/92
SFO
Таблица 1.3 — Таблица “Порты”
Прибытие
порт
Отправление
6/6/92
HNL
6/8/92
6/19/92
OSA
6/21/92
7/10/92
PAP
7/11/92
8/27/92
HNL
8/29/92
7/15/92
OSA
7/18/92
6/25/92
INC
6/28/92
8/28/92
SYD
9/2/92
9/30/92
OSA
10/2/92
сейчас нужно установить связь меж таблицами Порты и Рейсы судов. В столбце рейс указывается текущий год, номер рейса за этот год, также направление рейса (к примеру, 9204W- это 4-ый рейс за 1992 год в западном направлении). Таковым образом, для связи меж таблицами следует использовать поля Судно и Рейс. Употреблять какой-нибудь один из этих методов недостаточно, так как одно судно может созодать несколько рейсов в течение года, а в одном направлении могут отчаливать сходу несколько судов. Так как для ублажения требований первой обычной формы придется сделать новейшую таблицу Порты, нужно отсортировать ее столбцы в порядке значимости. Первыми, как правило, располагаются столбцы, применяемые для установки связи. При всем этом они размещаются в той последовательности, в которой они входят в составной первичный ключ. Данные показаны в таблице 1.4
Таблица 1.4 — Таблица “Порты”
Судно
Рейс
порт
Прибытие
Отправление
528
9203W
HNL
6/6/92
6/8/92
603
9203W
OSA
6/19/92
6/21/92
531
9204W
PAP
7/10/92
7/11/92
528
9204W
HNL
8/27/92
8/29/92
528
9203W
OSA
7/15/92
7/18/92
603
9203W
INC
6/25/92
6/28/92
531
9204W
SYD
8/28/92
9/2/92
528
9204W
OSA
9/30/92
10/2/92
сейчас нужно найти главные поля таблицы Порты, что дает возможность буквально идентифицировать ее записи. Непременно нужно сделать первичный ключ, так как от данной для нас таблицы могут зависеть почти все остальные. нужно добавить столбцы Судно и рейс, потому что они обеспечивают связь с данными таблицы Рейсы судов, также добавить поле порт для сотворения совершенного неповторимого ключа (столбы Судно и Рейс могут содержать повторяющиеся значения). Композиции Судно+Рейс+Порт представляет собой составной первичный ключ, созодать остановку в одном порту (придвижении туда и назад). Так, если судно ворачивается с востока, рейс помечается суффиксом “Е».
1.3 2-ая обычная форма
Для приведения таблиц ко 2-ой обычной форме нужно обеспечить полную зависимость столбцов, которые не являются главными, от первичного ключа, а если этот ключ составной, то от всякого его элемента. Под полной зависимостью понимается возможность конкретного определения значения всякого неключевого поля при помощи значения первичного ключа. Если для конкретного определения употребляется составной первичный ключ, то это правило применяется к любому значению из полей, входящих в составной ключ. Постоянно, когда это может быть, в качестве первичного ключа следует применять самые обыкновенные данные, имеющие “естественные» неповторимые значения. Перед переходом ко 2-ой обычной форме нужно привести данные к первой нормально форме. В процессе сотворения 2-ой обычной формы большая часть циклических данных, оставшихся в таблице опосля приведения ее к первой обычной форме, будет удалена.
Для сотворения в таблице Рейсы судов конкретного ключа придется применять составной ключ (Судно+Рейс). Так как номер и заглавие судна могут повторяться. Поля Судно и Заглавие не зависят от первичного ключа, потому что полем Рейс ничего не определяется. Заглавие судна указывается в любом рейсе. Так, к примеру, заглавие JapanBear возникает два раза. Все эти недочеты нарушают правила 2-ой обычной формы. Возникает необходимость разбиения таблицы Рейсы судов еще на две: Рейсы и суда. Любой корабль описывается одной строчкой в таблице суда, а одна строчка таблицы Рейсы обрисовывает рейс 1-го судна (с целью упрощения построения базы данных восточные и западные направления рассматриваются как отдельные рейсы). Как и в таблице Порты, для установления соответствия меж рейсами и судами нужно сделать ключ, потому нужно добавить поле номеров судов в таблицу Рейсы. Таблицы суда и Рейсы показаны в таблицах 1.5 и 1.6
Таблица 1.5 — Таблица “Суда”
Судно
Заглавие
528
Japan Bera
603
Korea Bear
531
China bear
Таблица 1.6 — Таблица “Рейсы”
Судно
Рейс
Погрузка
Прибытие из
528
9203W
5/31/92
SFO
603
9203W
6/5/92
OAK
531
9204W
6/20/92
LAX
528
9204W
8/20/92
SFO
Опосля определения первичных ключей для каждой таблицы можно проверить, чтоб все данные, включенные в таблицы, относились к подходящим объектам. Иными словами, следует убедиться, что каждое поле функционально зависит от первичного ключа, который идентифицирует начальный объект таблицы. Для всякого значения первичного ключа значения в столбцах данных должны относиться к объекту таблицы и на сто процентов его обрисовывать. Это правило употребляется двойственно. Во-1-х, в таблице не обязано быть данных, не относящихся к объекту, определяемому первичным ключом. Во-2-х, данные в таблице должны на сто процентов обрисовывать объект.
1.4 3-я обычная форма
В третьей обычной форме столбцы, не являющиеся главными, зависят от первичного ключа таблицы и не зависят от всех других столбцов. Обязана быть возможность изменять значения хоть какого поля (не входящего в первичный ключ) без действия на данные остальных полей. До этого чем перейти к третьей обычной форме, нужно привести свои данные к первой, а потом — ко 2-ой.
Таблицы суда и Рейсы уже представлены в третьей обычной форме, так как они содержат циклических групп столбцов и существует зависимость неключевых столбцов этих таблиц от главных. В таблице Порты нет циклических групп полей, а неключевые поля Прибытие и Отправление зависят от составного ключа (Судно + Рейс + порт) и не зависят друг от друга. (Дата прибытия никак не влияет на дату отправления, так как интервал меж ними определяется временем погрузки продуктов на судно, доступностью стоянок, погрузочных кранов, также погодными критериями) Таковым образом, таблица Порты удовлетворяет требованиям первой, 2-ой и третьей обычных форм.
к примеру, нужно ввести в базу данных к тому же служащих, членов установок кораблей (капитанов, инженеров и т.п.). Для этого нужно добавить в таблицу суда коды служащих, являющиеся первичными ключами таблицы Служащие, показанные в таблице 1.7
Таблица 1.7 — Таблица с транзитивным отношением меж судами и служащими команды.
Судно
Заглавие
капитан
Старший ассистент
1-ый ассистент
528
Japan Bear
01023
01155
01367
603
Korea Bear
00955
01203
00823
531
China Bear
00721
00912
01251
Таковая таблица нарушает одно из правил третьей обычной формы, так как никто из назначенных на какое-либо судно служащих от этого судна не зависит конкретно (т.е. добавлямое неключевое поле не зависит от первичного ключа). капитан, его ассистент либо старший ассистент могут делать свои обязанности на любом судне. Всякого офицера можно в случае необходимости перевести на иной корабль либо бросить на суше до последующего рейса.
Можно испытать добавить столбец с номерами служащих в таблицу Рейсы. Да и это не решит всех заморочек, потому что судно может прибыть в порт с одним командным составом, а отбыть с совсем остальным. Не считая этого, возникает необходимость найти членов команды, остающихся на судне во время его стоянки в порту.
Все эти вопросцы решаются при помощи таблицы Команды, приведенной в таблице 1.8 повторяющиеся значения полей Порт (порт отправления) и Отправление в (порт прибытия) делают записи для тех членов команды, которые несут ответственность за судно во время его пребывания в порту. Таблица Команды относится к группы связывающих таблиц, так как все поля соединены с первичными ключами главных таблиц (суда, Рейсы, Порты и Служащие) либо являются частью этих таблиц.
Таблица 1.8 — Таблица “Команды”
Судно
Рейс
порт
Отправление в
Капитан
Старший ассистент
1-ый ассистент
528
9203W
SFO
HNL
01023
01156
01367
528
9203W
HNL
HNL
01023
01156
01367
528
9203W
HNL
OSA
01023
01156
01367
528
9203W
OSA
OSA
01023
01156
01367
528
9203W
OSA
INC
01023
01156
01367
сейчас все таблицы являются плоскими, не содержат циклической инфы, кроме данных, применяемых в ключах. Все эти таблицы удовлетворяют требованиям первой, 2-ой и третьей обычной форм.
1.5 4-ая обычная форма
Чтоб база данных находилась в четвертой обычной форме, нужно, чтоб независящие элементы данных, меж которыми существует связь типа многие-ко-многим, не хранились в одной таблице. Таблица команды не находится в четвертой обычной форме, так как связи многие-ко-многим установлены меж ее полем судно и полями с кодами членов команды.
Почти все создатели приложений баз данных игнорируют четвертую и пятую обычные формы в собственных программных продуктах, так как считают их очень специфичными. Результатом этого часто является создание базы данных неверной структуры, хотя это совершенно не значит, что она не будет работать.
1.6 5-ая обычная форма
5-ая обычная форма просит обеспечения способности четкого восстановления начальной таблицы из таблиц, на которых она базирована. Построение пятой обычной формы просит ублажения требований третьей обычной формы и, при наличии связей многие-ко-многим, соответствия правилам четвертой.
Таблица Рейсы весьма похожа на таблицу Порты. Столбец прибытие из аналогичен столбцу порт, а столбец Погрузка — столбцу Отправление. конкретно по данной для нас причине можно переместить данные таблицы Рейсы в таблицу порты и удалить таблицу Рейсы. новенькая таблица порты показана в таблице 1.9 значения столбца Прибытие для записей, внесенных в таблицу Порты из таблицы рейсы, не определены (не есть), потому что в таблице Рейсы были указаны лишь даты отравления.
Таблица 1.9 — Таблица “Порты”
Судно
Рейс
порт
Прибытие
Отправление
528
9203W
HNL
6/6/92
6/8/92
528
9203W
OSA
6/19/92
6/21/92
528
9204W
PAP
7/10/92
7/11/92
528
9204W
HNL
8/27/92
8/29/92
528
9203W
OSA
7/15/92
7/18/92
603
9203W
INC
6/25/92
6/28/92
531
9204W
SYD
8/28/92
9/2/92
528
9204W
OSA
9/30/92
10/2/92
528
9203W
SFO
5/31/92
603
9203W
OAK
6/5/92
531
9204W
LAX
6/20/92
528
9204W
SFO
6/20/92
Нереально вернуть начальную таблицу из объединенных таблиц Рейсы и Порты, так как не можете отличить строчку отправления от остальных строк по значениям ее полей в таблице. Чтоб отличить строчки отправлений, можно было бы применять значения Null в поле Прибытие, но значение Null обязано быть зарезервированным для условия “данные недосягаемы». нужно убрать любые двусмысленности, которые могут привести к возникновению значений Null, и конвертировать таблицу в пятую нормальную форму, добавив односимвольное поле Тип, определяющее прибытие либо отправление. В показанной таблице 1.10 Порты коды Е и S представляют соответственно погрузку (Embarkation) и ожидаемое прибытие (Scheduled). Могут также употребляться коды M для заправки (Maintenance) и R- для оборотного рейса (Returnvoyage).
Таблица 1.10 — Таблица “Порты”
Судно
Рейс
порт
Тип
Прибытие
Отправление
528
9203W
HNL
S
6/6/92
6/8/92
528
9203W
OSA
S
6/19/92
6/21/92
528
9204W
PAP
S
7/10/92
7/11/92
528
9204W
HNL
S
8/27/92
8/29/92
528
9203W
OSA
S
7/15/92
7/18/92
603
9203W
INC
S
6/25/92
6/28/92
531
9204W
SYD
S
8/28/92
9/2/92
528
9204W
OSA
S
9/30/92
10/2/92
528
9203W
SFO
E
5/31/92
603
9203W
OAK
E
6/5/92
531
9204W
LAX
E
6/20/92
528
9204W
SFO
E
6/20/92
2. Реляционная алгебра над учебной базой
R1 — перечень абитуриентов, сдававших репетиционные вступительные экзамены;
R2 — перечень абитуриентов, сдававших вступительные экзамены на общих основаниях;
R3 — перечень абитуриентов, принятых в институт.
нужно написать ответ на запрос в виде формулы реляционной алгебры. запрос: “Перечень абитуриентов, которые поступали дважды и поступили в университет».
Таблица 2.1 — Отношение R1
Обозначение записи
ФИО абитуриента
Номер и серия паспорта
№ Школы
a
Жилкова О.А.
32 05 4237
№ 31
b
Богач Д.О.
34 07 4385
№ 42
с
Конопелько О.П.
37 08 4282
№ 56
d
Кочкина Т.В.
38 02 3458
№ 52
e
Докучаев Ю.А.
58 02 3718
№ 62
f
Богданова Ю.В.
38 72 4290
№ 48
g
Сидорова С.И.
39 52 4870
№ 45
h
Сидоров А.А.
38 59 3295
№ 46
l
Тарабрина Л.В.
40 58 2598
№ 49
Таблица 2.2 — Отношение R2
Обозначение записи
ФИО абитуриента
Номер и серия паспорта
№ Школы
a
Жилкова О.А.
32 05 4237
№ 31
b
Богач Д.О.
34 07 4385
№ 42
d
Кочкина Т.В.
38 02 3458
№ 52
h
Сидоров А.А.
38 59 3295
№ 46
m
Тарабрин В.В.
35 92 4058
№48
n
Голоушкина В.А.
38 92 4259
№ 52
o
Токарева М.А.
39 98 4085
№ 46
p
Круглова Т.Ю.
32 58 3498
№ 47
Таблица 2.3 — Отношение R3
Обозначение записи
ФИО абитуриента
Номер и серия паспорта
№ Школы
a
Жилкова О.А.
32 05 4237
№ 31
b
Богач Д.О.
34 07 4385
№ 42
d
Кочкина Т.В.
38 02 3458
№ 52
h
Сидоров А.А.
38 59 3295
№ 46
p
Круглова Т.Ю.
32 58 3498
№ 47
с
Конопелько О.П.
37 08 4282
№ 56
Операция реляционной алгебры — “пересечение».
R1 (a,b,c,d,e,f,g,h,l) 
R2 (a,b,d,h,m,n,o,p) =R3 (a,b,d,h)
3. база данных для предметной области “Учебные пособия»
Ненормализованное инфы;
Таблица “Дисциплины» — номер дисциплины, наименование дисциплины, количество часов;
Таблица “Пособия” — номер пособия, ФИО создателя, Номер дисциплины;
Таблица “Специальности” — номер специальности, наименование специальности;
2-ая обычная форма — любая таблица обязана иметь первичный ключ, который может состоять из 1-го Ии нескольких полей.
Таблица “Дисциплины» — номер дисциплины;
Таблица “Пособия” — номер пособия;
Таблица “Специальности” — номер специальности.
3-я обычная форма — столбцы, не являющиеся главными, зависят от первичного ключа таблицы и не зависят от всех других столбцов. Для связывания таблиц “Дисциплины» и “Специальности” нужно сделать доп таблицу “Дисциплины-Специальности».
Таблица “Дисциплины-Специальности» — номер дисциплины, номер специальности.
Данные таблиц.
Таблица 3.1 — Таблица “Дисциплины”
Номер дисциплины
Наименование дисциплины
количество часов
1
Информатик
132
2
Экономика
180
3
Базы данных
72
4
Базы бухгалтерского учета
86
5
Базы программирования
92
6
Теория вероятностей и математическая статистика
146
7
Глобальная Экономика
112
8
Компьютерные сети
98
Таблица 3.2 — Таблица “Пособия”
Номер пособия
ФИО создателя
Номер дисциплины
1
Джон Вейкас
3
2
Роджер Дженнингс
3
3
Вирджиния Андерсон
1
4
Попов А.А.
1
5
Булатов А.С.
2
6
Бендина Н.В.
4
7
Видяпин В.И.
2
8
Дурович А.П.
4
9
Коуров Л.В.
1
10
Кашанин Т.В.
7
11
Гмурман В.Е.
6
12
Кенин А.М.
8
13
Питер Эйткен
5
14
Подбельский В.В.
5
15
Вендров А.М.
7
16
Рапаков Г.Г.
8
17
Якушева Г.В.
6
18
Комягина В.Б.
4
19
Бердиченко Е.В.
7
Таблица 3.3 — Таблица “Специальности”
Номер дисциплины
Наименование дисциплины
101170
Прикладная информатика в экономике
220135
Программное обеспечение ВТ и АС
11370
Бухгалтерский учет
13568
Финансовая теория
73809
Администрирование компьютерных сетей
Таблица 3.4 — Таблица “Дисциплины — Специальности»
Номер специальности
Номер дисциплины
101170
1
101170
5
101170
6
101170
2
101170
7
220135
1
220135
5
220135
6
220135
3
11370
1
11370
4
11370
2
13568
2
13568
6
13568
7
13568
1
73809
3
73809
5
73809
8
Создание таблиц. Для сотворения запросов выбрана СУБД ACCESS.
CREATE дисциплины (number integer,
name_disz varchar (100),
hour integer);
CREATE пособия (number integer,
author varchar (100),
diszipl integer);
CREATE специальности (number varchar (10),
name_spez varchar (100));
CREATE дисциплины_специальности (number_spez varchar (100),
number_diszinteger).
Наполнение таблиц данными. Для примера показаны вставки по одной записи.
INSERT INTO дисциплины (number, name_disz, hour) VALUES (1, “Информатика”, 132);
INSERTINTO специальности (number, name_spez) VALUES (“101170″, “Прикладная информатика в экономике”);
INSERT INTO пособия (number, autor, diszipl) VALUES (1, “Джон Вейкас”, 3);
INSERT INTO дисциплины_специальности (number_spez, number_disz) VALUES (“101170”, 1)
запрос1 — Для номера специальности “220135″ вывести наименование данной для нас специальности, наименования дисциплин для данной для нас специальности, у каких количество часов больше 90 и меньше 140, также создателей пособий для этих дисциплин.
SELECT специальности. number AS «Номер специальности»,
специальности. name_spezAS «Специальность»,
дисциплины. name_diszAS «Дисциплина»,
дисциплины. hourAS «количество часов»,
пособия. authorAS «Создатель пособия»
FROM специальности, дисциплины, пособия,
дисциплины_специальности
WHERE дисциплины_специальности. number_disz=дисциплины. number And
дисциплины_специальности. number_spez=специальности. number And
пособия. diszipl=дисциплины. number And
специальности. number=»220135″ And
дисциплины. hour Between 90 And 140
ORDER BY дисциплины. name_disz, пособия. author;
запрос 2 — Вывести для каждой специальности номер специальности, наименование специальности, количество дисциплин для каждой специальности, у каких количество часов больше 90 и меньше 150.
SELECT специальности. number AS «Номер специальности»,
специальности. name_spez AS «Специальность»,
COUNT (дисциплины_специальности. number_disz) AS «количество
дисциплин»
FROM специальности, дисциплины_специальности, дисциплины
WHERE дисциплины_специальности. number_spez=специальности. number
And дисциплины_специальности. number_disz=дисциплины. number
And дисциплины. hour Between 90 And 150
GROUP BY специальности. number, специальности. name_spez
ORDER BY специальности. name_spez;
Литература
1. Базы данных: теория и практика: Учебник для вузов/ Б.Я. Советов., В.В. Цехановский, В.Д. Черотовской. — М.: Высш. шк., 2005. — 463 с. ил.
2. Вейскас Д. действенная работа с MicrosoftAccess 97 — СПб: ЗАО “Издательство “Питер””, 1999. — 976 с.: ил.
3. В.В. Корнеев, А.Ф. Гареев, С.В. Васютин, В.В. Райх. Базы данных. Умственная обработка инфы. — М.: Издатель Момачева С.В., Издательство Нолидж, 2001. — 496 с., ил.
4. Дженнингс, Роджер. Внедрение MicrosoftAccess 2000. Особое издание.: Пер. с англ.: Уч. пос. — М.: Издательский дом “Вильямс», 2000. — 1152 с.: ил. — Парал. тит. англ.
]]>