Changes between Version 22 and Version 23 of normalization

Timestamp:

09/02/25 22:26:13 (8 weeks ago)

Author:

221046

Comment:

--

normalization

@@ -1 +1 @@
 = Нормализација на базата =
+== 1. Почетна релација (UNF) ==
 
-== Нормални форми по релации ==
+{{{
+Full(track_id, name, album_id, artist_id, media_type_id, genre_id, composer,
+milliseconds, bytes, price_id, value, date,
+invoice_line_id, invoice_id, quantity,
+customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id,
+employee_id, title, reports_to, birth_date, hire_date,
+address_info_id, address, city, state, country, postal_code, phone, fax, email,
+playlist_id, playlist_name, playlist_track_id)
+}}}
 
-== Почетна релација ==
-   * Сите атрибути се атомични
+== 2. Функционални зависности (FDs) ==
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''Full(track_id, name, album_id, artist_id, media_type_id, genre_id, composer, milliseconds, bytes, price_id, value, date, invoice_line_id, invoice_id, quantity, customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id, employee_id, title, reports_to, birth_date, hire_date, address_info_id, address, city, state, country, postal_code, phone, fax, email, playlist_id, playlist_name, playlist_track_id)'''||track_id, album_id, artist_id, media_type_id, genre_id, price_id, invoice_line_id, invoice_id, customer_id, employee_id, address_info_id, contact_id, playlist_id, playlist_track_id||Многу функционални зависности (track_id → {...}, album_id → {...}, artist_id → name, ...)||1NF||
+'''Track'''
 
+track_id → name, album_id, media_type_id, genre_id, composer, milliseconds, bytes
 
-== Основни ентитети==
+'''Price'''
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''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_id → value, date, track_id
 
+'''Album'''
 
-== Табела за цени==
+album_id → artist_id
 
+'''InvoiceLine'''
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''Price(price_id, value, date, track_id)'''||price_id (и можен (date, track_id))||price_id → {value, date, track_id}||BCNF||
+invoice_line_id → invoice_id, track_id, quantity
 
+'''Invoice'''
 
+invoice_id → customer_id
 
-== Фактури==
+'''Customer'''
 
+customer_id → first_name, last_name, company, support_rep_id, personal_info_id, contact_id
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''!InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)'''||invoice_line_id (и можен (invoice_id, track_id))||(invoice_line_id → {invoice_id, track_id, quantity})||BCNF||
-||'''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||
+'''Employee'''
 
+employee_id → title, reports_to, birth_date, hire_date
 
-== Контакт инфо ==
+'''AddressInfo'''
 
- 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)'''||customer_id||customer_id → {first_name, last_name, company, support_rep_id, personal_info_id, contact_id}||BCNF||
-||'''Employee(employee_id, first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id)'''||employee_id||employee_id → {first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id}||BCNF||
-||'''!AddressInfo(address_info_id, address, city, state, country, postal_code)'''||address_info_id||address_info_id → {address, city, state, country, postal_code}||BCNF||
-||'''Contact(contact_id, phone, fax, email)'''||contact_id||contact_id → {phone, fax, email}||BCNF||
+address_info_id → address, city, state, country, postal_code, phone, fax, email
 
+'''Playlist'''
 
-== отстрануваат повторувања на Track во Playlist ==
+playlist_id → playlist_name
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''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}||BCNF||
+'''PlaylistTrack'''
 
+playlist_track_id → playlist_id, track_id
 
-== Нормализирана база ==
+== 3. Прва нормална форма (1NF) ==
 
-||= Релација =||= Клуч(еви) =||= Функциски зависности (ФЗ) =||= Нормална форма =||
-||'''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 (и можен (date, track_id))||price_id → {value, date, track_id}||BCNF|| 
-||'''!InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)'''||invoice_line_id (и можен (invoice_id, track_id))||(invoice_line_id → {invoice_id, track_id, quantity})||BCNF|| 
-||'''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, personal_info_id, contact_id)'''||customer_id||customer_id → {first_name, last_name, company, support_rep_id, personal_info_id, contact_id}||BCNF||
-||'''Employee(employee_id, first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id)'''||employee_id||employee_id → {first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id}||BCNF||
-||'''!AddressInfo(address_info_id, address, city, state, country, postal_code)'''||address_info_id||address_info_id → {address, city, state, country, postal_code}||BCNF||
-||'''Contact(contact_id, phone, fax, email)'''||contact_id||contact_id → {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}||BCNF||
+Сите атрибути се атомарни → релацијата е во 1NF.
 
