Das Relationenmodell In Datenbankmanagementsystemen (DBMS), die sich auf das relationale Modell stützen, werden Beziehungen wie Informationen in der Form von Tabellen dargestellt. Relationen - Die Elemente einer k-stelligen Relation R heißen k-Tupel oder einfach Tupel. - Eine Tabellenzeile (z.B. in einer Access-Tabelle) entspricht einem Tupel - Ein Tupel ist eine endliche, geordnete Menge von Werten (Komponenten); Darstellung (x1, x2, ... xn). Beispiel: (Betriebs_Nr, Name, Strasse, Hausnummer, PLZ, Ort) könnte zur Beschreibung eines Übernachtungsbetriebes dienen. - Damit entspricht jede Tabellenspalte einer Komponente - Jedes Attribut und ein entsprechender Wert liefern eine elementare Aussage über einen Gegenstand oder Sachverhalt; die Werte eines Tupels beziehen sich i.d.R. auf den selben Gegenstand. Ein Tupel ist damit eine komprimierte Darstellung zusammengehöriger, elementarer Aussagen. Verbesserungen durch das Relationenmodell Wohldefinierte Konzepte zur Handhabung der Semantik der Daten Integrität Redundanz Mengenorientierung Erweiterbarkeit Das Relationenmodell ist ein theoretisches Modell: Nicht alle Kaufprodukte bieten alle theoretisch vorhandenen Eigenschaften Seite 1 Relationenschema Ein Relationenschema ist eine Objekttypdefinition für eine bestimmte Relation, geschrieben als Name der Relation, gefolgt von der Liste ihrer Attribut-DomänenPaare (meist wird die Domäne weggelassen): R(A1:D1,....Ak:Dk) (ohne Domäne: R(A1,....Ak)) Die Tupel einer Relation bilden Instanzen des jeweiligen Objekttyps. Seite 2 Beispiel Domäne – Relationenschema – Tupel – Extension Ein relationales Datenbankschema ist die Menge aller Relationenschemata einer Datenbank. Eine relationale Datenbank ist ein relationales Datenbankschema zusammen mit den Extensionen der Relationen Schema und Extension einer Relation: Seite 3 Eigenschaften von Relationen 1. Relationen sind Mengen. Es sind keine Duplikate erlaubt. 2. Tupel werden durch Attributwerte idendifiziert 3. Es gibt keine Ordnung der Tupel, die Tupelreihenfolge ist irrelevant (keine Adressierung durch Positionsangabe!) 4. Es gibt keine Ordnung der Attribute, die Spaltenreihenfolge ist irrelevant. 5. Es sind nur atomare Attributwerte zugelassen 6. Schlüsselkandidaten sind alle Teilmengen der Attributmenge, für die gilt: Sie sind eindeutig Sie sind minimal 7. Der Primärschlüssel wird beim Entwurf aus den verfügbaren Schlüsselkandidaten ausgewählt Die meisten kommerziellen Systeme unterstützen diese Eigenschaften Seite 4 Vom ER-Modell zum Relationenmodell Gesamtprozeß des logischen Datenbankdesigns: ER-Modell Relationenmodell Normalisierung Integrität Semantisch reichhaltig Leichter zu implementieren Auflösen von Anomalien Gewährleistung der Konsistenz Konvertierung der Entity-Typen: 1. Jeder Entity-Typ (Entity-Menge) wird auf eine Relation abgebildet. Die Wertigkeit entspricht der Menge an Attributen 2. Jedes Entity (Instanz, „Datensatz“) entspricht einem Tupel („Zeile in der Tabelle“) 3. Falls ein Entity-Typ einem anderen untergeordnet ist, bekommt die übergeordnete Relation alle Attribute des untergeordneten. Schlüssel ist der Schlüssel der übergeordneten Relation Konvertierung der Relationships: 1. Ein Relationship-Typ wird auf eine Relation abgebildet, die alle Schlüsselattribute der beteiligten Entity-Typen enthält. 2. Eindeutige Attributnamen werden durch Umbenennung (z.B. Zusatz der Rollenbezeichnung) erreicht. 3. Wenn die Relationship eigene Attribute enthält, gehören diese auch zur Relation 4. Ist-Beziehungen werden nicht abgebildet Seite 5 Relationale Abfragesprachen Die relationale Algebra wird aufgebaut über einer Grundmenge von mengenwertigen Operationen, die in einem gewissen Sinne vollständig sind. Diese Grundoperationen können auf Relationen angewendet werden. Und liefern als Ergebnis wieder Relationen. Die relationale Algebra dient somit verschiedenen Zwecken: 1. 2. 3. 4. Grundlage einer direkt implementierbaren relationalen Abfragesprache Theoretische Grundlage höherer relationaler Abfragesprachen Maßstab für deren Ausdrucksmächtigkeit Grundlage der Datenstrukturen, die in einem praktischen Query-Prozessor (Abfrage-Prozessor) eingesetzt werden. Klassische Mengenoperationen auf zwei vereinigungsverträgliche (jedem Attribut der ersten Relation ist ein Attribut vom gleichen Datentyp in der zweiten Relation zugeordnet) Relationen R und S: Vereinigung R S Durchschnitt R S Differenz R – S symmetrische Differenz R / S Klassische Mengenoperation auf zwei Relationen R und S mit disjunkten Attributmengen (sie enthalten keine gemeinsamen Attribute) ist das kartesisches Produkt R x S. Seite 6 Daraus leiten sich die nachfolgend zu besprechenden Grundoperationen des Relationenmodells ab: 1. 2. 3. 4. 5. 6. Selektion (Restriktion) Projektion Kartesisches Produkt Union (Vereinigung) Intersektion (Schnittmenge) und Differenz Join Durch das Anwenden der hier definierten Operatoren auf eine oder mehrere Relationen entsteht eine neue Relation, auf die durch Verschachtelung weitere Operatoren angewendet werden können. Die Tupel dieser Ergebnisrelation können durch Wertzuweisungen einer anderen Relation zugewiesen werden. Seite 7 Beispiel Gegeben seien die zwei Relationen Projekt und Ausschuß (Mitarbeiter in einem Projekt bzw. Mitarbeiter in einem Ausschuß) Projekt Ausschuß Mname Abteilung Funkt Mname Abteilung Funkt Abs 112 Laborant Beer 116 Chemiker Beer 116 Chemiker Riedle 114 Laborant Hein 112 Konstrukteur Gegeben seien weiterhin die zwei Relationen Personal und Gehalt Personal Gehalt Mname Abteilung Funkt Mname Grundgehalt Abs 112 Laborant Abs 50000 Beer 116 Chemiker Beer 100000 Hein 112 Konstrukteur Hein 80000 Riedle 114 Laborant Riedle 50000 Seite 8 Zulagen 20000 30000 125000 15000 Vereinigung Def.: Seien R und S zwei vereinigungsverträgliche Relationen und bezeichne t einen Tupel, dann besteht die Vereinigung aus der Menge aller Tupel, die in R oder in S oder in R und S enthalten sind: R S = {t | t R oder t S oder t R und t S} In den o.g. Beispielen sind Projekt und Ausschuß vereinigungsverträglich. Die Vereinigung ergibt. Projekt Ausschuß Ergebnis: Gebildet wird die Gesamtheit der Mitarbeiter bzw. Mitglieder ohne Doppelnennungen Personal = Projekt Ausschuß Seite 9 Durchschnitt Def.: Der Durchschnitt zweier vereinigungsverträglicher Relationen R und S besteht aus der Menge aller Tupel, die in R und in S enthalten sind: R S = {t | t R und t S} Der Durchschnitt Projekt und Ausschuß ergibt: Projekt Ausschuß Ergebnis: Mitarbeiter der/die beiden Institutionen angehören. Seite 10 Differenz Def.: Die Differenz zweier vereinigungsverträglicher Relationen R und S besteht aus der Menge aller Tupel, die in R aber nicht in S enthalten sind: R - S = {t | t R und t S} Die Differenz von Projekt und Ausschuß ergibt: Projekt - Ausschuß Ergebnis: Aus der Relation Projekt werden die Mitarbeiter selektiert, die nicht zugleich in Ausschuß vertreten sind. Seite 11 Kartesisches Produkt Def.: Seien r = (r1, r2, ..., rm) und s=(s1, s2, ..., sn) zwei Tupel, dann bezeichnet die Verkettung (=concatenation) r ~ s einen aus r und s zusammengesetzten Tupel: r ~ s = (r1, r2, ..., rm, s1, s2, ..., sm) Def.: Seien R und S zwei Relationen beliebigen Grades, dann besteht das kartesische Produkt R x S dieser Relationen aus der Menge aller möglichen Verkettungen von Tupeln aus R und aus S: R x S = {r ~ s | t R und s S} Gegeben sei die Relation Sprache: Sprache Deutsch Englisch Das kartesische Produkt von Projekt und Sprache lautet: Projekt x Sprache Seite 12 Projektion I Die Projektion ermöglicht die Abspaltung eines Teil einer Relation (bezogen auf Attribute). Die abzuspaltenden Attribute sind vorzugeben. Def.: Bezeichne R eine Relation m-ten Grades und a=(a1, ..., ai), i < m, eine Kombination von in R vertretenen Attributen, dann ist die Projektion der Relation R auf die Attributkombination definiert als: P[a] = {t[a1], t[a2], ..., t[ai]) | t R t[ai] gibt den Wert an, den das Attribut ai im Tupel t besitzt. R[a] entsteht also durch das Streichen der Spalten in Relation R, welche nicht in a enthalten sind. In R[a] darf kein Tupel mehrfach vorkommen. Beispiel: Die Projektion der Relation Personal auf die Attributkombination (Mname, Abteilung) führt zu folgender neuen Relation: Seite 13 Projektion II Die Projektion von Personal auf das Attribut Abteilung führt zu einer Verkleinerung der Spalten- und Zeilenzahl. Normalisierung beruht auf der Operation der Projektion Seite 14 Verbund (= Join) Zusammenfügung von Relationen zu neuen Relationen. In eine neue Relation gehen aber nur solche Tupelpaare aus zwei zusammenzufügenden Relationen ein, zwischen denen ein vorzugebender Zusammenhang besteht. Wenn einen der Vergleichsoperatoren "=", "", "<", "", ">", und "" bezeichnet, dann gilt für den Theta-Verbund: Def.: Seien a und b vereinigungsverträgliche Attributkombinationen aus den Relationen R und S, dann ist der Theta-Verbund der Relationen R und S über die Attributkombinationen a und b definiert als: R[a b]S = {r ~ s | r R, s S und r[a] s[b]} Zusammengefügt und in den Theta-Verbund einbezogen werden nur die Tupel r und s, die hinsichtlich der Attributkombinationen a und b die Qualifikationsbedingung r[a] s[b] erfüllen. Seite 15 Sonderfall des Gleich-Verbunds (= natural join) Hierbei gilt: gleich "=". Keine Attribute dürfen doppelt auftreten. Komplementbildung Def.: Sei a eine in der Relation R enthaltene Attributkombination und r ein Tupel aus R, dann bezeichnen (1) r[a] die Projektion des Tupels r auf die Attributkombination a. r[a] besteht aus den Werten der in a enthaltenen Attribute (2) r[a] die Projektion des Tupels r auf das Komplement a von a. r[a] besteht aus den Werten der nicht in a enthaltenen Attribute. Es gilt daher: r = r[a] ~ r[a]. Für den Tupel t = (Beer, 116, Chemiker) Personal und die Attributkombination a = (Mname, Funkt) gilt: t[a] = (Beer, Chemiker) und t[a] = (116). Seite 16 Natürlicher Verbund Def.: Der natürliche Verbund zweier Relationen R und S über die vereinigungsverträglichen Attributkombinationen a und b ist definiert als: R[a=b]S = {r[a] ~ r[a] ~ s[b] | r R, s S und r[a] = s[b]} (Hinweis: "~" bedeutet "Verkettung") Der "Natural Join" der Relationen Personal und Gehalt über die Attribute Personal.Mname und Gehalt.Mname ergibt z.B. P = Personal [Personal.Mname = Gehalt.Mname] Gehalt Seite 17 Restriktion I Es werden die Tupel aus einer Relation ausgewählt, die einer auf Attributwerten definierten Auswahlbedingung genügen. Def.: Seien a und b vereinigungsverträgliche Attributkombinationen aus der Relation R, dann ist die Restriktion der Relation R bezüglich den Attributkombinationen a und b definiert als: R[a b] = {t | t R und t[a] t[b]} Es werden diejenigen Tupel ausgewählt, für die der Wert der Attributkombination a gleich oder ungleich oder kleiner usw. ist als der Wert der Attributkombination b. Damit können z.B. Personen aus der Relation P ausgewählt werden, deren Zulagen das Grundgehalt übersteigen P[P.Zulagen > P.Grundgehalt] Seite 18 Restriktion II Im o.g. Beispiel ist das Ergebnis für P[P.Zulagen > P.Grundgehalt]: Interessiert beispielsweise nur MName und die Abteilung, dann ist o.g. Restriktion und eine Projektion auf die beiden Attribute MName und Abteilung notwendig. Q:= P[P.Zulagen > P.Grundgehalt][Mname, Abteilung] Die Ergebnisrelation Q lautet: Seite 19 SQL (Structured Query Language) Funktionsumfang: - Abfragen - Datenbeschreibung - Definition von Indizes - Mutieren von Daten Seite 20 Architektur einer relationalen Datenbank SQL-Benutzer Sicht 1 Sicht 2 externe Ebene Basistabelle 1 Basistabelle 2 Basistabelle 3 Basistabelle 4 konzeptionelle Ebene Datei1 Datei2 Datei3 Datei4 interne Ebene Seite 21 1. Datenbeschreibung mit SQL - Erzeugung von Basistabellen (CREATE TABLE-Anweisung) - Erweiterung von Basistabellen (EXPAND TABLE-Anweisung) - Löschen von Basistabellen (DROP TABLE-Anweisung) - Erzeugung von Indizes (CREATE INDEX-Anweisung) - Löschen von Indizes (DROP INDEX-Anweisung) Seite 22 Erzeugung von Basistabellen (CREATE TABLE-Anweisung) CREATE TABLE erzeugt eine leere Basistabelle. Form: CREATE TABLE basistabellenname (felddefinition [,felddefinition] ...) [IN SEGMENT segmentname] Es gibt "public" und "private" Segmente felddefinition besitzt dabei folgende Form: feldname (datentyp[,NOT NULL]) Hinweis: Schlüsselwörter werden groß und Bezeichner klein geschrieben; optionale Anweisungsteile stehen in eckigen Klammern. Datentypen: CHAR(n) CHAR(n)VAR INTEGER SMALLINTEGER FLOAT DATE LONGINTEGER ALPHANUMERIC Zeichenkette fester Länge Zeichenkette variabler Länge ganze Zahl in einem Vollwort ganze Zahl in einem Halbwort Gleitkommazahl in einem Doppelwort Datum ganze Zahl in einem Doppelwort alphanumerischer Datentyp Seite 23 entspricht dem Datentyp "Text" in Access Datenmanipulation mit SQL SQL-Anweisungen zur Datenmanipulation Auswahl (SELECT) Ändern (UPDATE) Mutation Einfügen (INSERT) Seite 24 Löschen (DELETE) 1 Einfache Auswahl 1.1 Projektion mit Duplikaten SELECT ArtikelNr, DispoMenge FROM ArtikelBew Ergebnis: 1.2 Projektion ohne Duplikate mittels DISTINCT-Spezifikation SELECT DISTINCT ArtikelNr, Ergebnis: DispoMenge FROM ArtikelBew 1.3 Kopieren einer Basistabelle SELECT * FROM ArtikelBew Seite 25 2. Qualifizierte Auswahl SELECT FROM WHERE AND ArtikelNr ArtikelBew BewDatum = 920505 Bestand > 10 Ergebnis: Verknüpfung von Einzelbedingungen mit den logischen Operatoren: NAD, OR und NOT Vergleichsoperatoren: = <>, <, <=, > und >= Auswertungsfolge kann durch Klammern beeinflußt werden. SELECT FROM WHERE AND ArtikelNr, ProjektNr ArtikelBew BewDatum = 920505 (Bestand = 0 OR Menge < 0) Ergebnis: Entnahme aus dem Bestand Auswertungsreihenfolge: Seite 26 1. Schritt: Für jeden Tupel von ArtikelBew wird geprüft, ob - der Wert von Bestand gleich Null ist oder ob - der Wert von Menge kleiner Null ist. Ist eine Bedingung erfüllt oder sind beide Bedingungen erfüllt, so ist die Bedingung in der Klammer erfüllt. 2. Schritt: Für jeden Tupel von ArtikelBew wird geprüft, ob - der Wert von BewDatum gleich 920505 ist und ob - die in der Klammer angegebene Bedingung erfüllt ist. Sind beide Bedingungen für einen Tupel erfüllt, so liegt ein zutreffender Tupel vor. Seite 27 3 Auswahl mit Sortierung Wird erzielt mit der Spezifikation: feldname[ORDER][,feldname[ORDER]] ... Absteigende Sortierung wird mittels DESC, aufsteigende Sortierung mittels ASC erzielt (DefaultWert; d.h., wird ASC weggelassen, gilt ASC). Aufsteigende Sortierung SELECT ArtikelNr, ProjektNr FROM ArtikelBew WHERE BewDatum = 920505 ORDER BY ProjektNr Absteigende Sortierung SELECT ArtikelNr, ProjektNr FROM Artikel WHERE MindBestand >= 10 ORDER BY Bestand DESC Ergebnis: Ergebnis: Seite 28 4 Auswahl mit Verbund Wenn sich die Auswahl auf mehrere Tabellen richtet, sind - der herzustellende Verbund zwischen zwei oder mehr Tabellen explizit zu formulieren und - die Zugehörigkeit jedes angesprochenen Attributs zu einer der Tabellen mittels Punktqualifizierung in der Form tabellenname.feldname anzugeben. 4.1 Auswahl mit natürlichem Verbund SELECT DISTINCT FROM WHERE AND AND ArtikelNr, Bestand Artikel, ArtikelBew ArtikelBew.ArtikelNr = Artikel.ArtikelNr Artikel.MindBestand=0 ArtikelBew.BewDatum = 920505 Ergebnis: Auswertungsschritte: 1) Verbinden der zusammengehörigen Tupel beider Tabellen 2) Eliminieren der Tupel, welche die Qualifikationsbedingungen nicht erfüllen 3) Projizieren des Zwischenergebnisses auf die auszugebenden Attribute 4) Eliminieren der Duplikate Seite 29 4.2 Auswahl mittels IN-Klausel (IN kann als "Mitgliedschaftsoperator verstanden werden) feldname IN (SELECT ... FROM ... WHERE ...) SELECT FROM WHERE AND Name KundenAuftrag AuftrNr IN (SELECT AuftrNr FROM AuftragPos WHERE ArtikelNr = 910030) First.ProjektNr < SecondProjektNr Ergebnis: - IN-Klauseln dürfen beliebig tief ineinander geschachtelt werden. - Auswahl mit NOT IN-Klausel möglich. Seite 30 4.3 Auswahl mit UNION UNION entspricht dem Verinigungsoperator "" der klassischen Mengenlehre. Damit können zwei Auswahlanweisungen miteinander verknüpft werden. Das Ergebnis der so entstehenden Gesamtanweisung besteht aus der Zusammenfassung der Ergebnisse der beiden Einzelanweisungen. Duplikate werden elimininiert. SELECT FROM WHERE ArtikelNr Artikel Bestand = MindBestand Ergebnis: UNION SELECT FROM WHERE ArtikelNr ArtikelBew BewDatum = 920505 Seite 31 4.4 Auswahl mit berechneten Werten Die SELECT und die WHERE-Klausel dürfen arithmetische Ausdrücke einschließen. Zulässig sind "/" (Division), "*" (Multiplikation), "+" (Addition), "-" (Subtraktion). SELECT FROM WHERE ArtikelNr, Preis / 100 Artikel ArtikelNr >= 900000 Ergebnis: Seite 32 4.5 Auswahl mit eingebetteten Funktionen Zulässige Funktionen sind (dazu ggf. weitere anbieterspezifische Funktionen): COUNT SUM AVG MAX MIN - Ermitteln der Anzahl von Werten einer Tabellenspalte - Bilden der Summe der Werte einer Tabellenspalte - Bilden des Durchschnitts der Werte einer Tabellenspalte - Ermitteln des größten Werts einer Tabellenspalte - Ermitteln des kleinsten Werts einer Tabellenspalte SELECT COUNT (*) FROM Ergebnis: Artikel Nachfolgend wird die Anzahl unterschiedlicher Artikel ermittelt, die in der Tabelle ArtikelBew stehen. SELECT COUNT (DISTINCT ArtikelNr) Ergebnis: FROM ArtikelBew Seite 33 4.6 Anwendung von GROUP BY und HAVING Beides sind Funktionen, die der Ergebnisdatenbildung dienen: - GROUP BY bildet aus den Zeilen der angesprochenen Tabellen Gruppen - HAVING entspricht der WHERE-Klausel, wobei die WHERE-Klausel jedoch alle Zeilen einer Tabelle betrifft, wird der nach HAVING anzugebende arithmetische Ausdruck für jede Gruppe gesondert ausgewertet. Spezifikationsform: GROUP BY feldname [,feldname, ...] [HAVING arithmetischer Ausdruck] SELECT FROM GROUP ArtikelNr, SUM (Menge) AuftragPos BY ArtikelNr Ergebnis: Es werden zwei Gruppen gebildet: Eine für die Artikelnummer 910030 (umfaßt 2 Tupel mit den Mengen 4 und 5) und eine für die Artikelnummer 920020 (1 Tupel mit der Menge 10). Nachfolgend werden die Nummern der Artikel ermittelt, die öfters als 1x geordert wurden. SELECT ArtikelNr Ergebnis: FROM AuftragPos GROUP BY ArtikelNr HAVING COUNT (*) > 1 Seite 34