SELECT-Anweisung: die Group-by-Klausel

(1)

03.04.2002 Datenbanken

Studiengang Technische Informatik / Wirtschaftsinformatik

151

SELECT-Anweisung:

die Group-by-Klausel

SELECT-Anweisung:

die Group-by-Klausel

(2)

153

die Group-by-Klausel

Die group-by-Klausel erlaubt die Zusammenfassung von Tupeln mit gleichen Eigenschaften. Sie wirkt ähnlich wie die Option DISTINCT der SELECT-Klausel:

„Die Wohnorte der Mitarbeiter“:

• Jede Ortschaft erscheint nur einmal in der Ergebnisliste

• Das Attribut, nach dem gruppiert wird, darf auch in der SELECT-Klausel stehen

• Es macht keinen Sinn, in der SELECT-Klausel weitere Attribute zu haben, nach denen nicht gruppiert wird (WARUM??)

• In der GROUP-BY-Klausel dürfen nur einfache Attribute stehen und keine Ausdrücke!

SELECT distinct Ort FROM Personal.

SELECT Ort FROM Personal GROUP BY Ort.

SELECT-Anweisung:

die Group-by-Klausel und die Statistikfunktionen

Die Mächtigkeit der group-by-Klausel ergibt sich im Zusammenwirken mit Statistikfunktionen:

„Die Wohnorte der Mitarbeiter mit der zugehörigen Anzahl der Mitarbeiter, die dort wohnen“:

SELECT Ort, COUNT(*) AS Anzahl FROM Personal

GROUP BY Ort;

(3)

155

SELECT-Anweisung:

die Group-by-Klausel und die Statistikfunktionen

• Die Statistikfunktionen beziehen sich nun nicht mehr auf die gesamte Tabelle, sondern nur auf die einzelnen Gruppen

• Als Parameter der Statistikfunktionen können natürlich auch Attribute vorkommen, über die nicht gruppiert wird!

SELECT-Anweisung:

die Group-by-Klausel und die HAVING-Klausel

• Wie kann ich Bedingungen an die zu selektierenden Gruppen stellen?

• „Alle Wohnorte, in denen mindestens zwei Mitarbeiter des Unternehmens wohnen“ mit dem aufsummierten Jahresgehalt und das höchste Gehalt der Mitarbeiter, die hier wohnen.

SELECT Ort, COUNT(*) , SUM( 12 * Gehalt ), 12 * MAX( Gehalt ) FROM Personal

GROUP BY Ort

HAVING COUNT(*) > 1;

• Die HAVING-Klausel ist nur in Zusammenhang mit der GROUP-BY-Klausel zulässig und dient der Restriktion nach der Gruppierung

• Die in HAVING verwendeten Statistikfunktionen beziehen sich auf die vorher durchgeführte Gruppierung.

(4)

157

Die ORDER-BY-Klausel

• Die Zeilen sind in Tabellen definitionsgemäß ungeordnet.

• Die ORDER-BY-Klausel hilft, die Resultate von Datenbankselektionen zu sortieren:

• ORDER BY Ort, Name

– Es wird nach dem Ort sortiert

– Innerhalb der einzelnen Orte wird alphabetisch sortiert

SELECT Ort, COUNT(*) AS Anzahl FROM Personal

GROUP BY Ort

ORDER BY Anzahl DESC, Ort;

• ASCending aufsteigend

• DESCending absteigend

SELECT-Anweisung:

Die INTO-Klausel

Wird SQL im Rahmen einer Programmiersprache verwendet, so bietet sich die INTO-Klausel an, um die Selektionsergebnisse zwischenzuspeichern (Hier im SAP-System):

Programmiersprachen müssen Kontrollstrukturen anbieten, um die Daten zu verarbeiten: Im SAP-System ist SELECT selbst eine!

************ Los! - Flugpreise *****

select carrid connid min( price ) max( price ) avg(

price )

into (g, c, minimum, maximum, mittel) from sflight

group by carrid connid.

write: / g, 15(10) minimum, 40(10) maximum, 60(10) mittel.

endselect.

(5)

159

SELECT-Anweisung:

Beispiele

• Alle Flugverbindungen mit Fluggesellschaft, Flugnummer und Abflugort, sortiert nach dem Abflugort

• In der Tabelle SFLIGHT sind die verfügbaren und belegten Plätze der einzelnen Flüge gespeichert. Finden Sie heraus:

– die maximale und minimale Anzahl sowie die im Durchschnitt belegten Plätze aller Flüge

– Tun Sie das gleiche bezogen auf die einzelnen Fluggesellschaften

SELECT-Anweisung Die WHERE-Klausel

• Die WHERE-Klausel entspricht der Restriktion

• Sie ist optional, kommt aber praktisch in allen SELECT-Anweisungen vor.

• Die Syntax: WHERE Bedingung

• Die Semantik: Nur diejenigen Zeilen werden selektiert, die die angegebene Bedingung erfüllen

(6)

161

in einem SELECT-Befehl

Beispiel

SELECT carrid count (*) FROM spfli

WHERE cityfrom = ‚FRANKFURT‘

GROUP BY carrid HAVING count(*) > 100.

• Die Where-Klausel bestimmt die Zeilen, auf die schließlich die Gruppierung angewendet wird!

• Die HAVING-Klausel muss links ein Attribut haben und rechts einen Ausdruck.

Count(*) ist eine Spalte der Ergebnistabelle!!!

(7)

163

Logische Abarbeitungsreihenfolge in einem SELECT-Befehl

• Statistikfunktionen beziehen sich auf die nach der Berücksichtigung der WHERE-Klausel ausgewählten Zeilen!

• Noch einmal: erst die FROM-Klausel betrachten, dann die WHERE-Klausel und anschließend die SELECT-Klausel.

SELECT-Anweisung Die WHERE-Klausel - Beispiele

SELECT Name, Gehalt FROM Personal WHERE Gehalt > 5000 ORDER BY Gehalt ASC;

Alle Mitarbeiter mit einem Gehalt größer als 5.000 DM, aufsteigend sortiert

1 2

Das kleinste Gehalt im Unternehmen, das größer als 5.000 DM ist SELECT Min( Gehalt )

FROM Personal WHERE Gehalt > 5000;

(8)

165

Die WHERE-Klausel

• Die Bedingung ist ein Boolscher Ausdruck; Wertebereich (true, false)

• Logische Operatoren – AND

– OR – NOT

• Bindungsstärke wie üblich: NOT bindet am stärksten, OR am schwächsten.

• Alle folgenden Operatoren binden jedoch stärker:

SELECT-Anweisung

Die WHERE-Klausel – der Intervalloperator

Ich bitte um ein Kommentar zu folgenden logischen Ausdrücken:

A BETWEEN B AND C A >= B AND A <= C

