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
Relationenschema
Ein Relationenschema ist eine Objekttypdefinition für eine bestimmte Relation, geschrieben als Name der Relation, gefolgt von der Liste ihrer Attribut-Domänen- Paare (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.
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:
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
Vom ER-Modell zum Relationenmodell
Gesamtprozeß des logischen Datenbankdesigns:
ER-Modell Semantisch reichhaltig
Relationenmodell Leichter zu implementieren
Normalisierung Auflösen von Anomalien
Integrität 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
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.
Grundlage einer direkt implementierbaren relationalen Abfragesprache2.
Theoretische Grundlage höherer relationaler Abfragesprachen3.
Maßstab für deren Ausdrucksmächtigkeit4.
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.
Daraus leiten sich die nachfolgend zu besprechenden Grundoperationen des Relationenmodells ab:
1. Selektion (Restriktion) 2. Projektion
3. Kartesisches Produkt 4. Union (Vereinigung)
5. Intersektion (Schnittmenge) und Differenz 6. 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.
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 Grundge-
halt
Zulagen
Abs 112 Laborant Abs 50000 20000
Beer 116 Chemiker Beer 100000 30000
Hein 112 Konstrukteur Hein 80000 125000
Riedle 114 Laborant Riedle 50000 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ß
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.
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.
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
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:
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
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.
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:
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
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]
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:
SQL (Structured Query Language)
Funktionsumfang:
- Abfragen
- Datenbeschreibung
- Definition von Indizes
- Mutieren von Daten
Architektur einer relationalen Datenbank
SQL-Benutzer
Sicht 1 Sicht 2 externe Ebene
Basis- tabelle
2
Basis- tabelle
4
konzeptionelle Ebene
Basis- tabelle
3 Basis-
tabelle 1
Datei2 Datei3 Datei4 interne Ebene
Datei1
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)
Erzeugung von Basistabellen (CREATE TABLE-Anweisung)
CREATE TABLE erzeugt eine leere Basistabelle.
Form:
CREATE TABLE basistabellenname
(felddefinition [,felddefinition] ...) [IN SEGMENT segmentname]
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) Zeichenkette fester Länge CHAR(n)VAR Zeichenkette variabler Länge INTEGER ganze Zahl in einem Vollwort SMALLINTEGER ganze Zahl in einem Halbwort
FLOAT Gleitkommazahl in einem Doppelwort
DATE Datum
LONGINTEGER ganze Zahl in einem Doppelwort ALPHANUMERIC alphanumerischer Datentyp
Es gibt "public" und "private"
Segmente
entspricht dem Datentyp "Text" in Access
Datenmanipulation mit SQL
SQL-Anweisungen zur Datenmanipulation
Auswahl (SELECT) Mutation
Ändern (UPDATE)
Einfügen (INSERT)
Löschen (DELETE)
1 Einfache Auswahl
1.1 Projektion mit Duplikaten SELECT ArtikelNr,
DispoMenge
Ergebnis:
FROM ArtikelBew
1.2 Projektion ohne Duplikate mittels DISTINCT-Spezifikation SELECT DISTINCT ArtikelNr,
DispoMenge
Ergebnis:
FROM ArtikelBew
1.3 Kopieren einer Basistabelle SELECT *
FROM ArtikelBew
2. Qualifizierte Auswahl
SELECT ArtikelNr Ergebnis:
FROM ArtikelBew
WHERE BewDatum = 920505 AND Bestand > 10
Verknüpfung von Einzelbedingungen mit den logischen Operatoren:
NAD, OR und NOT
Vergleichsoperatoren: = <>, <, <=, > und >=
Auswertungsfolge kann durch Klammern beeinflußt werden.
SELECT ArtikelNr, ProjektNr Ergebnis:
FROM ArtikelBew
WHERE BewDatum = 920505
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.
3 Auswahl mit Sortierung
Wird erzielt mit der Spezifikation:
feldname[ORDER][,feldname[ORDER]] ...
Absteigende Sortierung wird mittels DESC, aufsteigende Sortierung mittels ASC erzielt (Default- Wert; d.h., wird ASC weggelassen, gilt ASC).
Aufsteigende Sortierung
SELECT ArtikelNr, ProjektNr Ergebnis:
FROM ArtikelBew
WHERE BewDatum = 920505 ORDER BY ProjektNr
Absteigende Sortierung
SELECT ArtikelNr, ProjektNr Ergebnis:
FROM Artikel
WHERE MindBestand >= 10
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 VerbundSELECT DISTINCT ArtikelNr, Bestand Ergebnis:
FROM Artikel, ArtikelBew
WHERE ArtikelBew.ArtikelNr = Artikel.ArtikelNr
AND Artikel.MindBestand=0
AND ArtikelBew.BewDatum = 920505
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
4.2 Auswahl mittels IN-Klausel (IN kann als "Mitgliedschaftsoperator verstanden werden)
feldname IN (SELECT ... FROM ... WHERE ...)
SELECT Name Ergebnis:
FROM KundenAuftrag
WHERE AuftrNr IN
(SELECT AuftrNr FROM AuftragPos
WHERE ArtikelNr = 910030) AND First.ProjektNr < SecondProjektNr
- IN-Klauseln dürfen beliebig tief ineinander geschachtelt werden.
- Auswahl mit NOT IN-Klausel möglich.
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 ArtikelNr Ergebnis:
FROM Artikel
WHERE Bestand = MindBestand
UNION
SELECT ArtikelNr
FROM ArtikelBew
WHERE BewDatum = 920505
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 ArtikelNr, Preis / 100 Ergebnis:
FROM Artikel
WHERE ArtikelNr >= 900000
4.5 Auswahl mit eingebetteten Funktionen
Zulässige Funktionen sind (dazu ggf. weitere anbieterspezifische Funktionen):
COUNT - Ermitteln der Anzahl von Werten einer Tabellenspalte SUM - Bilden der Summe der Werte einer Tabellenspalte
AVG - Bilden des Durchschnitts der Werte einer Tabellenspalte MAX - Ermitteln des größten Werts einer Tabellenspalte
MIN - Ermitteln des kleinsten Werts einer Tabellenspalte
SELECT COUNT (*) Ergebnis:
FROM Artikel
Nachfolgend wird die Anzahl unterschiedlicher Artikel ermittelt, die in der Tabelle ArtikelBew stehen.
SELECT COUNT (DISTINCT ArtikelNr) Ergebnis:
FROM ArtikelBew
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 ArtikelNr, SUM (Menge) Ergebnis:
FROM AuftragPos
GROUP BY ArtikelNr
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: