ER-Datenmodell und Abfragen in SQL

Prof. Dr. Martin Thost

Hinweis zum Ausdrucken

• Falls Sie diese Folien ausdrucken möchten, wird dringend empfohlen, Handzettel mit 6 Folien je Druckseite anstatt für jede Folie eine eigene DIN- A4-Seite auszudrucken. Dies erreichen Sie, indem Sie im Menü Datei/Drucken ... in das untere Listenfeld "Drucken:", wo steht "Folien" die Zeile "Handzettel (6 Folien je Seite)" auswählen.

Das Entity-Relationship-Modell

• Entität und Entitätstyp (auch Objekt und Objekttyp)

• Attribut / Attributwerte

• Beziehung (Relationship)

• Kardinalität einer Beziehung

Kardinalität der Beziehungen

• 1:1

• 1:N

• M:N

Krähenfußnotation

• 1 ist senkrechter Strich

• N ist „Krähenfuß“

Auflösung einer M:N-Beziehung in zwei 1:N-Beziehungen

• Eine M:N-Beziehung kann in einer relationalen Datenbank nicht direkt realisiert werden.

• Durch die Zwischenschaltung einer zusätzlichen Tabelle wird sie aufgelöst in zwei 1:N-Beziehungen.

• (Tabelle) Studentin besucht (Tabelle) Vorlesung wird ergänzt durch (Tabelle) Vorlesungsbesuch

Beziehungen

• Optional oder obligatorisch

• Parallele Beziehungen (z. B. Person – Versicherungspolice)

• Reflexive Beziehungen (z. B. zur Darstellung der Mitarbeiterhierarchie)

Optionale Beziehungen

Kunde Bestellung

Kunden ohne Bestellung sind möglich. Beziehung ist optional. Beziehungen ohne „Null“ gelten dann als obligatorisch!

Obligatorische Beziehungen

Rechnung Rechnungsposition

Eine Rechnung ohne Rechnungsposition soll nicht vorkommen dürfen. (Vgl. „Komposition“ in der UML)

Reflexive Beziehungen

Mitarbeiter

Ist verheiratet mit

Ist Vorgesetzter von

Parallele Beziehungen

Person VersicherungspoliceIst Versicherungsnehmer

Ist Versicherte Person

SQL-Abfragesprache

• Structured Query Language

• Auswahl mit SELECT

• Funktionen COUNT, AVG, MIN,MAX, SUM

• Zusammenfassung mit GROUP BY

• Sortierung mit ORDER BY

SQL-Datenmanipulation

• Einfügen mit INSERT

• Verändern mit UPDATE

• Löschen mit DELETE

SQL-Datendefinition

• Erstellen von Tabellen mit CREATE TABLE

Lernprogramm zu SQL

• Im Ordner SQL-Lernprogramm können Sie sich mit dem gesamten Umfang dieser Sprache vertraut machen.

Datenmodell: Mitarbeiter - Kunde

Anfrage: Mitarbeiter - Kunde

SELECT mitarbeiter.name, telefon

FROM mitarbeiter, beratung, kunde

WHERE kunde.name="Max"

and kunde.kundennummer = beratung.kundennummer

and mitarbeiter.personalnummer = beratung.personalnummer ;

SELECT kundennummer,datum, stundenanzahl from mitarbeiter, beratung where name = "Müller"

and mitarbeiter.personalnummer = beratung.personalnummer

Anfrage: Mitarbeiter - Kunde

SELECT Kunde.Name, Sum(Beratung.Stundenanzahl) AS [Summe von Stundenanzahl]

FROM Beratung, Kunde

WHERE Beratung.Kundennummer = Kunde.Kundennummer

GROUP BY Kunde.Name ;

Anfrage: Mitarbeiter - Kunde

SELECT DISTINCT mitarbeiter.name, kunde.name

FROM mitarbeiter, beratung, kunde

WHERE kunde.kundennummer =

beratung.kundennummer and

mitarbeiter.personalnummer = beratung.personalnummer;

Anfrage: Mitarbeiter - Kunde

SELECT personalnummer, sum(stundenanzahl)

FROM beratung

GROUP BY personalnummer;

Anfrage: Mitarbeiter - Kunde

SELECT Mitarbeiter.Name, Mitarbeiter.Telefon

FROM Mitarbeiter

WHERE Mitarbeiter.Name=[Geben Sie bitte den Namen des Mitarbeiters ein!];

Anfrage: Mitarbeiter - Kunde

SELECT Name, Stundensatz

FROM Mitarbeiter

WHERE Stundensatz = (SELECT MAX(Stundensatz) FROM Mitarbeiter)

Anfrage: Mitarbeiter - Kunde

SELECT kunde.Name, Sum(stundenanzahl * stundensatz) AS Rechnung_pro_Kunde

