Adatbázis rendszerek. Molnár Bence. Szerkesztette: Koppányi Zoltán

Adatbázis rendszerek Molnár Bence

Szerkesztette: Koppányi Zoltán

Kiterjesztett relációs algebra

Az ismétlődések megszüntetése – Delta op. —A

műveleteinket multihalmazon definiáltuk

— Így

a sorok ismétlődhetnek

— Előfordul

hogy csak vagyunk kíváncsiak

— Ekkor

δ (S ) δ (

a

különálló

sorokra

használható a delta operátor. Név

Jegy Jelenlét

Kiss Pista

1

14

Nagy Péter 3

14

Kiss Péter

1

14

Nagy Ákos

3

10

Név

Jegy Jelenlét

)= Nagy Péter

1

14

3

14

Nagy Ákos

3

10

Kiss Pista

Attribútum átnevezés — Az

attribútumok nevei átnevezhetőek

a nyilat (→ ) használhatjuk egy operátor, művelet belsejében

— Erre — Így

például projekcióesetén:

π réginév → újnév (S ) Név

π Név → Hallgató (

Kiss Pista

Jegy Jelenlét 1

14

Nagy Péter 3

14

Kiss Péter

1

14

Nagy Ákos

3

10

Hallgató

)= Nagy Péter Kiss Pista

Nagy Ákos

Attribútum átnevezés — Az

átnevezés után ezzel az új attribútum névvel végezhetünk műveleteket.

σ Hallgató=' Kiss Pista '( π Név → Hallgató (

=

Név

Jegy Jelenlét

Kiss Pista

1

14

Nagy Péter 3

14

Kiss Péter

1

14

Nagy Ákos

3

10

Hallgató Kiss Pista

))

Kiterjesztett projekció — Új

attribútum vezethető le

πTermék , Darab∗Ár

(

=

Termék

Jegy Jelenlét

Tej

1

250

Kifli

3

50

Kenyér

2

120

Túró Rudi

3

200

Termék

Darab*Ár

Tej

250

Kifli

150

Kenyér

240

Túró Rudi

600

)

Kiterjesztett projekció + Átnevezés —A

levezetett átnevezzük

új

πTermék , Darab∗Ár →ÖsszÁr(

=

attribútumot

Termék

Jegy Jelenlét

Tej

1

250

Kifli

3

50

Kenyér

2

120

Túró Rudi

3

200

Termék

ÖsszÁr

Tej

250

Kifli

150

Kenyér

240

Túró Rudi

600

legtöbbször

)

Csoportosítás — Csoportosítás

a reláció sorainak „csoportokba” történő beosztása a reláció egy vagy több attribútumának értékétől függően.

— Jele:

γ attr 1, attr 2, ... (S ) Termék

γTermék (πTermék (

Darab

Kenyér

1

Kifli

3

Tej

1

Kifli

3

))

Lépésről lépésre Termék

π Termék (

Darab

Kenyér

1

Kifli

3

Tej

1

Tej

Kifli

3

Kifli

Termék

γTermék (

Termék

Kenyér Kifli Tej Kifli

)=

Termék

)=

Kenyér Kifli Tej

Kenyér Kifli

Csoportosítás + Összegzés — Ahogy

láttuk a csoportosítás ugyanolyan elemeket von össze az adott attribútumon.

— Azonban

sorokat, módon.

a többi attribútum is tartalmaz ezeket összesíthetjük valamilyen

— Az

összesítéshez különböző függvényeket alkalmazhatunk a gamma operátoron belül.

— Ezek

a következőek: SUM, AVG, MIN, MAX, COUNT, FIRST, LAST

Az eredmény reláció felépítése —

Osszuk a reláció sorait csoportokba. Egy csoport azokat a sorokat tartalmazza, amelyeknek az {attr1, attr2, …} listán szereplő csoportosítási attribútumokhoz tartozó értékei megegyeznek. Ha nincs csoportosítási attribútum, akkor az egész R reláció egy csoportot képez.

—

Minden csoporthoz hozzunk amelyik tartalmazza: csoport

létre

olyan

sort,

—

Szóban forgó attribútumait.

csoportosítási

—

Az {attr1, attr2, …} lista összesítési attribútumaira vonatkozó összesítéseket.

Csoportosítás + Összegzés (Példa - SUM) Termék

γTermék , SUM( Darab) (

Darab

Kenyér

1

Kifli

3

Tej

2

Kifli

5

Termék

SumDarab

Kenyér

1

Kifli

8

Tej

2

)=

Kiterjesztett projekció — Új

attribútum vezethető le

πTermék , Darab∗Ár

