Teine normaalkuju
Teise normaalkuju nõuded
- Relatsioon peab olema esimeses normaalkujus ja
- iga mitte-võtme atribuut (ingl nonkey attribute, mõnikord nimetatakse ka sekundaarseks atribuudiks) on täielikus funktsionaalses sõltuvuses (ingl fully functional dependency) ainult kogu (tervest) primaarvõtmest.
Meeldetuletuseks: primaarvõti (ingl primary key) on minimaalne atribuutide hulk (võib olla ka ainult üks atribuut), mis üheselt identifitseerib (eristab) iga rea andmetabelis. Selle väärtus on alati unikaalne ja ei tohi kunagi olla tühi (NULL).
Mõned vihjed, mis aitavad kiiremini otsustada, kas relatsioon on teises normaalkujus või mitte:
- Kui primaarvõti koosneb ainult ühest atribuudist, siis relatsioon on automaatselt teises normaalkujus ja seda relatsiooni ei ole vaja kontrollida.
- Kui primaarvõti koosneb mitmest atribuudist, siis relatsioon on teises normaalkujus ainult siis, kui kõik mitte-võtme atribuudid sõltuvad kogu (tervest) primaarvõtmest, mitte primaarvõtme mingist osast. Kui aga mõni mitte-võtme atribuut sõltub ainult ühest primaarvõtme osast (ehk tegemist on osalise funktsionaalse sõltuvusega, ingl partial functional dependency), siis see relatsioon ei ole teises normaalkujus (teise normaalkuju nõuded on rikutud). Olukorra parandamiseks tuleb läbi viia dekompositsioon (ingl decomposition) ehk jagada suur relatsioon väiksemateks relatsioonideks. Kõik uued relatsioonid peavad olema kontrollitud esimese ja teise normaalkujule vastamisele.
Oletame, et firma tahab luua andmebaasi, et hallata oma autoparki. Vestluse käigus tellijaga selgusid järgmised nõuded:
- Igal autol on unikaalne registreerimisnumber (nt 123ABC, 456DEF).
- Igal autol on kindel mark ja mudel (nt Toyota Corolla, Ford Focus).
- Mudeli nimetus kuulub konkreetsele automargile.
- Auto asukoht (nt Tallinn, Tartu) sõltub osakonnast, ehk iga osakond asub kindlas linnas.
- Üks auto saab kasutuses olla ainult ühel osakonnal.
- Ühel osakonnal võib olla mitu autot.
- Võib juhtuda, et osakonnal pole hetkel ühtegi autot.
- Kõik väljad peavad olema alati täidetud.
Tellija esitas näidistabeli.
| Registreerimisnumber | Mark | Mudel | Osakond | Asukoht |
|---|---|---|---|---|
| 510THI | Toyota | Corolla | Müük | Tartu |
| 381TDX | Ford | Focus | Logistika | Pärnu |
| 285AZA | Honda | Civic | Juhtkond | Tallinn |
| 321JSD | Opel | Omega | Juhtkond | Tallinn |
| 623JSE | BMW | 530i | Logistika | Pärnu |
Näiteks auto registreerimisnumbriga 510THI on Toyota Corolla. Auto kuulub ettevõtte müügiosakonda ja asub Tartus.
Näeme, et tellija antud näidistabelis on korduvaid andmeid, nagu osakond, asukoht. Korduvad andmed raskendavad andmebaasi haldamist ja suurendavad vigade tekkimise riski, ehk võivad tekkida erinevad anomaaliad.
Eristatakse järgmised anomaaliad.
Andmete kustutamise anomaalia (ingl Deletion Anomaly). Praeguses tabelis hoitakse autode infot koos osakondadega. Võib ette tulla olukord, mil olemasolev osakonna kasutus autoga lõpetatakse, ning tabeli rida kustutatakse. Sellisel juhul kaotatakse mitte ainult osakonnaga seotud andmed (millises osakonnas autot kasutatakse ja kus see asub), vaid ka autoga seotud info (mark ja mudel). Näiteks kui ettevõte otsustab müüa auto 510THI, kaotame müügiosakonna asukoha.
Andmete uuendamise anomaalia (ingl Update Anomaly). Kui osakonna asukohta (linna) soovitakse muuta, siis peab muudatused olema tehtud kõikides ridades, kus selle osakonna andmed esinevad. Kui näiteks otsustatakse, et logistikaosakond kolib Pärnust Tartusse, kuid muudatus tehakse ainult osades ridades, jäävad ülejäänud ridadesse endiselt kirja vanad andmed (asukoht Pärnu). See tekitab vastuolu, kus sama osakonna kohta on andmebaasis mitu erinevat linna.
Andmete lisamise anomaalia (ingl Insertion Anomaly). Soovime lisada uue auto andmed, kuid pole veel teada milline osakond seda kasutama hakkab. Sellist autot ei saa tabelisse lisada, sest osakonna ja seega ka asukoha veerud jääksid tühjaks, mis rikub tabeli terviklikkuse nõudeid. Näiteks kui auto, mille registreerimisnumber on 510ABC, lisatakse tabelisse ilma andmeteta, milline osakond seda kasutab, ei saa ka määrata, kus linnas auto asub.
Tuleme tagasi meie näidistabeli juurde. Mõelge korraks, mis selles tabelis võiks olla primaarvõtmeks. AVA Vastus
Selles tabelis on primaarvõtmeks Registreerimisnumber, kuna teades seda numbrit saame üheselt tuvastada mis automargi või automudeliga on tegemist. Registreerimisnumber määrab üheselt, mis osakonda sõiduk kuulub ja iga osakond asub kindlas linnas (mõlemad tulevad kliendi poolt esitatud nõuetest). Seega registreerimisnumber tagab, et iga tabeli rida on üheselt eristatav.
Kontrollime, kas relatsioon on teises normaalkujus:
- ta on esimeses normaalkujus, sest väärtused on atomaarsed ja korduvaid korteeže ei ole;
- ta on teises normaalkujus, sest primaarvõti koosneb ühest atribuudist ja see tähendabki automaatselt, et relatsioon on teises normaalkujus.
Oletame, et loome raamatukogu andmebaasi ja tellija on meile andnud järgmised nõuded:
- Kuna raamatukogus on igal raamatul mitmeid eksemplare, siis raamatut võivad laenutada mitmed lugejad.
- Igal raamatul on oma unikaalne ISBN (nt „978-3-16-148410-0“). NB! ISBN on seotud konkreetse raamatu väljaandega, mitte üksiku eksemplariga.
- Igal raamatul on olemas pealkiri ja autor.
- Raamat võib olla laenutatud erinevatel kuupäevadel.
- Lugeja võib laenutada mitu raamatut korraga.
- Lugeja võib korraga laenutada ainult ühe eksemplari konkreetse raamatu kohta.
- Lugejal on unikaalne ID.
- Võib juhtuda, et andmebaasis on kaks isikut sama ees- ja perenimega.
- Raamatu laenutamisel kõik väljad peavad olema alati täidetud.
Tellija on esitanud ka järgmise näidistabeli.
| Lugeja_ID | Eesnimi | Perenimi | ISBN | Pealkiri | Autor | Laenutuse_kuupaev | Tagastamise_kuupaev |
|---|---|---|---|---|---|---|---|
| L001 | Mari | Maasikas | 9783161484100 | Andmebaasid algajatele | J. Smith | 2025-02-01 | 2025-02-16 |
| L002 | Peeter | Paju | 9780131103627 | Programmeerimise alused | D. Knuth | 2025-02-02 | 2025-04-03 |
| L001 | Mari | Maasikas | 9781566199094 | Keemia õpik | A. Brown | 2025-02-02 | 2025-02-17 |
| L003 | Anna | Tamm | 9783161484100 | Andmebaasid algajatele | J. Smith | 2025-02-04 | 2025-03-06 |
Näeme, et näidistabelis on palju korduvaid andmeid, nagu näiteks eesnimed, perenimed, pealkirjad jne. Korduvad andmed raskendavad andmebaasi haldamist ja suurendavad vigade tekkimise riski, ehk võivad tekkida erinevad anomaaliad.
Eristatakse järgmised anomaaliad.
Andmete kustutamise anomaalia - kui raamatute ja laenutuste andmed on samas tabelis, siis raamatu eemaldamisel kaovad ka kõik sellega seotud laenutused. Raamatukogu tahaks siiski, et isegi pärast raamatu eemaldamist oleks võimalik näha, millised laenutused on olnud seotud selle konkreetse raamatuga, tagades laenutuste ajaloo säilimise. Näiteks kui raamat "Programmeerimise alused" (ISBN: 9780131103627) kantakse raamatukogu nimekirjast maha, siis kaob ka info Peetri laenutuse kohta.
Andmete uuendamise anomaalia - kui raamatu teave kordub igas laenutuskandes, siis muutuste tegemisel peab uuendama kõiki kirjeid korraga. Kui mõnes kohas jääb uuendus tegemata, tekib andmebaasis vastuolu. Näiteks raamat "Andmebaasid algajatele" (ISBN: 9783161484100) on mitmel korral laenutatud ning igas laenutuskandes kordub raamatu pealkiri ja autor. Kui avastatakse trükiviga ja pealkiri tuleb muuta (nt "Andmebaasid edasijõudnutele"), tuleb see teha kõikides kirjetes, vastasel juhul tekivad andmebaasis erinevad variandid.
Andmete lisamise anomaalia - kui raamatute ja laenutuste andmeid hoitakse koos, võib juhtuda, et uue raamatu kohta ei saa infot sisestada enne, kui see on vähemalt ühe korra laenutatud. Näiteks raamatukogu ostab uue raamatu "Tehisintellekt ja tulevik" (ISBN: 9783161484117), kuid kuna seda pole veel keegi laenutanud, ei saa seda tabelisse lisada, sest laenutuse kuupäeva veerg jääks tühjaks. See takistab raamatu andmete haldamist enne esimest laenutust.
Tuleme tagasi näidistabeli juurde. Kõigepealt määrame primaarvõtme. Meie näidistabeli puhul primaarvõtmeks on (Lugeja_ID, ISBN, Laenutuse_kuupaev).
Mõtlemishetk:
- Kas Lugeja_ID võiks olla primaarvõtmeks? AVA Vastus
Vastus: Ainult Lugeja_ID’st ei piisa, sest inimene võib laenata erinevatel kuupäevadel erinevad raamatud.
- Kas Ees- ja Perenime kombinatsioon sobiks primaarvõtmeks? AVA Vastus
Vastus: Ka ei sobi primaarvõtmeks, sest tellija ütles meile, et saab olla isikud, kellel on samad ees- ja perenimed.
- Aga Lugeja_ID ja Laenutuse_kuupaev koos? AVA Vastus
Vastus: Sellisel juhul iga kord lugeja saab võtta ainult ühte raamatut, aga tellija ütles meile, et lugeja võib laenutada mitu raamatut korraga.
Kuna primaarvõti koosneb mitmest atribuudist, siis selline relatsioon ei ole automaatselt teises normaalkujus.
Paneme kirja funktsionaalsed sõltuvused, mis kehtivad meie näidistabeli puhul:
- Lugeja_ID -> Eesnimi, Perenimi (teades lugeja ID-d saame kindlaks teha tema eesnime või perenime; “->” loetakse kui “määrab”)
- ISBN -> Pealkiri, Autor
- Lugeja_ID, ISBN, Laenutuse_kuupaev -> Tagastamise_kuupaev
Infoks: tegelikult primaarvõtme määramine toimub funktsionaalsete sõltuvuste põhjal. Aga lihtsuse mõttes oleme juba ülesande alguses selle määranud.
Mõtlemishetk:
- Aga miks ei kehti Eesnimi, Perenimi -> Lugeja_ID? AVA Vastus
Vastus: Sest meie andmebaasis võivad olla lugejad, kellel on samad ees- ja pernimed.
- Aga miks ei saa kasutada Pealkiri, Autor -> ISBN? AVA Vastus
Vastus: Sest raamatul võib olla mitu väljaannet ja teades ainult pealkirja ja autorit ei saa määrata ISBN-i.
Praegune relatsioon ei ole teises normaalkujus, sest mitte-võtme atribuudid Eesnimi, Perenimi sõltuvad Lugeja_ID-st, kuid mitte ISBN-ist ega Laenutuse_kuupaevast (tuletagem siin meelde, et primaarvõti on (Lugeja_ID, ISBN, Laenutuse_kuupaev)). See tähendab, et relatsioonis esineb osaline funktsionaalne sõltuvus – funktsionaalne sõltuvus, kus üks või mitu mitte-võtme atribuutidest (nt Eesnimi) sõltuvad ainult primaarvõtme osast, mitte täielikust (kogu) primaarvõtmest. Relatsiooni viimiseks teise normaalkuju tuleb läbi viia dekompositsioon, ehk jagada suur relatsioon väiksemateks relatsioonideks, mis vastaksid teise normaalkuju nõuetele. Jagamise aluseks võtame leitud funktsionaalsed sõltuvused.
1. Lugejad (Lugeja_ID, Eesnimi, Perenimi)
- Primaarvõti: Lugeja_ID
- See relatsioon ei ole veel esimeses normaalkujus, sest kuigi väärtused on atomaarsed, esineb korduvaid korteeže.
| Lugeja_ID 🔑 | Eesnimi | Perenimi |
|---|---|---|
| L001 | Mari | Maasikas |
| L002 | Peeter | Paju |
| L001 | Mari | Maasikas |
| L003 | Anna | Tamm |
Eemaldades need saame relatsiooni, mis on esimeses normaalkujus.
| Lugeja_ID 🔑 | Eesnimi | Perenimi |
|---|---|---|
| L001 | Mari | Maasikas |
| L002 | Peeter | Paju |
| L003 | Anna | Tamm |
- Primaarvõti (Lugeja_ID) koosneb ainult ühest atribuudist, ning relatsioon Lugejad on seega automaatselt teises normaalkujus.
2. Raamatud (ISBN, Pealkiri, Autor)
- Primaarvõti: ISBN
- See relatsioon ei ole veel esimeses normaalkujus, sest kuigi väärtused on atomaarsed, esineb korduvaid korteeže.
| ISBN 🔑 | Pealkiri | Autor |
|---|---|---|
| 9783161484100 | Andmebaasid algajatele | J. Smith |
| 9780131103627 | Programmeerimise alused | D. Knuth |
| 9781566199094 | Keemia õpik | A. Brown |
| 9783161484100 | Andmebaasid algajatele | J. Smith |
Eemaldades need saame relatsiooni, mis on esimeses normaalkujus.
| ISBN 🔑 | Pealkiri | Autor |
|---|---|---|
| 9783161484100 | Andmebaasid algajatele | J. Smith |
| 9780131103627 | Programmeerimise alused | D. Knuth |
| 9781566199094 | Keemia õpik | A. Brown |
- Primaarvõti (ISBN) koosneb ainult ühest atribuudist, ning relatsioon Raamatud on seega automaatselt teises normaalkujus.
3. Laenutused (Lugeja_ID, ISBN, Laenutuse_kuupaev, Tagastamise_tahtaeg)
- Primaarvõti: (Lugeja_ID, ISBN, Laenutuse_kuupaev)
- See relatsioon on esimeses normaalkujus, sest atribuutide väärtused on atomaarsed ning korduvaid korteeže ei esine.
- Primaarvõti koosneb kolmest atribuudist. Relatsiooni ainus mitte-võtme atribuut (Tagastamise_tahtaeg) on funktsionaalses sõltuvuses tervest primaarvõtmest ning seega on relatsioon Laenutused teises normaalkujus.
| Lugeja_ID 🔑 | ISBN 🔑 | Laenutuse_kuupaev 🔑 | Tagastamise_tahtaeg |
|---|---|---|---|
| L001 | 9783161484100 | 2025-02-01 | 2025-02-16 |
| L002 | 9780131103627 | 2025-02-02 | 2025-04-03 |
| L001 | 9781566199094 | 2025-02-03 | 2025-02-17 |
| L003 | 9783161484100 | 2025-02-04 | 2025-03-06 |
Kokkuvõtvalt võime öelda, et dekompositsiooni tulemusel on eemaldatud osalised funktsionaalsed sõltuvused, mis olid esialgses relatsioonis, ning tagatud, et uued relatsioonid vastavad teise normaalkuju nõuetele.
Pärnu lennujaam soovib luua piletihaldussüsteemi, mis võimaldab neil efektiivselt hallata reisijate broneeringuid, lende ja piletihindu.
Tellija on meile andnud järgmised nõuded:
- Lennujaam haldab kõiki lende ja broneeringuid.
- Igal lennul on oma unikaalne ID (LendID), samuti sihtkoht, väljumiskuupäev ja väljumisaeg.
- Reisijad võivad broneerida mitmeid erinevaid lende.
- Ühel lennul võib olla mitu reisijat.
- Ühel reisijal võib olla mitu broneeringut erinevatele lendudele.
- Igal broneeringul peab olema määratud konkreetne istekoht lennukis.
- Võib juhtuda, et ühel lennul on mitu inimest sama ees- ja perenimega.
- Pileti hind võib erineda sõltuvalt istekohast, broneeringu ajastusest ning reisija personaalsetest sooduspakkumistest.
Tellija on esitanud ka järgmise näidistabeli.
| Eesnimi | Perenimi | Synnikuupaev | Sugu | LendID | Sihtkoht | Valjumiskuupaev | Valjumisaeg | Istekoht | Hind |
|---|---|---|---|---|---|---|---|---|---|
| Mari | Tamm | 1990-05-12 | N | 101 | London | 2025-03-10 | 8:30 | 12A | 150 |
| Karl | Mägi | 1985-09-23 | M | 101 | London | 2025-03-10 | 8:30 | 12B | 180 |
| Liisa | Põld | 1998-12-01 | N | 102 | Pariis | 2025-03-11 | 12:45 | 3B | 200 |
| Mari | Tamm | 1990-05-12 | N | 103 | New York | 2025-03-12 | 16:20 | 9D | 500 |
Näeme, et näidistabelis on korduvad andmed, mis raskendavad andmebaasi haldamist ja suurendavad vigade tekkimise riski. Seetõttu võivad tekkida erinevad anomaaliad. Mõelge, millised anomaaliad võivad siin tekkida.
Mõtlemishetk:
- Mis selles näidistabelis võib olla primaarvõtmeks? AVA Vastus
Vastus: Primaarvõtmeks sobib (Eesnimi, Perenimi, Synnikuupaev, LendID).
- Miks primaatvõtmes ei saa olla (Eesnimi, Perenimi, LendID)? AVA Vastus
Vastus: Kuna tellija ütles, et samal lennul võivad olla inimesed, kellel on samad eesnimed ja perenimed, siis kaasame primaarvõtmesse veel ühe tunnuse - sünnikuupäeva. On üsna ebatõenäoline, et ühele lennule saavad inimesed, kellel on samad nimed ja kes on sündinud samal kuupäeval.
- Miks primaarvõtmes ei ole sihtkohta? AVA Vastus
Vastus: Sest igasse sihtkohta võib olla erinevad lennud (erinevatel kuupäevadel).
Näidistabeli puhul primaarvõtmeks on (Eesnimi, Perenimi, Synnikuupaev, LendID). See koosneb mitmest atribuudist, peame kontrollima vastavust teisele normaalkujule.
Alustame sellest, et kirjutame välja funktsionaalsed sõltuvused:
- LendID -> Sihtkoht, Valjumiskuupaev, Valjumisaeg (teades lennu ID-d saame kindlaks teha lennu sihtkohta, väljumiskuupäeva ning väljumisaja)
- Eesnimi, Perenimi, Synnikuupaev -> Sugu
- Eesnimi, Perenimi, Synnikuupaev, LendID -> Istekoht, Hind
Kui funksionaalsed sõltuvused on paigas, kontrollime kas leiduvad osalised sõltuvused.
Praegune relatsioon ei ole teises normaalkujus, sest mitte-võtme atribuut Sugu sõltub Eesnimi, Perenimi, Synnikuupaev-ast, kuid mitte LendID-st. Samamoodi mitte-võtme atribuudid Sihtkoht, Valjumiskuupaev ning Valjumisaeg ei sõltu Eesnimi, Perenimi, Synnikuupaev-ast. See tähendab, et relatsioonis esineb osaline funktsionaalne sõltuvus (üks või mitu mitte-võtme atribuutidest sõltuvad ainult primaarvõtme osast, mitte täielikust (tervest) primaarvõtmest).
Relatsiooni viimiseks teise normaalkuju tuleb läbi viia dekompositsioon. Iga uue relatsiooni puhul tuleb kontrollida selle vastavust teise normaalkuju nõuetele. Jagamise aluseks kasutame leitud funktsionaalsed sõltuvused.
1. Reisijad (Eesnimi, Perenimi, Synnikuupaev, Sugu)
- Primaarvõti: Eesnimi, Perenimi, Synnikuupaev
- See relatsioon ei ole veel esimeses normaalkujus, sest kuigi väärtused on atomaarsed, esineb korduvaid korteeže.
| Eesnimi 🔑 | Perenimi 🔑 | Synnikuupaev 🔑 | Sugu |
|---|---|---|---|
| Mari | Tamm | 1990-05-12 | N |
| Karl | Mägi | 1985-09-23 | M |
| Liisa | Põld | 1998-12-01 | N |
| Mari | Tamm | 1990-05-12 | N |
Kui me korduva korteeži eemaldame, saame relatsiooni, mis on esimeses normaalkujus.
| Eesnimi 🔑 | Perenimi 🔑 | Synnikuupaev 🔑 | Sugu |
|---|---|---|---|
| Mari | Tamm | 1990-05-12 | N |
| Karl | Mägi | 1985-09-23 | M |
| Liisa | Põld | 1998-12-01 | N |
- Uue relatsiooni primaarvõti koosneb mitmest atribuudist. Relatsiooni ainus mitte-võtme atribuut (Sugu) on funktsionaalses sõltuvuses tervest primaarvõtmest ning seega on uus relatsioon teises normaalkujus.
2. Lennud (LendID, Sihtkoht, Valjumiskuupaev, Valjumisaeg)
- Primaarvõti: LendID
- Relatsioon ei ole esimeses normaalkujus, sest kuigi väärtused on atomaarsed, see sisaldab korduvaid korteeže.
| LendID 🔑 | Sihtkoht | Valjumiskuupaev | Valjumisaeg |
|---|---|---|---|
| 101 | London | 2025-03-10 | 8:30 |
| 101 | London | 2025-03-10 | 8:30 |
| 102 | Pariis | 2025-03-11 | 12:45 |
| 103 | New York | 2025-03-12 | 16:20 |
Eemaldades korduva korteeži, viime relatsiooni esimesse normaalkujusse.
| LendID 🔑 | Sihtkoht | Valjumiskuupaev | Valjumisaeg |
|---|---|---|---|
| 101 | London | 025-03-10 | 8:30 |
| 102 | Pariis | 2025-03-11 | 12:45 |
| 103 | New York | 2025-03-12 | 16:20 |
- Kuna primaarvõti koosneb ühest atribuudist, siis on relatsioon automaatselt teises normaalkujus.
3. Broneeringud (Eesnimi, Perenimi, Synnikuupaev, LendID, Istekoht, Hind)
- Primaarvõti: Eesnimi, Perenimi, Synnikuupaev, LendID
- Relatsioon on esimeses normaalkujus, sest kõik väärtused on atomaarsed ja korduvaid korteeže ei esine.
- Primaarvõti koosneb mitmest atribuudist, aga kuna mitte-võtme atribuudid (Istekoht ja Hind) on funktsionaalses sõltuvuses tervest primaarvõtmest, siis uus relatsioon on teises normaalkujus.
| Eesnimi 🔑 | Perenimi 🔑 | Synnikuupaev 🔑 | LendID 🔑 | Istekoht | Hind |
|---|---|---|---|---|---|
| Mari | Tamm | 1990-05-12 | 101 | 12A | 150 |
| Karl | Mägi | 1985-09-23 | 101 | 12B | 180 |
| Liisa | Põld | 1998-12-01 | 102 | 3B | 200 |
| Mari | Tamm | 1990-05-12 | 103 | 9D | 500 |
Kokkuvõtvalt võime öelda, et dekompositsiooni tulemusel on eemaldatud osalised funktsionaalsed sõltuvused, ning kõik uued relatsioonid vastavad teise normaalkuju nõuetele.
| Esimene normaalkuju | Kolmas normaalkuju |