A NOT BETWEEN B AND C NOT A BETWEEN B AND C Alle Mitarbeiter, deren Gehalt zwischen 3000 DM und 4000 DM liegt SELECT *

FROM Personal

WHERE Gehalt BETWEEN 3000 AND 4000;

Alle Passagiere, deren Gepäck zwischen 21 und 23 Kg wiegt.

select * from sbook

where LUGGWEIGHT between '21.0' and '23.0'.

write: / sbook-carrid, sbook-connid, sbook-CUSTOMID, sbook-luggweight.

endselect.

(9)

167

SELECT-Anweisung

Die WHERE-Klausel – der Intervalloperator

****************** BETWEEN **************************

Select fldate count(*) from sbook into (datum, anzahl)

where fldate between '20020201' and '20020228' group by fldate.

write: / 'Datum: ', datum, 'Anzahl: ', anzahl.

endselect^.

Streik: Die Anzahl der Buchungen an bestimmten Tagen

SELECT-Anweisung

Die WHERE-Klausel – der Mengenoperator IN

Alle Mitarbeiter, die genau 3200, 3800 oder 4600 DM verdienen

• Aufzählungstyp

• Mengenzugehörigkeit

SELECT * FROM Personal

WHERE Gehalt IN (3200, 3800, 4600);

(10)

169

Die WHERE-Klausel – der Mengenoperator IN

where^fldate^IN

('20020219', '20020220', '20020221', '20020222')

group by fldate.

endselect.

Streik: Die Anzahl der Buchungen an bestimmten Tagen

SELECT-Anweisung

Die WHERE-Klausel – der Mengenoperator IN

where fldate NOT IN

('20020219', '20020220', '20020221', '20020222')

group by^fldate.

endselect.

Bestimmte Tage nicht berücksichtigen

Select fldate count(*) from sbook into^(datum,^anzahl)

where NOT fldate IN

('20020219', '20020220', '20020221', '20020222')

Unterschied der beiden Versionen?

(11)

171

SELECT-Anweisung

Die WHERE-Klausel –Auswahloperatoren

• ANY, SOME, ALL

• SOME und ANY haben die selbe Semantik

• Davor steht immer ein Vergleichsoperator

• Können leicht auch mit dem IN-Operator ausgedruckt werden

• Sinnvoll erst in Zusammenhang mit verschachtelten SELECT-Anweisungen Alle Mitarbeiter, die genau 3200, 3800 oder 4600 DM verdienen SELECT *

FROM Personal

WHERE Gehalt = ANY (3200, 3800, 4600); Gehalt = 3200 OR ...

Die Flugpläne aller Gesellschaften bis auf die LH und AA SELECT *

FROM SPFLI

WHERE carrid <> ALL (LH, AA); carrid <> LH AND carrid <> AA

SELECT-Anweisung

Die WHERE-Klausel –Auswahloperatoren

Verneinung von Gehalt = ANY (3200, 3800, 4600)

NOT ( Gehalt = ANY (3200, 3800, 4600) ) Gehalt <> ALL (3200, 3800, 4600) Gehalt = NOT ALL (3200, 3800, 4600)

Gehalt = NOT ANY (3200, 3800, 4600)

Gehalt <> ANY (3200, 3800, 4600) == Gehalt <> 3200 OR Gehalt <> 3800 Was bedeuten die folgenden Ausdrücke?

Die letzten beiden Bedingungen sind immer wahr!!

(12)

173

Die WHERE-Klausel - Unterabfragen

Gesucht sind alle Mitarbeiter, die weniger verdienen, als Frau Rank (PersNr = 3) Was verdient Frau Rank?

SELECT Gehalt

FROM Personal

WHERE PersNr = 3;

Alle, die weniger verdienen:

( SELECT Gehalt FROM Personal WHERE PersNr = 3 ) SELECT *

FROM Personal

WHERE Gehalt <

SELECT-Anweisung

Die WHERE-Klausel – Unterabfragen + Auswahloperatoren

Die üblichen Vergleichsoperatoren gehen davon aus, dass die Unterabfrage eine einelementige Menge als Resultat liefert.

Die Spitzenverdiener im Unternehmen finden SELECT *

FROM Personal

WHERE Gehalt >= ALL ( SELECT Gehalt FROM Personal ) SELECT *

FROM Personal

WHERE Gehalt >= ( SELECT MAX(Gehalt) FROM Personal ) Gültigkeit der Bezeichner:

- in der Umgebung, wo der Bezeichner eingeführt wurde

- In allen Unterabfragen, es sei denn die Unterabfrage definiert selbst einen gleichnamigen Bezeichner --Æ wie in allen „normalen“ Programmiersprachen

- Der übergeordnete Bezeichner muss ggf. qualifiziert werden

(13)

175

SELECT-Anweisung

Die WHERE-Klausel – Unterabfragen + der Existenzoperator EXISTS

• Überprüfung, ob das Ergebnis einer Unterabfrage die leere Menge ist oder nicht

SELECT *

FROM Personal AS P1

WHERE EXISTS ( SELECT * FROM Personal WHERE PersNr = 3 AND P1.Gehalt < Gehalt

)

Gesucht sind alle Mitarbeiter, die weniger als Frau Rank (PersNr = 3) verdienen

Die WHERE-Klausel – Unterabfragen + der Existenzoperator EXISTS: Beispiel

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND FLDATE BETWEEN '19990101' AND '19990331'.

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID AND CONNID = F~CONNID AND CITYFROM = 'FRANKFURT' AND CITYTO = 'NEW YORK' ) .

ENDSELECT.

Gesucht sind alle Flüge von Frankfurt nach New York im ersten Quartal 1999, die noch nicht ausgebucht sind.

(14)

177

Die WHERE-Klausel – Unterabfragen + der Mengenoperator IN

Gesucht sind alle Zielflughäfen der LH

SELECT * FROM Spfli

WHERE cityto IN ( SELECT cityto FROM Spfli WHERE carrid = ‚LH‘

)

Die Bedingung ist wahr, wenn das aktuelle Tupel in der Ergebnismenge der Unterabfrage enthalten ist.

Voraussetzung: die Ergebnistabelle ist einspaltig!

Bei einzeiligen Tabellen kann man auf Gleichheit abfragen!

SELECT-Anweisung

Die WHERE-Klausel- der Ähnlichkeitsoperator (LIKE)

• Ungenaue Suche durch den Operator LIKE

• Eine wildcard-Syntax steht zur Verfügung – % beliebig viele Zeichen – _ genau ein Zeichen

Alle Mitarbeiter finden, deren Familienname mit „F“ beginnt:

SELECT *

FROM Personal

WHERE Name LIKE ‘F%‘;

select * from scarr

where carrid LIKE 'L%'.

write: / 'Fluggesellschaft: ', scarr-carrid.

