3. Grundlagen des Relationenmodells

3.GrundlagendesRelationenmodells
Vorlesung"Informationssysteme"
Sommersemester2017
Gliederung
§ KonzeptezurDatenmodellierungimRelationenmodell(RM)
• NormalisierteRelationen
• Primärschlüssel,Fremdschlüssel,Nullwerte
§ AbbildungERMà RM
• Entity- undRelationship-Mengen
• Generalisierung
• Aggregation
§ Relationenalgebra
• KlassischeMengenoperationen
• Relationenoperationen
• MotivationderAnfrageoptimierung(Rewrite-Regeln)
• ErweiterungenderRelationenalgebra
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
2
KonzeptezurDatenmodellierung
§ Datenstruktur"Relation"*(auch:Tabelle)
• einzigeDatenstruktur(nebenatomarenWerten)
• alleInformationenausschließlichdurchWertedargestellt
• zeitinvarianteTypinformation:Relationenschema
• Integritätsbedingungenauf/zwischenRelationen:relationale
Invarianten
§ Beziehungen
• sindstetsexplizit,binärundsymmetrisch
• werdendurchWertedargestellt:RollevonPrimär-/Fremdschlüssel
è Entwurfstheorie(sieheKapitel4)
• Normalformenlehre,SynthesevonRelationen
* Dr. E.F. Codd described the relational model of data – one firmly grounded in predicate logic and
mathematics – in a series of papers published between 1969 and 1981. He was named an IBM fellow in
1976 and received the highest technical honor in computing, the Turing award, in 1981.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
3
Relationenmodell – Grundkonzepte
§ Definition:NormalisierteRelation
R(A1, A2, …, An) ⊆ W(A1) ☓ W(A2) ☓ … ☓ W(An)
Di
Untermenge
Dj
Dk
des kartesischen Produktes
§ Relationenschema
• NamederRelation,Attribute,Wertebereiche (Domänen)
• Attributesindatomar,d.h.nureinfacheWertebereiche
• Primärschlüssel(einfachoderzusammengesetzt)– optional!
- eindeutigundminimal(sieheER-Modell)
• 𝑠𝑠𝑠𝑠𝑠(𝑅𝑅) oder𝓡𝓡 bezeichnetdieMengederAttributederRelation𝑅𝑅
§ Relation(Ausprägung)
• Teilmengedeskart.ProduktesderWertebereiche
• Relationenelementeheißen"Tupel"
§ RelationisteineMenge
• GarantiederEindeutigkeitderTupel
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
4
SchemanotationundTabellendarstelllung
§ Schemanotation(textuell):
Relation:{[A1:W(A1),...,Ak:W(Ak),...An:W(An)]}
§ DarstellungsmöglichkeitfürRelation:n-spaltigeTabelle
§ Beispiel:
FB:{[FBNR:string,FBNAME:string,DEKAN:integer]}
PROF:{[PNR:integer,PNAME:string,FBNR:string,FACHGEBIET:string]}
FB
PROF
FBNR
FBNAME
DEKAN
PNR
PNAME
FBNR
FACHGEBIET
FB9
Wirtschaftswissenschaften
4711
1234
Härder
FB5
Datenbanksysteme
5678
Wedekind
FB9
Informationssysteme
Informatik
2223
4711
Müller
FB9
Operations Research
6780
Nehmer
FB5
Betriebssysteme
2223
Richter
FB5
Expertensysteme
FB5
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
5
EigenschaftenderTabellendarstellung
§ Vorsicht!TabelleistnureineDarstellungsform!
§ EsgeltenfolgendeGrundregeln*
• JedeZeile(Tupel)isteindeutig undbeschreibteinObjektder
Miniwelt
• DieOrdnungderZeilenistohneBedeutung;durchihre
ReihenfolgewirdkeinefürdenBenutzerrelevanteInformation
ausgedrückt
• DieOrdnungderSpaltenistohneBedeutung,dasieeinen
eindeutigenNamen(Attributnamen)tragen
• JederDatenwertinnerhalbeinerRelationisteinatomares
Datenelement
• AllefürdenBenutzerbedeutungsvollenInformationensind
ausschließlichdurchDatenwerte ausgedrückt
(*) Codd, E.F.: A Relational Model of Data for Large Shared Data Banks,
in: Comm. ACM 13:6, June 1970, pp. 377-387.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
6
DarstellungvonBeziehungen
§ InformationsdarstellungimRM
• ausschließlichdurchWerteeinesW(Ai)inRelationen
§ WiewerdenBeziehungendargestellt?
• (Evtl.zusammengesetzter)AttributwerteineTupelsenthält
Primärschlüsselwerteines(anderen)Tupels
• EinsolchesAttribut(odereineAttributkombination)wird
Fremdschlüssel genannt
- Fremdschlüssel istinBezugaufPrimärschlüssel/Schlüsselkandidateiner
Relationdefiniert(gleicherDefinitionsbereich)
- PaarevonFremdschlüsselundPrimärschlüssel/Schlüsselkandidatenmit
gleichenDomains
- mansagtauch:derFremdschlüsselreferenziertdenPrimärschlüssel
• Beziehungensinddadurchwertbasiert undsymmetrisch!
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
7
BeziehungenimRelationenmodell
§ ImSchemadiagramm:
• PfeilvomFremdschlüssel(FS)zumPrimärschlüssel
§ Beispiel:
FB
FB
FBNR
FBNAME
DEKAN
PROF
PNR
PNAME
Informationssysteme2017
FBNR
FBNR
FBNAME
DEKAN
FB9
Wirtschaftswissenschaften
4711
FB5
Informatik
2223
FACHGEBIET
PROF
PNR
PNAME
FBNR
FACHGEBIET
1234
Härder
FB5
Datenbanksysteme
5678
Wedekind
FB9
Informationssysteme
4711
Müller
FB9
Operations Research
6780
Nehmer
FB5
Betriebssysteme
2223
Richter
FB5
Expertensysteme
Kapitel3:GrundlagendesRelationenmodells
8
Nullwerte
§ Nullwerte
• AttributwertinnerhalbeinesTupelsistunbekanntodertrifft
nichtzu
• NULL-Indikatorwirdverwendet,umdieszubeschreiben
- inunserenTabellen-Beispielenauchdurchein"-"dargstellt
§ VerbietenvonNullwertenfüreinAttribut
• imSchemaAngabevon"NOTNULL"fürdasAttribut
PROF
FB
FBNR
FBNAME
DEKAN
PNR
PNAME
FBNR
FACHGEBIET
FB9
Wirtschaftswissenschaften
-
1234
Härder
FB5
Datenbanksysteme
Informatik
2223
5678
Wedekind
FB9
Informationssysteme
4711
Müller
FB9
Operations Research
6780
Nehmer
FB5
-
2223
Richter
FB5
Expertensysteme
FB5
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
9
Fremdschlüssel&Primärschlüssel
§ Definition"Fremdschlüssel":
EinFremdschlüssel bzgl.einerRelationR1istein(ggf.
zusammengesetztes)AttributFSeinerRelationR2,fürdaszujedem
Zeitpunktgilt:zujedemWert(ungleichNull)vonFSmusseingleicher
WertdesPrimärschlüssels PSodereinesSchlüsselkandidaten SKin
irgendeinemTupel vonRelationR1vorhandensein.
§ Bemerkungen
• Primärschlüssel:automatischNOTNULL.
• FremdschlüsselundSchlüsselkandidat:NullwerteOK
- Ausnahme:ÜberlappungmitPrimärschlüsseloderNOTNULLspezifiziert
• Primärschlüssel(Schlüsselkandidat)zusammengesetzt
à Fremdschlüsselauch
• EineRelationkannmehrereFremdschlüsselbesitzen,welchedie
gleicheoderverschiedeneRelationenreferenzieren.
• ReferenzierteundreferenzierendeRelationsindnichtnotwendig
verschieden(„self-referencing table“).
• Zyklensindmöglich(geschlossenerreferentiellerPfad).
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
10
AbbildungvomER-ModellindasRelationenmodell
ER-Modell
Relationenmodell
EinwertigesAttribut
✔
Definitionsbereich
✔
Primärschlüssel
✔
ZusammengesetztesAttribut
nur alsunabh.einwertige
Attribute
MehrwertigesAttribut
Relation
Entity-Menge
Relation
Relationship-Menge
Relation
➥ Die RM-Konzepte erreichen nicht das semantische Ausdrucksvermögen
der ER-Konzepte. Deshalb gehen gewisse Aspekte der Semantik verloren.
Es kann jedoch versucht werden, diese durch weitergehende
Datenmodellkonzepte (siehe SQL) oder durch Anwendungsfunktionen
nachzubilden.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
11
AbbildungvonEntity- undRelationship-Mengen
§ Kriterien
• Informationserhaltung
- AbbildungderER-KonzepteaufRM-Konzepte
- möglichstgenaueÜbereinstimmungderSemantik
(ÜbernahmeallerspezifiziertenEigenschaften)
• MinimierungderRedundanz
• MinimierungdesVerknüpfungsaufwandes
• aberauch:NatürlichkeitderAbbildung,keineVermischungvon
Objekten,Verständlichkeit
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
12
(Initiale)AbbildungvonEntity-Mengen
§ Jeder(starke)Entity-TypEwirdaufeineRelationRabgebildet
• RenthältalleeinfachenAttributevonE
• RenthältalleeinfachenKomponentenattributeder
zusammengesetztenAttributevonE(evtl.Umbenennung)
- mengenwertigeAttributeerforderngesonderteBehandlung
• PrimärschlüsselvonR:=PrimärschlüsselvonE
Name
InvNr
Autor
Titel
Ort
Verlag
EJahr
Bücher
Bücher
InvNr Titel
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
VName
EJahr
13
AbbildungvonmengenwertigenAttributen
§ MengenwertigesAttributMinEwirdaufeigeneRelationR'
abgebildet,mitfolgendenDefinitionen
• R'enthältM(oderalleKomponenten,fallsWerte
zusammengesetztsind)
• R'erhältPrimärschlüsselvonRalsFremdschlüsselK
• (K,M)wirdPrimärschlüsselvonR'
§ Beispiel(fürAutor vonBücher)
Bücher
• analogfürOrt vonVerlag
InvNr Titel VName EJahr
§ WiederholteAbbildungbeikomplexer
geschachteltenStrukturen
Autoren
InvNr Autor
NatürlicheAbbildung?Alternativen?
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
14
AbbildungvonschwachenEntities
§ SchwacheEntities
• AbbildungwiebeistarkenEntities,aber:
• PrimärschlüsselvonRergibtsichausderKombinationder
PrimärschlüsselderbestimmendenEntity-Typenunddes
partiellenSchlüssels,fallsEeinenpartiellenSchlüsseldefiniert
...
PNr
Angestellte
Angestellte
PNr
Informationssysteme2017
...
Vorname
(0, *)
Eltern
von
(1, 1)
Alter
Kinder
Kinder
PNr Vorname
Alter
Kapitel3:GrundlagendesRelationenmodells
15
AbbildungvonRelationships
§ Initiales(generelles)Vorgehen
• jeden-äreRelationshipmitEntity-TypenE1,...En wirdalseigene
RelationR dargestellt
• RenthältalsAttribute
- alleSchlüsselK1,...Kn,mitKiSchlüsselvonEi
- alleRelationship-Attribute
- wennnötigmitUmbenennungvonAttributen(eindeutigeNamen!)
• K1,...KnsindFremdschlüssel,referenzierenE1,...En
• (K1,...Kn)ist(zusammengesetzter)(Primär-)Schlüssel vonR
§ NachfolgendwirddasresultierendeRelationenschemaverfeinertbzw.
vereinfacht
• falls(K1,...Kn)nichtminimalist(alsokeinechterPrimärschlüssel),
bestimmeeinen(minimalen)PrimärschlüsselPS
- abhängigvondendefiniertenFunktionalitäten/Kardinalitäten
• optional:ZusammenfassenvonTabellenmitdengleichenPS (à
einfacheresSchema,geringererVerknüpfungsaufwand)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
16
ZweiEntity-Mengenmitn:m-Verknüpfung
Dauer
JNR
PNR
n
PROJEKT
Projekt
j1
MITARBEIT
Mitarbeit
p1
p2
j2
j3
PROJEKT (JNR, BEZEICH, …)
j1
j2
j3
m
PERS
Pers
p3
MITARBEIT (JNR,
j1,
j2,
j2,
PNR,
p1
p1
p2
DAUER)
...
...
...
PERS (PNR, PNAME, …)
p1
p2
p3
VerwendungvondreiRelationenerforderlich:
§ GenerischeAbbildung(wiebereitsdefiniert)
§ AlleFremdschlüsselsindBestandteiledesPrimärschlüssels!
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
17
ZweiEntity-Mengenmit1:n-Verknüpfung
Dauer
ANR
ABT
1
(0,*)
N
ABT-ZUGEH
PNR
(0,1)
PERS
§ InitialeAbbildungführtzudreiRelationen(ABT,PER,ABT-ZUGEH)
§ "1"beiABTbzw.Kardinalitäsrestriktion (0,1)oder(1,1)beiPERS
heißtPERSà ABT(PERSbestimmtABTeindeutig)
èANRistnurFemdschlüssel,nichtTeildesPrimärschlüssels
èPNRistSchlüsselkandidatbzw.Primärschlüssel
§ KorrekteinitialeAbbildungmitdreiRelationen:
ABT(ANR, ANAME, …)
a1
a2
PERS (PNR, PNAME, …)
p1
p2
ABT-ZUGEH (ANR, PNR, DAUER)
a1 p1
a1 p2
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
18
ZweiEntity-Mengenmit1:n-Verknüpfung(2)
§ PERSundABT-ZUGEHhabenbeidePNRalsPrimärschlüssel und
könnendeshalbzusammengefasstwerden
• alleAttributeausABT-ZUGEHwerdenübernommen
ABT(ANR, ANAME, …)
a1
a2
PERS (PNR, PNAME, …, ANR, DAUER)
p1, ...
a1
p2, ...
a1
§ Fazit:bei1:n-BeziehungenkannaufeigeneRelationzurDarstellung
derRelationshipverzichtetwerden
• beiExistenzvonRelationship-AttributenkannVerständlichkeit
durchVermischungderAttributeleiden
§ Vorsicht:niemalsRelationenmitverschiedenen Primärschlüsseln
zusammenfassen!(FührtzuAnomalien)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
19
ZweiEntity-Mengenmit1:1-Verknüpfung
PNR
Assistenten
1
(0,1)
halten
1
ÜNR
Übungen
(0,1)
§ InitialeAbbildung:Relation"halten"(hiermitPS"ÜNR")
• sowohlÜNRalsauchPNRsindSchlüsselkandidaten
Assistenten(PNR, …)
Übungen(ÜNR, …)
halten (PNR, ÜNR)
§ Zusammenfassen– 2Möglichkeiten,nachWahldesPS
Assistenten(PNR, …)
Übungen(ÜNR, …)
welche?
Übungen(ÜNR, PNR…)
Assistenten(PNR, ÜNR, …)
§ Vorsicht:Abbildunggarantiertsonur1:N!
§ 1:1erfordertexpl.DefinitionvonSchlüsselkandidat(UNIQUE)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
20
Kardinalitätsrestriktionen– "(1,1)"
PNR
Beispiel:
Assistenten
1
(0,1)
halten
1
ÜNR
Übungen
(1,1)
§ KannbeigeeigneterZusammenfassungdurchVerbotvon
Nullwertenerfolgen
• inder"abhängigen"Relationwirdfür
Assistenten(PNR, …)
FremdschlüsselNOTNULLgefordert
Übungen(ÜNR, PNR…)
• ImBeispiel:Übungen- PNRNOTNULL
§ AuchbeizusammengefasstenRelationenbei1:Nverwendbar
Informationssysteme2017
1
1 bzw. N
(0,n)
(0,1)
FS
(0,n)
(1,1)
FS ... NOT NULL
(0,1)
(0,1)
FS ... UNIQUE
(0,1)
(1,1)
FS ... UNIQUE NOT NULL
Kapitel3:GrundlagendesRelationenmodells
21
Verfeinerungbein-ärenBeziehungen
Studenten
N
(0, 1)
1
betreuen
1
Professoren
Professoren
Seminarthemen
Studenten
Seminarthemen
betreuen(MatrNr, PNr, Thema)
§ InitialeAbbildung:Relation"betreuen"mitMatrNr,PNrundThema
alsFremdschlüssel
§ GeeigneterPSfür"betreuen"?
• EsgiltaufgrundN:1:1
- ProfessorenXStudentenà Seminarthemen
- SeminarthemenXStudentenà Professoren
è(PNr,MatrNr)und(Thema,MatrNr)sindSchlüsselkandidaten
§ Zusätzlichcard(0,1)odercard(1,1)bei"Studenten"?
• (MatrNr)istSchlüsselkandidat(unddamitPS)von"betreuen"
• Option:Zusammenfassenvon"betreuen"mit"Studenten"!?
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
22
AbbildungderGeneralisierungimRM
§ EinschränkungendesRelationenmodells
• keineUnterstützungderAbstraktionskonzepte
• keineMaßnahmenzurVererbung
(vonStruktur,Integritätsbedingungen,Operationen)
• „Simulation“derGeneralisierungeingeschränktmöglich
§ Generalisierungsbeispiel:
UNI-ANGEH. ID
NAME
Ernie
ANGESTELLTE BAT
TECHNIKER
Garfield
ERFAHRUNG
...
WISS.-MA
DIPLOM
SPEZ.-GEB.
Donald
Informationssysteme2017
BEAMTE
Daisy
Grouch
Kapitel3:GrundlagendesRelationenmodells
23
Generalisierung– relationaleSicht
§ Lösung1:Hausklassen-Modell:
• JedeInstanzistgenaueinmalundvollständiginihrerHausklasse
gespeichert
UNI-ANGEH.
ID
NAME
• "horizontalePartitionierung"
111 Ernie
ANGESTELLTE
TECHNIKER
WISS.-MA.
ID
NAME
BAT
007
Garfield
Ia
ID
ERFAHRUNG
NAME
BAT
123
SUN
Donald
IVa
ID
DIPLOM
SPEZ.-GEB.
NAME
BAT
007
765
Informatik
Mathematik
Recovery
ERM
Daisy
Grouch
IIa
IIa
§ Eigenschaften:
• niedrigeSpeicherungskostenundkeineÄnderungsanomalien
• Retrieval kannrekursivesSucheninUnterklassenerfordern
• expliziteRekonstruktiondurchRelationenoperationen
➥ Beispiel:FindealleANGESTELLTE:
TECHNIKER∪ WISS.-MA∪ ANGESTELLTE,beschränktaufID,NAME,BAT
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
24
Generalisierung– relationaleSicht(2)
§ Lösung2:Partitionierungs-Modell
• InstanzwirdentsprechendderKlassenattributeinderIs_a-Hierarchie
zerlegtundinTeilenindenzugehörigenKlassengespeichert
• ID-Attributdupliziert
ANGESTELLTE
ID
BAT
• "vertikalePartitionierung"
007
Ia
UNI-ANGEH.
ID
NAME
007
111
123
333
765
Garfield
Ernie
Donald
Daisy
Grouch
TECHNIKER
ID
ERFAHRUNG
123
SUN
WISS.-MA.
123
333
765
IVa
IIa
IIa
ID
DIPLOM
SPEZ.-GEB.
007
765
Informatik
Mathematik
Recovery
ERM
§ Eigenschaften
• geringfügigerhöhteSpeicherungskosten,aberhoheAufsuch- und
Aktualisierungskosten
• Integritätsbedingungen: TECHNIKER.ID⊆ ANGESTELLTE.IDusw.
• InstanzenzugrifferfordertimpliziteoderexpliziteVerbundoperationen
➥ Beispiel:FindealleTECHNIKER-Daten
VerbindeTECHNIKER,ANGESTELLTE,UNI-ANGEHübergleicheIDs
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
25
Generalisierung– relationaleSicht(3)
§ Lösung3:VolleRedundanz
• InstanzwirdwiederholtinjederKlasse,zudersiegehört,mitallen
(auchgeerbten)Attributengespeichert
UNI-ANGEH.
TECHNIKER
WISS.-MA.
ID
NAME
ANGESTELLTE
ID
NAME
BAT
007
111
123
333
765
Garfield
Ernie
Donald
Daisy
Grouch
007
123
333
765
Garfield
Donald
Daisy
Grouch
Ia
IVa
IIa
IIa
ID
NAME
BAT
ERFAHRUNG
123
Donald
IVa
SUN
ID
NAME
BAT
DIPLOM
SPEZ.-GEB.
333
765
Daisy
Grouch
IIa
IIa
Informatik
Mathematik
Recovery
ERM
§ Eigenschaften
• hoherSpeicherplatzbedarf,AuftretenvonÄnderungsanomalien
• einfachesRetrieval:Aufsuchender Zielklasse(z. B.ANGESTELLTE)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
26
Generalisierung– relationaleSicht(4)
§ Lösung4:Hierarchierelation
• EineeinzigeRelationfürGeneralisierungshierarchie
• AttributzurTypidentifikation(TT- typetag):Kennzeichnungder
KlassenzugehörigkeiteinerInstanz
• NullwertefürAttributefürnichtzutreffendeAttribute
ID
TT
NAME
BAT
ERFAHR.
DIPLOM
SPEZ.-GEB.
007
111
123
333
765
Angestellte
Uni-Angeh.
Techniker
Wiss.-MA
Wiss.-MA
Garfield
Ernie
Donald
Daisy
Grouch
Ia
Iva
IIa
IIa
SUN
-
Informatik
Mathematik
Recovery
ERM
§ Eigenschaften
• erhöhteSpeicherkosten(Typidentifikator,Nullwerte)
• VermeidungvonRedundanz
• VermeidungvonVerbund,Union
• Retrieval erfordertProjektionundmglw.zusätzliche
SelektionsbedingungenüberTTabhängigvonderZielklasse
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
27
Aggregation– relationaleSicht
§ AbbildungähnlichzurAbbildungvonRelationships
• Kardinalitätsrestriktionenbeachten/nutzen,umExklusivitätund
Abhängigkeitzumodellieren
tv
Prozessor
ID
PREIS
tv
tv
Plattenk.
tv
CD-ROM
Informationssysteme2017
PC-System
tv = teil-von
tv
Drucker
Monitor
tv
Festplatte
ID
KAPAZITÄT
EINZELPREIS
Kapitel3:GrundlagendesRelationenmodells
28
Aggregation– relationaleSicht(2)
PC-System
ID
PC-1
PC-2
PC-3
Plattenk.
PREIS
900,-1700,-2200,-ID
PK1
PK2
PK3
CD-ROM
PROZESSOR
PLATTENK.
Festplatte
C1
C2
C3
F1
F2
F3
Geschwindigk.
Preis
C1
C2
C3
DVDR
DVD R+CDRW
DVDR+RW
35,-85,-165,--
ID
ID
Tintenstrahldrucker
Laserdrucker
Informationssysteme2017
15“TFT
17“TFT
17“TFT
ID
Hersteller
Festplatte
15“
17“
Belinea
Belinea
320,-420,--
Festplatte
ID
Kapaz.
F1
F2
F3
SPEC int 2000
Preis
633
1034
1226
150,-190,-310,--
I-Pentium IV1,8GHz
I-PentiumIV2,4GHz
I-PentiumIV3,0GHz
MONITOR
Tintenstrahldr.
Laserdrucker
Laserdrucker
Monitor
ID
Prozessor
Drucker
PK1
PK2
PK3
I-PentiumIV1,8GHz
I-PentiumIV2,4GHz
I-PentiumIV3,0GHz
CD-ROM
DRUCKER
80GB
120GB
160GB
Hersteller
Typ
Preis
HP
HP
DeskJet5150
Laser1300
100,-360,--
Kapitel3:GrundlagendesRelationenmodells
Preis
85,-100,-150,--
29
Zusammenfassung:Abbildungskonzepte
§ DatenstrukturRelation(Tabelle)
➥ einzigeDatenstruktur(nebenatomarenWerten)
➥ alleInformationenausschließlichdurchWertedargestellt
➥ Integritätsbedingungen:relationaleInvarianten
§ AbbildungvonBeziehungendurchPS/SK– FS
• alleBeziehungensindexplizit,binärundsymmetrisch
• alleBeziehungstypenmüssenimPrinzipdurch(n:1)-Beziehungendargestelltwerden
• generellesVorgehen:
eigeneRelationà ErmittlungdesPSà ggf.Zusammenfassen
- (n:m)-BeziehungstypensinddurcheineeigeneRelationdarzustellen
- für(1:n)und(1:1)istZusammenfassenvonRelationenmöglich
§ BewertunghinsichtlichderAbstraktionskonzepte
• keinedirekteBereitstellungderAbstraktionskonzepte
(nurKlassifikationderTupeln inRelationen)
• begrenzteMöglichkeitenzurAbbildungderAbstraktionskonzepte
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
30
SprachenfürdasRelationenmodell
§ Datenmodell=Datenobjekte+Operatoren
§ ImRMwirdvereinheitlichteSpracheangestrebtfür
• alleAufgabenderDatenverwaltung(Datendefinition,Anfragen
(Queries),Datenmanipulation,Zugriffs-,Integritäts- und
Transaktionskontrolle
• zurNutzung
- imStand-Alone-Modus(Ad-hoc-Anweisungen)und
- ineinerWirtssprache(eingebetteteDB-Anweisungen)
§ VierverschiedeneGrundtypen:
• Relationenalgebra (z. B.ISBL)ç indiesemKapitel
• Relationenkalkül (z. B.Alpha)
• AbbildungsorientierteSprachen(z. B.SQL)
• GraphikorientierteSprachen(z. B.Query-by-Example)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
31
Relationenalgebra(RA) - Überblick
§ Objekte:
• EinSystem,dasauseinernichtleerenMengeundeinerFamilie
vonOperationenbesteht,heißtAlgebra.
➥ RelationensindMengen.
§ Operationen:
• ArbeitenaufeinerodermehrerenRelationenalsEingabeund
erzeugeneineRelationalsAusgabe.
Abgeschlossenheit
nur unäre und binäre Operationen
op3
op5
op1
R
Informationssysteme2017
op2
R
opi
S
R
S
op4
T
R
S
Kapitel3:GrundlagendesRelationenmodells
T
32
OperationenderRelationalenAlgebra- Überblick
§ KlassischerelationaleAlgebra
• KlassischeMengenoperationen:
- Vereinigung,Differenz,kartesischesProdukt
- ableitbar:Durchschnitt
• Relationenoperationen:
- Projektion,Restriktion(Selektion)
- ableitbar:Verbund(Join),Division
➥ AuswahlvermögenentsprichtPrädikatenkalkülersterOrdnung
(„relationalvollständig“)
• Schemaoperation:
- Umbenennung(Rename)
§ Erweiterungen
• Join-Varianten(OuterJoin,Semi-Join...)
• GruppierungundAggregation,Duplikateliminierung
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
33
Selektion(Restriktion)
§ AuswahlvonZeileneinerRelationüberPrädikate,abgekürzt𝜎𝜎𝑃𝑃
𝑃𝑃 =log.Formel(ohneQuantoren!)zusammengestelltaus:
a)Operanden(KonstantenoderAttributnamen)
b)VergleichsoperatorQÎ{<, =, >, ≤, ≠, ≥}
c)∧,∨, ¬
§ Definition:
s𝑷𝑷(𝑹𝑹) = {𝒕𝒕|𝒕𝒕Î𝑹𝑹 ∧ 𝑷𝑷(𝒕𝒕)}
𝑃𝑃(𝑡𝑡):Ersetzeinderlog.Formel𝑃𝑃 alleAttributnamendurch
entsprechendeWerteaus𝑡𝑡.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
34
Selektion- Beipiele
PERS
PNR
NAME
ALTER
GEHALT
PROVISION
ANR
MNR
406
123
829
574
Coy
Müller
Schmid
Abel
47
32
36
28
50700
43500
45200
36000
34000
21000
15800
39100
K55
K51
K53
K55
123
777
123
s<=>?@ABCDEFGA∧=HI?JKLM (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
sP?Q=HIRSJTUVBVT< (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
PNR
NAME
ALTER
GEHALT
PROVISION
ANR
MNR
829
Schmid
36
45200
15800
K53
777
PNR
NAME
ALTER
GEHALT
PROVISION
ANR
MNR
574
Abel
28
36000
39100
K55
123
ALTER
GEHALT
PROVISION
ANR
MNR
47
50777
34000
K55
123
s=<J@AWXXA∧P?Q=HIKXMMMM (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
Informationssysteme2017
PNR
NAME
406
Coy
Kapitel3:GrundlagendesRelationenmodells
35
Projektion
§ AuswahlderSpalten(Attribute)𝐴𝐴 = 𝐴𝐴1, 𝐴𝐴2, . . . , 𝐴𝐴𝑘𝑘auseiner
Relation𝑅𝑅 (Gradn≥k)
p𝑨𝑨(𝑹𝑹) = {𝒑𝒑|$𝒕𝒕Î𝑹𝑹: 𝒑𝒑 =< 𝒕𝒕[𝑨𝑨𝟏𝟏], …, 𝒕𝒕[𝑨𝑨𝒌𝒌] >}
§ Definition:
§ Mengeneigenschaft erfordertggf.Duplikateliminierung!
§ Beispiele:
• p<=>?,P?Q=HI,=HI?J (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
• p=<J,><J (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
PERS
PNR
NAME
ALTER
GEHALT
ANR
MNR
406
123
829
574
Coy
Müller
Schmid
Abel
47
32
36
28
50700
43500
45200
36000
K55
K51
K53
K55
123
777
123
ERG
p ANR,MNR
ANR
MNR
K55
K51
K53
123
777
! Duplikate entfernt !
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
36
Operationssequenzen
§ AusführenvonmehrerenOperatorennacheinander
➥ AbgeschlossenheitderAlgebra
§ GeschachtelteRA-Ausdrücke
Beispiel:
p NAME,GEHALT,ALTER(sANR='K55'∧ GEHALT>50000(PERS))
§ Alternativ:VerwendungvonZwischenrelationen
Beipiel:
TEMPß sANR='K55'∧ GEHALT>50000(PERS)
RESULTATß p NAME,GEHALT,ALTER(TEMP)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
37
KlassischeMengenoperationen
§ KlassischebinäreMengenoperationenfürRelationen
Voraussetzung:Vereinigungsverträglichkeitderbeteiligten
Relationen(gleicherGrad+gleicheWertebereiche)
(A1, A2, . . . , An)
(B1, B2, . . . , Bn)
➥
Di
Dj
...
1.Vereinigung(UNION)vonRundS
RÈ S={t|t Î R∨ tÎ S}
W(Ai)=W(Bi):i=1,n
Dk
2.Differenz
R‒S={t|t Î R∧ tÏ S}
zusätzlich (redundanteMengenoperationen):
3.Durchschnitt(INTERSECTION)
RÇ S=R‒(R‒S)
={t|t Î R∧ tÎ S}
Informationssysteme2017
4.SymmetrischeDifferenz
RΔ S =(RÈ S) ‒(RÇ S)
=((RÈ S) ‒(R‒(R‒S)))
={t|t Î RÅ tÎ S}
Kapitel3:GrundlagendesRelationenmodells
38
(Erweitertes)KartesischesProdukt
§ 𝑅𝑅 (Gradr)und𝑆𝑆 (Grads)beliebig
Definition:𝑲𝑲 = 𝑹𝑹×𝑺𝑺 = {𝒌𝒌|$𝒙𝒙Î𝑹𝑹, 𝒚𝒚Î𝑺𝑺:(𝒌𝒌 = 𝒙𝒙|𝒚𝒚)}
𝑘𝑘 = 𝑥𝑥|𝑦𝑦 =< 𝑥𝑥1, … , 𝑥𝑥𝑟𝑟, 𝑦𝑦1, … , 𝑦𝑦𝑠𝑠 >
nicht << 𝑥𝑥1, … , 𝑥𝑥𝑟𝑟 >, < 𝑦𝑦1, … , 𝑦𝑦𝑠𝑠 >>wieüblicheskartesisches
Produkt
§ Anwendungvon: 𝐴𝐴𝐴𝐴𝐴𝐴×𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃
ABT
PERS
ANR
ANAME
AORT
K51
K53
K55
Planung
Einkauf
Vertrieb
KL
F
F
PNR
ALTER
ANR
406
123
829
574
47
32
36
28
K55
K51
K53
K55
Informationssysteme2017
ABTxPERS
ANR
ANAME
AORT
PNR
ALTER
ANR'
K51
K51
K51
K51
K53
:
:
Planung
Planung
Planung
Planung
Einkauf
:
:
KL
KL
KL
KL
F
:
:
406
123
829
574
406
:
:
47
32
36
28
47
:
:
K55
K51
K53
K55
K55
:
:
Enthält viele (oft auch unsinnige) Kombinationen!
Kapitel3:GrundlagendesRelationenmodells
39
UmbenennungvonAttributenundRelationen
§ AuflösungvonNamenskonfliktenkannerforderlichsein,wenn
• einAttributnameinmehrerenbeteiligtenRelationenauftaucht
• diegleicheRelationmehrmalsinOperationenverwendetwird
§ ImpliziteUmbenennungvonAttributen
• NamederUrsprungsrelationwirdalsPräfixverwendet
Beispiel:ErmittleKombinationenvonAbteilungenmitPersonen,dieinden
Abteilungenarbeiten
p=pI.=<J,=<=>?,S<J (s=pI.=<J@S?JB.=<J (𝐴𝐴𝐴𝐴𝐴𝐴×𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃))
§ ExpliziteUmbenennungdurchRename-Operator(r)
• UmbenennungeinesAttributs
p=<J,=<=>?,S<J (s=<J@=pI<J (𝐴𝐴𝐴𝐴𝐴𝐴×r=pI<J←=<J (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)))
• UmbenennungvonRelationendurchAngabedesneuen
Relationennamens
Beispiel:ErmittlePaarevonPersonen,dieindergleichenAbteilungarbeiten
sSr.=<J@Ss.=<J (rSr (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)×rSs (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃))
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
40
Verbund(Join,Θ-Join)
§ Grob:
KartesischesProduktzwischenzweiRelationen𝑅𝑅 (Gradr)und𝑆𝑆
(Grads)eingeschränktdurch𝑄𝑄-Bedingungenzwischeni-Spalte
von𝑅𝑅 undj-Spaltevon𝑆𝑆.
§ Definition:
SeiΘ ∊ {<, =, >, ≤, ≠, ≥}(Vergleichsoperator)
Θ-Verbund zwischen𝑅𝑅(𝐴𝐴1, … 𝐴𝐴𝐴𝐴)und𝑆𝑆(𝐵𝐵1, … 𝐵𝐵𝐵𝐵):
𝑽𝑽 = 𝑹𝑹 ⋈𝑨𝑨𝒊𝒊𝜣𝜣𝜣𝜣𝜣𝜣 𝑺𝑺 = 𝝈𝝈𝑨𝑨𝒊𝒊𝜣𝜣𝜣𝜣𝜣𝜣 (𝑹𝑹×𝑺𝑺)
§ Bemerkungen:
(1) SpeziellQ ='=':Gleichverbund(Equi-Join)
(2) EinGleichverbundzwischenRundSheißtverlustfrei(lossless
join),wennalleTupelvonRundSamVerbundteilnehmen.
DieinverseOperationProjektionerzeugtdannwiederRundS.
(3) Joins sindkommutativundassoziativ
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
41
Gleichverbund- Beispiel
§ AnwendungvonABT⋈ABT.ANR=PERS.ANR PERS
ABT
ANR
ANAME
AORT
K51
K53
K55
Planung
Einkauf
Vertrieb
KL
F
F
R=ABT⋈ PERS
➥
PERS
PNR
ALTER
ANR
406
123
829
574
47
32
36
28
K55
K51
K53
K55
ANR
ANAME
AORT
PNR
ALTER
K51
K53
K55
K55
Planung
Einkauf
Vertrieb
Vertrieb
KL
F
F
F
123
829
406
574
32
36
47
28
Fremdschlüssel
ANR'
K51
K53
K55
K55
verlustfrei:pANR,ANAME,AORT (R)=ABT;pPNR,ALTER,ANR‘ (R)=PERS
§ Wäreverlustbehaftet,wennTupel inABToderPERSkeine
Verbundpartnerfinden
• z.B.(K56,Finanzen,M)inABToder(471,63,-)inPERS
➥ p alsUmkehroperationführtnichtaufABToderPERS
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
42
NatürlicherVerbund(NaturalJoin)
§ Grob:GleichverbundüberallegemeinsamenAttributeund
anschließendeProjektion(gemeinsameAttributewerdennur
einmalübernommen)
§ Definition
• gegeben:𝑅𝑅(𝐴𝐴1, 𝐴𝐴2, … 𝐴𝐴𝐴𝐴, 𝐵𝐵r , … , 𝐵𝐵𝑘𝑘), 𝑆𝑆(𝐵𝐵r , … , 𝐵𝐵𝑘𝑘, 𝐶𝐶1 … , 𝐶𝐶𝑛𝑛)
o.B.d.A.:𝐵𝐵𝑖𝑖 sindgleichbenannt(sonst.Umsortierung)
• NatürlicherVerbund zwischenRundS:
𝑹𝑹 ⋈ 𝑺𝑺 = p𝑨𝑨𝟏𝟏,…,𝑨𝑨𝒎𝒎 ,𝑹𝑹.𝑩𝑩𝟏𝟏,…,𝑹𝑹.𝑩𝑩𝒌𝒌 ,𝑪𝑪𝟏𝟏,…,𝑪𝑪𝒏𝒏 (s
𝑹𝑹.𝑩𝑩𝟏𝟏 @𝑺𝑺.𝑩𝑩𝟏𝟏 ∧⋯∧(𝑹𝑹.𝑩𝑩𝒌𝒌 @𝑺𝑺.𝑩𝑩𝒌𝒌 ) (𝑹𝑹´𝑺𝑺))
§ Bemerkungen:
• ⋈ ohneJoinbedingung =ZeichenfürNaturalJoin
• gemeinsameAttributesinddurchNamensübereinstimmung
gegeben(nichtdurchPK/FK-Paare!)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
43
NatürlicherVerbund- Beispiel
Anwendungvon𝑨𝑨𝑨𝑨𝑨𝑨 ⋈ 𝑷𝑷𝑷𝑷𝑷𝑷𝑷𝑷
ABT
ANR
ANAME
AORT
K51
K53
K55
Planung
Einkauf
Vertrieb
KL
F
F
AP=ABT⋈ PERS
PERS
PNR
ALTER
ANR
406
123
829
574
47
32
36
28
K55
K51
K53
K55
ANR
ANAME
AORT
PNR
ALTER
K51
K53
K55
K55
Planung
Einkauf
Vertrieb
Vertrieb
KL
F
F
F
123
829
406
574
32
36
47
28
verlustfrei?
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
44
ÄußererVerbund(Outer Join)
§ Join(auchInnerJoingenannt)istnichtimmerverlustfrei
§ Ziel:VerlustfreierVerbundsollerzwungenwerden
Beispiel:BeiR⋈ SsollenauchTeilobjektealsErgebnisgeliefert
R
werden
S
§ Ansatz
• Input-Tupel,diekeinenJoin-Partnerhaben,werdentrotzdem
übernommen
• diefehlendenPartnerattributewerdenmitNullwertenbelegt
§ ÄußerernatürlicherVerbund⟗ (full outer join)
R
A
B
C
a1
a2
b1
b2
c1
c2
Informationssysteme2017
⟗
S
C
D
E
c1
c3
d1
d2
e1
e2
=
ERG
A
B
C
D
E
a1
a2
-
b1
b2
-
c1
c2
c3
d1
d2
e1
e2
Kapitel3:GrundlagendesRelationenmodells
45
ÄußererVerbund- Varianten
§ LinkeräußererVerbund⟕
• linkeArgumentrelationbleibtverlustfrei
R
A
B
C
a1
a2
b1
b2
c1
c2
⟕
S
C
D
E
c1
c3
d1
d2
e1
e2
=
ERG
A
B
C
D
E
a1
a2
b1
b2
c1
c2
d1
--
e1
--
A
B
C
D
E
a1
--
b1
--
c1
c3
d1
d2
e1
e2
§ RechteräußererVerbund⟖
• rechteArgumentrelationbleibtverlustfrei
R
A
B
C
a1
a2
b1
b2
c1
c2
⟖
S
C
D
E
c1
c3
d1
d2
e1
e2
=
ERG
§ ÄußererTheta-Join,GleichverbundmitVariantenanalogdefiniert
§ RechterundlinkeräußererVerbundsindnichtkommutativ
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
46
WeitereJoin Typen:Semi-Joins
§ Idee:FindealleTupelderlinkenRelation,dieJoinpartner inder
rechtenRelationhaben.
𝐿𝐿 ⋉ 𝑅𝑅 = 𝜋𝜋ℒ (𝐿𝐿 ⋈ 𝑅𝑅)
R
A
B
C
a1
a2
b1
b2
c1
c2
⋉
S
C
D
E
c1
c3
d1
d2
e1
e2
C
D
E
c1
c3
d1
d2
e1
e2
• 𝐿𝐿 ⋊ 𝑅𝑅istanalogdefiniert.
R
A
B
C
a1
a2
b1
b2
c1
c2
⋊
S
=
=
ERG
ERG
A
B
C
a1
b1
c1
C
D
E
c1
d1
e1
• Esgilt:𝐿𝐿 ⋉ 𝑅𝑅 = 𝑅𝑅 ⋊ 𝐿𝐿
• allerdingssindSemi-Joins genauwiedielinkenundrechten
äußerenJoins nicht kommutativ!
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
47
WeitereJoinTypen:Anti-Joins
§ Idee:FindealleTupel,fürdiekeinJoinpartner existiert.
• Anti-Join vonLmitR
R
A
B
C
a1
a2
b1
b2
c1
c2
⊳
S
C
D
E
c1
c3
d1
d2
e1
e2
C
D
E
c1
c3
d1
d2
e1
e2
=
ERG
A
B
C
a2
b2
c2
C
D
E
c3
d2
e2
• Anti-Join vonRmitL
R
A
B
C
a1
a2
b1
b2
c1
c2
Informationssysteme2017
⊲
S
=
ERG
Kapitel3:GrundlagendesRelationenmodells
48
Division
§ Ziel
• BeantwortungvonFragen,beideneneine„ganzeRelation“zur
Qualifikationherangezogenwird
Beispiel:WelcheSchauspielerhabenalleRollengespielt?
• SimulationdesAllquantors è einTupelaus𝑅𝑅 stehtmitallen
Tupeln aus𝑆𝑆 ineinerbestimmtenBeziehung
§ DefinitionderDivision:
• Sei𝑅𝑅 vomGradrund𝑆𝑆 vomGrads,r>sunds¹ 0
𝑆𝑆-Attribute⊆ 𝑅𝑅-Attribute,𝑡𝑡 sei(r- s)-Tupel,𝑢𝑢 seis-Tupel
• Danngilt:𝑹𝑹¸𝑺𝑺 = {𝒕𝒕|"𝒖𝒖Î𝑺𝑺:(𝒕𝒕|𝒖𝒖Î𝑹𝑹)}
§ BeschreibungderDivisionmitdenGrundoperationen
𝑅𝑅¸𝑆𝑆 = p𝓡𝓡ë𝓢𝓢 (𝑅𝑅)-p𝓡𝓡ë𝓢𝓢 ((p𝓡𝓡ë𝓢𝓢 (𝑅𝑅)´𝑆𝑆)-𝑅𝑅)
Esgilt:(𝑅𝑅´𝑆𝑆)÷𝑆𝑆 = 𝑅𝑅
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
49
Division- Beispiel
Datenbank:
DARSTELLER
PNR
P1
P1
P2
P3
P3
P3
ROLLE
FIGUR
A-Jahr…
FIGUR
TITEL
Faust
Nathan
Werther
Faust
Nathan
Werther
1999
1998
1997
1998
1999
1998
Faust
Nathan
Werther
Faust
NathanderWeise
DieLeiden…
R-Typ
Frage:WelcheSchauspieler habenalleRollengespielt?(pPNR,FIGUR(DA))÷ (pFIGUR(RO))
Auswertung:
pPNR,FIGUR(DA)
PNR
P1
P1
pPNR,FIGUR(DA)
PNR
P2
pPNR,FIGUR(DA)
PNR
P3
P3
P3
Informationssysteme2017
FIGUR
Faust
Nathan
?⊇
pFIGUR(RO)
FIGUR
Faust
Nathan
Werther
FIGUR
Werther
FIGUR
Ergebnis
Faust
Nathan
Werther
Kapitel3:GrundlagendesRelationenmodells
PNR
P3
50
AnfragedarstellungalsOperatorbaum
§ AnstellekomplexgeschachtelterAusdrücke(inline)kannaucheine
(oftübersichtlicher)Baumdarstellungverwendetwerden
§ Beispiel:
𝝅𝝅PNR, NAME, ANAME, AORT
⋈
σGEHALT>50000
PERS
Informationssysteme2017
ABT
Kapitel3:GrundlagendesRelationenmodells
51
Anfrageoptimierung- Motivation
ABT
ANR
ANAME
AORT
K51
K53
K55
Planung
Einkauf
Vertrieb
Kaiserslautern
Frankfurt
Frankfurt
PERS
PNR
NAME
ALTER
GEHALT
ANR
MNR
406
123
829
574
Coy
Müller
Schmid
Abel
47
32
36
28
50700
43500
45200
36000
K55
K51
K53
K55
123
777
123
Anfrage: Finde alle Angestellten (PNR, ALTER, ANAME), die in einer Abteilung in
Frankfurt arbeiten und zwischen 30 und 34 Jahre alt sind.
p
s
3 mögliche Lösungen
PNR, ALTER, ANAME
AORT='F'
p PNR, ALTER, ANAME
s ALTER ³ 30 Ù ALTER £
s
´
⋈
PERS
⋈
34
ABT.ANR=PERS.ANR
Informationssysteme2017
PNR, ALTER, ANAME
s ALTER ³ 30 Ù ALTER £
34
ABT
p
ABT
Ù AORT='F'
PERS
Kapitel3:GrundlagendesRelationenmodells
s
AORT='F'
ABT
s
ALTER ³ 30 Ù
ALTER £ 34
PERS
52
Relationenalgebra - Optimierung
§ Relationenalgebraische FormulierungenspezifizierenAusführungsreihenfolge (prozeduraleElemente)
➥ äquivalenteUmformungenmöglich
§ Problem
• gegeben:AusdruckderRelationenalgebra (RA)
• gesucht: äquivalenter,möglichsteffizientauszuführender
AusdruckderRelationenalgebra
§ HierbeikommenÄquivalenzregelnzumEinsatz,dieBeschreiben,
wieOperatoreninnerhalbeinesAusdrucksverschobenwerden
können.
§ SpäterimKapitelüberAnfrageverarbeitungundOptimierung
betrachtenwirHeuristikenzurAnwendungderRegeln,um
kostengünstigere"Anfragepläne"zufinden.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
53
Rewrite-RegelnderRelationenalgebra
ÄquivalenzvonAusdrückenmitRelationenRi
1. KommutativesGesetzfürProdukt,Verbund,Schnitt,Vereinigung
• R1 ´ R2 º R2 ´ R1
• R1 ⋈F R2 º R2 ⋈F R1
• R1∩ R2 º R2 ∩R1
• R1∪R2 º R2 ∪ R1
2. AssoziativesGesetzfürProdukt,Verbund,Schnitt,Vereinigung
•
•
(R1´ R2 )´ R3 º R1´ (R2 ´ R3 )
(R1⋈F1R2 )⋈F2R3 º R1⋈F1(R2 ⋈F2R3 )
• (R1∩ R2 )∩ R3 º R1∩ (R2 ∩ R3 )
• (R1∪ R2 )∪ R3 º R1∪ (R2 ∪ R3 )
3. FolgenvonProjektionen(Eliminieren/GenerierenvonProjektionen)
• pA,B,C(pA,B,C,…,Z(R))º pA,B,C(R)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
54
Rewrite-RegelnderRelationenalgebra(2)
4.FolgenvonSelektionenvs.KonjunktioneninBedingungen
• sF1(sF2(R))º sF1Ù F2(R)
• Da(F1 Ù F2 º F2 Ù F1):sF1(sF2(R))º sF2 (sF1(R))
5.VertauschungvonSelektionenundProjektionen
• FenthältnurAttributeausA...Z:
sF(pA,…,Z(R))º pA,…,Z(sF(R))
• wennFauchAttributeausB1…Bm enthält:
pA,…,Z(sF(R))º pA,…,Z(sF(pA,…,Z,B1,...,Bm(R)))
6.VertauschungvonSelektionundProdukt(bzw.Verbund)
• FenthältnurAttributeausR1:sF(R1 ´ R2)º sF(R1)´ R2
• allgemeiner:F=F1 Ù F2 Ù F3
F1:nurAttributeausR1,F2:nurAttributeausR2,F3:beides
sF(R1 ´ R2)º sF3 (sF1(R1)´ sF2(R2))ºsF1(R1)⋈F3 sF2(R2)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
55
Rewrite-RegelnderRelationenalgebra (2)
7.VertauschungvonSelektionenundMengenoperationen
(SelektionistdistributivmitMengenoperationen)
• sF(R1∪R2) º sF(R1)∪sF(R2)
• sF(R1∩R2) º sF(R1) ∩sF(R2)
• sF(R1 - R2) º sF(R1)- sF(R2)
8.VertauschungvonProjektionundVereinigung
(ProjektionistdistributivmitVereinigung)
• pA(R1∪R2) º pA(R1)∪pA(R2)
9.VeränderungvonBedingungen(à DeMorgansGesetz)
• ¬(p1 ∨ p2)º ¬p1 ∧ ¬p2
• ¬(p1 ∧ p2)º ¬p1 ∨ ¬p2
à erlaubtanschließendAufbrechen/VerschiebenvonSelektionen
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
56
ErweiterungenderRelationalenAlgebra
WirbetrachtennuneinigepraxisrelevanteErweiterungender
relationalenAlgebra.
§ Aggregation
§ Multimengen
§ VerschiedeneweitereOperatoren(Duplikateliminierung,
Sortierung,erweiterteProjektion)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
57
GruppierungundAggregation
§ Ermittlungvon"verdichteten"Daten(Summe,Durchschnitt,..)
• Schritt1(optional):BildeGruppenvonTupelnaufgrund
WertegleichheitvonAttributen(z.B.PERSnachANRgruppiert)
• Schritt2:WendefürjedeGruppeAggregatfunktion(en)an(z.B.
AVG(Gehalt)umDurchschnittsgehaltzuberechnen)
• Ergebnis:FürjedeGruppegenaueinTupel(Zusammenfassung)
a
a
a
b
Gruppierung
b
b
b
a
b
c
b
b
c
Aggregation
c
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
58
Gruppierung/Aggregation:𝛾𝛾 - Operator
§ 𝛾𝛾 – OperatorermöglichtSpezifikationvon
• Gruppierungsattributen
• AggregatfunktionenmitzuaggregierendenAttributen
- optionaleBenennungdesresultierendenErgebnisattributs
§ Genauer:
• geg.Relation𝑅𝑅 mitAttributen𝐴𝐴 = {𝐴𝐴1, … , 𝐴𝐴𝐴𝐴}
• G=𝜸𝜸𝑮𝑮𝑮𝑮;𝑨𝑨𝑨𝑨 𝑹𝑹mit
- Gruppierungsattributen𝐺𝐺𝐺𝐺 ⊆ 𝐴𝐴
- Aggregatfunktionen𝐴𝐴𝐴𝐴 = 𝐴𝐴𝐴𝐴1, … 𝐴𝐴𝐴𝐴𝑘𝑘 mit
𝐴𝐴𝐴𝐴𝑖𝑖 = 𝐴𝐴𝐴𝐴𝑖𝑖 ß 𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝑆𝑆𝑆𝑆𝑆𝑆 𝐴𝐴𝐴𝐴𝐴𝐴 𝑀𝑀𝑀𝑀𝑀𝑀 𝑀𝑀𝑀𝑀𝑀𝑀 (𝐴𝐴𝑖𝑖)
- 𝐴𝐴𝐴𝐴𝑖𝑖 istNamedesentstehendenAttributesfürAF-Wert(optional)
- zusätzlichCOUNT(*)erlaubtà AnzahlderTupelinGruppe
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
59
Gruppierung/Aggregation:𝛾𝛾 - Operator(2)
§ DieAttributeinGAbeschreibenwiedieTupelderRelationR
gruppiertwerden.
§ ErgebnisrelationenthältfürjedevorkommendeWertekombination
vonGAeinTupel
• TupelhältGA-WerteundberechneteAF-Wertenfürjede
Gruppe
• Gruppe=alleTupelinRmitdengleichenGA-Werten
§ FallsGA = ∅ wirdaufdergesamtenRelation(einegroßeGruppe)
aggregiert.ErgebnisbestehtindiesemFallnurausden
berechnetenAF-Werten(1Tupel).UndGA = ∅ wirdaucheinfach
weggelasseninderAnfrage.
§ Beispiele:
• 𝛾𝛾=<J;¶P?Q←=UP P?Q=HI (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
• 𝛾𝛾=UP
P?Q=HI
Informationssysteme2017
(𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
Kapitel3:GrundlagendesRelationenmodells
60
DerErweiterteProjektions-Operator
§ WoinderursprünglichenDefinitionderProjektionnur
Attributnamenangegebenwerden,alsop=r,…,=ß (𝑅𝑅)lassenwirin
derErweiterungdesProjektions-OperatorsauchFunktionenzu,die
einodermehrereAttributealsArgumentehaben.
§ AusgabederFunktionenkönnenbenanntwerden.
§ Beispiel:
𝜋𝜋S<J,>P?Q←P?Q=HI⁄rs, (𝑃𝑃𝑃𝑃𝑃𝑃𝑃𝑃)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
61
Sortierung
𝜏𝜏H 𝑅𝑅
isteineListevonTupeln ausRelation𝑅𝑅,geordnetnachden
Attributen,diein𝐿𝐿 genanntsind.
• InlexikographischerOrdnung.
• OptionalmitAngabederSortierung:
- AbsteigendoderAufsteigend,proAttribut.
- Default:Aufsteigend.
𝜏𝜏=,p 𝑅𝑅
B
C
A
2
3
3
1
3
4
3
2
4
4
A
1
Informationssysteme2017
𝜏𝜏p,™ 𝑅𝑅
B
C
A
2
1
1
1
2
3
1
1
3
4
4
4
𝜏𝜏↓™ 𝑅𝑅
B
C
3
4
4
4
4
4
1
2
3
4
3
2
1
Kapitel3:GrundlagendesRelationenmodells
62
DuplikateindenDaten:Multimengen(Bags)
§ DiebislangbetrachtetenAnfragesprachenarbeitenaufMengen,im
SinnevonRelationen,dieMengenvonTupeln sind.
§ EswerdenbeidieserMengen-BetrachtungimplizitDuplikate
entfernt.InsbesonderenacheinerProjektionoder
MengenvereinigungtretenoftDuplikateauf.
§ DatenbanksystemearbeiteninderRegelmitMultimengen(aka.
Bags),d.h.dieRelationen(Tabellen)dürfenauchDuplikate
enthalten.
§ ZumBeispielsind{a,b,b}und{a,b}Multimengen,wobeiletztere
aucheineMengeist.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
63
OperationenaufMultimengen(Bags)
§ Selektion,Join,Kreuzprodukt,Umbenennung(sowieso),
Aggregation/Gruppierung,Sortierungfunktionierenauchfür
Multimengen.
§ ProjektionerfordertnurVerzichtaufDuplikateliminierung (einfach)
§ WasistmitMengendifferenz,Mengendurchschnitt,
MengenvereinigungfürMultimengen?
FüreinBag𝐵𝐵 bezeichnenwirmit#𝐵𝐵(𝑥𝑥)dieAnzahldesAuftretens
vonElement𝑥𝑥 in𝐵𝐵 (aka.Multiplizität).
Wirhabendann
• Kardinalität vonBagBals 𝐵𝐵 = ∑ÆØ #𝐵𝐵(𝑥𝑥)
• 𝐴𝐴 ⊆ 𝐵𝐵 als#𝐴𝐴(𝑥𝑥) ≤ #𝐵𝐵 𝑥𝑥 ∀𝑥𝑥
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
64
Vereinigung,Differenz,SchnittmengefürMultimengen
§
§
§
§
Vereinigung:#(𝑋𝑋 ∪ 𝑌𝑌)(𝑧𝑧) = #𝑋𝑋(𝑧𝑧) + #𝑌𝑌(𝑧𝑧)
Schnittmenge:#(𝑋𝑋 ∩ 𝑌𝑌)(𝑧𝑧) = min(#𝑋𝑋 𝑧𝑧 , #𝑌𝑌(𝑧𝑧))
Differenz:#(𝑋𝑋 − 𝑌𝑌)(𝑧𝑧) = max(0, #𝑋𝑋 𝑧𝑧 − #(𝑌𝑌))
Anmerkung:
DieDefinitionderVereinigungfürBagsistnichtidentischzuder
VereinigungvonBagsausderMathematik,welchedefiniertwäre
als#(𝑋𝑋 ∪EΩØ 𝑌𝑌)(𝑧𝑧) = max(#𝑋𝑋 𝑧𝑧 , #𝑌𝑌(𝑧𝑧)).MitdieserDefinition
würdenauchdieRegelnfürMengenbeiBagsgelten.
DieobenangegebeneDefinition#(𝑋𝑋 ∪ 𝑌𝑌)(𝑧𝑧) = #𝑋𝑋(𝑧𝑧) + #𝑌𝑌 𝑧𝑧
folgtjedochgenaudemwasinDB-Systemen(siehespäterSQL)
oderProgrammiersprachenbenutztwird,imSinneder
"Konkatenation"vonBags.𝑋𝑋 ∪EΩØ 𝑌𝑌 ≡ (𝑋𝑋 − 𝑌𝑌) ∪ 𝑌𝑌
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
65
Beispiel:VerletzteRegelfürBags
𝑅𝑅 ∩ 𝑆𝑆 ∪ 𝑇𝑇 ≡ 𝑅𝑅 ∩ 𝑆𝑆 ∪ 𝑅𝑅 ∩ 𝑇𝑇 giltfürMengen,abernichtfürBags
SeienR,SundTjeweilsdasBag{1}
DannistdielinkeSeite:𝑆𝑆 ∪ 𝑇𝑇 = 1, 1 ; 𝑅𝑅 ∩ 𝑆𝑆 ∪ 𝑇𝑇 = {1}.
UnddierechteSeite: 𝑅𝑅 ∩ 𝑆𝑆 = 𝑅𝑅 ∩ 𝑇𝑇 = 1
𝑅𝑅 ∩ 𝑆𝑆 ∪ 𝑅𝑅 ∩ 𝑇𝑇 = 1 ∪ 1 = 1, 1 ≠ {1}
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
66
OperatorfürDuplikateliminierung
𝛿𝛿(𝑅𝑅)
ergibteineRelation,inderjedesTupeleinmalauftrittfürTupeldiein
Reinmalodermehrfachauftreten.
A
𝑅𝑅
𝛿𝛿 𝑅𝑅
B
A
B
1
2
1
2
3
4
3
4
1
2
1
2
TechnischgesehenistderDuplikateliminierungsoperator redundant.
FürRelation𝑅𝑅(𝐴𝐴1, 𝐴𝐴2, … , 𝐴𝐴𝐴𝐴)istdieRelationδ(𝑅𝑅) äquivalentzu
𝛾𝛾=r,=s,…,=¡ (𝑅𝑅).D.h.umDuplikatezueliminierengruppierenwirnach
allenAttributen,ohneAnwendungeinerAggregatfunktion.
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
67
Zusammenfassung
§ Relationenmodell
• Relationmit(atomaren)AttributenalszentraleDatenstruktur
• wertbasierteDarstellungvonBeziehungen(Fremdschlüssel)
• wohldefinierteAbbildungERà RM
§ Relationenalgebra
• Abgeschlossenheitbzgl.derAlgebraoperationen
• KlassischeMengenoperationen
• Relationenoperationen
• elementareundabgeleiteteOperatorenfürAnfragen
• komplexeRA-Ausdrücke,Schachtelung
• ÄquivalenzregelnalsBasisderalgebraischenOptimierung
§ Erweiterungen
• GruppierungundAggregation
• Multimengen,Duplikateliminierung,...
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
68
ANHANG:BEISPIELANFRAGEN
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
69
Beispiel-DB:BÜHNE
ER-Diagramm
n
n
Schauspieler
stelltdar
m
n
Rolle
1
hat
IstAutorvon
1
Drama
n
Dichter
1
IstKritikervon
Relationales Schema
SCHAUSPIELER (SP)
PNR
NAME
ROLLE (RO)
W-ORT . . .
FIGUR
TITEL
R-Typ . . .
DICHTER (DI)
AUTOR
G-ORT
DARSTELLER (DA)
PNR
FIGUR
Informationssysteme2017
A-JAHR
A-ORT
THEATER
G-JAHR
DRAMA (DR)
TITEL AUTOR
Kapitel3:GrundlagendesRelationenmodells
KRITIKER
U-ORT
U-JAHR
70
Beispiel-DB:Anfragen
Q1:FindealleSchauspieler(NAME),dieeinmalden’Faust’(dieFigur’Faust’)
gespielthaben.
πNAME (σFIGUR=Faust (SP⋈ DA))
Q2:FindealleSchauspieler(NAME),dieeinmalim’Faust’(imDrama
’Faust’)mitgespielthaben.
πNAME (σTITEL=Faust (SP⋈ DA⋈ RO))
Q3:FindealleSchauspieler(NAME),dieinDramenvonSchillermitgespielt
haben.
πNAME (SP⋈ DA⋈ RO⋈ (σAUTOR=Schiller DR))
Q4:WelcherDichteristSchauspieler?
SP⋈SP.NAME=DI.AUTOR DI
Q5:FindealleDramenmitAutoren (TITEL,AUTOR,G-JAHR),dienach1800
uraufgeführtwurden.
πTITEL, AUTOR, G-JAHR ((σU-JAHR>1800DR)⋈ DI)
Q6:ListealleDramenmitihrenKritikern,dieinWeimargeborenwurden,
(mitTITEL,KRITIKER)auf.
πTITEL, KRITIKER ((σG-ORT=WeimarDI)⋈DI.AUTOR=DR.KRITIKER DR)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
71
Beispiel-DB:Anfragen(2)
Q7:FindedieSchauspieler(PNR),dienie gespielthaben.
πPNR SP- πPNRDA
Q8:FindedieSchauspieler(PNR),dienurFaustoderWallenstein gespielt
haben.
πPNR (σFIGUR=Faust∨ FIGUR=Wallenstein DA)- πPNR (σFIGUR≠Faust ∧ FIGUR≠Wallenstein DA)
Q9: FindealleSchaupieler (NAME),diealle RolleninDramenvonGoethe
gespielthaben.
πNAME(SP⋈(πPNR,FIGURDA÷πFIGUR(RO⋈ (σAUTOR=GoetheDR))))
Q10:FindealleSchauspieler(NAME),diealle NarrenrollenamPfalztheater
gespielthaben.
πNAME(SP⋈((πPNR,FIGUR(σTHEATER=PfalztheaterDA))÷ (πFIGUR(σR-TYP=NarrRO))))
Q11:ErmittlefürjedeFigur,wieoftsievonSchauspielern,diein
Kaiserslauternwohnen,gespieltwurde
𝛾𝛾FIGUR;ANZßCOUNT(*)((σW-ORT=KLSP)⋈ DA)
Informationssysteme2017
Kapitel3:GrundlagendesRelationenmodells
72