GEIAL Kovács László. GEIAL Kovács László

Adatbá Adatbázis rendszerek I Adatkezelé Adatkezelés és adatbá adatbázis kezelé kezelés alapfogalmai

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

Informá Információ ciós rendszerek Adatok mindenü mindenütt

NSA telekommuniká telekommunikáció ció

bankszektor

kereskedelem

kutatá kutatás

GEIAL Kovács László

1

Informá Információ ciós rendszerek Adatkezelé Adatkezelés szerepe

alkalmazá alkalmazás szerver

adatbá adatbázis szerver

Web szerver

kliensek GEIAL Kovács László

Informá Információ ciós rendszerek Hatalmas üzlet rejlik az adatbá adatbáziskezelé ziskezelésben

Oracle


folyamatos állá lláskí skínálat VIR

2

Adatkezelé Adatkezelés alapjai Szintek állomá llománykezelő nykezelő táblá blázat kezelő kezelő desktop adatbá adatbázis multiuser adatbá adatbázis elosztott adatbá adatbázisok adattá adattárhá rházak funkció funkciók

API

rugalmassá rugalmasság


Excel adatkezelé adatkezelés alapjai Adatok tá tárolá rolása: táblá blázatok Táblá blázat szerkezete: sorok oszlopok

Főmenü menü Adatok

cella c(s,o) Közvetlen mű műveletek:

Sorba rendezé rendezés Szű Szűrő

sorba rendezé rendezés

Részö szösszegek

szű szűrő (szelekció (szelekció)

Érvé rvényesí nyesítés

aggregá aggregálás Érté rtékellenő kellenőrzé rzés: cella adattí adattípus megszorí megszorítások (é (érvé rvényesí nyesítés) GEIAL Kovács László

3

Excel adatkezelé adatkezelés alapjai elemi(QBE)

Szelekció Szelekció megadá megadása

külön tá táblá blázatban formula

mező1

mező2

>érték

=érték

mező3

soron bel belü ül AND kapcsolat sorok kö között OR kapcsolat A mező mezőhivatkozá hivatkozás a ké képletbe kerü ül (=oszlopcimke ker (=oszlopcimke) oszlopcimke)

<érték

tipus rsz

tipus

ar

r11

skoda

39,00

r5

skoda

38,00

r2

opel

36,00

tipus

=opel

ar >30.0

=fiat

>30.0

=C2 > 24


Excel adatkezelé adatkezelés alapjai összetett mű műveletek makro megí megírása

makro programozá programozás

VB

Főmenü menü Eszkö Eszközök Makró Makró Makró Makrók Létrehozá trehozás Szerkeszté Szerkesztés

indí indító nyomó nyomógomb

Főmenü menü Nézet Eszkö Eszköztá ztárak Űrlapok Nyomó Nyomógomb


4

Excel adatkezelé adatkezelés alapjai Makro mintaprogram: a rendszá rendszám egyedisé egyediségének ellenő ellenőrzé rzése

GEIAL

Sub ellen1() ss1 = 0 For i = 3 To 10 k1 = Sheets(1).Cells(i, 1) s1 = 0 For j = i + 1 To 10 k2 = Sheets(1).Cells(j, 1) If k1 = k2 Then s1 = 1 End If Next j If s1 = 1 Then ss1 = 1 MsgBox (k1 & " nem egyedi") egyedi") End If Next i If ss1 = 0 Then MsgBox ("egyediseg ("egyediseg rendben") rendben") End If End Sub Kovács László

dinamikus vá változó ltozók ciklus cella hivatkozá hivatkozás

elá elágazá gazás üzenet kií kiírás

pozí pozíció ciók bebetonozá bebetonozása

Adatkezelé Adatkezelés kö követelmé vetelményei Az Excel adatkezelé adatkezelése nem tá támogatja az alá alábbi funkció funkciókat: Nagy adatmennyisé adatmennyiség kezelé kezelése Dinamikus adatmennyisé adatmennyiség Kapcsolatok nyilvá nyilvántartá ntartása Megszorí Megszorítások kezelé kezelése Adatvé Adatvédelem Haté Hatékony leké lekérdezé rdezés Nyí Nyílt kapcsoló kapcsolódási felü felület Párhuzamos hozzá hozzáférés GEIAL Kovács László

5

Adatbá Adatbázis fogalma Olyan adatkezelő adatkezelő kell, ami minden igé igényelt funkció funkciót biztosí biztosít Az adatbá adatbázis egy integrá integrált adatrendszer, adatrendszer, mely tö több különbö nböző egyed elő előfordulá fordulásainak adatait adatmodell szerinti struktú struktúrában, perzisztens módon tá tárolja a kapcsolat leí leíró elemek mellett a metaadatokkal metaadatokkal együ együtt, melyek a haté hatékonysá konyság, integritá integritásőrzé rzés, az adatvé adatvédelem biztosí biztosítását szolgá szolgáljá lják.

megkö megkötések kapcsolatok

adatok

jogosultsá jogosultság szerkezet

DB

napló napló


Adatbá Adatbáziskezelő ziskezelő fogalma Az adatrendszer megfelelő megfelelő elé eléréséről is gondoskodni kell Az adatbá adatbázis kezelő kezelő rendszer olyan programrendszer, programrendszer, Amelynek feladata az adatbá adatbázishoz tö törté rténő szabá biztosítása és szabályozott hozzá hozzáférés biztosí az adatbá adatbázis belső belső karbantartá karbantartási mű műveleteinek végrehajtá grehajtása. A DB csak a DBMSDBMS-en keresztü keresztül érhető rhető el legá legálisan

védelem

integritá integritás ellenő ellenőrzé rzés

napló naplózás optimalizá optimalizálás művelet vé végrehajtá grehajtás párhuzamos hozzá hozzáférés ellenő ellenőrzé rzés DBMS GEIAL Kovács László

6

Adatbá Adatbázisrendszer fogalma Az adatbá adatbázis kezelő kezelő, az adatbá adatbázis és az alkalmazó alkalmazói programok együ együttesé ttesét adatbá adatbáziszis-rendszernek nevezik

SQL/...

kliens adatbá adatbáziszis-kezelő kezelő

adatbá adatbázis GEIAL Kovács László

ANSI SPARC modell Az adatbá adatbázis logikai felé felépítése, referencia modellje Felh1 felhaszná felhasználó

Külső lső szint

tervező tervező

Fogalmi szint

programozó programozó

Fizikai szint

Felh2

Felh9 egyed

Globá Globális sé séma

Fizikai szerkezet

tábla

Index, rekord

Függetlensé ggetlenségi szintek: - logikai : a kü külső lső szint és a globá globális szint kö között - fizikai : a globá globális szint és a fizikai szint kö között - hálózati : fizikai szinten belü belül GEIAL Kovács László

7

DBMS funkció funkció struktú struktúrája SQL Kommuniká Kommunikáció ciós ré réteg

SELECT * FROM AUTO ORDER BY RSZ

CFG grá gráf

Utasí Utasítás értelmező rtelmező

DAC má mátrix

Hozzá Hozzáférés ellenő ellenőrzé rzés

ECA modul

Aktí Aktív szabá szabály ellenő ellenőrző rző

QOG grá gráf

Optimalizá Optimalizáló

QEP grá gráf

Végrehajtó grehajtó

TM modul, zá zárolá rolás

Tranzakció Tranzakció vezé vezérlő rlő

Recovery modul

Napló Naplózó modul IO rendszer

read(), read(), write() write()


Nagy adatmennyisé adatmennyiség kezelé kezelése Mit jelent a nagy adatmennyisé adatmennyiség? VLDB (www.vldb.org (www.vldb.org) www.vldb.org) :

400 GB <

kínai telefontá telefontársasá rsaság: 1TB1TB-os tá tábla 108 lap, 5 km hosszú hosszú polc

> 400 év

2005: EMC Corp. Corp. : 100 Tbyte DB, Standford : 500 TByte DB


8

Nagy adatmennyisé adatmennyiség kezelé kezelése A fő fő problé probléma a haté hatékony keresé keresés Kívánalmak idő időben: optimá optimális vá válaszidő laszidő: < 2 s megtű megtűrt vá válaszidő laszidő:

< 20 s

Elvá Elvárások hellyel: minimá minimális redundancia szabá szabályozott redundancia

www.tpc.org GEIAL Kovács László

Adatrekord elé elérési mó módszerek Keresé Keresés gyorsí gyorsítása Szekvenciá Szekvenciális keresé keresés O(N) nem haté hatékony Index: (é (érté rték, pointer) bejegyzé bejegyzések rendezett listá listája 3

7

1

6

11

2

4

9

1

2

3

4

5

6

7

9

5

Rendezett állomá llományban keresé keresés: O(log O(log(N)) log(N)) O(g) = {f | ∃ m,x0 : ∀x > x0: x0: |f(x)| < m |g(x)| } Problé Probléma:

Lemezes állomá llományok eseté esetén majdnem minden elem Olvasá Olvasás kü külön blokkolvasá blokkolvasás


9

Adatrekord elé elérési mó módszerek A blokkolvasá blokkolvasások darabszá darabszámát optimalizá optimalizáló index Indexet ké készí szítünk az indexhez (hierarchikus index) B-fa index szerkezet kereső kereső fa , egy csomó csomópontban M elem és M+1 pointer jó helykihaszná helykihasználtsá ltság (min 50%) csomó csomóponton belü belül rendezett logM(N) logM(N) szint, O(log O(log(N)) log(N)) keresé keresés

10

3

5

7

8

40

12

60

100

23


Adatrekord elé elérési mó módszerek B-fa bő bővítés algoritmusa Algoritmus elve: alulró alulról felfelé felfelé építkezik ha megtelt egy csomó csomópont,lé pont,létrehoz egy új szü szülőt Algoritmus lé lépései: levé levél megkeresé megkeresése, elem beszú beszúrása a rendezett listá listába Ha megtelt a levé levél, akkor középső pső elem meghatá meghatározá rozása középső pső elem a szü szülőbe testvé testvér generá generálás elemek szé szétosztá tosztása pointerek kiigazí kiigazítása GEIAL Kovács László

10

Adatrekord elé elérési mó módszerek B-fa bő bővítés algoritmusa A F U D H B C O G R


Adatrekord elé elérési mó módszerek Hash algoritmus alapjai Algoritmus elve: A rekord helye kö közvetlenü zvetlenül a kulcsé kulcsérté rtékbő kből hatá határozó rozódik meg A tá tároló roló terü terület blokkokra osztott, M darab blokk A hash függvé ggvény adja meg a blokk sorszá sorszámát, h(x)=x mod M A mó módszer jellemző jellemzői: Elvi keresé keresési kö költsé ltség: O(1) A túlcsordullá lcsordullás jelentő jelentős tö többletkö bbletköltsé ltséget adhat Nem stabil a kö költsé ltség, a statikus adatokra optimá optimális


11

Adatrekord elé elérési mó módszerek Hash algoritmus alapjai 11 21 7 13

h(x) = x mod 3

4 16 8 14 9

M=3 kapacitá kapacitás=2

10


Köszö szönöm a figyelmet!

12

Adatbá Adatbázis rendszerek I Szemantikai adatmodellek adatmodellek

MEME- GEIAL

Dr. Ková Kovács

Lá László szló

Modellezé Modellezés szerepe Problé Problémakö makör modellezé modellezése adatbá adatbázis struktú struktúrával Dolgozó Dolgozó kód

név

beosztá beosztás

Feladat kód

dolg

termé term ék

Termé Termék kód

ear

megnevezé megnevezés


1

Adatbá Adatbázis tervezé tervezés fő főbb lé lépései Tervezé Tervezési piramis követelmé vetelmény analí analízis

‘autó autók és tulajdonosok’ tulajdonosok’

szemantikai leí leírás

A

T

adatmodell leí leírás

normalizá normalizálás create table A create table T

parancs kó kódolá dolás

eredmé eredmények

műveletek GEIAL Kovács László

Adatbá Adatbázis adatmodell adatmodell Adatmodell : azon formalizmus, melynek segí segítsé tségével megadható megadhatók a vizsgá vizsgált problé problémakö makör adatelemeinek szerkezete, viselkedé viselkedése. Séma: az adatmodell felhaszná felhasználásával lé létrehozott adatszerkezet Elő Előfordulá fordulás: a sé séma érté rtékekkel kitö kitöltö ltött pé példá ldánya Adatmodell elemei

struktú struktúra leí leíró építő elemek definiá definiálása

művelet leí leíró elvé égezhető elv gezhető operá operáció ciók

integritá integritási felté feltételek statikus vagy aktiv szabá szabályok GEIAL Kovács László

2

Adatbá Adatbázis adatmodellek adatmodellek tí típusai A T

szemantikai adatmodell

emberkö emberközeli, lé lényeget emelik ki, pontatlan adatbá adatbázis modell gépkö pközeli, megadja a ré részleteket, teljes

ER

1960

1970 HDM

EER

1980 NDM

ODMG UML

1990 RDM

ORDM

2000

idő idő

MDDM


EgyedEgyed-kapcsolat modell egyszerű egyszerűsített szemantikai adatmodell, jele: ER (Chen (Chen) Chen) a fogalmak szintjé szintjén értelmezett csak strukturá strukturális és elemi integritá integritási ré részt tartalmaz egyszerű egyszerű grafikus jelö jelölés rendszert tartalmaz nem teljes adatmodell, de egyszerű egyszerűsége ré révén elterjedt tervezé tervezési segé segédeszkö deszköz a relá reláció ciós adatbá adatbázis tervezé tervezéséhez 3 fő fő komponense van egyed kapcsolat

A T

tulajdonsá tulajdonságok GEIAL Kovács László

3

Az ER modell elemei Egyed: egy objektum tí típus, egy a kü külvilá lvilág tö többi ré részé szétől egyé egyértelmű rtelműen megkü megkülönbö nböztetett dolog - önálló lló léttel bí bír - amikrő amikről az informá információ ciókat tá tárolni kivá kivánjuk Q: mire vonatkozó vonatkozó informá információ ciókat tá tároljunk

a rendszerben?

Típusai: - normá dolgozó normál egyed (ö (önmagá nmagában azonosí azonosítható tható): dolgoz ó - gyenge egyed (má (más egyedhez való való kapcsolatá kapcsolatán keresztü dolgozó felesé keresztül azonosí azonosított): dolgoz ó feles ége

egyed neve

egyed neve

normá normál egyed

gyenge egyed


Az ER modell elemei Kapcsolat: az egyedek kö között fenná fennálló lló ideiglenes vagy tartó tartós asszociá asszociáció ció - csak az első elsődleges kapcsolatokat adjuk meg Q: milyen kapcsolatban állnak az egyes egyedek egymá egymással? Kötelező telező jelleg szerinti tí típusok - opcioná opcionális: lé létezhet olyan egyedelő egyedelőfordulá fordulás, melyhez nem kapcsoló kapcsolódik egyedelő egyedelőfordulá fordulás a kapcsolatban - kötelező telező: minden egyedelő egyedelőfordulá forduláshoz kell kapcsoló kapcsolódnia egyedelő egyedelőfordulá fordulásnak a kapcsolatban

A opcioná opcionális

B

kötelező telező az A oldalon


4

Az ER modell elemei Szá Számossá mosság jelleg szerinti kapcsolat tí típusok: - 1:1 egy egyedelő egyedelőfordulá forduláshoz maximum egy egyed társul a kapcsolatban, mindké mindkét viszonylatban - 1:N (egy(egy-több) egy egyedelő egyedelőfordulá forduláshoz több egyed társulhat, de a má másik irá irányban csak egy kapcsoló kapcsolódó egyedelő egyedelőfordulá fordulás létezhet - N:M (tö (többbb-több) mindké mindkét irá irányban tö több kapcsoló kapcsolódó elő előfordulá fordulás lé létezhet orszá ország - főváros

tulaj - autó autó

A 1:1

szí színész - szí színdarab

B 1:N

N:M


Az ER modell elemei Tulajdonsá Tulajdonság: az egyedeket, kapcsolatokat jellemző jellemző mennyisé mennyiség - a letá letárolandó rolandó informá informácóelemek tartalmazza Q: milyen informá információ ciókat tá tároljunk az egyedekre vonatkozó vonatkozólag? Típusai: -

normá ember.szü lidő normál: egyé egyérté rtékű ember.sz ülid ő ember.TAJszá kulcs: azonosí azonosító szerepű szerepű ember.TAJsz ám összetett: tö lakcim( irsz, több tagbó tagból áll lakcim (irsz , varos) többé bbérté rtékű: tö több érté rtéke is lehet ember.hobby szá ember.é származtatott: érté rtéke kiszá kiszámítható tható ember. életkor

t normá normál

többé bbérté rtékű

t kulcs


t t

t

t t

összetett

szá származtatott

5

Az ER modell elemei mintapé mintapélda

Main Entities and Relationships startdate

1

manages 1

N 1 supervises

isee superv

supervisor

Employee

Department 1

N

controls

M workson

N

N

hours 24th November 1998

1

worksfor

Project

Sample Powerpoint Presentation

3


Az ER modell elemei mintapé mintapélda


6

Modellezé Modellezés az ERER-ben Követelmé vetelmények összegyü sszegyüjté jtése Egyedek meghatá meghatározá rozása Kapcsolatok meghatá meghatározá rozása Tulajdonsá Tulajdonságok meghatá meghatározá rozása Követelmé vetelmények ellenő ellenőrzé rzése


Mintapé Mintapélda: étterem konyha A pé példa feladat egy éttermi nyilvá nyilvántartá ntartás: ételek, hozzá hozzávaló valók, nyersanyagok, raktá raktárké rkészlet, rendelé rendelések, beszá beszállí llítók (egy nyersanyagot csak egy beszá beszállí llító hozhat)

ételek Rendelt adagok ételek hozzá hozzávaló valók raktá raktárké rkészlet … árak receptek

Beszá Beszállitó llitó név

Beszá Beszállí llító cim


7

Mintapé Mintapélda: étterem konyha

név

dátum rendelés

recept

mennyiség

étel

mennyiség

ear

benne

név

nyersanyag

szállít

szállító

összár cím azonosító

készlet

A kapcsolathoz is rendelhető rendelhető tulajdonsá tulajdonság GEIAL Kovács László

DIA rendszer ingyenes diagramm rajzoló rajzoló program (MS VisioVisio-hoz hasonló hasonló)

http://www.gnome.org/projects/dia http://www.gnome.org/projects/dia/ www.gnome.org/projects/dia/ GEIAL Kovács László

8

DIA rendszer Minta sé séma


Modellezé Modellezés az ERER-ben Az ER modellben ugyanazt a objektum má más-más mó módon is ábrá brázolható zolható aszerint, hogy a tervező tervező mit kivá kiván hangsú hangsúlyozni Egy objektum lehet egyed és tulajdonsá tulajdonság is - egyed : önálló lló lét, fontossá fontosság kiemelé kiemelése - tulajdonsá tulajdonság : kapcsolat má már egyedekhez ügyosztá gyosztály:

egyed egy vá vállalati struktú struktúrában, de tulajdonsá ág is lehet az olvasó tulajdons olvasó egyedné egyednél

ügyosztály

dolgozó tagja

projekt részt vesz

dolgozó

könyv kölcsönöz

név

ügyosztály

telefon GEIAL Kovács László

9

Modellezé Modellezés az ERER-ben Egy objektum megjelenhet kapcsolatké kapcsolatként és egyedké egyedként is házassá zasság:

egyed egy anyak anyakö önyvi hivatalban de kapcsolat is lehet az egyedek kö között

idõpont férj

adószám házasság

polgár

házasság polgár

feleség

Egy objektum megjelenhet kapcsolatké kapcsolatként és tulajdonsá tulajdonságké gként is tulajdonos:

kapcsolat egy adó adó hivatalban de tulajdonsá tulajdonság is lehet egy autó autótelepen sz.szám

rendszám

rendszám tulaj

autó

polgár

autó

tulaj GEIAL Kovács László

Modellezé Modellezés az ERER-ben A rugalmassá rugalmasság ellené ellenére, szá számos esetben nem lehet egzaktul megoldani az adatrendszer leí leírását Problé Problémát jelent a specializá specializáció ciók, általá ltalánosí nosítások, tartalmazá tartalmazási relá reláció ciók ábrá brázolá zolása, hiszen az ER csak az asszociá asszociáció ciót ismeri ember

jármű rmű

Asszociá Asszociácó Specializá Specializáció ció

ideiglenes szimmetrikus laza

autó autó

Tartalmazá Tartalmazás szoros kapcsolat nem szimmetrikus állandó llandósult

motor

állandó llandósult nem szimmetrikus fogalmi szintű szintű

Az egyfajta jelö jelölési mó mód elmossa a kü különbsé nbségeket


10

Az EER adatmodell Az ER modell kibő kibővítése a specializá specializáció ció és a tartalmazá tartalmazás Kapcsolat elemekkel. Jele: EER

ember

jármű rmű

Asszociá Asszociácó

IS_A Specializá Specializáció ció

autó autó Tartalmazá Tartalmazás HAS_A motor

A tartalmi kü különbsé nbség megmutatkozik a formá formában is GEIAL Kovács László

Az IFO szemantikai modell Újfajta fogalom orientá orientált szemantikai modell Funkcioná Funkcionális modellek csalá családjá djába tartozik A struktú struktúra orientá orientáltsá ltság helyett a hozzá hozzárendelé rendelésen alapszik Egyenrangú Egyenrangú objektumokat tartalmaz Gazdag struktú struktúra és kapcsolat ké készlet Elemei objektumok

elemi obejktum

absztrakt objektum

származtatott objektum

c asszociá asszociáció ció

A

B


11

Az IFO szemantikai modell Elemei struktú struktúrák aggregá aggregáció ció

csoportké csoportképzé pzés

specializá specializáció ció

A

B A

általá ltalánosí nosítás X

Y


Az IFO szemantikai modell Mintapé Mintapélda: utazá utazási iroda

utak

turistá turisták

nyelvismeret

idő időpont

idegenvezető idegenvezető

alkalmazottak lakcí lakcím


12

Az IFO szemantikai modell Mintapé Mintapélda: utazá utazási iroda

személy id

név Út,hsz

út

varos

alkalmazott

túrista kategória

nyelv szint

résztvevõ idegenvezetõ


13

Adatbá Adatbázis rendszerek I Hálós adatmodell

MEME- GEIAL

Dr. Ková Kovács

Lá László szló

Fizikai adatmodellek Az igé igények és lehető lehetőségek kompromisszuma

A modellek vá változnak a koncepció koncepciók és a technoló technológia fejlő fejlődésével Megkö Megközelí zelítőleg 10 évenké venként új adatmodell jelent meg

1

Hálós adatmodell Idő Időszak: 19601960-as évek kö közepe Fizikai keretfelté keretfeltételek: - diszklemezes tá tárolá rolás - osztott memó memória - multiprocesszoros OS Kialakulá Kialakulása: - 1961 : MIACS (Bachman (Bachman, Bachman, GE) - 1965 : Honeywell (IDS) - 1971 : CODASYL Elő Előnyei: - haté hatékonysá konyság - funkcionalitá funkcionalitás - API felü felület Hátrá trányai: - alacsony szintű szintű kezelé kezelés - procedurá procedurális kezelő kezelő felü felület - rugalmatlan struktú struktúra

Hálós adatmodell Adatmodell elemei

mező mező

rekord

rekord mező mező

rekordké rekordkészlet

PCR

PCR SET adatbá adatbázis SET adatbá adatbázis

2

Hálós adatmodell Adatkezelő Adatkezelő felü felület Adatbá Adatbázis tartalom kezelé kezelése gazdanyelvi programbó programból tö törté rténik - Adatkezelő Adatkezelő és leké lekérdező rdező nyelv (DML,DQL) - Gazdanyelvbe ágyazott, rekordorientá rekordorientált kezelő kezelő felü felület - DB rekordstruktú rekordstruktúrával azonos felé felépítésű kapcsolati vá változó ltozók

x

ids_x1

vauto

auto

y z

ids_x2

alkalmazá alkalmazás

osztott memó memória

DBMS DB

A há hálós modell elemei MEZŐ MEZŐ :

leí leíró tulajdonsá tulajdonság tá tárolá rolására szolgá szolgál

a legkisebb né névvel azonosí azonosított tá tárolá rolási egysé egység elnevezé elnevezés, tí típus jellemzi lehet összetett is - vektor: tö struktú úra többé bbérté rtékű strukt - csoport: összetett egy vagy tö többé bbérté rtékű struktú struktúra lehet normá normál vagy kulcs

könyv ISBN

szerző

C(11)

C(50) *

kiadás * dátum D

ár N

3

A há hálós modell elemei REKORD : egysé egységké gként tá tárolt mező mezők együ együttese Az ER egyed fogalmá fogalmához hasonló hasonló egysé egység szerkezet (sé (séma) jellemzi rögzí gzített mező mezősorrend REKORDKÉ REKORDKÉSZLET : az egy sé sémához tartozó tartozó rekord-elő rekord előfordulá fordulások együ együttese Rekordké Rekordkészlet könyv ISBN

IS12

szerző szerző

Nagy B

rekordrekord-elő előfordulá fordulás

rekordsé rekordséma

A há hálós modell elemei PCR kapcsolat : ké két rekord kö közötti 1:N kapcsolat egy szü szülő és egy gyerek rekord alkotja, nincs azonosí azonosító neve a szü szülő rekord minden elő előfordulá fordulásához tö több gyerek RekordRekord-elő előfordulá fordulás tartozhat egy gyerek elő előfordulá forduláshoz egy szü szülő rekordrekord-elő előfordulá fordulás tartozik

szülő rekord

ember

ország

gyerek rekord

autó

város

4

A há hálós modell elemei SET: kapcsolatelem, azonos rekordbó rekordból kiinduló kiinduló PCRPCR-ek együ együttese egyszintű egyszintű fa struktú struktúra A setset-nek van azonosí azonosító neve a gyö gyökér rekordtí rekordtípus lesz a set tulajdonosa a gyermek rekordtí rekordtípusok a set tagjai egy rekordtí rekordtípus tö több SETSET-ben is szerepelhet (CODASYL) Speciá Speciális SETSET-ek: ek: szingulá szinguláris SET,

rekurzí rekurzív SET könyvtá nyvtár

szülő

... gyerek1

könyv

gyerek2

setné setnév

olvasó olvasó

Könyvtá nyvtár

CODASYL szabá szabályok A há háló adatmodell formai leí leírása (1971) az adatbá adatbázis tetsző tetszőleges szá számú setset-típust tartalmazhat minden setnek van neve és egy tulajdonosa minden setben van egy vagy tö több tag rekordtí rekordtípus minden sethez tartozik egy tagrekord tá tárolá rolási sorrend bármely egyedtí egyedtípus megadható megadható egy vagy tö több set tagjaké tagjaként bármely egyedtí egyedtípus csak egy setben lehet tulajdonos egy tulajdonos rekord elő előfordulá fordulás lé létrehoz egy set elő előfordulá fordulást egy setben egy rekordtí rekordtípus bá bármely elő előfordulá fordulása maximum csak egyszer szerepelhet, s nem tö többszö bbször egy set elő előfordulá fordulásban a tagrekordnak tetsző tetszőleges sok elő előfordulá fordulása szerepelhet kiadó kiadó

könyvtá nyvtár

könyv SETSET-séma

olvasó olvasó

Honvé Honvéd K. Abigé Abigél

Tanoda

SETSET-példá ldány

5

A há hálós modell tervezé tervezése ER modell konvertá konvertálása há hálós modellre egyed →

rekordtí rekordtípus

elemi tulajdonsá tulajdonság

→

mező mező

kulcs tulajdonsá tulajdonság

→ kulcs mező mező

étel

nyersanyag

szá szállí llító nev cim név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító

összár cím készlet

azonosító

A há hálós modell tervezé tervezése ER modell konvertá konvertálása há hálós modellre többé bbérté rtékű tulajdonsá tulajdonság → vektor mező mező étel

összetett tulajdonsá tulajdonság → csoport mező mező

Rendelé Rendelés*

név

dátum menny. név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


készlet

6

A há hálós modell tervezé tervezése ER modell konvertá konvertálása há hálós modellre 1:1 kapcsolat → PCR

szá szállí llító

1:N kapcsolat → PCR nyersanyag

név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


azonosító

A há hálós modell tervezé tervezése ER modell konvertá konvertálása há hálós modellre N:M kapcsolat → kapcsoló kapcsoló rekord + 2*PCR

étel

étel

nyersanyag

név

nyersanyag

belevaló belevalók

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


készlet

7

A há hálós modell elő előfordulá fordulás-kezelé kezelése Fizikai

elő előfordulá fordulások logikai szinten

séma

ember id

tá tárolá rolási struktú struktúra

1

elő előfordulá fordulások fizikai szinten

Peti

név

autó autó rsz

3

Tomi

r4

Opel

tipus 2 r1

Laci Audi r3

Opel

1

Peti

3

Tomi

2

Laci

r4

Opel

r1

Audi

r3

Opel

A há hálós modell elő előfordulá fordulás-kezelé kezelése Fizikai

tá tárolá rolási struktú struktúra

A PCR, SET pointer lá láncokkal kerü kerül nyilvá nyilvántartá ntartásra

Kiado SET

Könyvtá nyvtár SET

kiadó kiadó könyvtá nyvtár könyv olvasó olvasó

logikai szint

ki1 kö1

kt1

kt2

ki2

kö2

ol1 kö3

kö4

ol2

ol3

kö5

fizikai szint

8

A há hálós modell mű műveletei Az adatkezelé adatkezelés navigá navigáció ciós mű műveleteken alapszik

ki1 kö1

kt1

kt2

ki2

kö2

ol1 kö3

kö4

ol2

ol3

kö5 adatbá adatbázis Adattartalom az osztott memó memóriá rián keresztü ül érhető kereszt rhető el

ki1

Minden rekordtí rekordtípushoz sajá saját memó memóriaria-változó ltozó (MV)

kö3 ol1 kt1 osztott memó memória

Az aktuá rekord-elő aktuális rekord előfordulá fordulás (AR) érhető rhető el az MVMV-ben A navigá navigáció ció a pointerlá pointerlánc menté mentén tö törté rténik, új AR lesz

A há hálós modell mű műveletei Az adatkezelé adatkezelés navigá navigáció ciós mű műveletei

Mozgá Mozgás a szingurá szingurális SET menté mentén

1

Peti

3

Tomi

2

Laci

r4

Opel

r1

Audi

r3

Opel

első első elő előfordulá fordulás: p1felté feltétel(rekord) következő vetkező elem: pnfelté feltétel(rekord) Mozgá Mozgás a szü szülőhöz szü szülő kivá kiválasztá lasztása: o (set,rekord) set,rekord) Mozgá Mozgás a tagokhoz első első elem: m1felté set,rekord) feltétel(set,rekord) Következő vetkező elem: mnfelté set,rekord) feltétel(set,rekord)

9

A há hálós modell mű műveletei Az adatkezelé adatkezelés navigá navigáció ciós mű műveletei Feladat: Az Opel nevű nevű autó autó tulajdonosainak nevei

P1tipus=‘ auto) tipus=‘Opel’ Opel’(auto) while (status==OK) { o(ember_set o(ember_set, set, auto) auto) print ‘nev= nev=‘ + v_ember pntipus= auto) tipus=‘Opel’ Opel’(auto) }

1

Peti

3

Tomi

2

Laci

r4

Opel

r1

Audi

r3

Opel

10

Adatbá Adatbázis rendszerek I Relá Reláció ciós adatmodell strukturá trukturális és integritá integritási ré rész

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

Relá Reláció ciós modell kialakulá kialakulása hardver technoló technológia robbaná robbanásszerű sszerű fejlő fejlődése MooreMoore-megfigyelé megfigyelése: se: évente duplá duplázódik a teljesí teljesítmé tmény

- ma már két év a periódus - még kb. 15 évig érvényes


1


Relá Reláció ciós modell kialakulá kialakulása Az új adatmodell kialakí kialakításának há háttere: PC, minigé minigépek elterjedé elterjedése igé igény a kis erő erőforrá forrás melletti megoldá megoldásokra igé igény a rugalmasabb megvaló megvalósításra igé igény az egyszerű egyszerűbb kezelé kezelésre számítógépek elterjedése 1970: 1978: 1981: 1985: 1989: 1996: 2004: 2007:

pár tízezer pár százezer ötmillió harmincötmillió százmillió háromszázmillió egy milliárd kétmilliárd

E.F. Codd (1932-2003) 1970: A Relational Model of Data for Large Shared Data Banks


2

Relá Reláció ciós modell kialakulá kialakulása Relá Reláció ciós modell idő időrendi fejlő fejlődése Codd javaslata(IBM)

1970

SystemSystem-R Sequel 1980

Oracle Ingres Informix DB2

RDB Sybase

1990

dBase

Postgres mySQL

SQLServer

2000

Oracle

SQL86

Clipper

VFP

DB2 SQLServer mySQL

E-SQL ODBC

ADO JDBC

SQL89 SQL92

SQL1999 SQL2003


Relá Reláció ciós adatmodell Az adatmodell elemei

rekord sé séma

mező

mező mező

rekord rekord-halmaz (reláció) adatbázis

adatbá adatbázis

egyszerű szerkezet GEIAL Kovács László

3

Relá Reláció ciós adatmodell Az adatmodell elemei rekord előfordulások logikailag

séma

Dolgozó kód név

beosztás

Auto rsz

tulaj

tipus

Rekord előfordulások fizikai szintje 1

Zoli

3

Feri

portás

2

Anna

1

Fiat

r2

Opel

r2

3

Opel

r1

Fiat

r3

1

Fiat r3

Fiat

1

Zoli

főnök

2

Anna

titkár

3

Feri

r1


A relá reláció ciós modell elemei mező mező :

a leí leíró tulajdonsá tulajdonság tá tárolá rolására szolgá szolgál

a legkisebb né névvel azonosí azonosított tá tárolá rolási egysé egység elnevezé elnevezés, tí típus jellemzi csak elemi érté rtékű lehet Lehet normá normál vagy kulcs mező mező REKORD : egysé egységké gként tá tárolt mező mezők együ együttese Az ER egyed fogalmá fogalmához hasonló hasonló egysé egység könyv szerző C(50)

kod N

ár N


4

A relá reláció ciós modell elemei RELÁ RELÁCIÓ CIÓ :

az azonos tí típusú pusú rekordrekord-elő előfordulá fordulások halmaza

elnevezé elnevezés, tí típus jellemzi nincs sorrendisé sorrendiség a rekordok kö között nincs rekordpozí rekordpozíció ció nem lehet ismé ismétlő tlődés

a rekordok kö között

rendszerint tá táblá blázattal ábrá brázoljuk (de ez má már tö több mint halmaz) Nyersanyag 1233

Alma

23

98.V.3

1253

Dió

783

01.I.8

4433

Mogyi

6233

Bab

97.II.11 79

75.X.2


A relá reláció ciós modell elemei A kapcsolat nyilvá nyilvántartá ntartás jellege nincs struktú struktúrába épített kapcsolat asszociatí asszociatív, érté rtékalapú kalapú kapcsolatteremté kapcsolatteremtés az kapcsolat ké két mező mező érté rtékegyező kegyezőségén alapszik: - kulcs mező mező - idegen kulcs séma

A

B

példá ldány

key

f_key

Rendelé Rendelés

Nyersanyag kod

nev

aru

ID

datum

1253

Dió

4433

783

01.I.8

4433

Mogyi

1253

81

01.03.2


5

A relá reláció ciós modell elemei A kapcsolat nyilvá nyilvántartá ntartás jellege Idegen kulcs: érté rtéke megegyezik egy (má (másik) tá tábla valamely rekordjá rekordjának kulcsé kulcsérté rtékével Kijelö Kijelöl érté rtéken keresztü keresztül egy rekord elő előfordulá fordulást Az idegen kulcs csak arra az oldalra kerü kerülhet, melyhez maximum egy elő előfordulá fordulás kapcsoló kapcsolódik a má másik oldalon Idegen kulcs mutathat a sajá saját tá táblá blájára is

Dolgozok

főnök

ID

nev

81

783

Gabor

12

81

Antal


A relá reláció ciós modell tervezé tervezése ER modell konvertá konvertálása relá reláció ciós modellre egyed →

rekordtí rekordtípus

elemi tulajdonsá tulajdonság

→

mező mező

kulcs tulajdonsá tulajdonság

→ kulcs mező mező

étel

nyersanyag

szá szállí llító nev cim név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


készlet


6

A relá reláció ciós modell tervezé tervezése ER modell konvertá konvertálása relá reláció ciós modellre szá szállí llító

1:1 kapcsolat → idegen kulcs

nev

cim

1:N kapcsolat → idegen kulcs nyersanyag azonosito ear szallito

név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


azonosító GEIAL Kovács László

A relá reláció ciós modell tervezé tervezése ER modell konvertá konvertálása relá reláció ciós modellre N:M kapcsolat → kapcsoló kapcsoló rekord + 2*idegen kulcs etel nev

nyersanyag azonosito ear szallito belevalo

recept

etel alapanyag

név

darab

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


készlet


7

A relá reláció ciós modell tervezé tervezése ER modell konvertá konvertálása relá reláció ciós modellre többé bbérté rtékű tulajdonsá tulajdonság → új relá reláció ció összetett tulajdonsá tulajdonság → tagmező tagmezők Rendelé Rendelés etel nev

etel dátum menny. recept

név

dátum rendelés

recept

mennyiség

étel

mennyiség

benne

ear

név

nyersanyag

szállít

szállító


azonosító GEIAL Kovács László

Relá Reláció ciós adatmodell megszorí megszorítások Hibá Hibás adatok haszná használhatatlanná lhatatlanná teszik az adatbá adatbázist Adatbá Adatbázis tartalom ellenő ellenőrzé rzésére integritá integritási felté feltételek, megszorí megszorítások hozható hozhatók lé létre Típusai: - Statikus : a felvehető felvehető adaté adatérté rtékek kö körét korlá korlátozza - Dinamikus : az elvé elvégezhető gezhető műveletek kö körét korlá korlátozza A nem megfelelő megfelelő műveletek, érté rtékek visszautasí visszautasításra kerü kerülnek

Rendelé Rendelés

Nyersanyag kod

nev

aru

ID

datum

1253

Dió

4433

783

01.I.8

4433

Mogyi

1221

81

01.03.2


8

Megszorí Megszorítási elemek Hatá Hatáskö skör szerinti csoportosí csoportosítás domain szintű szintű

CHECK felté feltétel

érté rtékellenő kellenőrzé rzés

mező mező szintű szintű

CHECK felté feltétel NOT NULL

érté rtékellenő kellenőrzé rzés nem maradhat üres

rekord szintű szintű

CHECK felté feltétel

érté rtékellenő kellenőrzé rzés

relá reláció ció szintű szintű

PRIMARY KEY UNIQUE

kulcs egyedisé egyediség

adatbá adatbázis szintű szintű

FOREIGN KEY ASSERTION felté feltétel

idegen kulcs összetett érté rték ellenő ellenőrzé rzés


A relá reláció ciós modell elemei A sé séma szö szöveges megadá megadása relá reláció ciónév (mező (mezőnév adattí adattípus integritá integritási felt.,..)

C(n) : szö szöveges N(n,m) : numerikus D : dá dátum Integer Float Double Logical DateTime Time Intervall CLOB BLOB …

PK: kulcs REF relá reláció ció : idegen kulcs NN : nem üres UN : egyedi C felté feltétel : érté rtékellenő kellenőrzé rzés

RESZLEG(kod C(4) PK, nev C(15) NN, letszam N(3) C letszam > 5) DOLGOZO(kod N(3) PK, nev C(25) NN, csoport N(4) REF reszleg)


9

A relá reláció ciós modell elemei A relá reláció ció formá formális felí felírása lista Relá Reláció ció értelmezé rtelmezései

halmaz Descartes szorzat függvé ggvények halmaza

U: attribú attribútumok halmaza, A ∈ U : attribú attribútum dom(A) dom(A) : az A attribú attribútum érté rtékeinek halmaza, domain R ⊆ U : relá reláció ció séma r(R) : relá reláció ció az R sé sémához t ∈ r(R) : egy rekord t ∈ dom(A dom(A1) × dom(A dom(A2) × … × dom( dom(An) : Ai ∈ R r(R) ⊆ dom(A dom(A1) × dom(A dom(A2) × … × dom( dom(An)


A relá reláció ciós modell elemei A relá reláció ció formá formális felí felírása lista Relá Reláció ció értelmezé rtelmezései

halmaz Descartes szorzat függvé ggvények halmaza

t : R ⇒ ∪ dom( dom(Ai ∈ R ) : ∀ Ai : t(A t(Ai) ∈ dom( dom(Ai) r(R) = {t {t : R ⇒ ∪ dom( dom(Ai ∈ R ) : ∀ Ai : t(A t(Ai) ∈ dom( dom(Ai)} r(R) = {r | r (R) ∧

bK(r(R))

∀b∈B: b(r) (r) = 1 }

= 1, ha K ⊆ R ∧ ∀t1,t2∈ t1,t2∈r(R): t1≠ t1≠t2 ⇒ t1(K) ≠t2(K) = 0 kü különben


10

A relá reláció ciós modell elemei Speciá Speciális elemek

gyenge egyed konverzió konverziója GYEREK(nev C(25), kor N(4), szulo REF DOLG, PK(nev, szulo)) kötelező telező kapcsolat konverzió konverziója BEFIZETES(kod N(25) PK, utas N(4) REF UTAS NN, datum D) kapcsolatok tulajdonsá tulajdonságainak konverzió konverziója SZEREPEL(darab REF DARAB,szinesz REF SZINESZ, szerep C(25)) N-es kapcsolat konverzió konverziója SZALLIT(aru REF ARU, vevo REF VEVO, szallito REF SZALLITO) GEIAL Kovács László

11

Adatbá Adatbázis rendszerek I Relá Reláció ciós adatmodell műveleti ré rész (relá (reláció ciós algebra)

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

Relá Reláció ciós adatmodell

adatmodell

strukturá strukturális ré rész

tárolá rolási struktú struktúra séma R(m1,m2,… R(m1,m2,…)

integritá integritási ré rész

érté rték korlá korlátozá tozás PK, REF, NN, C, UN

műveleti ré rész

igé igényelt adatelem kinyeré kinyerése relá reláció ciós algebra

művelet orientá orientált

relá reláció ciós kalkulus

tulajdonsá tulajdonság orientá orientált


1

Relá Reláció ciós algebra A relá reláció ciós algebra alaptulajdonsá alaptulajdonságai Nyersanyag kod

nev

ar

lejarat

1253

Dió

783

01.I.8

4433

Mogyi

6233

Bab

Zárt a relá reláció ciók halmazá halmazára Relá Reláció ciókat relá reláció cióba ké képez

97.II.11 79

75.X.2

A mű műveletek lá láncolható ncolhatók Imperatí Imperatív szemlé szemléletű letű

Eredmé Eredmény A relá reláció ciók egyes ré részeit emeli ki kod

nev

1253

Dió

4433

Mogyi

Egy vagy kétoperandusú toperandusú operá operátorai vannak Az SQL nyelv alapjá alapját ké képezi


Relá Reláció ciós algebra A relá reláció ciós algebrai mű műveletek áttekinté ttekintése szelekció szelekció

egy operandusú operandusú

{

projekció projekció kiterjeszté kiterjesztés aggregá aggregáció ció csoportké csoportképzé pzés join

két operandusú operandusú

{

metszet unió unió osztá osztás különbsé nbség


2

Relá Reláció ciós algebra A szelekció szelekció művelete Szelekció Szelekció: a megadott felté feltételnek eleget té tévő rekordok kerü kerülnek át az eredmé eredmény relá reláció cióba

jele:

σX ΘY(r) AUTÓ rsz tipus

σfelt(r)

= { t ∈ r | t(X) Θ t(Y)}

szín

r1

Fiat

zöld

rsz r2

tipus Opel

szín kék

r2 r6

Opel Mazda

kék piros

r4 r6

Skoda Mazda

kék piros

r4 r9

Skoda Suzuki

kék piros

szelekció (AUTO) OR tipus= =‘Mazda’ tipus Mazda feltétel: szín = 'piros' VAGY tipus =’ 'Mazda'

σszin= szin=‘kék’


Relá Reláció ciós algebra A projekció projekció művelete Projekció Projekció: a kijelö kijelölt mező mezők kerü kerülnek csak át az eredmé eredmény relá reláció cióba

jele:

Π X (r)

Πmlista(r)

= {t(X) ∈ r | t ∈ r}

AUTÓ rsz r1 r2 r6 r4 r9

tipus Fiat Opel Mazda Skoda Suzuki

szín zöld kék piros kék piros

tipus Fiat Opel Mazda projekció Πkijelölt (AUTO) tipus, , rsz= tipus, tipusmezõk rsz

szín zöld kék piros

Skoda kék Suzuki piros


3

Relá Reláció ciós algebra A mű műveletek kombiná kombinálása Az eredmény reláció bemenete lehet egy újabb műveletnek

AUTÓ rsz tipus r1 Fiat r2 Opel r6 r4 r9

szín zöld kék

Mazda Skoda Suzuki

Π

rsz

piros kék piros

szelekció feltétel: szín='piros' VAGY szín='kék'

rsz r2 r6 r4 r9

projekció kijelölt mezõk = rsz

(σszin= szin=‘kék’

OR szin= szin=‘piros’ piros’

(AUTO) )


Relá Reláció ciós algebra A join művelet Alap Join: Join: ké két relá reláció ció rekordjainak pá párosai

jele: r1 >< r2 DescartesDescartes-szorzat AUTÓ rsz tulaj r1 3 r4 1 r6 4

r1 >< r2 = {(t,s) | t ∈ r1 , s ∈ r2}

összekapcsolás

EMBER id név 1 Laci 2 Ágota 3 János 4 GEIAL Kovács László

Zoltán

AUTO >< EMBER

rsz r1 r4 r6 r1 r4

tulaj 3 1 4 3 1

id 1 1 1 2 2

név Laci Laci Laci Ágota Ágota

r6 r1 r4 r6 r1 r4

4 3 1 4 3 1

2 3 3 3 4 4

Ágota János János János Zoltán Zoltán

r6

4

4

Zoltán

4

Relá Reláció ciós algebra A join művelet Szelekció Szelekciós Join: Join: a ké két relá reláció ció rekordpá rekordpárosaibó rosaiból a felté feltételnek eleget té tévő párosokat adja eredmé eredményké nyként

jele: r1 ><

felt

r2 = σfelt(r1 >< r2 )

Natural Join: join, , mely az azonos Join: olyan szelekció szelekciós join elnevezé elnevezésű mező mezők érté rtékegyező kegyezőségén alapszik

jele: r1 ><= r2 r1 ><

=

r2 = Π

(R1 U R2)

(σr1.X

= r2.X

(r1 >< r2 ))


Relá Reláció ciós algebra A join művelet Outer Join: Join: olyan szelekció szelekciós join, join, melyben az illeszkedő illeszkedő pár nélkü lküli rekodok is bekerü bekerülnek az eredmé eredmény halmazba (üres étékekkel kiegé kiegészí szítve)

jele: r1 +>
jele: r1 ><+felt r2 jele: r1 +><+felt r2

Az üres érté rték szimbó szimbóluma: NULL GEIAL Kovács László

5

Relá Reláció ciós algebra A join művelet Righr outer join végrehajtá grehajtása

Könyv kod

cim

32

c1

1253

46

c2

4463

nev

1253

K1

4433

K2

4463

K3

><+ ><+

kod

cim

kiadó

ID

nev

32

c1

1253

1253

K1

46

c2

4463

4463

K3

NULL

NULL

NULL

4433

K2

Kiadó kiado=ID kiado=ID Kiadó

kiadó

Kiadó Kiadó ID

Könyv


Relá Reláció ciós algebra A join művelet Semi Join: Join: olyan szelekció szelekciós join, join, melyben az illeszkedő illeszkedő párokbó rokból csak a megadott oldal mező mezői szerepelnek

jele: r1 >
R2(r1

>
tipusai: tipusai: - left semi join - right semi join

Az illeszkedő illeszkedő párral rendelkező rendelkező rekordokat adja vissza


6

Relá Reláció ciós algebra A kiterjesz kiterjesztés művelet Kiterjeszté Kiterjesztés: a relá reláció ció kibő kibővítése szá származtatott mező mezőkkel

jele:

εmlista(r)

Az mező mező érté rtékének a tö többi mező mező érté rtékéből kell meghatá meghatározó rozódnia

AUTÓ rsz

tipus

ár

kiterjesztés

rsz

tipus

ár

r1

Fiat

1200

új mezõ: ár/100

r1

Fiat

1200

12

r2

Opel

930

r2

Opel

930

9.3

r6

Mazda

870

r6

Mazda

870

8.7

r4

Skoda

1500

r4

Skoda

1500

15

r9

Suzuki

1400

r9

Suzuki

1400

14

εar/100 ar/100(AUTO)

ár/100


Relá Reláció ciós algebra A halmaz mű műveletek Unió Unió: azonos sé sémájú relá reláció ciók rekordhalmazá rekordhalmazának egyesí egyesítése

jele: r1 ∪ r2 Metszet: azonos sé sémájú relá reláció ciók rekordhalmazá rekordhalmazának metszete

jele: r1 ∩ r2 Különbsé nbség: azonos sé sémájú relá reláció ciók rekordhalmazá rekordhalmazának kü különbsé nbsége

jele: r1 \ r2 FIÚK id

név 1

Laci

2 3

Tibor János

4

Zoltán

VERSENYZÕK id 21

3 GEIAL Kovács László

id

Tibor Zoltán

név 1 3

név

Erika 2 4

különbség Laci János

FIUK \ VERSENYZOK

Éva

7

Relá Reláció ciós algebra Az osztá osztás mű művelete Osztá Osztás: a Descartes szorzat inverze, azon legnagyobb relá reláció ció, melynek Descartes szorzata r2r2-vel benne van r1r1-ben

jele: r1 ÷ r2 HOBBY hobby

név

foci

Laci

tenisz

Tibor

foci

János

futás

Zoltán

futás

Laci

foci

Zoltán

tenisz

Gábor

HOBBY ÷ SPORTAG

osztás név Laci Zoltán

SPORTÁG hobby foci futás


Relá Reláció ciós algebra Az osztá osztás mű művelete Osztá Osztás levezeté levezetése az alapmű alapműveletekbő veletekből: r1 ÷ r2

= {t

| t ∈ ΠR1\ R1\R2(r1) ,

t >< r2

⊂ r1}

a ‘minden’ minden’ kvantorhoz kapcsoló kapcsolódik a jelenté jelentése Az eredmé eredmény levezeté levezetése: Jelö Jelöltek: ΠR1\ R1\R2 (r1) A jó jó jelö jelöltek szorzata benne van r1r1-ben: ΠR1\ R1\R2(r1) >< r2 A rossz jelö jelöltek: ΠR1\ (ΠR1\ R1\R2( (Π R1\R2(r1) >< r2)\r1 r1 ÷ r2 = ΠR1\ (ΠR1\ R1\R2 (r1) \ ΠR1\ R1\R2( (Π R1\R2(r1) >< r2)\r1) GEIAL Kovács László

8

Relá Reláció ciós algebra Az aggregá aggregáció ció művelete Aggregá Aggregáció ció: a relá reláció cióból összesí sszesítő rekordot állí llít elő elő

jele: Γaggregációs-lista (r) aggregá aggregáció ciós függvé ggvények: SUM(mez.kif SUM(mez.kif) mez.kif) COUNT(mez.kif COUNT(mez.kif) mez.kif) MIN(mez.kif MIN(mez.kif) mez.kif) AUTÓ rsz tipus r1 Fiat


ár 1200

r2 r6 r4

Opel Mazda Skoda

930 870 1500

r9 r7 r11 r8

Suzuki Opel Fiat Opel

1400 2570 850 1000

AVG(mez.kif AVG(mez.kif) mez.kif) MAX(mez.kif MAX(mez.kif) mez.kif)

csoportképzés kifejezés: Count() 8 kiírandó: COUNT()

count(*) (AUTO) Γcount(*)

Relá Reláció ciós algebra A csoportké csoportképzé pzéses aggregá aggregáció ció művelete Csoportké Csoportképzé pzés és aggregá aggregáció ció: a relá reláció ció rekodjait csoportokba rendezi, s minden csoportra egy összesí sszesítő rekordot állí llít elő elő

jele: Γcsoport képzési kifejezésaggregációs-lista (r) Azon rekordok kerü kerülnek egy csoportba, melyekre a csoport képzé pzési kifejezé kifejezés azonos érté rtékű AUTÓ rsz tipus r1 Fiat

ár 1200

r2 r6 r4 r9

Opel Mazda Skoda Suzuki

930 870 1500 1400

r7 r11 r8

Opel Fiat Opel

2570 850 1000


csoportképzés kifejezés: tipus

kiírandó: tipus, AVG(ár)

tipus, Γtipostipus,

count(*) count(*)

tipus

AVG(ár)

Fiat Opel Mazda Skoda

1025 1500 870 1500

Suzuki

1400

(AUTO)

9

Relá Reláció ciós algebra Speciá Speciális szelekció szelekciós operá operátorok Halmaz és logikai operá operátorok is élnek: ∈ : tartalmaz ∉ : nem tartalmaz ∀ : minden kvantor ∃ : lé létezik kvantor AlAl-szelekció szelekciók: A szelekció szelekciós felté feltételben is szerepelhet relá reláció ciós algebrai kifejezé kifejezés

Γcount(*)(σ fiz> (Γavg(fiz)(DOLG)) (DOLG)) GEIAL Kovács László

Relá Reláció ciós algebra Kifejezé Kifejezés felé felépítésének lé lépései 1. Mely relá reláció ciókbó kból emelhető emelhetők ki az igé igényel informá információ ciók? 2. Milyen az illeszté illesztési felté feltétel

a join eseté esetén?

3. Milyen szű szűkítések vannak az alaprelá alapreláció ciókon? 4. Szü Szüksé kség vanvan-e alal-szelekció szelekciókra? kra? 5. Szü Szüksé kség vanvan-e csoportké csoportképzé pzésre és ha igen, mi alapjá alapján? 6. Milyen aggregá aggregáció ciós kifejezé kifejezést kell szerepeltetni? 7. Milyen mező mezők szerepeljenek az eredmé eredmény relá reláció cióban? GEIAL Kovács László

10

Relá Reláció ciós algebra Mintapé Mintapélda Séma: BOROK(kod BOROK(kod, kod, nev, nev, gyarto, gyarto, fajta, ar) ar) VEVO(vkod VEVO(vkod, vkod, nev, nev, cim) cim) RENDELES(vevo RENDELES(vevo, vevo, bor, mennyiseg, mennyiseg,datum) datum) 1. Az 50005000-nél drá drágább borok nevei

Πmev(σar>5000 (BOROK)) 2. A vevő vevők neve és rendelé rendelésének összmennyisé sszmennyisége

Γnevnev, sum(mennyiseg)(RENDELES >< rendeles.vevo = vevo.vkod VEVO) GEIAL Kovács László

Relá Reláció ciós algebra Mintapé Mintapélda

1. azon vevő vevők, akik rendeltek Bikavé Bikavért

Πnev(σborok.kod = rendeles.bor AND rendeles.vevo = vevo.vkod AND borok.fajta = ‘Bikaver’ (BOROK >< RENDELES >< VEVO)) 2. Melyik gyá gyártó rtó termel 55-nél tö több vö vörös bort

Πgyarto(σcount(*)>5 (Γgyartogyarto,count(*)(σ fajta=‘voros’(BOROK)))) 3. Az átlagná tlagnál drá drágább borok darabszá darabszáma

Γcount(*)(σ ar> (Γavg(ar)(BOROK))(BOROK)) GEIAL Kovács László

11

Adatbá Adatbázis rendszerek I Az SQL nyelv alapjai

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

SQL nyelv kialkulá kialkulása Procedurá Procedurális nyelv: kö költsé ltséges fejleszteni, mó módosí dosítani 1974 : System/R System/R (IBM) Új parancsnyelv kidolgozá kidolgozása meglé meglévő rekord orientá orientált FIND KIADO FIRST FOR C=„H” DO WHILE STATUS_OK IF L> 100 SET l = l –10 STORE KIADO ENDIF FIND KIADO NEXT ENDDDO

?

igé igények: relá reláció ciós algebrá algebrán alapuljon egyszerű egyszerűség haté hatékony implementá implementáció ció GEIAL Kovács László

1

SQL nyelv kialakulá kialakulása 1975: SEQUEL Structured English Query Language magas szintű szintű műveletek egymá egymásba ágyazható gyazható műveletek termé természetes nyelvhez kö közelí zelít imperatí imperatív nyelv

FIND KIADO FIRST FOR C=„H” DO WHILE STATUS_OK IF L> 100 SET l = l –10 STORE KIADO ENDIF FIND KIADO NEXT ENDDDO

nem programozá programozási nyelv UPDATE KIADO SET L = L-10 WHERE C = „H”

bővíthető thető nyelv relá reláció ciós modellen alapszik

A KIADO táblában legyen L = L-10, ahol C = „H”

szabvá szabvány GEIAL Kovács László

SQL nyelv kialakulá kialakulása Többbb-résztvevő sztvevős folyamat a fejlő fejlődése

termé termékek

szabvá szabványok 1986: 1987: 1989: 1992: 1999: 2003:

ANSI SQL ISO SQL ANSI89 SQL92 SQL1999 SQL2003

SQL

igé igények Relá Reláció ciós, aktí aktív elemek, OO, OLAP, ..

1975: 1977: 1979: 1979: 1983:

SEQUEL SEQUEL/2 (SQL) system/38 system/38 SQL Oracle SQL DB2 SQL Ingres Oracle IBM DB2 Sybase Informix MS SQLServer PostgresSQL mySQL Access


2

SQL nyelv elemei adatdefiniá adatdefiniáló (DDL) struktú struktúra lé létrehozá trehozás (CREATE) struktú struktúra mó módosí dosítás (ALTER) struktú struktúra megszü megszünteté ntetés (DROP) adatkezelő adatkezelő (DML) adat felvitel (INSERT) adat mó módosí dosítás (UPDATE) adat tö törlé rlés (DELETE) leké lekérdező rdező (DQL) adat kiolvasá kiolvasása

(SELECT)

vezé vezérlő rlő (DCL) védelem, tranzakció tranzakciók GEIAL Kovács László

DDL utasí utasítások DDL: A tá táblá blák, objektumok struktú struktúrájának kezelé kezelésére szolgá szolgál Igényelt információk: Milyen szerkezete legyen? Minek nevezzü nevezzük el? Milyen megkö megkötéseket tegyü tegyünk? Hozz létre egy KÖNYV nevű táblát, melynek van egy cím nevű 20 hosszú szöveges, és egy ár nevű numerikus mezője!

CREATE TABLE KÖ KÖNYV (cí (cím CHAR(20), ár INTEGER); létrehozás: tábla megszüntetés: szerkezet módosítás:

CREATE TABLE név paraméterek; DROP TABLE név; ALTER TABLE név paraméterek;


3

DDL utasí utasítások Objektum lé létrehozá trehozása: CREATE objektumtí objektumtípus azonosí azonosító paramé paraméterek; Tábla lé létrehozá trehozása: CREATE TABLE tné tnév (mnev1 tipus1 intfelt1, …,intfelt) intfelt) … ; PRIMARY KEY NOT NULL UNIQUE CHECK felt REFERENCING tabla

CHAR(n) NUMBER(n,m) DATE

PRIMARY KEY(m1,m2) FOREIGN KEY(m1,m2) REFERENCING tabla CHECK felt CREATE TABLE AUTO (RSZ CHAR(6) PRIMARY KEY, AR NUMBER(9) NOT NULL, IDO DATE); GEIAL Kovács László

DDL utasí utasítások Objektum megszü megszünteté ntetése: DROP objektumtí objektumtípus azonosí azonosító; Tábla megszü megszünteté ntetése: DROP TABLE tné tnév; Objektum szerkezet mó módosí dosítás: ALTER objektumtí objektumtípus azonosí azonosító paramé paraméterek; Tábla szerkezet mó módosí dosítás: ALTER TABLE tné tnév ADD | MODIFY (mnev (mnev tip intfelt) intfelt); DROP TABLE AUTO; ALTER TABLE AUTO ADD (TULAJ REFERENCING EMBER);

A mű műveletek nem tranzakció tranzakció hatá hatáskö skörben futnak le! GEIAL Kovács László

4

DDL utasí utasítások Tábla átnevezé tnevezése: ALTER TABLE nev1 RENAME TO nev2; Mező Mező hozzá hozzáadá adása: ALTER TABLE nev1 ADD (mnev (mnev szerkezet); Mező Mező módosí dosítása: ALTER TABLE nev1 MODIFY (mnev (mnev szerkezet); Mező Mező megszü megszünteté ntetése: ALTER TABLE nev1 DROP COLUMN mnev; mnev; Mező Mező átnevezé tnevezése: ALTER TABLE nev1 RENAME COLUMN mnev1 TO mnev2; GEIAL Kovács László

DML utasí utasítások DDL: A tá táblá blák, objektumok tartalm tartalmának kezelé kezelésére szolgá szolgál Igényelt információk: Mely objektumot mó módosí dosítjuk? Mely adatelem mó módosul? Mi lesz az új érté rték? Hozz létre egy új KÖNYV rekordot, melynek van a címe Lepkefogó és az ára 2340 Ft.

INSERT INTO KÖ KÖNYV (‘ (‘Lepkefogó’ Lepkefogó’, ó’, 2340); rekord felvitele: rekord tö törlé rlése: rekordé rekordérté rték mó módosí dosítása:

INSERT DELETE UPDATE

INTO né név (é (érté rtékek); FROM né név WHERE felté feltétel; né név SET érté rtékadá kadás WHERE felté feltétel ;


5

DML utasí utasítások Rekord felvitele: INSERT INTO tabla VALUES (ert1, ert2, …,ertn); ertn); INSERT INTO tabla (mezo1,..) VALUES (ert1,..); INSERT INTO tabla VALUES (mezo (mezo= mezo=ertek, ertek,…); INSERT INTO tabla SELECT …; Fontos a mező mezősorrend Minden szü szüksé kséges mező mező kapjon érté rtéket INSERT INTO AUTO VALUES (‘bju564’,234,’FIAT’); INSERT INTO AUTO (rsz, tipus) VALUES (‘bju564’,’FIAT’); INSERT INTO AUTO VALUES (RSZ=‘bju564’,...); A mű műveletek tranzakció tranzakció hatá hatáskö skörben futnak le! GEIAL Kovács László

DML utasí utasítások Rekord tö törlé rlése: DELETE FROM tabla WHERE felté feltétel; A felté feltételnek eleget tevő tevő rekordok tö törlő rlődnek Ha elmarad a WHERE tag, minden rekord tö törlő rlődik A felté feltétel ré rész ré részletesen a SELECTSELECT-nél jön

DELETE FROM AUTO; DELETE FROM AUTO WHERE AR<1200000; DELETE FROM AUTO WHERE (SELECT … ) ; A mű műveletek tranzakció tranzakció hatá hatáskö skörben futnak le! GEIAL Kovács László

6

DML utasí utasítások Rekord mó módosí dosítása: UPDATE tabla SET mezo= mezo= érté rték,... WHERE felté feltétel; A felté feltételnek eleget tevő tevő rekordok mó módosulnak Ha elmarad a WHERE tag, minden rekord mó módosul

UPDATE DOLGOZO SET KOR = KOR + 1, FIZ = 10000; UPDATE AUTO SET AR

= AR * 1.2 WHERE AR<1200000;

A mű műveletek tranzakció tranzakció hatá hatáskö skörben futnak le! GEIAL Kovács László

DML utasí utasítások Speciá Speciális érté rtékek kezelé kezelése Üres, nem ismert érté rték jelö jelölése: NULL A DEFAULT érté rték kerü kerül letá letárolá rolásra INSERT INTO AUTO VALUES(12,’ALMA’, NULL,..);

A ” jel: azonosí azonosítónév hatá határolá rolása A ‘ jel: szö szövegkonstans hatá határolá rolása INSERT INTO AUTO(”SZUL. HELY”,..) VALUES(‘OZD’,..);

Dátumok felvitele konverzió konverziós fü függvé ggvénnyel tö törté rténik INSERT INTO AUTO(DATUM,..) VALUES( TO_DATE(‘2002.02.12’,’YYYY.MM.DD’),…); GEIAL Kovács László

7

DQL utasí utasítások DQL: A tá táblá blák, objektumok tartalm tartalmának leké lekérdezé rdezése Igényelt információk: Mely táblákból vesszük az adatokat? Mely rekordokat vegyük figyelembe? Milyen számításokat végezzünk?

SELECT cim FROM

kö könyvek WHERE ár > 3300;

SELECT * FROM kö könyvek INNER JOIN kiadok ON kod = pub; pub;

rekordok lekérdezése:

SELECT mezőlista FROM táblalista


DQL utasí utasítások A leké lekérdezé rdezés alapjai SELECT m_kif1,… m_kif1,… FROM t_kif t_kif WHERE felté feltétel GROUP BY cskif HAVING felté feltétel UNION | INTERSECT | MINUS SELECT … ORDER BY rkif; rkif;

projekció, kiterjesztés aggregáció join szelekció csoportképzés csoport szelekció halmazműveletek rendezés

A relá reláció ciós algebrá algebrán alapszik Tartalmaz nem relá reláció ciós elemeket is (pl. ORDER BY) Állandó llandóan bő bővül, az SQL kö központi eleme GEIAL Kovács László

8

DQL utasí utasítások A projekció projekció megadá megadása SELECT mlista FROM tá tábla;

Πmlista(tá (tábla)

Az mlista helyé helyén * áll, ha minden mező mező kell AUTÓ rsz r1 r2 r6 r4 r9

tipus Fiat Opel Mazda Skoda Suzuki

tipus Fiat Opel Mazda

szín zöld kék piros kék piros

projekció kijelölt mezõk = tipus, rsz

szín zöld kék piros

Skoda kék Suzuki piros

SELECT RSZ, TIPUS FROM AUTO; SELECT *

FROM AUTO;


DQL utasí utasítások A szelekció szelekció megadá megadása σfelté (tábla) feltétel(tá

SELECT mlsita FROM tá tábla WHERE felté feltétel;

SELECT * FROM AUTO WHERE SZIN = ‘PIROS’ OR TIPUS = ‘MAZDA’;


9

DQL utasí utasítások A kiterjeszté kiterjesztés megadá megadása εkif

(tábla) nev(tá

SELECT kif név,... FROM tá tábla;

Az elő előállí llító kifejezé kifejezés

A mező mezőkhö khöz új nevet is rendelhetü rendelhetünk

SELECT SAL FIZETES, AGE KOR FROM EMP; SELECT AR/100 UJAR FROM AUTO;


SQL felü felületek Mily mó módon adható adhatók át a DBMSDBMS-nek az SQL utasí utasítások? Hogyan érhető rhetők el az eredmé eredménytá nytábla adatai? parancsértelmező

DB segédprogram DBMS

ESQL

CLI / ODBC

alkalmazói program

JDBC / ADO 4GL GEIAL Kovács László

10

Oracle SQLPlus Gyakorlati eszkö eszköz: Oracle RDBMS Felü interaktí ív SQL: sqlplus (Sun gépek, 105Felület: interakt 105-ös lab.) lab.) WEBWEB-es SQL felü felület: ISqlplus regisztá regisztáció ció: https:// https://www ://wwwwww-db.iit.unidb.iit.uni-miskolc.hu/ miskolc.hu/dbreg/ dbreg/

sqlplus: sqlplus: sun> sun> export ORACLE_HOME=/opt/OraHome1 sun> sun> export PATH=${PATH}:${ORACLE_HOME}/bin sun> sun> sqlplus név@info v@info isqlplus: isqlplus: http:// http://arrakis.iit.uni ://arrakis.iit.uniarrakis.iit.uni-miskolc.hu:5560/ miskolc.hu:5560/isqlplus :5560/isqlplus


11

Adatbá Adatbázis rendszerek I Az SQL nyelv tová további elemei

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

SQL nyelv elemei adatdefiniá adatdefiniáló (DDL) struktú struktúra lé létrehozá trehozás (CREATE)

√

struktú struktúra mó módosí dosítás (ALTER)

√

struktú struktúra megszü megszünteté ntetés (DROP)

√

adatkezelő adatkezelő (DML) adat felvitel (INSERT)

√

adat mó módosí dosítás (UPDATE)

√ √

adat tö törlé rlés (DELETE) leké lekérdező rdező (DQL) adat kiolvasá kiolvasása

(SELECT)

vezé vezérlő rlő (DCL) védelem, tranzakció tranzakciók GEIAL Kovács László

1

DQL utasí utasítások A leké lekérdezé rdezés alapjai

projekció, kiterjesztés

SELECT m_kif1,… m_kif1,… FROM t_kif t_kif WHERE felté feltétel GROUP BY cskif HAVING felté feltétel UNION | INTERSECT | MINUS SELECT … ORDER BY rkif; rkif;

√

aggregáció join szelekció

√

csoportképzés csoport szelekció halmazműveletek rendezés

A relá reláció ciós algebrá algebrán alapszik Tartalmaz nem relá reláció ciós elemeket is (pl. ORDER BY) Állandó llandóan bő bővül, az SQL kö központi eleme GEIAL Kovács László

SQL SELECT funkció funkciói Táblá blák összekapcsolá sszekapcsolása, DescartesDescartes-szorzat r1 >< r2

SELECT mlista FROM r1, r2 ... ;

Kettő Kettőnél tö több tá tábla is összekapcsolható sszekapcsolható Ügyelni kell a mé méretproblé retproblémákra AUTÓ rsz tulaj 3 r1 1 r4 r6 4

EMBER id név 1 Laci 2 Ágota 3 János 4 Zoltán

összekapcsolás

rsz r1 r4 r6 r1 r4 r6 r1 r4 r6 r1 r4 r6

tulaj id 3 1 1 1 4 1 3 2 1 2 4 2 3 3 1 3 4 3 3 4 1 4 4 4

név Laci Laci Laci Ágota Ágota Ágota János János János Zoltán Zoltán Zoltán


2

SQL SELECT funkció funkciói Táblá blák összekapcsolá sszekapcsolása, szelekció szelekciós join r1 >
SELECT … FROM r1 INNER JOIN r2 ON felté feltétel …;

Ha tö több tá táblá blában is azonos elnevezé elnevezésű mező mező van, akkor a kibő kibővitett mező mezőnevet haszná használjuk: tábla.mező bla.mező A tá táblané blanévhez alias nevet rendelhetü rendelhetünk:

tá tábla alias

Tábla szerepkö szerepkört is alias né névvel jelö jelöljü ljük Visszavezethető Visszavezethető Descartes szorzatra és szelekció szelekcióra DOLGOZO (kod (kod PK, nev, nev, beosztas, beosztas, fonok REF DOLGOZO, fizetes) fizetes) SELECT f.nev, f.nev, d.nev FROM dolgozo f INNER JOIN dolgozo d ON f.kod = d.fonok WHERE d.beosztas = ‘raktá raktáros’ ros’ GEIAL Kovács László

SQL SELECT funkció funkciói Az outerouter-join megadá megadása r1 +>
OUTER JOIN

SELECT * FROM AUTO A RIGHT OUTER JOIN EMBER E ON A.TULAJ = E.IGSZ WHERE A.AR > 6000000; T1

T1 +>
T2

= T2.A

T2

A

B

A

C

A

B

A

C

1

C

3

L

1

C

1

T

2

G

1

T

2

G

3

U

5

P

3

U

3

L


extra rekord

3

SQL SELECT funkció funkciói Az aggregá aggregáció ció megadá megadása aggregáció ciós-lista (r) Γaggregá

SELECT aggregá aggregáció ciós-lista FROM r;

SUM(mez.kif SUM(mez.kif) mez.kif) COUNT(mez.kif mez.kif) COUNT( mez.kif) MIN(mez.kif MIN(mez.kif) mez.kif)

AVG(mez.kif AVG(mez.kif) mez.kif) MAX(mez.kif mez.kif) MAX( mez.kif)

Aggregá Aggregált és ré részletező szletező kifejezé kifejezések nem keveredhetnek AUTÓ csoportképzés

rsz

tipus

ár

r1

Fiat

1200

r2

Opel

930

r6

Mazda

870

r4

Skoda

1500

r9

Suzuki

1400

r7

Opel

2570

r11

Fiat

850

r8

Opel

1000

kifejezés: Count() 8 kiírandó: COUNT()

SELECT COUNT(*) FROM AUTO;


SQL SELECT funkció funkciói Az aggregá aggregáció ció és csoportké csoportképzé pzés megadá megadása aggregá aggregáció ciós-lista (r) Γcsopké csopképkif

SELECT aggregá aggregáció ciós-lista FROM r GROUP BY csopké csopképkif; pkif; A csopké csopképkif szerepelhet az eredmé eredmény mező mezőlistá listájában AUTÓ rsz

tipus

ár

csoportképzés

r1 r2

Fiat Opel

1200 930

kifejezés: tipus

r6

Mazda

r4 r9

Skoda Suzuki

tipus Fiat

AVG(ár) 1025

870

Opel

1500

1500 1400

Mazda Skoda

870 1500

Suzuki

1400

r7

Opel

2570

r11

Fiat

850

r8

Opel

1000


kiírandó: tipus, AVG(ár)

SELECT TIPUS, AVG(AR) FROM AUTO GROUP BY TIPUS;

4

SQL SELECT funkció funkciói Szű Szűrés az elké elkészü szült csoportokbó csoportokból aggagg-lista (r)) σfelté feltétel(Γcskif

SELECT aggagg-lista FROM tá tábla GROUP BY cskif HAVING felté feltétel; SELECT TIPUS FROM AUTO GROUP BY TIPUS HAVING AVG(AR) > 10000000; A csoportké csoportképzé pzés elő előtti és utá utáni szelekció szelekció együ együttesen is szerepelhet SELECT TIPUS FROM AUTO WHERE SZIN= SZIN=” ”PIROS” PIROS” GROUP BY TIPUS HAVING AVG(AR) > 10000000;


WHERE

HAVING

SQL SELECT funkció funkciói Az eredmé eredmény rekordok rendezé rendezése SELECT mlista FROM tá tábla ORDER BY mezo1 mod1, mezo2 ...; ASC vagy DESC Nem ré része a relá reláció ciós algebrá algebrának A má második mező mező szerinti szempont csak az első első egyező egyezősége eseté én lé eset lényeges SELECT RSZ, TIPUS, AR FROM AUTO ORDER BY AR DESC, TIPUS; Lehet sorszá sorszámmal is hivatkozni a mező mezőkre SELECT RSZ, TIPUS, AR FROM AUTO ORDER BY 3 DESC, 2; GEIAL Kovács László

5

SQL SELECT funkció funkciói Táblá blák halmaz mű műveletei r1 ∪ r2 , r1 ∩ r2 , r1 \ r2 SELECT SELECT SELECT SELECT

… … … …

FROM FROM FROM FROM

r1 r1 r1 r1

… … … …

UNION SELECT … FROM r2 …; UNION ALL SELECT … FROM r2 …; INTERSECT SELECT … FROM r2 …; MINUS SELECT … FROM r2 …;

Csak kompatí kompatíbilis szerkezetű szerkezetű táblá blák vonható vonhatók össze A rendezé rendezés csak a legvé legvégén szerepelhet egyszer

SELECT nev FROM OKTATOK WHERE varos = ‘EGER’ EGER’ UNION ALL SELECT nev FROM HALLGATOK WHERE varos = ‘EGER’ EGER’ ORDER BY 1;


SQL SELECT funkció funkciói AlAl-SELECT haszná használata A szelekció szelekciós felté feltételben szá számított érté rték is szerepelhet

Γcount(*)(σ ar> (Γavg(ar)(BOROK))(BOROK)) A szá számított érté rtéket az elő előállí llító SELECT paranccsal adjuk meg SELECT … FROM … WHERE … (SELECT …) GROUP BY … HAVING (SELECT… (SELECT…)

A SELECT kifejezé kifejezések tö többszö bbszörösen egymá egymásba ágyazható gyazhatók

SELECT COUNT(*) FROM BOROK WHERE ar > (SELECT AVG(ar AVG(ar) ar) FROM BOROK); GEIAL Kovács László

6

SQL SELECT funkció funkciói Speciá Speciális szelekció szelekciós operá operátorok

kif LIKE minta

sztring illeszkedé illeszkedése egy mintá mintára (%,_)

kif IS [NOT] NULL

üresres-e a kifejezé kifejezés

kif BETWEEN kif2 AND kif3

köztes érté rték vizsgá vizsgálat

EXISTS (al (al_SELECT) al_SELECT)

eredmé eredménytá nytábla lé létezé tezése

kif IN lista | (al (al_SELECT) al_SELECT)

tartalmazá tartalmazás

kif op ALL lista | (al (al_SELECT) al_SELECT)

minden kvantor

kif op ANY lista | (al (al_SELECT) al_SELECT)

létezé tezés kvantor

SELECT d1.kod FROM dolgozok d1 WHERE NOT EXISTS (SELECT d2.kod FROM dolgozok d2 WHERE d1.beosztas = d2.beosztas AND d1.fizetes < d2.fizetes AND d2.telep LIKE ‘Budapest%’ Budapest%’) GEIAL Kovács László

SQL SELECT funkció funkciói Speciá Speciális szelekció szelekciós operá operátorok Az alap leké lekérdezé rdezés, szelekció szelekció eredmé eredménye nem halmazké halmazként működik, tartalmazhat ismé ismétlő tlődést Van lehető lehetőség az ismé ismétlő tlődés megszű megszűrésére: DISTINCT SELECT DISTINCT mlista FROM … SELECT aggr_ aggr_fv(DISTINCT fv(DISTINCT kif) kif) FROM … SELECT COUNT(DISTINCT varos) FROM kiadok INNER JOIN konyvek ON kiadok.kod = konyvek.kiado WHERE konyv.ar > 10000

A SELECT DISTINCT biztosí biztosítja az algebrai projeció projeciót GEIAL Kovács László

7

SQL SELECT funkció funkciói Speciá Speciális szelekció szelekciós operá operátorok Az inner join eseté esetében is tö több tá tábla kapcsolható kapcsolható össze A má második join első első argumentuma az első első join lesz select * from konyv ko inner join kiado ki on ko.kiado= ko.kiado=ki.kod inner join vallalat v on ki.kiado = v.kod where ko.ar > 3000

Támogatott a natural join is r1 >
SELECT … FROM r1 NATURAL JOIN r2

SELECT * FROM kiado NATURAL JOIN konyv GEIAL Kovács László

SQL SELECT funkció funkciói Speciá Speciális szelekció szelekciós operá operátorok Sztring mintá mintára való való illeszté illesztés tová további lehető lehetőségei Foglalt karaktereket tartalmazó tartalmazó minta keresé keresése: kif LIKE minta ESCAPE ‘karakter’ karakter’ SELECT kod FROM aru WHERE nev LIKE ‘_A#_B’ ESCAPE ‘#’ Regulá Reguláris kifejezé kifejezésen alapuló alapuló minta keresé keresés: kif SIMILAR TO minta ESCAPE ‘karakter’ karakter’ xx SIMILAR TO ‘(szö (szöveg1) | (szö (szöveg2)’ veg2)’ xx SIMILAR TO ‘szö szöveg[abc]’ veg[abc]’ xx SIMILAR TO ‘szö szöveg[abc]*szö veg[abc]*szöveg[abc2]+’ veg[abc2]+’ GEIAL Kovács László

8

SQL SELECT funkció funkciói Gyá Gyári fü függvé ggvények Az adaté adatérté rtékek feldolgozá feldolgozását gyá gyári fü függvé ggvények tá támogatjá mogatják karakterkezelő karakterkezelő függvé ggvények szá számkezelő mkezelő függvé ggvények dátumkezelő tumkezelő függvé ggvények konverzió konverziós fü függvé ggvények

Nincs szabvá szabvány a fü függvé ggvények kö körére és alakjá alakjára

SELECT SUBSTR(nev,1,INSTR (nev, ‘né’, 1)) FROM dolgozok GEIAL Kovács László

SQL SELECT funkció funkciói Gyá Gyári fü függvé ggvények Karakter kezelő kezelő függvé ggvények (Oracle (Oracle): Oracle): INSTR(kar1, kar2, kpoz, kpoz, eszaml) eszaml) : részsz-sztring pozí pozíció ció SUBSTR(kar1, kpoz, kpoz, hossz) : részsz-sztring kiemelé kiemelés LENGTH(kar) : sztring hossza INSTR(kar1, kar2, kpoz, kpoz, eszaml) eszaml) : részsz-sztring pozí pozíció ció RTRIM(kar1) : jobboldali szó szóvégi szó szóközök levá levágása RPAD(kar1,n,jel) : jobboldali szó szóvég kigé kigészí szítése a jellel, hogy a teljes hossz n legyen INITCAP(kar1) :negy :negy kezdő kezdőbetű betű, tö többi kisbetű kisbetű CONCAT(kar1, kar2) : sztringek összefű sszefűzése GEIAL Kovács László

9

SQL SELECT funkció funkciói Gyá Gyári fü függvé ggvények Konverzió Konverziós fü függvé ggvények (Oracle (Oracle): Oracle): TO_CHAR(szam TO_CHAR(szam) szam) : szá szám szö szöveg tí típusú pusúra alakí alakítása TO_CHAR(dá TO_CHAR(dátum, formá formátum) : dá dátum szö szöveg tí típusú pusúra alakí alakítása TO_NUMBER(szö TO_NUMBER(szöveg, formá formátum) : szö szöveg szá szám tí típusra alakí alakítása TO_DATE(szö TO_DATE(szöveg, formá formátum) : szö szöveg dá dátum tí típusra alakí alakítása CAST(kif CAST(kif AS tipus) tipus) : tetsző tetszőleges irá irányú nyú konverzió konverzió CONVERT (szö (szöveg, kó kód1, kó kód2) : kó kódké dkészletek kö közötti konverzió konverzió DECODE (kif1, k2,k3,..) : kif1 érté rték konvertá konvertálása TRANSLATE(szö TRANSLATE(szöveg,k1,k2) : k1 cseré cseréje k2k2-re a szö szövegben GEIAL Kovács László

SQL haszná használata ANSI SQL kompatibilitá kompatibilitás A kü különbö nböző RDBMSRDBMS-ek egyedi SQL elemekkel is rendelkeznek Az SQL szabv szabvá ány és az egyes SQL implementá implementáció ciók szoros kö kölcsö lcsönhatá nhatásban állnak egymá egymással - ismerni kell a szabvá szabványt - ismerni kell a konkré konkrét SQL felü felületet -

egyedi objektumok egyedi operá operátorok egyedi fü függvé ggvények egyedi kapcsoló kapcsolók egyedi paramé paraméterek egyedi adattí adattípusok elté eltérő operá operátorok hiá hiányzó nyzó operá operátorok …

FROM T1 OUTER JOIN T2 ON f FROM T1 = (+) T2 WHERE f

WHERE WHERE WHERE WHERE

CTOD(‘2003.02.10’)… TO_DATE(‘2003.02.10’,…) CONVERT(‘2003.02.10’,…) {2003.02.0}…


10

SQL jelentő jelentősége Az SQL ez egyik legfontosabb alaptechnoló alaptechnológia az informatiká informatikában. ban. Az SQL fejlő fejlődése sorá során egyre tö több funkció funkciót integrá integrál magá magába, globá globális adatkezelé adatkezelés felé felé halad. (Vikram Vaswani) A legkeresettebb ismeretek egyike az IT állá lláshirdeté shirdetésekben az SQL,amely stabilan tartja magá magát (mellette a C++, Java, PHP, UNIX vá váltakoznak) (CWJobs.UK) Kb. évi 10%10%-os piacnö piacnövekedé vekedés az adatbá adatbáziskezelő ziskezelőkné knél, kb. 8 billió billió $ 20052005-ben (Gartner Group) Szá Számos feladat specifikus SQL DBMS létezik a piacon GEIAL Kovács László

11

Adatbá Adatbázis rendszerek I Ková Kovács

Lá László szló

Az SQL nyelv speciá speciális elemei

SQL szabvá szabványok Többbb-lépcső pcsős folyamat a fejlő fejlődése

alap DDL, DML, DQL, tranzakció tranzakci ó,INDEX

1986: ANSI SQL 1987: ISO SQL 1989: ANSI89

1992: SQL92 1999: SQL1999 2003: SQL2003 2007: ?

SQL

integritá integritási elemek, védelem,VIEW DOMAIN, CONSTRAINTS, spec. spec. tá táblá blák, SELECTSELECT-érté rték OO elemek, aktí aktív elemek,kezelő elemek,kezelő fv.fv.-ek, ek, programozá programozás, új adattí adattípus OLAP, XML,ú XML,új adattí adattípus

1

SQL szabvá szabványai Az SQL szabvá szabvány tö több, mint az alap DDL, DML, DQL. modulok SQL/Framework SQL/Framework

SQL/Foundation SQL/ Foundation SQL/Bindings SQL/Bindings SQL/CLI SQL/PSM SQL/OLAP SQL/Transaction SQL/Transaction SQL/Temporal SQL/Temporal SQL/Objects SQL/Objects SQL/MED SQL/OLB SQL/Schemata SQL/Schemata SQL/JRT SQL/XML SQL/MM

:fogalmi keretek :alap utasí utasítások :beá :beágyazott SQL :ODBC :tá :tárolt eljá eljárások :elemzé :elemzési funkció funkciók :tranzakció :tranzakció menedzser :ideiglenes adatok :OO elvek :kü :külső lső állomá llományok :beá :beágyazott JSQL :sé :séma kezelé kezelés :Java felü felület :XML felü felület :multimé :multimédia kezelé kezelés

SQL89: SQL92: SQL99: SQL03:

300 1100 2000 3600

terjedelem

SQL haszná használata Az SQL tö több, mint a SELECT parancs - tábla - view - domain - snapshot - trigger - job - tárolt eljá eljárás - tárolt fü függvé ggvény - felhaszná felhasználó - profile - role - schema - database link - replication - cluster - lock ...

interaktí interaktív

SQL

program

CLICLI-SQL E-SQL

PLPL-SQL T-SQL

Oracle SQL referencia: wwwwww-db.iit.unidb.iit.uni-miskolc.hu/ miskolc.hu/…

2

SQL tá táblatí blatípusok Az adatok tá tárolá rolása elté eltérő módokon, elté eltérő céllal tö törté rténhet

tábla

alaptá alaptábla

nézeti CREATE VIEW

szá származtatott CREATE SNAPSHOT

deklará deklarált perzisztens CREATE TABLE

loká lokáisan ideiglenes CREATE LOCAL TEMPORARY TABLE

globá globálisan ideiglenes CREATE GLOBAL TEMPORARY TABLE

SQL VIEW objektum Szá Származtatott tá táblá blák haszna: - parancs egyszerű egyszerűsítés - egyszerű egyszerűbb vé végrehajtá grehajtás - védelemv CREATE VIEW vné vnév (mné (mnév1, …) AS SELECT … ; CREATE VIEW BETEGSZAB AS SELECT nev, nev, beosztas, beosztas, reszleg FROM dolgozok WHERE status = 2; Csak az elő előállí llító SQL parancs tá tároló rolódik, az adattá adattábla nem Hivatkozá Hivatkozáskor áll elő elő a tartalom - aktuá aktuális - lassú lassú lehet

3

SQL VIEW objektum

VIEW mó módosí dosításra

4

SQL snapshot

SQL haté hatékonysá konysági elemei Hozzá Hozzáférés haté hatékonysá konysága

UPDATE .. tábla SELECT .. index

lehet explicit és implicit index lé létrehozá trehozás

CREATE TABLE t1 (m1 Number(3) Number(3) PRIMARY KEY, m2… m2… ) CREATE INDEX i1 ON t1(m2 ASC|DESC) CREATE INDEX NEVEK ON DOLGOZOK(NEV)

5

INDEX

Explicit implicit

SQL vé védelmi elemei Adat hozzá hozzáférés vé védelem

UPDATE .. objektum SELECT ..

jog adomá adományozá nyozása: GRANT jog ON objektum TO felhaszná felhasználó jog visszavoná visszavonása: REVOKE jog ON objektum FROM felhaszná felhasználó

6

SQL vé védelmi elemei Hozzá Hozzáférés vé védelmi utasí utasítások

egy objektum (objektum jog): GRANT SELECT|INSERT|UPDATE|DELETE ON objektum TO felh tetsző tetszőleges objektum (privilé (privilégium): GRANT mű művelet TO felh mindenkinek engedé engedélyezve: GRANT mű művelet ON … TO PUBLIC a kapott jog tová továbbadható bbadható: GRANT … TO … WITH GRANT OPTION | ADMIN OPTION adott jog visszavoná visszavonása: REVOKE … FROM …

SQL sé séma elemei Objektum azonosí azonosítása

UPDATE .. objektum séma

SELECT ..

kataló katalógus adatbá adatbázis

teljes azonosí azonosítás: adatbá adatbázis.kataló zis.katalógus.sé gus.séma.objektum1… ma.objektum1… ORA1.INFO.TEREMF.ORAK.KEZDES

7

SQL sé séma elemei Séma: objektumok logikai tá tároló roló egysé egysége (egy tulajdonosa van) CREATE SCHEMA sné snév paramé paraméterek DROP SCHEMA sné snév CASCADE egy felhaszná felhasználónak tö több sé sémája is lehet Kataló Katalógus: sé sémák adminisztrá adminisztráció ciós egysé egysége tartalmaz egy definí definíció ciós és egy informá információ ciós sé sémát DEFINITION_SCHEMA: leí leíró bázistá zistáblá blák (pl. USERS, SCHEMATA, TABLES, VIEWS, TABLE_PRIVILEGES,… TABLE_PRIVILEGES,…) a rendszernek szó szól INFORMATION_SCHEMA: informá információ ciós né nézetek (pl. TABLES, VIEWS, COLUMNS,TABLE_CONSTRAINTS,… COLUMNS,TABLE_CONSTRAINTS,…) a felhaszná felhasználóknak szó szól

SQL sé séma elemei CATALOG

Schema A T:ARUK

DEFINITION_SCHEMA

INFORMATION_SCHEMA

T:TABLES

V:TABLES

T:VIEWS

V:VIEWS

USER:U

SELECT * FROM INFORMATION_SCHEMA.TABLES

8

SQL elemei Az SQL kö közelí zelít az erő erősen tí típusos nyelvekhez DOMAIN:

egyedi, megkü megkülönbö nböztető ztető adattí adattípus tábla mező mezőjéhez rendelhető rendelhető típuské pusként

CREATE DOMAIN dné dnév AS tí típus CHECK megkö megkötés elő előnyei: - automatikus megkö megkötések - jelenté jelentéskö sközeli nevek CREATE DOMAIN eletkor AS INTEGER CHECK (VALUE BETWEEN 18 AND 120); CREATE TABLE DOLGOZO (kod (kod INTEGER PRIMARY KEY, .., kor eletkor,..) eletkor,..) Az OO elemek bevoná bevonásával jelentő jelentősége csö csökkent

SQL elemei ASSERTION:

Az összetett, tö többbb-táblá blás integritá integritási felté feltételek deklaratí ív megadá deklarat megadási mó módja

CREATE ASSERTION né név CHECK (felté (feltétel)

pénztá nztár kód

nyitó nyitó záró

ptá ptár érté rték

Uid

forgalom

a pé pénztá nztáranké rankénti ké készletvá szletváltozá ltozás egyezzen meg a forgalommal CREATE VIEW V1 AS SELECT kó kód, SUM(COALESCE(F.é SUM(COALESCE(F.érté rték,0)) ossz FROM forgalom F RIGTH OUTER JOIN pé pénztá nztár P ON P.kó P.kód = F.ptá F.ptár GROUP BY kó kód; CREATE ASSERTION A1 CHECK ( NOT EXISTS (SELECT P.kó P.kód FROM pé pénztá nztár P INNER JOIN V1 ON V1.kó V1.kód = P.kó P.kód WHERE (P.zá (P.záró-P.nyitó P.nyitó) <> v1.kó v1.kód))

idő időigé igényes vé végrehajtá grehajtás: - minden SQL DML parancs utá után - csak az érintett tá táblá blák DML parancsai utá után

9

SQL tranzakció tranzakció elemei Tranzakció Tranzakció elemei A konzisztens állapot el elé érésére tö több mű műveletnek együ együttesen kell lefutnia tranzakció tranzakció: adatbá adatbázis mű műveletek egysé egységké gként kezelt sora

INSERT INTO dolgozo VALUES(1,… VALUES(1,…); UPDATE dolgozo SET fiz = 0; ROLLBACK;

tranzakció tranzakció

COMMIT

elfogad

ROLLBACK

egyes mű műveletek (pl. DDL) rögtö gtön vé végrehajtó grehajtódnak

elvet

SQL leké lekérdezé rdezés elemei A felté megjelení ítésre is igé feltételes megjelen igény jelentkezik, ezé ezért pótoltá tolták ezt a hiá hiányossá nyosságot: CASE WHEN WHEN … ELSE END

felté feltétel1 THEN felté feltétel2 THEN

kif1; kif2;

kif; kif;

A SELECT parancs projekció projekciós ré részé szében haszná használható lható SELECT né név, fiz * (CASE WHEN fiz<1000 THEN 0.1; WHEN fiz BETWEEN 1000 AND 5000 THEN 0.2; ELSE 0.3; END) adó adó FROM dolgozok; A NULL érté rték kezelé kezelésére: COALESCE(kif1,kif2,kif3,… COALESCE(kif1,kif2,kif3,…) Az első első nem NULL érté rtéket adja vissza

10

NULL érté rték kezelé kezelése

NULL érté rték NVL

11


Lá László szló

Normalizá Normalizálási alapok

dr Ková Kovács László szló

ME GEIAL

Normalizá Normalizálási alapok A szoftver az egyik legö legösszetettebb és legmegbí legmegbízhatatlanabb ipari termé termék Szá Számítási hibá hibák Mars Climate Orbiter (1999) Ariane 5 raké rakéta (1996) Védelmi hiá hiányossá nyosságok ILOVEYOU (2000), $10 billió billió SQLslammer (2003) Adatkezelé Adatkezelés Y2000 és Y2038 Kezelő Kezelő felü felület hiá hiányossá nyosságai …


1

Normalizá Normalizálási alapok A tervezé tervezéssel eltö eltöltö ltött idő idő sohasem elvesztegetett idő idő Adatbá Adatbázisokná zisoknál a javí javítás kö költsé ltsége a tervezé tervezési hiá hiányossá nyosságokná goknál egy nagysá nagyságrenddel nagyobb mint a tö többi hiba eseté esetében Mire kell ügyelni? -

minden informá információ cióelem meglegyen beszé beszédes elnevezé elnevezések érvé rvényes adattí adattípusok érvé rvényes kapcsolatok érvé rvényes integritá integritási elemek kezelé kezelés biztonsá biztonsága haté hatékonysá konyság védelem …

Failure is not an option. option. It is a key part of a software. GEIAL Kovács László

Normalizá Normalizálási alapok Az adatbá adatbázis tervezé tervezésné snél, az egyik jó jól formalizá formalizálható lható elem a redundancia kezelé kezelése redundancia: informá információ ció ismé ismétlő tlődése hátrá trányai: - beszú beszúrási anomá anomália - módosí dosítási anomá anomália - törlé rlési anomá anomália

elő előnyei: - elé elérési gyorsasá gyorsaság - hibatű hibatűrés

- helyfoglalá helyfoglalás cél: szabá szabályozott, minimá minimális redundancia dkod


uticél

dnév

dcím

befizetes

1

Eger

Peter

Miskolc

3

3

Eger

Antal

Baja

5

1

Ózd

Peter

Miskolc

2

2

Normalizá Normalizálási alapok redundancia oka: mező mezők kö közötti érté rték-függő ggőség

dkod

uticél

dnév

dcím

befizetes

1

Eger

Peter

Miskolc

3

3

Eger

Antal

Baja

5

1

Ózd

Peter

Miskolc

2

függő ggőség jele: FD

A

B

függő ggőség értelmezé rtelmezése: minden A érté rtékhez maximum egy B érté rték tartozik Az FD eltá eltávolí volítása megszü megszünteti a redundanciá redundanciát GEIAL Kovács László

Normalizá Normalizálási alapok Normalizá Normalizálás: redundancia megszü megszünteté ntetése az FDFD-k eliminá eliminálásával de nem mindegyik FDFD-t kell eldobni!

dkod

uticél

dnév

dcím

befizetes

1

Eger

Peter

Miskolc

3

3

Eger

Antal

Baja

5

1

Ózd

Peter

Miskolc

2

Az ismé ismétlő tlődő érté rtékű mező mezőkbő kből kiinduló kiinduló FDFD-ket kell elvetni A kulcsbó kulcsból kiinduló kiinduló FDFD-k nem okoznak redundanciá redundanciát


3

Normalizá Normalizálási alapok A nemkí nemkívánt FD megszü megszünteté ntetése a tartalmazó tartalmazó relá reláció ció felbontá felbontásával tö törté rténik (dekompoz (dekompozí dekompozíció ció)

dkod 1

dkod 1

uticél

dnév

Eger

uticél Eger

Peter

befizetes

dcím Miskolc

befizetes 3

dkod

dnév

1

Peter

3

dcím Miskolc

A felbontá felbontásnak vesztesé veszteségmentesnek kell lennie Az eredeti relá reláció ciókat elő elő kell tudni állí llítani a ré részekbő szekből GEIAL Kovács László

Normalizá Normalizálási elemek Mező Mező elnevezé elnevezéshez kapcsoló kapcsolódó hibá hibák

szinonima nem beszé beszédes elnevezé elnevezés

homonima TILOS!

DOLGOZO(nev DOLGOZO(nev,c12,fonok,f_ nev,c12,fonok,f_cim ,c12,fonok,f_cim, cim,fizetes,telephely) fizetes,telephely) OSZTALY(nev OSZTALY(nev,telephely, nev,telephely,vezeto ,telephely,vezeto,v_ vezeto,v_cim ,v_cim, cim,fizetes) fizetes) PROJEKT(nev PROJEKT(nev,d_ nev,d_nev ,d_nev, nev,megrendelo, megrendelo,fizetes, fizetes,mcim) mcim)

technikai homonima

nyilt logikai átfedé tfedés


4

Normalizá Normalizálási elemek FD fogalma:

A → B az R(A,B,C) sé sémában, ha minden t1,t2 r(R)r(R)-beli rekordra, ha t1(A) = t2(A), akkor t1(B) = t2(B) is teljesü teljesül definí ció fü ggő a defin íci ó r(R) f ügg ő

eseti fü függő ggőség (né (néhány rr-re) ⇔ érdemi fü függő ggőség (minden rr-re) egy adott r(R)r(R)-ből nem lehet kö következtetni az FD érdemisé rdemiségére, de lehet kö következtetni az esetisé esetiségére minden R sé sémához tartozik egy érdemi FD halmaz

dkod

uticél

dnév

dcím

befizetes


Normalizá Normalizálási elemek Cél: áttekinthető ttekinthetőség, egyszerű egyszerűség

dkod

uticél

dnév

dcím

befizetes

felesleges FDFD-k jelennek meg az FDFD-k kö között is redundancia jelenhet meg Armstrong axió axiómák: ha B ⊆ A ⇒ A → B (triviá (triviális FD) A → B ⇒ AC → BC A → B, B → C ⇒ A → C következmé vetkezmények: A → B, C → D ⇒ AC → BD GEIAL Kovács László

5

Normalizá Normalizálási elemek Egy FDFD-halmaz kibő kibővíthető thető a szá származtatott FDFD-kel Az X FDFD-halmaz lezá lezártja: X+ X+ : {B | X ⇒ B} FDFD-halmazok ekvivalenciá ekvivalenciája: X ≈ Y, ha X+ = Y+ Egy FDFD-halmaz egyszerű egyszerűsíthető thető a redundá redundáns FDFD-kel Az X FDFD-halmaz irreducibilis magja: XX- jellemz jellemző ői: - nem tartalmaz triviá triviális FDFD-t - jobb oldal elemi - minimá minimális Belá Belátható tható, hogy X ≈ X+ és X ≈ X- és X- ≈ X+

X-

X

X+


Normalizá Normalizálási elemek

dkod

uticél

dnév

dcím

befizetes

irreducibilis mag meghatá meghatározá rozása

dkod

uticél

dnév

dcím

befizetes


6

Normalizá Normalizálási elemek Redundancia oka: ismé ismétlő tlődő érté rtékű mező mezőből kiinduló kiinduló FD Redundancia mentesí mentesítés: csak nemnem-ismé ismétlő tlődő érté rtékű mező mezőkbő kből induljon ki FD Normalizá Normalizálás: nem kí kívánt FDFD-k megszü megszünteté ntetése Az FDFD-ket a mező mezők szé szétvá tválasztá lasztásával, a relá reláció ció felbontá felbontásával (dekompozí dekompozíció ció) szü szüntetjü ntetjük meg


Normalizá Normalizálási elemek Alap normalizá normalizálási lé lépések: -

dkod

FDFD-k feltá feltárása 1NF : lé létezzen kulcs, minden mező mező elemi 2NF : 1NF + ré részkulcsbó szkulcsból ne induljon ki FD 3NF : 2NF + nem kulcsbó kulcsból ne induljon ki FD

uticél

dnév

dcím

1NF dkod

uticél

befizetes

3NF

2NF dnév

befizetes dkod

dcím

dnév


7

Normalizá Normalizálási elemek A gyakorlat szinte mindig megtalá megtalálja az elmé elmélet hiá hiányossá nyosságait Mié Miért nem jó jó a 3NF?

ig.szám

taj-szám

név

A felbontá felbontás nem hoz nyeresé nyereséget Szü üksé Sz kségtelen a felbontá felbontás A taj_sz taj_szá _szám jelö jelölt kulcs, nem ismé ismétlő tlődik, nem okoz redundanciá redundanciát

BCNF:

FD csak jelö jelölt kulcsbó kulcsból induljon ki


Normalizá Normalizálási elemek Az elmé elmélet a gyakorlat egy kö közelí zelítése, modellje Az adatbá adatbázis tervezé tervezésné snél a haté hatékonysá konysági szempontokat is lé lényegesnek kell tekinteni (JOIN kö költsé ltségessé gessége) rere-decomposition: decomposition: a szé szétbontott tá táblá blák egyesí egyesítése „A normalizá normalizálás nem tervezé tervezés hanem elemzé elemzés” „A val való ós adatbá adatbázissé zisséma a 3NF és a teljes ká káosz kö között van” van”


8

Normalizá Normalizálási alapok

Egyedi elemek - levezethetős levezethet ség és logikai következmé vetkezmény - igazsá igazságté gtétel bizonyí bizonyítása - levezeté levezetési példá ldák - szuperkulcs - FD halmaz lezá lezárási tételek - teljessé teljességi tétel bizonyí bizonyítása - lezá lezárási té tétel bizonyí bizonyítása


9


Lá László szló


dr Ková Kovács László szló

ME GEIAL

Normalizá Normalizálási elemek A hibá hibás sé séma megszü megszünteté ntetése a sé séma felbontá felbontásával tö törté rténik Mire kell ügyelni a felbontá felbontásná snál? -

mező mező ne szű szűnjö njön meg informá információ ció ne vesszen el hibá hibás FD eliminá eliminálódjon új FD ne lé lépjen fel redundancia ne nö növekedjen


1

Normalizá Normalizálási elemek Vesztesé Veszteségmentessé gmentesség: Az induló induló relá reláció ció helyreá helyreállí llítható tható legyen a felbontá felbontás utá után is - mező mezők és kapcsolatok megő megőrzé rzése - helyreá helyreállí llítás a joinjoin-nal

Nem minden felbontá felbontás vesztesé veszteségmentes Megfelelő Megfelelő kapcsolatot kell hagyni

A FD menté mentén kell felbontani A FD kiindul kiinduló ó mező mezője megfelelő megfelelő kapcsoló kapcsoló


Normalizá Normalizálási elemek Független felbontá felbontás: A kapott relá reláció ciók tartalma egymá egymástó stól fü függetlenü ggetlenül kezelhető kezelhető - FD nem nyú nyúlik át má másik relá reláció cióba Az FDFD-k loká lokális megő megőrzé rzésén alapul rsz

típus

gyártó

rsz

típus

rsz

gyártó

ab34

Fiat

ab34

FiatW

gz56

Opel

gz56

OpelW

ag91

Opel

ag91

?


2

Normalizá Normalizálási elemek Mindig ad adó ódhatnak vá váratlan esemé események, esetek az elmé elmélet oldalá oldaláról is… is… név

ital

étel

Peti

sör

lecsó

Peti

bor

lecsó

Peti

sör

kaviár

Peti

bor

kaviár

nincs FD, nem lehet normalizá normalizálni ? MVD: tö többé bbérté rtékű függő ggőség (A →> B) egy A érté rtékhez egyé egyértelmű rtelműen hozzá hozzárendelhető rendelhető egy BB-beli érté rtékhalmaz GEIAL Kovács László

Normalizá Normalizálási elemek Függő ggőségek kapcsolata FD: olyan MVD, melyben az érté rtékhalmaz egyelemű egyelemű JD: az r relá reláció ció elő előállí llítható tható az r1,…,rn relá reláció ciók join műveleté veletével MVD: olyan JD, melyben a felbontá felbontás ké két relá reláció cióra tö törté rténik

FD

MVD

JD

A tervezé tervezés sorá során a nemnem-FD fü függő ggőségeket eliminá eliminálni kell


3

Normalizá Normalizálási elemek Séma tervezé tervezés összefoglalá sszefoglalása -

informá információ ció igé igények összegyű sszegyűjté jtése integritá ási felté integrit feltételek meghatá meghatározá rozása fogalmak jelenté jelentésének meghatá meghatározá rozása adatelemek elnevezé elnevezése, tí típusa, megkö megkötések kapcsolatok meghatá meghatározá rozása relá reláció ciók meghatá meghatározá rozása

-

FD, MVD, JD meghatá meghatározá rozása kulcsok kijelö kijelölése (1NF) részsz-kulcsbeli FD megszü megszünteté ntetése (2NF) tranzití tranzitív FD megszü megszünteté ntetése (3NF) csak jelö jelölt kulcsbó kulcsból indul ki FD (BCNF) a nem FD jellegű jellegű MVD megszü megszünteté ntetése (4NF) a nem FD jellegű jellegű JD megszü megszünteté ntetése (5NF)

- hat haté ékonysá konyság elemzé elemzés - redundancia nö növelé velés (denormaliz (denormalizá denormalizálás) - generá generáló kód (SQL) elő előállí llítása


Normalizá Normalizálási elemek Vesztesé Veszteségmentes felbontá felbontás p : az R sé séma egy felbontá felbontása (∪ (∪ Ri = R) ×p(r) : az r relá reláció ció p szerinti felbontá felbontásának összekapcsolá sszekapcsolása vesztesé veszteségmentes felbontá felbontás: r = ×p(r)

dkod

dkod

dnév

dnév

dkod

dnév

dnév

dcím

dcím

dcím


4

Normalizá Normalizálási elemek Vesztesé Veszteségmentes felbontá felbontás Heathkrité ériuma): Heath-tétele (vesztesé (veszteségmentessé gmentesség krit Ha R = R1 ∪ R2 ∪ R3 és R1 → R2 akkor a p = ({R1,R2}, {R1,R3}) felbontá felbontás vesztesé veszteségmentes

dkod

dnév

R1= dkod R2 = dné dnév R3 = dcim

dcím

R1= dkod R2 = dcí dcím R3 = dné dnév


R1= dné dnév R2 = dcí dcím R3 = dcim

Normalizá Normalizálási elemek Független felbontá felbontás p : az R sé séma egy felbontá felbontása (∪ (∪ Ri = R) F : az R sé sémához tartozó tartozó FD halmaz φp(r) : az F halmaz p szerinti felbontá felbontáshoz tartozó tartozó részhalmazá szhalmazának lezá lezártja φp(r) = {X →Y | (X →Y) ∈ F, ∃i: X,Y⊆ X,Y⊆Ri } Független felbontá felbontás:

dkod


dnév

φp(r)+ = F+

dcím

dkod

dnév

dkod

dcím

dkod

dnév

dnév

dcím

5

Normalizá Normalizálási elemek Független felbontá felbontás RissanenRissanen-tétele (fü (függetlensé ggetlenség krité kritériuma) Ha R = R1 ∪ R2 ∪ R3 és R1 → R2 és F ⊆ φp(r)+ akkor a p = ({R1,R2}, {R1,R3}) felbontá felbontás fü független

dkod

R1= dkod R2 = dné dnév R3 = dcim

dnév

dcím

R1= dkod R2 = dcí dcím R3 = dné dnév


R1= dné dnév R2 = dcí dcím R3 = dcim

Normalizá Normalizálási elemek BCNF alak jellemzé jellemzése: BCNF: minden A,B ⊆ R –re , ahol (A → B) ∈ F+ és (A → B) nem triviá triviális : az A szuperkulcs belá belátható tható, hogy nincs tranzití tranzitív FD ha (R,F) nem BCNF, akkor ∃ (A → B) ∈ F-, hogy A nem szuperkulcs BCNF alak jelentő jelentősége: Minden (R,F) sé séma felbontható felbontható BCNF relá reláció ciókra vesztesé veszteségmentesen Felbontá Felbontás algoritmusa: - nem szuperkulcsbó szuperkulcsból kiinduló kiinduló FD keresé keresése (A → B) - R1 = AB, R2 = R\ R\B


6

Normalizá Normalizálási elemek BCNF felbontá felbontás pé példa

A

B

(AC → E) (D → A) (E → B) (AC → C)

C

D

E

(AC → E) (D → A) (E → B)

(CD → ABE)

(CD → EB) (D → A)

B

E

A

D

E

C

D


Normalizá Normalizálási elemek Atomi sé séma: nincs vesztesé veszteségmentes és fü független felbontá felbontása

E

C

D

C

D

Nem minden atomi sé séma BCNF

E

Minden (R,F) sé felbontható ó vesztesé séma felbonthat veszteségmentes, de nem felté étlenü felt tlenül atomi BCNF relá reláció ciókra E

D

C

E

Belá Belátható tható, hogy minden (R,F) sé séma felbontható felbontható vesztesé veszteségmentes és atomi 3NF relá reláció ciókra GEIAL Kovács László

7

Normalizá Normalizálási elemek Többé bbérté rtékű függé ggés nev

hobby

etel

Peter

foci

bab

Peter

foci

szilva

Peter

tenisz

bab

nev

Peter

tenisz

szilva

Gabi

futas

Gabi

futas

nev

hobby

hobby

Peter

bab

Peter

foci

Peter

szilva

alma

Peter

tenisz

Gabi

alma

fagyi

Gabi

futas

Gabi

fagyi

MVD: t tö öbbé bbérté rtékű függő ggőség (A →> B) ∀ t1,t2 ∈ r -re, ahol t1[A] = t2[A]: ∃ t3,t4 ∈ r: - t3[AB] = t1[AB] - t3[R\ [R\AB] = t2[R\ [R\AB] - t4[AB] = t2[AB] - t4[R\ [R\AB] = t1[R\ [R\AB] GEIAL Kovács László

Normalizá Normalizálási elemek Többé bbérté rtékű függé ggés jellemző jellemzői triviá triviális MVDMVD-k: - ha A ⊆ B, akkor B →> A - ha AB = R, akkor A →> B - ha A →> B, akkor A →> R \ AB - ha A → B , akkor A →> B MVD fontossá fontossága Fagin tétele: (vesztesé (veszteségmentessé gmentesség elé elégsé gséges felté feltétele) Ha R = R1 ∪ R2 ∪ R3 és R1 →> R2 akkor és csak akkor a p = ({R1,R2}, {R1,R3}) felbontá felbontás vesztesé veszteségmentes


8

Normalizá Normalizálási elemek JoinJoin-függő ggőség, a helyzet egyre bonyoló bonyolódik.. tárgy

taná tanár

diá diák

tárgy

diá diák

tárgy

taná tanár

taná tanár

diá diák

Prog. Prog.

T1

D1

Prog. Prog.

D1

Prog. Prog.

T1

T1

D1

Prog. Prog.

T1

D2

Ab.

D2

Ab.

T2

T3

D1

Prog. Prog.

T2

D2

Prog. Prog.

D2

Prog. Prog.

T2

T1

D2

Opt. Opt.

T1

D1

Opt. Opt.

D1

Opt. Opt.

T1

T2

D2

Opt

T3

D1

Opt

T3

Ab.

T2

D2

a sé sémában nincs FD és MVD (nincs benne pl. t(Prog t(Prog., Prog., T2, D1)) viszont elő előáll a ré rész relá reláció ciók szorzataké szorzataként JD (join (joinjoin-függő ggőség): r = r1 ×= r2 ×= r3 … GEIAL Kovács László


Egyedi elemek - Vesztesé Veszteségmentessé gmentesség tétele bizonyí bizonyítása - Heath tétel bizonyí bizonyítása - Tábla mó módszer a vesztesé veszteségmentessé gmentességné gnél - BCNF felbontá felbontási té tétel bizonyí bizonyítása - 3NF felbontá felbontási té tétel bizonyí bizonyítása - normá normálformá lformák kapcsolatá kapcsolatának bizonyí bizonyítása


9

Adatbá Adatbázis rendszerek I mySQL kezelé kezelése

Dr. Ková Kovács

MEME- GEIAL

Lá László szló

DBMS alternatí alternatívák problé probléma mé mérete otthoni feladat

adatkezelő adatkezelő állomá llomány kezelé kezelés Excel kezelé kezelés

egyé egyéni vá vállalkozá llalkozás xBase/Access xBase/Access kis vá vállalat

mySQL Oracle XE

Közép vá vállalat

nagyvá nagyvállalat

SQLServer Oracle DB2

nemzetkö nemzetközi cé cég

Terradata


1

mySQL áttekinté ttekintése mySQL: mySQL: nyí nyílt felü felületű letű adatbá adatbáziskezelő ziskezelő-rendszer (SUN) típusai:

Community Server (ingyenes) Enterprise Server (fizető (fizetős)

letö etölté ltési hely: http: http://dev.mysql.com //dev.mysql.com/downloads dev.mysql.com/downloads


http://www.mysql.com/whyhttp://www.mysql.com/why-mysql/marketshare/ mysql/marketshare/

mySQL installá installáció ció

GEIAL Kovács László GEIAL Kovács László

2




Instance: Instance: futó futó DBMS

Adatbá Adatbázis DB1 Adatbá Adatbázis DB2

Szolgá Szolgáltatá ltatás (mySQL (mySQL) mySQL) net start mySQL

net stop mySQL

Állomá llományok, kataló katalógusok Bin (->Path) Path) Data Docs Examples Include lib Share


3

mySQL működése DBMS mű működése


www.akadia.com

mySQL működése myISAM tábla jellemzé jellemzése Az alapé alapértelmezé rtelmezési tá tábla tá tárolá rolási mó mód CREATE TABLE tnev (mezo tipus intfelt, intfelt,…) TYPE=myISAM TYPE=myISAM; myISAM; Minden adatbá adatbázis egy kü külön kataló katalógus Minden t tá ábla FRM : MYD : MYI :

egy vagy tö több állomá llomány tá ábla szerkezet leí t leíró adatok indexek

Belső Belső tárolá rolási mó mód: -

fixed rekordmé rekordméret változó ltozó rekordmé rekordméret (TEXT, BLOB kü külön) tömörített (READONLY)

Táblabla-szintű szintű zárolá rolás


4

mySQL működése HEAP tábla jellemzé jellemzése A tá tábla tartalma a memó memóriá riában foglal helyet CREATE TABLE tnev (mezo tipus intfelt, intfelt,…) TYPE=HEAP; TYPE=HEAP; Elő Előnyö nyök:

- gyorsabb mó módosí dosítás - szekvenciá szekvenciális keresé keresés gyorsabb - felé felépítés gyorsabb

Hátrá trányok:

- tempor temporá ális - lassabb az összetett leké lekérdezé rdezés - kevesebb mező mezőtípust tá támogat

Indexelé Indexelés lehetsé lehetséges, de csak HASH indexet generá generál


mySQL működése innoDB tábla jellemzé jellemzése A legnagyobb integritá integritáns védelmet biztosí biztosítja CREATE TABLE tnev (mezo tipus intfelt, intfelt,…) TYPE=innoDB TYPE=innoDB; innoDB; A tá táblá blák együ együtt tá tároló rolódnak (tablespace (tablespace) tablespace) Tábla szerkezeti ré része: FRM : tá tábla szerkezet leí leíró Tábla tartalom (együ (együtt) IBDATAn : tartalom, indexek Támogatott szolgá szolgáltatá ltatások: - rekord szintű szintű zárolá rolás - ACID elvek - idegen kulcs


5

mySQL haszná használata Kliens oldali elemek: mysql parancsparancs-felü felület -u -D -h -X -e -p

nev : felhaszná felhasználó neve adatbá ázis neve nev : adatb nev : host neve : XML kimenet parancs : parancs végrehajtá grehajtás ertek: ertek: jelszó jelszó megadá megadása

Batch file futtatá futtatása: os> os> mysql … < file.txt mysql> mysql> source file.txt


mySQL haszná használata Kliens oldali elemek: mysql query browser


6

mySQL haszná használata Alapparancsok Parancsfelü Parancsfelület indí indítása: Kliens csatlakozá csatlakozása: os> os> mysql –host H –u U -p Adatbá Adatbázisok kezelé kezelése: Adatbá Adatbázis lé létrehozá trehozása: sql> sql> CREATE DATABASE db Adatbá Adatbázis megszü megszünteté ntetése: sql> sql> DROP DATABASE db Adatbá Adatbázis kivá kiválasztá lasztása: sql> sql> USE db1 Adatbá Adatbázisok adatainak leké lekérdezé rdezése: sql> sql> SHOW DATABASES Aktuá Aktuális adatbá adatbázis neve: sql> sql> SELECT DATABASE() Táblá blák kezelé kezelése: Táblanevek listá listája: sql> sql> SHOW TABLES Egy tá tábla felé felépítésének kijelzé kijelzése: sql> sql> DESCRIBE tá tábla GEIAL Kovács László

mySQL haszná használata Speciá Speciális tá tábla lehető lehetőségek A tá tábla lehet ideiglenes élettartalmú lettartalmú CREATE TABLE TEMPORARY tnev (…) A megszorí megszorításokhoz azonosí azonosító név rendelhető rendelhető CREATE TABLE … (mezo tipus CONSTRAINT mnev kifejezes) kifejezes) Automatikus érté rtékadá kadás kulcs mező mezőknek CREATE TABLE … (mezo tipus AUTO_INCREMENT ..) Create table t1 (a int auto_increment primary key, b char(3)); Insert into t1(b) values (‘ (‘szoveg’ szoveg’); Tábla teljes tö törlé rlése: TRUNCATE TABLE tabla GEIAL Kovács László

7

mySQL haszná használata Felhaszná trehozá Felhasználói account létrehoz ása: sql> > CREATE USER unev IDENTIFIED BY jelszo sql A felhaszná felhasználó jogait explicite meg kell adni Teljes jog: GRANT ALL ON *.* TO nev@host nev@host Adatbá Adatbázis szintű szintű jog: GRANT ALL ON db.* TO nev@host nev@host Tábla szintű szintű művelet: GRANT ALL ON db.tabla TO nev@host nev@host Mező Mezőszintű szintű műveletek: GRANT SELECT(mező SELECT(mező), INSERT(mező INSERT(mező) ON db.tabla TO nev@host nev@host Jogok és privilé privilégiumok adható adhatók ki


mySQL haszná használata Index lé létrehozá trehozása: CREATE INDEX inev ON tabla (mezo1 ASC|DESC,..) USING BTREE | HASH | RTREEE

BTREE create create Select Select Select Delete

HASH

RTREE

table ss2 (a int, int, b int, int, c char(10)); index i2 on ss2 (a,b (a,b); a,b); count(*) from ss2 where b=5; // 00.64 00.64 count(*) from ss2 where a=5; // 00 00.00 .00 count(*) from ss2 where a+b=10 a+b=10 // 00.68 00.68 fropm ss2 where b<5; // 27.10

sec sec sec sec

Index tö törlé rlése: DROP INDEX nev GEIAL Kovács László

8

mySQL haszná használata Karakterké Karakterkészlet, rendezé rendezési sorrend

CHARACTER SET: ABC, betű betűkészlet (cp1250, latin2,… latin2,…) COLLATION: rendezé rendezési sorrendisé sorrendiség

Adatbá Adatbázis, tá tábla és mező mező szinten lehet beá beállí llítani

CREATE TABLE tnev () CHARACTER SET nev COLLATE nev Create table ss1 (a int, b char(10)) char(10)) CHARACTER SET cp850 collate cp850_bin cp850_bin; bin; Konstans karaktertí karaktertípusá pusának beá beállí llítása: _tipus _tipus’ tipus’érté rték’


mySQL haszná használata Karakterké Karakterkészlet, rendezé rendezési sorrend

create create insert insert

table ss5 (a int, int, b char(20)) character set cp850; table ss6 (a int, int, b char(20)) character set latin2; latin2; into ss5 values (1,'aá (1,'aáeéiíoóöő óöőuúüű úüű'); -- hiba into ss6 values (1,'aá (1,'aáeéiíoóöő óöőuúüű úüű'); -- rendben

select * from ss6 order by b collate latin2_hungarian_ci latin2_hungarian_ci select * from ss6 order by b collate latin2_bin latin2_bin


aáeéií álom aroma élet eredő eredő írhat itat kelet üzlet

aroma aáeéií eredő eredő itat kelet álom élet írhat üzlet

9

mySQL haszná használata Gyá Gyári fü függvé ggvények Dátum kezelő kezelő függvé ggvények Select dayname dayname( (convert( convert(‘19681968-1111-12’ 12’)); select datediff(curdate(),convert('1968 datediff(curdate(),convert('1968-1111-12',datetime)); ',datetime)); Szö Szöveg kezelő kezelő függvé ggvények Select upper( upper(substr( substr(rpad( rpad(nev),2)); nev),2)); Update ugyfelek set lakcim= lakcim=replace( replace(lakcim, lakcim,’Bp’ Bp’,’Budapest’ Budapest’); Szá Számkezelő mkezelő függvé ggvények Select floor (1 + rand()*89) rand()*89) Select power(2,6) power(2,6)


10

GEIAL Kovács László. GEIAL Kovács László

Recommend Documents