datenbankentwicklung iv lk n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Datenbankentwicklung IV-LK PowerPoint Presentation
Download Presentation
Datenbankentwicklung IV-LK

Loading in 2 Seconds...

play fullscreen
1 / 18

Datenbankentwicklung IV-LK - PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on

Datenbankentwicklung IV-LK. Warum Datenbanken? Anforderungsanalyse für Datenbanken Ableitung von Tabellenstrukturen Normalisierung Relationenalgebra Formalisierung von Tabellen in SQL SQL- Anfragen Gruppierungen in SQL Verschachtelte Anfragen in SQL.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Datenbankentwicklung IV-LK' - ianthe


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
datenbankentwicklung iv lk
DatenbankentwicklungIV-LK
  • Warum Datenbanken?
  • Anforderungsanalyse für Datenbanken
  • Ableitung von Tabellenstrukturen
  • Normalisierung
  • Relationenalgebra
  • Formalisierung von Tabellen in SQL
  • SQL- Anfragen
  • Gruppierungen in SQL
  • Verschachtelte Anfragen in SQL
9 verschachtelte anfragen in sql iv lk
9. Verschachtelte Anfragen in SQLIV-LK
  • Nutzung von Mengen- Operationen
  • Teilanfragen in der SELECT-Zeile
  • Teilanfragen in der WHERE-Bedingung
  • Teilanfragen in der HAVING-Zeile
  • Teilanfragen in der FROM-Zeile
  • Aufgaben
9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Als Beispiel dient weiterhin die Datenbank Zoo

Gehege

Tier

Art

9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk1
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Vereinigung: Es werden die Elemente von zwei Mengen zusammen gefasst.

Durchschnitt: Menge von Elementen, die in beiden Mengen vorkommen.

Differenz: Menge von Elementen, die in der ersten Menge, nicht aber in der zweiten Menge vorkommen.

In SQL ist es auch möglich, dass in einer Menge Werte doppelt vorkommen.

  • Es gibt jeden Mengenoperator in zwei Ausführungen. In der ersten Ausführung wird jede Zeile im Ergebnis nur einmal vorkommen, in der zweiten Ausführung werden die Anzahlen der vorhandenen Elemente berücksichtigt.
9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk2
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Vereinigung durch UNION

Frage: Man möchte alle Gattungen bestimmen, die in den Gehegen mit den Nummern 2 und 3 leben. => Abfrage 13

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr =2

UNION

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr = 3

Alternative Berechnung mit OR => Abfrage 14

SELECT DISTINCT Tier.Gattung

FROM Tier

WHERE Tier.Gnr = 2

OR Tier.Gnr =3

9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk3
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Randbedingungen:

  • Die resultierenden Tabellen der Teilanfragen müssen im Ergebnis die gleiche Spaltenanzahl haben.
  • Die Spalten müssen vom Typ zusammen passen.
  • Die Einträge müssen mit den Datentypen zusammen passen.

Mit UNION ALL werden doppelte Elemente nicht gelöscht. => Abfrage 15

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr =2

UNION ALL

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr = 3

9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk4
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Durchschnitt – INTERSECT (lässt sich nicht in Access verwenden)

Frage: Welche Gattungen kommen in beiden Gehegen vor?

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr=2

INTERSECT

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr =3

Mengendifferenz – EXCEPT (MINUS)

Frage: Welche Gattungen kommen im Gehege mit der Nummer 2, nicht aber im Gehege mit der Nummer 3 vor?

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr=2

MINUS

SELECT Tier.Gattung

FROM Tier

WHERE Tier.Gnr =3

9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk5
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Vermeidung von NULL Werten in Ausgaben:

In einer Ausgabe sollen die NULL Werte durch unbekannt ersetzt werden:Dazu werden zunächst die Personen ausgegeben, deren Status bekannt ist, und diese Ausgabe dann mit einem Ergebnis einer Anfrage vereinigt, in der nur Personen betrachtet werden, deren Status-Wert NULL ist.

SELECT *

FROM Personal

WHERE Personal.Status Is NOT NULL

UNION

SELECT Personal.Pnr, Personal.Name, 'unbekannt'

FROM Personal

WHERE Personal.Status IS NULL

9 verschachtelte anfragen in sql 2 teilanfragen in der select zeile iv lk
9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECT-ZeileIV-LK

Frage: Zusätzlich zu den Spalten der Tabelle Gehege soll noch die insgesamt von den Gehegen verbrauchte Fläche angezeigt werden. =>Abfrage 17

SELECT Gehege.*,

(SELECT SUM(Gehege.Flaeche)

FROM Gehege) AS Gesamtflaeche

FROM Gehege

