Нормализација и подобрувања на дизајнот на базата

Функционална зависност

За делот полагање на основен испит, почнувам од делот на Нормализација и Функционална зависност.
Правиме анализа на базата во моментот и треба да провериме :
-> Дали секое поле е атомско? (1НФ)
-> Дали сите атрибути зависат целосно од примарниот клуч? (2НФ)
-> Дали има транзитивни зависности? (3НФ) A → B и B → C, тогаш A → C
Моментална ситуација на базата е следна :

При анализа ги следине сите табелиод базата и одлучуваме:
→ Кои полиња зависат од што?
→ Што го одредува што?

• Табела 1 → orders :
  

id_order → payment_method, status, delivery_price, total_price, invoice_code, order_date, id_customer
Ова значи дека сè што се однесува на некоја нарачка,притоа секој атрибут зависи од примарен клуч id_order.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 2 → customers:
  

id_customer → email, first_name, last_name, delivery_address, password, phone
id_customer е примарниот клуч па ги одредува сите други полиња што го опишуваат клиентот.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 3 → delivery:
  

id_delivery, delivery_address, status, id_order
Во оваа табела, id_delivery е примарниот клуч со кој добиваме инфо каде дадена нарачка и каков статус има притоа имаме id_order кој е надворешен клуч и не влијае на зависностите на другите атрибути.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 4 → contains:

quantity, size, price, id_order, id_stock
id_order + id_stock → quantity, size, price
Табелата contains е нормализирана во 2НФ и 3НФ и не треба декомпозиција иако имаме ваков спој за клуч.
Табелата contains го поврзува id_order со id_stock, и секоја нарачка ја означува количината на производот кој е поврзан со нарачката.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 5 → stock:

id_stock, id_product, size, quantity, price
Оваа табела чува податоци за секој продукт за да видиме со што располагаме.
Табелата е во 2НФ, затоа што сите атрибути зависат целосно од примарниот клуч (id_stock).
Заклучок: 1НФ - 2НФ - 3НФ Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 6 → products :

id_product, id_category, product_name, color, price, description, image_url
Табелата е во 2НФ, затоа што сите атрибути зависат целосно од id_product, кој е примарен клуч.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 7 → categories:

id_category, category_name
Табелата е во 2NF, затоа што сите атрибути зависат целосно од id_category, кој е примарен клуч.

• Клучни точки за базата :
  

orders → customers = еден корисник може да има повеќе нарачки
delivery → orders = секоја достава е поврзана со една нарачка
stock → products = еден артикл во магацин е поврзан за еден производ, но може да има повеќе варијации по големина и боја
contains ја врзува orders и stock = една нарачка може да содржи повеќе артикли, а еден артикл може да биде дел од повеќе нарачки

• Заклучок: 1НФ - 2НФ - 3НФ
  

Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

Update

Може да забележиме во постоечкиот модел дека имаме јасни ентитети:
customers, products, orders, stock, contains, categories, delivery.
За почеток, што би можеле да подобриме?

Приметуваме дека имаме повторување на delivery_address и во customers и во delivery.
Затоа можеби е подобро да се извлече addresses како посебна табела.

CREATE TABLE addresses (
    id_address INTEGER PRIMARY KEY,
    id_customer INTEGER,
    address TEXT,
    FOREIGN KEY (id_customer) REFERENCES customers(id_customer)
);

Ја додаваме колоната id_address

ALTER TABLE orders ADD COLUMN id_address INTEGER;

Потоа го додаваме надворешниот клуч

ALTER TABLE orders ADD CONSTRAINT fk_orders_address 
FOREIGN KEY (id_address) REFERENCES addresses(id_address);

Истата постапка ја правиме и за delivery
На крај правиме насочување на orders и delivery кон на id_address
наместо да ја чуваат адресата директно.
Ова овозможува еден корисник да има повеќе адреси и да ги избере при checkout.

Наредна измена е кај опцијата price се појавува во products, stock, и contains.
Може да има проблем овде бидејки products.price е основна цена а contains.price
ја претставува цената при купување на производт,кој можеби е на попуст. Затоа, правиме посебна табела discounts

CREATE TABLE discounts (
    id_discount INTEGER PRIMARY KEY,
    id_product INTEGER NOT NULL,
    size TEXT NOT NULL,
    discount_price DECIMAL NOT NULL,
    valid_from DATE,
    valid_to DATE,
    FOREIGN KEY (id_product) REFERENCES products(id_product)
);

А како што може да приметиме, додадовме нова табела која се вика payment_methods.
Ова е со цел наместоorders да чува payment_method како текст (Credit Card,PayPal...)
ние направивме да има id_payment_method (foreign key), додаваме foreign key constraint
па новата payment_methods табелата ги содржи сите можни методи,многу појасна и нормализирана.