ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
Государственное образовательное учреждение
Высшего профессионального образования
РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ ТОРГОВО-ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ
КЕМЕРОВСКИЙ ИНСТИТУТ (ФИЛИАЛ)
ФАКУЛЬТЕТ ЗАОЧНОГО ОБУЧЕНИЯ
Кафедра вычислительной техники и информационных технологий
Контрольная работа
по дисциплине
“Базы данных”
по теме: “Нормализация таблиц в реляционной модели базы данных”
Выполнил:
студент группы ПИс-061
(сокращенная форма обучения)
Жилкова Ольга Анатольевна
г. Кемерово 2007 г.
Содержание
1 Нормализация таблиц в реляционной модели БД
1.1 Понятие “Нормализация"
1.2 Первая нормальная форма
1.3 Вторая нормальная форма
1.4 Третья нормальная форма
1.5 Четвертая нормальная форма
1.6 Пятая нормальная форма.
2. Реляционная алгебра над учебной базой
3. База данных для предметной области “Учебныепособия"
Литература
/>1 Нормализация таблиц вреляционной модели БД
/> 1.1 Понятие “Нормализация"
Нормализация — этоформализованная процедура, в процессе выполнения которой атрибуты данных (поля)группируются в таблицы, а таблицы, в свою очередь, — в базы данных. Целинормализации следующие:
Исключить дублированиеинформации в таблицах.
Обеспечить возможность измененийв структуре таблиц.
Уменьшить влияние структурных измененийбазы данных на работу приложений, обеспечивающих пользователям доступ к данным.
Процесс нормализации состоит изнескольких этапов. Первые три из них, выполняемых чаще всего, были описаны в1972 году доктором Коддом.
/> 1.2 Первая нормальная форма
Каждое поле таблицы должнопредставлять уникальный тип информации. Это правило означает, что необходимоизбавиться от повторяющихся полей и разделить составные поля на отдельные элементыданных. Правила построения первой нормальной формы требуют, чтобы все таблицыданных были плоскими и не содержали повторяющихся данных в различных строках. Подплоской понимается таблица, имеющая только два измерения: длина (число записейили строк) и ширина (число полей или столбцов). Ее ячейки не могут содержатьбольше одного значения. Если хотя бы одна ячейка таблицы содержит больше одногозначения, для представления ее содержимого уже требуется третье измерение — глубина. Плоские таблицы и плоские файлы баз данных очень похожи тем, что имеюттолько два измерения. Однако в плоском файле содержится лишь одна таблица и ненакладываются ограничения на содержимое ее ячеек.
Примером ненормализованныхданных могут выступать данные, представленные в таблице 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 — это четвертый рейс за 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 Вторая нормальная форма
Для приведения таблиц ко второйнормальной форме необходимо обеспечить полную зависимость столбцов, которые неявляются ключевыми, от первичного ключа, а если этот ключ составной, то откаждого его элемента. Под полной зависимостью понимается возможностьоднозначного определения значения каждого неключевого поля с помощью значенияпервичного ключа. Если для однозначного определения используется составнойпервичный ключ, то это правило применяется к каждому значению из полей,входящих в составной ключ. Всегда, когда это возможно, в качестве первичногоключа следует использовать самые простые данные, имеющие “естественные" уникальныезначения. Перед переходом ко второй нормальной форме необходимо привести данныек первой нормально форме. В процессе создания второй нормальной формы большаячасть повторяющихся данных, оставшихся в таблице после приведения ее к первойнормальной форме, будет удалена.
Для создания в таблице Рейсысудов однозначного ключа придется использовать составной ключ (Судно+Рейс). Посколькуномер и название судна могут повторяться. Поля Судно и Название не зависят отпервичного ключа, так как полем Рейс ничего не определяется. Название суднауказывается в каждом рейсе. Так, например, название Japan Bear появляется дважды. Все этинедостатки нарушают правила второй нормальной формы. Возникает необходимостьразбиения таблицы Рейсы судов еще на две: Рейсы и Суда. Каждый корабльописывается одной строкой в таблице суда, а одна строка таблицы Рейсы описываетрейс одного судна (с целью упрощения построения базы данных восточные изападные направления рассматриваются как отдельные рейсы). Как и в таблицеПорты, для установления соответствия между рейсами и судами необходимо создатьключ, поэтому необходимо добавить поле номеров судов в таблицу Рейсы. ТаблицыСуда и Рейсы показаны в таблицах 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.4 Третья нормальная форма
В третьей нормальной форместолбцы, не являющиеся ключевыми, зависят от первичного ключа таблицы и независят от всех остальных столбцов. Должна быть возможность изменять значениялюбого поля (не входящего в первичный ключ) без воздействия на данные другихполей. Прежде чем перейти к третьей нормальной форме, необходимо привести своиданные к первой, а затем — ко второй.
Таблицы суда и Рейсы ужепредставлены в третьей нормальной форме, поскольку они содержат повторяющихсягрупп столбцов и существует зависимость неключевых столбцов этих таблиц отключевых. В таблице Порты нет повторяющихся групп полей, а неключевые поляПрибытие и Отправление зависят от составного ключа (Судно + Рейс + Порт) и независят друг от друга. (Дата прибытия никак не влияет на дату отправления,поскольку интервал между ними определяется временем погрузки товаров на судно,доступностью стоянок, погрузочных кранов, а также погодными условиями) Такимобразом, таблица Порты удовлетворяет требованиям первой, второй и третьейнормальных форм.
Например, необходимо ввести вбазу данных еще и служащих, членов команд кораблей (капитанов, инженеров и т.п.).Для этого необходимо добавить в таблицу Суда коды служащих, являющиесяпервичными ключами таблицы Служащие, показанные в таблице 1.7
Таблица 1.7 — Таблица с транзитивным отношением между судами и служащими команды. Судно Название Капитан Старший помощник Первый помощник 528 Japan Bear 01023 01155 01367 603 Korea Bear 00955 01203 00823 531 China Bear 00721 00912 01251
Такая таблица нарушает одно изправил третьей нормальной формы, поскольку никто из назначенных на какое-либосудно служащих от этого судна не зависит непосредственно (т.е. добавлямоенеключевое поле не зависит от первичного ключа). Капитан, его помощник илистарший помощник могут выполнять свои обязанности на любом судне. Каждогоофицера можно в случае необходимости перевести на другой корабль или оставитьна суше до следующего рейса.
Можно попробовать добавитьстолбец с номерами служащих в таблицу Рейсы. Но и это не решит всех проблем,так как судно может прибыть в порт с одним командным составом, а отбыть ссовершенно другим. Кроме этого, возникает необходимость определить членовкоманды, остающихся на судне во время его стоянки в порту.
Все эти вопросы решаются спомощью таблицы Команды, приведенной в таблице 1.8 повторяющиеся значения полейПорт (порт отправления) и Отправление в (порт прибытия) создают записи для техчленов команды, которые несут ответственность за судно во время его пребыванияв порту. Таблица Команды относится к категории связующих таблиц, поскольку всеполя связаны с первичными ключами основных таблиц (Суда, Рейсы, Порты иСлужащие) или являются частью этих таблиц.
Таблица 1.8 — Таблица “Команды”Судно Рейс Порт Отправление в Капитан Старший помощник Первый помощник 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
Теперь все таблицы являютсяплоскими, не содержат повторяющейся информации, за исключением данных,используемых в ключах. Все эти таблицы удовлетворяют требованиям первой, второйи третьей нормальной форм.
/> 1.5 Четвертая нормальная форма
Чтобы база данных находилась вчетвертой нормальной форме, необходимо, чтобы независимые элементы данных,между которыми существует связь типа многие-ко-многим, не хранились в однойтаблице. Таблица команды не находится в четвертой нормальной форме, посколькусвязи многие-ко-многим установлены между ее полем судно и полями с кодамичленов команды.
Многие разработчики приложенийбаз данных игнорируют четвертую и пятую нормальные формы в своих программныхпродуктах, поскольку считают их весьма специфическими. Результатом этогозачастую является создание базы данных неправильной структуры, хотя это совсемне означает, что она не будет функционировать.
/>/>1.6 Пятая нормальная форма
Пятая нормальная форма требуетобеспечения возможности точного восстановления исходной таблицы из таблиц, накоторых она основана. Построение пятой нормальной формы требует удовлетворениятребований третьей нормальной формы и, при наличии связей многие-ко-многим,соответствия правилам четвертой.
Таблица Рейсы очень похожа натаблицу Порты. Столбец прибытие из аналогичен столбцу Порт, а столбец Погрузка — столбцу Отправление. именно по этой причине можно переместить данные таблицыРейсы в таблицу порты и удалить таблицу Рейсы. новая таблица порты показана втаблице 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 — для обратного рейса (Return voyage).
Таблица 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. База данных дляпредметной области “Учебные пособия"/> />
Ненормализованное представление информации в виде схемы.
Приведение к третьей нормальнойформе.
первая нормальная форма — каждоеполе таблицы представляет уникальный тип информации;
Таблица “Дисциплины" — номер дисциплины, наименование дисциплины, количество часов;
Таблица “Пособия” — номерпособия, ФИО автора, Номер дисциплины;
Таблица “Специальности” — номерспециальности, наименование специальности;
вторая нормальная форма — каждаятаблица должна иметь первичный ключ, который может состоять из одного Иинескольких полей.
Таблица “Дисциплины" — номер дисциплины;
Таблица “Пособия” — номерпособия;
Таблица “Специальности” — номерспециальности.
третья нормальная форма — столбцы, не являющиеся ключевыми, зависят от первичного ключа таблицы и независят от всех остальных столбцов. Для связывания таблиц “Дисциплины" и“Специальности” необходимо создать дополнительную таблицу“Дисциплины-Специальности".
Таблица“Дисциплины-Специальности" — номер дисциплины, номер специальности.
/>
Данные таблиц.
Таблица 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_diszvarchar (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_disz integer).
Заполнение таблиц данными. Дляпримера показаны вставки по одной записи.
INSERT INTO дисциплины (number, name_disz, hour) VALUES (1, “Информатика”, 132);
INSERT INTO специальности (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_spez AS«Специальность»,
дисциплины. name_disz AS«Дисциплина»,
дисциплины. hour AS «Количество часов»,
пособия. author AS «Автор пособия»
FROM специальности, дисциплины,пособия,
дисциплины_специальности
WHERE дисциплины_специальности. number_disz=дисциплины.number And
дисциплины_специальности. number_spez=специальности.number And
пособия. diszipl=дисциплины. numberAnd
специальности. number=«220135»And
дисциплины. hourBetween 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. Вейскас Д. эффективная работа с Microsoft Access 97 — СПб: ЗАО “Издательство“Питер””, 1999. — 976 с.: ил.
3. В.В. Корнеев, А.Ф. Гареев, С.В. Васютин, В.В. Райх. Базы данных. Интеллектуальнаяобработка информации. — М.: Издатель Момачева С.В., Издательство Нолидж, 2001. — 496 с., ил.
4. Дженнингс, Роджер. Использование Microsoft Access 2000. Специальное издание.: Пер.с англ.: Уч. пос. — М.: Издательский дом “Вильямс", 2000. — 1152 с.: ил. — Парал. тит. англ.