Нормализация

Накратко: Нормализацията разделя данните в таблици така, че всеки факт да е записан на едно място, за да няма аномалии при промяна, добавяне и изтриване.

Проблем

Excel-ът дойде в базата

Помниш таблицата с поръчките от първия урок на курса? Някой я е пренесъл в базата както е: един ред за всеки продукт в поръчка, а до него датата, клиентът, имейлът му и градът.

flat_orders

• order_idinteger *
• ordered_attext *
• customertext *
• emailtext *
• citytext
• producttext *
• pricereal *
• quantityinteger *

SQLпразна база

SELECT * FROM flat_orders;

SELECT * FROM flat_orders;

▶ ИзпълниВърни заявкатаНова база

11 реда, а клиентите са трима. Името и имейлът на Мария са записани 6 пъти. Погледни и града ѝ в поръчка 6.

Идеята

Аномалии

Когато един факт е записан на много места, всяка промяна е риск. Тези проблеми се казват аномалии. Те са три вида.

При промяна. Мария сменя имейла си. Колегата го поправя само в последната ѝ поръчка:

SQLпразна база

UPDATE flat_orders SET email = 'maria.i@example.com'
WHERE order_id = 6;

SELECT DISTINCT customer, email FROM flat_orders
WHERE customer = 'Мария Иванова';

UPDATE flat_orders SET email = 'maria.i@example.com' WHERE order_id = 6; SELECT DISTINCT customer, email FROM flat_orders WHERE customer = 'Мария Иванова';

▶ ИзпълниВърни заявкатаНова база

Сега базата казва две неща наведнъж. Кой имейл е верният?

Виж го

Добавяне и изтриване

При добавяне. Магазинът започва да продава овесени ядки. Още никой не ги е поръчал. Къде да запишеш цената им?

SQLпразна база

INSERT INTO flat_orders (product, price)
VALUES ('Овесени ядки', 3.2);

INSERT INTO flat_orders (product, price) VALUES ('Овесени ядки', 3.2);

▶ ИзпълниВърни заявкатаНова база

Ред без поръчка не може да влезе. Продуктът съществува само докато някой го купи.

При изтриване. Елена отказва поръчка 4 и я трият. Това е единствената поръчка с тигана:

SQLпразна база

DELETE FROM flat_orders WHERE order_id = 4;

SELECT product, price FROM flat_orders
WHERE product = 'Тиган 28 см';

DELETE FROM flat_orders WHERE order_id = 4; SELECT product, price FROM flat_orders WHERE product = 'Тиган 28 см';

▶ ИзпълниВърни заявкатаНова база

Заедно с поръчката изчезна и това, че магазинът продава тиган за 24,90 €.

Идеята

Нормални форми

Подреждането на таблиците така, че всеки факт да е на едно място, се казва нормализация. Правилата са на стъпки, нормални форми. Всяка следваща включва предишната.

Първа (1НФ): една стойност в клетка. Без списъци като „веган, био“ и без колони tag1, tag2, tag3.

SQLпразна база

SELECT name, tags FROM products_sheet
WHERE tags = 'веган';

SELECT name, tags FROM products_sheet WHERE tags = 'веган';

▶ ИзпълниВърни заявкатаНова база

Само белият хляб, макар че веган са и пълнозърнестият хляб, ябълките и кафето. Пълнозърнестият е веган, но клетката му е „веган, био“. С LIKE '%веган%' ще го намериш, но не можеш да преброиш продуктите по етикет, да поправиш „био“ навсякъде или да не пуснеш правописна грешка. Етикетите отиват в таблица за връзка, както в първия урок на модула.

Втора (2НФ): всяка колона зависи от целия ключ. Има значение, когато ключът е от няколко колони:

order_lines

• PKorder_idinteger
• PKproduct_idinteger
• product_nametext *
• quantityinteger *

Името на продукта зависи само от product_id, не от поръчката. Ще се повтаря във всяка поръчка с този продукт. Мястото му е в products.

Трета (3НФ): колоните зависят само от ключа, а не от друга колона:

products

• PKidinteger
• nametext *
• category_idinteger *
• category_nametext *
• pricereal *

category_name се определя от category_id, не от продукта. Ако категорията се преименува, трябва да поправиш всички продукти в нея. Мястото на името е в categories.

По-точно, за любопитните

