Нормальные формы
Проектирование баз данных
Lis Sulmont
Curriculum Manager
Нормализация
Выявите повторяющиеся группы данных и создайте для них новые таблицы
Более формальное определение:
Цели нормализации:
- Оценить уровень избыточности в реляционной схеме
- Предоставить механизмы преобразования схем для устранения избыточности
1 Database Design, 2nd Edition, Adrienne Watt
Нормальные формы (НФ)
От наименее к наиболее нормализованной:
- Первая нормальная форма (1НФ)
- Вторая нормальная форма (2НФ)
- Третья нормальная форма (3НФ)
- Элементарная ключевая нормальная форма (EKNF)
- Нормальная форма Бойса–Кодда (BCNF)
$$
- Четвёртая нормальная форма (4НФ)
- Существенная кортежная нормальная форма (ETNF)
- Пятая нормальная форма (5НФ)
- Доменно-ключевая нормальная форма (DKNF)
- Шестая нормальная форма (6НФ)
1 https://en.wikipedia.org/wiki/Database_normalization
Правила 1НФ
- Каждая запись должна быть уникальной — без дублирующихся строк
- Каждая ячейка должна содержать одно значение
Исходные данные
| Student_id | Student_Email | Courses_Completed |
|------------|-----------------|----------------------------------------------------------|
| 235 | [email protected] | Introduction to Python, Intermediate Python |
| 455 | [email protected] | Cleaning Data in R |
| 767 | [email protected] | Machine Learning Toolbox, Deep Learning in Python |
В форме 1НФ
| Student_id | Student_Email |
|------------|-----------------|
| 235 | [email protected] |
| 455 | [email protected] |
| 767 | [email protected] |
| Student_id | Completed |
|------------|--------------------------|
| 235 | Introduction to Python |
| 235 | Intermediate Python |
| 455 | Cleaning Data in R |
| 767 | Machine Learning Toolbox |
| 767 | Deep Learning in Python |
2НФ
- Должна удовлетворять 1НФ И
- Если первичный ключ состоит из одного столбца
- то 2НФ выполняется автоматически
- Если первичный ключ составной
- то каждый неключевой столбец должен зависеть от всех ключей
- Если первичный ключ состоит из одного столбца
Исходные данные
| Student_id (PK) | Course_id (PK) | Instructor_id | Instructor | Progress |
|-----------------|----------------|---------------|---------------|----------|
| 235 | 2001 | 560 | Nick Carchedi | .55 |
| 455 | 2345 | 658 | Ginger Grant | .10 |
| 767 | 6584 | 999 | Chester Ismay | 1.00 |
В форме 2НФ
| Student_id (PK) | Course_id (PK) | Percent_Completed |
|-----------------|----------------|-------------------|
| 235 | 2001 | .55 |
| 455 | 2345 | .10 |
| 767 | 6584 | 1.00 |
| Course_id (PK) | Instructor_id | Instructor |
|----------------|---------------|---------------|
| 2001 | 560 | Nick Carchedi |
| 2345 | 658 | Ginger Grant |
| 6584 | 999 | Chester Ismay |
3НФ
- Удовлетворяет 2НФ
- Нет транзитивных зависимостей: неключевые столбцы не могут зависеть от других неключевых столбцов
Исходные данные
| Course_id (PK) | Instructor_id | Instructor | Tech |
|----------------|---------------|---------------|--------|
| 2001 | 560 | Nick Carchedi | Python |
| 2345 | 658 | Ginger Grant | SQL |
| 6584 | 999 | Chester Ismay | R |
В форме 3НФ
| Course_id (PK) | Instructor | Tech |
|----------------|---------------|--------|
| 2001 | Nick Carchedi | Python |
| 2345 | Ginger Grant | SQL |
| 6584 | Chester Ismay | R |
| Instructor_id | Instructor |
|---------------|---------------|
| 560 | Nick Carchedi |
| 658 | Ginger Grant |
| 999 | Chester Ismay |
Аномалии данных
Какие риски возникают при недостаточной нормализации?
1. Аномалия обновления
2. Аномалия вставки
3. Аномалия удаления
Аномалия обновления
Несогласованность данных из-за их избыточности при обновлении
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
Чтобы обновить email студента 520:
- Нужно изменить несколько записей, иначе возникнет несогласованность
- Пользователь должен знать об избыточности данных
Аномалия вставки
Невозможность добавить запись из-за отсутствия обязательных атрибутов
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
Невозможно добавить студента, который зарегистрировался, но ещё не записался ни на один курс
Аномалия удаления
Удаление записи приводит к непреднамеренной потере данных
| Student_ID | Student_Email | Enrolled_in | Taught_by |
|------------|-----------------|-------------------------|---------------------|
| 230 | [email protected] | Cleaning Data in R | Maggie Matsui |
| 367 | [email protected] | Data Visualization in R | Ronald Pearson |
| 520 | [email protected] | Introduction to Python | Hugo Bowne-Anderson |
| 520 | [email protected] | Arima Models in R | David Stoffer |
Если удалить студента 230, что произойдёт с данными о курсе Cleaning Data in R?
Аномалии данных
Какие риски возникают при недостаточной нормализации?
1. Аномалия обновления
2. Аномалия вставки
3. Аномалия удаления
Чем выше степень нормализации базы данных, тем меньше риск аномалий данных
Не забудьте про недостатки нормализации, рассмотренные в предыдущем видео
Давайте потренируемся!
Проектирование баз данных