endselect^.

Alle Fluggesellschaften, deren Name mit ‚L‘ beginnen.

(15)

179

SELECT-Anweisung

Die WHERE-Klausel- der NULL Operator

Feststellung, ob ein Wert undefiniert ist SELECT *

FROM Personal

WHERE Vorgesetzt IS NULL;

Das Komplement davon:

NOT

Vorgesetzt IS NULL; Vorgesetzt IS NOTNULL;

• Wert existiert aber zur Zeit nicht bekannt

• Beim Anlegen einer Tabelle kann man die Felder entsprechend klassifizieren

• Die Datensätze werden bei einer Projektion ignoriert:

SELECT COUNT(Vorgesetzt) FROM Personal;

SELECT Name, Ort

FROM Personal

WHERE Vorgesetzt <> 1 AND Vorgesetzt <> 6;

SELECT-Anweisung

Die WHERE-Klausel- der NULL Operator

• Datenbanksysteme legen Spalten als NULL-Spalten an, wenn dies nicht explizit anders definiert wurde

• Im SAP-System werden beim Anlegen einer Tabelle alle Felder als NOT NULL Felder angelegt. Lediglich beim Erweitern einer Tabelle muss man aufpassen!

• Die Attribute erhalten automatisch einen Initialwert (typabhängig!), wenn ein Tupel angelegt wird und der Attributwert nicht festgelegt wurde.

Demo Tabelle SBOOK Felder COUNTER

AGENCYNUM

(16)

181

Verbund (join): Wiederholung

Selektion (Verbund)

Syntax:

JOIN (Relation1, rel(attr1, attr2), Relation2) Semantik:

Die Verbundoperation liefert als Ergebnis die Tupel aus dem Produkt der beiden Relationen, die in der Beziehung rel stehen.

Die Attribute, über die die Verbundoperation durchgeführt wird (join-Attribute), müssen keine Schlüsselkandidaten sein

Regeln

Die join-Attribute müssen nicht den gleichen Namen haben Die join-Attribute müssen die selben Wertebereiche haben AUTO-join: JOIN (Relation1, rel(attr1, attr2), Relation1)

Relationale Operationen Verbund (join)

H. Braun C. H. Chen N. Wirth

Autor

3-234-12985-0 Compiler

3

3-600-12345-2 Entity-Relationship-Model

2

2-509-50342-7 Die Programmiersprache PASCAL

1

ISBN Titel

Nr Bücher

3-234-12985-0 3-600-12345-2 2-509-50342-7 ISBN

Addison-Wesley Hanser Hanser

Verlag Buchverlage

JOIN (Bücher, (Bücher.ISBN = Buchverlage.ISBN), Buchverlage)

Fragestellung: Alle Bücher mit Informationen über Titel, Autor, ISBN-Nummer und Verlag

Addison-Wesley Hanser Hanser

Verlag

H. Braun C. H. Chen N. Wirth

Autor

3-234-12985-0 Compiler

3

3-600-12345-2 Entity-Relationship-Model

2

2-509-50342-7 Die Programmiersprache PASCAL

1

ISBN Titel

Nr

(17)

183

Relationale Operationen Verbund (join)

Abt1 Ulm

Mozer Sibylle 2855

Abt2 München

Kaufmann Brigitte 0258

Abt2 Augsburg

Meier Rainer 0903

Abt2 Augsburg

Probst Andreas 2934

Stuttgart Landshut München Ort

Abt1 Müller Gabrielle

1831

Abt3 Huber Petra

1010

Abt3 Keller Hans

2534

Abteilung Name

MNr Abteilung

München Freigabe

Abt3

München Diagnose

Abt2

Stuttgart Beratung

Abt1

Ort AbtBez

AbtNr

Abt1 Ulm

Mozer Sibylle 2855

Abt2 Augsburg

Meier Rainer 0903

Abt2 Augsburg

Probst Andreas 2934

Landshut Ort

Abt3 Huber Petra

1010

Abteilung Name

MNr

Fragestellung: alle Mitarbeiter, die nicht am Standort ihrer Abteilung wohnen

SELECT M.MNr, M.Name, M.Ort, M.Abteilung

FROM Mitarbeiter M, Abteilung A WHERE M.Abteilung = A.AbtNr

AND M.Ort <> A. Ort.

SELECT M.MNr, M.Name, M.Ort, M.Abteilung

FROM Mitarbeiter AS M INNER JOIN Abteilung AS A ON

M.Abteilung = A.AbtNr AND M.Ort <> A. Ort.

Beispiel natürlicher Verbund

JOIN (Personal, Personal.Persnr = Auftrag.Persnr, Auftrag)

(18)

185

Der INNER-JOIN-Operator

Natürlicher Verbund: Verbund der Tabellen über die Gleichheit gleichnamiger Spalten

SELECT *

FROM Personal NATURAL INNER JOIN Auftrag

SELECT-Anweisung Der INNER-JOIN-Operator

Probleme

• Der NATURAL-JOIN-Operator ist nicht immer realisiert (z. B. ORACLE, MS-Access, SAP)

• Er scheitert oft daran, dass die Attribute nicht gleich heißen, aber gleiche Wertebereiche haben

• Deshalb die allgemeingültige Alternative:

SELECT *

FROM Personal INNER JOIN Auftrag ON Personal.PersNr = Auftrag.PersNr;

• In Standard-SQL darf „INNER“ weggelassen werden. Das ist die „Default“-Art eines Tabellenverbunds

• In der Praxis bestehen viele Systeme darauf (MS-Access, SAP)

• Allgemeiner: θ - JOIN: Die angegebenen Spalten stehen in einer Beziehung θ, die nicht unbedingt die Gleichheit sein muss.

(19)

187

Beispiel:

outer-join

Outer-join: Die Tupel der einen Relation bleiben in der Ergebnisliste, auch wenn sie Keinen „passenden“ Eintrag in der 2. Tabelle haben. Sie erhalten dann für die Attribute der 2. Tabelle NULL-Werte

JOIN (Personal, Personal.Persnr = Personal.Vorgesetzt, Personal)

Hier werden die Attribute umbenannt!

SELECT-Anweisung Der outer-join-Operator

Frage: Liste aller Mitarbeiter mit der Anzahl der Aufträge, die sie getätigt haben

Problem: Mitarbeiter, die noch nichts verkauft haben, stehen nicht auf der Liste!

Warum steht jetzt plötzlich COUNT(Auftrag) und nicht COUNT(*) ??

(20)

189

Der join-Operator: Beispiele

Frage: Liste aller Flüge von Frankfurt aus, mit Identifikation und Name der Fluggesellschaft sowie Angabe der Flugverbindung

SELECTcarrid, connid FROM spfli

WHERE cityfrom = FRANKFURT;

