Samenvatting slides gegevensbanken Niels Vermeiren & Simon Cek 1 No Non n Cor Corrrel elat ated ed Su Subqu bquer ery’ y’ss • • •
Niet gecorreleerde subquery = Tabelexpressie binnen een tabelexpressie esultaat !ordt doorgegeven aan aanroepende tabelexpressie Subquery’s mogen genest "i#n
1$1 1$ 1 Sc Scala alair ire e sub subqu quer ery y = 1 ri#% 1 !aarde
1$ 1$ i i## ' su subq bque uery ry = 1 ri#
1$( 1$ ( )olo olom m ' su subq bque uery ry = elke ri# 1 !aarde
1$* 1$ * Tab abel el ' subq subque uery ry = ver"ameling ri#en en kolommen kolommen • • • •
+rom ' component bevat subquery Ti#deli#k resultaat resultaat ,een order by Subquery moet pseudoniem kri#gen
-oins $1 +./ • • •
0evat tabelspecicaties )unnen subquery’s "i#n 2mpliciete #oins 3 expliciete #oins
$ $ 4x 4xpl plici iciet ete e inne innerr #oin #oin
$( 4xp 4xplici liciete ete +ull .u .uter ter -oi -oin n
$* 4xp 4xplici liciete ete 5e 5e6t 6t .ut .uter er -oin -oin
$7 .ute terr -oi -oin ns •
5e6t8outer8#oin9 :lle ri#en uit linker tabel met eventueel bi#;orende gegevens uit rec;ter tabel% anders N<55 !aardes ig;t8outer8#oin9 :lle ri#en uit rec;ter tabel met eventueel bi#;orende gegevens uit de linker tabel% anders N<55 !aardes +ull8outer8#oin9 :lle ri#en uit linker tabel en alle ri#en uit rec;ter tabel met eventueel bi#;orende gegevens uit andere tabel% anders N<55 !aardes
•
•
$ Con Condit dities ies +ro rom m vs >;er >;ere e • •
4xtra voor!aarden mogen 5et op er is een versc;il met !;ere?
$@ Cr Cros osss -oi -oin n = expliciet cartesisc; product
$A
$B $ B Na Natu tura rall -o -oin in = natuurli#ke #oin% lexiograsc;
$1 $ 1 4q 4qui uiDT; DT;et eta a -oin -oin 4qui ' #oin = vergeli#king met = T;eta ' #oin = vergeli#king vergeli#king met met een andere andere vergeli#kingsoperator vergeli#kingsoperator
$11 $ 11 5a 5ate tera rall -oi -oin n Subqueries die voorkomen in de +./ kunnen voora6 gegaan !orden door ;et sleutel!oord 5:T4:5$ Eit laat ;en toe te ver!i#"en naar kolommen ver"orgd door de voorgaande +./ items$ Fonder 5:T4:5% !ordt elke subquery ona6;ankeli#k geGvalueerd en kan er "o geen kruisende ver!i#"ing gemaakt !orden naar een ander +./ item$
( ,e ,eco corr rrel elee eerd rde e su subq bque uery ry’s ’s ($1 ($ 1 Su Subq bque uery ry’s ’s in in >H4 >H44 4 >elke soort subquery mogeli#k is% ;angt a6 van de geko"en operator$ =% 3% I% J 9 scalaire 2NKJL
9 kolom
($ ($ Ho Hoo6 o6dq dque uery ry en subq subque uery ry • • •
Hoodquery kri#gt alles !at in S454CT staat van subquery Hoo6dquery !eet niets van detail van subquery% kri#gt alleen de output Subquery !eet alles van ;oo6dquery
($( ,ec ,ecor orre relee leerd rde e subq subquer uery y = subquery !aarin een kolom !ordt gebruikt die tot een tabel be;oort uit een andere select8blok$ •
Subquery kan dus niet autonoom uitgevoerd !orden$
($* .pe .perat rator or 4MS2S 4MS2STS TS and and N.T 4MS2 4MS2STS STS
Voor N.T 4MS2TS idem$
($7 ($ 7 .p .per erat ator or :N :N an and d :55 :55 = extra operator% ver!ac;t scalaire !aarde en kolomexpressie Voorbeeld all ,ee6 van elk team ;et teamnr en de nr van de speler met ;et laagste aantal ge!onnen sets9 S454CT distinct teamnr% spelersnr +./ !edstri#den !1
>H44 ge!onnen I= :55 KS454CT KS454CT ge!onnen 6rom 6rom !edstri#den ! !;ere !;ere !1$teamnr = !$teamnrL Voorbeeld any ,ee6 de spelersnummers spelersnummers van de spelers voor !ie minstens OOn boete betaald is die groter is dan een boete betaald voor speler @P de"e speler mag "el6 niet in ;et resultaat voorkomen$ S454CT E2ST2NCT spelersnr +./ spelers inner #oin boetes b usingKspelersnrL >H44 b$bedrag 3 :NKS454CT bedrag +./ boetes >H44 spelersnr = @L :NE spelersnr I3 @
3:55 3= :55 3:N 3:N 3= :N :N I:55 I:N
($ ($ .p .per erat ator or
($@ ($ @ .p .per erat ator or .v .ver erlap lapss
* Qat atrron onen en ve verrge gelili#k #ken en *$1 52) 52)4 4 ' operat operator or KSql KSql stan standaa daard rdLL = like operatoren operatoren matc;en alti#d op de ge;ele string? • •
•
>;ere naam 52)4 RD’ 4SC:Q4 RD’ 4scapen van tekens "orgt ervoor dat de"e gebruikt !orden "oals "e "i#n% "onder interpretatieve$ interpretatieve$ 52)4 I3 N.T 52)4
*$ Similar to operato operatorr KSql KSql standaar standaard d sinds sinds 1BBBL 1BBBL • •
Foals 52)4 met % en 4SC:Q4 U extra’s uit regex staat voor o6 W staat voor mogeli#ke ;er;aling U staat voor mogeli#ke ;er;aling KL om samen te nemen XY analog aan ge!one regex regex
*$( *$ ( :n :nde derre oper operat ator oren en • • • •
04T>44N .V45:QS 2S N<55 N.T
7 Ven ensste terr 6un 6unct ctie iess 7$1 7$ 1 Nu Numm mmer erin ings gs6u 6unc ncti ties es 7$1$1 7$1 $1 o!n o!numb umberK erKLL o!numberKL overKL !ilt "eggen dat !e een extra kolom toevoegen met ri#nummers% op basis van een nummering via een order by in de .V4KL component$ Ee"e kan versc;illen van de order by van de query% !aardoor de"e ri#nummers niet de"el6de ;oeven te "i#n als de natural numbers$ S454CT ro!numberKL .V4K.E4 0 spelersnrL% spelersnrL% plaats% spelersnr +./ spelers >H44 geslac;t = Z/Z .E4 0 plaats N<55S +2STP 0i# de"e query !ordt een kolom toegevoegd met ri#nummers die toegekend !orden op basis van ;et spelersnr asc Kdit komt door de .E4 0 spelersnr in de .V4KL componentL $
7$1$ 7$ 1$ an ankK kKLL ank doet ;et"el6de als ro!numberKL maar gaat t!ee ri#en die een"el6de kolom!aarden ;ebben de"el6de ri#nummer geven Kri# nummer = :N) nummerL$ 0i# ;et toe!i#"en van een"el6de ri#nummer aan een t!eede ri#% !ordt ;et ri#nummer ac;terliggend !el ver;oogd% !aardoor ;et ri#nummer van de volgenden in de reeks een ri#nummer overslaan$ S454CT rankKL .V4K.E4 0 plaatsL% plaats +./ spelers >H44 geslac;t = Z/Z
.E4 0 plaats N<55S 5:STP Hierbi# kri#gen allee spelers die op de "el6de plaats !onen ;et"el6de ranknummer$ ranknummer$
7$1$ 7$ 1$( ( Ee Eens nse eran rankK kKLL 2dem als rankKL maar de ri#nummers !orden intern niet ver;oogd !anneer er dubbels optreden$ Hierdoor onstaan er geen gaten in de :N)KL nummering$ 88c6 distinct aantal plaatsen S454CT denserankKL .V4K.E4 0 plaatsL% plaats +./ spelers >H44 geslac;t = Z/Z .E4 0 plaats N<55S 5:STP
7$ 7$ Qar arti titi tion oner eren en Vergeli#kbaar concept van ,.
H44 geslac;t = Z/Z .E4 0 P
7$$1 7$ $1 Qart artitie itiess sor sorter teren en >anneer !e een order by toevoegen in de .V4 component na een partitie dan !orden de elementen gesorteerd alvorens "e gepartitioneerd !orden$ S454CT ro!numberKL .V4Kpartition 0 plaats .E4 0 spelersnr ascL% plaats% spelersnr +./ spelers >H44 geslac;t = Z/Z .E4 0 P
7$$ :ggr :ggregati egatie e 6uncties 6uncties met met .V4KL .V4KL S454CT sumK#aartoeL .V4K.E4 0 #aartoeL% #aartoe +./ spelers
7$( 7$ ( .V4 V4KL KL VS , ,.< .
:ndere positie% volgorde van ver!erking Venster ?= groepering9 per groep ;eb #e OOn !aarde% per venster meerdere !aarden$
OVER() -
,ebruik venster 6uncties ,ebruik aggregatie6uncties aggregatie6uncties
GROUP BY -
,ebruik aggregatie6uncties aggregatie6uncties
88groepering S454CT spelersnr% sumKbedragL +./ boetes ,.
7$* 0ereik 88cumulatie6 op spelersnr de boetesommen geven S454CT spelersnr% sumKbedragL .V4Korder by spelersnr ro!s bet!een unbounded preceding and current ro!L +./ boetes between unbounded recedin! and current row: be;andel dubbele kolom!aarden kolom!aarden als een nieu!e ri# in de venster6unctie bi# ;et nemen van de cumulatieve som9 anders "ou de cumulatieve som niet correct genomen !orden omdat bi# ri#en met een"el6de kolom!aarde binnen ;et venster % bi# de eerste ri# onmiddelli#k de totale cumulatieve som !ordt opgeteld% en bi# de andere dubbele !aarden niks$
,.2TH .55anneer men de"e query uitvoert ui tvoert kri#gt men eerst ;et totaal bedrag van boetes per speler met vervolgen nog 1 ri# met ;et totaal bedrag aan boetes van alle spelers$ Volgorde van ver!erking 9 spelersnr =3 XY Spelersnr% plaats !it; rollup = 3 Xgeslac;t% plaatsY% Xgeslac;tY% XY
$ C<04 S454CT ro!numberKL over KL as volgnr% geslac;t% plaats% countKWL +./ spelers ,.2TH C<04 .E4 0 geslac;t% plaats 0i# de"e query kri#gt men eerst per geslac;t en plaats ;et aantal spelers% dan per geslac;t ;et aantal spelersKplaats is ;ier leegL% daarna per plaats ;et aantal spelersKgeslac;t spelersKgeslac;t is ;ier leegL en als laatste ;et totaal aantal spelers$ Volgorde van ver!erking9 Xgeslac;t%plaatsY % Xgeslac;tY% XplaatsY% XY
$( $ ( , ,.< .
/eer mogeli#k;eden
b"# b"# S454CT geslac;t% plaats% countKWL +./ spelers ,.
,.
b"# b"# S454CT geslac;t% plaats% countKWL
+./ spelers ,.
$* .5 .555 & C< C<0 04 •
Vereenvoudiging door .55
bv$ bv$ S454CT geslac;t% plaats% countKWL +./ spelers ,.
Vereenvoudiging door C<04
bv$ bv$ S454CT geslac;t% plaats% countKWL +./ spelers ,.
$7 Com ombi bin nat atie iess /eerdere groeperingen "i#n samen mogeli#k /ogeli#k;eden 9 •
1 grouping set U 1 simpele 9 toevoeging9 GROUP BY E1, GROUPING SETS((E2)) = GROUPING SETS((E2, E1))
•
• •
grouping sets 9 [ vermenigvuldiging vermenigvuldiging \ van specicaties Kbinnen OOn ,.
]
@ 5i 5imi miti ting ng res esul ultt set setss • • • •
4MQ5:2N 9 plaats bovenaan de query om uitvoerti#d etc$ te beki#ken$ +4TCH +2ST ^ .>S .N59 ;aal alleen de eerste ^ ri#en op Kna .E4 0L .++S4T9 plaats van!aar te starten Kna .E4 0% voor +4TCHL 52/2T !ordt niet gebruikt?
Vb$ S454CT spelersnr% naam% gebdatum +./ spelers .E4 by gebdatum .++S4T .>S +4TCH +2ST ( .>S .N5P = ;aalt ri# X(%7Y op
A Se Sett8o 8op perator ore en S4T8operatoren9 combineren van resultaten van individuele S454CT8instructies$
A$1
•
•
Ee versc;illende blokken moeten ;et"el6de aantal kolommen ;ebben en de kolommen kolommen die aan elkaar [ geplakt \ !orden% moeten ;et"el6de datatype ;ebben$ :lleen op ;et einde mag een .E4 0 voorkomen% de"e sorteert ;et eindresultaat$ S454CT moet geen E2ST2NCT bevatten Kdubbele ri#en !orden automatisc; ver!i#derdL
A$ 2N 2NT4 T4 S4 S4CT CT = alleen die ri#en die in de resultaten van de beide selectblokken selectblokken voorkomen Kdubbele ri#en !orden ver!i#derdL$ Vb#
,ee6 ;et spelersnummer van de spelers die aanvoerder "i#n en voor !ie minstens OOn boete is betaald S454CT spelersnr +./ teams 2NT4S4CT S454CT spelersnr +./ boetes
A$( 4MC4QT = alleen die ri#en die !el in ;et resultaat van ;et eerste select8blok voorkomen voorkomen maar niet in ;et resultaat van de t!eede select blokKdubbele ri#en !orden ver!i#derdL$ Vb$ ,ee6 ;et spelersnummer van de spelers voor !ie minstens OOn boete is betaald% maar die geen aanvoerder "i#n S454CT spelersnr +./ boetes 4MC4QT S454CT spelersnr +./ teams
A$* A$ * :5 :555 = be;o be;oud ud van van dub dubbe bels ls • •
Standaard !orden dubbele ri#en ver!i#derd ver!i#derd :558variant9 :558variant9 gebruiken om dubbele ri#en te be;ouden
Vb$ ,ee6 ;et spelersnummer van de spelers voor !ie minstens OOn boete is betaald% maar die geen aanvoerder "i#n$ 0e;oud dubbele ri#en$ S454CT spelersnr +./ boetes 4MC4QT 4MC4QT :55 :5 5 S454CT spelersnr +./ teams
A$7 N<55 i#en met een N<558!aarde !orden als geli#k besc;ou!d door de set8operatoren?
A$ Com ombi bin nat atie iess • •
/eerdere set8operatoren Haak#es kunnen de volgorde !i#"igen
Vb$,ee6 ;et spelersnummer van de spelers voor !ie minstens OOn boete is betaald% maar die geen aanvoerder "i#n% en daarenboven de spelers uit Hove$ KS454CT spelersnr +./ boetes 4MC4QT S454CT spelersnr +./ teamsL H44 plaats = RHove’
B :5T4 ob# b#e ect B$1 >i >i#"i #"igen gen van tab tabels elstru tructu ctuur ur •
:5T4 T:0549 !i#"igt structuur
Vb. Karakterset veraneren !"nvert t" !#ara!ter set $t%& !"''ate $t%&enera'!* Vb. Rena+e tab'e REN-E TBE s/e'ers TO tenn*ssers O% TER TBE s/e'ers REN-E TO tenn*ssers
B$ B$ >i# i#"i "ige gen n van kolo kolomm mmen en •
:5T4 T:0549 !i#"igt de structuur - :EE9 kolom toevoegen - E.Q ver!i#dert een kolom - :5T49 verandert de kolomeigensc;appen
Vb$ :lter table spelers alter plaats varc;arK7L not null
B$( >i >i#"i #"igen gen van integ integrit riteit eitsr srege egels ls •
:5T4 T:0549 !i#"igt de structuur - :EE C.NST:2NT9 voegt integriteitsregel toe - E.Q C.NST:2NT9 ver!i#derd integriteitsregel
Vb$ +oreign key
add constraint +) 6oreign key KspelersnrL re6erences spelers KspelersnrL Vb$ Qrimary key ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey MyPrimaryKey PRIMARY PRIMARY KEY (colmn!" colmn!" colmn#$$$%& colmn#$$$%&
Vb$ C;eck constraint ALTER TABLE table_name ADD CONSTRAINT My'nieCon)traint My'nieCon)traint C*ECK C*ECK (CONDITION%& CONDITION%&
Vb$
B$* B$ * >i# i#"i "ige gen n van van data databa base se •
:5T4 E:T:0:S4
Vb$ create database tennis de6ault c;aracter set s#is de6ault collate s#is#apaneseci
B$7 2nvoe 2nvoeren renDver! Dver!i#der i#deren en van gebr gebruik uikers ers •
•
•
C4:T4
B$ B$ 2n 2nse sert rt vo voor orbe beel eld d 2NS4T 2NT. tablex S454CT W +./ tableyP 2NS4T 2NT. teams V:5<4S Knull% % REerde’LP
1Common Table 4xpressions = vergeli#kbaar met subquery’s in t;e +./% maar met extra’s$
1$1 1 $1 ec ecur ursi sie e
O!east: .neindige lussen ,een controle door CT4’s Fel6 controles inbou!en?
1$ Strat StrategieG egieGn n voor oneind oneindige ige lus lus detectie detectie Teller Teller 5us detectie Ee"e "i#n combineerbaar?
• •
Otie$
Otie%
1$( 1 $( 2n 2nse sert rtss met CT4 CT4’s ’s
1$* 1 $* Ee Eele lete te met met CT4 CT4’s ’s
1$7 1 $7 Voor oorbe beel elde den n
U Ee"e gee6t voor elke elke naam in tabel spelers% een li#st van alle niet8"el6de namen die even lang "i#n terug$
Tussen Tussen resultaten resultaten !egsc;ri#ven !egsc;ri#ven naar debugtable debugtable om eventuele eventuele 6outen te vinden$ RO&(l#') = RO&(l#') = pak alles van de geselecteerde ri# Resultaat:
11M/5 • • • •
eMtensible /arkup 5anguage ETE Ko6 xml sc;emaL 4erste versie van xml standaard versc;een in 1BBA Hierac;isc;e structuren structuren in een nieu! kleed#e
• •
Qlatte tekstbestanden tekstbestanden 2S. standaard
11$1 11$ 1 4en 4envou voudig dige e M/5 M/5 synta syntax x • • • • •
,ebruik een tag om aan te geven !aar men begint en eindigt 4lk document moet in 1 root tag omvat "i#n 0eginnen met een ci#6er mag N24T? Case sensitive? Tags Tags mogen genest genest !orden
Vb$ Iexample3tekstIDexample3
11$ Eoel • • •
MmlKdataL vs ;tmlKvormL =3 Kxsl vs cssL .m "o!el ;iGrarc;isc;e datastructuren datastructuren te omsc;ri#ven als "e te bevatten .m data uit te !isselen tussen versc;illende gegevensbronnen gegevensbronnen
11$( 11 $( /4 /4T T: data data • • •
-e kan attributen attributen aan #e tags meegeven Kde"e Kde"e staan tussen tussen _ _ o6 R RL ,ebruik dit enkel om /4T: data !eer te geven Kbv eigensc;appenL Eata "el6 ;oort daar niet t;uis?
Vb Iexample type =’music’3 lalalala$mp(IDexample3
11$* 11$ *
•
Mml !ord bv ook gebruikt om conguraties van so6t!are bi# t;e ;ouden vb menubalk%J 4r "i#n versc;illende 6ormaten die van de"e standaard gebruik maken9 M;tml% xml dom% xsl% xslt% xpat;%xsl86o%xlink% xpointer% dtd% xsd% x6orms% etc$
11$7 11 $7 Qro vs Co Cont ntra ra 2S.
edundantie tov relationeel model Sequentieel Traag J
11$ E0/S •
Versc;illende E0/S voor"ien manieren om xml te gebruiken?
11$@ 11 $@ Voor oorbe beel elde den n S454CT xmlcommentKZ;alloZLP xmlcommentKZ;alloZLP xmlcomment 88888888888888 I?88;allo883 888888888888vb 88888888888888888 select xml6orestKdivisie% teamnrL 6rom teamsP xml6orest
Iteams3 Idivisie3ere Idivisie3ere IDdivisie3Iteamnr31IDteamnr3 IDdivisie3Iteamnr31IDteamnr3 Idivisie3t!eedeIDdivisie3Iteamnr3IDteamnr3 IDteams3
1Eatatypes
Numeric currency C;aracter 0inair time 0oolean J
1$1 :lgem :lgemen en versc;illen versc;illen voor teken8d teken8datatyp atatypes es C;arKnL 9 vaste lengte =3 plaats !ordt gereserveer gereserveerd% d% ook indien de"e plaats niet gebruikt !ordt Varc;arKnL9 variabele lengte% `exibele lengte met maximum =3 trager Tekst9 Tekst9 onbeperkte onbeperkte variabele lengte% meest `exibel =3 traagst
1$ 1$ Str String ingss vs 2den 2denti tier erss 4en String !ordt aangeduid d$m$v$ R R quotes$ 4en identier !ordt aangeduid d$m$v _ _ quotes$ Sql = standaard uppercase$ uppercase$
1(Creatie 1( Creatie en ont!erp ont!erp 1($1 1($ 1 2nv 2nvoer oeren en van nieu nieu!e !e ri#en ri#en *ER+ +O On ri# invoeren in een tabel Tabel Tabel vullen met ri#en ri#en van een andere andere tabel ,oand: 2NS4T ,oand: 2NS4T .escrition: create .escrition: create ne! ro!s in a table *ynta/: X >2TH X 4C<S2V4 Y !it;query X% $$$Y Y 2NS4T 2NT. tablename X K columnname X% $$$Y L Y E4+:<5T V:5<4S V:5<4S K expression E4+:<5T X% $$$Y L X% $$$Y query X 4T<N2N, W outputexpression X X :S Y outputname Y X% $$$Y Y
1($ Tabel vullen vullen vanuit vanuit andere andere tabel Tabel Tabel !ordt gevuld gevuld met ri#en ri#en uit een andere andere tabel Re!els: •
Eit mag de"el6de tabel "i#n 2n de tabelexpressie tabelexpressie mag alles staan "oals in i n een _ge!one tabelexpressie tabelexpressie
Vb#
:antal kolommen in *ER+ +RO moet +RO moet geli#k "i#n aan aantal expressies in *E0E,+ Ee datatypes van de kolommen moeten overeenkomen met de expressies in de *E0E,+ Create table recreanten Kspelersnr smallint not null% naam c;arK17L not null% plaats c;arK1L not null% primary keyKspelersnrL L 2nsert into recreanten Kspelersnr% Kspelersnr% naam% plaatsL select spelersnr% naam% plaats 6rom spelers !;ere bondsnr is null
1($( >i# >i#"igen "igen van !aar !aarden den in ri#en ri#en UP.1+E9 UP.1+E9 !aarde !orden ge!i#"igd Vb# U/ate 0estr*en set e0"nnen = 0#ere s/e'ersnr *n (se'e!t s/e'ersnr %r"+ s/e'ers 0#ere /'aats = 34en 5aa6)
1($* Ver!i# er!i#der deren en van ri#en ri#en uit uit een tabel .E0E+E: ri#en !orden ver!i#derd Vb# e'ete %r"+ s/e'ers 0#ere aart"e 7 (se'e!t av(aart"e) %r"+ s/e'ers)
1($7 .p"e .p"etten tten van nieu! nieu!e e tabelle tabellen n ,RE1+E +1B0E: creGert +1B0E: creGert lege tabel Vb 8reate tab'e SPEERS ( s/e'ersnr s+a''*nt n"t n$'', naa+ !#ar(19) n"t n$'',
v""r'etters !#ar(:) n"t n$'', ebat$+ ate , ; /r*+ar< ke< (s/e'ersnr) (s/e'ersnr) )
+abelschea: tabelKtabelnaamL +abelschea: tabelKtabelnaamL 2olode3nitie: kolomnaam U datatype U null8specicatieUintegr null8specicatieUintegriteitsrege iteitsregell
1($ 1($ Eat Eatatyp atypes es van van kolom kolommen men nte!er: ge;ele getallen - tinyint - smallint - integerKintL .ecial: niet8ge;ele .ecial: niet8ge;ele getallen - decimalK % L KdecL = numeric 4loat: ;eel groteDkleine getallen - single precision - double precision 1l5anuerie6e: karakter 1l5anuerie6e: karakter - c;arac;terKc;arL - varc;ar - long varc;ar +eoral 9 datum en ti#d - Eate - Time - Timestamp Timestamp Boolean 9 Boolean 9 true D 6alse Blob Kbasic Blob Kbasic large ob#ectL
1($@ CreGr CreGren en van ti#deli#ke tabellen +i7deli76e tabellen: 8reate te+/"rar< tab'e SO-BOETES ( t"taa' e!*+a'(1,2)) Insert *nt" s"+b"etes se'e!t s$+(bera) %r"+ b"etes
1($A 1($ A )opiG opiGre ren n van tabel tabellen len !reate tab'e tea+sk"/*e2 as (se'e!t %r"+ tea+s)
1($B Naamge Naamgeving ving tabel tabellen8k len8kolomme olommen n Beer6in! in de 6eu8e:
• • • • • •
tabellen uit ;et"el6de databasesc;ema databasesc;ema 9 versc;illende namen kolommen in OOn tabel 9 versc;illende namen lengte van naam is beperkt alleen letters% ci#6ers en enkele symbolen beginnen met een letter geen gereserveerde !oorden Foniet !erken met identiers _
aa6eu8e is 8eer belan!ri7699999999999999999999999999999999999999999999999999999999999999999999999999999 9 • • • • • • • •
korte namen Kniet cryptisc;L tabelnaam in meervoud geen [ in6ormatiedragende \ namen consistente naamgeving geen te lange namen kolommen met een"el6de betekenis% de"el6de naam )olommen )olommen met vergeli#kbare vergeli#kbare populaties% de"el6de naam geen termen uit ;et besturingssysteem besturingssysteem
1($1
kolom8opties9 de de6ault ' comment
Ee6ault!aarde Ee6ault!aarde als laatste bi# kolomdenitie$ kolomdenitie$ 8reate tab'e b"etes (beta'*nsnr *nteer n"t n$'' /r*+ar< ke<, ke<, s/e'ersnr *nteer n"t n$'', at$+ ate n"t n$'' e%a$'t 31>>?1?16, bera e!*+a'(@,2) n"t n$'' e%a$'t 9.)A
1($11
tabellen en de catalogus
% catalo!ustabellen:
1($1 •
speci icceren van de integriteit itsr sre egels
consistentie
•
correct;eid
1($1(
integriteit bi# primaire sleutel
kolom !aarvan de !aarde uniek moet "i#n Knooit nullL samengestelde primary key Re!els: maximaal OOn primaire sleutel per tabel uniciteitsregel uniciteitsregel 9 !aarde is uniek minimaliteitsregel minimaliteitsregel 9 geen overbodige kolom kolomnaam 9 1 maal in primaire sleutel 2ntegriteitsregel 2ntegriteitsregel 9 kolom mag geen N<55 "i#n • •
• • • • •
Raad: minimaal OOn primaire sleutel per tabel
1($1*
2ntegriteit bi bi# al alternatieve sl sleutels )olom !aarvan de !aarde uniek is% alternatie6 voor primary key /ag niet null "i#n 2n S59 na de kolom% m$b$v$
• • •
1($17
e6ererende sleutels
)olom die ver!i#st naar primaire sleutel uit een andere tabel 2n S59 4OREG 2EY(6olo) RE4ERE,E* tabel(6olo) Re!els: ,ere6ereerde tabel 9 bestaan o6 gecreGerd !orden ,ere6ereerde tabel 9 een primaire sleutel bevatten ,ere6ereerde tabel 9 )olomnaam )olomnaam aanduiden ,een kolomnaam aanduiden K"el6de kolomnaam ?L e6ererende sleutel mag N<55 "i#n :antal kolommen moet geli#k "i#n Eatatypes van de kolommen kolommen moeten geli#k "i#n er6 o: e6ererende sleutel mag uit meerdere kolommen bestaan 4en kolom mag deel "i#n van versc;illende re6ererende sleutels 4en deel Ko6 alleL !aarden van een primaire sleutel mag een re6ererende sleutel "i#n e6ererende tabel mag = gere6ereerde tabel • •
• • •
• • •
• • •
•
1($1
Ee re6ererende actie
0i# UP.1+E & .E0E+E: • • •
4ST2CTKde5ault) 4ST2CTKde5ault) 9 9 verboden C:SC:E4 9 doorgetrokken naar de re6ererende tabel S4T N<55 9 N<55 !aardes in de re6ererende tabel$
1($1@
C;eck8integriteitsregels
= geven aan !elke !elke !aardes toegelaten "i#n S59 ,;E,2( ) es'a!#t !#ar(1) n"t n$'' !#e!k (es'a!#t *n (3-6, 3V6)),
1($1A
Namen aan integriteitsregels
0i# 6oute insturctie9 6outboodsc;ap =3 voor duideli#k;eid gee6t men een naam aan integriteitsregel$ integriteitsregel$ 8reate tab'e s/e'ers (s/e'ersnr *nteer n"t n$'' !"nstra*nt /r*+a*res'e$te's/e'ers /r*+a*res'e$te's/e'ers /r*+ar< ke<, ke<, ; es'a!#t !#ar(1) n"t n$'' !"nstra*nt toegestane_waarden_geslacht !#e!k (es'a!#t *n (3-6, 3V6)), ;
1($1B
2ntegriteitsregels vo voor ve ver!i#deren
.ROP +1B0E: Qrimaire% re6ererende en alternatieve sleutels verd!i#nen e6ererende sleutels !aarvan de tabel de gere6ereerde tabel !as% verd!i#nen 10+ER +1B0E: Ver!i#deren van integriteitsregels a6"onderli#k • •
1($ • •
2ntegriteitsregels en de catalogus Vastleggen van de integriteitsregels integriteitsregels gebeurt in de catalogustabellen :6;ankeli#k van ;et product
1($1
Voo oorrbeelden kol olom om88 & tabel beperkingen
0#ere !"'$+n!"nstra*nt *s C 8ONSTRINT !"nstra*ntna+e D NOT NU F NU F 85E8K ( e/ress*"n ) C NO IN5ERIT D F 4EHUT 4EHUT e%a $'te/r F UNIUE *ne/ara+eters F PRI-RY KEY *ne/ara+eters F REHEREN8ES re%tab'e C ( re%! "'$+n ) D C -T85 HU F -T85
PRTI F -T85 SI-PE D C ON 4EETE a!t*"n D C ON UP4TE a!t*"n D J C 4EHERRBE F NOT 4EHERRBE D C INITIY INITIY 4EHERRE4 F INITIY I--E4ITE D
?????????????????????????tabe'?????????????????????????????????????????????? an tab'e!"nstra*nt *s C 8ONSTRINT !"nstra*ntna+e D 85E8K ( e/ress*"n ) C NO IN5ERIT D F UNIUE ( !"'$+nna+e C, ... D ) *ne/ara+eters F PRI-RY KEY ( !"'$+nna+e C, ... D ) *ne/ara+eters F E8U4E C USING *ne+et#" D ( e!'$ee'e+ent LIT5 "/erat"r C, ... D ) *ne/ara+eters C L5ERE ( /re*!ate ) D F HOREIGN KEY ( !"'$+nna+e C, ... D ) REHEREN8ES re%tab'e C ( re%!"'$+n C, ... D ) D C -T85 HU F -T85 PRTI F -T85 SI-PE D C ON 4EETE a!t*"n D C ON UP4TE a!t*"n D J C 4EHERRBE F NOT 4EHERRBE D C INITIY INITIY 4EHERRE4 F INITIY I--E4ITE D
1*Serial datatype = ,44N ec;te sleutel? Vb$ C4:T4 T:054 serialdemoK id serial Q2/: )4% content text LP Serial creGert extra ob#ect een sequence? Forg dat gebruiker ook rec;ten ;ee6t voor die sequence via ,:NT? *equence 4en sequence is een databaseob#ect !aarmee !e oplopende o6 a`opende% unieke o6 niet unieke% cyclisc;e cyclisc; e o6 niet8cyclisc;e niet8cycl isc;e reeksen met nummers kunnen genereren$ Vb$ ,:NT
:55 S4<4NC4S 2N SCH4/: sc;emaname X% $$$Y T. X ,.2TH ,:NT ,:NT .QT2.N Y
17-E0C 17$1 17 $1 >; >;at at is -E -E0 0Cf •
•
Ee rite once% run any!;ere capaciteiten te exploiteren voor applicaties die toegang nodig ;ebben tot entreprise data$ /et een -E0C tec;nologie toegelaten driver kan #e verbinden met alle bedri#6sdata% "el6s in een ;eterogene omgeving$
17$ SS5 •
,ebruik maken van een -E0C connectie string met een niet gevalideerd ssl certicaat9 _#dbc9postgresql9DDserverDdatabasef user=me&pass!ord=mypass!ord&ssl=true&ssl6actory=org$postgresql$ssl$ NonValidating+actory
1 Sc;ema% rig;ts% locale 1$ $1 1 Sc Sc; ;ema • • •
•
Sc;ema = namespace 0innen OOn namespace moeten alle ob#ecten een unieke naam ;ebben 0i# postgreS5 is de de6ault namespace _public Kc;ecken met 9 3 SH.> searc;pat;PL 0i# sommige andere producten de6ault namespace = username
1$ 1$ ig ig;ts ;tsKst Kstand andar ardL dL • •
ec;ten op parentob#ect nodig om rec;ten op c;ild te kunnen uitvoeren Ee6ault9 alleen eigenaar ;ee6t rec;ten
1$$1
ec;ten toekennen
,:NT Iprivilege3 .N Iob#ecttype3 Iob#ectname3 T. Irole3P Vb$ ,RANT SELECT ON mytable TO P'BLIC& ,RANT SELECT" 'PDATE" INSERT ON mytable TO a-min& ,RANT SELECT (col!%" 'PDATE (col!% ON mytable TO miriam_r.&
1$$
ec;ten a6 a6nemen
4V.)4 Iprivilege3 .N Iob#ecttype3 Iob#ectname3 +./ Irole3P
1$( 1$ ( Qr Qrivi ivileg leges es Kpassa Kpassable bleLL = ec;ten geven om rec;ten door te geven ,:NT Iprivilege3 .N Iob#ecttype3 Iob#ectname3 T. Irole3 >2TH ,:NT .QT2.NP
1$* 1 $* 5oca ocalis lisat atie ie Vb1$ • •
C;aracter sets9 or or not% h or not Collating sequences9 abcJ !"''at*n seM$en!e (a's" !a''e a s"rt seM$en!e) enes enes #"0 !#ara!ters *n a !#ara!ter set re'ate t" ea!# "t#er 0#en t#e< are !"+/are an "rere. 4*erent !"''at*n seM$en!es are $se%$' %"r t#"se 0#" 0ant t#e*r ata "rere %"r a s/e!*! 'an$ae. H"r ea+/'e, '*sts !an be "rere as t#e< are n"r+a''< seen %"r a s/e!*! 'an$ae. !" ''at*n seM$en!e !an a's" be $se t" treat !erta*n !#ara!ters as eM$*va'ent, %"r *nstan!e, a an .
•
4ncoding9
Vb $ • • • • • •
5CC.55:T49 string order 5CCTQ49 c;aracter classication 5C/4SS:,4S9 5C/4SS:,4S9 language o6 t;e message 5C/.N4T:9 6ormat currency 5CN 5CC.55:T4P
[email protected] en native 1@$1 1@ $1 >at is .E0 .E0Cf Cf Open .ataB ataBase ,onnectivity K.E0CL K.E0CL is een manier om op een gestandaardiseerde gestandaardiseerde manier toegang te geven aan een database% ona6;ankeli#k van ;et type applicatie en van ;et type database$ Fonder .E0C9
/et .E0C9
1@$ 1@ $ .E .E0 0C o6 nat nativ ivef ef Eit ;angt a6 van9 •
•
•
•
>elke drivers "i#n er besc;ikbaarfKClient en ServerL :6;ankeli#k van taal$ Fi#n er prestatie o6 stabiliteit versc;illenf Native bli#6t sneller% .E0C beter !anneer databases kunnen !orden ge!i#"igd$ >at is ;et versc;il met #dbcf .E0C is enkel voor /icroso6t8applicaties en -0EC enkel voor -ava8 applicaties$ .E0C kan niet gebruikt !orden met -ava omdat .E0C onderliggend een C inter6ace voor"iet$ >at is ;et versc;il met pearf Qear KQHQ extention and application repositoryL ’s Qear E0 let you access databases in p;p 6or !;ic; an obdc driver is available$
1AConnection pooling 1A$1 1A$ 1 >at is connec connectio tion n pooling poolingff 2n so6t!are ont!ikkeling is een connection ool een ool een cac;e van databank connecties onder;ouden door de databank "o dat de connecties kunnen !orden !orden ;erbruikt !anneer de databank een toekomstig ver"oek voor data ontvangt$ .pmerking9 men kan ook aan client8side pooling doen$
"onder pooling9
met pooling9
1A$ 1A$ >aar aarom om poo poolin lingf gf Qooling "orgt voor een boost in opstart prestatie Kelke connectie ;ee6t een opstart kostL Nadeel9 !anneer connecties niet gebruikt !orden% gebruiken "e toc; Keen deelt#eL ge;eugen$
1B2ndexen • • • •
Eoel9 benvloeden van de ver!erkingsti#d? ver!erkingsti#d? i#en !orden in bestanden opgeslagen op ;et .S 4en bestand bestaat uit pagina’s :ls een ri# opge;aald !ordt9 - Ee betrejende pagina !ordt opge;aald - Ee betrejende ri# !ordt opge;aald
1B$1 1B$ 1 >erk erking ing van van een een index index 4r "i#n met;odes voor ;et op"oeken9 op"oeken9 SequentiGle "oekmet;ode9 "oekmet;ode9 ri# per ri# - Ti#drovend Ti#drovend en ineciGnt ,endexeerde "oekmet;ode9 index K08treeL - 4en index in S5 is opge"et als een Boo - Ee"e boom bestaat dan uit een aantal 2noounten aantal 2noounten KnodesL - 4en knooppunt dat naar een ri# ver!i#st = 0ea5a!e - met;odes9 Foeken van ri#en met een bepaalde% ge!enste !aarde Eoorlopen van de ge;ele tabel via een gesorteerde gesorteerde kolomKgeclusterde indexL &aaro !ebrui6 a6en "an een !eclusterde inde/= >anneer men bv geordend op spelersnr spelers !ilt opvragen gaan de"e spelers gesorteerd van de sc;i#6 ge;aald moeten !orden$ Eit kan ervoor "orgen dat een pagina meerdere meerdere malen van de sc;i#6 gele"en moet !orden$Klees !orden$Klees en beki#k tekening tekening index boek pg *B78*B@L$ .m dit probleem te ver;elpen maakt men gebruik van geclusterde indexen$ indexen$ Ee"e bepaald de volgorde van de ri#en in ;et bestand !aarin "e opgeslagen staan$ Hierdoor !ordt elke pagina maximaal 1 x opge;aald$ •
•
Oer6in!en: 0i# aanpassing in tabel9 index aangepast 2ndex9 ook op niet8unieke kolom .p OOn tabel9 meerder indexen .p OOn tabel9 OOn geclusterde index • • • •
Samengestelde Samengestelde index O!elet: 2ndex neemt opslagruimte in :ls index vol9 reorganisatie van de index •
• •
/eerde indexvormen "i#n mogeli#k?
1B$ 1B $ .p .pti timi mise serr Voor elke ingevoerde query probeert S5 de meest eciGnte strategie voor uitvoering te bepalen$ Ee"e analyse% ook !el query optimalisation genoemd% !ordt uitgevoerd uitgevoerd door een module binnen S5% genaamd otiiser$ otiiser$ Ee"e ont!ikkelt ont!ikkelt voor elke instructie een aantal alternatieve strategieGn strategieGn voor uitvoering$ Eit doet ;i# op basis van9 • • •
Ver!ac;te ver!erkingsti#d :antal ri#en 2ndexen
1B$( 1B $( Cr Crea eate te in inde dex x • • •
• • •
No :NS2 o6 2S. specication CREATE / 'NI+'E 0 INDE1 / CONC'RRENTLY 0 / name 0 name 0 ON table / table / 'SIN, method 0 0 ( 2 column 3 column 3 ( expression % expression % 4 / COLLATE collation 0 collation 0 / opclass 0 opclass 0 / ASC 3 DESC 0 / N'LLS 2 5IRST 3 LAST 4 0 /" $$$0 % / 6IT* ( storage_parameter storage_parameter 7 7 value /" value /" $$$ 0 % 0 / TABLESPACE tablespace 0 tablespace 0 / 6*ERE predicate 0 predicate 0
Qostgresql Qostgresql boek pagina *B(9 ,re>ren "an een inde/ 8RETE IN4E s/e'ers/"st!"e* ON s/e'ers (/"st!"e as!)A
,re>ren "an een unie6e inde/ 8RETE UNIUE IN4E s/e'ersnaa+v'* ON s/e'ers (naa+, v""r'etters)A
,re>ren "an een saen!estelde inde/ 8RETE 8RETE IN4E s/e'ersnaa+v'/art*a'* ON s/e'ers (naa+, v""r'etters) L5ERE s/e'ersnr 1A
,re>ren "an een !eclusterde inde/ 8RETE UNIUE 8USTERE4 IN4E s/e'ers!'$stere ON SPEERS(s/e'ersnr) SPEERS(s/e'ersnr)
1B$* 1B $* ei eind nde ex
• • •
42NE4M 2NE4M eenindexP 42NE4M T:054 eentabelP 42NE4M E:T:0:S4 eendatabaseP
1B$7 1B$ 7 2nd 2ndex ex man manage agemen mentt •
C4:T4 8RETE IN4E *nenaa+7
•
:5T4 TER TER IN4E r""t* SET TBESP8E erensaners
•
E.Q 4ROP IN4E *nenaa+7
Vele S58producten9 :utomatisc;e creatie van index op primaire en alternatieve sleutels bi# creatie van tabel Naam !ordt a6geleid uit naam van de tabel en de betrejende kolommen kolommen •
•
1B$ 1B$ >at inde indexe xere renf nf • •
Voordeel: index Voordeel: index versnelt ver!erking adeel: index adeel: index neemt opslagruimte in en elke mutatie vraagt aanpassing van index K= vertraagde ver!erkingL ver!erkingL
1B$@ 1B$ @ >elk elke e kolo kolomme mmenf nf Richtli7nen voor Richtli7nen voor keu"e van kolommen9 •
•
•
•
•
H448component een :NE bevat 2ndex op kolommen !aarop gesorteerd !ordt >anneer er gesorteerd moet !orden maar geen index op die kolom% is er een extra sorteerslag nodig$
1B$A 1B $A Sp Spec ecial iale e vorme vormen n ulti-tabelinde/ (7oin-inde/en) : = index op kolommen in meerdere tabellen Vb. 8RETE 8RETE IN4E s/e'0es ON SPEERS(s/e'ersnr), LE4STRIQ4EN(s/e'ersnr)
Voordeel: !anneer Voordeel: !anneer tabellen met een #oin # oin op spelersnr gekoppeld !orden% !orden% !ordt de"e #oin "eer snel ver!erkt$
Virtuele-6oloinde/ : = index op een expressie op voor!aarde dat de expressie geen aggregatie6uncties aggregatie6uncties o6 subquery’s bevat$ Vb. 8RETE 8RETE IN4E 0e#a'vesa'" ON LE4STRIQ4EN(e0"nnen?ver'"ren2)
Voordeel: verbetering Voordeel: verbetering van de ver!erkingssnel;eid ver!erkingssnel;eid van instructies !aarbi# desbetrejende desbetrejende expressie expressie in i n de >H448component !ordt gebruikt$ K>H44 ge!onne8verlorenDL$ *electie"e inde/: = index op een gedeelte van de ri#en$ uote pg 71*9 Ste' $ v""r at e LE4STRIQ4EN?tabe' n +*'"en r*en bevat. En ste' at e +eeste ebr$*kers v""rna+e'*k eevens van e 'aatste 2 aar enteresseer *n, 0at neerk"+t "/ 2. r*en. ' #$n vraen bevatten een !"n*t*e 0aar e at$+ n*et "$er *s an t0ee aar. -aar er *n enke'e ebr$*kers v""r 0*e e anere &. r*en 0e' n"* *n. V""r *neen e't #"e +eer r*en, #"e r"ter e *neb""+ 0"rt, en #"e 'ana+er e ver0erk*n 0"rt. 4*t kan +en v""rk"+en +et se'e!t*eve *neen.W Vb. 8RETE 8RETE IN4E b"etesat$+ ON BOETES L5ERE 4TU- 7 31>>X?12?:16
Voordeel: snellere ver!erking bi# ;et !erken met gedeelte van tabellen$ ;ash-inde/: = index op basis van ;et adres op de pagina maar er !ordt geen indexboom gecreGerd$ Ee"e index moet gemaakt !orden voor de tabel gevuld !ordt$ >anneer een ;as;8index !ordt gecreGerd% !ordt er automatisc; een bepaalde ruimte op de sc;i#6 gereserveerd$ Ee"e ruimte "al gebruikt !orden voor ;et opslagen van ri#en$ Vb. 8RETE 8RETE 5S5 IN4E I N4E s/e'ersnr#as# ON SPEERS(s/e'ersnr) LIT5 PGES = 1
Voordeel: bi# een 08tree Kvorige ( items "i#n ;ierop gebaseerdL moet men eerst de ;ele indexboom doorlopen voordat men bi# de !erkeli#ke ri# uitkomt$ 0i# de ;as;8index springen !e vri#!el meteen naar de ri# toe$ Bitainde/: = interessant als er veel dubbele !aardes "i#n$ Vb.
8RETE 8RETE BIT-P IN4E s/e'erses'a!#t ON SPEERS(es'a!#t)
,lustered inde/: een index !aarbi# een kolom de 6ysieke volgorde van de records records op ;et opslagmedium bepaalt$
1B$B 1B$ B Nie Nieu!e u!ere re dat dataty atypes pes • • •
,iST K,enerali"ed Searc; TreeL SQ8,iST Kspace8partitioned ,iSTL ,2N K,enerali"ed 2nverted 2ndexL
= pgindex nde/ catalo!?table in ost!resql ost!resql = ;ttp9DD!!!$postgresql$o ;ttp9DD!!!$postgresql$orgDdocsDB$1DstaticDcatalog8pg8inde rgDdocsDB$1DstaticDcatalog8pg8index$;tml x$;tml ;ttp9DD!!!$postgresql$orgDdocsDcurrentDstaticDinternals$;tml
.ptimaliser .ptimaliseren en van insturc insturcties ties $1 Vermi ermi#den #den van . .8operat 8operator or OR: index OR: index !ordt meestal niet gebruikt 1lternatie5: 2N o6 x select U
$ $ .nn .nnodi odig g gebru gebruik ik van unio union n UO: de"el6de tabel meerdere malen doorlopen 1lternatie5: ;er6ormuleren ;er6ormuleren !aarbi# alle voor!aarden in 1 select instructie staan$
$( Vermi ermi#d #d N.T N.T8operat 8operator or O+: index O+: index !ordt niet gebruikt 1lternatie5: vervang 1lternatie5: vervang N.T door vergeli#kingsoperatoren vergeli#kingsoperatoren
$* 2solee 2soleerr kol kolommen ommen in condi condities ties 2olo in een bere6enin! o5 in een scalaire 5unctie: index !ordt niet gebruikt$ 1lternatie5: isoleer de kolom
$7 $ 7 ,eb ,ebrui ruikk de 04T>44N8o 04T>44N8oper perator ator 1.: gebruikt de index meestal niet
$ 0epaa 0epaalde lde vorm vormen en van 52)48op 52)48operato eratorr 02E: index !ordt niet gebruikt als patroon begint met o6 52)4 R^’ 4SC:Q4 R^’ 52)4 R^’ 1lternatie5: ,44N
$@ edun edundante dante condit condities ies bi# bi# #oins #oins Redundante condities: om S5 te verplic;ten om een bepaald pad te kei"en
$A Vermi ermi#d #d de de Having8 Having8compon component ent ;1VG: index niet gebruikt 1lternatie5: "oveel mogenli#k in >H44
$B $ B S45 S454CT 4CT8co 8compo mponen nent9 t9 sma small *E0E,+-coonent "o klein mogeli#k • •
$1
.nnodige kolommen !eglaten uit *E0E,+ 0i# gecorreleerde subquery met exists9 OOn expressie bestaande uit OOn constante
Vermi#d E2ST2NCT
ver!erkingsti#d verlengt .*+,+: ver!erkingsti#d 1lternatie5: vermi#den vermi#den als ;et overbodig is
$11
:558optie bi bi# se set op operatoren
@onder 100: ver!erkingsti#d ver!erkingsti#d verlengt% data moet gesorteerd !orden om dubbels eruit te ;alen VB: ipv
$1
)ies outer8#oins boven
UO: ver!erkingsti#d ver!erkingsti#d verlengt 1lternatie5: outer8#oin is beter
$1(
Vermi#d datatype8conversies
,on"erteren "an datatyes: ver!erkingsti#d ver!erkingsti#d verlengt 1lterntie5: conversie datatypes vermi#den
$1*
,rootste tabel als laatste
Vol!orde "an tabellen 6an belan!ri76 8i7n9 1lteratie5: grootste tabel als laatste plaatsen
$17
Vermi#d :N8 en :558 operatoren
1Y en en 100: index !ordt niet gebruikt 1lternatie5: geen
14MQ5:2N • • •
4xplain = gee6t in6ormatie over kostpri#s% kostpri#s% etc$ van query 4xplain :naly"e = naast kostpri#s ook ec;te uitvoeringsti#d C4:T4 2NE4M .N T:045K).5./N::/L = index creGren
S4T enableseqscan T. oj = sequentieel "oeken uit"etten =3 "oeken op index
Vie!s $1 $1 C C4: 4:T4 T4 V2 V24> 4> •
•
•
Vie!9 Vie!9 tabel die "ic;tbaar is voor de gebruiker maar geen opslagruimte inneemt$ Ee in;oud van een vie! !ordt a6geleid bi# ;et gebruik van een S454CT vie! 4en vie! kan dus maar opgebou!d !orden op basis van gegevens die in andere tabellen opgeslagen "itten
$ Nutf • • • •
Vereenvoudingen van routinematige instructies eorganiseren van tabellen Stapsge!i#s op"etten van S454CT8instructies S454CT8instructies 0eveiligen van gegevens
View: •
0evat geen ri#en?
•
Voorsc;ri6t o6 6ormule om gegevens uit basistabellen in een Rvirtuele’ tabel te steken
$( $ ( Cr CreGr eGren en van vie vie!s !s • •
C4:T4 V24>9 creGert een vie! Vie!s9 raadplegen en muteren U synoniemen en commentaar?
$* $ * )olom olomnam namen en van van vie!s vie!s *E0E,+ denieert *E0E,+ denieert de kolomnamen% maar expliciete denitie is ook mogeli#k?
4xpliciete denitie is verplic;t als kolom bestaat uit een 6unctie o6 berekening$ berekening$
$7 $ 7 >i >it; t; c;e c;eck ck opt option ion /uteren van vie!s =3 muteren van tabellen &+; ,;E,2 OP+O controleert9 OP+O controleert9 • • •
$ $ Ver! er!i#d i#der eren en van vie!s vie!s gedenieerde vie!s .ROP VE&: ver!i#dert vie! en alle ;ierop gedenieerde
$@ $ @ 0e 0eper perkin kingen gen bi# muter muteren en *E0E,+A *ER+A UP.1+EA .E0E+E van vie!s /aar mutatie mag alleen als9 • • • • • • • • •
Vie! moet directDindirect gebaseerd "i#n op OOn o6 meerdere basistabellen Select mag geen distinct bevatten Select mag geen aggregatie6unctie bevatten +./ mag slec;ts OOn tabel bevatten Select mag geen ,.
$A Ver!e er!erk rken en van instruc instructies ties op vie!s vie!s 4xtra stap !aarin vie!6ormule vie!6ormule !ordt opgenomen
•
Kc;eck8optionL
$B Toepassi oepassingen ngen van vie!s Vereenvoudigen van routinematige instructies 2nstructies die vaak gebruikt !orden eorganisatie eorganisatie van tabellen 0i# aanpassingen _oude programma’s laten bestaan Stapge!i#s op"etten van select8instructies 0i# complexe queries stukken _voorprogrammeren Speciceren Speciceren van integriteitsregels integriteitsregels >it; c;eck option9 toegestane !aarden controleren controleren ,egevensbeveiliging 0eveiligen van delen van tabellen
•
•
•
•
•
$1
0eveiliging
0eveiligen? S5 gebruiker9 /oet gekend "i#n >ac;t!oord 4xpliciete toekenning van bevoegd;eden )olombevoeg;eden Tabelbevoeg;eden Tabelbevoeg;eden Eatabasebevoegd;eden ,ebruikersbevoegd;eden
• • •
$11
2nvoerenDver!i#den van gebruikers
,RE1+E ,RE1+E U*ER: U *ER: creGert een user Vb$ Create user +rank identied by +rankp! 10+ER U*ER: verandert ;et pas!oord Vb$ :lter user +rank indentied by +rankp! .ROP U*ER: ver!i#dert een user Vb$ Erop user +rank
$1
Tabel8 en en ko kolombevoegd;eden
GR1+: kent bevoegd;eden toe *oorten tabelbe"oe!heden: *E0E,+: bevoeg;eid *E0E,+: bevoeg;eid tot select en vie! *ER+9 *ER+9 ri#en toevoegen .E0E+E9 .E0E+E9 ri#en ver!i#deren ver!i#deren UP.1+E: ri#en UP.1+E: ri#en !i#"igen RE4ERE,E*: re6ererende sleutels naar de"e tabel creGren 10+ER: tabel 10+ER: tabel veranderen .E: indexen creGren KtabelL 100-100 PRV0EGE*: alle bevoegd;eden
$1(
Eatabasebevoegd;eden 2dem vorige U .ROP: tabellen ver!i#deren ver!i#deren ,RE1+E +EPOR1RY +1B0E*: ,RE1+E ,RE1+E VE& V E& ,RE1+E ROU+E: nieu!e stored procedureD6unctie 10+ER ROU+E9 ROU+E9 EE,U+E ROU+E 0O,2 +1B0E*: bestaande tabellen blokkeren • • • • • • •
$1*
,ebruikersbevoegd;eden
= databasebevoeg;eden toekennen aan gebruiker ,RE1+E ,RE1+E U*ER: U *ER: gebruiker gebruiker aanmaken
$17
>it; grant option
= de gebruikers die toegang kri#gen% kunnen de"e mac;tiging doorgeven aan andere gebruikers
$1
>erken met rollen
,RE1+E ,RE1+E RO0E: creGert een rol met bevoegd;eden bevoegd;eden voor een aantal users% als de rol verandert% veranderen de bevoegd;eden voor al de betrokken users$
.ROP RO0E: ver!i#dert de rol
$1@
2ntrekken van bevoeg;eden
REVO2E: veri#dert bevoegd;eid Ken de a6;ankeli#ke bevoegd;edenL
$1A • •
0eveiliging van en met vie!s
:naloog voor bevoegd;eden op vie!s )an gebruikt !orden voor de beveiliging van gegevens ,ebruiker kri#gt enkel enkel mac;tiging op een vie!% !aarin een deel van de tabel gedenieerd gedenieerd !ordt
(0eveiliging • • • • • • • •
Hard!are Het plat6orm !aar ;et op draait Ee databank so6t!are 0innen sql "el6 Kgrant% revoke% vie!%$$L Sql als _vertaler _vertaler :lgemeen terugkerende security problematiek K"ie (ti% bv aut;enticatie% maar ook backups% backups% $$L $$
($1 ($ 1 .nd .nders ersteu teune nend nd plat6or plat6orm m • • •
($ ($ Eat Eataba abank nk so6 so6t!a t!are re • •
•
at doe #ef
($( ( $( >it it;i ;in n S5 S5 • • • •
($* S5 • • • •
S5 !ordt niet gecompileerd S5 !ordt _vertaald in de onderliggendeDomsluite onderliggendeDomsluitende nde laag Eit kan gebruikt !orden om sql onge!enste instructies te laten uitvoeren Het is probleem dat bi# elke taal die _vertaald% terugkomt Keg p;pL
($7 ( $7 S S55 2n# 2n#ec ecti tion on •
• •
2ndien !e de onderliggende sql code van bv een 6ormulier niet kennen% dan gaan !e proberen te raden !at de sql code is die erac;ter "it$ 2pv ge!one !aarden gaan sql code meegeven met ;et 6ormulier$ >atc; out !it; serial% identity% auto increment increment
($ 2ncor 2ncorrec rectly tly ltered ltered escape c;arac c;aracters ters •
+out9 input is niet gelterd op escape c;aracters
($@ ($ @ 2nc 2ncor orre rect ct type type ;andlin ;andling g •
+out9 types van de input i nput !orden niet gec;eckt
($A ( $A .p .plo lossi ssing ngen en • • • •
4scape c;aracter ver!i#deren uit de invoervelden Controleren o6 ;et invoerveld !el ;et #uiste type ;ee6t Qrepared statements Stored procedures - Type Type - P en escaping - ,rants$$ - Eic;t bi# de bron - $$
($B ( $B .p .pge gepa past st •
•
4nkel 4nkel 4scaping is niet voldoende9
Eus "eg eerder !at !el mag "i#n als invoer% ipv !at niet magP ;et eerste is eenvoudiger% er "i#n KmeestalL maar een eindig aantal mogeli#k;eden$
*4mbedded S5 = S5 binnen een ;ost language
*$1 Hoef • •
Host8language ;erkent S5 niet en gee6t 6outen =3 precompileren Qrecompiler Ka6;ankeli#k van productL 9 identiceert de S58instucties in ;et programma vertaalt de S58instructies naar de ;ost8language controleert de syntax van de S58instructies S58instructies controleert o6 de gebruikte tabellenDkolommen tabellenDkolommen bestaan controleert de bevoegd;eden bevoegd;eden bepaalt de ver!erkingsstrategie ver!erkingsstrategie voert de S58instructies uit
7Transacties en multi8user gebruik Single ' user I83 multiple8user •
>at als meerderen tegeli#k de"el6de gegevens !illen gebruiken
7$1 7$ 1 Tran ransact sacties ies = ver"ameling S58instructies die door OOn gebruiker gebruiker ingevoerd !ordt en !aarvan de mutaties bli#vend moeten "i#n o6 ongedaan moeten !orden$ 1utocoit: 4lke S58instructie is een transactie 4lke transactie is permanent commit9 permanent maken van een transactie Rollbac6: ongedaan maken van een transactie +ransactie: vana6 begin tot een commit o6 rollback • •
0aatste: steeds commit o6 rollback >anneer "involf :ls een bepaald gegeven uit meerdere tabellen gesc;rapt moet !orden :ls gebruiker "ic; vergist ;ee6t bi# aanpassingen o!eli76e uit8onderin!en: instructies die de catalogus !i#"igen • •
7$ Hoef nliecete start bv na .550:C) o6 C.//2T C6 autocommit E/licite start • • • •
beginP sql codeP commit P beginP sql codeP rollbackP start transactionP sql codeP commit P start transactionP sql codeP rollbackP
7$( 7 $( Sa Save vepo poin intt = maken een deel van een actuele transactie ongedaan$ Vb U/ate ; *nsert ; save/"*nt S1 *nsert ; save/"*nt S2 e'ete ; r"''ba!k 0"rk t" save/"*nt S2 ;
7$* Qr Qrobleme oblemen n multi8 multi8user user gebr gebruik uik .irty read (uncoitted read) : een gebruiker leest een gegeven dat nooit gecommit !erd onreeatable C nonreroducible read : 4en gebruiker leest voor en na de commit andere gegevens Kgegevens !orden ge!i#"igdL Phanto read : een gebruiker leest voor en na de commit andere gegevens Ker komen nieu!e gegevensL 0ost udate : 4en !i#"iging van OOn gebruiker !ordt oversc;reven door een andere gebruiker
7$7 7 $7 .p .plo loss ssin ing g Olossin! : Transacties Transacties serieel serieel ver!erken ver!erken ? Olossin! indien honderden !ebrui6ers te!eli76 willen wer6en Transacties Transacties parallel ver!erk ver!erken en ?
7$ 7 $ 5ock ta tabl ble$ e$$$ 0oc6in! : de ri# !aar OOn gebruiker mee !erkt !ordt gelocked voor de andere gebruikers als transactie a6gelopen is% !ordt de blokkade opge;even 5ocking gebeurt in de bujer Keg :/L Versc;illende opties voor granulariteit en rec;ten
• •
Kbv SH:4 vs 4MC5
7$@ 7 $@ Ee Eead adlo lock ckss • •
•
C6 operatings systems Eeadlock 9 o indien t!ee o6 meerdere gebruikers op elkaar !ac;ten .plossing 9 o indien deadlock aan!e"ig% dan !ordt OOn transactie a6gebroken a6gebroken
7$A Transac ransacties9 ties9 2S.5: 2S.5:T2.N T2.N 54V45 54V45 • • •
2solation level 9 mate van isolatie van gebruikers Niveaus9 Seriali"able 9 maximaal gesc;eiden epeatable read 9 le"en 9 s;are blokkades Kstopt bi# einde transactieL muteren 9 exclusive blokkades Cursor stability Kread committedL 9 le"en 9 s;are blokkades Kstopt bi# einde selectL muteren 9 exclusive blokkades Eirty read Kread uncommittedL 9 le"en 9 s;are blokkades Kstopt bi# einde selectL muteren 9 exclusive blokkades blokkades Kstopt bi# einde mutatieL Tabel Tabel boek
•
7$B 7 $B ,e ,evo volg lgen en •
Seriali"able 9 concurrency concurrency is ;et laagst Snel;eid laagst Eirty read 9 concurrency concurrency is ;oog% moeten !einig op elkaar !ac;ten )unnen gegevens le"en die enkele momenten later niet meer bestaan
•
Vb$ Set transaction isolation level seriali"able
Qroce Qrocedurele durele transacties $1 $ 1 Sto Store red d proce procedur dures es = ;oeveel;eid code die opgeslagen is in de catalogus van een E0 en die geactiveerd kan !orden door de"e aan te roepen vanuit een programma% een trigger% o6 een andere stored procedure$ Stored procedures maken ;et mogeli#k om bepaalde delen van een programma centraal in de catalogus van een database server op te slaan$ Hier "i#n "e dan vanuit alle programma’s aanroepbaar$ Vb$ 8reate /r"!e$re e'ete0estr*en (*n /s/e'ersnr *nteer) be*n e'ete %r"+ 0estr*en 0#ere s/e'ersnr = /s/e'ersnr A en 8a'' e'ete0estr*en (&) A
"erwer6in!: • • •
•
Vanuit programma !ordt procedure opgeroepen E0/S ontvangt oproep en "oekt procedure Qrocedure !ordt uitgevoerd !aarbi# de instructies op de database ver!erkt !orden /$b$v$ code !ordt aangegeven o6 procedurecorrect ver!erkt is KsqlcodeL
$ $ Qar arame amete ters rs • •
Communicatie met buiten!ereld buiten!ereld ( soorten 9 2nvoerparameters
•
$( 0ody • • • •
Vb. BOK1 BEGIN BOK2 BEGIN BOK: BEGIN EN4 BOK1A EN4 BOK2A EN4 BOK:A
$* $ * 5o 5okal kale e ver veriab iabele elen n • •
Vast;ouden van ti#deli#ke tussenresultaten E4C5:4 C<S. nood"akeli#k
$7 $ 7 S4T S4T82N 82NST ST
$ $ +lo +lo!8c !8cont ontro roll ins instru tructi cties es o!enli76heden: • • • • • •
2+ J TH4N J 45S42+ J TH4N J 4NE 2+ 2+ J TH4N J 45S4 J 4NE 2+ C:S4 >H4N J >H4N J 45S4 J 4NE C:S4P >H254 J E. J 4NE >H254 P 4Q4:T J
$@ :anr :anroepen oepen van stored stored pr procedur ocedures es ,100-instructie o!eli76heden : • • • •
Qrogramma 2nteractie6 S5 Stored procedure ecursieve oproep van een stored procedure
$A $A S4 S454 54CT CT 2NT 2NT. . 2ndien gegarandeerd maximaal 1 ri#? Vb. !reate /r"!e$re s"+b"etess/e'er (*n /s/e'ersnr *nteer, "$t s"+b"etes e!*+a'(&,2)) be*n se'e!t s$+(bera) *nt" s"+b"etes %r"+ b"etes 0#ere s/e'ersnr = /s/e'ersnrA en
!a'' s"+b"etes s/e'er s/e'er
(2@, s"+)
se'e!t s"+
$B +outbo outboodsc;a odsc;appen ppen%% ;andlers% ;andlers% conditi conditions ons •
• •
+outboodsc;appen o S58error8code9 besc;ri#vende tekst o S5ST:T49 S5ST:T49 code E4C5:4 H:NE549 S5 stopt ver!erking niet?? ( soorten9 o Continue o 4xit o
$1
Stored procedures met een cursor
2ndien resultaat uit meerdere ri#en bestaat
$11
Stored procedures en transacties
Stored procedure procedure analoog aan S58instructies • • •
Commit ollback Start transaction
$1
Stored pr procedures en en catalogus
ROU+E*: tabel in catalogus
$1(
Ver!i#deren stored procedures
.ROP PRO,E.URE: ver!i#dert procedures Vb$ 4r"/ /r"!e$re e'etes/e'ere
$1*
Compileren en ;ercompileren
T!ee T!ee tabellen met met "el6de naam gaat als "e versc;illende versc;illende eigenaren eigenaren ;ebben$ &ith recoile: compiler !ordt steeds opnieu! opgeroepen$ opgeroepen$ Ee ver!erkingsstrategie ver!erkingsstrategie voor de procedure procedure !ordt steeds opnieu! aan de actuele situatie van de db aangepast$ Nadeel9 ;ercompileren kost ti#d$
• •
>at met tabellen met de"el6de naam maar versc;illende eigenaar f :6;ankeli#k van ;et moment van compilatie Soms 9 bi# creatie van de procedure procedure Soms 9 bi# uitvoeren van de procedure procedure Soms 9 kan men kie"en
Vb. 8reate /r"!e$re e'ete0estr*en2 e'ete0estr*en2 (snrvar *n s+a''*nt) as 0*t# re!"+/*'e Be*n ; En
$17 • •
0eveiliging met stored procedures
,:NT 4M4C
Vb. rant ee!$te "n e'ete0estr*en e'ete0estr*en t" Q"#n
$1 • • • •
Voordelen van stored procedures
.nder;oudbaar;eid9 .nder;oudbaar;eid9 vb aantal versc;illende mutaties samen Ver!erkingssnel;eid9 minimaliseert net!erkverkeer Qrecompilatie bi# stored procedures .proepen .proepen vanuit versc;illende ;ost8languages
$1@
Stored 6unction
= Stukken code bestaande uit S58 en procedurele instructies% instructies% opgeslagen in de catalogus /aar9 • • •
,een uitvoerparameters mogeli#k .proep via allerlei expressies eturn nood"akeli#k
Vb$ !reate %$n!t*"n "''ars (bera e!*+a'(@,2)) ret$rns e!*+a'(@,2) be*n ret$rn bera (1 ,&) A en se'e!t beta'*nsnr, beta'*nsnr, bera, "''ars(bera) %r"+ b"etes
$1A
Ver!i#deren stored 6u 6unctions
.ROP 4U,+O: ver!i#dert 4U,+O: ver!i#dert 6unction Vb r"/ %$n!t*"n e'etes/e'er
$1B
Triggers
= ;oeveel;eid code die opgeslagen is in de catalogus die geactiveerd !ordt door ;et dbms indien een bepaalde operatie !ordt uitgevoerd en een conditie !aar is$ Ee"e !orden ;et dbms "el6 automatisc; opgeroepenKniet opgeroepenKniet door vb$ een callL$
D .E0E: -
-
$
+ri!! ri!!er er-e -e"e "ent nt tri! tri!!e !err-o oe ent nt o >anneer activerenf :+T49 :+T49 nadat triggering instructie is ver!erkt 04+.49 eerst de trigger8actie 2NST4:E .+9 alleen de trigger8actie o Voor !elke ri# activerenf +. 4:CH .>9 voor elke ri# +. 4:CH ST:T4/4NT9 voor een statement +ri!!e i!!err-co -condit ditie: ie: >H4N +ri!!er-actie: !at doet de triggerf E&: nieu!e tabel li#kt te bestaan$
Complexere vo voorbeelden
Veronderstel extra tabel met per speler ;et aantal gespeelde !edstri#den$ :ls speler gesc;rapt !ordt% moet de in6ormatie in6ormatie van die speler ook uit de"e tabel ver!i#derd !orden !reate tr*er e'etes/e'ers a%ter e'ete "n s/e'ers %"r ea!# r"0 be*n e'ete %r"+ s/e'ers0e
0#ere s/e'ersnr = "'.s/e'ersn
end P
$1
Triggers als integriteitsregels
Triggers Triggers kunnen kunnen gebruikt !orden voor ;et implementeren implementeren van van integriteitsregels$ integriteitsregels$ Vb$ !reate tr*er ebaart"e be%"re *nsert, $/ate(ebat$+, aart"e) "% s/e'ers %"r ea!# r"0 0#en (
$
Ver!i#deren van triggers
.ROP +RGGER: ver!i#deren +RGGER: ver!i#deren van een trigger Vb$ drop trigger geb#aartoe
$( • •
• • •
Versc;illen tussen producten
/eerdere /eerdere triggers op OOn tabel en een bepaalde mutatie f )an OOn trigger8actie leiden tot ;et activeren van een andere Ko6 de"el6deL trigger f >anneer !ordt de trigger8actie precies ver!erkt f >elke trigger8events !orden ondersteund f "i#n triggers op catalogustabellen toegelaten f
$*
versc;illen
*tored rocedure
*tored 5unctions
@History @$1 @$ 1 $Ea $EataK taKban bankkenL enL;is ;istor tory y
AHiGrar A HiGrarc;isc;e c;isc;e ,0E • • •
.nstaan in #aren ’ K’8’AL enrom populaire ge!eest 0ekendste voorbeeld9 2/S K20/L /omenteel9 verliest aan belangf
A$1 .pstellen .pstellen ;iGrar ;iGrarc;isc; c;isc;e e model A$ A $ 0o 0ou! u!st sten enen en
A$( 4 48mode 8modell naar ;iGrar ;iGrarc;isc; c;isc; model
A$* 5ee 5ee6re 6regels gels van van ;et ;iGrar ;iGrarc;isc; c;isc; model
A$7 Termin erminologie ologie ;ierarc; ;ierarc;isc; isc; model
+win-se!ent = +win-se!ent = "el6de type% "el6de parent *e!entcode = *e!entcode = nr dat segment identicieert Vol!orde-"eld= Vol!orde-"eld= veld !aarmee t!ins ondersc;eiden saen!estelde sleutel= sleutel= bepaald segment vinden
A$ A $ Voor oorbe beel eld d
A$@ A$ @ (8s (8sc;e c;ema ma arc;it arc;itect ectuur uur
A$A A $A ,0 ,0S S ta tale len n ,egevensdenitietaal9 .atabase .enition 0anguage ,egevensmanipulatietaal9 .atabase anipulation 0anguage
A$B EE5 • • •
Conceptueel niveau9 .ata b ata base ase description 2ntern niveau9 bestand 4xtern niveau9 Program ,ommunication Block
A$1
Conceptueel niveau9 E0E
= in de E0E vind #e de besc;ri#ving van de segmenten en ;un relaties$ Kgemaakt m$b$v$ macro8taalL •
/acro8bevelen9 E0E8macro9 E0E8macro9 gee6t een naam aan de databank S4,/8macro9 gee6t gee6t een naam en totale lengte in bytes aan een segment +245E8macro9 +245E8macro9 gee6t naam en startpositie van veld in segmentKvelden kunnen overlappenL
A$11
E0E Kdata base descriptionL
,RE1+E
A$1
4xtern niveau9 QC0
= besc;ri#ving van ;et QC0 d$!$"$ van de sensitieve segmenten en veldenKook met macro8taalL$ •
•
•
•
QC08macro9 QC08macro9 bevat naam van de E0E en de lengte van de sam$ Sleutel die ;et langst is S4NS4,8macro9 S4NS4,8macro9 voor elk sensitie6 segment% gebruik van naam segment "oals de"e voorkomt in E0E Q.C.QT9 ;iermee aangeven voor elk segment !elke be!erkingen "i#n toegelaten S4N+5E8macro9 voor meerdere sensitieve segmenten de"el6de processing options geven
GR.12: • • • • • •
,9+or ,et 2 9+or 2nsert 9+or replace E96or delete :9+or all option K,%2%%EL )9 key8sensitie6Ksegment overnemen omdat men een o6 meerdere van "i#n kinderen nodig ;ee6t$ :lleen in de samengestelde sleutel merkt men dat dit segment overgenomen is% verdere be!erkingen "i#n niet mogeli#kL$
A$1( A$1* •
•
•
,egevensmanipulatietaal9 E/5 )oppeling met de gasttaal
<,09 <,09 SS FoekargumentenKoptione FoekargumentenKoptioneelL elL = aangeven !elk segment men "oektKbv =%3%I% I3% $$L =3 >SS
P,B-as6er
A$17 •
•
•
•
+unctiecode
GU Kget uniqueL9 overloopt de boom ;iGrarc;isc; en stopt bi# ;et eerst GU Kget gevonden segment dat voldoet aan de meegeven voor!aarde$ Kpointer =3 volgende segmentL GKget GKget nextL9 ;oudt rekening rekening met pointer en begint op ;uidige plaats in de boom Kaangegeven door pointerL$ Stopt !anneer segment is gevonden dat voldoet aan voor!aarde$ Kpointer =3 volgende segmentL GPKget GPKget next !it;in parentL9 beginnen vana6 pointer maar alleen in a6;ankeli#ke segmenten van parent$ G;U C G; C G;P9 G;P9 "i#n ;et"el6de als bovenstaande maar "i#n verplic;t als de volgende instructie i nstructie een delete o6 replace is van ;et betrokken segment$
A$1
,<
A$1@
,N
A$1A
,NQ
A$1B
,H<8,HN8,HNQ
A$
E/586unctiecode
A$1
2ST
A$
E54T ' 4Q5
A$(
Foekargumenten
= !orden gebruikt om aan te geven !elk segment men "oekt$ Segmentnaam XKconditieLY
Veldnaam operatiecode !aarde
BNet!erkEatabank B$1 .pstellen van ;et net!erkmodel B$ B $ 0o 0ou! u!st sten enen en Recordtyes eniteitstype uit 4E$ Recordinstantie = OOn concreet voorkomen van een record van dat type$ *ettyes = komen overeen met 199n verbanden tussen enteitstypes in ;et 4E *etinstantie = voorkomen van een o!nerrecord met "i#n memberrecords $:n "erband • •
.!ner8recordtype /ember8recordtype
B$( 4 48mode 8modell naar net!e net!erkmode rkmodell • •
n8op8m relatie naar 18op8n relatie .m"ettingsregels 9 4ntiteittype !ordt recordtype 0inair 191 verband kan settype !orden 0inair 19n verband !ordt settype 0inair n9m verband 9 nieu! recordtype creGren
B$* B$ * Ter ermin minolog ologie ie Recordtye = Recordtye = besc;ri#6t de gegevens in de gegevensbank *ettye = geven relaties tussen recordtypes aan Record 6ey = ver"ameling van de velden die gebruikt !orden om rec;tstreeks recordinstantiaties op te "oeken
B$7 Versc; ersc;ilil ;iGrarc; ;iGrarc;isc; isc; ' net! net!erk erk •
0i# net!erkgegevensbanksyst net!erkgegevensbanksystemen9 emen9 )an een recordtype member "i#n in meerdere settypes )unnen meerdere settypes bestaan tussen de"e recordtypes )unnen members bestaan "onder o!ners
B$ B$ (8s (8sc;e c;ema ma arc;it arc;itect ectuur uur •
•
Conceptueel niveau 9 sc;ema Ksc;ema EE5L bevat besc;ri#ving van alle record8 en settypes 2ntern niveau 9 intern sc;ema KESE5 = .ata *torage .escription 0anguageL
•
4xtern niverau 9 subsc;ema Ksubsc;ema EE5L alleen ;et deel besc;reven dat nodig is voor applicatie
B$@ B$ @ Con Concep ceptue tueel el niveau9 niveau9 sc;ema sc;ema • • •
Naamgeving 0esc;ri#ving van de recordtypes 0esc;ri#ving van de settypes Naamgeving Specicatie o!ner en member Set order Set insertion optie Set retention optio Set selectie clausule
B$A B$ A Sc; Sc;ema ema EE5 ' naamg naamgevi eving ng Het sc;ema moet een naam kri#gen Bv. S85E- N-E IS s!#e+a?naa+ S85E- 4OKTERSPRKTIQK
B$B B$ B Sc; Sc;ema ema EE58re EE58recor cordty dtypes pes •
Het recordtype moet een naam kri#gen
RE8OR4 N-E IS re!"rnaa+ •
Qer recordtype !ordt de record key en de velden gespecicieerd
KEY ke
B$1 •
Sc;ema EE5 ' settypes
Het settype moet een naam kri#gen
Bv. SET N-E IS setnaa+ •
Specicatie o!ner en member
ONLER IS "0nernaa+ -E-BER IS +e+bernaa+ •
Set order = gee6t aan !elke de logisc;e volgorde van de members is binnen een setinstantie = indien logisc;e volgorde gestorteerd is op sleitel moet men aangeven in !elke volgorde de nieu!e memebers opgenomen moeten !orden
o6 ac;teraan •
• •
= gee6t aan o6 er duplicaten van members mogeli#k "i#n op de gespecieerde gespecieerde velden% "o #a o6 de"e voorop opgenomen moeten !orden$
Set insertion optie = aangeven o6 een member8recordtype bi# toevoeging meteen opgenomen moet !orden in een setinstantie$ /anual = niet verplic;t :utomatic =direct opnemen$
INSERTION IS UTO-TI8 -NU •
• • •
Set retention optie = gee6t aan o6 memberrecords binnen een setinstantie mogen losgekoppeld !orden van ;un o!ner$ +ixed = alti#d bi# de"el6de o!ner /andatory = loskoppelen maar daarna aan andere o!ner koppelen koppelen .ptional = kan op elk moment losgekoppelt losgekoppelt !orden
RETENTION IS HIE4 -N4TORY OPTION •
Set selectie clausules = aangeven op !elke manier de o!ner gedenticeerd !ordt$
SET SEE8TION IS T5RU setnaa+ OLNER I4ENTIHIE4 BY SYSTE PPI8TION PPI8TION KEY ve'naa+ Vb.
B$11
4xtern ni niveau9 su subsc;ema
= velden% record8 en settypes !orden gedenieerd die nodig "i#n voor een bepaalde applicatie$ /en kan ;ierin ook recordtypes% settypes en velden ;ernoemen$ • •
Velden en type van OOn bepaalde applicatie Syntax9 Naam9 SS subsc;ema8naam >2TH2N sc;ema8naam Hernoemen Hernoemen van velden9 :E 1lias .enitions Ee relevante record8 en settypes9 4C.E S4CT2.N 4C.E N:/4 2S recordnaam S4T S4CT2.N S4T N:/4 2S setnaam
B$1 •
•
E/ 5
User wor6 area (U&1) = (U&1) = plaats !aar gegevens a6komstig a6komstig van gegevensbank in geplaatst !orden Run unit = unit = voor elk programma OOn run unit% per run unit OOn <>:$ .B-status = per run unit OOn E08status% gee6t aan o6 vorige E/5instructie al dan niet succesvol a6gelopen is$ ,urrency indicator = kri#gen !aarde door ;et uitvoeren van een E/5instuctie$
B$1( •
)oppeling met gasttaal
Het subsc;ema moet geassocieerd geassocieerd !orden met ;et applicatieprogramma$ Eit is nodig voor opbou! van ork :rea$ E0 S<0SCH4/: subsc;ema8naam >2TH2N sc;ema8naam
B$1* • • • • • • • •
E/5 instructies
+2NE8 en ,4T8instructie >i#"igen van velden >eglaten van een recordinstantiatie Toevoegen Toevoegen van records records 4C.NN4CT8instructie :ssociGren van "!evende records 5oskoppelen van members 4T:2N2N,8optie
B$17 • •
•
,urrent o5 run unit(,RU) = unit(,RU) = ver!i#"igen naar de laatst gere6ereerde record ,urrent o5 record ointer(,RP) = ointer(,RP) = ver!i#"ing naar laatst gere6ereerde record van recordtype ,urrent o5 set ointer (,*P) = ver!i#"ing naar ;et laatste gere6ereerde record van ;et o!ner o6 van ;et member recordtype van ;et betrejende settype$
E/58 +2NE en ,4T
+2NE positioneert de currency currency indicatoren Copieren Copieren van in;oud naar C< d$m$v +2NE C<4NT record8naam >2TH2N set8naam ,4T ;aalt de in6ormatie op
groepen bi# de +2NE8instructie9 •
•
.p"oeken van een record van een recordtype
.p"oeken van een record van een settype9 o Foeken naar een o!ner o
B$1 •
Foeken naar members binnen een setinstantie
E/5 ' !i#"igen van velden
>i#"igen van velden binnen een record8instantie9 record8instantie9 /.E2+
B$1@ • •
E/5 ' !eglaten van een record
>eglaten van een recordinstantie uit de gegevensbank9 4:S4 :lle members !eglaten9 4:S4 :55
2>.1& 4- Z t"ev"een van re!"rs ST.4 recordnaam •
B$1B •
E/5 ' reconnect
4en memberrecord van o!ner veranderen9 RE8ONNE8T re!"rnaa+ LIT5IN setnaa+
B$ •
E/5 ' "!evend record associGren
Het associGren van een "!evend member8record Krecord van ;et memberrecordtype dat op ;et moment niet opgenomen is in een setinstantiatie van ;et besc;ou!de settypeL9 8ONNE8T re!"rnaa+ TO setnaa+
B$1
E/5 ' lo losk sko oppelen va van n members van ;un o!ner
4IS8ONNE8T 4IS8ONNE8T re!"rnaa+ HRO- setnaa+
B$ •
E/5 ' 4T:2N2N, optie
Sommige currency currency indicastoren niet !i#"igen Kmet uit"ondering van C
B$(
.e6eningen
V. .O2+ER V1 EE P1+F+ V1 ,O*U01+E
%#
( ( elationele vergeli#ki vergeli#king ng ;ierarchische structuren in een relationeel odel H concetueel
etwer6 structuren in een relationeel odel H concetueel
(1E0/S8soorten (1$1 .b#ec .b#ectt related related database database manage management ment system system ' .E/S •
2nleiding .E0/S en .E/S t$o$v$ de rest .E0/S t$o$v$ .E/S )enmerken K.E/SL
•
•
•
•
•
*iele !e!e"ens 8onder queries :ls men simpele gegevens Kgeen video% audio% en"$L % ge!oon !oorden% getallen% en"$ gebruikt% !aar men geen ondervragen op doet% volstaat ;et om een tekstver!erker te gebruiken$ Het E0/S dat men dan gebruikt is ;et bestandssysteem van ;et .S$ *iele !e!e"ens et queries 0v$ ,egevensbank met gegevens rond ;et personeel dat in een rma !erk"aam is$ Hiervoor gebruiken !e tabellen$ Vervolgens kunnen !e de"e tabellen ook ondervragen via queries$ Hiervoor gebruikt men een relationele gegevensbank$ ,ole/e !e!e"ens 8onder queries )i#k voorbeeld cursus pg 7A$ :ange"ien !e !erken met complexe gegevens maar niet met queries% kan men best gebruik aken van persistent opslaan in programmeertalen$ programmeertalen$ ,oele/e !e!e"ens et queries
)i#k voorbeeld cursus pg 7B$ Voor de"e situatie gaat men gebruik maken van een ob#ect8relationeel ob#ect8relationeel o6 ob#ect8georiGnteerde ob#ect8georiGnteerde gegevensbank$ • •
.E0/S9 belangri#kste speller .E/S9 specialisten ' nic;e markt
(.E/S ($1 ($ 1 ,eG ,eGnt nt op op ..Q5 ..Q5 conc concept epten en • •
Nieu!f SmalltalkK1B@L )enmerken9 .b#ecten - State vs 0e;aviour K"i#n tov kunnenL - .b#ect identier K.2EL = unique and immutable i mmutable Complexe types en structuren - :tomic% structKtupleL - CollectionKset% list% bag% array% dictionaryKkvvLL 2nkapseling Kti#deli#kKtransientL vs persistentL .vererving QolymorsmeKoperator QolymorsmeKoperator overloadingL
($ ($ .E5 ' .b#ect .b#ect deni denition tion langu language age •
.b#ecten vertaald 9 2n Qractice 9 Value vs e6erence e6erence 9 ob#ectid K.2EL 5evensduur 9 transient vs persistent Structuur 9 atomic o6 samengesteld Create 9 Ne! .verving 9 4xtends $$
• • • • •
Vb.
($( ($ ( .E/ .E/S9 S9 kolom kolom ob# ob#ect ecten en
($* ($ * .E/ .E/S9 S9 genes geneste te ob#e ob#ecte cten n
($7 ($ 7 .E/ .E/S9 S9 ove overe rervi rving ng
($ ($ .b# .b#ect ect quer query y languag language e ' .5
((.E0/S 2erner6en:
.vererving van gegevens .vererving van types egelsKbvL
(($1 Fel6 Fel6gede gedenieer nieerde de datat datatypes ypes = complexere en meer specialistisc;e datatypes
(($ Fel6 den deniGre iGren n van van datatyp datatypes es
•
C4:T4 TQ49 creGert datatype )enmerken9 ,ebruikt !aar basis8datatypes gebruikt !orden Hebben geen populatie Fi#n niet manipuleerbaar met een select Hee6t een statisc;e in;oudKaantal !aardenL Voordeel9 vergeli#king moet "invol "i#n Kstrong typingL
•
E.Q TQ49 ver!i#dert datatype
• •
Vb.
(($( Toegang tot datat datatypes ypes •
4igenaar van datatype9 degene die type creGert Eegene die type !il gebruiken% gebruiken% moet mac;tiging kri#gen9
•
Ver!i#deren van mac;tiging9
•
(($* (($ * Cas Castin ting g van van !aar !aardes des • • •
Casting9 om versc;illende datatypes te kunnen vergeli#ken Eestructor9 Eestructor9 trans6ormeert trans6ormeert "el6 gedenieerd gedenieerd datatype naar basisdatatype Constructor9 Constructor9 trans6ormeert trans6ormeert basisdatatype naar "el6 gedenieerd datatype
(($7 Fel6 den deniGre iGren n van oper operator atoren en •
Voora6gaandeli#ke opmerkingen9 .peratoren .peratoren "i#n toegevoegd voor ;et gemak 0i# elk basis8datatype ;oren operatoren .peratoren .peratoren ;ebben een betekenis betekenis al naargelang ;et datatype EeniGren van operatoren op "el6 denieerde datatypes operaties op onderliggend type$ EeniGren van operatoren in de vorm van scalaire 6uncties
Vb.
(($ (($ .pa .paque que8da 8datat tatype ype = datatype dat niet a6;ankeli#k is van een basisdatatype EeniGren van 6uncties om ;iermee te !erken is nood"akeli#k Vb.
(($@ (($ @ Nam Named ed ro ro!8d !8data atatyp type e = groeperen van !aarden die logisc; bi# elkaar ;oren Vb.
(($A (($ A
(($B (($ B ,et ,etype ypeer erde de tab tabel el = een datatype toekennen aan een tabel 4envoudig om geli#kende tabellen te creGren Vb.
(($1
2ntegriteitsregels op datatypes
= beperking leggen op toegestane !aardes Vb.
(($11
:5T4 TQ4
= !i#"igen van datatype Vb.
's !"n*t*e str*kter str*kter *s, 0***nen e0e*er e0e*er t"t a' ee 0aaren 0aaren *n e tabe' aane/ast *n.
(($1 • •
Sleutels en indexen
2s volledig analoog bi# "el6 gedenieerde gedenieerde datatypes 0i# named ro!8datatypes9 ro!8datatypes9 .p de volledige !aarde .p een deel ervan
(($1(
.vererving
= alle eigensc;appen van OOn datatype !orden overgeGr6d door een anderKsupertype anderKsupertype en subtypeL Vb.
(($1* • •
)oppelen van tabellen
2n ..8E09 alle al le ri#en ;ebben een unieke identicatieKdoor ;et systeemL 4+9 om identicatie op te vragen
Vb.
•
4+9 om tabellen te koppelen
(($17 •
Voor8 en nadelen
Voordelen9 :lti#d ;et #uiste datatype bi# de re6ererende re6ererende sleutel 2ndien primary keys breed "i#n% bespaart ;et !erken met re6erence8 kolommen kolommen opslagruimte
0i# !i#"igingen van primary keys !ordt geen ti#d verloren door ;et !i#"igen van de re6ererende sleutel 0epaalde selects !orden eenvoudiger Nadelen9 0epaald mutaties "i#n moeili#ker te deniGren deniGren e6erences !erken in OOn ric;ting 0i# E08ont!erp kri#gt men meerdere keu"es% dit !ordt dus moeili#ker e6erences kunnen de integriteit van de gegevens niet be!aken "oals re6ererende sleutels dat kunnen
•
(($1
Collecties
= ver"ameling !aardes in OOn cel Vb.
(($1@
.vererving van tabellen
= alle eigensc;appen van OOn tabel !orden overgeGr6d overgeGr6d door een andere tabel Beer6in!en: ,een cyclisc;e structuur ,een meervoudige overerving :lleen getypeerde tabellen in de tabel;iGrarc;ie .vereenkomst tabel;iGrarc;ie met type;iGrarc;ie
(($1A • • •
<54S
,aan verder dan triggers9 S454CT% 2NS4T%
•
Vb.
(*N.S5 = not only *I0 (niet relationeel)
(*$ *$1 1 /on ongo go db • • • • • • •
0inaire -S.N C
(*$ )V • • • •
5ook up Value using )ey Structuurf C6 Eictionary K.E/SL E0/S oplossingenJ Qostgresql9 ;store o6 ge!oonJ
(*$( (*$ ( :C :C2EK 2EKtra transa nsacti ctions onsLL • • • • • • •
:tomicity9 c6 0oolean Consistency 9 c6 state 2solation9 c6 concurrency concurrency Eurability9 c6 commit% levensduur en select E0/S9 UE0/S 8N.S5J
(7Eatabase (7 Eatabase keu"e keu"e (7$1 (7$ 1 Qoin ointer terss and c;o c;oice icess (7$1$1 • • • •
2deal !orld vs reality )no! your options >;at is importantf /ake your c;oices
(7$1$ • • • • • • • • •
SqlStandard
So6t!are8requirements
Standalone vs clientDserver Single user vs multi user Close to t;e S5 standard or $$$ uick and easy or$$$ Eurable or J ,Q58alike or J .cial Support or J esources% .S% $$$ $$$
Some gpl8alike examples9 SmallS5% S5ite% ,S5E0% +irebird% Eerby% /yS5% QostgreS5%J
(7$1$(
Simple classication
(7$1$*
/istakes
• • • • • • • • •
Too Too many database Kabstract Kabstract levelsL $$ vs Time Time )no! your data KstructureL XrequirementsY E0 ?= spreads;eetP skill o6 it’s o!n T;ird normal normal 6orm vs vs common sense K:llL t;e application logic in t;e database 0ackup% replication Version control
(7$1$7 •
>;at about 6rame!orks
Eata live expectancy compared compared to t;e programming environment
(E0/S • • •
)no! your toolsDoptions ContextDsituation ;andCra6ted ;andCra6ted vs /anHours
• • • • •
Humans vs /at;ematics Eata independence Standardi"ation :C2E vs Qer6ormance Voor bi#na elke klein bedri#6 tot )/. is een E0/S prima
($1 ($ 1 Toek oekomst omst van van S5
(@+requently made mistakes (@$1 (@ $1 -o -oin inss en te tell llen en
(@$ (@$ 5o 5ogic gical al ope operat rators ors
Haak#es rond . .perator?
