E-R диаграммы и нормализация
| English | Русский |
|---|---|
| entity-relationship diagram/ˈentɪti rɪˈleɪʃənʃɪp ˈdaɪəɡræm/ | диаграмма «сущность-связь» |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | нормализация |
| entity/ˈentɪti/ | сущность |
| cardinality/ˌkɑːdɪˈnælɪti/ | кардинальностью |
| one-to-many/wʌn tə ˈmeni/ | один-ко-многим |
| many-to-many/ˈmeni tə ˈmeni/ | многие-ко-многим |
| link table/lɪŋk ˈteɪbl/ | связующая таблица |
| normal forms/ˈnɔːml fɔːmz/ | нормальные формы |
| atomic/əˈtɒmɪk/ | атомная |
| transitive dependency/ˈtrænsɪtɪv dɪˈpendənsi/ | транзитивная зависимость |
Сорок строк, чтобы изменить один телефонный номер
- Школа ведет список зачисленных студентов в одной электронной таблице. Каждая строка соответствует одному студенту на одном курсе, и каждая строка также содержит классного руководителя студента и телефонный номер этого руководителя.
- Классный руководитель меняет свой номер. Приходится редактировать сорок строк. Тридцать девять исправлены. Остальную часть года один список курсов звонит незнакомому человеку.
- Ничего не было напечатано с опечаткой. Ошибка была в проектировании: факт, относящийся к классному руководителю, был сохранен отдельно для каждого зачисления, поэтому он мог быть верным в одном месте и неверным в другом.
- Этот урок посвящён двум инструментам проектирования, которые предотвращают это: диаграмме «сущность-связь», которая отображает структуру, и нормализации, которая устраняет повторения.
Диаграммы «сущность-связь»
- Диаграмма «сущность-связь» (E-R диаграмма) документирует проект базы данных: каждая сущность обозначается прямоугольником, каждая связь — линией между двумя прямоугольниками, а кардинальность отмечается на каждом конце.
- В нотации «воронья лапка» одна черта означает «один», а трехзубчатая лапка означает «многие». Читайте каждую линию в обоих направлениях: каждый клиент размещает много заказов; каждый заказ размещен одним клиентом.
- Диаграмма рисуется до создания любой таблицы, а связи на ней становятся внешними ключами.
Один прямоугольник на сущность, одна линия на связь
Черта для «один», лапка для «многие»
На E-R диаграмме количество одной сущности, которое может быть связано с одной сущностью другой, отмеченное на каждом конце линии, называется ____.
Кардинальность — это один или многие на каждом конце; нотация «воронья лапка» рисует черту для одного и лапку для многих.
Три вида связей
- «Один к одному» (1:1): у каждого члена клуба одна библиотечная карта, и каждая карта принадлежит одному члену. Редко встречается; две сущности часто объединяются в одну таблицу.
- «Один ко многим» (1:M): один клиент размещает много заказов; каждый заказ принадлежит одному клиенту. Реализуется путем внесения первичного ключа со стороны «один» в таблицу со стороны «многие» в качестве внешнего ключа.
- «Многие ко многим» (M:N): студент посещает много курсов, а на курсе учится много студентов. Не может быть реализована напрямую; требуется связующая таблица.
Каждый клиент может сделать много заказов, но каждый заказ принадлежит одному клиенту. Эта связь является:
Один клиент → много заказов, каждый заказ → один клиент: связь один-ко-многим.
Соотнесите каждую связь с её кардинальностью.
1:1 каждая сторона имеет одну; 1:M одна сторона имеет много; M:N обе стороны имеют много (требуется соединительная таблица).
Разбор примера: постройте E-R диаграмму
- В школе есть преподаватели, классы и ученики. Каждый преподаватель ведет много классов; каждый класс ведет один преподаватель. В каждом классе много учеников; каждый ученик учится в одном классе. Ученики могут участвовать во многих кружках, а в каждом кружке много учеников.
- Четыре прямоугольника:
TEACHER,CLASS,STUDENT,CLUB.TEACHER—CLASS— один ко многим, лапка находится уCLASS.CLASS—STUDENT— один ко многим, лапка находится уSTUDENT.STUDENT—CLUB— многие ко многим, лапки находятся на обоих концах. - Отметки: наличие каждой сущности, наличие каждой связи и правильный символ кардинальности на каждом конце. Линия без символов оценивается как половина ответа.
Связующие таблицы
- Связь «многие ко многим» разбивается на две связи «один ко многим» через связующую таблицу, которая содержит два внешних ключа.
ENROLMENT(StudentID, CourseID, EnrolmentDate): у одного студента много записей о зачислении, на одном курсе много записей о зачислении, а каждая строка соответствует одному студенту на одном курсе. Его первичный ключ является составным из двух внешних ключей.- Данные о самом сочетании, дата, оценка помещаются в связующую таблицу; данные о студенте или курсе остаются в их собственных таблицах.
Одна связь «многие ко многим» становится двумя связями «один ко многим»
Как реализуется связь «многие-ко-многим» в реляционной базе данных?
Соединительная (переходная) таблица содержит внешний ключ к каждой стороне, превращая связь M:N в две связи 1:M.
ENROLMENT(StudentID, CourseID, EnrolmentDate) — это соединительная таблица. Какие утверждения верны? Выберите все подходящие варианты.
Соединительная таблица хранит пару и факты о ней. Собственные данные студента остаются в STUDENT, иначе они будут повторяться в каждой записи об обучении.
Нормализация
- Нормализация организует таблицы так, чтобы каждый факт хранился ровно один раз, сокращая избыточность и несоответствия. Она проходит через нормальные формы последовательно: первую, вторую, третью.
- Процедура: найдите сущности и их атрибуты; выберите первичный ключ для каждой; удалите повторяющиеся группы и неатомные значения (1NF); удалите атрибуты, зависящие только от части составного ключа (2NF); удалите атрибуты, зависящие от другого неключевого атрибута (3NF); добавьте внешние ключи для связей.
- Цена этого процесса — больше таблиц и больше джоинов. На экзамене требуется 3NF.
Лабораторная работа по сервисам баз данных
Посмотрите, как СУБМ преобразует запрос в безопасный доступ к общим данным.
Расставьте нормальные формы в порядке их применения.
Вы достигаете 3NF, проходя через 1NF, затем 2NF — каждая опирается на предыдущую.
Основная цель нормализации — это:
Нормализация до 3NF сохраняет каждый факт один раз, устраняя аномалии обновления/вставки/удаления (за счет большего числа соединений).
Первая нормальная форма
- Таблица находится в 1NF, когда каждое поле содержит одно атомное значение, нет повторяющихся групп и есть первичный ключ.
STUDENT(StudentID, Name, Phone)сPhone, содержащим0123, 0456, не является атомным. УSTUDENT(StudentID, Name, Course1, Course2, Course3)есть повторяющаяся группа.- Исправьте оба случая, переместив повторяющиеся данные в отдельную таблицу с одной строкой на значение:
STUDENT_PHONE(StudentID, Phone),ENROLMENT(StudentID, CourseID).
Поле Phone содержит "0123, 0456" для одного студента. К какой нормальной форме нарушена таблица?
Два значения в одной ячейке — нарушение 1NF. Перенесите номера в STUDENT_PHONE(StudentID, Phone), по одному на строку.
Разбор примера: вторая нормальная форма
ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity)имеет составной первичный ключ(OrderID, ProductID). Он находится во 2NF?- Проверьте каждое неключевое поле относительно всего ключа.
Quantityзависит от обоихOrderIDиProductID: какой заказ, какой продукт. Хорошо. CustomerIDиCustomerNameзависят только отOrderID, т.е. только от части ключа: это частичная зависимость, поэтому таблица не находится во 2NF. Разделите её:ORDER(OrderID, CustomerID, CustomerName)иORDER_LINE(OrderID, ProductID, Quantity).
Разбор примера: третья нормальная форма
- Находится ли
ORDER(OrderID, CustomerID, CustomerName)в 3NF? - Таблица находится в 3NF, если она находится во 2NF и каждое неключевое поле зависит только от первичного ключа, а не от другого неключевого поля.
CustomerNameзависит отCustomerID, который не является ключом: это транзитивная зависимость, поэтому таблица не находится в 3NF. - Разделите снова:
ORDER(OrderID, CustomerID)иCUSTOMER(CustomerID, CustomerName), гдеCustomerIDявляется внешним ключом. Название теперь хранится один раз, сколько бы заказов ни сделал клиент.
Каждая форма удаляет один вид зависимости
Нормализация до 3NF сохраняет каждый факт один раз и устраняет аномалии обновления, ценой появления большего количества таблиц и соединений.
Этот компромисс — чистота данных против количества JOIN-операций — объясняет, почему 3NF является стандартной целью нормализации.
Объяснение, почему таблица находится или не находится в 3NF
- Не находится в 3NF: назовите зависимость. «
TEACHERне находится в 3NF, потому что неключевой атрибутDepartmentNameзависит от неключевого атрибутаDepartmentID, а не от первичного ключа.» - Находится в 3NF: охватите все три условия. «Каждый атрибут атомен, нет повторяющихся групп; нет частичной зависимости от части ключа; каждый неключевой атрибут зависит только от первичного ключа, без транзитивной зависимости.»
- Затем, если требуется, приведите нормализованные таблицы в стандартной нотации с отмеченными внешними ключами.
В таблице TEACHER(TeacherID, Name, DepartmentID, DepartmentName) почему она не соответствует 3NF?
Транзитивная зависимость. Выделите DEPARTMENT(DepartmentID, DepartmentName) и оставьте DepartmentID в TEACHER в качестве внешнего ключа.
Потерянные баллы
- 2NF — это вопрос только тогда, когда первичный ключ составной. Ключ из одного поля не может иметь частичную зависимость.
- 3NF требует соблюдения 2NF. Указывайте это при обосновании.
- Атомарность означает одно значение в ячейке. «Два номера телефона в одном поле» — это нарушение 1NF, а не 3NF.
- Называть нормальную форму недостаточно; нужно называть зависимость. Наличие дополнительных таблиц после нормализации — это цель, а не недостаток.
Вы поняли
- E-R диаграмма отображает сущности прямоугольниками, а связи — линиями с кардинальностью на каждом конце: 1:1, 1:M, M:N
- Связь «многие-ко-многим» хранится через таблицу-связку двух внешних ключей, первичным ключом которой является их составной ключ
- 1NF: атомарные значения, отсутствие повторяющихся групп, наличие первичного ключа · 2NF: отсутствие частичной зависимости от части составного ключа · 3NF: отсутствие транзитивной зависимости между атрибутами, не входящими в ключ
- Обосновать, назвав зависимость, затем указать разбитые таблицы и их внешние ключи