ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

35
ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost

Transcript of ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Page 1: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

ER-Datenmodell und Abfragen in SQL

Prof. Dr. Martin Thost

Page 2: 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.

Page 3: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Das Entity-Relationship-Modell

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

• Attribut / Attributwerte

• Beziehung (Relationship)

• Kardinalität einer Beziehung

Page 4: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Kardinalität der Beziehungen

• 1:1

• 1:N

• M:N

Page 5: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Krähenfußnotation

• 1 ist senkrechter Strich

• N ist „Krähenfuß“

Page 6: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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

Page 7: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Beziehungen

• Optional oder obligatorisch

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

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

Page 8: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Optionale Beziehungen

Kunde Bestellung

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

Page 9: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Obligatorische Beziehungen

Rechnung Rechnungsposition

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

Page 10: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Reflexive Beziehungen

Mitarbeiter

Ist verheiratet mit

Ist Vorgesetzter von

Page 11: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Parallele Beziehungen

Person VersicherungspoliceIst Versicherungsnehmer

Ist Versicherte Person

Page 12: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

SQL-Abfragesprache

• Structured Query Language

• Auswahl mit SELECT

• Funktionen COUNT, AVG, MIN,MAX, SUM

• Zusammenfassung mit GROUP BY

• Sortierung mit ORDER BY

Page 13: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

SQL-Datenmanipulation

• Einfügen mit INSERT

• Verändern mit UPDATE

• Löschen mit DELETE

Page 14: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

SQL-Datendefinition

• Erstellen von Tabellen mit CREATE TABLE

Page 15: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Lernprogramm zu SQL

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

Page 16: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Datenmodell: Mitarbeiter - Kunde

Page 17: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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 ;

Page 18: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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

and mitarbeiter.personalnummer = beratung.personalnummer

Page 19: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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 ;

Page 20: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Anfrage: Mitarbeiter - Kunde

SELECT DISTINCT mitarbeiter.name, kunde.name

FROM mitarbeiter, beratung, kunde

WHERE kunde.kundennummer =

beratung.kundennummer and

mitarbeiter.personalnummer = beratung.personalnummer;

Page 21: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Anfrage: Mitarbeiter - Kunde

SELECT personalnummer, sum(stundenanzahl)

FROM beratung

GROUP BY personalnummer;

Page 22: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Anfrage: Mitarbeiter - Kunde

SELECT Mitarbeiter.Name, Mitarbeiter.Telefon

FROM Mitarbeiter

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

Page 23: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Anfrage: Mitarbeiter - Kunde

SELECT Name, Stundensatz

FROM Mitarbeiter

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

Page 24: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;

Page 25: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;

Page 26: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;

Page 27: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;

Page 28: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Datenmodell: Projektverwaltung

Page 29: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Anfrage: Projektverwaltung

SELECT Name

FROM Mitarbeiter, Abteilung

WHERE bezeichnung="Kredite"

and abteilung.abteilungsnummer = mitarbeiter.abteilung;

Page 30: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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]));

Page 31: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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")));

Page 32: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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]));

Page 33: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

Gruppenbildung: Group byselect abteilungsnummer, sum(gehalt)

from mitarbeiter

group by abteilungsnummer

Page 34: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;

Page 35: ER-Datenmodell und Abfragen in SQL Prof. Dr. Martin Thost.

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;