Пояснительная записка
Учебно-методическое пособие для проведения практических занятий по дисциплине «Основы проектирования баз данных» реализует требования ФГОС к подготовке выпускников по специальности 230401 «Информационные системы (по отраслям)».
Дисциплина «Основы проектирования баз данных» входит в профессиональный цикл общепрофессиональных дисциплин в структуре основной профессиональной образовательной программы по данной специальности.
Для закрепления теоретических знаний, навыков и умений рабочей программой дисциплины предусматривается проведение практических занятий.
Практические занятия способствуют представлению о видах моделей данных, технологиях обработки информации в базе данных, о современных программных средствах разработки и проектирования баз данных, формируют навыки работы по построению информационной модели данных, выполнению нормализации данных, проектированию базы данных, созданию информационной и логической моделей данных, работе с системой управления базами данных на примере MS Access.
Проведение практических занятий способствует формированию у студентов:
Освоенных умений:
проектировать реляционную базу данных;
использовать язык запросов для программного извлечения сведений из баз данных.
Усвоенных знаний:
основы теории баз данных;
модели данных;
особенности реляционной модели и проектирование баз данных;
изобразительные средства, используемые в ER-моделировании;
основы реляционной алгебры;
принципы проектирования баз данных, обеспечение непротиворечивости и целостности данных;
средства проектирования структур баз данных;
язык запросов SQL.
Учебно-методическое пособие содержит 15 практических занятий. Каждое практическое занятие состоит из информационной части, в которой объясняются основные термины, изучаемой темы, поясняются технологии, используемые при выполнении практического занятия. Вторая часть - исполнительная, содержит задание и методические указания для его выполнения. Третья часть - контрольные вопросы, которые позволяют провести преподавателю анализ уровня освоения студентами умений, закрепления знаний на практических занятиях. Для закрепления навыков в каждом занятии содержатся задания для самостоятельного выполнения.
Целью проведения практических занятий является формирование у студентов умений работы с базами данных.
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 1
«ПРОЕКТИРОВАНИЕ СТРУКТУРЫ БАЗЫ ДАННЫХ. НОРМАЛИЗАЦИЯ ТАБЛИЦ»
Цель занятия: Научиться определять виды зависимостей между атрибутами в таблице, определять вид нормальной формы таблицы и переводить таблицы в нормальные формы более высокого уровня нормализации.
ИНФОРМАЦИОННАЯ ЧАСТЬ
Проектирование баз данных осуществляется на физическом и логическом уровне. Решение проблем проектирования на физическом уровне зависит в основном от используемой СУБД, часто автоматизировано и скрыто от пользователя. Логическое проектирование заключается в определении числа и структуры таблиц, формировании запросов к БД, определении типов отчетных документов, разработке алгоритмов обработки информации, создании форм для ввода и редактирования данных.
При проектировании структур данных чаще всего сначала собирают сведения об объектах решаемой задачи в рамках одной таблицы и затем производят ее декомпозицию на несколько взаимосвязанных таблиц на основе процедуры нормализации.
Рассмотрим собранные в одну таблицу 1 сведения о преподавателях учебного заведения.
Таблица 1 - Преподаватель
ФИО
|
Долж-ность
|
Оклад
|
Стаж
|
Надбавка за стаж
|
Кафед-ра
|
Предмет
|
Группа
|
Вид занятия
|
Иванов И.И.
|
преп
|
500
|
5
|
100
|
25
|
СУБД
|
256
|
Практ
|
Иванов И.И.
|
преп
|
500
|
5
|
100
|
25
|
ПЛ/1
|
123
|
Практ
|
Петров П.П.
|
Ст.преп
|
800
|
7
|
100
|
25
|
СУБД
|
256
|
Лекц
|
Петров П.П.
|
Ст.преп
|
800
|
7
|
100
|
25
|
Паскаль
|
256
|
Практ
|
Сидоров С.С
|
преп
|
500
|
10
|
150
|
25
|
ПЛ/1
|
123
|
Лекц
|
Сидоров С.С
|
преп
|
500
|
10
|
150
|
25
|
Паскаль
|
256
|
Лекц
|
Егоров Е.Е.
|
Преп
|
500
|
5
|
100
|
24
|
ПЭВМ
|
244
|
Лекц
|
Исходное отношение содержит избыточное дублирование данных о преподавателя.
Определяют виды зависимостей между атрибутами:
Атрибут В функционально зависит от атрибута А, если каждому значению атрибута А соответствует в точности одно значение В. Записывается это следующим образом: АВ. Это означает, что во всех кортежах с одинаковым значением атрибута А атрибут В будет иметь так же одно и тоже значение. В отношении «Преподаватель» можно выделить следующие функциональные зависимости: ФИОКафедра, ФИОДолжность, ФИООклад и т.д.
Частичная функциональная зависимость- это зависимость неключевого атрибута от части составного первичного ключа. В отношении «Преподаватель» первичным ключом является совокупность полей ФИО+Группа+Предмет. Тогда Должность, Оклад, Стаж, Надбавка за стаж, Кафедра находятся в частичной функциональной зависимости от ключа таблицы, так как зависят от ФИО, являющегося частью составного первичного ключа.
Полная функциональная зависимость- это зависимость неключевого атрибута от всего составного первичного ключа. В отношении «Преподаватель»- это атрибут Вид занятия.
Атрибут С зависит от атрибута А транзитивно, если для атрибутов А, В, С выполняются условия: АВ и ВС. В отношении «Преподаватель» транзитивной зависимостью связаны атрибуты: ФИОДолжностьОклад и ФИОСтажНадбавка за стаж.
Зависимости между атрибутами можно представить в виде схемы зависимостей (рис.1).
Рисунок 1 - Схема зависимости атрибутов
Нормализация
Отношение находится в первой нормальной форме, если все его атрибуты являются простыми. Наше исходное отношение уже находится в 1НФ. Таблица 2 не находится в 1НФ. Чтобы привести её в 1НФ необходимо дублировать информацию о преподавателях.
Таблица 2 - Отношение, не находящееся в 1НФ
ФИО
|
Долж-ность
|
Оклад
|
Стаж
|
Надбавка за стаж
|
Кафед-ра
|
Предмет
|
Группа
|
Вид занятия
|
Иванов И.И.
|
преп
|
500
|
5
|
100
|
25
|
СУБД
|
256
|
Практ
|
ПЛ/1
|
123
|
Практ
|
Петров П.П.
|
Ст.преп
|
800
|
7
|
100
|
25
|
СУБД
|
256
|
Лекц
|
Паскаль
|
256
|
Практ
|
Сидоров С.С
|
преп
|
500
|
10
|
150
|
25
|
ПЛ/1
|
123
|
Лекц
|
Паскаль
|
256
|
Лекц
|
Егоров Е.Е.
|
Преп
|
500
|
5
|
100
|
24
|
ПЭВМ
|
244
|
Лекц
|
Отношение находится во второй нормальной форме (2НФ), если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от первичного ключа (составного). Приведем отношение «ПРЕПОДАВАТЕЛЬ» ко 2НФ, разбив его на 2 связанные таблицы. В первой таблице (таблица 3) остается весь составной первичный ключ и атрибуты, которые от него зависят полно. Назовем таблицу «Предметы». Во вторую таблицу (таблица 4) помещаем атрибут-часть составного ключа, и атрибуты, находящиеся в частичной функциональной зависимости от этой части первичного ключа. Назовем ее «Преподаватели».
Таблица 3 – Предметы
ФИО
|
Предмет
|
Группа
|
Вид занятия
|
Иванов И.И.
|
СУБД
|
256
|
Практ
|
Иванов И.И.
|
ПЛ/1
|
123
|
Практ
|
Петров П.П.
|
СУБД
|
256
|
Лекц
|
Петров П.П.
|
Паскаль
|
256
|
Практ
|
Сидоров С.С
|
ПЛ/1
|
123
|
Лекц
|
Сидоров С.С
|
Паскаль
|
256
|
Лекц
|
Егоров Е.Е.
|
ПЭВМ
|
244
|
Лекц
|
Таблица 4 - Преподаватели
ФИО
|
Должность
|
Оклад
|
Стаж
|
Надбавка за стаж
|
Кафедра
|
Иванов И.И.
|
преп
|
500
|
5
|
100
|
25
|
Петров П.П.
|
Ст.преп
|
800
|
7
|
100
|
25
|
Сидоров С.С
|
преп
|
500
|
10
|
150
|
25
|
Егоров Е.Е.
|
Преп
|
500
|
5
|
100
|
24
|
Отношение находится в третей нормальной форме, если оно находится во 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.
Таблица «Предметы» уже находится в 3НФ, а в таблице «Преподаватели» нужно избавиться от транзитивной зависимости атрибутов Оклад и Надбавка за стаж, разбив ее на 3 связанные таблицы: «Преподаватели», «Должности», «Стаж».
Таблица 5 - Преподаватели
ФИО
|
Должность
|
Стаж
|
Кафедра
|
Иванов И.И.
|
преп
|
5
|
25
|
Петров П.П.
|
Ст.преп
|
7
|
25
|
Сидоров С.С
|
преп
|
10
|
25
|
Егоров Е.Е.
|
Преп
|
5
|
24
|
|
|
|
|
|
|
|
|
|
|
|
|
Стаж
|
Надбавка за стаж
|
5
|
100
|
7
|
100
|
10
|
150
|
Таблица 6 – Должности Таблица 7 – Стаж
Должность
|
Оклад
|
преп
|
500
|
Ст.преп
|
800
|
Обычно достаточно бывает привести таблицу к 3НФ и на этом закончить процесс нормализации.
Всего существует 5 нормальных форм и нормальная форма Бойса-Кодда, которые рассматривать мы не будем.
ИСПОЛНИТЕЛЬНАЯ ЧАСТЬ
1. Приведите таблицу 8 «Заказы на ремонт от жильцов дома по ул. Победы, 18 , 1 подъезд за 2005 год» в 1НФ.
2. Определите составной первичный ключ таблицы, т.е. необходимо найти такую совокупность атрибутов, которая не будет повторяться, будет уникальной.
3. Определите виды зависимости между атрибутами и представьте их в виде схемы зависимостей атрибутов.
4. Приведите таблицу ко 2НФ и 3НФ.
КОНТРОЛЬНЫЕ ВОПРОСЫ
Сколько различных видов нормальных форм существует?
Какие виды зависимостей между атрибутов существуют?
Для чего применяют процесс нормализации таблиц?
Опишите алгоритм приведения таблицы ко 2 и 3 нормальной формам. Какие виды зависимостей между атрибутами могут присутствовать у таблиц, находящихся во 2 и 3 нормальных формах.
Таблица 8 - Заказы на ремонт от жильцов дома по ул. Победы, 18, 1 подъезд за 2005 год
№
квартиры
|
ФИО владельца
|
Вид владельца
|
Процент скидки
|
Кол-во комнат
|
Постоянная часть оплаты
|
Кол-во прописан-ных
|
Переменная часть оплаты
|
Вид ремонта
|
Мастер
|
Качество проделанной работы
|
1
|
Иванов И.Л.
|
Льгот
|
20%
|
2
|
400
|
3
|
300
|
Замена труб
|
Роев
|
Удовл.
|
Замена батареи
|
Шитов
|
Удовл.
|
Чистка канализации
|
Роев
|
Плохое
|
4
|
Прохоров В.А.
|
Обычн
|
0%
|
3
|
600
|
2
|
200
|
Сварка труб
|
Дунаев
|
Удовл.
|
Установка счетчиков
|
Дунаев
|
Удовл.
|
7
|
Балуев Т.Д.
|
Пенсион.
|
50%
|
3
|
600
|
4
|
400
|
Установка счетчиков
|
Дунаев
|
Плохое
|
Замена труб
|
Дунаев
|
Удовл
|
8
|
Юзов А.А.
|
Льгот
|
20%
|
1
|
200
|
2
|
200
|
Чистка канализации
|
Роев
|
Плохое
|
11
|
Лагунов П.Л.
|
Обычн.
|
0%
|
4
|
800
|
4
|
400
|
Замена труб
|
Роев
|
Удовлетв.
|
Установка счетчиков
|
Шитов
|
Удовлетв.
|
12
|
Петров П.П.
|
Пенсион.
|
50%
|
1
|
200
|
1
|
100
|
Замена батареи
|
Роев
|
Удовлетв.
|
15
|
Лосева Р.О.
|
Пенсион.
|
50%
|
3
|
600
|
4
|
400
|
Установка крана
|
Шитов
|
Плохое
|
Чистка труб
|
Дунаев
|
Удовлетв.
|
|