Wo steht der Name der Fluggesellschaft?

SELECT Flug.carrid, Flug.connid, FG.Carrname FROM spfli AS Flug INNER JOIN scarr AS FG ON

Flug.carrid = FG.carrid WHERE cityfrom = FRANKFURT;

SPFLI wird um die Spalten von SCARR erweitert. Die Einschränkung kommt von der WHERE-Klausel

SELECT-Anweisung Der join-Operator: Beispiele

Man kann auch mehrere Tabellen miteinander verknüpfen

SELECT p~cityfrom p~cityto f~seatsmax f~seatsocc b~bookid INTO TABLE flight_tab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p~carrid = f~carrid AND p~connid = f~connid ) INNER JOIN sbook AS b

ON b~carrid = f~carrid AND b~connid = f~connid AND b~fldate = f~fldate ) WHERE f~fldate = p_fldate AND

f~seatsmax > f~seatsocc.

(21)

191

SELECT-Anweisung Der join-Operator: Beispiele

Man kann auch mehrere Tabellen miteinander verknüpfen

SELECT p~cityfrom p~cityto f~seatsmax f~seatsocc b~bookid INTO TABLE flight_tab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p~carrid = f~carrid AND p~connid = f~connid ) INNER JOIN sbook AS b

ON b~carrid = f~carrid AND b~connid = f~connid AND b~fldate = f~fldate ) WHERE f~fldate = p_fldate AND

f~seatsmax > f~seatsocc.

Beispielprogramm: Z1D99_S_SELECT_INNER_JOIN --Æ im Rechner demonstrieren!

SELECT-Anweisung Der join-Operator: Beispiele

Wenn man alle Fluggesellschaften aufgelistet haben wird, dann reicht das nicht.

Frage: Liste aller Flüge von Frankfurt aus, mit Identifikation und Name der Fluggesellschaft sowie Angabe der Flugverbindung

Das gleiche etwas anders geschrieben...

(22)

193

Der join-Operator: Beispiele

Demo_select_left_outer_join

Z1D99_DEMO_INNER_JOIN

SELECT-Anweisung Der join-Operator: Beispiele

left_outer_join

INNER_JOIN

(23)

195

SELECT-Anweisung Der join-Operator: Beispiele

Das Beispiel kennen wir, oder?

SELECT F~CARRID F~CONNID F~FLDATE INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON

F~CARRID = P~CARRID AND F~CONNID = P~CONNID WHERE P~CITYFROM = 'FRANKFURT' AND P~CITYTO = 'NEW YORK' AND F~FLDATE BETWEEN

'19970910' AND '19970920' AND F~SEATSOCC < F~SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Liste aller nicht ausgebuchten Flüge vonFrankfurt nach New Yorkzwischen dem 10. und 20. September 1997:

SELECT-Anweisung Der join-Operator: Beispiele

Auswertungsreihenfolge:

• JOIN-Operation

• WHERE-Klausel

Was ist das Ergebnis dieser Anweisung?

SELECT F~CARRID F~CONNID F~FLDATE INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P ON F~CARRID = P~CARRID

WHERE F~CONNID = P~CONNID AND P~CITYFROM = 'FRANKFURT' AND P~CITYTO = 'NEW YORK' AND F~FLDATE BETWEEN

'19970910' AND '19970920' AND F~SEATSOCC < F~SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

(24)

197

Der join-Operator: Beispiele

Ausgabe einer Liste aller Kunden mit ihren - soweit vorhanden - Flugbuchungen für den 15. Oktober 1997

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY

SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID, SBOOK-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK ON SCUSTOM~ID = SBOOK~CUSTOMID AND SBOOK~FLDATE = '19971015' ORDER BY SCUSTOM~NAME SBOOK~FLDATE.

WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID, SBOOK-BOOKID.

ENDSELECT.

Kann man SBOOK~FLDATE = '19971015‚ auch in einer WHERE-Klausel stecken?

Left Outer Join: es macht einen Unterschied, wo die Bedingung steht!

Left-outer-join:

erhebliche Unterschiede in der Realisierung

Hinweis

Für die Ergebnismenge eines SELECT-Befehls mit einem Left Outer Join in der FROM-Klausel ist es im allgemeinen von entscheidender Bedeutung, ob eine logische Bedingung in der ON- oder in der WHERE-Bedingung steht. Da jedoch nicht alle von SAP unterstützten Datenbanksysteme die genormte Syntax und Semantik des Left Outer Joins unterstützen, mußten syntaktische Einschränkungen vorgenommen werden, die nur solche Left Outer Joins zulassen, die auf allen Datenbanksystemen dieselbe Lösung liefern:

• rechts vom Operator JOIN darf nur eine Tabelle oder View stehen, nicht wieder ein Join- Ausdruck,

• als logischer Operator ist in der ON-Bedingung nur AND möglich,

• jeder Vergleich in der ON-Bedingung muß ein Feld aus der rechten Tabelle enthalten,

• kein Vergleich in der WHERE-Bedingung darf ein Feld aus der rechten Tabelle enthalten,

• als Vergleichsoperator ist in der ON-Bedingung nur EQ (bzw. =) möglich,

• die ON-Bedingung muß mindestens eine "echte" JOIN-Bedingung (eine Bedingung, in der sowohl ein Feld aus tabref1 als auch ein Feld aus tabref2 auftritt) enthalten.

(25)

199

Weitere Abweichungen vom SQL-Standard

Open-SQL Unterschiede zu Standard-SQL SELECT-Anweisung

• SELECT-Klausel: die Attribute werden nicht durch ‚,’ getrennt

• SELECT-Klausel : keine Ausdrücke , lediglich einfache Attribute und Statistikfunktionen

• INTO-Klausel: die selektierten Attribute zwischenzuspeichern

• Die SELECT-Anweisung selbst ist eine Schleife. Man kann die selektierten Werte unmittelbar verarbeiten

• Join: die JOIN-Art muss explizit angegeben werden. Die Attribute werden mit T~Attribut statt T.Attribut angesprochen

• NULL-Werte

o SAP legt beim Anlegen einer Tabelle alle Attribute als NOT NULL an

o Änderungsoperationen (INSERT, UPDATE) hinterlassen keine NULL-Werte auf der Datenbank

o NULL-Werte können entstehen, wenn eine Tabelle erweitert wird und beim LEFT OUTER JOIN. In jedem Fall werden aber diese Werte bei der Übergabe an das ABAP- Programm durch typgerechte Initialwerte ersetzt.

SELECT-Anweisung Der join-Operator: Beispiele

Selektiere alle Flüge von Frankfurt nach New York zwischen dem 10.9.97-20.9.97 mit freien Plätzen und gib sie mit dem Namen der Fluggesellschaft aus.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F~CARRID = P~CARRID AND F~CONNID = P~CONNID )