(

=

Termék

Jegy Jelenlét

Tej

1

250

Kifli

3

50

Kenyér

2

120

Túró Rudi

3

200

Termék

Darab*Ár

Tej

250

Kifli

150

Kenyér

240

Túró Rudi

600

)

Csoportosítás + Összegzés (Példa – Több attribútumra)

γTermék , SUM( Darab)→ÖsszDb , SUM (ÖsszDb∗Ár )→ÖsszÁr

(

Termék

Darab

Ár

Kenyér

1

100

Kifli

3

100

Kifli

3

150

Kenyér

2

150

Tej

2

100

)=

Termék

ÖsszDb

ÖsszÁr

Kenyér

3

400

Kifli

6

750

Tej

2

200

Csoportosítás + Összegzés (Példa - COUNT) Termék

γTermék , COUNT( Darab ) (

Darab

Kenyér

1

Kifli

3

Tej

2

Kifli

5

Termék

CountDarab

Kenyér

1

Kifli

2

Tej

1

)=

Csoportosítás + Összegzés (Példa - FIRST) Termék

γTermék , FIRST ( Darab)

(

Darab

Kenyér

1

Kifli

3

Tej

2

Kifli

5

Termék

FirstDarab

Kenyér

1

Kifli

3

Tej

2

)=

Csoportosítás + Összegzés (Példa – Több attribútumra)

γTermék , SUM( Darab)→ÖsszDb , SUM (ÖsszDb∗Ár )→ÖsszÁr

(

Termék

Darab

Ár

Kenyér

1

100

Kifli

3

100

Kifli

3

150

Kenyér

2

150

Tej

2

100

)=

Termék

ÖsszDb

ÖsszÁr

Kenyér

3

400

Kifli

6

750

Tej

2

200

Sorrendezés —A

rendezetlen halmazon a sorba rendezés operátor segítéségével a rekordok rendezhetőek. ABC sorrend, növekvő, stb.

— Jele:

τ Név (

τ attr 1, attr 2, ... (S ) Név

Jegy

Jelenlét

Név

Kiss Pista

1

14

Kiss Aladár 2

14

Nagy Péter

4

14

Kiss Pista

1

14

Kiss Aladár

2

14

Nagy Péter 4

14 10

Nagy Péter

3

10

Nagy Péter 3

)=

Jegy Jelenlét

Sorrendezés, több attribútumon —

Először az első attribútumon rendezünk, majd az azonos csoportba lévőket a következőn, és így tovább.

τ Név , Jegy (

Név

Jegy

Jelen lét

Kiss Pista

1

14

Nagy Péter

4

14

Kiss Aladár

2

14

Név

Jegy

Jelenlét

Nagy Péter

3

10

Kiss Aladár

2

14

Kiss Pista

1

14

Nagy Péter

3

10

Nagy Péter

4

14

)=

SQL

Történet —

1970-es évek eleje IBM SEQUEL (Structured English QUery Languge)

—

Structured/Standard Query Language

—

1986-tól ANSI, 1987-től ISO szabvány

—

SQL2 ('92), SQL3 ('99), ...

—

folyamatos fejlesztés (SQL2011)

—

szabvány, melyet többé-kevésbé a legtöbb relációs adatbázis-kezelő támogat

—

Relational Software, Inc. → Oracle Corp.

Alapok —

Magja ekvivalens a relációs algebrával, de annál bővebb —

összesítő műveletek (pl. SUM, AVG)

—

adatbázis-módosítások

—

Nem procedurális nyelv (deklaratív), tehát az alapvető kérdés az, hogy mit kell csinálni, és nem az, hogy hogyan.

—

Adatleíró (DDL) -, Adatmódosító (DML) -, Adatelérést vezérlő (DCL) -, Lekérdező (QL) nyelv

SQL alapok —

adatbázis létrehozása CREATE DATABASE adatbazis;

—

jogosultságok

—

felhasználó

—

elérés „helye”

—

kódolás (UTF8, LATIN2 [ISO-8859-2])

—

sablonok (pl. téradatok tárolása esetén)

—

használat

—

explicit definíció kapcsolódáskor

—

USE adatbazis;

—

Használt karakterek: [_a-z0-9]

Áttérés relációs algebráról

Emlékeztető Relációs adatbázis séma — Relációs

séma: Hallgató(Neptun: Szöveg, Név: Szöveg, Születési_év: Szám)

— Relációs

adatbázis séma: Hallgató Neptun: Szöveg Név: Szöveg Születési_év: Szám ...

Tábla létrehozása CREATE TABLE hallgato ( neptun VARCHAR(6) [...], nev VARCHAR(50), szuletesi_ev INTEGER, atlag DOUBLE PRECISION, PRIMARY KEY (neptun) );

