Adatbázis-tervezés alapjai

1Mi az adatbázis?

Az adatbázis rendezett módon tárolt adatok gyűjteménye, amelyet úgy alakítunk ki, hogy gyorsan és megbízhatóan tudjunk benne keresni, adatot beszúrni, módosítani és törölni. Egy webshop esetében ilyen adat például a vásárlók listája, a termékek, vagy a leadott rendelések.

Az adatbázist nem közvetlenül kezeljük, hanem egy adatbázis-kezelő rendszeren (angolul DBMS – Database Management System) keresztül. A mi esetünkben ez a MySQL. A DBMS feladata, hogy fogadja az utasításainkat (lekérdezéseket), gondoskodjon az adatok épségéről, a párhuzamos hozzáférésről és a biztonságról.

2Adatbáziskezelő rendszerek — nem csak relációs

A relációs (SQL-alapú) rendszerek a legelterjedtebbek — ezt tanuljuk mi is —, de nem ez az egyetlen út. A nem relációs rendszereket összefoglaló néven NoSQL-nek („not only SQL” — nem csak SQL) nevezzük. Több családjuk van: mindegyik más adatmodellt használ, és más feladatra ideális.

3A relációs modell

A relációs modellben az adatokat táblákban tároljuk. Egy tábla olyan, mint egy jól megszervezett táblázat: oszlopokból (mezők) és sorokból (rekordok) áll.

• Az oszlop (mező) egy adott tulajdonságot ír le — pl. a vásárló e-mail-címe. Minden oszlopnak van neve és adattípusa.
• A sor (rekord) egy konkrét egyedet ír le — pl. egyetlen vásárló minden adata.
• A cella egy sor és egy oszlop metszéspontja: egyetlen konkrét érték.

4Entitások és attribútumok

A tervezés első lépése, hogy összegyűjtjük, miről akarunk adatot tárolni. Ezeket a „dolgokat” nevezzük entitásoknak. Egy webshopban tipikus entitások:

vásárló termék kategória rendelés cím

Minden entitásból később egy tábla lesz. Az entitás tulajdonságait attribútumoknak nevezzük — ezekből lesznek az oszlopok. Például a termék entitás attribútumai: név, ár, leírás, készlet.

5Kulcsok

Hogyan különböztetünk meg egyértelműen két vásárlót, ha történetesen mindkettőt Nagy Annának hívják? Erre valók a kulcsok.

Elsődleges kulcs (PRIMARY KEY)

Az elsődleges kulcs egy olyan oszlop (vagy oszlopok együttese), amely egyértelműen azonosít minden sort a táblában. Két fontos szabály: az értéke nem ismétlődhet (egyedi), és nem lehet üres (NULL). A gyakorlatban szinte mindig egy automatikusan növekvő id oszlopot használunk erre.

Idegen kulcs (FOREIGN KEY)

Az idegen kulcs egy olyan oszlop, amely egy másik tábla elsődleges kulcsára hivatkozik. Így kötjük össze a táblákat. Például a rendeles táblában egy vasarlo_id oszlop megmondja, melyik vásárló adta le a rendelést.

6Kapcsolattípusok

Az entitások között kapcsolatok vannak. A kapcsolat számossága (kardinalitása) azt mondja meg, hány sor kapcsolódhat egymáshoz. Három alaptípus van.

1:1 — egy sorhoz pontosan egy másik tartozik (pl. egy vásárlónak egy kiterjesztett profilja). Ritka; gyakran egyetlen táblába is összevonható.

1:N — a leggyakoribb. Egy vásárlóhoz több rendelés tartozhat, de egy rendelés egyetlen vásárlóhoz. Megvalósítás: a „sok” oldali táblába (rendeles) teszünk egy idegen kulcsot.

N:M — sok a sokhoz

Egy rendelés több terméket tartalmazhat, és egy termék több rendelésben is szerepelhet. Ezt közvetlenül nem lehet megvalósítani — kell egy kapcsolótábla (más néven kötőtábla), amely két 1:N kapcsolatra bontja a feladatot.

Önmagára hivatkozó (rekurzív) kapcsolat

Különleges eset, amikor egy tábla saját magával áll kapcsolatban: az idegen kulcs ugyanannak a táblának az elsődleges kulcsára mutat. Ez nem egy negyedik számosság — az önhivatkozó kapcsolat is 1:1, 1:N vagy N:M lehet. A webshopban a kategoria tábla ilyen: a szulo_id oszlopa egy másik kategória id-jára mutat. Egy szülőkategóriához több alkategória tartozhat, de minden kategóriának legfeljebb egy szülője van → ez egy önhivatkozó 1:N. A főkategóriáknál (pl. „Elektronika”) a szulo_id értéke NULL.

7A webshop ER-diagramja