-== ЕР дијаграм по нормализација ==
-[[Image(Fianl Diagram Light.png, width=800, align=center)]]
+'''Примарен клуч (на Full):'''
+(invoice_line_id, playlist_track_id)
+(секој ред е уникатен по комбинацијата на фактурна линија и песна во плејлиста).
 
-== Заклучок ==
-  * Сите релации се во **BCNF**.
-  * Релациите **Price** и **!InvoiceLine** може да сметаме дека се во 3NF, бидејќи date, track_id се уникатни кај Price, invoice_id, track_id кај !InvoiceLine, во база не е сетирано дека мора да се уникатни, но за ова се осигураме деке е такa со соодветни тригери (**upsert_price** и **merge_invoice_line**).
-  * **Price** – нова теабела која ни овозможува чување на историј на цени.  
-  * Новите табели **!AddressInfo** и **Contact** овозможуваат отстранување на повторувања на адреси и контакти → уште почиста нормализација.
+== 4. Втора нормална форма (2NF) ==
 
+Ги одвојуваме зависностите од делови на составениот клуч:
 
+=== 4.1 Track ===
+{{{
+Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)
+PK: track_id
+}}}
+
+=== 4.2 Price ===
+{{{
+Price(price_id, value, date, track_id)
+PK: price_id
+FK: track_id → Track(track_id)
+}}}
+
+=== 4.3 Album ===
+{{{
+Album(album_id, artist_id)
+PK: album_id
+FK: artist_id → Artist(artist_id)
+}}}
+
+=== 4.4 Artist ===
+{{{
+Artist(artist_id, artist_name)
+PK: artist_id
+}}}
+
+=== 4.5 InvoiceLine ===
+{{{
+InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)
+PK: invoice_line_id
+FK: invoice_id → Invoice(invoice_id)
+FK: track_id → Track(track_id)
+}}}
+
+=== 4.6 Invoice ===
+{{{
+Invoice(invoice_id, customer_id)
+PK: invoice_id
+FK: customer_id → Customer(customer_id)
+}}}
+
+=== 4.7 Customer ===
+{{{
+Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)
+PK: customer_id
+FK: support_rep_id → Employee(employee_id)
+}}}
+
+=== 4.8 Employee ===
+{{{
+Employee(employee_id, title, reports_to, birth_date, hire_date)
+PK: employee_id
+}}}
+
+=== 4.9 AddressInfo ===
+{{{
+AddressInfo(address_info_id, address, city, state, country, postal_code, phone, fax, email)
+PK: address_info_id
+}}}
+
+=== 4.10 Playlist ===
+{{{
+Playlist(playlist_id, playlist_name)
+PK: playlist_id
+}}}
+
+=== 4.11 PlaylistTrack ===
+{{{
+PlaylistTrack(playlist_track_id, playlist_id, track_id)
+PK: playlist_track_id
+FK: playlist_id → Playlist(playlist_id)
+FK: track_id → Track(track_id)
+}}}
+
+== 5. Трета нормална форма (3NF) ==
+
+album_id → artist_id → Artist е одвоен.
+
+support_rep_id → Employee е FK, нема транзитивна зависност во Customer.
+
+Адресата е изнесена во AddressInfo.
+
+Сите не-клучни атрибути зависат директно од клучевите.
+
+✅ Сега сите релации се во 3NF.
+
+== Финален нормализиран модел (3NF) ==
+
+{{{
+Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)
+
+Price(price_id, value, date, track_id)
+
+Album(album_id, artist_id)
+
+Artist(artist_id, artist_name)
+
+InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)
+
+Invoice(invoice_id, customer_id)
+
+Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)
+
+Employee(employee_id, title, reports_to, birth_date, hire_date)
+
+AddressInfo(address_info_id, address, city, state, country, postal_code, phone, fax, email)
+
+Playlist(playlist_id, playlist_name)
+
+PlaylistTrack(playlist_track_id, playlist_id, track_id)
+}}}
 
 ** ← [wiki: Назад на почетна] **