Upload
tiwaz-keffeler
View
117
Download
5
Embed Size (px)
Citation preview
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;