Normalization
De-normalized database form
Процесот на нормализација започнува од една унифицирана денормализирана релација R која ги содржи сите атрибути од концептуалниот модел на BiblioPremium, како сите податоци да се чуваат во една единствена табела.
Бидејќи во унифицираната релација не смее да има дупликати на имиња на атрибути, атрибутите со исти или слични имиња се именувани со префикс според ентитетот или релацијата на која припаѓаат.
За M:N релациите се користат посебни идентификатори според улогата во која учествуваат. На пример, жанрот поврзан со книга и жанрот што го преферира корисникот се два различни факти, па се претставуваат со kniga_zhanr_id и preferiran_zhanr_id. Истото важи за категориите и расположенијата.
R( korisnik_id, korisnik_ime, korisnik_prezime, korisnik_email, korisnik_lozinka, korisnik_datum_registracija, korisnik_uloga, naracka_id, naracka_datum, naracka_status, naracka_vkupna_cena, plakjanje_id, plakjanje_datum, plakjanje_iznos, plakjanje_nacin, plakjanje_status, kniga_id, kniga_naslov, kniga_isbn, kniga_opis, kniga_korica_url, kniga_godina_izdavanje, kniga_kolicina_na_zaliha, kniga_cena, avtor_id, avtor_ime, avtor_prezime, avtor_biografija, kniga_zhanr_id, kniga_zhanr_naziv, kniga_zhanr_opis, kniga_kategorija_id, kniga_kategorija_naziv, kniga_kategorija_opis, kniga_raspolozenie_id, kniga_raspolozenie_naziv, kniga_raspolozenie_opis, sodrzi_kolicina, sodrzi_edinechna_cena, omilena_kniga_id, omileni_datum_dodavanje, preferiran_zhanr_id, preferiran_zhanr_naziv, preferiran_zhanr_opis, preferira_zhanr_datum_dodavanje, preferirana_kategorija_id, preferirana_kategorija_naziv, preferirana_kategorija_opis, preferira_kategorija_datum_dodavanje, izbrano_raspolozenie_id, izbrano_raspolozenie_naziv, izbrano_raspolozenie_opis, izbira_raspolozenie_datum_izbor )
Во унифицираната релација се опфатени податоците за корисниците, книгите, авторите, жанровите, категориите, расположенијата, нарачките и плаќањата, како и атрибутите на M:N релациите SODRZI, OMILENI, PREFERIRA_ZHANR, PREFERIRA_KATEGORIJA и IZBIRA_RASPOLOZENIE.
Релациите NAPISANA_OD, IMA_ZHANR, IMA_KATEGORIJA и POVRZANA_SO немаат сопствени описни атрибути. Тие претставуваат факти за поврзување помеѓу соодветните ентитети.
Functional dependencies
Од глобалното множество на атрибути се определува следното канонично покривање на функционалните зависности што важат во унифицираната релација R.
FD1: korisnik_id → korisnik_ime, korisnik_prezime, korisnik_email, korisnik_lozinka, korisnik_datum_registracija, korisnik_uloga FD2: korisnik_email → korisnik_id FD3: naracka_id → naracka_datum, naracka_status, naracka_vkupna_cena, korisnik_id FD4: plakjanje_id → plakjanje_datum, plakjanje_iznos, plakjanje_nacin, plakjanje_status, naracka_id FD5: kniga_id → kniga_naslov, kniga_isbn, kniga_opis, kniga_korica_url, kniga_godina_izdavanje, kniga_kolicina_na_zaliha, kniga_cena FD6: kniga_isbn → kniga_id FD7: avtor_id → avtor_ime, avtor_prezime, avtor_biografija FD8: kniga_zhanr_id → kniga_zhanr_naziv, kniga_zhanr_opis FD9: kniga_kategorija_id → kniga_kategorija_naziv, kniga_kategorija_opis FD10: kniga_raspolozenie_id → kniga_raspolozenie_naziv, kniga_raspolozenie_opis FD11: naracka_id, kniga_id → sodrzi_kolicina, sodrzi_edinechna_cena FD12: korisnik_id, omilena_kniga_id → omileni_datum_dodavanje FD13: preferiran_zhanr_id → preferiran_zhanr_naziv, preferiran_zhanr_opis FD14: korisnik_id, preferiran_zhanr_id → preferira_zhanr_datum_dodavanje FD15: preferirana_kategorija_id → preferirana_kategorija_naziv, preferirana_kategorija_opis FD16: korisnik_id, preferirana_kategorija_id → preferira_kategorija_datum_dodavanje FD17: izbrano_raspolozenie_id → izbrano_raspolozenie_naziv, izbrano_raspolozenie_opis FD18: korisnik_id, izbrano_raspolozenie_id → izbira_raspolozenie_datum_izbor
FD1-FD10 ги опишуваат главните функционални зависности на основните податоци за корисници, нарачки, плаќања, книги, автори, жанрови, категории и расположенија.
FD11-FD18 ги опишуваат зависностите што произлегуваат од врските со сопствени атрибути и од различните улоги на жанровите, категориите и расположенијата.
FD2 важи бидејќи email адресата на корисникот е единствена, а FD6 бидејќи ISBN е единствен за конкретно издание на книга.
Релациите NAPISANA_OD, IMA_ZHANR, IMA_KATEGORIJA и POVRZANA_SO немаат сопствени не-клучни атрибути, па не воведуваат дополнителни нетривијални функционални зависности кон описни атрибути.
Candidate keys and primary key
При определувањето на кандидатскиот клуч мора да бидат опфатени независните факти од M:N релациите, бидејќи тие не можат да се изведат само преку функционалните зависности на основните ентитети.
Еден кандидатски клуч за унифицираната релација е:
K = {
plakjanje_id,
kniga_id,
avtor_id,
kniga_zhanr_id,
kniga_kategorija_id,
kniga_raspolozenie_id,
omilena_kniga_id,
preferiran_zhanr_id,
preferirana_kategorija_id,
izbrano_raspolozenie_id
}
Од plakjanje_id преку FD4 се добива naracka_id, а од naracka_id преку FD3 се добива korisnik_id.
Потоа:
- од korisnik_id преку FD1 се добиваат сите атрибути на корисникот;
- од naracka_id преку FD3 се добиваат сите атрибути на нарачката;
- од plakjanje_id преку FD4 се добиваат сите атрибути на плаќањето;
- од kniga_id преку FD5 се добиваат сите атрибути на книгата;
- од avtor_id преку FD7 се добиваат атрибутите на авторот;
- од kniga_zhanr_id преку FD8 се добиваат атрибутите на жанрот на книгата;
- од kniga_kategorija_id преку FD9 се добиваат атрибутите на категоријата на книгата;
- од kniga_raspolozenie_id преку FD10 се добиваат атрибутите на расположението поврзано со книгата;
- од {naracka_id, kniga_id} преку FD11 се добиваат атрибутите на SODRZI;
- од {korisnik_id, omilena_kniga_id} преку FD12 се добива датумот на додавање во омилени;
- од preferiran_zhanr_id преку FD13 и FD14 се добиваат податоците за преферираниот жанр и датумот на додавање;
- од preferirana_kategorija_id преку FD15 и FD16 се добиваат податоците за преферираната категорија и датумот;
- од izbrano_raspolozenie_id преку FD17 и FD18 се добиваат податоците за избраното расположение и датумот на избор.
Следствено, K+ ги содржи сите атрибути на R.
Поради алтернативните зависности korisnik_email → korisnik_id и kniga_isbn → kniga_id, во соодветните проекции постојат и алтернативни клучеви.
За примарен клуч на почетната унифицирана релација се избира K, бидејќи се состои од стабилни идентификатори.
Initial normal form
Сите атрибути во R се атомични. Повеќекратните автори, жанрови, категории, расположенија и останатите M:N врски се претставуваат преку повеќе редови, а не преку повеќе вредности во едно поле.
Затоа R е во 1NF.
R не е во 2NF бидејќи постојат не-клучни атрибути што зависат само од дел од сложениот клуч. На пример:
kniga_id → kniga_naslov, kniga_isbn, kniga_opis, ... avtor_id → avtor_ime, avtor_prezime, avtor_biografija kniga_zhanr_id → kniga_zhanr_naziv, kniga_zhanr_opis
1NF decomposition
R веќе е во 1NF бидејќи сите атрибути се атомични и не постојат повторувачки групи во едно поле.
Затоа во овој чекор не е потребна декомпозиција.
2NF decomposition
R не е во 2NF поради парцијалните зависности од делови на сложениот кандидатски клуч.
Во секој чекор зависноста X → Y се издвојува во посебна релација. Декомпозицијата е без загуба кога заедничките атрибути содржат клуч на една од добиените релации.
Step 2.1 - Users
Problem:
korisnik_id → korisnik_ime, korisnik_prezime, korisnik_email, korisnik_lozinka, korisnik_datum_registracija, korisnik_uloga
Result:
KORISNICI( korisnik_id, korisnik_ime, korisnik_prezime, korisnik_email, korisnik_lozinka, korisnik_datum_registracija, korisnik_uloga )
Во KORISNICI важат FD1 и FD2.
Кандидатски клучеви се korisnik_id и korisnik_email, а примарен клуч е korisnik_id.
FD1 и FD2 се зачувани. Спојувањето е без загуба бидејќи korisnik_id е клуч во KORISNICI.
Step 2.2 - Books
Problem:
kniga_id → kniga_naslov, kniga_isbn, kniga_opis, kniga_korica_url, kniga_godina_izdavanje, kniga_kolicina_na_zaliha, kniga_cena
Result:
KNIGI( kniga_id, kniga_naslov, kniga_isbn, kniga_opis, kniga_korica_url, kniga_godina_izdavanje, kniga_kolicina_na_zaliha, kniga_cena )
Во KNIGI важат FD5 и FD6.
Кандидатски клучеви се kniga_id и kniga_isbn, а примарен клуч е kniga_id.
FD5 и FD6 се зачувани, а декомпозицијата е без загуба бидејќи kniga_id е клуч на KNIGI.
Step 2.3 - Authors
Problem:
avtor_id → avtor_ime, avtor_prezime, avtor_biografija
Result:
AVTORI( avtor_id, avtor_ime, avtor_prezime, avtor_biografija )
Примарен и кандидатски клуч е avtor_id.
FD7 е зачувана, а декомпозицијата е без загуба бидејќи avtor_id е клуч на AVTORI.
Step 2.4 - Genres
Problem:
kniga_zhanr_id → kniga_zhanr_naziv, kniga_zhanr_opis
Истите податоци за жанрот се користат и кога жанрот претставува преференција на корисникот. Затоа во нормализираниот модел тие се претставуваат со еден ентитет ZHANROVI.
Result:
ZHANROVI( zhanr_id, naziv, opis )
Примарен клуч е zhanr_id.
Описните податоци за жанрот зависат само од идентификаторот на жанрот, па не треба да се повторуваат во врските со книги или корисници.
Step 2.5 - Categories
Аналогно, важи:
kniga_kategorija_id → kniga_kategorija_naziv, kniga_kategorija_opis
и истите категории се користат во преференциите на корисниците.
Result:
KATEGORII( kategorija_id, naziv, opis )
Примарен клуч е kategorija_id.
Описните атрибути на категоријата се чуваат само еднаш, а врските кон книги и корисници го користат нејзиниот идентификатор.
Step 2.6 - Moods
За расположенијата важи истата логика.
Result:
RASPOLOZENIJA( raspolozenie_id, naziv, opis )
Примарен клуч е raspolozenie_id.
Идентификаторот на расположението ги определува неговите описни атрибути. Истиот ентитет се користи за поврзување со книги и за избор на расположение од страна на корисник.
Step 2.7 - Orders
Од FD3:
naracka_id → naracka_datum, naracka_status, naracka_vkupna_cena, korisnik_id
се добива:
NARACKI( naracka_id, korisnik_id, naracka_datum, naracka_status, naracka_vkupna_cena )
Примарен клуч е naracka_id.
FD3 е зачувана, а декомпозицијата е без загуба бидејќи naracka_id е клуч на NARACKI.
Step 2.8 - Payments
Од FD4:
plakjanje_id → plakjanje_datum, plakjanje_iznos, plakjanje_nacin, plakjanje_status, naracka_id
се добива:
PLAKANJA( plakjanje_id, naracka_id, plakjanje_datum, plakjanje_iznos, plakjanje_nacin, plakjanje_status )
Примарен клуч е plakjanje_id.
FD4 е зачувана и декомпозицијата е без загуба.
Step 2.9 - Order items
Од FD11:
naracka_id, kniga_id → sodrzi_kolicina, sodrzi_edinechna_cena
се добива:
SODRZI( naracka_id, kniga_id, kolicina, edinechna_cena )
Кандидатски и примарен клуч е:
{naracka_id, kniga_id}
Сите не-клучни атрибути зависат од целиот составен клуч.
FD11 е зачувана и декомпозицијата е без загуба.
Step 2.10 - Favorites
Од FD12:
korisnik_id, omilena_kniga_id → omileni_datum_dodavanje
се добива:
OMILENI( korisnik_id, kniga_id, datum_dodavanje )
Примарен клуч е {korisnik_id, kniga_id}.
FD12 е зачувана.
Step 2.11 - Genre preferences
Од зависноста:
korisnik_id, preferiran_zhanr_id → preferira_zhanr_datum_dodavanje
се добива:
PREFERIRA_ZHANR( korisnik_id, zhanr_id, datum_dodavanje )
Примарен клуч е {korisnik_id, zhanr_id}.
Step 2.12 - Category preferences
Се добива:
PREFERIRA_KATEGORIJA( korisnik_id, kategorija_id, datum_dodavanje )
Примарен клуч е {korisnik_id, kategorija_id}.
Step 2.13 - Mood selections
Се добива:
IZBIRA_RASPOLOZENIE( korisnik_id, raspolozenie_id, datum_izbor )
Примарен клуч е {korisnik_id, raspolozenie_id}.
Step 2.14 - Book-author relationship
Релацијата NAPISANA_OD нема сопствени не-клучни атрибути.
NAPISANA_OD( kniga_id, avtor_id )
Примарен клуч е {kniga_id, avtor_id}.
Бидејќи релацијата содржи само клучни атрибути, нема парцијални или транзитивни зависности.
Step 2.15 - Book-genre relationship
IMA_ZHANR( kniga_id, zhanr_id )
Примарен клуч е {kniga_id, zhanr_id}.
Step 2.16 - Book-category relationship
IMA_KATEGORIJA( kniga_id, kategorija_id )
Примарен клуч е {kniga_id, kategorija_id}.
Step 2.17 - Book-mood relationship
POVRZANA_SO( kniga_id, raspolozenie_id )
Примарен клуч е {kniga_id, raspolozenie_id}.
State after 2NF decomposition
По декомпозицијата се отстранети парцијалните зависности од почетната унифицирана релација.
Релациите со едноставни примарни клучеви ги чуваат описните атрибути на соодветните ентитети, додека M:N релациите се претставени преку составни клучеви.
Кај релациите со составен клуч и сопствени атрибути, како SODRZI, OMILENI, PREFERIRA_ZHANR, PREFERIRA_KATEGORIJA и IZBIRA_RASPOLOZENIE, не-клучните атрибути зависат од целиот составен клуч.
Затоа добиените релации се најмалку во 2NF.
3NF decomposition
Следниот чекор е проверка за транзитивни зависности.
По издвојувањето на ентитетите, описните податоци за корисник, книга, автор, жанр, категорија и расположение повеќе не се повторуваат во релациите што ги поврзуваат.
На пример, во NARACKI се чува korisnik_id, но не и името, презимето или email адресата на корисникот. Тие се добиваат преку KORISNICI.
Слично, во SODRZI се чуваат naracka_id и kniga_id, но описните атрибути на книгата остануваат во KNIGI.
Во PREFERIRA_ZHANR се чува zhanr_id, а naziv и opis се чуваат во ZHANROVI.
Во PREFERIRA_KATEGORIJA се чува kategorija_id, а описните атрибути на категоријата се во KATEGORII.
Во IZBIRA_RASPOLOZENIE се чува raspolozenie_id, додека описните атрибути се во RASPOLOZENIJA.
Со тоа не постојат транзитивни зависности од примарен клуч преку не-клучен атрибут кон друг не-клучен атрибут.
Затоа не е потребна дополнителна декомпозиција за достигнување на 3NF.
Сите функционални зависности од почетното множество се зачувани во соодветните добиени релации, а декомпозициите се извршени преку заеднички атрибути што се клучеви во издвоените релации, со што се обезбедува lossless join.
BCNF
За BCNF се проверува дали за секоја нетривијална функционална зависност:
X → Y
левата страна X е суперклуч на релацијата во која зависноста важи.
Во KORISNICI, korisnik_id е кандидатски клуч. Поради уникатноста на email адресата, и korisnik_email претставува алтернативен кандидатски клуч.
Во KNIGI, kniga_id е кандидатски клуч, а kniga_isbn е алтернативен кандидатски клуч.
Во AVTORI, ZHANROVI, KATEGORII, RASPOLOZENIJA, NARACKI и PLAKANJA, левата страна на секоја нетривијална функционална зависност е примарниот клуч.
Во SODRZI:
{naracka_id, kniga_id} →
kolicina, edinechna_cena
левата страна е кандидатски клуч.
Во OMILENI, PREFERIRA_ZHANR, PREFERIRA_KATEGORIJA и IZBIRA_RASPOLOZENIE, левата страна на зависноста што го определува датумот е целиот составен примарен клуч.
NAPISANA_OD, IMA_ZHANR, IMA_KATEGORIJA и POVRZANA_SO содржат само атрибути од нивните составни клучеви и немаат нетривијални функционални зависности кон не-клучни атрибути.
Следствено, добиените релации ги исполнуваат условите за BCNF според дефинираното множество функционални зависности.
Функционалните зависности се зачувани во декомпозицијата, а врските меѓу релациите овозможуваат спојување без загуба.
Final result and discussion
Normalized relational model
По нормализацијата е добиен следниот релационен модел:
KORISNICI( korisnik_id PK, ime, prezime, email, lozinka, datum_registracija, uloga ) KNIGI( kniga_id PK, naslov, isbn, opis, korica_url, godina_izdavanje, kolicina_na_zaliha, cena ) AVTORI( avtor_id PK, ime, prezime, biografija ) ZHANROVI( zhanr_id PK, naziv, opis ) KATEGORII( kategorija_id PK, naziv, opis ) RASPOLOZENIJA( raspolozenie_id PK, naziv, opis ) NARACKI( naracka_id PK, korisnik_id FK, datum, status, vkupna_cena ) PLAKANJA( plakjanje_id PK, naracka_id FK, datum, iznos, nacin_na_plakjanje, status ) SODRZI( naracka_id PK/FK, kniga_id PK/FK, kolicina, edinechna_cena ) NAPISANA_OD( kniga_id PK/FK, avtor_id PK/FK ) IMA_ZHANR( kniga_id PK/FK, zhanr_id PK/FK ) IMA_KATEGORIJA( kniga_id PK/FK, kategorija_id PK/FK ) POVRZANA_SO( kniga_id PK/FK, raspolozenie_id PK/FK ) OMILENI( korisnik_id PK/FK, kniga_id PK/FK, datum_dodavanje ) PREFERIRA_ZHANR( korisnik_id PK/FK, zhanr_id PK/FK, datum_dodavanje ) PREFERIRA_KATEGORIJA( korisnik_id PK/FK, kategorija_id PK/FK, datum_dodavanje ) IZBIRA_RASPOLOZENIE( korisnik_id PK/FK, raspolozenie_id PK/FK, datum_izbor )
Discussion
Нормализацијата, започната од единствената глобална денормализирана релација без користење на претходниот релационен дизајн како основа за декомпозицијата, резултира со модел што суштински одговара на релациониот дизајн изработен во Phase P2.
Основните ентитети KORISNICI, KNIGI, AVTORI, ZHANROVI, KATEGORII, RASPOLOZENIJA, NARACKI и PLAKANJA се издвоени во посебни релации.
M:N врските се претставени преку посебни релации со составни примарни клучеви: SODRZI, NAPISANA_OD, IMA_ZHANR, IMA_KATEGORIJA, POVRZANA_SO, OMILENI, PREFERIRA_ZHANR, PREFERIRA_KATEGORIJA и IZBIRA_RASPOLOZENIE.
Со декомпозицијата се отстрануваат парцијалните и транзитивните зависности, се намалува редундантноста и се избегнуваат аномалии при внесување, измена и бришење на податоци.
Сите релации од конечниот модел се во BCNF во однос на идентификуваните функционални зависности. Функционалните зависности се зачувани во соодветните релации, а декомпозицијата овозможува lossless join.
Конечниот нормализиран модел е суштински ист со моделот што веќе е имплементиран во Phase P2. Поради тоа не е потребно суштинско преструктурирање на постојните database objects. Постоечкиот P2 релационен дизајн се задржува и продолжува да се користи во следните фази на проектот.
На овој начин процесот на нормализација дополнително потврдува дека релациониот модел на BiblioPremium соодветно ги раздвојува податоците за корисници, книги, автори, жанрови, категории, расположенија, нарачки и плаќања, како и нивните M:N врски.