Кратката формула на трите форми е: всяка колона зависи „от ключа, от целия ключ и от нищо друго освен ключа“.

Има и по-високи форми (на Бойс и Код, четвърта, пета), но на практика таблица в трета форма обикновено вече няма тези аномалии.

flat_orders нарушава втората форма. Ключът на реда е поръчката и продуктът, а датата и клиентът зависят само от поръчката. Нарушава и третата: градът и имейлът зависят от клиента, не от поръчката.

Пробвай

Коя форма?

1. В order_lines ключът е (order_id, product_id), а product_name зависи само от product_id. Коя форма е нарушена?

ВторатаПърватаТретата

2. В products има category_id и category_name. Коя форма е нарушена?

ПърватаТретатаВтората

Провери

Идеята

Разделяне

Разделяш flat_orders на таблиците на магазина: customers, products, orders и order_items. Започваш с тези, които не сочат към никого.

INSERT може да вземе редовете от SELECT вместо от VALUES. С DISTINCT всеки продукт влиза веднъж:

products

• PKidinteger
• nametext *
• pricereal *

SQLпразна база

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  price REAL NOT NULL
);
INSERT INTO products (name, price)
SELECT DISTINCT product, price FROM flat_orders;

SELECT * FROM products;

CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, price REAL NOT NULL ); INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders; SELECT * FROM products;

▶ ИзпълниВърни заявкатаНова база

От 11 реда остават 6 продукта, всеки с id. Цената остава и в редовете на поръчките, защото това е цената, на която е купено. Защо това не е повторение, ще видим в следващия урок.

Виж го

Excel-ът е мръсен

Същото с клиентите:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext

SQLпразна база

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders;

▶ ИзпълниВърни заявкатаНова база

Грешка. В поръчка 6 градът на Мария е „Софя“. DISTINCT сравнява целия ред, затова вижда двама клиенти с един имейл, а UNIQUE не ги пуска. Това е добре: правилото хвана грешката, вместо да я пренесе в новата база.

Първо поправяш данните, после разделяш:

SQLпразна база

UPDATE flat_orders SET city = 'София' WHERE city = 'Софя';

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

SELECT * FROM customers;

UPDATE flat_orders SET city = 'София' WHERE city = 'Софя'; CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders; SELECT * FROM customers;

▶ ИзпълниВърни заявкатаНова база

Почти всеки пренос от Excel започва с такова почистване.

Пробвай

Поръчките

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *

Продуктите и клиентите вече са разделени. Напълни orders (id, customer_id, ordered_at) от flat_orders. Номерът на поръчката е order_id, а клиентът се намира по имейла.

SQLпразна база

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at TEXT NOT NULL
);

CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), ordered_at TEXT NOT NULL );

Провери▶ Изпълни

Упражнение

Две задачи

1. Остава таблицата за връзка между поръчките и продуктите.

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

Напълни order_items от flat_orders: номера на поръчката, id-то на продукта, броя и цената.

SQLпразна база

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price REAL NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price REAL NOT NULL, PRIMARY KEY (order_id, product_id) );

Провери▶ Изпълни

2. Сега отчетът от първия урок на курса отнема една заявка. Колко е похарчил всеки клиент?

Покажи името на всеки клиент и сбора на покупките му (броя по цената):

SQLпразна база

Провери▶ Изпълни

Тест

Провери се

1. Мария смени имейла си, а в таблицата той е на 40 реда. Колегата поправи 38. Как се казва това?

Аномалия при изтриванеАномалия при промянаАномалия при добавяне

2. Кое нарушава първата нормална форма?

Колона city, която понякога е NULLКолона tags с текст „веган, био“Таблица без колона id

3. Какво прави INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders?

Добавя по един ред за всяка различна двойка продукт и ценаДобавя ред в products за всяка поръчкаНищо, INSERT не може да ползва SELECT

4. Защо разделянето на Мариините поръчки спря с грешка UNIQUE?

Защото на два реда градът ѝ е написан различно и DISTINCT я видя като двама клиентиЗащото имейлът не може да е UNIQUEЗащото Мария има много поръчки

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: Ограничения: правила за данните · Външни ключове: връзки между таблиците · Видове връзки: 1:1, 1:N, M:N · ER диаграма: от описание до схема
• Продължава в: Естествен ключ и денормализация · Документни бази · Пекарната, етап 1: базата и таблиците
• Задачи по темата: Проектиране на база