Hogyan (ne) használjuk a dokumentációt? — PostgreSQL — MySQL — Oracle — W3Schools

Tábla létrehozása — CREATE

TABLE táblanév

— …: —

DEFAULT alapértelmezett érték

—

NULL /NOT NULL

—

UNIQUE (!= PRIMARY KEY!)

—

AUTO INCREMENT/Sequence/AutoNumber

Tábla módosítása —

ALTER TABLE táblanév ADD oszlopnév típus; —

—

ALTER TABLE táblanév DROP oszlopnév; —

—

DROP hallgato;

TRUNCATE táblanév; —

—

ALTER TABLE hallgato RENAME TO bme_hallgatok;

DROP táblanév; —

—

ALTER TABLE hallgato DROP nev;

ALTER TABLE táblanév RENAME TO új_táblanév; —

—

ALTER TABLE hallgato ADD szuletesi_hely VARCHAR(50);

TRUNCATE hallgato;

Változtatások esetén figyelni kapcsolatok frissítésére is!

kell

a

többi

táblával

való

Adatfeltöltés —

Beszúrás —

INSERT INTO táblanév [(attribútum1, attribútum2, …)] VALUES (érték1, érték2, …);

—

INSERT INTO hallgato (neptun, nev, szuletesi_ev, atlag) VALUES ('ABCDEF', 'Kiss Péter', 1993, 4.5);

—

LOAD DATA

—

COPY

Adatmódosítás —

—

Módosítás —

UPDATE táblanév WHERE feltétel;

SET

attribútum

=

érték

—

UPDATE hallgato SET atlag = 4.6 WHERE nev = 'Nagy Sándor';

Törlés —

DELETE FROM táblanév WHERE attribútum = érték;

—

DELETE hallgato WHERE nev = 'Kovács József';

Vetítés SQL-ben — Vetítés

(projekció) [π, Π]: a reláció bizonyos attribútumait megtartjuk és a többit törölve új relációt hozunk létre.

— SELECT

nev, szuletesi_ev FROM hallgato; neptun

— SELECT

nev

szuletesi_ev

* FROM hallgato; neptun

nev

szuletesi_ev

Szelekció SQL-ben — Lekérdezés — SELECT

Péter';

eredmény sorainak szűrése

* FROM hallgato WHERE nev='Kis

— Több

feltétel is lehet, logikai kapcoslat közöttük

— Módosítási

jelentőségű

parancsoknál

különös

Egyszerű lekérdezések —

SELECT attribútum(ok) FROM táblá(k) WHERE tulajdonság(ok); —

SELECT nev FROM hallgatok WHERE nev = 'Kiss Péter';

—

attribútumok, táblák elválasztása vesszővel

—

tulajdonságok összefűzése logikai operátorral: AND, OR

—

operátorok: =, <>, >, <, =>, <=, LIKE, BETWEEN, IN, NOT

—

ismétlődések megszüntetése (DISTINCT)

—

aliasok (AS) -

—

