 |
Урок 16: Просто о нормальных формах
|
|
|
|
Урок 16: Просто о нормальных формах
НФ – процесс изменения структуры таблиц.
Первая нормальная форма
Первая нормальная форма – это когда у нас все данные атомарны (и поля, и данные в этих полях)
Пример:
| mysql> CREATE DATABASE users_db; | Создадим базу данных |
| mysql> USE users_db; | |
| mysql> CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL ); | |
| mysql> INSERT INTO users VALUES(NULL, 'Петр'); mysql> INSERT INTO users VALUES(NULL, 'Игорь'); mysql> INSERT INTO users VALUES(NULL, 'Роман'); | |
| mysql> CREATE TABLE interests ( interest_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, user_id INT, FOREIGN KEY(user_id) REFERENCES users(user_id) ); | |
| mysql> INSERT INTO users VALUES(NULL, 'Собаки', 1); mysql> INSERT INTO users VALUES(NULL, 'SQL', 1); mysql> INSERT INTO users VALUES(NULL, 'Кошки', 1); mysql> INSERT INTO users VALUES(NULL, 'Музыка', 3); |
Теперь объединим данные на основе внутреннего соединения
| mysql> SELECT u. name, i. name FROM users u INNER JOIN interests i ON u. user_id=i. user_id; |
| name | name |
| Петр | Собаки |
| Петр | SQL |
| Игорь | Кошки |
| Роман | Музыка |
Вторая нормальная форма
Вторая нормальная форма – это отсутствие частичных функциональных зависимостей
|
Таблица «super_heroes» | ||||
| name | power | initials | enemy_id | enemy_city |
|
Таблица «enemies» | |
| enemy_id | name |
Опытный проектировщик БД сразу увидит, где у меня ошибка, т. к. я их специально сделал. На этом примере очень хорошо можно показать приведение БД ко 2-ой и 3-й НФ.
Давайте посмотрим и логически попробуем прикинуть в чем у нас может быть проблема?
- name и power является первичным ключом, т. к. это составной первичный ключ
- enemy_id также является первичным ключом.
Поле initials зависит от name, но не от power – это не хорошо! Решение проблемы: создаем новую таблицу и называем ее initials.
|
|
|
| Таблица «Initials» | |
| sh_id | initials |
| С. Т. | |
|
Таблица «super_heroes» | |||||
| sh_id | name | power | initials | enemy_id | enemy_city |
|
UNIQUE | |||||
| Супер-Турник | |||||
Третья нормальная форма
Третья нормальная форма – очень хитрая, но не такая уж и сложная, как казалось бы. Это отсутствие транзитивных функциональных зависимостей.
Пример: Транзитивной функциональной зависимости:
|
Таблица «super_heroes» | ||||
| name | power | initials | enemy_id | enemy_city |
| Москва | ||||
| Нью-Йорк | ||||
| Вашингтон | ||||
Вся проблема в том, что у нас просто не ключевое поле зависит от другого не ключевого поля. Вот и все! И это называют транзитивной функциональной зависимостью.
Решение проблемы:
Переносим столбец enemy_city в таблицу «enemies». В таком случае у нас никаких проблем не будет. И все 3 таблицы у нас в третьей нормальной форме.
|
Таблица «enemies» | ||
| enemy_id | name | enemy_city |
| Зло | Москва | |
| Жесть | Урюпинск | |
Практика:
| mysql> DROP DATABASE users_db; | Для начала удалим старую БД users_db |
| mysql> CREATE DATABASE super_heroes_db; | |
| mysql> USE super_heroes_db; | |
| mysql> CREATE TABLE super_heroes ( sg_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, power VARCHAR(15) NOT NULL ); | |
| mysql> CREATE TABLE initials ( sh_id INT PRIMARY KEY, initial VARCHAR(5) NOT NULL, FOREIGN KEY(sh_id) REFERENCES super_heroes(sh_id) ); | |
| mysql> CREATE TABLE enemies ( enemy_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, city VARCHAR(15) NOT NULL ); | |
| mysql> ALTER TABLE super_heroes ADD COLUMN enemy_id INT, ADD FOREIGN KEY(enemy_id) REFERENCES enemies(enemy_id); |
Все три таблицы в 3НФ. Давайте дополним ее двумя значениями в каждой таблице и выведем их.
| mysql> INSERT INTO super_heroes VALUES(NULL, 'Супер-Турник', 'Мускулы', NULL); mysql> INSERT INTO super_heroes VALUES(NULL, 'Бэтмен', 'Гаджеты', NULL); |
| mysql> INSERT INTO initials VALUES(1, 'С. Т. '); mysql> INSERT INTO initials VALUES(1, 'Бэт'); |
| mysql> INSERT INTO enemies VALUES(NULL, 'Злыдень', 'Москва'); mysql> INSERT INTO enemies VALUES(NULL, 'Ленивец', 'Олимп'); |
|
|
|
А теперь нам надо назначить наших злодеев нашим супергероям:
| mysql> UPDATE super_heroes SET enemy_id = 1 WHERE sh_id = 1; mysql> UPDATE super_heroes SET enemy_id = 2 WHERE sh_id = 2; |
Выбрать имена супергероев, а также их силу, их инициалы и их врагов.
| mysql> SELECT sh. name, sh. power, i. initial, e. name FROM super_heroes sh INNER JOIN enemies e USING(e. enemy_id) INNER JOIN initials i USING(sh_id); |
| name | power | initial | name |
| Супер-Турник | Мускулы | С. Т. | Злыдень |
| Бэтмен | Гаджеты | Бэт | Ленивец |
Урок 17: Представления
Чтобы раскрыть эту тему, нужно иметь под рукой БД. Для этого создадим БД.
| mysql> CREATE DATABASE people_db; | |||||||||||||||||
| mysql> USE people_db; | |||||||||||||||||
| mysql> CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(20) NOT NULL, lname VARCHAR(20), email VARCHAR(30) NOT NULL UNIQUE ); | mysql> INSERT INTO users VALUES(NULL, 'Петр', 'Радько', 'test@mail. ru'); mysql> INSERT INTO users VALUES(NULL, 'Роман', 'Попов', 'romp@mail. ru'); mysql> INSERT INTO users VALUES(NULL, 'Евгений', 'Оливье', 'evgeni@mail. ru');
| ||||||||||||||||
| Устанавливаем ключ 1 к 1 mysql> CREATE TABLE private_inf ( id INT PRIMARY KEY, income INT NOT NULL DEFAULT 0, FOREIGN KEY(id) REFERENCES users(id) ); | mysql> INSERT INTO private_inf VALUES(1, 20000); mysql> INSERT INTO private_inf VALUES(2, 19000); mysql> INSERT INTO private_inf VALUES(3, 100);
|
Давайте представим, что у вас не 3 записи в каждой из этих таблиц, а 1000 записей и вы должны регулярно производить и писать одну и ту же команду. Например,
| mysql> SELECT u. fname, u. lname, p. income FROM users u INNER JOIN private_inf p ON u. id=p. id WHERE p. income > (SELECT AVG(income) FROM private_inf); |
|
И как вы видите, мы с вами соединили три поля и сделали так, чтобы у нас с вами выводились богачи. А теперь представьте, что вам раз 15 приходится вводить эту штуку (или даже больше). Вы, естественно, задолбаетесь это делать. Можете скопировать это в файл, а потом его успешно потерять. Есть более простой выход. Называется он представление.
NB: Представления создаются для удобного вывода данных.
Мы пишем: CREATE VIEW maxIncome AS и наш супер-сложный запрос
Например, CREATE VIEW maxIncome AS SELECT u. fname, u. lname, p. income FROM users u
INNER JOIN private_inf p ON u. id=p. id WHERE p. income > (SELECT AVG(income) FROM private_inf);
SELECT * FROM maxIncome; и мы получим то же самое:
|
|
|
| fname | lname | income |
| Петр | Радько | |
| Роман | Попов |
Если вы хотите удалить представление, то вы пишете:
DROP VIEW maxIncome;
|
|
|
