SQL tabelite täiendava normaliseerimine interaktiivne õpetus

Tabelite viimine teisele normaalkujule (2NK)

Funktsionaalsed sõltuvused (1NK tabelis)

Esmalt tuvastame funktsionaalsed sõltuvused algses tabelis Kliendi_rentimine. Primaarvõti on liitvõti (vara_nr, rendi_algus).

fs1: (vara_nr, rendi_algus) -> (kliendi_nr, kliendi_eesnimi, kliendi_perenimi, rendi_lopp, vara_aadress, omaniku_nr, omaniku_eesnimi, omaniku_perenimi)
   (Rent ei sõltu enam tervikvõtmest, seega eemaldatud siit)

fs2: kliendi_nr -> (kliendi_eesnimi, kliendi_perenimi)
   (Kliendi number määrab kliendi nime) - Transitiivne sõltuvus

fs3: vara_nr -> (vara_aadress, rent, omaniku_nr, omaniku_eesnimi, omaniku_perenimi)
   (Vara number määrab vara aadressi, rendi ja omaniku info) - Osaline sõltuvus primaarvõtmest (sõltub ainult `vara_nr`-st)

fs4: omaniku_nr -> (omaniku_eesnimi, omaniku_perenimi)
   (Omaniku number määrab omaniku nime) - Transitiivne sõltuvus (fs3 kaudu)

2NK Definitsioon ja Põhjendus

Tulemuseks olevad tabelid (2NK)

Selle tulemusena jagame algse tabeli kaheks. Jälgime roheliselt märgitud andmete liikumist. Pange tähele, et `rent` on nüüd tabelis `Vara_omanik`.

Tabel 1: Vara_rentimine

Vara_rentimine (
    kliendi_nr (FK -> ?), -- Viitab tulevasele Klient tabelile
    kliendi_eesnimi,
    kliendi_perenimi,
    vara_nr (PK osa) (FK -> Vara_omanik),
    rendi_algus (PK osa),
    rendi_lopp
)
Primaarvõti (PK): (vara_nr, rendi_algus)
Välisvõti (FK): vara_nr -> Vara_omanik(vara_nr)
(Rendi summa eemaldati siit, kuna see sõltub osaliselt PK-st)

Tabel 2: Vara_omanik

Vara_omanik (
    vara_nr (PK),
    vara_aadress,
    rent, -- Rent kuulub nüüd siia, kuna sõltub ainult varast
    omaniku_nr (FK -> ?), -- Viitab tulevasele Omanik tabelile
    omaniku_eesnimi,
    omaniku_perenimi
)
Primaarvõti (PK): vara_nr

Näidisandmed (2NK)

Vara_rentimine tabel

Vara_omanik tabel

* Märkus: Rent asub nüüd õiges tabelis vastavalt eeldusele, et see sõltub ainult varast.

Tabelite viimine kolmandale normaalkujule (3NK)

Funktsionaalsed sõltuvused (2NK tabelites)

Vaatame nüüd 2NK tabeleid ja nende sõltuvusi:

Tabel: Vara_rentimine

fs1: (vara_nr, rendi_algus) -> (kliendi_nr, kliendi_eesnimi, kliendi_perenimi, rendi_lopp)
   (Primaarvõti määrab ülejäänud veerud)

fs2: kliendi_nr -> (kliendi_eesnimi, kliendi_perenimi)
   (Kliendi nr määrab nime) - Transitiivne sõltuvus (PK -> kliendi_nr -> nimi)

Tabel: Vara_omanik

fs3: vara_nr -> (vara_aadress, rent, omaniku_nr, omaniku_eesnimi, omaniku_perenimi)
    (Primaarvõti määrab ülejäänud veerud)

fs4: omaniku_nr -> (omaniku_eesnimi, omaniku_perenimi)
   (Omaniku nr määrab nime) - Transitiivne sõltuvus (PK -> omaniku_nr -> nimi)

3NK Definitsioon ja Põhjendus

Tulemuseks olevad tabelid (3NK)

Jagame tabelid edasi. Roheliselt on tähistatud endiselt algsest 1NK tabeli esimesest reast pärit andmed. Rent jääb nüüd tabelisse Vara.

Tabel 1: Klient

Klient (
    kliendi_nr (PK),
    kliendi_eesnimi,
    kliendi_perenimi
)
Primaarvõti (PK): kliendi_nr

Tabel 2: Rentimine

Rentimine (
    vara_nr (PK osa) (FK -> Vara),
    kliendi_nr (FK -> Klient),
    rendi_algus (PK osa),
    rendi_lopp
)
Primaarvõti (PK): (vara_nr, rendi_algus)
Välisvõti (FK): kliendi_nr -> Klient(kliendi_nr)
Välisvõti (FK): vara_nr -> Vara(vara_nr)

Tabel 3: Vara

Vara (
    vara_nr (PK),
    vara_aadress,
    rent, -- Rent on nüüd siin
    omaniku_nr (FK -> Omanik)
)
Primaarvõti (PK): vara_nr
Välisvõti (FK): omaniku_nr -> Omanik(omaniku_nr)

Tabel 4: Omanik

Omanik (
    omaniku_nr (PK),
    omaniku_eesnimi,
    omaniku_perenimi
)
Primaarvõti (PK): omaniku_nr