INNER JOIN SCARR AS C ON F~CARRID = C~CARRID

WHERE P~CITYFROM = 'FRANKFURT' AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '19970910' AND '19970920' AND F~SEATSOCC < F~SEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT- CARRID, WA-FLIGHT-CONNID.

ENDSELECT.

(26)

201

Welche Zeilen?

Welche Tabelle

Welche Spalten?

Fehlt diese Klausel, so werden alle Zeilen verändert

Manipulationsbefehle in SQL update

Alle Mitarbeiter, die weniger verdienen als 5000 DM erhalten eine Gehaltserhöhung um 5%

Die Mächtigkeit dieses Befehls liegt in den vielfältigen Möglichkeiten der WHERE- Klausel:

-Sie können eine DB ruinieren!

-Positiv: Sie können mit einem Befehl alle Datensätze einer Tabelle aktualisieren -Sie können NULL-Werte in die DB schreiben

(27)

203

Manipulationsbefehle in SQL delete

Manipulationsbefehle in SQL insert

• Eine neue Zeile mit VALUES ...

– Die Anzahl der in der Spaltenliste angegebenen Attribute muss mit der Anzahl der Werte in der Auswahlliste übereinstimmen

– Wird die Spaltenliste ausgelassen, so sollten alle Attribute in der Auswahlliste angegeben werden und zwar in der durch die Definition der Relation angegebenen Reihenfolge (!!! -Æ problematisch!!)

– Die Datentypen der angegebenen Attribute müssen übereinstimmen.

• Mehrere Zeilen mit einer SELECT-Anweisung

– Da kann irgendetwas stehen, was eine Tabelle zurückliefert, die zur Spaltenliste

„kompatibel“ ist

Wie kann ich eine neue Zeile an einer bestimmten Position in der Tabelle einfügen?

(28)

205

insert

Die fehlenden Attribute werden mit NULL vorbelegt. Was passiert, wenn dies bei der Definition der Tabelle verboten wurde?

Der neue Kollege soll in die gleiche Abteilung wie Herr Noster und soll das gleiche Gehalt erhalten

Datenbankdesign

• Wir haben bislang die Existenz einer Datenbank vorausgesetzt. Aber...

• Wie kommt man zu Tabellendefinitionen?

– Eine oder mehrere Tabellen?

– Wenn mehrere, wie hängen diese zusammen?

– Wie komme ich zu den Tabellen?

– Wie komme ich zu den Attributen?

– Wie komme ich zu den Fremdschlüsseln?

– Wie bestimme ich den Primärschlüssel einer Tabelle?

• Wir werden lernen, dass es keinen Sinn macht, schnell in Access ein Paar Spalten zu machen...

• Normalisierung von Relationen:

einzelne Tabellen werden untersucht

• Entwicklungsmethodologie:

wie komme ich vom Anwendungsbereich zum relationalen Datenmodell: hier werden alle Tabellen und ihre Beziehungen betrachtet.

(29)

207

Normalformenlehre

• Wir gehen davon aus, dass wir das Relationenmodell bereits entwickelt haben

• Die Normalformenlehre untersucht die Abhängigkeiten zwischen den Attributen einer Relation.

• Ziel dieser Untersuchung ist die Erkennung und Vermeidung von redundanter Information.

• Was heißt „normal“? Der Begriff ist unglücklich, der Inhalt sehr wichtig!

• Es gibt mehrere Normalformen, die aufeinander aufbauen: die Einschränkungen bzgl. der Attribute einer Relation nehmen zu.

Normalformenlehre

Einkauf K23

8000 München Rhein 25

Stark 124

Lager K25

8000 München Donau 22

Schmid 213

Einkauf K23

8000 München Isar 12

Schulz 123

AbtBez AbtNr

Ort Straße

Name MNr

Redundante Information: sie kann ohne Informationsverlust weggelassen werden

Mitarbeiter

Die Bezeichnung der Abteilung wird für jeden Mitarbeiter einer Abteilung separat aufgeführt. Auf diese Information kann verzichtet werden. Stattdessen sollte die Bezeichnung der Abteilungen separat in einer anderen Tabelle gespeichert werden

(30)

209 K23

8000 München Rhein 25

Stark 124

K25 8000 München Donau 22

Schmid 213

K23 8000 München Isar 12

Schulz 123

AbtNr Ort

Straße Name

MNr

Lager K25

Einkauf K23

Mitarbeiter

Abteilung

Probleme bei der redundanten Datenhaltung

• INSERT-Anomalie: Wenn im Unternehmen eine neue Abteilung entsteht, die noch keine Mitarbeiter hat, kann sie nicht gespeichert werden. Abteilungen können nur in Zusammenhang mit Mitarbeitern existieren!

• DELETE-Anomalie: Wenn alle Mitarbeiter einer Abteilung kündigen, oder versetzt werden, existiert anschließend die Abteilung automatisch auch nicht mehr!

• UPDATE-Anomalie: Wenn die Bezeichnung einer Abteilung geändert werden muss, dann sind alle Datensätze der Relation Mitarbeiter zu ändern.

Offensichtlich besteht ein Zusammenhang zwischen der Existenz redundanter Information und den Abhängigkeiten der Attribute untereinander!

(31)

211

1. Normalform

• Definition: Eine Relation ist in erster Normalform, wenn die Wertebereiche ihrer Attribute nur elementare Objekte enthalten können.

• Mengen, Aufzählungstypen oder Wiederholungsgruppen sind nicht zugelassen.

P3 8000 München Donau 22

Schmid 213

{P1, P2}

Schulz 123

Projekt Ort

MNr

Beispiel: unnormalisierte Relation Projektmitarbeiter

1. Normalform

Schulz P2

123

P3 P1 ProjektNr

Schmid 213

Schulz 123

Ort Straße

Name MNr

Überführung in die 1. Normalform: für jedes Element der Gruppe {P1, P2} wird eine Zeile eingetragen

Projektmitarbeiter

Der Schlüssel der Relation muss angepasst werden: (MNr, ProjektNr)

Sind wir fertig? Hat die Tabelle jetzt keine redundanten Daten mehr?

Die Mitarbeiterdaten können mehrfach gespeichert sein, wenn ein Mitarbeiter an mehreren Projekten beteiligt ist!

Die 1. Normalform reicht nicht aus, um die angesprochenen Probleme zu lösen!

(32)

213

• Definition: Ein Attribut Y einer Relation R heißt funktional abhängig vom Attribut X derselben Relation, wenn zu jedem X-Wert höchstens ein Y-Wert möglich ist.

• Notation X Æ Y

• “X bestimmt den Wert von Y“

• „Wenn ich X kenne, dann kenne ich Y auch“