különleges karakterek: *, _, % (Access-ben: *, ?, #)

—

escape karakterek

—

Idézőjelek: ', "

—

kis- és nagybetűk

Nézettáblák —

CREATE TABLE: fizikailag létrejön (tárolásra kerül)

—

CREATE VIEW nézettábla AS lekérdezés; —

CREATE VIEW hallgato_a AS SELECT * FROM hallgato WHERE nev LIKE 'A%';

—

nem jön létre (csak a definíció tárolódik), nem foglal tárkapacitást

—

lekérdezhető

—

módosítható? (materializált view)

—

DROP VIEW nézettábla;

Sorba rendezés — SELECT

* FROM hallgato WHERE nev LIKE 'Kovács%' ORDER BY szuletesi_hely ASC/DESC;

neptun_kod

nev

szuletesi_hely

neptun_kod

nev

szuletesi_hely

Abádszalók

Zalaegerszeg

Abádszalók

Zalaegerszeg

Almásfüzitő

Záhony

Budapest

Záhony

…

…

Sorok számának korlátozása —

SELECT * FROM tabla LIMIT sorok_szama kezdo_sor;

—

SELECT nev, jegy FROM hallgatok ORDER BY jegy LIMIT 1;

—

Ha egy sort keresünk, érdemes ezt beleírni, mert gyorsítja a lekérdezést, hiszen nem keres tovább.

—

Access: SELECT TOP 1 FROM hallgatok ORDER BY jegy;

—

Click to add Text

Csoportosítás —

Valamilyen attribútum értéken egyező rekordok csoportosítása, „együttes kezelése”

—

SELECT AVG(jegy) FROM hallgato WHERE targy = 'Adatbázisok' GROUP BY szuletesi_ido;

—

Feltétel megadása a csoport tulajdonságaira

—

SELECT AVG(jegy) FROM hallgato WHERE targy = 'Adatbázisok' GROUP BY szuletesi_ido HAVING AVG(jegy)>=3;

Függvények használata —

SELECT COUNT(*) FROM hallgato;

—

SELECT AVG(jegy) FROM hallgato;

—

SELECT MIN(jegy) FROM hallgato;

—

SELECT MAX(jegy) FROM hallgato;

— SELECT

SUM(jegy)/COUNT(*) FROM hallgato jegy IS NOT NULL;

— SELECT

DISTINCT szuletesi_hely FROM hallgato;

Szöveg függvények —

SELECT vnev || " " || knev FROM hallgatok;

—

SELECT * FROM hallgatok WHERE vnev || " " || knev LIKE "Nagy Béla";

—

MySQL (MariaDB): CONCAT()

—

PgSQL: ||

—

Access: &

—

SELECT * FROM tantargyak WHERE SUBSTRING(kod FROM 6 TO 7)="FT";

Szabadszavas kersés — mezo

LIKE 'szoveg'

— SELECT

'Kiss%';

— Joker

* FROM hallgatok WHERE nev LIKE

karakterek:

—

%: tetszőleges karakter

karakter;

—

_: egy tetszőleges karakter

0,1,sok

Bonyolultabb lekérdezések —

SELECT nev FROM hallgato WHERE (nev LIKE '%Péter' OR nev LIKE '%Sándor') AND nev LIKE 'Nagy%';

—

SELECT nev FROM hallgato WHERE nev LIKE 'Kov_cs%';

—

SELECT eloadas_hossza*60 AS perc FROM targyak;

—

SELECT nev FROM hallgatok WHERE targy = 'Adatbázisok' AND NOT bukott;

Metszet/Unió — Általános

formula:

(SELECT neptun_kod FROM hallgatok WHERE nev LIKE 'Nagy%') INTERSECT/UNION (SELECT neptun_kod FROM hallgatok WHERE szuletesi_hely = 'Budapest'); — szintaktika — attribútumok

száma

Subselect — film(cim,

ev, hossz, studioNev, rendezoID)

— rendezo(ID,

nev, cim, bevetel)

— SELECT

nev FROM rendezo WHERE ID = (SELECT rendezoID FROM Film WHERE cim = 'Gyűrűk Ura');

— SELECT

* FROM film WHERE rendezoID IN (SELECT ID FROM Rendezo WHERE nev LIKE 'P_ter%');

Több táblát érintő lekérdezések — film(cim,

ev, hossz, studioNev, rendezoID)

— rendezo(ID, — SELECT

— SELECT

nev, cim, bevetel)

cim, nev FROM rendezo, film; (?)

cim, nev FROM rendezo, WHERE film.rendezoID = rendezo.ID;

film

Kapcsolatok — természetes

összekapcsolás

—

az összekapcsolási feltétel az azonos nevű attribútumok egyenlőségéből képződik

—

nincs más feltétel

—

az egyenlő attribútumok közül csak egyet tartalmaz az eredmény

— théta-összekapcsolás

(csak = operátor)

Kapcsolatok oktatok oktato

tanszekKod

Koppányi Zoltán

FMT

Lovas Tamás

FMT

Tuchband Tamás

AGT

Gipsz Jakab

NULL tanszekek tanszekKod

tanszekNev

FMT

Fotogrammetria

AGT

Geodézia

OCT

Szerves Kémia

Kapcsolatok (természetes) — SELECT

* FROM oktatok NATURAL JOIN tanszekek; oktato

tanszekKod

tanszekNev

Koppányi Zoltán

FMT

Fotogrammetria

Lovas Tamás

FMT

Fotogrammetria

Tuchband Tamás

AGT

Geodézia

Kapcsolatok (INNER JOIN) — SELECT

* FROM oktatok INNER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

Kapcsolatok —

Lógó sor: olyan sor, amelyek egy másik reláció egyetlen sorával sem kapcsolódnak össze.

—

Külső összekapcsolások —

a külső kapcsolatok esetén a lógó sorokat is megjelenítjük

—

teljes (FULL OUTER JOIN)

—

jobbról, balról (LEFT OUTER JOIN, RIGHT OUTER JOIN)

Kapcsolatok (LEFT OUTER) — SELECT

* FROM oktatok LEFT OUTER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

Gipsz Jakab

NULL

NULL

NULL

Kapcsolatok (RIGHT OUTER) — SELECT

* FROM oktatok RIGHT OUTER JOIN tanszekek ON oktatok.tanszekKod = tanszekek.tanszekKod; oktatok.oktato

oktatok.tansz ekKod

tanszekek.tan szekKod

tanszekek.tan szekNev

Koppányi Zoltán

FMT

FMT

Fotogrammetria

Lovas Tamás

FMT

FMT

Fotogrammetria

Tuchband Tamás

AGT

AGT

Geodézia

NULL

NULL

OCT

Szerves Kémia

Hivatkozási épség megőrzése —

NULL értékre állítás módszere

—

Kaszkádolt frissítés CREATE TABLE táblanév ( …, ON DELETE SET NULL ON UPDATE CASCADE );

Megszorítások —

Attribútumokra vonatkozó (definíciókor) CREATE TABLE hallgato ( neptun_kod VARCHAR(6) NOT NULL, atlag FLOAT CHECK (altag > 0), nem CHAR(1) CHECK (nem IN ('F','N')), ... );

Megszorítások —

Értéktartományra vonatkozó

—

PostgreSQL példa!

—

CREATE DOMAIN azonositok INT CHECK (VALUE >= 10000);

—

CREATE TABLE hallgatok ( azon azonositok, … );

Megszorítások — Rekordra

— CREATE

vonatkozó

TABLE hallgatok

( nev CHAR(30) UNIQUE, nem CHAR(1) CHECK (nem = 'N' OR nem = 'F') );

Megszorítások —

—

Elnevezés —

... nev CHAR(30) CONSTRAINT nevKulcs PRIMARY KEY ...

—

CREATE DOMAIN fizetes INT CONSTRAINT nagySzam CHECK (VALUE >= 100000);

Módosítás —

ALTER TABLE szinesz DROP CONSTRAINT nevKulcs;

—

ALTER TABLE rendezo ADD CONSTRAINT nagySzam;

Az adatbázis-kezelő rendszerek jelenkora

Indexek —

Adatstruktúra, ami a táblában található adatok elérését gyorsítja. Ezáltal gyorsulnak a lekérdezések!

—

Hátrányai:

—

—

lassabb írás a táblába (az indexet is írni kell),

—

nagyobb kapacitás igény.

Egy vagy több attribútumra (függvények, kifejezések).

vonatkozik

Index architektúrák —

Non-clustered (nem-csoportosított) Az adatok önkényes sorrendjével ellentétben az index kulcsokat sorrendben tároljuk a rekord pointerekkel (pl. sor; oszlop) együtt. Azaz az adatok sorrendje a táblában és az indexben eltérő.

—

Clustered (csoportosított) A fizikai adatsorokat rendezi össze (a sorrendjük megváltoztatásával) az index blokkokkal, amelyek az adatsorokra mutatnak. Így, az adatok egymásutániságának köszönhetően nagymértékben gyorsulhat az olvasási sebesség.

—

Cluster

Index típusok — PostgreSQL: —

Hash tábla

—

B-tree

—

R-tree

—

GiST (geometria)

SP-GiST — GIN (tömbök) —

Hash tábla

B-tree

R-tree

Mit érdemes indexelni? — Sokszor

keresett adatok (WHERE ...)

— Elsődleges

és idegen kulcsok (… JOIN)

— Geometria

(!)

— Nem

érdemes

—

ritkán használt attribútumok

—

bináris adatok

Index létrehozása — PostgreSQL —

példák:

egy adott attribútumra: CREATE UNIQUE INDEX title_idx ON films (title);

—

egy függvényre: CREATE INDEX ON films ((lower(title)));

Tárolt eljárás — Mikor

érdemes alkalmazni?

— Bemenő

adatok függvényében, azokat feldolgozva, figyelembe véve ad eredményt, vagy frissíti az adatokat.

— Triggerek

Tárolt eljárások — Tulajdonképpen

egy „program”, amelyeket programozási eszközökkel (nyelvvel) hozunk létre, melyek lehetnek: —

Oracle: PL/SQL

—

PostgreSQL: PL/pqSQL

—

C, Python, Perl, ...

Tárolt eljárások CREATE OR REPLACE FUNCTION … RETURNS …. AS $$ DECLARE ...; BEGIN SELECT .. INTO ... FROM ..; LIMIT 1; RETURN ...; END; $$ LANGUAGE plpgsql;

Triggerek —

Ravasz :)

