select

DatenbankbasierteWeb-Anwendungen
ForeignKeysundDatenabfragen
MedieninformatikSoSe2017
RenzoKottmann
ThisworkislicensedunderaCreativeCommonsAttribution-NonCommercial
4.0InternationalLicense.
Zusammenfassungdervorherigen
Vorlesung
EigeneDatentypen
ForeignKeysbeiAufzaehlungen(technisch)
Nichtbesprochen:
UmsetzungvonERMBeziehungenmittelsForeignKeys
AktuelleImplementierung
SQLfilemiterstenTestdaten
Relationships(Beziehungen)revisited
VerschiedeneEntitätenkönnenzueinanderinBeziehunggesetztwerden.
InjederBeziehunghabenEntitätengewisseRollen
BeziehungenkönnenEigenschaften(Attribute)haben
BeziehungenhabenKardinalitäten
Beziehungen
TeilenhmeristineinerProjektgruppeundeiner
Laborgruppe
JedeProjektgruppebearbeitetmehereLaboruebungen
BeziehungenmitForeignKeys
Bestimmemdie
AttributewelchedieBeziehungenidentifizieren
Kardinalitäten
ForeignKeys(onetomany)
Koennennureinszu
vieleBeziehungen
umsetzen
Werdenimmervon
vielezueinsgesetzt
CREATETABLElabor_gruppe(
codetext
PRIMARYKEY
CHECK(codeIN('w','x','y','z')),
--Attributefehlen
anfangszeittime
NOTNULL
);
CREATETABLEteilnehmer(
vornametext
CHECK(vorname!=''),
nachnametext,
CHECK(vorname!=''),
matrikel_nrinteger
PRIMARYKEY,
--Attributefehlen
labortext
REFERENCESlabor_gruppe(code),
geschlechttext
REFERENCESgeschlecht(name)
ONUPDATECASCADE
);
ForeignKeys(onetomany)
Frage
Wirdhier0..1zuN
oder
1zuNumgesetzt?
CREATETABLElabor_gruppe(
codetext
PRIMARYKEY
CHECK(codeIN('w','x','y','z')),
--Attributefehlen
anfangszeittime
NOTNULL
);
CREATETABLEteilnehmer(
vornametext
CHECK(vorname!=''),
nachnametext,
CHECK(vorname!=''),
matrikel_nrinteger
PRIMARYKEY,
--Attributefehlen
labortext
REFERENCESlabor_gruppe(code),
geschlechttext
REFERENCESgeschlecht(name)
ONUPDATECASCADE
);
ForeignKeys(manytomany)
ForeignKeys(manytomany)
Umsetzungdurchneue"Beziehungs"-Relation
ForeignKeys(manytomany)
NeueTabelle,dieauf
diebeiden
existierenden
referenziert
PrimaryKeyder
neuenTabelleist
Kombinationder
PKsder
existierenden
Tabellen
CREATETABLEprojekt_gruppe(
titeltext
PRIMARYKEY
CHECK(titel!=''),
thematext
NOTNULL
DEFAULT''
);
CREATETABLElabor_uebung(
titeltext
PRIMARYKEY,
gestelltdate
NOTNULL
UNIQUE
);
CREATETABLElabor_abgabe(
gruppetext
REFERENCESprojekt_gruppe(titel),
uebungtext
REFERENCESlabor_uebung(titel),
PRIMARYKEY(gruppe,uebung)
);
VonAnfragenzuAbfragen
Anfragen
Z.b.WievielTeilnehmersindinmeinemKurs?
StaerkenrelationalerDatenbanken:
1. Persistente,sichereundstrukturierteDatenspeicherung
2. EffizienteAbfragenumAnfragenbeantwortenzukoennen!
StaerkenrelationalerDatenbanken:
1. Persistente,sichereundstrukturierteDatenspeicherung
2. EffizienteAbfragenumAnfragenbeantwortenzukoennen!
SQLhatnureinenBefehldafuer:
StaerkenrelationalerDatenbanken:
1. Persistente,sichereundstrukturierteDatenspeicherung
2. EffizienteAbfragenumAnfragenbeantwortenzukoennen!
SQLhatnureinenBefehldafuer:
SELECT
AnatomyvonSELECT
SELECT*--welcheSpaltensollenwieangezeigtwerden
FROMtabelle--DatenwelcherTabelle
WHEREtrue--Selektionesbedingungen:nurDaten,dieKriteriumentsprechen
AnatomyvonSELECT
SELECT*--welcheSpaltensollenwieangezeigtwerden
FROMtabelle--DatenwelcherTabelle
WHEREtrue--Selektionesbedingungen:nurDaten,dieKriteriumentsprechen
Kanngelesenwerdenals:
ZeigemiralleSpaltenderTabelle"tabelle"anunddavonalleZeilen.
AnatomyvonSELECT
SELECT*--welcheSpaltensollenwieangezeigtwerden
FROMtabelle--DatenwelcherTabelle
WHEREtrue--Selektionesbedingungen:nurDaten,dieKriteriumentsprechen
Kanngelesenwerdenals:
ZeigemiralleSpaltenderTabelle"tabelle"anunddavonalleZeilen.
DatenbankinterpretiertdasinderReihenfolgeFROM,WHERE,'*'(Spalten)
HoleausderTabelle"tabelle"alleZeilendiederBedingung'true'entsprechen
undzeigedavonalleSpaltenan.
KonkretesSELECT
SELECT*--*(asterisk)heisstallespalten,wiesiesind
FROM"teilnehmer";--DatenderTabellemitdemNamen"order"
BoolscheWHEREBedingungkannweggelassenwerden,wennmanalleZeilen
will.
SammelnvonAnfragen
Transaktion
EineTranskationisteineMengevonOperationendieatomic,consistent,
isoliertunddauerhaftsind(ACID).
ACID
AlleAenderungenmit
Chirurgischerintegritaetineinem
IsoliertemVorgangzusammengefasstundmit
DauerhaftemErgebnis.
DiewichtigstenEigenschaftenimEinzelnen:
Atomic
EineTransaktiongruppiertmehrereAnweisungenineineeinzigeatomare
Operationinder"AllesoderNichts”stattfindet.
Consistent
EineTransaktionbringteineDatenbankvoneinemkonsistentenZustandin
dennächsten.
Isolated
Transaktionistunabhängig,d.h.siewirdnichtdurchkonkurrierende
Transaktionenbeeinflusst.
Durable
BeidererfolgreichenBeendigungeinerTransaktionsindalleÄnderungen
dauerhaftgespeichert.
Konsistenzbeisehrvielengleichzeitigen
Aenderungen
Transaktionengarantieren,dasstrotzgleichzeitiger
AenderungenvonvielenverschiedenenBenuztern,alle
Aenderungenkonsistentsind.
TatsaechlichistjedeeinzelneSQL-Anweisungbeiden
meistenDatenbankimpliziteineeinzelneTransaktion,d.h.
eineGruppevonAnweisungenmitnureinerAnweisung
TransaktionmitmeherenAnweisungen
PostgreSQLDocumentationfuersyntaktischeDetailsundfortgeschrittene
Eigenschaften
Ausgangslage:DatenbankistineinemsupergutemZustand:Z1
--Transaktionwirdangefangen:
BEGIN;
SQLAnweisung1;
SQLAnweisung2;
...
SQLAnweisungN;
--Bleibtoffenbis:
COMMIT;
WennTransaktiongutlaeuft,d.h.alleAnweisungenkorrektsind:
DatenbankistineinemsupergutemZustand:Z2
WennTransaktionfehlschlaegt,d.h.nureinederAnweisungenzumFehler
fuehrt:
DatenbankwirdindenvorherigenZustandzurueckgesetzt(rollback):Z1
PostgreSQLDocumentationfuersyntaktischeDetailsundfortgeschrittene
GewollterTransaktionsabbruch
--Transaktionwirdangefangen:
BEGIN;
SQLAnweisung1;
SQLAnweisung2;
...
SQLAnweisungN;
--Bleibtoffenbis:
ROLLBACK;
WennTransaktionfehlschlaegtodererfolgreichist:
DatenbankwirdindenvorherigenZustandzurueckgesetzt(rollback):Z1
DDLEntwicklung
PostgreSQListeinerderwenigenDBMS,dieauchDDL
AnweisungeninTransaktionenausfuehrenkann
--Transaktionwirdangefangen:
BEGIN;
CREATETABLEtest(idintegerPRIMARYKEY);
INSERTINTOtestVALUES(1),(2),(3);
SELECT*fromtest;
UPDATEtestSETid=id+3;
SELECT*fromtest;
ROLLBACK;
Sehrnuetzlichfueriterative,evolutionaere
Datenbankentwicklung
DankefürdieZusammenarbeit
Anhang
AbfragenuebermehereTabellen
Revisited:AnatomyvonSELECT
SELECT*--welcheSpaltensollenwieangezeigtwerden
FROMtabelle--DatenwelcherTabelle
WHEREtrue--Selektionesbedingungen:nurDaten,dieKriteriumentsprechen
Kanngelesenwerdenals:
ZeigemiralleSpaltenderTabelle"tabelle"anunddavonalleZeilen.
Revisited:AnatomyvonSELECT
SELECT*--welcheSpaltensollenwieangezeigtwerden
FROMtabelle--DatenwelcherTabelle
WHEREtrue--Selektionesbedingungen:nurDaten,dieKriteriumentsprechen
Kanngelesenwerdenals:
ZeigemiralleSpaltenderTabelle"tabelle"anunddavonalleZeilen.
EswirdimmereineundnureineTabelledurchSELECTerzeugt,daheristdas
technischpraeziser:
ErzeugeundzeigmireinevirtuelleTabelle,diefolgenderAnweisungenpricht:
ZeigealleSpaltenderTabelle"tabelle"anunddavonalleZeilen.
Beispiel-Tabellen
TabelleATabelleB
idnameidname
-----------1Pirate1Rutabaga
2Monkey2Pirate
3Ninja3DarthVader
4Spaghetti4Ninja
INNERJOIN
SELECT*
FROMTabelleAASa
INNERJOIN
TabelleBasb
ONa.name=b.name
idnameidname
-----------1Pirate2Pirate
3Ninja4Ninja
FULLOUTERJOIN
SELECT*
FROMTabelleAasa
FULLOUTERJOIN
TabelleBasb
ONa.name=b.name
idnameidname
-----------1Pirate2Pirate
2Monkeynullnull
3Ninja4Ninja
4Spaghettinullnull
nullnull1Rutabaga
nullnull3DarthVader
LEFTOUTERJOIN
SELECT*
FROMTabelleAASa
LEFTOUTERJOIN
TabelleBASb
ONa.name=b.name
idnameidname
-----------1Pirate2Pirate
2Monkeynullnull
3Ninja4Ninja
4Spaghettinullnull
LEFTOUTERJOIN:nurZeilenvonTabelleA
SELECT*
FROMTabelleAASa
LEFTOUTERJOIN
TabelleBASb
ONa.name=b.name
WHEREb.idISnull
idnameidname
-----------2Monkeynullnull
4Spaghettinullnull
FULLOUTERJOIN:nurexklusiveZeilen
SELECT*
FROMTabelleAASa
FULLOUTERJOIN
TabelleBASb
ONa.name=b.name
WHEREa.idISnull
OR
b.idISnull
idnameidname
-----------2Monkeynullnull
4Spaghettinullnull
nullnull1Rutabaga
nullnull3DarthVader
CROSSJOIN:ErzeugtkartesichesProdukt
SELECT*
FROMTabelleA
CROSSJOIN
TabelleB
IneinfachenWorten:VerbindetjedeZeilederTabelleAmitjederZeileder
TabelleB
DiskussionzuVisualisierungvonSQL-joins
OriginalArtikelmitVenn-Overview
DiskussionundalternativeDarstellung
KategorisierungnachJOIN-Typen
Normalisierung
MeineVorgehensweise
Alleswas,gemaessdenAnforderungen,eigenstaendigund
unabhängigvonexisiterenedenEntitaetengemanaged
werdenmuss,isteineneueeigenstaendigeEntitaet.
Managenheisst:AlleswaszuunterschiedlichenZeiten
erstinsSystemkommtundeigeneinsert,update,und
deleteRegelnhat.
Appendix
MotherCelko'sThirteenNormalizationHeuristics
1. Doesthetablemodeleitherasetofoneandonlyonekindof
entityoroneandonlyonerelationship.ThisiswhatIcall
disallowinga“Automobiles,SquidsandLadyGaGa”table.
Followingthisrulewillprevent‘Multi-valueddependencies’
(MVD)problemsanditisthebasisfortheotherheuristics.
2. Doestheentitytablemakesense?Canyouexpresstheideaof
thetableinasimplecollectiveorpluralnoun?Tobeistobe
somethinginparticular;tobeeverythingingeneralor
nothinginparticularistobenothingatall(thisisknownas
theLawofIdentityinGreeklogic).ThisiswhyEAVdoesnot
work–itiseverythingandanything.
3. Doyouhavealltheattributesthatdescribethethinginthe
table?Ineachrow?Themostimportantlegonathree-legged
stoolisthelegthatismissing.
1. Areallthecolumnsscalar?Orisacolumnservingmorethan
onepurpose?Didyouactuallyputhatsizeandshoesizein
onecolumn?OrstoreaCSVlistinit?
2. Donotstorecomputedvalues,suchas(unit_price*
order_qty).YoucancomputethesethingsinVIEWsor
computedcolumns.
3. Doestherelationshiptablemakesense?Canyouexpressthe
ideaofthetableinasimplesentence,orevenbetter,aname
fortherelationship?Therelationshipis“marriage”andnot
“person_person_legal_thing”
4. Didyouchecktoseeiftherelationshipis1:1,1:morn:m?
Doestherelationshiphaveattributesofitsown?Amarriage
hasadateandalicensenumberthatdoesnotbelongto
eitherofthepeopleinvolved.Thisiswhywedon'tmind
tablesthatmodel1:1relationships.
1. Doestheentityorrelationshiphaveanaturalkey?Ifitdoes,
thenyouabsolutelyhavetomodelitasthePRIMARYKEYor
aUNIQUEconstraint.Isthereastandardindustryidentifier
forit?Letsomeoneelsedoallthatworkforyou.
2. IfyouhavealotofNULL-ablecolumns,thetableisprobably
notnormalized.
3. TheNULLscouldbenon-relatedentitiesorrelationships.
4. DotheNULLshaveoneandonlyonemeaningineach
column?
5. Ifyouhavetochangemorethanonerowtoupdate,insertor
deleteasimplefact,thenthetableisnotnormalized.
6. Didyouconfuseattributes,entitiesandvalues?Wouldyou
splitthePersonneltableinto“Male_Personnel”and
”Female_Personnel”bysplittingoutthesexcode?No,sexis
anattributeandnotanentity.Wouldyouhaveacolumnfor
eachshoesize?No,ashoesizeisavalueandnotan
attribute.