• Zu jedem Wert von X gibt es höchstens einen Wert von Y („höchstens“ wegen NULL-Werten)

• Die Definition gilt auch, wenn X ein zusammengesetztes Attribut ist

Wir denken nach über folgende Aussagen nach:

• Alle Attribute einer Relation sind vom Primärschlüssel der Relation abhängig

• Alle Attribute einer Relation sind von jedem Schlüsselkandidaten der Relation abhängig

• Diese Eigenschaft gilt nicht für eine Teilmenge der Attribute eines Schlüsselkandidaten.

Funktionale Abhängigkeit Beispiele

Beispiel

Schulz P2

123

P3 P1 ProjektNr

Schmid 213

Schulz 123

Ort Straße

Name

MNr MNr Æ Name

Die so konstruierte Tabelle ist zwar in der 1. Normalform, unser Ziel, keine redundante Daten zu haben, wird aber noch nicht erreicht!

Das hängt offensichtlich damit zusammen, dass die Mitarbeiterdaten (Name, Straße, Ort) allein durch die Angabe der Personalnummer eindeutig bestimmt sind. Das Attribut ProjektNr ist dazu nicht notwendig.

(33)

215

Funktionale Abhängigkeit Beispiele

Primärschlüssel

Es gilt aber schon:

Volle Funktionale Abhängigkeit

Ein Attribut ist von einer Attributkombination voll funktional abhängig, wenn es funktional abhängig ist und diese Eigenschaft bereits durch das Weglassen eines Attributs der Attributkombination verloren geht.

Volle funktionale Abhängigkeit

Schulz P2

123

P3 P1 ProjektNr

Schmid 213

Schulz 123

Ort Straße

Name MNr

Gegenbeispiel

Das Attribut Name ist von der Schlüsselkombination (MNr, ProjektNr) nicht voll funktional abhängig.

(34)

217 Eine Relation befindet sich in der 2. Normalform, wenn sie in der 1. Normalform ist und zusätzlich jedes Nichtschlüsselattribut vom Primärschlüssel voll funktional abhängig ist.

Definition

Schulz P2

123

P3 P1 ProjektNr

Schmid 213

Schulz 123

Ort Straße

Name MNr

Beispiel: woran liegt das Problem hier?

In natürlichen Begriffen denken: Die Tabelle Projektmitarbeiter vereinigt offensichtlich zwei

„Welten“: Projekte und Mitarbeiter.

1. Normalform Æ Æ Æ 2. Normalform

Schulz P2

123

P3 P1 ProjektNr

Schmid 213

Schulz 123

Ort Straße

Name MNr

Wir speichern getrennt Projekt- und Mitarbeiterdaten. Dann überlegen wir uns, wie die Information festgehalten werden kann, wer an welchen Projekten beteiligt ist!

Schulz 123

Schmid 213

Ort Straße

Name MNr

...

P2

....

P1

Projektleiter Bezeichnung

ProjektNr

P3 213

P2 123

P1 123

ProjektNr MNr

Projekt

Mitarbeiter Projektzugehörigkeit

Bitte achten Sie auf den Schlüssel der Relationen!

(35)

219

2. Normalform

Wir denken nach über folgende Aussage nach:

• Eine Relation in der 1. NF ist automatisch in der 2. NF, wenn ihr Primärschlüssel nicht zusammengesetzt ist.

• Die Tabellen sehen jetzt ganz gut aus, die Redundanzen sind im wesentlichen beseitigt!

So wie in einem guten Film, wo der Hauptdarsteller nicht wesentlich früher als das Ende der Handlung sterben darf, so auch hier: Es muss an dem Beispiel liegen,

dass alles so gut aussieht...

2. Normalform weiteres Beispiel

Wir haben keine Redundanzen Beseitigt!

Wir können immer die 2. NF erreichen, wenn wir einen künstlichen, nicht zusammengesetzten Schlüssel einführen!

(36)

221

Funktionale Abhängigkeiten

Transitive Abhängigkeiten

8000 München 8000 München 8000 München Ort

K23 K25 K23 AbtNr

Lager Isar 12

Schulz1 223

Einkauf Donau 22

Schmid 213

Einkauf Isar 12

Schulz 123

AbtBez Straße

Name MNr

Diese Relation befindet sich offensichtlich in der 2. Normalform (Warum?)

Wir erkennen: Es gibt ein Problem mit dem Attribut AbtBez: Eine Abteilungsbezeichnung Kommt so oft in der Tabelle vor, wie die Abteilung Mitarbeiter hat!

(37)

223

Transitive Abhängigkeiten

Wir erkennen: MNr -Æ AbtNr (OK im Rahmen der 2. NF)

MNr AbtNr AbtBez

Wir erkennen: Die Abteilungsbezeichnung steht auch fest, wenn man die Mitarbeiternummer kennt, diese Abhängigkeit ist aber nicht direkt!

Transitive Abhängigkeiten

MNr AbtNr AbtBez

Ein Attribut C ist von einem Attribut A transitiv abhängig, wenn es ein Attribut B gibt, so dass C funktional abhängig von B ist, B funktional abhängig von A

und nicht gleichzeitig A von B abhängig ist.

Typische Situation: Schlüsselkandidaten!

Definition

(38)

225

(nach Codd)

Eine Relation befindet sich in der dritten Normalform, wenn sie in der 2. NF ist und kein Nichtschlüsselattribut von einem Schlüsselkandidaten transitiv abhängig ist.

Definition

2. Normalform Æ Æ Æ 3. Normalform

K23 K25 K23 AbtNr

Lager Isar 12

Schulz 123

Einkauf Donau 22

Schmid 213

Einkauf Isar 12

Schulz 123

AbtBez Straße

Name MNr

K23 K25 K23 AbtNr

Isar 12 Schulz

123

Donau 22 Schmid

213

Isar 12 Schulz

123

MNr

Lager K25

Einkauf K23

Dritte Normalform

(nach Codd): Beispiel (Prof. Sauer)