—

Esemény-feltétel-művelet szabály (+ tárolt eljárás/függvény)

—

Események bekövetkeztekor

—

—

beszúrás

—

törlés

—

módosítás

—

tranzakciók befejeződése

Ha a feltétel… —

teljesül

—

nem teljesül

Triggerek CREATE OR REPLACE FUNCTION bonusz_fv() RETURNS trigger AS $$ BEGIN -- NEW => az újonnan beszúrt sor -- ha az új sorban a jegy = 5 -- 10 bónusz pont jár IF NEW.jegy = 5 THEN NEW.bonusz = 10; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER bonusz BEFORE INSERT ON jegy FOR EACH ROW EXECUTE PROCEDURE bonusz_fv();

Triggerek — AFTER/BEFORE/INSTEAD — UPDATE — több

OF

OF attribútum/INSERT/DELETE

művelet

Tranzakciók —

Mi a tranzakció? —

SQL utasítások sora (nincs méretkorlát)

—

„mindent vagy semmit elv”

—

COMMIT; ROLLBACK

—

SAVEPOINT

—

ACID: Atomicity, Durability.

Consistency,

Isolaton,

—

Típusai: automatikus, explicit, implicit

ACID::A —

Atomicity (atomi): „mindent vagy semmit”

—

A tipikus példa a banki átutalás szokott lenni: A számláról leveszünk 10000 Ft-ot, B számlára ráteszünk 10000 Ft-ot. Ha a tranzakció sikerül, akkor B-nél lesz a szóban forgó 10000 Ft, ha nem, akkor A-nál marad. A félkész állapotok, miszerint sehol sincs a pénz, vagy éppen mindkét helyen megvan, nem megengedhetőek.

