Kolmas normaalkuju
Kolmanda normaalkuju nõuded
- Relatsioon peab olema teises normaalkujus ja
- relatsioonis ei leidu transitiivseid sõltuvusi (ingl transitive dependency).
Transitiivne sõltuvus relatsioonis on funktsionaalne sõltuvus kahe või rohkema mitte-võtme atribuudi vahel. Iga mitte-võtme atribuut peab sõltuma ainult primaarvõtmest, mitte teis(t)est mitte-võtme atribuuti(de)st. Kui atribuut sõltub teise atribuudi kaudu primaarvõtme asemel mõnest teisest mitte-võtme atribuudist, siis kolmanda normaalkuju nõue on rikutud. Näiteks Linn -> Riik -> Mander puhul Linn määrab Riiki (iga linn asub kindlas riigis) ja Riik määrab Mandrit (iga riik asub kindlal mandril, lihtsuse mõttes me ei käsitle praegu erandriike nagu Türgi või Egiptus). Seega Mander on transitiivselt sõltuv Linn-ast läbi Riik-i. Kui relatsioonis leidub transitiivne sõltuvus, siis kolmanda normaalkuju nõue on rikutud. Olukorra parandamiseks tuleb läbi viia dekompositsioon. Kõik uued relatsioonid peavad olema kontrollitud esimese, teise ja kolmanda normaalkujule vastamisele.
Oletame, et on vaja luua andmebaas, milles hoitakse andmeid erinevate ajakirjade müügi kohta. Vesteldes tellijaga saime teada järgmised nõuded:
- Müüdavad ajakirjad pärinevad erinevatelt kirjastustelt.
- Ajakirja nimetus võib olla kuni 255 sümbolit pikk ning sisaldada tähti, numbreid ja erisümboleid.
- Igal ajakirjal on ainult üks kirjastus.
- Erinevad kirjastused võivad välja anda sama nimega ajakirju.
- Üks kirjastus võib välja anda mitmeid erinevaid ajakirju.
- Igal kirjastusel on ainult üks peakontori aadress.
- Ühel aadressil võivad olla erinevate kirjastuste peakontorid.
- Müügitehinguid identifitseeritakse arvenumbrite alusel.
- Iga arve sisaldab ainult ühte müüdud ajakirja.
- Igal arvel on kuupäev, millal tehing sooritati.
- Igal ajakirjal on kindel (fikseeritud) hind.
Tellija esitas enda soovide illustreerimiseks ka näidistabeli.
| Arve_nr | Muugikuupaev | Ajakirja_nimi | Kirjastus | Kirjastuse_aadress | Hind |
|---|---|---|---|---|---|
| 2001 | 2024-02-10 | Teadus ja elu | Tark meedia OÜ | Teatri väljak 3, Tallinn | 5.00 |
| 2002 | 2024-02-11 | Matkamaailm | Loodus & Sõbrad OÜ | Narva mnt 22, Tartu | 6.50 |
| 2003 | 2024-02-11 | Teadus ja elu | Tark meedia OÜ | Teatri väljak 3, Tallinn | 5.00 |
| 2004 | 2024-02-13 | Merekaru | Loodus & Sõbrad OÜ | Narva mnt 22, Tartu | 6.00 |
| 2005 | 2024-02-13 | Teadus ja elu | Teaduskeskus OÜ | Ringi tn 35, Pärnu | 3.50 |
Näeme, et näidistabelis on korduvad andmed, nagu näiteks kirjastus või kirjastuse aadress. Korduvad andmed raskendavad andmebaasi haldamist ja suurendavad vigade tekkimise riski, ehk võivad tekkida erinevad anomaaliad.
Mida arvate, mis anomaaliad võivad siin tekkida? AVA Anomaaliate kohta
Andmete kustutamise anomaalia - kui kustutame tabelist arve numbri, siis kaotame andmed ajakirja, kirjastuse ja kirjastuse asukoha kohta. See on eriti kriitiline, kui meil on ajakiri, mida müüdi ainult üks eksemplar;
Andmete uuendamise anomaalia - kui mõne kirjastuse asukoht peaks muutuma, siis peame muudatused sisse viima mitmel real. Näiteks kui Tark Meedia OÜ uueks asukohaks saab Ülikooli tn 18, Tartu, siis peaksime aadressi muutma kahel real;
Andmete lisamise anomaalia - kui Loodus & Sõbrad OÜ hakkab väljastama uut ajakirja Minu Eestimaa, siis me ei saa seda andmebaasi lisada enne, kui vähemalt üks klient on selle ajakirja ostnud ja see on kajastatud mõnel arvel.
Siin etteantud tabelis on primaarvõtmeks Arve_nr, kuna see tähistab unikaalset müügitehingut. Kahte arvet sama numbriga ei ole. Teades arve numbrit saame kindlalt öelda, millal ja mis ajakiri müüdi ja mis hinnaga ta müüdi.
Paneme kirja funktsionaalsed sõltuvused, mis kehtivad meie näidistabeli puhul:
- Kirjastus -> Kirjastuse_aadress
- Ajakirja_nimi, Kirjastus -> Hind
- Arve_nr -> Muugikuupaev, Ajakirja_nimi, Kirjastus, Kirjastuse_aadress, Hind
Mõtlemishetk:
- Miks ajakirja nimi ei määra kirjastust? AVA Vastus
Vastus: Sest tellija mainis, et võib esineda samanimelisi, kuid erineva kirjastuse poolt välja antud ajakirju.
- Miks ei kehti Ajakirja_Nimi -> Hind? Miks kindlasti peab olema ka kirjastus? AVA Vastus
Vastus: Sest tellija nõuete järgi sama nimega ajakirju võivad välja anda erinevad kirjastused ja teades ainult ajakirja nime me ei saa kindlalt öelda, kui palju ta maksab.
- Aga miks kirjastus ei määra hinda? AVA Vastus
Vastus: Sest kirjastus võib välja anda mitu erinevat ajakirja ja iga ühel võib olla oma hind.
Kontrollime kas antud relatsioon on kolmandas normaalkujus:
- ta on kindlasti esimeses normaalkujus, sest atribuutide väärtused on atomaarsed ning korduvaid korteeže ei ole;
- ta on kindlasti teises normaalkujus, sest primaarvõti koosneb ühest atribuudist;
- relatsioon ei ole kolmandas normaalkujus, sest on olemas kaks transitiivset sõltuvust.
Avastatud transitiivsed sõltuvused:
- Arve_nr -> Kirjastus -> Kirjastuse_aadress (funktsionaalses sõltuvuses Arve_nr -> Muugikuupaev, Ajakirja_nimi, Kirjastus, Kirjastuse_aadress, Hind leidub atribuut Kirjastus, mis funktsionaalse sõltuvuse Kirjastus -> Kirjastuse_aadress järgi määrab kirjastuse aadressi. Seega kirjastuse aadress on funktsionaalses sõltuvuses kirjastusest ja kirjastus omakorda on funktsionaalses sõltuvuses arve numbrist).
- Arve_nr -> Ajakirja_nimi, Kirjastus -> Hind (funktsionaalses sõltuvuses Arve_nr -> Muugikuupaev, Ajakirja_nimi, Kirjastus, Kirjastuse_aadress, Hind leidub Muugikuupaev, Ajakirja_nimi, mis funktsionaalse sõltuvuse Ajakirja_nimi, Kirjastus -> Hind järgi määrab hinda. Seega hind on funktsionaalses sõltuvuses ajakirja nimest ja kirjastusest ja need omakorda on funktsionaalses sõltuvuses arve numbrist).
Transitiivse sõltuvuse probleemi lahendamiseks kasutame dekompositsiooni. Vaatleme kõigepealt ahelat Arve_nr -> Kirjastus -> Kirjastuse_aadress. Võtame ahela kaks viimast osa (Kirjastus -> Kirjastuse_aadress) ning paneme need ühte relatsiooni (eemaldades ka korduvaid korteeže).
1. Kirjastused (Kirjastus, Kirjastuse_aadress)
- Primaarvõti: Kirjastus
- See relatsioon on esimeses normaalkujus, sest kõik esinevad väärtused on atomaarsed ja korduvaid korteeže ei ole.
- Kuna primaarvõti koosneb ühest atribuudist, siis on relatsioon automaatselt teises normaalkujus.
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Kirjastus -> Kirjastuse_aadress. Kirjastus määrab kirjastuse aadressi, kuid vastupidine ei kehti, sest sama aadress võib olla mitmel erineval kirjastusel (näiteks kirjastused asuvad samas hoones, kuid erinevatel korrustel).
| Kirjastus 🔑 | Kirjastuse_aadress |
|---|---|
| Tark meedia OÜ | Teatri väljak 3, Tallinn |
| Loodus & Sõbrad OÜ | Narva mnt 22, Tartu |
| Teaduskeskus OÜ | Ringi tn 35, Pärnu |
Nüüd vaatame esialgse relatsiooni teist transitiivse sõltuvuse ahelat Arve_nr -> Ajakirja_nimi, Kirjastus -> Hind. Sarnaselt koondame ahela kaks viimast osa (Ajakirja_nimi, Kirjastus -> Hind) ühte relatsiooni (eemaldades ka korduvaid korteeže).
2. Ajakirjad (Ajakirja_nimi, Kirjastus, Hind)
- Primaarvõti: (Ajakirja_nimi, Kirjastus)
- See relatsioon on esimeses normaalkujus, sest olevad väärtused on atomaarsed ja ei sisalda korduvaid korteeže.
- Relatsioon on teises normaalkujus, sest relatsiooni ainus mitte-võtme atribuut (Hind) sõltub tervest primaarvõtmest (Ajakirja_nimi ja Kirjastus).
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Ajakirja_nimi, Kirjastus -> Hind. Ajakirja nime ja kirjastuse kombinatsioon määrab ajakirja hinna. Ainult ajakirja nimi ei saa määrata ajakirja hinda, sest ajakirjade nimed võivad korduda.
| Ajakirja_nimi 🔑 | Kirjastus 🔑 | Hind |
|---|---|---|
| Teadus ja elu | Tark meedia OÜ | 5.00 |
| Matkamaailm | Loodus & Sõbrad OÜ | 6.50 |
| Merekaru | Loodus & Sõbrad OÜ | 6.00 |
| Teadus ja elu | Teaduskeskus OÜ | 3.50 |
Viimasesse relatsiooni läheb kõik, mille määrab Arve_nr, aga kuna Kirjastuse_aadress ja Hind on juba eelmistes relatsioonides mitte-võtme atribuudid, ei lisa me neid viimasesse relatsiooni.
Tuletame meelde varasemalt toodud funktsionaalseid sõltuvusi:
- Kirjastus -> Kirjastuse_aadress
- Ajakirja_nimi, kirjastus -> Hind
- Arve_nr -> Muugikuupaev, Ajakirja_nimi, Kirjastus, Kirjastuse_aadress, Hind
3. Müügid (Arve_nr, Muugikuupaev, Ajakirja_nimi, Kirjastus)
- Primaarvõti: Arve_nr
- See relatsioon on esimeses normaalkujus, sest olevad väärtused on atomaarsed ja ei sisalda korduvaid korteeže.
- Relatsioon on teises normaalkujus, sest primaarvõti (Arve_nr) koosneb ühest atribuudist.
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Arve_nr -> Muugikuupaev, Ajakirja_nimi, Kirjastus.
| Arve_nr 🔑 | Muugikuupaev | Ajakirja_nimi | Kirjastus |
|---|---|---|---|
| 2001 | 2024-02-10 | Teadus ja elu | Tark meedia OÜ |
| 2002 | 2024-02-11 | Matkamaailm | Loodus & Sõbrad OÜ |
| 2003 | 2024-02-11 | Teadus ja elu | Tark meedia OÜ |
| 2004 | 2024-02-13 | Merekaru | Loodus & Sõbrad OÜ |
| 2005 | 2024-02-13 | Teadus ja elu | Teaduskeskus OÜ |
Kokkuvõtvalt võime öelda, et dekompositsiooni tulemusel on eemaldatud transitiivsed sõltuvused, mis olid esialgses relatsioonis, ning tagatud, et uued relatsioonid vastavad kolmanda normaalkuju nõuetele.
Oletame, et on vaja luua andmebaas, mis hoiab teavet loomaaia väga eksootiliste loomade kohta. Vesteldes tellijaga saime teada järgmised nõuded:
- Loomaaed hoiab andmeid ainult väga eksootiliste loomade kohta, mis tähendab, et iga liik esineb looduslikult ainult ühes geograafilises piirkonnas.
- Iga loom kuulub kindlasse liiki, millel on ka oma ladinakeelne nimetus.
- Iga looma kohta on teada tema sugu.
- Igal loomaaias olev loom on kiibistatud ning omab identifitseerimiskoodi Loom_ID.
- Loomad pärinevad erinevatest päritoluriikidest, kuid iga päritoluriik on seotud vaid ühe mandriosaga.
- Erinevad liigid võivad esineda samas geograafilises piirkonnas. Loomade päritoluriik määrab, millisel mandril loom esineb.
Loomaaia juhataja pakkus välja järgmise olukorda illustreeriva tabeli.
| Loom_ID | Liik | Ladinakeelne_nimetus | Paritoluriik | Mandriosa | Sugu |
|---|---|---|---|---|---|
| T0708 | Aafrika elevant | Loxodonta africana | Keenia | Aafrika | Isane |
| L0203 | Hiidpanda | Ailuropoda melanoleuca | Hiina | Aasia | Emane |
| C0912 | Punakänguru | Osphranter rufus | Austraalia | Austraalia | Emane |
| B0102 | Punakänguru | Osphranter rufus | Austraalia | Austraalia | Isane |
| F5678 | Bengali tiiger | Panthera tigris tigris | India | Aasia | Emane |
Näeme, et näidistabelis on korduvad andmed, nagu näiteks mandriosa. Korduvad andmed raskendavad andmebaasi haldamist ja suurendavad vigade tekkimisi riski, ehk võivad tekkida erinevad anomaaliad.
Mida arvate, millised anomaaliad võivad siin tekkida? AVA Anomaaliate kohta
Andmete kustutamise anomaalia - kui hiidpanda viiakse teise loomaaeda, siis kaotame info liigi ja päritolupiirkonna kohta. See teave on aga oluline loomaaia ajaloo dokumenteerimiseks, kuna andmed varem peetud loomade kohta aitavad jälgida, milliseid haruldasi liike on loomaaed varem majutanud. See võib olla väärtuslik nii teadustööks, looduskaitseprogrammide jaoks kui ka hariduslikel eesmärkidel.
Andmete uuendamise anomaalia - kui hiljem otsustatakse, et Austraalia mandriosa asemel peaks kirjutama hoopis Okeaania, siis tuleb see muudatus teha kõigil ridadel, kus see liik esineb. Kui andmed jäetakse mõnel real muutmata, siis võib andmebaasis esineda vastuolulisi andmeid.
Andmete lisamise anomaalia - kui loomaaed soovib hoidma hakata Aafrika kuldkassi, kuid loom pole veel loomaaeda jõudnud, siis ei saa looma andmebaasi lisada enne, kui ta on füüsiliselt kohal ja talle on määratud unikaalne Loom_ID.
Mõelge korraks, mis selles tabelis võiks olla primaarvõtmeks. AVA Vastus
Etteantud tabelis on primaarvõtmeks Loom_ID. Teades looma identifitseerimiskoodi saame täpselt öelda, mis loomaga on tegemist ja kust ta tuli.
Paneme kirja funktsionaalsed sõltuvused, mis kehtivad näidistabeli puhul:
- Paritoluriik -> Mandriosa
- Liik -> Ladinakeelne_nimetus
- Loom_ID -> Liik, Ladinakeelne_nimi, Paritoluriik, Mandriosa, Sugu
Mõtlemishetk:
- Miks ladinakeelne nimi ei määra liiki? AVA Vastus
Vastus: Selline funktsionaalne sõltuvus ei pruugi alati kehtida. Mõnikord võib sama ladinakeelne nimetus kehtida mitmele erinevale loomaliigile sünonüümide või varasemate klassifikatsioonide tõttu. Näiteks Felis concolor oli varem kasutatud mitmete puumaliikide jaoks, kuid hiljem klassifitseeriti nad üheks liigiks Puma concolor.
Kontrollime kas antud relatsioon on kolmandas normaalkujus:
- ta on kindlasti esimeses normaalkujus, sest atribuutide väärtused on atomaarsed ja korduvaid korteeže ei ole;
- ta on kindlasti teises normaalkujus, sest primaarvõti koosneb ühest atribuudist;
- relatsioon ei ole kolmandas normaalkujus, sest on olemas kaks transitiivset sõltuvust.
Avastatud transitiivsed sõltuvused:
- Loom_ID -> Paritoluriik -> Mandriosa (mandriosa on funktsionaalses sõltuvuses päritoluriigist ja päritoluriik omakorda on funktsionaalses sõltuvuses Loom_ID-st).
- Loom_ID -> Liik -> Ladinakeelne_nimetus (ladinakeelne nimetus on funktsionaalses sõltuvuses liigist ja liik omakorda on funktsionaalses sõltuvuses looma ID-st).
Transitiivse sõltuvuse probleemi saame lahendada dekompositsiooni abil. Võtame esimesena vaatluse alla transitiivse sõltuvuse ahelat Loom_ID -> Paritoluriik -> Mandriosa. Tõstame selle kaks viimast osa (Paritoluriik -> Mandriosa) ühte relatsiooni (eemaldades ka korduvaid korteeže).
1. Päritoluriigid (Paritoluriik, Mandriosa)
- Primaarvõti: Paritoluriik
- See relatsioon on esimeses normaalkujus, sest kõik esinevad väärtused on atomaarsed ja korduvaid korteeže ei ole.
- Kuna primaarvõti koosneb ühest atribuudist, siis on relatsioon automaatselt teises normaalkujus.
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Paritoluriik -> Mandriosa. Päritoluriik määrab mandriosa, kuid mandriosa ei määra päritoluriiki, sest ühel mandriosal võib asuda mitu riiki.
| Paritoluriik 🔑 | Mandriosa |
|---|---|
| Keenia | Aafrika |
| Hiina | Aasia |
| Austraalia | Austraalia |
| India | Aasia |
Nüüd vaatame esialgse relatsiooni teist transitiivse sõltuvuse ahelat Loom_ID -> Liik -> Ladinakeelne_nimetus. Sarnaselt koondame ahela kaks viimast osa (Liik -> Ladinakeelne_nimetus) ühte relatsiooni (eemaldades korduvaid korteeže).
2. Nimetused (Liik, Ladinakeelne nimetus)
- Primaarvõti: Liik
- See relatsioon on esimeses normaalkujus, sest kõik esinevad väärtused on atomaarsed ja korduvaid korteeže ei ole.
- Kuna primaarvõti koosneb ühest atribuudist, siis on relatsioon automaatselt teises normaalkujus.
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Liik -> Ladinakeelne_nimetus. Liik määrab ladinakeelset nimetust, kuid ladinakeelne nimetus ei määra liiki, sest sellele nimetusele võib (ajalooliselt) vastata mitu liiki.
| Liik 🔑 | Ladinakeelne_nimetus |
|---|---|
| Aafrika elevant | Loxodonta africana |
| Hiidpanda | Ailuropoda melanoleuca |
| Punakänguru | Osphranter rufus |
| Bengali tiiger | Panthera tigris tigris |
Viimasesse relatsiooni läheb kõik, mille määrab Loom_ID, aga kuna Mandriosa ja Ladinakeelne_nimetus on juba eelmistes relatsioonides mitte-võtme atribuudid, ei lisa me neid viimasesse relatsiooni.
Tuletame meelde varasemalt toodud funktsionaalseid sõltuvusi:
- Paritoluriik -> Mandriosa
- Liik -> Ladinakeelne_nimetus
- Loom_ID -> Liik, Ladinakeelne_nimi, Paritoluriik, Mandriosa, Sugu
3. Loomad (Loom_ID, Liik, Paritoluriik, Sugu)
- Primaarvõti: Loom_ID
- See relatsioon on esimeses normaalkujus, sest kõik esinevad väärtused on atomaarsed ja korduvaid korteeže ei ole.
- Kuna primaarvõti koosneb ühest atribuudist, siis on relatsioon automaatselt teises normaalkujus.
- Relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
- Selles relatsioonis on ainult üks funktsionaalne sõltuvus: Loom_ID -> Liik, Paritoluriik, Sugu.
| Loom_ID 🔑 | Liik | Paritoluriik | Sugu |
|---|---|---|---|
| T0708 | Aafrika elevant | Keenia | Isane |
| L0203 | Hiidpanda | Hiina | Emane |
| C0912 | Punakänguru | Austraalia | Emane |
| B0102 | Punakänguru | Austraalia | Isane |
| F5678 | Bengali tiiger | India | Emane |
Kokkuvõtvalt võime öelda, et dekompositsiooni tulemusel on eemaldatud transitiivsed sõltuvused, mis olid esialgses relatsioonis, ning tagatud, et uued relatsioonid vastavad kolmanda normaalkuju nõuetele.
Oletame, et koristusroboteid tootev ettevõte soovib hoiustada andmebaasis põhilist infot toodetud robotite kohta. Vesteldes ettevõtte omanikuga, saime teada järgmist:
- Igal robotil on unikaalne seerianumber, mida kasutatakse peamise identifikaatorina.
- Igal robotil on kindel mudel.
- Igal mudelil võivad olla erinevad valmimisaastad, mis näitavad, millal robot oli valmistatud.
Parema ülevaate saamiseks esitas ettevõtte omanik näidistabeli.
| Seerianumber 🔑 | Mudel | Valmimisaasta |
|---|---|---|
| 6310 | EcoSweep X | 2023 |
| 2351 | VacuMax 3 | 2021 |
| 3823 | WasteBot 7 | 2022 |
| 9841 | VacuMax 3 | 2022 |
Primaarvõtmeks on selles näidistabelis Seerianumber, sest seerianumber on igal robotil unikaalne, ning määrab üheselt vastava mudeli ja valmimisaasta.
Mõelge, millised funktsionaalsed sõltuvused siin kehtivad. AVA Vastus
Siin kehtib ainult üks funktsionaalne sõltuvus:
- Seerianumber -> Mudel, Valmimisaasta
Kontrollime kas antud relatsioon on kolmandas normaalkujus:
- relatsioon on kindlasti esimeses normaalkujus, sest olevad väärtused on atomaarsed ja korduvaid korteeže ei ole;
- relatsioon on kindlasti teises normaalkujus, sest primaarvõti koosneb ühest atribuudist;
- relatsioon on kolmandas normaalkujus, sest transitiivseid sõltuvusi ei ole.
| Teine normaalkuju |