Az ER-diagram (Entity–Relationship, egyed–kapcsolat diagram) egyetlen ábrán mutatja meg az összes táblát és a köztük lévő kapcsolatokat. Ezt fogjuk a teljes kurzuson végigvinni. Íme a webshopunk teljes sémája:

8Normalizálás (1NF–3NF)

A normalizálás az a folyamat, amellyel a táblákat úgy alakítjuk át, hogy elkerüljük a redundanciát és a belőle fakadó anomáliákat. Nézzünk egy rosszul megtervezett, „mindent egy táblába” típusú megoldást, és javítsuk lépésről lépésre.

Itt két baj van: a vásárló adatai ismétlődnek (ha Anna e-mailt vált, két helyen kell javítani), és a termekek cella több értéket tartalmaz, amit így nem lehet jól kezelni.

1NF — első normálforma

Szabály: minden cella egyetlen, oszthatatlan értéket tartalmaz, és nincsenek ismétlődő oszlopcsoportok. A több értéket tartalmazó termekek cellát külön sorokra bontjuk.

2NF — második normálforma

Feltétel: a tábla már 1NF-ben van, és minden nem-kulcs oszlop a teljes kulcstól függ — nem csak a kulcs egy darabjától. Ez a szabály csak akkor „élesedik”, ha a kulcs több oszlopból áll (összetett kulcs); egyoszlopos kulcs mellett egy 1NF-es tábla automatikusan 2NF-ben is van.

Nézzük meg a fenti 1NF-es táblát! Egy sorát melyik oszlop azonosítja? A rendeles_id önmagában kevés — a 101-es rendeléshez három sor is tartozik. Egy sort csak a (rendeles_id, termek) páros azonosít egyértelműen: ez tehát összetett kulcs. Most kérdezzük meg minden nem-kulcs oszlopról: a teljes párostól függ, vagy elég hozzá a kulcs egyik fele?

A vásárló neve és e-mailje csak attól függ, melyik rendelésről van szó — az teljesen mindegy, hogy a sorban egér vagy monitor szerepel. Ez a részleges függés, és pontosan emiatt ismétlődik Anna neve három soron át: a kulcs „termék” feléhez ezeknek az adatoknak semmi közük. A 2NF megoldása: a csak a rendeléstől függő adatokat kiemeljük a saját táblájukba — így születik meg a külön rendeles tábla (a vásárló adataival, amelyek onnan a külön vasarlo táblába kerülnek), a termékes sorokban pedig csak a rendelésre mutató hivatkozás marad.

3NF — harmadik normálforma

Feltétel: már 2NF-ben van, és nincs tranzitív függés — vagyis nem-kulcs oszlop nem függhet másik nem-kulcs oszloptól. Például ha tárolnánk az iranyitoszam mellett a varos nevét is, az tranzitív függés lenne (a város az irányítószámtól függ, nem a sor kulcsától).

Léteznek szigorúbb normálformák is

A sor a 3NF-fel nem ér véget — a szigorúbb formákkal azonban jellemzően már csak elméletben vagy speciális rendszerekben találkozni:

• BCNF (Boyce–Codd normálforma, „3,5NF”) — a 3NF szigorítása: minden oszlop(csoport), amelytől bármely másik oszlop függ, maga is kulcsjelölt legyen.
• 4NF — egy tábla ne tároljon két egymástól független, többértékű tulajdonságot (pl. egy oktató nyelvei és tantárgyai külön táblába valók).
• 5NF — a táblát ne lehessen úgy kisebb táblákra bontani, hogy azok összekapcsolása (JOIN) veszteség nélkül visszaadja az eredetit — ha lehet, bontsuk fel.
• 6NF — a legvégletesebb felbontás (minden tábla kulcs + legfeljebb egy további oszlop); főleg adattárházakban, idősoros rendszerekben merül fel.

9Adattípusok

Minden oszlophoz adattípust rendelünk, ami meghatározza, milyen értéket tárolhat. A jó típusválasztás helytakarékos és megelőzi a hibás adatokat. A leggyakoribb MySQL-típusok:

10A tervezés lépései

Foglaljuk össze a folyamatot, amit minden új adatbázisnál érdemes követni:

11Feladatok

kategoria(id PK, nev)
termek(id PK, nev, ar, kategoria_id FK -> kategoria.id)

rendeles(id, vasarlo, termekek)
101    | Nagy Anna | egér, billentyűzet, monitor

rendeles_minden(rendeles_id, vasarlo_nev, vasarlo_email,
                termek_nev, termek_ar, mennyiseg)

12Önellenőrző kvíz

13Fogalomtár

14Összegzés

A teljes folyamatot a webshop ER-diagramja köti össze — ezt visszük tovább az egész kurzuson.