—

History szerepe

ACID::C —

—

Consistency (konzisztens): konzisztencia megőrzés (a tranzakció után is megmarad). —

Nem lehetnek befejezetlen írási műveleteket (nincs „félig” írt rekord).

—

Referenciális integritás: nem lehet a referenciákat, hivatkozásokat (pl. az idegen kulcsokat) megsérteni, törölni (ROLLBACK). Nem lehet egy adott típusban más típust tárolni. Nem lehet megszorításokat (CONSTRAINTS) átlépni.

Több felhasználó esetén: lockolás (lásd később)

ACID::I —

Isolation (izolált): az egyszerre futó tranzakciók nincsenek hatással egymásra (akár nem is láthatják). Ez magába foglalja a konkurens hozzáférés biztonságos használatát. Az —

isolation level hatással)

—

dirty records

—

repeatable read

—

phantom records

(csak

a

SELECT-ekre

van

ACID::D — Durability

(tartós): COMMIT után az eredmény végérvényesen az adatbázisba kerül.

— Ez

igaz bármilyen helyzetben (hirtelen elmegy az áram, a szerver támadás alatt van, becsapódik egy repülőgép.

Tranzakció típusok — Automatikus:

általános eset, felhasználó kiad egy parancsot, a szerver végrehajtja (és közben tranzakciót nyit, zár)

— Explicit:

felhasználó nyitja (BEGIN TRANSACTION) és zárja (COMMIT/ ROLLBACK) a tranzakciót.

— Implicit:

szerver nyitja a tranzakciót, de a felhasználó zárja (COMMIT/ROLLBACK).

Tranzakció kezelés adatbázisokban —A

„heavyweight” adatbázis-kezelők megpróbálnak „ACID-teljesek” lenni, pl.: PostgreSQL, SQL Server, Oracle

— Más

adatbázisok feladnak pár elvet, így hatékonyabb és gyorsabb adatkezelést biztosítanak, de bizonyos funkcionalitásokat ezáltal nem tudnak biztosítani. Erre jó példa a manapság elterjedőben lévő NoSQL adatbázisok!

Zárolás (lock[olás]) — Többfelhasználós — Az

környezetben

adott felhasználó zárolja az adott rekordot, amelyet éppen ír vagy módosít, így az aktuális tranzakció befejeztéig (COMMIT/ROLLBACK) más nem férhet hozzá. —

optimista

—

pesszimista

Zárak — dirty

read

— non-repeatable — phantom — dead — nincs

read

lock

súlya műveleteknek

— ...

read

(fontossága)

az

egyes

Hibatűrés — SPOF:

single point of failure

— Replikáció —

file/blokk szint

—

WAL: Write Ahead Logging

—

PITR: Point-In-Time Recovery

—

Warm Standby

—

Hot Standby

Adatbázis klaszterek — Felhasználók;

kiszolgálók; adatok

Piaci eloszlás

Téradatbázisok

Téradat szerver programok —

—

Kereskedelmi szoftverek —

ArcGIS Server (Oracle, SQL Server, PostgreSQL) (korábban ArcSDE)

IBM

—

Oracle Spatial (Oracle)

—

MS SQL Server 2008+

—

Geomedia + MS Access

—

SyBase SQL Anywhere (mobil eszközökre is)

—

...

DB2,

Nyílt forráskódú —

PostGIS (PostgreSQL)

—

SpatiaLite (SQLite)

—

...

http://www.gita.hu/e2013/b3/teradat_workshop.pdf

PostGIS —

PostgreSQL kiegészítő —

C++ és plpgsql nyelven íródott

—

Tárolt eljárások

—

Minden adatbázishoz hozzá kell adni, amiben használni szeretnénk Új adattípusok

—

Új összehasonlító operátorok

—

Új összegző függvények

—

Új elemzési lehetőségek

—

—

Geometriát a megjeleníteni

DBMS

rendszerek

nem

tudnak

Pár infó — http://wiki.hup.hu/index.php/PostGIS — http://www.agt.bme.hu/gis/postgis/ — http://workshops.boundlessgeo.com/postgis

-intro/geometries.html — http://www.gita.hu/e2013/b3/teradat_work shop.pdf

Megoldandó feladatok — Adat

séma definíció

— Adattárolás —

Helyhez kötött adatok

— Keresés

– indexelés

— Elemzések — Vetületek

átszámítása

— Geometriai

adatfelvitel?

Séma definíció —

OGC: Simple Feature SQL

—

A geometriai adatok egy mezőben kerülnek ábrázolásra

—

Kódolt forma

—

Nem csak koordináta, hanem teljes poligon is lehet egy mezőben (akár több is)

CREATE TABLE alappontok ( pid integer NOT NULL DEFAULT nextval('alappontok_pid_seq'::regclass), nev character(5), coord geometry(Point,23700), CONSTRAINT pk_pid PRIMARY KEY (pid) ) —

Utólag: AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

Típusok — Point — Linestring — Polygon — Polygonwithhole,

Multipoint, MultiLinestring, MultiPolygon, GeometryCollection

— SRID:

Vetület megadása

Adattárolás - WKT Well Known Text – szöveges adatmegadás egyszerű elemek (2D) —

POINT (30 10) pont,

—

LINESTRING (30 10, 10 30, 40 40) törtvonal,

—

POLYGON ((30 10, 10 20, 20 40, 40 40, 30 10)) felület,

—

POLYGON ((35 10, 10 20, 15 40, 45 45, 35 10), (20 30, 35 35, 30 20, 20 30)) felület lyukkal

több azonos típusú részből —

MULTIPOINT ((10 40 32), (40 30 41), (20 20 22)) 3D

—

MULTIPOINT (10 40 32, 40 30 41, 20 20 22)

—

MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))

