Datenbanken: Aufgabe Universität Diplomanden-Seminar Prof. Dr. Wolfgang Riggert/ Prof. Dr. Roland Schwesig Gliederung Vorstellung des Beispiels Umsetzung in MS-Access Formulierung der vorgegebenen SQL-Statements Datenmodellierung Ausschnitt der Realen Miniwelt Manuelle/intellektuelle Modellierung Konzeptuelles Schema (ER-Schema) Transformation Relationales Schema Netzwerk Schema Objektorientiertes Schema Datenbankentwurf Anforderungen der Nutzer Anforderungsanalyse Konzeptueller Entwurf ER Schema DBMSCharakteristika logische Datenbankstruktur Physischer Entwurf Hardware/BSCharakteristika Modellierung der Beispielanwendung: Universität Studenten Vorlesungen Professoren Reale Welt: Universität Konzeptuelle Modellierung/ER-Modell MatrNr Name PersNr Professoren Studenten hören Name lesen VorlNr Vorlesungen Titel Zugehöriges relationales Datenmodell Vorlesungen Studenten MatrNr 26120 25403 ... VorlNr hören Name Fichte MatrNr VorlNr Jonas 25403 5022 ... 26120 5001 ... ... Titel 5001 Grundzüge 5022 Glaube und Wissen ... Select Name From Studenten, hören, Vorlesung Where Studenten.MatrNr = hören.MatrNr and hören.VorlNr = Vorlesungen.VorlNr and Vorlesungen.Titel = `Grundzüge´; updateVorlesungen set Titel = `Grundzüge der Logik´ where VorlNr = 5001; ... Gesamtschema: Universität VorlNr MatrNr Name Studenten hören Vorlesungen SWS Titel Semester lesen Rang Professoren PersNr Name Raum Entitytypen des Beispiels Studenten: {[MatrNr:integer, Name: string, Semester: integer]} Vorlesungen: {[VorlNr:integer, Titel: string, SWS: integer]} Professoren: {[PersNr:integer, Name: string, Rang: string, Raum: integer]} Schlüssel der Relationen (Beziehungen) hören : {[MatrNr: integer, VorlNr: integer]} lesen : {[PersNr: integer, VorlNr: integer]} Beziehung hören - n:m hören Studenten MatrNr ... 26120 ... 27550 ... ... ... MatrNr Studenten Vorlesungen MatrNr 26120 27550 27550 28106 VorlNr 5001 5001 4052 5041 28106 5052 28106 5216 28106 5259 29120 5001 N VorlNr ... 5001 ... 4052 ... ... ... VorlNr M hören Vorlesungen Beziehung lesen – 1:n Professoren 1 lesen N Vorlesungen 1:N-Beziehung Anfangsentwurf Vorlesungen : {[VorlNr, Titel, SWS]} Professoren : {[PersNr, Name, Rang, Raum]} lesen: {[VorlNr, PersNr]} 1 Ausprägung von Professoren und Vorlesung Vorlesungen Professoren VorlNr Titel SWS PersNr Name 2125 Sokrates C4 226 5001 Grundzüge 4 2126 Russel C4 232 5041 Ethik 4 2127 Kopernikus C3 310 5043 Erkenntnistheorie 3 2133 Popper C3 52 5049 Mäeutik 2 2134 Augustinus C3 309 4052 Logik 4 2136 Curie C4 36 5052 Wissenschaftstheorie 3 2137 Kant C4 7 Professoren Rang Raum 1 lesen N Vorlesungen Vorsicht: So geht es NICHT Umsetzung von lesen !! Vorlesungen Professoren VorlNr Titel SWS 5001 Grundzüge 4 5049 5041 Ethik 4 226 4052 5043 Erkenntnistheorie 3 ... ... ... 5049 Mäeutik 2 Augustinus C3 309 5022 4052 Logik 4 Curie C4 36 ?? 5052 Wissenschaftstheorie 3 PersNr Name 2125 Sokrates C4 226 5041 2125 Sokrates C4 226 2125 Sokrates C4 ... ... 2134 2136 Professoren Rang Raum 1 liest lesen N Vorlesungen Vorsicht: So geht es NICHT: FolgenÎAnomalien Professoren PersNr Name 2125 Sokrates C4 2125 Sokrates 2125 Vorlesungen Rang Raum liest VorlNr Titel SWS 226 5041 5001 Grundzüge 4 C4 226 5049 5041 Ethik 4 Sokrates C4 226 4052 5043 Erkenntnistheorie 3 ... ... ... ... ... 5049 Mäeutik 2 2134 Augustinus C3 309 5022 4052 Logik 4 2136 Curie C4 36 ?? 5052 Wissenschaftstheorie 3 Update-Anomalie: Was passiert wenn Sokrates umzieht Lösch-Anomalie: Was passiert wenn „Ethik“ wegfällt Einfügeanomalie: Curie ist neu und liest noch keine Vorlesungen Gliederung Vorstellung des Beispiels Umsetzung in MS-Access Formulierung der vorgegebenen SQL-Statements Relationale UNI-DB Lösung: Entitäten Studenten Vorlesungen MatrNr Name Semester 24002 Xenokrates 18 25403 Jonas 12 5001 Grundzüge 4 2137 26120 Fichte 10 5041 Ethik 4 2125 26830 Aristoxenos 8 5043 Erkenntnistheorie 3 2126 27550 Schopenhauer 6 5049 Mäeutik 2 2125 4052 Logik 4 2125 28106 Carnap 3 5052 Wissenschaftstheorie 3 2126 29120 Theophrastos 2 5216 Bioethik 2 2126 29555 Feuerbach 2 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 VorlNr Titel SWS gelesen von Professoren PersNr Name Rang Raum 2125 Sokrates C4 226 2126 Russel C4 232 2127 Kopernikus C3 310 2133 Popper C3 52 2134 Augustinus C3 309 2136 Curie C4 36 2137 Kant C4 7 Relationale UNI-DB Lösung: Beziehung hören MatrNr VorlNr 26120 5001 27550 5001 27550 4052 28106 5041 28106 5052 28106 5216 28106 5259 29120 5001 29120 5041 29120 5049 29555 5022 25403 5022 Datentypen character (n), char (n) character varying (n), varchar (n) numeric (p,s), integer blob oder raw für sehr große binäre Daten clob für sehr große String-Attribute date für Datumsangaben Gliederung Vorstellung des Beispiels Umsetzung in MS-Access Formulierung der vorgegebenen SQL-Statements SQL-Anfragen Anwendung unterschiedlicher SQLStatements auf das Beispiel Universität Statements create und insert create table Professoren (PersNr integer not null, Name varchar (30) not null Rang character (2) ); insert into Studenten (MatrNr, Name) values (28121, `Archimedes‘); Statements delete und update Löschen von Tupeln delete Studenten where Semester > 13; Verändern von Tupeln update Studenten set Semester= Semester + 1; Anfrage: Selektion select PersNr, Name from Professoren where Rang= ´C4´; PersNr Name 2125 Sokrates 2126 Russel 2136 Curie 2137 Kant Anfrage: Sortierung PersNr Name Rang select PersNr, Name, Rang 2136 Curie C4 from Professoren 2137 Kant C4 2126 Russel C4 2125 Sokrates C4 2134 Augustinus C3 2127 Kopernikus C3 2133 Popper C3 order by Rang desc, Name asc; Tabellenkombination: Join Welcher Professor liest "Mäeutik"? select Name, Titel from Professoren, Vorlesungen where PersNr = gelesenVon and Titel = „Mäeutik“ ; Tabellenkombination: Join Realisierung Professoren PersNr Name Rang Raum 2125 2126 Sokrates Russel C4 C4 226 232 2137 Kant C4 VorlNr 5001 5041 Vorlesungen Titel SWS Grundzüge 4 Ethik 4 gelesen Von 2137 2125 5049 Mäeutik 2 2125 4630 Die 3 Kritiken 4 2137 7 Verknüpfung PersNr Name Rang Raum VorlNr Titel SWS gelesen Von 2125 Sokrates C4 226 5001 Grundzüge 4 2137 1225 Sokrates C4 226 5041 Ethik 4 2125 2125 Sokrates C4 226 5049 Mäeutik 2 2125 2126 Russel C4 232 5001 Grundzüge 4 2137 2126 Russel C4 232 5041 Ethik 4 2125 2137 Kant C4 7 4630 Die 3 Kritiken 4 2137 Auswahl PersNr Name Rang Raum VorlNr Titel SWS gelesen Von 2125 Sokrates C4 226 5049 Mäeutik 2 2125 Projektion Name Titel Sokrates Mäeutik Anfragen über mehrere Relationen Welche Studenten hören welche Vorlesungen? select Name, Titel from Studenten, hören, Vorlesungen where Studenten.MatrNr = hören.MatrNr and hören.VorlNr = Vorlesungen.VorlNr; Geschachtelte Anfrage 1 Subquery Welche Prüfungen sind besser als durchschnittlich verlaufen? select * from prüfen where Note < ( select avg (Note) from prüfen ); Geschachtelte Anfrage 2 Unteranfrage in der select-Klausel Für jedes Ergebnistupel wird die Unteranfrage ausgeführt select PersNr, Name, ( select sum (SWS) as Lehrbelastung from Vorlesungen where gelesenVon=PersNr ) from Professoren; Spezielle Sprachkonstrukte 1 select * from Studenten where Semester > = 1 and Semester < = 4; select * from Studenten where Semester between 1 and 4; select * from Studenten where Semester in (1,2,3,4); Spezielle Sprachkonstrukte 2 select * from Studenten where Name like `T%eophrastos‘; select distinct s.Name from Vorlesungen v, hören h, Studenten s where s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr and v.Titel like `%thik%‘; Vergleiche mit like Platzhalter "%" ; "_" "%" steht für beliebig viele (auch gar kein) Zeichen "_" steht für genau ein Zeichen select * from Studenten where Name like ´T%eophrastos´; select distinct Name from Vorlesungen v, hören h, Studenten s where s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr and v.Titel = ´%thik%´; Literatur Praktische SQL-Übungen unter: http://www.db.fmi.uni-passau.de/publications/books/DBMSeinf SQL interaktiv unter: http://www.sql200.net Ende