STUDENT_X(S#, NAME, GEB , ADR, FB#, FBNAME , DEKAN) S1 Karl 01.10.48 xxx 20 Informatik Hechler S2 Vera 21.08.49 xxx 20 Informatik Hechler S3 Vera 13.05.48 xxx 19 Elektrotechnik Jung S4 Maria 04.12.47 xxx 20 Informatik Hechler S5 Tom 11.01.47 xxx 20 Informatik Hechler S6 Fritz 01.03.49 xxx 19 Elektrotechnik Jung

Der Wechsel eines "Dekans" bewirkt hier mehrere Änderungen in der Tabelle (update dependence). Sie sind verursacht durch folgende Abhängigkeiten, die sich durch die folgenden Transitivitäten ausdrücken lassen:

DEKAN FB

S # → # →

(39)

227

Dritte Normalform

(nach Codd): Beispiel (Prof. Sauer)

Überführung in die DNF

STUDENT(S#, NAME, ADR, GEB, FB#) S1 Karl xxx 01.10.48 20 S2 Vera xxx 21.08.48 20 S3 Vera xxx 13.05.48 19 S4 Maria xxx 04.12.47 20 S5 Tom xxx 11.01.47 20 S6 Fred xxx 01.03.49 19

FACHBEREICH(FB#, FBNAME , DEKAN) 20 Informatik Hechler 19 Elektrotechnik Jung

Weitere Normalformen

In der Praxis hört man meistens an dieser Stelle auf.Es gibt weitere

Abhängigkeitsformen, die zu Datenredundanzen führen, diese treten aber relativ selten auf.

Wir sind hier nicht die Praxis, sondern die Theorie, also machen wir ein Stück weiter...

Die ersten drei Normalformen beseitigen Redundanzen im Bereich der

Nichtschlüsselattribute, die 4. Und 5. Normalform im Schlüsselbereich. Unter den folgenden Voraussetzungen ist aber der Normalisierungsprozess nicht befriedigend beendet:

• die Relation hat mehrere Schlüsselkandidaten

• die Schlüsselkandidaten sind zusammengesetzt

• die Schlüsselkandidaten überlappen sich mit dem Primärschlüssel, d.h.: Sie haben mindestens ein Attribut mit dem Primärschlüssel gemeinsam

(40)

229

(nach Boyce/Codd)

• Eine Determinate ist ein (u.U. zusammengesetztes Attribut), von dem ein anderes Attribut voll funktional abhängig ist.

• Eine Relation ist in der 3. NF nach Boyce/Codd, wenn sie in der 3. NF ist und jede Determinante dieser Relation ein Schlüsselkandidat ist.

Definition

Die folgende Definition ist stärker:

Dritte Normalform (nach Boyce/Codd)

Gegenbeispiel

Wenn wir annehmen, dass ein Dozent zu einem Termin nur eine Vorlesung halten kann und eine bestimmte Vorlesung auch nur an einem Termin stattfinden kann, dann hat diese Tabelle zwei Schlüsselkandidaten:

Schlüsselkandidaten

• Dozent, Termin

• Termin, Vorlesung

Vorlesung Termin

Dozent

Das Attribut Dozent ist von Vorlesung voll funktional abhängig. Vorlesung ist aber kein Schlüsselkandidat!

(41)

231

Dritte Normalform (nach Boyce/Codd)

Problem:

• Eine Vorlesung kann solange nicht gespeichert werden, bis wir einen Dozenten haben, der sie hält (N.N. gibt es nicht!) -Æ Insert-Anomalie

Dozent

Überführung in die BC-Normalform

Dozent Vorlesung

Angebot der Dozenten

Vorlesungsverzeichnis

Vorlesungsverzeichnis

Analog gibt es Pläne mit dem Angebot der Dozenten!

(42)

233

ein weiteres Beispiel

• {Verk_Nr, Produktname} {Umsatz}

• {Verk_Nr} {Verk_Name, Verk_Adresse, PLZ}

• Schlüsselkandidaten: Nr, (Verk_Nr, Produktname)

Die Relation ist in 2. NF (warum?), nicht aber in 3. NF nach BC, wegen 2. (Verk_Nr ist kein Schlüsselkandidat)

Überführung in die dritte Normalform:

ein Beispiel

Günther Matthiessen / Michael Unterstein: Relationale Datenbanken und SQL, Bonn, 1997 Addison-Wesley

In einem fiktiven Unternehmen der Branche Versandhandel sollen die Lieferscheine und Rechnungen entsprechend dem nachstehenden Bild in einer Datenbank verwaltet werden.

Problemstellung

(43)

235

Überführung in die dritte Normalform:

ein Beispiel

• Schritt

Datensammlung

(44)

237

3 2 5,20 7

Bex-Bier 6er Pack G003 Kayhude 23863

Moor-damm 34 Stein, Peter 101

2.5.96 960151

...

4 2 12,45 7

Portwein 0,5l

G002 Kayhude 23863

Moor-damm 34 Stein, Peter 101

2.5.96 960151

Menge MWST Preis Lager Bez.

Pack.

Anr Ort PLZ Str.

Name KNR Datum LNr

Die Relationen wären nun prinzipiell schon in einer Datenbank speicherbar d.h. man kann problemlos die entsprechenden Tabellen anlegen. Im Betrieb würde es allerdings mit diesen Tabellen zu zahlreichen Problemen kommen, die hier nur exemplarisch aufgezeigt werden sollen um den Sinn der Normalisierung zu demonstrieren:

• Es kann keine Kunden geben, die keinen Lieferschein haben. Löscht man irgendwann alle Lieferscheine zu einem Kunden, dann ist auch die Information verloren, dass der Kunde mal existiert hat (Deletion-Anomalie)

• Ein Kunde ist erst dann ein Kunde, wenn er den ersten Lieferschein bekommen hat (Insert- Anomalie)

• Informationen über Kunden werden redundant gehalten, was bei Änderungen leicht zu Problemen führen kann (Update-Anomalie)

Bestimmung der funktionalen Abhängigkeiten

In den Lieferdaten hat man zwei offensichtliche und eine etwas versteckte funktionale Abhängigkeit

• {LieferNr} {Datum, KdNr}

• {LieferNr} {Name, Vorname, Straße, HausNr, PLZ, Ort}

• {Straße, HausNr, Ort} {PLZ}

In den Lieferpositionsdaten sind die funktionalen Abhängigkeiten etwas versteckter, aber auch vorhanden.

• {ArtNr} {Packung, Bezeichnung, Lager, Preis, Mwst}

• {LieferNr, ArtNr} {Menge}

(45)

239

Überführung in die 2. NF

Diese Relation ist in der 2. Normalform (Warum?)

Überführung in die 2. NF

Diese Relation ist noch nicht in der 2. NF (Warum?)

(46)

241

Diese Relationen sind auch in der 3. Normalform

Überführung in die 3. NF

{LieferNr} {Datum, KdNr}

{KdNr} {Name, Vorname, Straße,

HausNr, PLZ, Ort}

{Straße, HausNr, Ort} {PLZ}

{PLZ} {Ort}

(47)

243

Mehrwertige Abhängigkeit

VORLESUNG_DOZENT VORLESUNG

DB (Datenbanken)

AE (Anwendungsentwicklung)

DOZENTEN_NAME Jürgen, Ernst Alex

MERKMAL Grundlagen, Übungen Einführung, Übungen nicht normalisierte Relation

• Eine bestimmte Vorlesung kann von beliebig vielen Dozenten gehalten werden und kann beliebig viele Merkmale zeigen.

• Zwischen Dozenten und Merkmalen eines Kurses (Vorlesung) bestehen keinerlei Abhängigkeiten

• Dozenten und Merkmale können mit jedem beliebigen Kurs in Verbindung stehen

Mehrwertige Abhängigkeit

VORLESUNG_DOZENT_NORMALISIERT

VORLESUNG DOZENT_NAME MERKMAL

DB Jürgen Grundlagen

DB Jürgen Übungen

DB Ernst Grundlagen

AE Alex Einführung

AE Alex Übungen

DB Ernst Übungen

normalisierte Relation

Die Relation ist in der 3. BC- Normalform, da alle drei Attribute zum Schlüssel gehören!

• Jeder Dozent wird für dieselbe Vorlesung mehrfach aufgeführt

• Ein Merkmal einer und der selben Vorlesung wird mehrfach aufgeführt.

Der Wert „DB“ des Attributs Vorlesung bestimmt eine Menge von Werten des Attributs Merkmal und zwar unabhängig vom Wert des Attributs Dozent_Name.

(48)

245 Sprachelemente

Erfinder Methode

Beziehungstyp Chen

Datenmodellierung

Objekttyp Chen

Datenmodellierung

Verzweigung Schneiderman

Struktogramm

Iteration Schneiderman

Struktogramm

Sequenz Schneiderman

Struktogramm

Verzweigung Nassi

Struktogramm

Iteration Nassi

Struktogramm

Sequenz Nassi

Struktogramm

Die Tabelle ist bereits in der 3. Normalform.

• Jeder Erfinder wird für dieselbe Methode mehrfach aufgeführt

• Ein Sprachelement einer und der selben Methode wird mehrfach aufgeführt.

Der Wert „Struktogramm“ des Attributs Methode bestimmt eine Menge von Werten des Attributs Sprachelemente und zwar unabhängig vom Wert des Attributs Erfinder.

4. Normalform

In einer Relation R mit Attributen A, B und C ist C mehrwertig von A abhängig, wenn ein Wert von A unabhängig vom Wert von B mehrere Werte von C eindeutig bestimmt.

Eine Relation ist in der 4. Normalform, wenn sie in der 3. Normalform ist und keine Mehrwertigen Abhängigkeiten enthält.

(49)

247

Überführung in die 4. Normalform

Sprachelemente Erfinder

Methode

Beziehungstyp Chen

Datenmodellierung

Objekttyp Chen

Datenmodellierung

Struktogramm

Verzweigung Nassi

Struktogramm

Iteration Nassi

Struktogramm

Sequenz Nassi

Struktogramm

• Jeder Erfinder wird für dieselbe Methode mehrfach aufgeführt

Datenmodellierung Chen

Struktogramm Schneiderman

Struktogramm Nassi

Methode Erfinder

Überführung in die 4. Normalform

Sprachelemente Erfinder

Methode

Beziehungstyp Chen

Datenmodellierung

Objekttyp Chen

Datenmodellierung

Struktogramm

Verzweigung Nassi

Struktogramm

Iteration Nassi

Struktogramm

Sequenz Nassi

Struktogramm

• Ein Sprachelement einer und der selben Methode wird mehrfach aufgeführt.

Beziehungstyp Objekttyp Verzweigung Iteration Sequenz Sprachelemente

Datenmodellierung Datenmodellierung Struktogramm Struktogramm Struktogramm Methode

(50)

249

Übersicht

Alle Relationen

1. NF (Wertebereiche der Attribute sind elementare Objekte 4. NF (keine mehrwertigen Abhängigkeiten) BC-Normalform (nur Abhängigkeiten von einem

Schlüsselkandidaten zulässig) 3. NF (keine transitiven Abhängigkeiten

der Nichtschlüsselattribute) 2. NF (alle Nichtschlüsselattribute sind vom

Primärschlüssel abhängig)

Einschränkung

Datenbankdesign

• Wir haben bislang die Existenz einer Datenbank vorausgesetzt. Aber...

• Wie kommt man zu Tabellendefinitionen?

– Eine oder mehrere Tabellen?

– Wenn mehrere, wie hängen diese zusammen?

– Wie komme ich zu den Tabellen?

– Wie komme ich zu den Attributen?

– Wie komme ich zu den Fremdschlüsseln?

– Wie bestimme ich den Primärschlüssel einer Tabelle?

• Wir werden lernen, dass es keinen Sinn macht, schnelle in Access ein Paar Spalten zu machen...

• Normalisierung von Relationen:

einzelne Tabellen werden untersucht

• Entwicklungsmethodologie:

wie komme ich vom Anwendungsbereich zum relationalen Datenmodell: hier werden alle Tabellen und ihre Beziehungen betrachtet.

(51)

251

Entwicklungsmethodologie

• Wir brauchen eine Entwicklungsmethodologie, die uns sicher von der Aufgabenstellung zum fertigen relationalen Datenmodell führt!

• Ausgangspunkt ist die Datenanalyse

• Das konzeptionelle Schema wird mit Hilfe eines Entity-Relationship-Modells beschrieben

ER-Diagramme

Beispiel (Projektkosten)

Jeder Mitarbeiter ist eindeutig einer Abteilung zugeordnet. Projekte sind aufgrund einer Projektnummer eindeutig identifizierbar. Ein Mitarbeiter kann gleichzeitig an mehreren Projekten mit jeweils einem Anteil seiner Arbeitszeit teilnehmen.

Mitarbeiter Projekt

Abteilung AbtlZugeh.

Projektzugeh.

Das Entity-Relationship-Modell soll nun die Ergebnisse der Datenanalyse formalisieren:

(52)

253

Das angegebene ER-Modell beschreibt die Anwendung mit Hilfe von Objekten (Mitarbeiter, Abteilung, Projekt) und Beziehungen zwischen Objekten

(Projektzugehörigkeit, Abteilungszugehörigkeit). Anschließend soll die Überführung des ER-Modells in ein relationales Datenbankschema weitgehend automatisch ablaufen:

Das Entity-Relationship-Modell (ERM)

• Erfinder: Chen (1976)

• Grafische Sprache

• Es gibt (leider) viele Varianten dieser Sprache...

Das ER-Modell bedient sich einer grafischen Sprache (die Entity-Relation-ship- Diagramme), um Informationen und deren Beziehungen zu beschreiben. Die Elemente dieser Sprache werden im folgenden definiert:

Objekte (Entities) sind Gegenstände oder Sachverhalte aus der realen Welt, die eindeutig identifizierbar sind. Objekte können in verschiedenen Objekttypen klassifiziert werden (Mitarbeiter, Projekt) und durch Merkmale (Attribute) charakterisiert werden (Mitarbeitername, Projektnummer). Jeder Objekttyp besitzt einen Identifikationsschlüssel, dessen Wert die Objekte eines Typs eindeutig bestimmt.