—

MULTIPOLYGON (((30 20, 10 40, 45 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))

—

MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), ((20 35, 45 20, 30 5, 10 10, 10 30, 20 35), (30 20, 20 25, 20 15, 30 20)))

több eltérő típusú részből (nem minden kliens támogatja) —

GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10))

—

POINT ZM (1 1 5 60)

http://www.gita.hu/e2013/b3/teradat_workshop.pdf

Adattárolás - WKB Well Known Binary – bináris adatmegadás — 01040000000100000001010000009239006

4507D1C412F1B85CA03BD0F41

— MULTIPOINT(466772.10 — PostGIS

EWKT/EWKB tartalmazza —

260000.47) –

vetületet

is

"SRID=23700;POINT((466772.09765710 8 260000.473886692))"

http://www.gita.hu/e2013/b3/teradat_workshop.pdf

Keresés —

Geometriai kapcsolatok elemzése

—

Megjelenítés

—

Indexelés —

GIST

—

—

CREATE INDEX vez_geom_idx ON vezetekek USING GIST ( geom );

—

::geometry

—

CAST()

Elemző függvények —

ST_Equals(GEOMETRY,GEOMETRY)

—

ST_Contains(GEOMETRY,GEOMETRY)

—

ST_Within(GEOMETRY,GEOMETRY)

—

ST_Intersects(GEOMETRY,GEOMETRY)

—

ST_Crosses(GEOMETRY,GEOMETRY)

—

ST_Disjoint(GEOMETRY,GEOMETRY)

—

ST_DWithin(GEOMETRY,GEOMETRY,DISTANCE)

—