Näidisandmed (3NK)

Klient tabel

Rentimine tabel

Vara tabel

Omanik tabel

Tabelite viimine Boyce/Coddi normaalkujule (BCNF)

BCNF Definitsioon

Tabel on Boyce/Coddi normaalkujul (BCNF), kui iga funktsionaalse sõltuvuse (X -> Y) korral on X supervõti.

Praktikas tähendab see, et iga sõltuvuse determinant (vasak pool, X) peab olema kandidaatvõti.

Sõltuvused 3NK tabelites

Klient
fs_k: kliendi_nr -> (kliendi_eesnimi, kliendi_perenimi)
   (Determinant on PK. BCNF.)

Rentimine
fs_r: (vara_nr, rendi_algus) -> (kliendi_nr, rendi_lopp)
   (Determinant on PK. BCNF.)

Vara
fs_v: vara_nr -> (vara_aadress, rent, omaniku_nr)
   (Determinant on PK. BCNF.)

Omanik
fs_o: omaniku_nr -> (omaniku_eesnimi, omaniku_perenimi)
   (Determinant on PK. BCNF.)

Arutelu viienda normaalkuju (5NK) ja ortogonaalsuse üle

Viienda normaalkuju (5NK) reeglid

BCNF on sageli piisav. Vaatame siiski 5NK reegleid:

• Reegel 1: Kui tabel on BCNF ja mõni kandidaatvõti on lihtne (1 veerg), siis tabel on 4NK.
• Reegel 2: Kui tabel on 3NK/BCNF ja *iga* kandidaatvõti on lihtne, siis on tabel 5NK.
• Reegel 3: Kui tabel on BCNF ja sel on vähemalt üks atribuut, mis ei kuulu ühtegi kandidaatvõtmesse, siis tabel on 5NK.

Tabelite analüüs 5NK reeglite järgi

• Klient: BCNF, PK lihtne. 5NK (Reegel 2).
• Omanik: BCNF, PK lihtne. 5NK (Reegel 2).
• Vara: BCNF, PK lihtne. Sisaldab mitte-võtmeid (`vara_aadress`, `rent`, `omaniku_nr`). 5NK (Reegel 3 või 2).
• Rentimine: BCNF. PK pole lihtne. Sisaldab mitte-võtmeid (`kliendi_nr`, `rendi_lopp`). 5NK (Reegel 3).

Kõik tabelid näivad olevat ka 5NK.

Andmete liiasus ja ortogonaalsus

Lahendus: Isiku tabel

Isik (isik_id (PK), eesnimi, perenimi)

Klient (kliendi_nr (PK), isik_id (FK -> Isik) (AK))

Omanik (omaniku_nr (PK), isik_id (FK -> Isik) (AK))

Järgmised sammud

Vaatame lõplikke tabeleid. Kollane tähistab Mari Maasika (I02) andmeid/rolle.

Lõplikud tabelid (pärast ortogonaalsuse rakendamist)

Pärast ortogonaalsuse printsiibi rakendamist ja keskse Isik tabeli loomist on meie andmebaasi struktuur ja andmed järgmised.

Roheline taust näitab algsest 1NK esimesest reast pärit andmeid. Kollane taust näitab isiku I02 (Mari Maasikas) andmeid/rolle tabelites Isik, Klient ja Omanik. Tabelis Vara on nüüd veerg `rent`.

Lõplik tabelite struktuur

Tabel: Isik

Isik (
    isik_id (PK),
    eesnimi,
    perenimi
)
Primaarvõti (PK): isik_id

Tabel: Klient

Klient (
    kliendi_nr (PK),
    isik_id (FK -> Isik) (AK)
)
Primaarvõti (PK): kliendi_nr
Alternatiivvõti (AK): isik_id
Välisvõti (FK): isik_id -> Isik(isik_id)

Tabel: Omanik

Omanik (
    omaniku_nr (PK),
    isik_id (FK -> Isik) (AK)
)
Primaarvõti (PK): omaniku_nr
Alternatiivvõti (AK): isik_id
Välisvõti (FK): isik_id -> Isik(isik_id)

Tabel: Vara

Vara (
    vara_nr (PK),
    vara_aadress,
    rent, -- Vara rendihind
    omaniku_nr (FK -> Omanik)
)
Primaarvõti (PK): vara_nr
Välisvõti (FK): omaniku_nr -> Omanik(omaniku_nr)

Tabel: Rentimine

Rentimine (
    vara_nr (PK osa) (FK -> Vara),
    kliendi_nr (FK -> Klient),
    rendi_algus (PK osa),
    rendi_lopp
)
Primaarvõti (PK): (vara_nr, rendi_algus)
Välisvõti (FK): kliendi_nr -> Klient(kliendi_nr)
Välisvõti (FK): vara_nr -> Vara(vara_nr)

Lõplikud näidisandmed

Isik tabel

Klient tabel

Omanik tabel

Vara tabel

Rentimine tabel

Kokkuvõte

Selle lõpliku struktuuriga on isikuandmed hoitud ühes kohas (Isik tabel). Vara omadused, sh `rent`, on tabelis Vara. Konkreetsed rendiperioodid on tabelis Rentimine.