Document

2Relationenmodell
InformationssystemefürIngenieure
Herbstsemester 2016
“There is nothing more practical than a good theory.”
-- James C. Maxwell
“In theory, there is no difference between practice and theory.
But in practice, there is.”
-- Jan L.A. van de Snepscheut
R.Marti
ZieldesKapitels
• KenntnisdesRelationenmodells,einemDatenmodell,welchesunabhängigvonComputerHardwareundSoftware(inkl.physischenSpeicherstrukturen)definiertist,insbesondereder
wesentlichstenBegriffefür
–
dieDeklarationvonRelationenalsMengevonDatensätzen(Tupeln)
–
dieDeklarationwichtigerIntegritätsbedingungen zurErhöhungderDatenqualität
–
dieDefinitionvonAusdrückenderRelationenalgebra,dieeserlaubt,OperationenaufMengenvonTupeln
auszuführen,diezurBeantwortungvonAnfragenverwendetwerdenkönnen
GrundfürdieVermittlungdiesesStoffs
• DasRelationenmodellbildetdietheoretischeGrundlagerelationalerDatenbanken,diein
derPraxissehrgrosseVerbreitunghaben.
• EtlicheIdeendesRelationenmodellsflossen/fliessenauchin"modernere"
Datenbanksystemeein,u.a.inobjektorientierteDatenbanksysteme,XMLDaten,
sowieinAnfragesprachenwieHiveQLauf"BigData"File-SystemenwieHadoop
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
2
Grundlage:MathematischeRelationenundTupel
• Das Relationenmodell(auchrelationalesModell genannt)basiertaufdemKonzeptder
mathematischenRelation:
• Inder"reinenMathematik"isteinen-stelligeRelation R (relation of arity n)
eineTeilmengedeskartesischenProduktesvonn (nichtnotwendigerweiseverschiedenen)
MengenD1, D2, ... , Dn (mitn > 0):
R Í D1 ´ D2 ´ ... ´ Dn
RelationenderStelligkeit n = 1 heissenauchunär(unary).
RelationenderStelligkeit n = 2 heissenauchbinär(binary).
RelationenderStelligkeit n = 3 heissenauchternär(ternary).
• EinElement<d1, d2, ... , dn>Î R (mit di Î Di für 1 ≤ i ≤ n) wirdInder"reinen
Mathematik"als(n-)Tupel bezeichnet.
• Achtung:Indieser"reinenMathematikNotation"spieltdieReihenfolgederMengenDi
bzw.ihrerElementedi eineRolle!
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
3
ZurGeschichtedesRelationenmodells
• FürDatenbankenwurdedasRelationenmodell 1969ineinemIBM ResearchReportvom
englischenMathematikerEdgarF.Codd (1923−2003)vorgestelltund
1970ineinemangesehenenJournalpubliziert:
ARelationalModelforLargeSharedDataBanks,CommunicationsoftheACM,1970.
• DasPapierhat– zusammenmitCodd'sweiterenArbeiten1971,1972und1974– die
Datenbank-Technologierevolutioniert:
– DasRelationenmodellwardasersteDatenmodell,dasunabhängigvonderphysischen
Speicherungdefiniertwurde(vgl.physischeDatenunabhängigkeitinKap1).
– Codd'sAbfragesprache(Alpha)warwedersatzorientiertnochprozedural(wieheutenoch
praktischalleProgrammiersprachen),sondernmengenorientiert unddeklarativ.
• DieIdeewurdewegenZweifelnanderRealisierbarkeiteinesperformantenrelationalenDBMS
skeptischaufgenommen.ZudemwollteIBM ihrProduktIMS/DB nichtkonkurrenzieren.
• Mitteder70-erJahreexisitiertenerstePrototypenrelationalerSysteme:
PRTV undSYSTEM/R beiIBM ,INGRES anderUniversityofCaliforniainBerkeley
• Codderhielt1981denACM TouringAward.(≈"Nobel-PreisfürInformatiker")
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
4
Definition:Domäne
• Domäne (auchWertebereich,engl.:domain):
EineMengevon(atomaren)Werten,wobeizujederDomäneimplizitauchder
spezielleWertnull gehört,derfürfehlendeWertesteht.
InderTheoriekönntenwirunsz.B.folgendeDomänenvorstellen:
SwissCities = { Aarau, Basel, Bern, ... , Zürich }
BodyHeights = { 30, 31, 32, ... , 250 }
EyeColors = { Blue, Brown, Green, Grey }
InderPraxisentsprichteineDomänejedochimwesentlicheneinem
(unstrukturierten) DatentypeinerhöherenProgrammiersprache:
ganzeZahlen(Integer),
Gleitkommazahlen(Float)
ZeichenkettenvariablerLänge(Text)[auchString genannt]
DatumundZeitAngaben(Date,Time)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
5
Definitionen:(DB-)Relation,Attribut
Gegebenseienm nichtnotwendigerweiseverschiedeneDomänen D1, ... , Dm .
• Eine(Datenbank-) Relation R bestehtaus
1. einemRelationenschema sch(R) = { A1, ... , An } (mit n > 0)
wobeidieAi (mit 1 ≤ i ≤ n) Attribute heissenundjedemAttributAi
eineDomäneDj (mit 1 ≤ j ≤ m) zugeordnetwird:
dom(Ai) = Dj .[dom(Ai) bezeichnetdieDomänevonAi ]
DieAttributeeinerRelationsindverschieden,willheissen,siehaben
unterschiedliche(eindeutige)NameninnerhalbeinerRelation.
2. einerExtension(auchAusprägung,Instanz,Wert)ext(R) ,
fürdiezujedemZeitpunktgilt:
ext(R) Í dom(A1) ´ ... ´ dom(An)
DiesentsprichtdemKonzeptder"mathematischenRelation".
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
6
BeispieleinerRelation(1)
Attribut
(Domänenwerdenhiernichtgezeigt)
City
CityName
CountryCode Population
'Seoul'
'ROK'
10229262
'Mumbai'
'IND'
9925891
'Karachi'
'PK'
9863000
'Mexico?City'
'MEX'
9815795
'Sao?Paulo'
'BR'
9811776
Relationenschema:sch(City)
Ausprägung:ext(City)
KurznotationfürRelationenschemasch(City):
City( { CityName, CountryCode, Population } )
Esgilt:ext(City)
R.Marti
Í dom(CityName) ´ dom(CountryCode) ´ dom(Population)
= Text ´ Text ´ Integer
InformationssystemefürIngenieure2016 – 2Relationenmodell
7
BeispieleinerRelation(2)
ZweiDarstellungendergleichenRelationwieaufdervorangehendenSeite:
Links:
DieExtensioneinerRelationisteineMenge.
⇒ DieOrdnungderElementederExtension(sog.➛Tupel)istegal.
Rechts:
R.Marti
DasSchema einerRelationisteineMenge.
⇒ DieOrdnung der Attribute des Schemas ist (inderTheorie) egal.
InformationssystemefürIngenieure2016 – 2Relationenmodell
8
BeispieleinerRelation(3)
DasSchema einerRelationwirdimfolgendenalsunveränderlichbetrachtet.
InderPraxiskannsicheinSchemaüberdieZeitändern,wennauch"selten".
DieExtensioneinerRelationverändertsich(inallerRegel)überdieZeit.
SiekanninsbesondereauchkeineElementeodernureineinzigesElemententhalten.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
9
Achtung:DiessindkeineRelationen
AttributnamenmüsseninnerhalbeinerRelationeindeutigsein.
Borders
CountryCode CountryCode Length
'CH'
'D'
334
'CH'
'F'
573
'CH'
'I'
740
DoppelteElemente(Tupel)sind(in
derrelationalenTheorie) unzulässig.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
10
Definitionen:Tupel
• Tupel (tuple)
EinElementt derAusprägungext(R) einerRelationR:t Î ext(R).
• Beispiel:
City
CityName
CountryCode Population
...
...
'Mexico)City'
'MEX'
...
...
...
9815795
Tupel t
...
• AlsElementeinerRelationCity Í Text ´ Text ´ Integer inder"reinenMathematik"würde
diesesTupelals
< 'Mexico City', 'MEX', 9815795 >
dargestellt,dadieReihenfolgedortimGegensatzzuDB-RelationeneineRollespielt.
• Soferndiesnötigwird,bezeichnenwirdasobige(DB-)Tupelstattdessenwiefolgt:
< CityName: 'Mexico City', CountryCode: 'MEX', Population: 9815795 >
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
11
AbstraktesBeispieleinerRelation
Attribute
R
Tupel
A
B
1
'x'
2
'y'
3
'z'
(Relationen-)Schemasch(R)
RelationR
Ausprägung ext(R)
• Seit dasersteTupel(WerteingrünerSchrift)indieserDarstellungderRelationR.
Dannbezeichnett.A denAttributwert desAttributst.A vonTupelt .Esgilt:
t.A = 1 (und,analog,t.B = 'x' ).
(DieseNotationwirdineinigenderfolgendenDefinitionenverwendet.)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
12
BemerkungenzurNotation
Hinweis:ImBuchvonKemper&Eickler[KE] wirdteilweiseeineleichtunterschiedliche
Notationverwendet.Insbesondere:
• In[KE] enthältdieText-NotationfüreinSchemadieDomänen,z.B.
City : { [ CityName: Text, CountryCode: Text, Population: Integer ] }
anstellevon
City( { CityName, CountryCode, Population } )
• In[KE] wird- etwasunsauber- nichtzwischen(1)derRelationmitSchemaund
Ausprägung und(2)derAusprägungalleine unterschieden,z.B.stehtCity
sowohl fürsch(City) zusammenmitderAusprägungvonCity
wieauchfürdieAusprägungvonCity alleine.
IndiesemTextstehtCity fürSchema sch(City) mitAusprägung ext(City)
undext(City) fürdieAusprägungalleine.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
13
Attributwertemüssenatomarsein
RelationenmitstrukturiertenAttributen (wieMengen,ListenoderVektorenvon
Werten)sind verboten.
Student_illegal
FCode FacultyName
EnrolNo
Name
IT_Skills
'EC'
'Economy'
'10+250+7654' 'Scholes' {7'SQL'7}
'CS'
'Computer7Science'
'09+432+3456' 'Guttag' {7'Java',7'C',7Python'7}
'MA'
'Mathematics'
'12+777+4227' 'Gauss'
'CS'
'Computer7Science'
'11+839+5288' 'Cattell' {7'Java',7'SQL'7}
'EE'
'Electrical7Engineering' '10+773+2290' 'Myers'
{7}
{7'C',7'C++'7}
DerartigeunzulässigenRelationenheissenunnormalisiert.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
14
Normalisierung:Grundideeder1.Normalform(1NF)
BeimehrwertigenAttributen(wiez.B.IT_Skills) mussjederEinzelwertseparat
ineinemTupelgespeichertwerden.
Student_1NF
FCode FacultyName
EnrolNo
Konsequenzen:
'EC'
'Economy'
'10+250+7654' 'Scholes' 'SQL'
- Wiederholungen
(Redundanz)
'CS'
'Computer<Science'
'09+432+3456' 'Guttag' 'Java'
'CS'
'Computer<Science'
'09+432+3456' 'Guttag' 'C'
- Notwendigkeitvon
'CS'
'Computer<Science'
'09+432+3456' 'Guttag' 'Python'
Nullwerten (null)
'MA'
'Mathematics'
'12+777+4227' 'Gauss'
'CS'
'Computer<Science'
'11+839+5288' 'Cattell' 'Java'
'CS'
'Computer<Science'
'11+839+5288' 'Cattell' 'SQL'
'EE'
'Electrical<Engineering' '10+773+2290' 'Myers'
'C'
'EE'
'Electrical<Engineering' '10+773+2290' 'Myers'
'C++'
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
Name
IT_Skill
null
15
WeitereNormalisierung
Redundanz(undwennmöglichauchNullwerte) solltenvermiedenwerden!
Faustregeln(vgl.KapitelDatenbank-Entwurf):
- EigenschaftenvonObjekten,die"fürsichalleinstehenkönnen"(z.B.Faculty),
gehörenineineeigeneRelation
- jedemehrwertigeEigenschaftvonObjekten(z.B.IT_Skills)gehörtebenfalls
ineineeigeneRelation
Faculty
Student
IT_Skill
FCode FacultyName
EnrolNo
'EC'
'Economy'
'10$250$7654' 'Scholes' 'EC'
'10$250$7654' 'SQL'
'CS'
'Computer/Science'
'09$432$3456' 'Guttag' 'CS'
'09$432$3456' 'Java'
'MA'
'Mathematics'
'12$777$4227' 'Gauss'
'09$432$3456' 'C'
'EE'
'Electrical/Engineering'
'11$839$5288' 'Cattell' 'CS'
'09$432$3456' 'Python'
'10$773$2290' 'Myers'
'11$839$5288' 'Java'
Name
FCode
'MA'
'EE'
EnrolNo
IT_Skill
'11$839$5288' 'SQL'
ZusammenhängewerdendurchgleicheAttributwertein
verschiedenenRelationendargestellt(➛Fremdschlüssel):
- AttributFCode inFaculty undStudent
- AttributEnrolNo inStudent undIT_Skill
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
'10$773$2290' 'C'
'10$773$2290' 'C++'
16
Nichtempfohlen:Text-AttributemitspeziellerSyntax
MehrwertigeAttribute(Mengen,ListenoderVektoren)könntenalsTextmit
einerspeziellenSyntaxabgelegtwerden,indemz.B.einzelneWertedurch
einspeziellesSymbol(wiez.B.dasZeichen'|')getrenntwerden.
Diesistzwartechnischmöglichundnichtgrundätzlichverboten,
aber nichtempfohlen,insbesonderedaesdieFormulierungvonAnfragen
(undÄnderungsoperationen)komplexermacht.
Student_not_recommended
R.Marti
FCode FacultyName
EnrolNo
Name
IT_Skills
'EC'
'Economy'
'10+250+7654' 'Scholes' 'SQL'
'CS'
'Computer<Science'
'09+432+3456' 'Guttag' 'Java|C|Python'
'MA'
'Mathematics'
'12+777+4227' 'Gauss'
'CS'
'Computer<Science'
'11+839+5288' 'Cattell' 'Java|SQL'
'EE'
'Electrical<Engineering' '10+773+2290' 'Myers'
''
'C|C++'
InformationssystemefürIngenieure2016 – 2Relationenmodell
17
Definitionen:Datenbank
• EineDatenbank bestehtauseinerMengevon(DB-)Relationen.
• AufderkonzeptionellenEbenewerdenjeweils"logischzusammengehörige"
EigenschafteneinesTypsvonObjektensoineinerRelationgespeichert,dass
(möglichst)keineRedundanzenauftreten.
• Beispiele:
- Universität:
RelationenfürStudierende,Räume,Vorlesungen,Prüfungen,…
- Bank:
RelationenfürKunden,Konten,Angestellte,Finanzinstrumente,…
- Internet-Buchladen:
RelationenfürBücher,Lager,Lieferanten,Kunden,Bestellungen,…
• AufexternerEbenekönnenauchRelationenauftreten,dieEigenschaften
verschiedenerObjekteineinerRelationenthalten(inkl.Redundanzen).
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
18
Integritätsbedingungen
• Integritätsbedingungen sindEinschränkungenaufdenDaten,diealleAusprägungen
derDatenbankjederzeit erfüllenmüssen
• ImrelationalenModellgibtesdiefolgendenKlassenvonIntegritäts-bedingungen:
– Schlüssel (keys) und Entitätsintegrität (entityintegrity)
– Domänenintegrität (domainintegrity)
– ReferentielleIntegrität (referentialintegrity)
• DieobigenIntegritätsbedingungenwerdenvondenmeistenDBMSsautomatisch
überwacht.
• DieDefinitionvonIntegritätsbedingungenfördertdieDatenqualitätinDBs.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
19
Definition:Superschlüssel
SeiR eineRelationmitdemRelationenschemasch(R) = { A1, ... , An }
• EineMengeK Í { A1, ... , An } vonAttributenheisstSuperschlüssel (superkey),
wennfürzweibeliebigeTupelt1, t2 Î ext(R) jederzeit
(alsofürjedemöglicheAusprägungvonR) gilt:
t1.K = t2.K Þ t1 = t2
Bemerkung:t1.K = t2.K bedeutet:"A Î K: t1.A = t2.A
MitanderenWorten:
-
-
WennfürzweibeliebigeTupeleinerRelationallesichentsprechenden
AttributwerteAi imSuperschlüsselK gleichsind,dannsinddiebeiden
Tupelgleich.
bzw.:DieAttributwertevonK reichenaus,umdieTupelinR eindeutig
zuidentifizieren.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
20
Definition:SchlüsselkandidatundPrimärschlüssel
SeiK Í { A1, ... , An } einSuperschlüsseleinerRelationR mitdem
Relationenschemasch(R) = { A1, ... , An }
• DerSuperschlüsselK heisstSchlüsselkandidat (candidatekey),
wenneskeineechteTeilmengevonK gibt,dieauchSuperschlüsselist.
MitanderenWorten:
Wenn eskeineechteTeilmengevonK gibt,welchedieTupelinR eindeutig
identifizieren,
bzw.wenn K einminimaler Superschlüsselist,
dann istK einSchlüsselkandidat.
• JedeRelationbesitztimmermindestenseinenSchlüsselkandidaten.
Frage:Warum?
• EinerderSchlüsselkandidateneinerRelationR (vonallenfallsmehreren)wirdals
Primärschlüssel ausgezeichnet.
Dieserdientals"primärerMechanismus",umTupeleinerRelationR zuidentifizieren.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
21
Beispiel:Superschlüssel,SchlüsselkandidatenundPrimärschlüssel
GegebenseieineRelationEmployee ( { EmpNo, FamName, FstName } )
DerVerantwortlichefürdasPersonalwesenerklärt:
- EmpNo isteineLaufnummer,neueAngestelltebekommendienächsthöhereNummer.
- FamName istderFamiliennameeiner/einesAngestellten.
- FstName istderVornameeiner/einesAngstellten.
- Fernerwirdniemandeingestellt,dereinenNamen(Familien- undVorname)eines
existierendenAngestelltenträgt. [Obdiessinnvollist,seidahingestellt...]
Superschlüssel sinddemnach:
{ EmpNo, FamName, FstName }
{ EmpNo, FamName }
{ EmpNo, FstName }
{ EmpNo }
ß
{ FamName, FstName }
(alleAttributebezeichnenTupelimmereindeutig)
(EmpNo alleinebezeichnetTupelbereitseindeutig)
(EmpNo alleinebezeichnetTupelbereitseindeutig)
minimalerSuperschlüssel,somitSchlüsselkandidat
(Superschlüsselmitnur1Attributistimmerminimal!)
ß minimalerSuperschlüssel,somitSchlüsselkandidat
(FamName undFstName alleinsindnichteindeutig)
Primärschlüssel istdemnachentweder{ EmpNo } oder{ FamName, FstName } .
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
22
Beispiel:SchlüsselkandidatenineinerKunden-DB
Bem.:Diefolgendene-mailAdressenundAdressensindfreierfunden...
Customers
CusNo CusFamName CusFstName MidIn Title Email
Street
No Zip
City
Ctry
'Fed:Pen'
null '801210'
'Littleton' 'USA'
234 'Skilling'
'Jeffrey'
'R'
null '[email protected]'
235 'Cassano'
'Joseph'
'J'
null '[email protected]' 'Subprime:St' 123 'W1D:1NN' 'London'
236 'Ospel'
'Marcel'
'L'
null '[email protected]'
'Paradiesstr'
45 '8832'
'Wollerau' 'CH'
237 'Bruggisser'
'Philippe'
null
null '[email protected]' 'Hunterweg'
67 '8000'
'Zürich'
'CH'
238 'Rosenfeld'
'Eric'
'R'
'Dr' '[email protected]'
890 '02108'
'Boston'
'USA'
239 'Scholes'
'Myron'
'S'
'Prof' '[email protected]'
'Quant:Rd'
'Nobel:Dr'
2 'M4B:1B3' 'Toronto'
'GB'
'CAN'
Schlüsselkandidaten:
{ CusNo }
"bydesign"– jederneueKundeerhälteine(unveränderliche)Laufnummer
{ Email }
"bydesign"– e-mailAdressensindweltweiteindeutig
wobeieinKundeseinee-mailAdresseüberdieZeitevt.ändert!
{ CusFamName, CusFstName, MidIn, Title, Street, No, Zip, Ctry }
EindeutigkeitderTupelistzwarobenerfüllt,aberwiesiehtdiesinZukunftaus?
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
23
Beispiel:PrimärschlüsselineinerKunden-DB
Bem.:Diefolgendene-mailAdressenundAdressensindfreierfunden...
Customers
CusNo CusFamName CusFstName MidIn Title Email
Street
No Zip
City
Ctry
'Fed:Pen'
null '801210'
'Littleton' 'USA'
234 'Skilling'
'Jeffrey'
'R'
null '[email protected]'
235 'Cassano'
'Joseph'
'J'
null '[email protected]' 'Subprime:St' 123 'W1D:1NN' 'London'
236 'Ospel'
'Marcel'
'L'
null '[email protected]'
'Paradiesstr'
45 '8832'
'Wollerau' 'CH'
237 'Bruggisser'
'Philippe'
null
null '[email protected]' 'Hunterweg'
67 '8000'
'Zürich'
'CH'
238 'Rosenfeld'
'Eric'
'R'
'Dr' '[email protected]'
890 '02108'
'Boston'
'USA'
239 'Scholes'
'Myron'
'S'
'Prof' '[email protected]'
'Quant:Rd'
'Nobel:Dr'
2 'M4B:1B3' 'Toronto'
'GB'
'CAN'
AlsPrimärschlüsselempfiehltessich,{ CusNo } zuverwenden,wasinderobigen
DarstellungderRelationCustomers durchUnterstreichung gekennzeichnetwurde.
Begründung:
- DerSchlüsselkandidat{ CusNo } bestehtnurauseinemAttribut.
- DieWertediesesSchlüsselkandidatenändernsichnicht.
Achtung:WertegelöschterTupelsolltennichtfürneueTupelwiederverwendetwerden.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
24
IntegrierteÜbung:SchlüsselkandidateninBooks
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
25
IntegrierteÜbung:SchlüsselkandidateninCourses
Courses
Annahme:
AlleStundeneinesFachs(Subject)müssenimmer
imgleichenRaum(Room)stattfinden.
DergesundeMenschenverstandlegtnocheine
weitereAnnahmenahe,diehiernichterwähntist.
R.Marti
Subject
Room WkDay StartHour
'DB$Systems' 'A,36'
'Tue'
9
'DB$Systems' 'A,36'
'Tue'
10
'DB$Systems' 'A,36'
'Thu'
9
'DB$Systems' 'A,36'
'Thu'
10
'DB$Design
'B,32'
'Tue'
9
'DB$Design'
'B,32'
'Tue'
10
'DB$Design
'B,32'
'Wed'
13
'DB$Design'
'B,32'
'Wed'
14
'Big$Data'
'A,36'
'Fri'
16
InformationssystemefürIngenieure2016 – 2Relationenmodell
26
IntegrierteÜbung:SchlüsselkandidateninExamResults
ExamResults
CrsTitle
R.Marti
StuEnrolNo Grade Semester
'DB$Systems' '10-111-2222'
'B'
'2012-2'
'DB$Systems' '10-999-8888'
'B'
'2012-2'
'DB$Systems' '09-333-4444'
'B'
'2012-2'
'Accounting' '12-555-5555'
'A'
'2013-1'
'Options'
'08-777-7777'
'B'
'2012-1'
'Options
'12-555-5555'
'C'
'2012-1'
'Accounting' '11-666-3333'
'A'
'2012-1'
'Accounting' '10-999-8888'
'D'
'2013-1'
InformationssystemefürIngenieure2016 – 2Relationenmodell
27
DomänenintegritätundEntitätsintegrität
• DieDomänenintegrität (domainintegrity)verlangt,dassalleAttributwerteausder
entsprechendenDomänestammen.
IneinemDBMS könnennebendemDatentypweitereBedingungenformuliertwerden,z.B.
EinschränkungaufMengewenigerWerteundinsbeseinVerbotvonnull-Werten.
• FüreinenPrimärschlüsselwirdzusätzlichverlangt,dassdessenAttributwertenicht
null seindürfen.
DiesePrimärschlüsselbedingungwirdauchals Entitätsintegrität
(entityintegrity)bezeichnet.
FormallautetdieBedingung:
DieAttributedesPrimärschlüsselsK einerRelationR dürfennieden
Nullwertannehmen:
"t Î ext(R), "A Î K: t.A ¹ null .
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
28
Fremdschlüssel(1)
• AlleWerteeines(odermehrerer) AttributeeinerRelationkönnensichauf
existierendeWertedesPrimärschlüsselseineranderenRelationbeziehen.
EinsolchesAttributwirdalsFremdschlüssel (foreignkey)bezeichnet.
• Beispiel(vgl.frühereFolie"WeitereNormalisierung"):
Faculty
FCode FacultyName
Student
EnrolNo
Name
FCode
'EC'
'Economy'
'10$250$7654' 'Scholes' 'EC'
'CS'
'Computer/Science'
'09$432$3456' 'Guttag' 'CS'
'MA'
'Mathematics'
'12$777$4227' 'Gauss'
'EE'
'Electrical/Engineering'
'11$839$5288' 'Cattell' 'CS'
'10$773$2290' 'Myers'
'MA'
'EE'
DasAttributFCode inRelationStudent
isteinFremdschlüssel,daessichaufdenPrimärschlüsselderRelationFaculty,
ebenfallsFCode genannt,bezieht.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
29
Fremdschlüssel(2)
SeiR eineRelationmitdemRelationenschemasch(R) = { K, A },
wobeiK derPrimärschlüsselvonR ist.
SeiS eineandereRelationmitdemRelationenschemasch(S) = { F, B },
mitbeliebigemPrimärschlüssel.
• DasAttributF heisstFremdschlüssel (foreign key)inderRelationS ,sofernim
AttributF füralleTupelzurjederZeitentwederderWertnull oderaberein
momentanexistierenderWertdesPrimärschlüsselsK derRelationR steht.
Bemerkungen:
– DieseDefinitionkannaufPrimär- /FremdschlüsselmitmehrerenAttributen
verallgemeinertwerden.
– DieAttributnamendesPrimärschlüsselsvonR unddesFremdschlüsselsvonS
müssennichtgleichsein– auchwenndiesoftderFallist.
– Selbstwenn(1)AttributA PrimärschlüsselvonR istund(2)AttributA auchinS
vorkommt,somussA inS nichtFremdschlüsselsein.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
30
ReferentielleIntegrität
• DiereferentielleIntegrität (referentialintegrity)basiertaufdemFremdschlüssel
Konzept:
• FürjedenWerteinesFremdschlüsselsF ineinerRelationS mussinder
referenziertenRelationR jeweilsentwedereinTupelmitdemselbenWertim
PrimärschlüsselK existieren,
oderderWertdesFremdschlüsselattributsmussderNullwertsein.
• Formal:
"s Î ext(S): ( ($r Î ext(R): s.F = r.K) Ú (s.F = null) ) .
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
31
Beispiel:Lieferdatenbank
Customers
CusNo CusName CusCity BalanceDue Discount
101 'Legrand'
'Genève'
0.00
0.10
102 'Studer'
'Zürich'
480.00
0.20
103 'Huber'
'Zürich'
0.00
0.05
Orders.CusNo istein
Fremdschlüssel
Orders.PrdNo istein
Fremdschlüssel
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
32
ElementareRelationenalgebra
• DieelementareRelationenalgebra(RA,auchrelationaleAlgebra,relational
algebra)definierteineReihevonOperationenaufRelationen:
-
Selektions
Projektionp
VereinigungÈ
Differenz−
KartesischesProdukt×
Umbenennung(renaming)r
• SiebildetdasoperationelleFundamentfürdieRealisierungvonDatenbankanfragesprachen(wiez.B.SQL).DieRA istalsoprozedural.
• DieRelationenalgebraistabgeschlossen:
JedeOperationhateineoderzweiRelationenalsOperanden(input)
undproduzierteineRelationalsResultat(output).
DieresultierendeRelationwirddefiniertdurcheinSchemasowieeineExtension.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
33
Selektion
BeiderSelektion (selection,auchrestriction,filtering,dargestelltdurchdasSymbols)
gehtesumdieAuswahleinerMengevonTupel einerRelation.
SeiR eineRelationundp eineBoolescheFormel,dassog.Selektions-prädikat,
bestehendauseinfachenVergleichsbedingungenzwischen
- zweiAttributenderRelationR,z.B.Ai < Aj
- einemAttributderRelationR undeinerKonstanten,z.B.Ak = 5 (oder Al = 'xy' )
allenfallsverknüpftmitdenlogischenOperatoren¬(not),Ù (and)sowie∨ (or).
• DasSchemaderSelektionvonsp (R) istgleichwiedasSchemavonR.
DieExtensionderSelektion sp (R) (linearauchs[p](R) geschrieben) ist:
ext(sp (R)) := { t | t Î ext(R) Ù p }
wobeiimSelektionprädikatp dieAttributwertevont eingesetztwerden.
• sp (R) enthältdiejenigenTupel,welchedieFormelp erfüllen.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
34
Selektion:BeispielmiteinfachemSelektionsprädikat
Finde alle Zürcher Kunden
s CusCity = 'Zürich' (Customers)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
35
Selektion:BeispielmitzusammengesetztemPrädikat
Finde alle Zürcher Kunden, die uns
Geld schulden
s CusCity = 'Zürich' Ù BalanceDue > 0 (Customers)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
36
SelektioneinerSelektion:Beispiel
Zürcher Kunden
Kunden, die uns Geld schulden
s CusCity = 'Zürich' (Customers)
s BalanceDue > 0 (Customers)
Zürcher Kunden, die uns Geld schulden
s BalanceDue > 0 (s CusCity = 'Zürich' (Customers))
R.Marti
s CusCity = 'Zürich' (s BalanceDue > 0 (Customers))
InformationssystemefürIngenieure2016 – 2Relationenmodell
37
Projektion
BeiderProjektion (projection,Symbolp)gehtesumdieAuswahlderrelevanten
Attribute einerRelation.
SeiA = { A1, ... , Ak } eineTeilmengederAttributeeinerRelationR mitdem
Schema{ A1, ... , An } .(Esgiltalsok ≤ n,meistsogark < n).
• DasSchema derProjektion pA (R) bestehtausdenAttributeninA.
• DieExtensionderProjektion pA (R) (auchp[A](R) geschrieben)ist:
ext(pA (R)) := { t | $r Î ext(R): t.Ai = r.Ai , 1 ≤ i ≤ k }
pA (R) enthältnurdieAttributwertederTupel vonR,diezueinemAttribut
ausderTeilmengeA allerAttributegehören.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
38
Projektion:Beispiel
Finde Beschreibung, Lagerort und
Lagerbestand für alle Produkte
p PrdDescr, Warehouse, Stock (Products)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
39
KombinationvonSelektionundProjektion:Beispiel
Beschreibung, Lagerort
und Lagerbestand
für Produkte, deren
Lagerbestand < 300 ist
p PrdDescr, Warehouse, Stock (Products)
s Stock < 300 (p PrdDescr, Warehouse, Stock (Products))
R.Marti
s Stock < 300 (Products)
p PrdDescr, Warehouse, Stock (s Stock < 300 (Products))
InformationssystemefürIngenieure2016 – 2Relationenmodell
40
Umbenennung vonAttributen
DieUmbenennung einesAttributs einerRelation(renaming, Symbolr)isteine
Hilfsoperation,dievorderAnwendungbinärerOperationennotwendigseinkann,
umdieÜbereinstimmungvonAttributnamenderOperanden
- entwederzuerzwingen
- oderaberzuvermeiden
SeiR eineRelation mitdemSchema{ A1 , ... , Ak−1 , Ak, Ak+1 , ... , An }
undB einAttributname.
• DasSchema vonrB ← Ak (R) bestehtaus{ A1 , ... , Ak−1 , B , Ak+1 , ... , An }.
DieExtension der(Attribut-)Umbenennung rB ← Ak (R) ist:
ext(rB ← Ak (R)) := { t | $r Î ext(R): t.Ai = r.Ai für i ≠ k und t.B = r.Ak }
•
Bemerkung:
DerAttribut-UmbenennungsoperatorkannfürdiegleichzeitigeUmbenennungmehrererAttribute
verallgemeinertwerden.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
41
Umbenennung vonRelationen
DieUmbenennung einerRelation (renaming, Symbolr) isteineHilfsoperation,die
vorderAnwendungbinärerOperatorennotwendigseinkann,umeineungewollte
ÜbereinstimmungderRelationennamen derbeidenOperandenzuverhindern.
SeiR eineRelation mitdemSchemaA = { A1 , ... , An } = sch(R)
undS einRelationenname.
• DasSchema vonS := rS (R) bestehtaus A = { A1 , ... , An } = sch(S) .
• DieExtension der(Relationen-)Umbenennung rS (R) ist:
ext(rS (R)) := { t | $r Î ext(R): t.A = r.A } = ext(S)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
42
Vereinigung
FürzweiRelationenR undS mitdemgleichenSchema,sch(R) = sch(S) ,
kanndiefolgendeüblicheMengenoperationendefiniertwerden.
• DieVereinigung (union,SymbolÈ )zweierRelationenR undS mitdem
gleichenSchemaistdieMengeallerTupel,dieentwederinR oderinS oderin
beidenRelationensind.
• DasSchemaderVereinigungR È S ist
sch(R È S) = sch(R) = sch(S)
• DieExtensionderVereinigungR È S ist:
ext(R È S) := { t | t Î ext(R) Ú t Î ext(S) }
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
43
Vereinigung:Beispiel
Nummer, Beschreibung,
Gewicht und Preis
für bestehende und
neue Produkte
p PrdNo, PrdDescr, Weight, Price Products
È
p PrdNo, PrdDescr, Weight, Price (Products) È NewProducts
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
44
Differenz
FürzweiRelationenR undS mitdemgleichenSchema,sch(R) = sch(S) ,
kanndiefolgendeüblicheMengenoperationendefiniertwerden:
• DieDifferenz (difference,Symbol– )zweierRelationenR undS mitdem
gleichenSchemaistdieMengeallerTupel,dieinR abernichtinS sind.
• DasSchemaderDifferenzR – S ist
sch(R – S) = sch(R) = sch(S)
• DieExtensionderDifferenzR – S ist:
ext(R – S) := { t | t Î ext(R) Ù t Ï ext(S) }
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
45
Differenz:Beispiel
Nummer, Beschreibung,
Gewicht und Preis
für neue Produkte, die
noch nicht im ProduktKatalog sind
p PrdNo, PrdDescr, Weight, Price (Products)
−
NewProducts − p PrdNo, PrdDescr, Weight, Price (Products)
Result_9
PrdNo PrdDescr
1001 'iPhone*007'
R.Marti
Weight Price
120 900.00
InformationssystemefürIngenieure2016 – 2Relationenmodell
46
KartesischesProdukt
BeimkartesischenProdukt (auchKreuzprodukt,cartesian product,crossproduct,
crossjoin,Symbol´ )gehtes umdasKombinierenderTupelzweierRelationen.
SeiR eineRelationmitSchemaA = { A1, ... , An }
undS eineRelationmitSchemaB = { B1, ... , Bm } ,wobeiA Ç B = ∅ .
WenneinAttributinbeidenSchemasvorkommt,somussmindestenseinesder
beidenVorkommenumbenanntwerden.
• DasSchema vonR ´ S bestehtausdenAttributeninA È B .
• DieExtension deskartesischenProdukts R ´ S ist:
ext(R ´ S) := { t | $r Î ext(R), $s Î ext(S): t.A = r.A Ù t.B = s.B }
• IntuitiveBedeutung:
R ´ S enthält alle möglichen PaarevonTupel aus R undTupel aus S.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
47
KartesischesProdukt:AbstraktesBeispiel
S
×
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
B1
C
2
'X'
2
'Y'
3
'Z'
48
KartesischesProdukt:KonkretesBeispiel(1)
ρ [CusNo1(<*(CusNo] ((Customers)
×
CusNo1 CusName
... Discount
101 'Legrand'
...
0.10
102 'Studer'
...
0.20
103 'Huber'
...
0.05
Orders × r CusNo1 ß CusNo (Customers)
Result_10
OrdNo CusNo PrdNo Qty ... ShipDate CusNo1 CusName ... Discount
67890
101
901 100 ... 2013*07*20
101 'Legrand'
...
0.10
67890
101
901 100 ... 2013*07*20
102 'Studer'
...
0.20
67890
101
901 100 ... 2013*07*20
103 'Huber'
...
0.05
67891
101
901 100 ... 2013*07*28
101 'Legrand'
...
0.10
67891
101
901 100 ... 2013*07*28
102 'Studer'
...
0.20
67891
101
901 100 ... 2013*07*28
103 'Huber'
...
0.05
…
…
103 'Huber'
...
…
67893
R.Marti
…
102
…
903
…
...
…
1 ... 2013*09*21
…
InformationssystemefürIngenieure2016 – 2Relationenmodell
insgesamt 12
(=4×3)
Tupel
…
0.05
49
KartesischesProdukt:KonkretesBeispiel(2)
Result_10
OrdNo CusNo PrdNo Qty ... ShipDate CusNo1 CusName ... Discount
67890
101
901 100 ... 2013*07*20
101 'Legrand'
...
0.10
67890
101
901 100 ... 2013*07*20
102 'Studer'
...
0.20
67890
101
901 100 ... 2013*07*20
103 'Huber'
...
0.05
67891
101
901 100 ... 2013*07*28
101 'Legrand'
...
0.10
67891
101
901 100 ... 2013*07*28
102 'Studer'
...
0.20
67891
101
901 100 ... 2013*07*28
103 'Huber'
...
0.05
…
…
103 'Huber'
...
…
…
67893
102
…
903
…
...
…
1 ... 2013*09*21
…
Bestellungen mit
den "passenden"
Kundendaten
Selektionsprädikat
CusNo1 = CusNo
…
0.05
s CusNo1 = CusNo ( Orders × r CusNo1 ß CusNo (Customers) )
Result_11
OrdNo CusNo PrdNo Qty ... ShipDate CusNo1 CusName ... Discount
67890
101
901 100 ... 2013*07*20
101 'Legrand'
...
0.10
67891
101
901 100 ... 2013*07*28
101 'Legrand'
...
0.10
67892
101
902
8 ... 9999*12*31
101 'Legrand'
...
0.10
67893
102
903
1 ... 2013*09*21
102 'Studer'
...
0.20
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
50
KartesischesProdukt:KonkretesBeispiel(3)
Result_11
OrdNo CusNo PrdNo Qty ... ShipDate CusNo1 CusName ... Discount
67890
101
901 100 ... 2013*07*20
101 'Legrand'
...
0.10
67891
101
901 100 ... 2013*07*28
101 'Legrand'
...
0.10
67892
101
902
8 ... 9999*12*31
101 'Legrand'
...
0.10
67893
102
903
1 ... 2013*09*21
102 'Studer'
...
0.20
Bestellungen mit
den "passenden"
Kundendaten
(nur Kundenname):
p OrdNo, CusNo, CusName, PrdNo, Qty, ShipDate ( s CusNo1 = CusNo ( Orders × r CusNo1 ß CusNo (Customers) ) )
Result_12
OrdNo CusNo PrdNo Qty ShipDate CusName
R.Marti
67890
101
901 100 2013)07)20 'Legrand'
67891
101
901 100 2013)07)28 'Legrand'
67892
101
902
8 9999)12)31 'Legrand'
67893
102
903
1 2013)09)21 'Studer'
InformationssystemefürIngenieure2016 – 2Relationenmodell
Dieses "Zusammenkleben" von Relationen
ist häufig
⇒ eigene Operation
(siehe später).
51
DefinitionelementarerRA Ausdrücke
• EinelementarerAusdruckderrelationalenAlgebraist
– eineRelationR inderDatenbank
• FallsE1 undE2 AusdrückederelementarenRelationenalgebrasind,dannlassen
sichweitereAusdrückewiefolgtbilden:
– sp(E1)
p einPrädikat,dassichaufAttributevonE1 bezieht
– pA(E1)
A eineAttributmengemitA ⊆ sch(E1)
– E1 È E2
mitsch(E1) = sch(E2)
– E1 − E2
mitsch(E1) = sch(E2)
– E1 × E2
mitsch(E1) ∩ sch(E2) = ∅
– rB ß A (E1)
A einAttributinE1 ,B einAttributmitB ∉ sch(E1)
bzw.rR (E1)
R.Marti
R einneuerRelationenname
InformationssystemefürIngenieure2016 – 2Relationenmodell
52
AnfrageninelementarerRelationenalgebra:Beispiele(1)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q1
NamenderKunden,dieunsGeldschulden.
pCusName (sBalanceDue > 0 (Customers))
Q2
NamenderKunden,dieeinegelieferte(Status'2 shipped')Bestellung
erhaltenhaben,dievorAnfangSeptember2013 erfolgte.
pCusName (sCusNo = D Ù Status = '2 shipped' Ù ShipDate < 2013-09-01 (rD ß CusNo (Orders) × Customers))
Q3
KundennummernderKunden,vondenenkeineBestellung registriertist.
pCusNo (Customers) – pCusNo (Orders)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
53
AnfrageninelementarerRelationenalgebra:Beispiele(2)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q4
NamenderKunden,vondenenmindestenseineBestellung registriertist.
pCusName ( sCusNo = D ( Customers × rD ß CusNo (Orders) ) )
Q5
NamenderKunden,vondenenkeineBestellung registriertist.
pCusName (Customers) – pCusName ( sCusNo = D ( Customers × rD ß CusNo (Orders) ) )
zumVergleich(sieheQ3,vorhergehendeFolie):
NummernderKunden,vondenenkeineBestellungregistriertist.
pCusNo (Customers) – pCusNo (Orders)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
54
AnfrageninelementarerRelationenalgebra:Beispiele(3)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q6
NamenderZürcherKunden,dieeinProdukt ausZürichbestellt haben.
pCusName
( s CusNo = D1 Ù PrdNo = D2 Ù CusCity = 'Zürich' Ù Warehouse = ‘Zürich’
( Customers
× Products
× rD1 ß CusNo, D2 ß PrdNo (Orders)
)
)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
55
IntegrierteÜbung:AnfrageninelementarerRA
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
QI1
BeschreibungvonProdukten,diemindestenseinmalbestellt wurden.
...
QI2
NameundStadtvonKunden,dieeineBestellung vonmehralsCHF1000
getätigthaben.
...
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
56
ErweiterteRelationenalgebra
• NebendenbishereingeführtenelementarenOerationenderrelationalenAlgebra
sindinderLiteratureinigeweitereOperationenzufinden,welche
–
dieAlgebrazwarnichtausdrucksstärkermachen
(diezusätzlichenOperatorenkönnenmitHilfederelementarenOperatoren
ausgedrü̈cktwerden;deshalbsindsieredundant)
–
aberdieFormulierunghäufigerArtenvonAnfragenzumTeilerheblichvereinfacht.
• Essinddiesinsbesondere
-Mengendurchschnitt(intersection)Ç
-NatürlicherVerbund(naturaljoin)⋈
-AllgemeinerVerbund(thetajoin)
-ÄussereVerbunde(outerjoins)
-Zuweisung(assignment)
-RelationaleDivision(relationaldivison)÷
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
57
Mengendurchschnitt
FürzweiRelationenR undS mitdemgleichenSchema,
sch(R) = sch(S) ,sinddieüblichenMengenoperationendefiniert:
• DerMengendurchschnitt (intersection,SymbolÇ )zweierRelationenR undS
mitdemgleichenSchemaistdieMengeallerTupel,diesowohlinR alsauchinS
sind.
• DasSchemadesMengendurchschnittsR Ç S ist
sch(R Ç S) = sch(R) = sch(S)
• DieExtensiondesMengendurchschnittsR ∩ S ist
ext(R Ç S) := { t | t Î ext(R) Ù t Î ext(S) }
• Esgilt:R Ç S = R – (R – S)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
R
S
58
NatürlicherVerbund
BeimnatürlichenVerbund (naturaljoin,Symbol⋈ )gehtesumdieVerbindung
zweierRelationenübergleicheAttributnamen undgleicheAttributwerte der
jeweiligenTupel.
R
SeiR eineRelationmitSchemasch(R) = { A, B }
undS eineRelationmitSchemasch(S) = { B, C } .
A
B
S
B
C
B
C
• DasSchemadesnatürlichenJoins R ⋈ S (auchR * S geschrieben)ist:
sch(R ⋈ S) := sch(R) È sch(S) = { A, B, C }.
Bem:DasgemeinsameAttributB wirdJoin-Attributgenannt.
R"|x|"S
A
• DieExtensiondesnatürlichenJoins R ⋈ S ist:
ext(R ⋈ S) :=
{ t | $r Î ext(R), $s Î ext(S): t.A = r.A Ù t.B = r.B Ù r.B = s.B Ù t.C = s.C }
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
59
deshalb sind die neuen Operatoren redundant)
NatürlicherVerbund:AbstraktesBeispiel
I die Formulierung häufiger Anfragen zum Teil erheb
I
I
I
Mengendurchschnitt \
|×|
Join 1
Zuweisung
InfO12, Relationale Algebra
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
46/54
60
deshalb sind die neuen Operatoren redundant)
NatürlicherVerbund:KonkretesBeispiel
I die Formulierung häufiger Anfragen zum Teil er
I
I
I
Mengendurchschnitt \
Join 1
×
Zuweisung
InfO12, Relationale Algebra
46/54
p OrdNo, CusNo, CusName, PrdNo, Qty, ShipDate ( Orders ⋈ Customers )
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
61
deshalb sind die neuen Operatoren redundant)
I die Formulierung häufiger Anfragen zum Teil erheb
NatürlicherVerbund:KonkretesBeispiel
I
I
I
Mengendurchschnitt \
Join 1
×
Zuweisung
InfO12, Relationale Algebra
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
46/54
62
NatürlicherVerbund:Kreuzproduktà Selektionà Projektion
DernatürlicheVerbundkannmitHilfevonKreuzprodukt,SelektionundProjektion
(sowieevt.einerUmbenennung)ausgedrücktwerden.
SeiR eineRelationmitSchema{ A, B }
undS eineRelationmitSchema{ B, C }.
Esgilt:
R ⋈ S = p A, B, C ( s B1 = B ( R ´ r B1 ß B ( S )))
Beweis:
VergleichderresultierendenSchemasundExtensionen
gemässformalenDefinitionen...
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
63
NatürlicherVerbund:×à s à p:AbstraktesBeispiel
ρ![B1!<&!B]!(S)
×
R"x"ρ"[B1"<("B]"(S)
R.Marti
A
B
B1
C
'a'
1
2
'X'
'a'
1
2
'Y'
'a'
1
3
'Z'
'b'
2
2
'X'
'b'
2
2
'Y'
'b'
2
3
'Z'
'c'
2
2
'X'
'c'
2
2
'Y'
'c'
2
3
'Z'
'd'
3
2
'X'
'd'
3
2
'Y'
'd'
3
3
'Z'
B1
C
2
'X'
2
'Y'
3
'Z'
s
p
InformationssystemefürIngenieure2016 – 2Relationenmodell
64
AllgemeinerVerbund(auchallgemeinerJoin,Theta-Join)
BeimallgemeinenVerbund(Theta-Join,thetajoin,Symbol⋈p )
gehtes– wiebeimnatürlichenVerbund– umdasZusammenfügenvonTupeln
zweierRelationen,abernichtaufgrundgleicherAttributeundgleicherWerte,
sondernuntereinerexplizitenBedingung,diealsPrädikatp formuliertwird.
SeiR eineRelationmitSchemaX undS eineRelationmitSchemaY,
mitX Ç Y = Æ.SeienA Î X undB Î Y AttributevonR respektiveS
undq einederVergleichsoperationen= , ¹ , < , ≤ , ≥ , > .
• DasSchema vonR ⋈A q B S bestehtausX È Y .
• DieExtension desTheta-Joins (auchq-Join)R ⋈A q B S ist:
ext(R ⋈A q B S) := ext( sA q B (R ´ S) )
• Wennq dieGleichheit(=) ist,dannheisstderq-JoinauchEqui-Join .
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
65
AnfrageninerweiterterRelationenalgebra:Beispiele(1)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q2
NamenderKunden,dieeinegelieferte(nochunbezahlte)Bestellung
erhaltenhaben,dievorAnfangSeptember2013 erfolgte.
mitTheta-Join(genauer:mitEqui-Join):
pCusName (sStatus = '2 shipped' Ù ShipDate < 2013-09-01 (rD ß CusNo (Orders) ⋈CusNo = D Customers))
= pCusName (sStatus = '2 shipped' Ù ShipDate < 2013-09-01 (rD ß CusNo (Orders)) ⋈CusNo = D Customers)
mitnatürlichemJoin:
pCusName (sStatus = '2 shipped' Ù ShipDate < 2013-09-01 (Orders) ⋈ Customers)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
66
AnfrageninerweiterterRelationenalgebra:Beispiele(2)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q4
NamenderKunden,vondenenmindestenseineBestellung registriertist.
mitnatürlichemJoin:
pCusName ( Customers ⋈ Orders )
Q5
NamenderKunden,vondenenkeineBestellung registriertist.
mitnatürlichemJoin:
pCusName (Customers) – pCusName ( Customers ⋈ Orders )
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
67
AnfrageninerweiterterRelationenalgebra:Beispiele(3)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q6
NamenderZürcherKunden,dieeinProdukt ausZürichbestellt haben.
mitnatürlichemJoin:
pCusName ( s CusCity = 'Zürich' Ù Warehouse = ‘Zürich’ ( Customers ⋈ Orders ⋈ Products ) )
= pCusName (
s CusCity = 'Zürich' ( Customers ) ⋈ Orders ⋈ s Warehouse = ‘Zürich’ ( Products )
)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
68
AnfrageninerweiterterRelationenalgebra:Beispiele(4)
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
Q7
NummernneuerEinzelbestellungen,derenStückzahlüberdenVorrateines
Produkts hinausgehen.
pOrdNo ( sStatus = '1 ordered' ( Orders ) ⋈ PrdNo = D Ù Qty > Stock r D ß PrdNo ( Products ) )
Q8
NamenvonPaarenvonKunden,dieinderselbenStadtwohnen,mitStadt.
pCusName, OtherName, CusCity (
Customers ⋈ OtherNo > CusNo Ù OtherCity = CusCity
rOtherNo ß CusNo, OtherName ß CusName, OtherCity ß CusCity ( Customers )
)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
69
IntegrierteÜbung:AnfrageninerweiterterRA
Customers( { CusNo, CusName, CusCity, BalanceDue, Discount } )
Products( { PrdNo, PrdDescr, Weight, Price, Warehouse, Stock } )
Orders( { OrdNo, CusNo, PrdNo, Qty, Amount, Status, OrderDate, ShipDate } )
QI3
LagerortevonProdukten,mitmehrals20kg(Einzel-)Gewicht,vondenen
mindestenseineBestellung registriertist.(BitteJoin-Operatorverwenden.)
…
QI4
R.Marti
NamenderKunden,vondenenkeineBestellung registriertist.
…
InformationssystemefürIngenieure2016 – 2Relationenmodell
70
VergleichnatürlicherJoinundEqui-Join
• SowohlnatürlicherJoinwieEqui-Join
- bestehenauseinemKreuzproduktgefolgtvoneinerSelektion
-
könneneffizienterrealisiertwerdenalsKreuzproduktà Selektion
-
kombinierenTupelauszweiRelationenaufgrundgleicherWertein
bestimmtenAttributen
• BeimnatürlichenJoin
- istdasSelektionsprädikatimplizitdurchgleicheAttributnamengegeben
folgtdenOperationenKreuzproduktà Selektioneineimplizite
-
Projektion,inderdoppelteAttributeeliminiertwerden
• BeimEqui-Join
R.Marti
-
mussdasSelektionsprädikat explizitformuliertwerden,wobeidie
-
Attribute(inderklassischenRATheorie) nichtgleichheissendürfen
mussdieProjektionexplizitverlangtwerdenfallserwünscht
InformationssystemefürIngenieure2016 – 2Relationenmodell
71
VergleichnatürlicherJoinundEqui-Join:Bsp.(1)
Beispiel,indemdasFremdschlüsselattribut andersheisstalsdas
referenziertePrimärschlüsselattribut
Q9
Städtename,NamedesLandessowiePopulationallerStädte:
NatürlicherJoin:
pCityName, CName, Population ( rCode ß CountryCode ( City ) ⋈ Country )
Equi-Join(SpezialfalldesallgemeinenJoins):
pCityName, CName, Population ( City ⋈ Code = CountryCode Country )
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
72
VergleichnatürlicherJoinundEqui-Join:Bsp.(2)
Beispiel,indemeinige Fremdschlüsselattribute andersheissenalsdas
referenziertePrimärschlüsselattribut
Q10
NamenvonAusgangs- undZielstadtallerFlüge
hiernurmitEqui-Join:
pFrom, To ( Schedule
⋈AptFrom = AptCode pAptCode, From ( r From ß AptCity ( Airport ) )
⋈AptTo = T pT, To ( r T ß AptCode, To ß AptCity ( Airport ) )
)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
73
WeitereOperationen:ÄussereVerbunde(OuterJoins)
DeräussereVerbund(outerjoin)isteinJoin,beidemalleTupel
– deslinken Operanden(left outerjoin,Symbol⟕ )
– bzw.desrechtenOperanden(rightouterjoin,Symbol⟖ )
– bzw.beiderOperanden(fullouterjoin,Symbol⟗ )
imResultatbeibehaltenwerden,selbstwennimanderenOperandenkeineTupelmit
passendenAttributwertenexistieren.
DiefehlendenAttributwertewerdendurchNullwerte aufgefüllt.
SeiR eineRelationmitSchema{A, B} undS eineRelationmitSchema{B, C}.
Danngiltz.B.für
denLeft OuterJoin:
Relationbestehendauseinem
TupelmitAttributC mitWertnull
R ⟕ S := ( R ⋈ S ) È ( R − pA, B ( R ⋈ S ) ) ´ { < C: null > } )
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
74
LeftOuterJoin:KonkretesBeispiel
×
"normaler" natürlicher Verbund
( sog. inner join )
"Auffüllen" mit null
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
75
Zuweisung
• DieZuweisung (assignment,Symbol←)weisteinerRelationT denWerteiner
Relation(bzw.einesRA-Ausdrucks)S zu:T ← S .
BessereFormulierung:DieZuweisungbindeteinenneuen,bishernichtgebrauchten
RelationennamenT andenWerteinesRA-AusdrucksS .
• DieZuweisungerlaubtes,komplexeAusdrückeinkleinere,übersichtlichere
Ausdrückeaufzubrechen.
• Achtung:BeiderEntwicklungkomplexerAnfragenmachtesSinn,sovorzugehen.
BeimAusführen vonAnfragenmit➛SQLsolltenaber➛viewsdefiniert(undnicht
etwa➛insert Anweisungenverwendet)werden.
Beispiel:
Q5 NamenderKunden,vondenenkeineBestellungregistriertist:
Tmp ← sCusNo = D (Customers × rD ß CusNo (Orders) )
pCusName (Customers) – pCusName (Tmp)
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
76
RelationaleVollständigkeit
• Satz
MinimaleMengevonOperatorenderelementaren Relationenalgebra
È ,–,s,p und× (sowieUmbenennungr)bildeneineminimaleMengevon
Operationen,mitdenensichalleOperationenderRelationenalgebraausdrücken
lassen.
Diesbedeutet:
WeitereOperatoren,wiez.B.DurchschnittundnatürlicherVerbund,könnenauf
dieobigen5Operatorenzurückgeführtwerden.
• Definition
RelationaleVollständigkeit
EineAnfragespracheheisstrelationalvollständig,wennsichdamitalleAnfragen
derelementarenRelationenalgebraausdrückenlassen.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
77
EinigeÄquivalenzregelnderRelationenalgebra
sf(sy(R)) = sy(sf(R))
pA(sf(R)) = sf(pA(R))
fallsf nurAttributeausMengeA referenziert
R⋈S = S⋈R
R ⋈ (S ⋈ T) = (R ⋈ S) ⋈ T
pA(pC(R)) = pA(R)
fallsA Í C
sf(sy(R) ) = sf Ù y(R)
pA(R È S) = pA(R) È pA(S)
sf(R È S) = sf(R) È sf(S)
sf(R ⋈ S) = sf(R) ⋈ S
fallsf nurAttributevonR referenziert
pA, B, C (R ⋈ S) = pA, B (R) ⋈ pB, C (S)
R ⋈ (S È T) = (R ⋈ S) È (R ⋈ T)
SolcheRegelnwerdeninDBMSsangewendet,umdieBerechnungeffizienterzugestalten:
z.B.werdenSelektionundProjektionsofrühwiemöglichangewendet.
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
78
Zusammenfassung
DasRelationenmodellisteineinfachesModellmitfolgendenBausteinen:
• Struktur:
- Relation(➛ Tabelle)
- Attribut(➛ SpalteeinerTabelle)
- Tupel(➛ ZeileeinerTabelle)
• Integritätsbedingungen:
- PrimärschlüsselzurIdentifikationvonTupel
- weitereSchlüsselkandidaten
- FremdschlüsselzumVerweisaufTupeleineranderenRelation
• AuswahlvonTeileneinerodermehrererRelationen:
- elementareRelationenalgebramitfolgendenOperationen:
Selektions ,Projektionp ,VereinigungÈ ,Differenz− ,Kreuzprodukt× ,Umbenennungr
- weitereOperationen("syntacticsugar","creaturecomforts"),
insbesonderenatürlicherJoinundThetaJoin
R.Marti
InformationssystemefürIngenieure2016 – 2Relationenmodell
79