ST_Relate((GEOMETRY,GEOMETRY)

—

ST_Intersection(GEOMETRY,GEOMETRY)

—

ST_Union(GEOMETRY,GEOMETRY)

—

ST_Buffer(GEOMETRY,DISTANCE)

—

ST_ConvexHull(GEOMETRY)

—

ST_Transform(GEOMETRY,SRID)

Lekérdező függvények —

ST_Area(POLYGON)

—

ST_Perimeter(GEOMETRY)

—

ST_Distance(GEOMETRY,GEOMETRY)

—

ST_Dimension(GEOMETRY)

—

ST_AsText(GEOMETRY)

—

ST_X(POINT)

—

ST_Y(POINT)

—

ST_AsGeoJSON(GEOMETRY)

—

ST_NumPoints(GEOMETRY)

—

ST_PointN(GEOMETRY,integer)

—

ST_NumGeometries(GEOMETRY)

—

ST_GeometryN(GEOMETRY,integer)

—

ST_GeometryType(GEOMETRY)

Operátorok — <->

Távolság

—~

Tartalmaz

—@

Benne van

— ~=

Azonos

— http://postgis.refractions.net/documentati

on/manual-2.0/reference.html

Vetületek — Proj

függvénykönyvtár

— SRID —

EOV: 23700

—

WGS84: 4326

—

Google mercator: 900913 vagy 3857

Adatfelvitel —

DBMS —

—

Külső adatforrások (import) —

—

Geometriai adatot csak szövegesen lehet (WKT) OSM

Grafikus program —

QGIS

—

Geomedia

—

(ArcGIS)

—

Web

—

...

Adatfelvitel —

UPDATE csomopontok coord=ST_SetSRID(ST_MakePoint(x, y), 23700);

—

INSERT INTO csomopontok (coord, adat1, adat2) VALUES (ST_SetSRID(ST_MakePoint(x, y), 23700),’Egyadat’, ’Kétadat’);

—

shp2pgsql.exe –s 4326 –W shape_kiterjeszets_nelkul ujtablanev adatbazis

—

osm2pgsql.exe -s /file/path/name.osm

—

DXF-re nincs jó direkt megoldás OpenSource körökben (kereskedelmi: FME)

-U

felhasznalo

-d

SET

latin2 adatbazis

Kérdés — Miért

különül megjelenítéstől?

el

az

adatháttér

a

—

Mert az adatháttér több alkalmazás számára is használható lehet, felesleges többször tárolni

—

Egy alkalmazásban ugyanaz többször is megjelenhet, kinézettel.

az de

adat más

WebGIS —

Weboldal (html, javascript, java, flash...) —

Webserver (Apache, Tomcat, Lighthttpd, Nginx, IIS) —

Térképszerver (Geoserver, Mapserver) — WMS raszteres, georeferált adat — Web Map Service — Képek (tif) — WFS vektoros, georeferált adat — Web Feature Service — Adatbázis (PostGIS, shapefile, csv...) — WPS — Web Processing Service — Elemzések a szerveren futnak

WebGIS

QGIS — Ingyenes — PostGIS

térinformatikai software

adatokat olvas

— Rengeteg

fajta ki és bemeneti adatok

— Digitalizálásra — Saját

is alkalmas

geometriai elemző funkciók

QGIS elemzési lehetőségek —

Minden ami PostGIS-ben

—

Adatbázis->DB kezelő->DB kezelő

=> SQL ablak

—

Ezt kimásolva akár a szerveren is elmenthetőek Tárolt eljárás — Nézet QGIS natív elemzések —

— —

Vektor->

—

Ez csak a QGIS-ben lesz használható

—

Különböző adatforrások egyben elemzhetőek — — — —

Shape SQLite – SpatiaLite CSV PostGIS

Geometriai vs. algebrai kapcsolat — Viszonylat

– megálló N:M kapcsolat

—

Viszonylat(vid, jaratsuruseg….)

—

Megallo(mid, x, y)

—

Viszonylat_megallo(pid, vid(IK), mid(IK))

Szótár I. —

Proj —

—

GDAL —

—

Képek átalakítására, georeferálására, készítésre használható függvénykönyvtár

képpiramis

WKT —

—

Vetületi számításokért felelős függvénykönyvtár, megtalálható benne a legtöbb vetületi rendszer

Well Known Test – geometriai objektumok szöveges leírása

WKB —

Well known Binary - geometriai objektumok bináris leírása

Szótár II. —

OpenStreetMap (OSM) —

—

Ajax —

—

Ajax segítségével továbbított jól struktúrált adat

ExtJS, GeoExt, OpenLayers —

—

Aszinkron kommunikáció a böngésző és a szerver közt (Oldal egyes részeinek megváltoztatása a teljes újratöltés nélkül)

JSON, GeoJSON —

—

Ingyenes, közösségi vektoros térkép

Böngészőben használható javascript függvénykönyvtárak, megkönnyítik a megjelenítés megvalósítását

Pmapper —

Komplex megvalósítás

Köszönöm a figyelmet! Adatbázis rendszerek