FROM beratung, kunde, mitarbeiter

WHERE (((beratung.Kundennummer) = [kunde].[kundennummer]) AND (([beratung].[personalnummer]) = [mitarbeiter].[personalnummer]))

GROUP BY kunde.Name

ORDER BY kunde.name;

Anfrage: Mitarbeiter - Kunde

SELECT Kunde.Kundennummer, Sum(Stundensatz*Stundenanzahl) AS Kosten

FROM Kunde, Beratung, Mitarbeiter

WHERE (((Kunde.Kundennummer)=[Beratung].[Kundennummer]) AND ((Beratung.Personalnummer)=[Mitarbeiter].[Personalnummer]))

GROUP BY Kunde.Kundennummer;

Anfrage: Mitarbeiter - Kunde

SELECT DISTINCT mitarbeiter.name, sum(stundenanzahl*stundensatz) AS [Summe der Beratungskosten]

FROM beratung, mitarbeiter

WHERE mitarbeiter.personalnummer = beratung.personalnummer

GROUP BY mitarbeiter.name

ORDER BY mitarbeiter.name;

Anfrage: Mitarbeiter - KundeSELECT Mitarbeiter.Name, Kunde.Name

FROM Kunde INNER JOIN (Mitarbeiter INNER JOIN Beratung ON (Mitarbeiter.Personalnummer = Beratung.Personalnummer) AND (Mitarbeiter.Personalnummer = Beratung.Personalnummer) AND (Mitarbeiter.Personalnummer = Beratung.Personalnummer)) ON Kunde.Kundennummer = Beratung.Kundennummer;

Datenmodell: Projektverwaltung

Anfrage: Projektverwaltung

SELECT Name

FROM Mitarbeiter, Abteilung

WHERE bezeichnung="Kredite"

and abteilung.abteilungsnummer = mitarbeiter.abteilung;

Anfrage: ProjektverwaltungSELECT DISTINCT Abteilungsnummer,

Projekt.Bezeichnung

FROM Projekt, Abteilung, Mitarbeiter, Projektmitarbeit

WHERE (((Abteilung.Abteilungsnummer) =[mitarbeiter].[abteilung]) AND ((mitarbeiter.Mitarbeiternummer) =[projektmitarbeit].[mitarbeiternummer]) AND ((projektmitarbeit.Projektnummer) = [projekt].[projektnummer]));

Anfrage: ProjektverwaltungSELECT Mitarbeiter.Name,

Mitarbeiter.Mitarbeiternummer

FROM Mitarbeiter, Projekt, Projektmitarbeit

WHERE (((Mitarbeiter.Mitarbeiternummer)=[Projektmitarbeit].[Mitarbeiternummer]) AND ((Projektmitarbeit.Projektnummer)=[Projekt].[Projektnummer]) AND ((Projekt.Projektleiter)=(Select Mitarbeiternummer from Mitarbeiter where name = "Müller")));

Anfrage: ProjektverwaltungSELECT DISTINCT abteilung.bezeichnung,

projekt.bezeichnung

FROM abteilung, mitarbeiter, projektmitarbeit, projekt

WHERE (((Abteilung.Abteilungsnummer) =[mitarbeiter].[abteilung]) AND ((mitarbeiter.Mitarbeiternummer)=[projektmitarbeit].[mitarbeiternummer]) AND ((projektmitarbeit.Projektnummer)=[projekt].[projektnummer]));

Gruppenbildung: Group byselect abteilungsnummer, sum(gehalt)

from mitarbeiter

group by abteilungsnummer

Anfrage: ProjektverwaltungSELECT projekt.Bezeichnung,

Sum(Stundenanzahl*stundensatz) AS Projektkosten FROM Abteilung, mitarbeiter, projektmitarbeit, projekt

WHERE (((Abteilung.Abteilungsnummer) =[mitarbeiter].[abteilungsnummer]) AND ((mitarbeiter.Mitarbeiternummer)=[projektmitarbeit].[mitarbeiternummer]) AND ((projektmitarbeit.Projektnummer) =[projekt].[projektnummer]))

GROUP BY projekt.Bezeichnung;

Anfrage: ProjektverwaltungSELECT Mitarbeiter.Name, Projekt.Bezeichnung,

Sum(Projektmitarbeit.Stundenanzahl) AS [Summe von Stundenanzahl]

FROM Mitarbeiter, Projekt, Projektmitarbeit

WHERE (((Mitarbeiter.Mitarbeiternummer) =[Projektmitarbeit].[Mitarbeiternummer]) AND ((Projektmitarbeit.Projektnummer) =[Projekt].[Projektnummer]))

GROUP BY Mitarbeiter.Name, Projekt.Bezeichnung;