Changes between Version 1 and Version 2 of normalization

Timestamp:

08/31/25 16:54:20 (2 months ago)

Author:

221046

Comment:

--

normalization

@@ -1 @@
-= Нормализација
+= Нормализација на базата =
 
-Функциски зависности (FD)
-FD = {
+== Нормални форми по релации ==
 
-# Track
-track_id → name, album_id, media_type_id, genre_id, composer, milliseconds, bytes
+||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
+||'''Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)'''||track_id||track_id → {name, album_id, media_type_id, genre_id, composer, milliseconds, bytes}||BCNF||
+||'''Album(album_id, title, artist_id)'''||album_id||album_id → {title, artist_id}||BCNF||
+||'''Artist(artist_id, name)'''||artist_id||artist_id → name||BCNF||
+||'''Genre(genre_id, name)'''||genre_id||genre_id → name||BCNF||
+||'''MediaType(media_type_id, name)'''||media_type_id||media_type_id → name||BCNF||
+||'''Price(price_id, value, date, track_id)'''||price_id||price_id → {value, date, track_id}||BCNF (ако price_id е surrogate); 3NF ако клуч е (track_id, date)||
+||'''InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)'''||invoice_line_id (и можен (invoice_id, track_id))||invoice_line_id → {invoice_id, track_id, quantity}; (invoice_id, track_id) → quantity||BCNF (со surrogate); 3NF (ако композитен клуч)||
+||'''Invoice(invoice_id, customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total)'''||invoice_id||invoice_id → {customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total}||BCNF||
+||'''Customer(customer_id, first_name, last_name, company, support_rep_id, address, city, state, country, postal_code, phone, fax, email)'''||customer_id||customer_id → {first_name, last_name, company, support_rep_id, address, city, state, country, postal_code, phone, fax, email}||BCNF||
+||'''Employee(employee_id, first_name, last_name, title, reports_to, birth_date, hire_date, address, city, state, country, postal_code, phone, fax, email)'''||employee_id||employee_id → {first_name, last_name, title, reports_to, birth_date, hire_date, address, city, state, country, postal_code, phone, fax, email}||BCNF||
+||'''Playlist(playlist_id, name)'''||playlist_id||playlist_id → name||BCNF||
+||'''PlaylistTrack(playlist_track_id, playlist_id, track_id)'''||playlist_track_id (и можен (playlist_id, track_id))||playlist_track_id → {playlist_id, track_id}; (playlist_id, track_id) → playlist_track_id||BCNF||
 
-# Album
-album_id → title, artist_id
-
-# Artist
-artist_id → name
-
-# Genre
-genre_id → name
-
-# Media Type
-media_type_id → name
-
-# Customer (со враќање на personal info атрибути)
-customer_id → first_name, last_name, company, support_rep_id,
-               address, city, state, country, postal_code, phone, fax, email
-
-# Employee (со враќање на personal info атрибути)
-employee_id → first_name, last_name, title, reports_to, birth_date, hire_date,
-               address, city, state, country, postal_code, phone, fax, email
-
-# Invoice
-invoice_id → customer_id, invoice_date, billing_address, billing_city, billing_state,
-              billing_country, billing_postal_code, total
-
-# InvoiceLine
-invoice_line_id → invoice_id, track_id, quantity
-
-# Playlist
-playlist_id → name
-
-# PlaylistTrack
-playlist_track_id → playlist_id, track_id
-
-# Price
-price_id → value, date, track_id
-
-}
-Канонска покривање (Fc)
-Fc = {
-
-track_id → name,
-track_id → album_id,
-track_id → media_type_id,
-track_id → genre_id,
-track_id → composer,
-track_id → milliseconds,
-track_id → bytes,
-
-album_id → title,
-album_id → artist_id,
-
-artist_id → name,
-
-genre_id → name,
-
-media_type_id → name,
-
-customer_id → first_name,
-customer_id → last_name,
-customer_id → company,
-customer_id → support_rep_id,
-customer_id → address,
-customer_id → city,
-customer_id → state,
-customer_id → country,
-customer_id → postal_code,
-customer_id → phone,
-customer_id → fax,
-customer_id → email,
-
-employee_id → first_name,
-employee_id → last_name,
-employee_id → title,
-employee_id → reports_to,
-employee_id → birth_date,
-employee_id → hire_date,
-employee_id → address,
-employee_id → city,
-employee_id → state,
-employee_id → country,
-employee_id → postal_code,
-employee_id → phone,
-employee_id → fax,
-employee_id → email,
-
-invoice_id → customer_id,
-invoice_id → invoice_date,
-invoice_id → billing_address,
-invoice_id → billing_city,
-invoice_id → billing_state,
-invoice_id → billing_country,
-invoice_id → billing_postal_code,
-invoice_id → total,
-
-invoice_line_id → invoice_id,
-invoice_line_id → track_id,
-invoice_line_id → quantity,
-
-playlist_id → name,
-
-playlist_track_id → playlist_id,
-playlist_track_id → track_id,
-
-price_id → value,
-price_id → date,
-price_id → track_id
-}
-
+== Заклучок ==
+  * Сите релации се барем во **3-та нормална форма (3NF)**.
+  * Повеќето се дури во **BCNF**.
+  * Единствени забелешки:
+    * InvoiceLine – зависи дали дозволуваш повеќе исти песни во иста фактура.
+    * Price – зависи дали цените се временски зависни (во тој случај клуч е (track_id, date)).