Datenbanksysteme SS 2007 Frank Köster (Oliver Vornberger) Institut für Informatik Universität Osnabrück Kapitel 6b: Das relationale Modell Das Relationale Modell (vgl. Lerneinheit 6a) Wertebereiche (Domänen): Relation: Wertebereich von Attribut A: Relation: Element von R: Schema der Relation: aktuelle Ausprägung: D1, D2 ,, ... , Dn R ⊆ D1 × D2 × ... × Dn dom(A) R ⊆ dom (A1) × dom (A2) × ... × dom (An) Tupel sch(R) = A1, A2, ... , An R Bei DBS zusätzlich zum Wertebereich noch Bezeichner: Telefonbuch : { [Name : string, Adresse: string, TelefonNr : integer] } Telefonbuch : { [Name, Adresse, TelefonNr ] } Konzeptuelles Schema „Universität“ (vgl. Lerneinheit 6a) voraussetzen Vorgänger N MatrNr Name N Studenten Sem Vorlesungen M N N prüfen Assistenten N arbeitenFür is-a PersNr Name Angestellte SWS Titel lesen 1 Fachgebiet VorlNr M hören Note Nachfolger M 1 1 Rang Professoren Raum Relationenschema (vgl. Lerneinheit 6a) Studenten : {[ MatrNr : integer, Name : string, Semester : integer ]} Vorlesungen : {[ VorlNr : integer, Titel : string, SWS : integer, gelesenVon : integer ]} Professoren : {[ PersNr : integer, Name : string, Rang : string, Raum : integer ]} Assistenten : {[ PersNr : integer, Name : string,Fachgebiet : string, Boss : integer ]} hören : {[ MatNr : integer, VorlNr : integer ]} voraussetzen : {[ Vorgänger : integer, Nachfolger : integer ]} prüfen : {[ MatrNr : integer, VorlNr : integer, PersNr : integer, Note : decimal ]} Ausprägung: Professoren und Assistenten (vgl. Lerneinheit 6a) Professoren PersNr Name Rang Raum 2125 2126 2127 2133 2134 2136 2137 Sokrates Russel Kopernikus Popper Augustinus Curie Kant C4 C4 C3 C3 C3 C4 C4 226 232 310 52 309 36 7 Assistenten PersNr Name 3002 3003 3004 3005 3006 3007 Platon Aristoteles Wittgenstein Rhetikus Newton Spinoza Fachgebiet Boss Ideenlehre 2125 Syllogistik 2125 Sprachtheorie 2126 Planetenbewegung 2127 Keplersche Gesetze 2127 Gott und Natur 2134 Ausprägung: Vorlesungen und Studenten (vgl. Lerneinheit 6a) Vorlesungen Studenten VorlNr Titel SWS gelesenVon 5001 5041 5043 5049 4052 5052 5216 5259 5022 4630 Grundzüge Ethik Erkenntnistheorie Mäeutik Logik Wissenschaftstheorie Bioethik Der Wiener Kreis Glaube und Wissen Die 3 Kritiken 4 4 3 2 4 3 2 2 2 4 2137 2125 2126 2125 2125 2126 2126 2133 2134 2137 MatrNr Name Semester 24002 25403 26120 26830 27550 28106 29120 29555 Xenokrates Jonas Fichte Aristoxenos Schopenhauer Carnap Theophrastos Feuerbach 18 12 10 8 6 3 2 2 Ausprägung: hören, voraussetzen und prüfen (vgl. Lerneinheit 6a) hören MatrNr VorlNr 26120 27550 27550 27550 28106 28106 28106 27550 29120 29120 29555 25403 29555 5001 5001 4052 5041 4052 5216 5259 4630 5041 5049 5022 5022 5001 voraussetzen Vorgänger Nachfolger 5001 5001 5001 5041 5043 5041 5052 5041 5043 5049 5216 5052 5052 5259 prüfen MatrNr VorlNr PersNr Note 28106 25403 27550 5001 5041 4630 2126 2125 2137 1.0 2.0 2.0 Relationenalgebra (vgl. Lerneinheit 6a) Operanden = Relationen Operatoren: • Selektion (σ) • Projektion (Π) • Vereinigung (∪) abgeleitete Operatoren: • Verbund ( , θ, , , ) • Durchschnitt (∩) • Division (÷) • Mengendifferenz (–) • Kartesisisches Produkt (×) • Umbenennung (ρ) minimale Operatormenge (vgl. folgende Argumentation) Relationenalgebra (Warum ist Operatormenge σ, Π, ∪, –, ×, ρ minimal? Argumentation – kein Beweis!) • Die Umbenennung (ρ) kann nicht durch eine der anderen fünf Operatoren ersetzt werden. • ρ hat zudem keinen Einfluss auf die Darstellung eines der anderen Operatoren mithilfe der noch verbleibenden fünf. Daher wird auf ρ im Folgenden nicht weiter eingegangen. … es verbleiben also die Operatoren σ, π, ∪, –, ×. • Das kartesische Produkt (×) kann nicht von den anderen vier Operatoren simuliert werden, da σ, π, ∪ und – ein Schema nicht erweitern können. • Analoges Argument gilt für die Projektion (π), da keiner der Operatoren σ, ∪ und – ein Schema reduzieren kann. Relationenalgebra (Warum ist Operatormenge σ, Π, ∪, –, ×, ρ minimal? Argumentation – kein Beweis!) • Die Selektion (σ) kann höchstens durch – simuliert werden, jedoch testet dieser Operator nur auf Gleichheit ganzer Tupel und nicht auf beliebige Vergleiche durch Formeln, die sich auf Komponenten von Tupeln beziehen. • σ kann umgekehrt das Minus (–) nicht simulieren, da σ die "Negation" auf Relationen nicht darstellen kann. • Es gibt keine Möglichkeit, die Vereinigung (∪) von z.B. zwei einelementigen Relationen durch die Operatoren σ, π, × und – darzustellen. Relationenkalkül relationale Algebra (konstruktiv) relationale Kalküle (deklarativ) • Der relationale Tupelkalkül (binde freie Variable an Tupel) • Der relationale Domänenkalkül (binde freie Variable an Domäne) Der relationale Tupelkalkül Sei t eine Tupelvariable (repräsentiert ein Tupel einer Relation) und P ein Prädikat unter Verwendung von ∨ ∧ ¬ ∃ ∀ ⇒ Ein Ausdruck im relationalen Tupelkalkül hat die Form { t P(t) } t ist eine freie Variable, die unter Berücksichtigung des Prädikats sukzessive an die Tupel einer Relation gebunden wird. Der relationale Tupelkalkül Alle C4-Professoren: { p p ∈ Professoren ∧ p.Rang = 'C4' } Alle Professoren mit den Personalnummern ihrer Assistenten: { [ p.Name, a.PersNr ] p ∈ Professoren ∧ a ∈ Assistenten ∧ p.PersNr = a.Boss } Alle Studenten, die sämtliche 4-stündigen Vorlesungen hören: { s s ∈ Studenten ∧ ∀ v ∈ Vorlesungen ( v.SWS=4 ⇒ ∃ h ∈ hören ( h.VorlNr = v.VorlNr ∧ h.MatrNr = s.MatrNr ) ) } Tupelkalkül vs. Relationenalgebra Sicherer Ausdruck: Resultat ist wieder Teilmenge der Domäne. Beispielsweise nicht sicher: { n ¬ (n ∈ Professoren) } Bei Beschränkung auf sichere Ausdrücke sind Tupelkalkül und relationale Algebra gleichmächtig. Der relationale Domänenkalkül Seien v1, v2, ..., vn Domänenvariable (repräsent. Attributwerte) Sei P ein Prädikat unter Verwendung von ∨ ∧ ¬ ∃ ∀ ⇒ Ein Ausdruck im relationalen Domänenkalkül hat die Form { [v1, v2, ..., vn] P (v1, v2, ..., vn ) } v1, v2, ..., vn sind freie Domänenvariablen, die sukzessive unter Berücksichtigung des Prädikats an Wertebereiche der Attribute gebunden werden. Der relationale Domänenkalkül Alle Namen der Professoren mit den Personalnummern ihrer Assistenten: { [n,a] ∃ p, r, t ( [p, n, r, t ] ∈ Professoren ∧ ∃ v, w ( [a, v, w, p ] ∈ Assistenten ) ) } Bei Beschränkung auf sichere Ausdrücke sind die relationale Algebra und der relationale Domänenkalkül gleichmächtig. QBE Fordere Tabellenskelett an und fülle es exemplarisch: Vorlesungen VorlNr Titel p._t SWS >3 Grundzüge Ethik Logik Die 3 Kritiken Im Domänenkalkül: { [t] | ∃ v, s, r ( [v, t, s, r] ∈ Vorlesungen ∧ s > 3 ) } gelesenVon QBE (Verbund) Liste alle Professoren, die Logik lesen: Vorlesungen VorlNr Titel SWS Logik Professoren PersNr Name Rang p._n _otto Sokrates gelesenVon _otto Raum QBE (Condition Box) Liste alle Studenten, die in einem höheren Semester sind als Feuerbach: Studenten MatrNr Name p._s Feuerbach Semester _a _b conditions _a > _b QBE (Gruppierung) 2125 2126 2127 2133 2134 2136 2137 Gruppierung: g. Aggregatfunktionen: sum. avg. min. Sokrates Russel Kopernikus Popper Augustinus Curie Kant max. C4 C4 C3 C3 C3 C4 C4 226 232 310 52 309 36 7 (all.) Liste für jede Gehaltsgruppe die maximale Personalnummer und den Namen des zugehörigen Professors: Professoren PersNr p.max._x Name p._x Rang Raum p.g. 2137 2134 Kant Augustinus C4 C3 QBE (Gruppierung) 5001 5041 5043 5049 4052 5052 5216 5259 5022 4630 Grundzüge Ethik Erkenntnistheorie Mäeutik Logik Wissenschaftstheorie Bioethik Der Wiener Kreis Glaube und Wissen Die 3 Kritiken 4 4 3 2 4 3 2 2 2 4 2137 2125 2126 2125 2125 2126 2126 2133 2134 2137 Liste für jeden Professor die Summe seiner Vorlesungsstunden: Vorlesungen VorlNr Titel SWS p.sum.all._x 10 8 2 2 8 gelesenVon p.g. 2125 2126 2133 2134 2137 QBE (Einfügen) Füge neuen Studenten ein: Studenten i. MatrNr 4711 Name Wacker Semester 5 QBE (Ändern) Setze Semesterzahl von Feuerbach auf 3: Studenten MatrNr Name Feuerbach Semester u.3 QBE (Löschen) Entferne Sokrates und seine Vorlesungen: Professoren d. PersNr Name _x Sokrates Vorlesungen d. VorlNr _y hören d. Titel VorlNr _y Rang SWS Raum gelesenVon _x MatrNr SQL Liste die Namen der Studenten, die 4-stündige Vorlesungen hören: select from where and and s.name studenten s, hoeren h, vorlesungen v s.matrnr = h.matrnr h.vorlnr = v.vorlnr v.sws = 4 Der relationale Tupelkalkül Die Namen der Studenten, die jeweils alle 4-stündige Vorlesungen hören: { s.name s ∈ Studenten ∧ ∀ v ∈ Vorlesungen ( v.SWS=4 ⇒ ∃ h ∈ hören ( h.VorlNr = v.VorlNr ∧ h.MatrNr = s. MatrNr ) ) } Äquivalenzen ∀ t ∈ R ( P(t) ) A⇒B ¬ (A ∨ B) ¬ ( ∃ t ∈ R ( ¬ P(t) ) ¬A∨B ¬A∧¬B Der relationale Tupelkalkül Die Namen der Studenten, die jeweils alle 4-stündige Vorlesungen hören: { s.name s ∈ Studenten ∧ ∀ v ∈ Vorlesungen { s.name s ∈ Studenten ∧ ¬ (∃ v ∈ Vorlesungen (v.SWS=4 ⇒ ∃h ∈ hoeren ¬ (v.SWS=4 ⇒ ∃h ∈ hoeren ¬ (¬ v.SWS=4 ∨ ∃h ∈ hoeren ( v.SWS=4 ∧ ¬∃h ∈ hoeren (h.VorlNr = v.VorlNr ∧ h.MatrNr = s. MatrNr)) } (h.VorlNr = v.VorlNr ∧ h.MatrNr = s. MatrNr)) } SQL Die Namen der Studenten, die jeweils alle 4-stündige Vorlesungen hören: { s.name s ∈ Studenten ∧ ¬ ( ∃ v ∈ Vorlesungen (v.SWS=4 ∧ ¬ ∃ h ∈ hoeren ( h.VorlNr = v.VorlNr ∧ h.MatrNr = s. MatrNr ) ) } select s.name from Studenten s where not exists (select * from vorlesungen v where sws=4 and not exists (select * from hoeren h where h.vorlnr = v.vorlnr and h.matrnr = s.matrnr)) Datenbanksysteme SS 2007 Ende von Kapitel 6b: Das relationale Modell