Grundsätzlich kann man davon ausgehen, dass zuerst die innere Anfrage ausgewertet wird und im nächsten Schritt die Auswertung der umgebenden Anfrage geschieht.

9 verschachtelte anfragen in sql 2 teilanfragen in der select zeile iv lk1
9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECT-ZeileIV-LK

Statt einer einfachen Ausgabe in einer zusätzlichen Spalte, können die Ergebnisse einer Anfrage in einer SELECT-Zeile auch zur Berechnung genutzt werden. Dabei kann der berechnete Wert, wie andere Werte auch, mit anderen Attributwerten verknüpft werden.

Frage: Gib den Anteil jedes Geheges an der Gesamtfläche aus. =>Abfrage 18

SELECT Gehege.GName, Gehege.Flaeche*100/(SELECT SUM(Gehege.Flaeche)

FROM Gehege) As Anteil

FROM Gehege

9 verschachtelte anfragen in sql 3 teilanfragen in der where bedingung iv lk
9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der WHERE-BedingungIV-LK

Bisher war es nicht möglich, zu der Größe des größten Geheges auch den Namen auszugeben. Diese Möglichkeit wird durch folgende Anfrage geschaffen: => Abfrage 19

SELECT Gehege.GName, Gehege.Flaeche

FROM Gehege

WHERE Gehege.Flaeche = (SELECT MAX(Gehege.Flaeche) FROM Gehege)

Die Minimum und Maximum Berechnung ist auch für Texte möglich. Das Tier mit dem alphabetisch zuerst vorkommenden Namen, zusammen mit seiner Gattung, kann folgendermaßen gefunden werden. => Abfrage 20

SELECT Tier.Tname, Tier.Gattung

FROM Tier

WHERE Tier.Tname=(SELECT MIN (Tier.Tname) FROM Tier)

9 verschachtelte anfragen in sql 3 teilanfragen in der select zeile iv lk
9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der SELECT-ZeileIV-LK

Frage: Gib alle Gattungen aus, die mindestens so viel Fläche verbrauchen, wie der Durchschnitt aller Gattungen. => Abfrage 21

SELECT Art.Gattung

FROM Art

WHERE Art.MinFlaeche>=(SELECT AVG(Art.MinFlaeche) FROM Art)

Frage: Gib die Namen aller Tiere aus, deren Gattungen im Gehege Feld vorkommen. => Abfrage 22

SELECT Tier.TName

FROM Tier

WHERE Tier.Gattung IN

(SELECT Tier.Gattung

FROM Gehege, Tier

WHERE Gehege.Gnr = Tier.Gnr

AND Gehege.Gname ='Feld')

9 verschachtelte anfragen in sql 1 nutzung von mengen operatoren iv lk6
9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK

Frage: Gib die Namen der Gehege aus, in denen ein Hase vorkommt.

SELECT Gehege.Gname

FROM Gehege

WHERE 'Hase' IN (SELECT Tier.Gattung

FROM Tier

WHERE Gehege.Gnr=Tier.Gnr)

In der äußeren Anfrage wird das Gehege betrachtet. Für jedes dieser Gehege wird in der WHERE Bedingung untersucht, ob sich dort ein Hase befindet. Deshalb muss in der inneren Bedingung auf das Gehege Bezug genommen werden. Lokale Umbenennungen müssen auch in der inneren Anfrage berücksichtigt werden.

In diesem Fall wäre man auch ohne den IN-Operator ausgekommen.

SELECT DISTINCT Gehege.Gname

FROM Gehege, Tier

WHERE Gehege.Gnr=Tier.Gnr

AND Tier.Gattung = 'Hase'

Eine weitere Möglichkeit zur Auswahl in der WHERE-Bedingung bietet der EXISTS-Operator

9 verschachtelte anfragen in sql 4 teilanfragen in der having bedingung iv lk
9. Verschachtelte Anfragen in SQL – 4.Teilanfragen in der HAVING-BedingungIV-LK

Mit der HAVING-Bedingung werden Gruppen für das Ergebnis der Anfrage ausgewählt, mit der WHERE-Bedingung einzelne Zeilen. Die Bedingungen für die Teilanfragen in der WHERE-Bedingung können also auf die HAVING-Bedingung übertragen werden.

Frage: Es sollen die Gehege gesucht werden, deren Tiere zusammen auch im kleinsten Gehege leben könnten. Z.B. passen die beiden Bären im Gehege Wald nicht in das kleinste Gehege.

SELECT Gehege.Gname

FROM Gehege, Tier, Art

WHERE Gehege.Gnr = Tier.Gnr

AND Tier.Gattung=Art.Gattung

GROUP BY Gehege.Gname

HAVING SUM (Art.Minflaeche)<=(SELECT MIN(Gehege.Flaeche)

FROM Gehege)

In Teilanfragen dürfen entweder nur vollständig neue Berechnungen stehen, oder Aggregatsfunktionen für Berechnungen in den jeweiligen Gruppen und Attributen, die in der GROUP-BY Zeile genannt wurden.

9 verschachtelte anfragen in sql 5 teilanfragen in der from zeile iv lk
9. Verschachtelte Anfragen in SQL – 5.Teilanfragen in der FROM-Zeile IV-LK

Damit können komplexe Aufgabenstellungen in einfachere Teilanfragen zerlegt werden. Diese Teilanfragen liefern Tabellen als Ergebnisse und werden dann in der FROM-Zeile als benötigte Tabellen angegeben und zur Lösung der Gesamtaufgabe genutzt.

Frage: Wie viel Fläche wird in jedem Gehege von Hasen verbraucht?

  • Wie viel Hasen gibt es pro Gehege? => Abfrage 25

SELECT Gehege.Gname As Gehegename,

COUNT (*) As Hasenanzahl

FROM Gehege, Tier

WHERE Gehege.Gnr=Tier.Gnr

AND Tier.Gattung='Hase'

GROUP BY Gehege.Gname

2. Anzahl der Hasen wird mit der benötigten Fläche multipliziert. => Abfrage 26

SELECT Hasentabelle.Gehegename,

Hasentabelle.Hasenanzahl*Art.MinFlaeche As Hasenflaechenverbrauch

FROM Art,

(SELECT Gehege.Gname AS Gehegename, COUNT(*) AS Hasenanzahl

FROM Gehege,Tier

WHERE Gehege.Gnr=Tier.Gnr

AND Tier.Gattung ='Hase'

GROUP BY Gehege.Gname) As Hasentabelle

WHERE Art.Gattung ='Hase'

9 verschachtelte anfragen in sql 5 teilanfragen in der from zeile iv lk1
9. Verschachtelte Anfragen in SQL – 5.Teilanfragen in der FROM-Zeile IV-LK

Frage: Welcher Anteil hat jedes Tier an der insgesamt für Gehege zur Verfügung stehenden Fläche? => Abfrage 27

SELECT Gehege.Gname, Tier.Tname, Art.Minflaeche/Alle.Gesamtflaeche*100 AS Gehegeanteil

FROM Gehege, Tier, Art,

(SELECT SUM(Gehege.Flaeche) AS Gesamtflaeche

FROM Gehege) AS Alle

WHERE Gehege.Gnr=Tier.Gnr

AND Tier.Gattung=Art.Gattung

Frage: Wie viel Fläche ist in den einzelnen Gehegen noch frei? => Abfrage 28

SELECT Gehege.Gname,

Gehege.Flaeche - Belegung.Verbraucht AS Frei

FROM Gehege,

(SELECT Gehege.Gname, SUM(Art.Minflaeche) AS Verbraucht =Abfrage 29

FROM Gehege, Tier, Art

WHERE Gehege.Gnr=Tier.Gnr

AND Tier.Gattung = Art.Gattung

GROUP BY Gehege.Gname) AS Belegung

WHERE Gehege.Gname=Belegung.Gname

9 verschachtelte anfragen in sql 6 aufgaben iv lk
9. Verschachtelte Anfragen in SQL- 6. AufgabenIV-LK

Gegeben sind folgende Tabellen zur Beschreibung, welche Filme in welchen Kinos laufen. Im Gloria gibt es beispielsweise 3 Säle mit jeweils 20 Plätzen.

Film

Vorfuehrung

Kino

9 verschachtelte anfragen in sql iv lk1
9. Verschachtelte Anfragen in SQLIV-LK

Formuliere folgende SQL- Anfragen:

  • Gib die Namen aller Kinos aus, in denen der Film „Die Hand“ läuft.
  • Gib zu jedem Kino die Gesamtzahl aller zur Verfügung stehenden Plätze aus (Ausgabe: Kinoname, Gesamtplatzzahl)
  • Gib die Titel der Filme aus, die in mindestens zwei Kinos laufen.
  • Gib zu jedem Kino die Länge des längsten Films aus, der in diesem Kino läuft.
  • Gib zu jedem Filmtitel die maximale Anzahl von Zuschauern aus, die den Film gleichzeitig sehen können. Es ist davon auszugehen, dass alle Vorführungen gleichzeitig und nur einmal am Tag stattfinden.
  • Gib für jeden Film und jedes Kino an, ob dieser Film in diesem Kino läuft oder nicht (Ausgabe: Filmtitel, Kino, Anzahl). Dabei soll eine Anzahl>0 ausgegeben werden, wenn der Film in dem Kino läuft, und sonst soll Anzahl= 0 ausgegeben werden.