Skript von Johannes Oechsle
Werbung
Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Kontakt: Till Hänisch e-Mail: [email protected] Informationssysteme: Datenbanksysteme Datenbanken Anwendung von Datenbanken: Geldautomat Flugreservierung Bibliothekenkatalog Supermarktkasse News-Ticker Web-Suchmaschine Historie Ca. 1890: Ca. 1950 Ca. 1970 Ca. 1975 Ca. 1985 Heute Lochkarten (batch) Computer (batch) Transaktionssysteme erste RDBMS (= relationale Datenbank Management Systeme) erste OODBMS (= objekt orientiertes DBMS) ORDBMS (objekt relationales DBMS) Ursprung Notwendigkeit zur effizienten Verarbeitung von Massendaten Kommunikation (Telefonrechnungen) Banken (Konten, Börsen) Reise (Fluglinie, Hotelketten) Produktion (Aufträge, Lagerhaltung) Verwaltung (Personal, Steuer) Motivation Schneller o Viele Datensätze Verlässlicher o Wichtige Daten (Finanzen) Zugriff auf Informationen o Komplexe Strukturen Durch viele Anwender o Wenige bis viele Tausend Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Die Anfänge Früher wurden Datenbanken selber programmiert in Cobol oder in C. Anlegen der Datenstruktur, Schreiben von Funktionen; Umständlich bei Änderungen der Datenstruktur Für bessere Performance auch umständlich zu Ändern Zugriff über mehrere Benutzer über Sperren der Datenbank Konsistenz Was passiert, wenn Anwendung abstürzt? Bei Programmierfehlern? Log o Änderungen werden protokolliert o Wiederherstellung möglich Datenbank Zusammenhängende Datensätze, die eine bestimmte Bedeutung haben Repräsentation einen bestimmten Aspekt der realen Welt („miniworld“) Enthält Daten für einen bestimmten Zweck für bestimmte Anwender und Applikationen Datenbanksystem Daten Hardware Software o DBMS Benutzer o Applikationsentwickler o Endanwender o DBA (= Datenbank Administratoren) Relationale Datenbank Für den Anwender stellen sich die Daten in Form von Tabellen dar Die möglichen Operationen erzeugen neue Tabellen aus bestehenden, z.B. o Untermenge der Zeilen einer Tabelle o Untermenge der Spalten einer Tabelle Möglich wären aber auch hierarchisch organisierte Datenbanken (z.B. XML-Datenbanken) Operationen Tabellen hinzufügen Datensätze hinzufügen (insert) Datensätze holen (select) Datensätze ändern (update) Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Datensätze löschen (delete) Tabellen löschen Eigenschaften von DBMS Persistenz Datenunabhängigkeit Unterschiedliche Sichten Transaktionen (ACID) (= Atomicity, Consistency, Isolation, Durability) Schnell teuer Datenunabhängigkeit Applikationen brauchen nicht geändert werden, wenn o Datenstrukturen geändert werden (z.B. Feld zu Tabelle dazukommt) o Speicherstrukturen geändert werden (z.B. Daten auf meheren Platten verteilt werden) o Zugriffspfade geändert werden (z.B. Index erzeugt oder gelöscht wird) 3-Schichten-Architektur [einfügen vom Skript] Atomicity Die Änderungen an den Daten durch eine Transaktion finden entweder vollständig oder gar nicht statt. Beispiel: Geld wird von Konto 1 abgehoben und Konto 2 gutgeschrieben. Consistency Änderungen finden so statt, dass das System konsistent bleibt Beispiel: Der von Konto 1 abgehobene Betrag ist der gleiche wie der, der Konto 2 gutgeschrieben wird. Isolation Für jede Transaktion Ti sieht das System so aus, als ob alle Zj mit i <> j entweder vor oder nach Ti ablaufen. Beilspiel: Wenn jemand anderes gleichzeitig eine Buchung auf Konto 1 oder Konto 2 macht, wird die Überweisung trotzdem korrekt ausgeführt. Transaktionen sollen such nicht stören Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Durability Wenn eine Transaktion abgeschlosPerformance sen ist, bleiben die Daten auch bei Abstürzen,... erhalten Beispiel: Auch wenn der Geldautomat nach der Auszahlung abstürzt taucht die Auszahlung im Konto (Auszug) auf. Performance Forschungsgegenstand siet mehr als 20 Jahren schnell Typischer Zugriff auf einzelne Datensätze < 100 ms unabhängig von Anzahl Typisch bei mehreren Tabellen wird das System langsamer Aber: Overhead durch Transaktionen (ACID Properties brauchen Zeit) o www.tpc.org Relationale Datenbanken ORACLE DB/2 Sybase ASE Microsoft SQL Server Informix MS Access mySQL („Spielzeug”, aber ohne Transaktionen) Nachteile Teuer Resourcen o Hardware, Software, Personal Overhead o Transaktionen, Abstraktion Tabellen, nicht Objekte o Texte, Bilder, Videos,... Alternativen „alte“ Technologien o Dateien, Hierarchische DB OODBMS Spezielle Systeme o z.B. Multimedia DBS XML Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Aufbau der Vorlesung SQL in 21 Minuten Konzeptionelle Datenmodellierung Relationenalgebra, Entwurfstheorie Logische Datenmodellierung SQL (Basics, Advanced) Pysikalische Datenmodellierung Projekt (Modellierung, Umsetzung) Literatur C.J. Date: An introduction to database systems, Addison Wesley, 1995 Elmarsi / Navathe: Fundamentals of database Systems Addison Wesley, 2000 Helmut Balzert: Lehrbuch der Softwaretechnik Spektrum Akademischer Verlag 1996 (2 Bände) P. Greenspun, Database backed web sites, Ziff Davis Press, 1997 SQL in 21 Minuten Tabellen In relationalen DBs werden Daten in Tabellen organisiert. Jede Spalte enthält eine bestimmte Art von Information, jede Zeile einen Datensatz Jede Spalte hat einen Datentyp (Unterscheidung vom „Papier“) o CHAR, VARCHAR, NUMBER, DATE,... Tabellen anlegen CREATE TABLE DEPT ( DEPTNO NUMBER (2), DNAME VARCHAR2(14), LOC VARCHAR2(13)); Tabelle löschen DROP TABLE DEBT; Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Daten einfügen INSERT INTO DEPT ( DEPTNO, DNAME, LOC), VALUES(1’Accounting’,’New York’); Daten Abrufen SELECT DEPTNO, DNAME FROM DEPT; SELECT * FROM DEPT; SELECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO=2; Daten löschen DELETE FROM DEPT WHERE DEPTNO=2; Daten ändern UPDATE DEPT SET DEPTNO=20 WHERE EMPNO=7934; Tabellen verknüpfen SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO; Joins Joins verknüpfen zwei (oder mehr) Tabellen Wie? Kartesisches Produkt + Selektion, d.h. jede Zeile aus Tabelle A wird mit jeder Zeile aus Tabelle B kombiniert, dann werden die herausgesucht, die die Bedingung erfüllen. Equi-join / Non-equi-join Equi-join: Bedingung „=“ Non-equi-join: Bedingung z.B. >, <, <= Self-join Join einer Tabelle mit der gleichen Z.B. Alle Angestellten mit einem höheren Gehalt als ‚Jones’ Null Was trägt man ein, wenn Attribut keinen Wert hat? SQL: NULL Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Vorgehen bei der Entwicklung von Datenbankapplikationen Analog zur „klassischen“ Softwareentwicklung: Analyse Entwurf Umsetzung Aber andere Schwerpunkte: Wie werden die speziellen Eigenschaften von Datenbankapplikationen berücksichtigt? „normale“ Softwareentwicklung ist stärker funktionsorientiert, Datenbanken sind eher datenorientiert. Deshalb: Trennung in den Applikationsteil und den eigentlichen Datenbankteil Applikation Datenbank GUI, Schnittstellen SE Daten, Transaktionen Datenbankdesign Requirements Daten o Konzeptionelles Datenmodell entwickeln data requirements werden verfeinert (Entities, Relationships, Constrants,...) Systemunabhängige Darstellung der zu bearbeitenden Daten als ER-Diagramm (Entity-Relationsship-Diagramm) o Logisches Datenmodell entwickeln Abbildung des konzeptionellen Modells auf ein konkretes Datenbankmodell, hier auf eine relationale Datenbank relational o Physikalisches Datenmodell entwickeln Abbildung des logischen Datenmodells auf ein Datenbankmanagementsystem unter Berücksichtigung aller technischer Details ORACLE Konzeptionelles Datenmodell (oder semantisches Datenmodell) abstrakt Vom System unabhängige Darstellung der Datenmodells Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester o Abstraktion über (alles?) möglichen Zielsysteme, d.h. was ist bei allen möglichen Datenbanksystemen gleich? o Systemtheorie: Bei allen Datenbanksystemen werden Informationen über Objekte (= Ding) gespeichert und diese werden durch Attribute charakterisiert. o Entity (= Dinge) o Relationsship: Zwischen Objekten bestehen Beziehungen Darstellung als Entitiy-Relationsship-Diagramm (ER-Diagramm) (geht zurück auf Chen 1976) Entities und Attribute Entitiy: Ding, aus der realen Welt o Real (Person, Auto,...) o Formale (Firma, Vorlesung,...) Attribut: Eingenschaft, die ein Entity näher beschreibt, z.B. Entity Person, Attribute „Name“, „Vorname“ Für jeden einzelnen Entitiy hat ein Attribut einen Wert Attributarten atomic / composite Attribute können aus mehreren Teilen bestehen, z.B. „Adresse“ könnte in „PLZ“, „Straße“, „Ort“ zerlegt werden zusammengesetztes Attribut (composite) Attribute die nicht zerlegt werden können heißen atomar (atomic) oder „einfach“ (simple) Auch mehrstufige Hierarchie: Adresse PLZ Straße Name Ort Hausnummer Der Wert eines zusammengesetzten Attributs ist die Verknüpfung der zugrunde liegenden atomaren Attribute Attribut kann sein: Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester o Single valued o Multi valued Normalerweise haben Attribute einen Wert, unter Umständen ahebn Attribute aber mehrere Werte, z.B. Telefonnummern mehrwertige / multivalued Attribute o Stored / derived Wenn der wert eines Attributs aus einem anderen abgeleitet werden kann, heißt dieses derived, z.B. Geburtsdatum / Alter, die gilt auch für Attribute von verschiedenen Entities: Attribut Mitarbeiterzahl bei Abteilung o null wenn ein Attribut keinen Wert hat, dann erhält es den Wert null. o complex composite und multivalued Attribute können geschachtelt werden. {Adresse({Telefonnummer},(...))} Entity types, Entity sets, Keys Ein Entity type definiert eine Gruppe von Entities, die dieselben Attribute haben. Ein Entitiy wird definiert durch die Menge der Attribute und einen Namen. Alle Entities, die zu einem Entity type gehören heißen entity set. Analogie zur OOP (objektorientierten Programmierung): Entity type = Klasse Entity = Objekt Entity set = ? (gibt’s nicht!) Schlüsselattribute (eines Entity types) Wenn ein Entity type ein Attribut hat, das für alle möglichen Entities unterschiedlich ist, heißt dieses Schlüsselattribut (key attribute). Durch diesen kann jeder Entity identifiziert werden. Weak Entities sind Entities ohne Schlüsselattribut Beispiel: Entity Firma Abteilung Attribute Name, Chef, Adresse Nummer, Name, Leiter {Name, Beförderung}, {Standort}, {Projekt} Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Nummer, Name, Adresse, Gehalt, Geschlecht, Geburtsdatum, Abteilung, {Projekt (Name, Stunden/Woche)}, Chef, {Familienmitglied (Name, Geschlecht, Geburtsdatum, Beziehung)} Mitarbeiter Name, Standort, Nummer, Abteilung Projekt Name, Geschlecht, Art Familienangehörige Relationsships Man findet Attribute, die sich auf andere Entities beziehen (z.B. Abteilung (Leiter), Mitarbeiter (Abteilung). Diese sollen durch Relationsships dargestellt werden (und eben nicht durch Attribute) Relationship types Ein Relationship type R zwischen n Entity types E1 ... En definiert eine Menge von Assoziationen (Relationship set) zwischen Entities dieser Typen. R ist ein Menge von Relationship instances ri, wobei jedes ri n Entities (E1 ... En) miteinander verknüpft. R E1 E 2 ... E n Mitarbeiter Arbeitet für Abteilung E1 A1 E2 A2 E3 A3 E4 A4 E5 Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Die Ornung (degree) einer Relationship ist die Zahl der jeweils beteiligten Entities (Entity types), bei arbeitet für also = 2 2= binär 3 = tertiär Lieferant liefert Projekt Artikel Normalerweise: binär, tertiär selten, höher kaum Rollen Relationships als (multivalued) Attribute der entsprechenden Entities mit Wertebereich. Jeder Entity type, der an einer Relationship beteiligt ist, hat eine bestimmte Rolle z.B. arbeitet für Mitarbeiter „beschäftigt bei“ Abteilung Abteilung „hat Angestellten“ Mitarbeiter Wenn eine Relationship verschiedene Entity types miteinander verknüpft sind Rollennamen redundant, sonst notwendig zur Unterscheidung. Relationships (Verknüpfungen) haben keine Richtung. Bei rekursiven Relationships nötig. Constraints bei Relationship types Typischerweise sind die Möglichkeiten von Entities zur Teilnahme an Relationships eingeschränkt Kardinalität Durch die Kardinalität wird (bei binären Relationships) definiert, wie viele Entities jeweils teilnehmen können z. B. arbeitet für Abteilung : Mitarbeiter 1 : n d.h. 1 Abteilung beschäftigt einen oder mehrere Mitarbeiter. Jeder Mitarbeiter arbeitet für eine Abteilung. Mögliche Kardinalitäten: 1 : 1, 1 : n, m : n Ist jeder existierende Entity ei des Typs E an einer Relationship beteiligt ist, heißt „total participation“. Man nennt dies auch „existence dependency“, d.h. ei kann nur existieren, wenn Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester er an R beteiligt ist. Ist dies nicht der Fall, heißt dies „partial participation“ Abteilung zu „arbeitet für“: partial Mitarbeiter zu „arbeitet für“: total Relationships können (wie Entities) Attribute haben, z.B. Attribut “Stunden“ bei R Mitarbeiter – Projekt Relations zum Beispielunternehmen „arbeitet für“ (Abt., MA); 1:n, partial, total “chef von“ (MA, MA); 1:n, partial, ? “arbeitet in“ (MA, Projekt); m:n, partial?, total? “Niederlassung“ (Abt, Standort); m:n, total, total? “führt durch“ (Abt., Projekt); m?:n, partial?, total? “wo“ (Projekt Standort); n:1, total, partial? “verwandt mit“ (MA, FamAng); 1:n, partial, total? ERD (Entity Relationship Diagramm) M ERD werden Entity types und Relationship types dargestellt (da die allgemein gültigen Beziehungen von Interesse sind) Name Mitarbeiter Telefon Pers. Nr. Geb. Dat. Adresse PLZ Straße Konventionen: Entity types GROSS, Attribute mit großen Anfangsbuchstaben, Rollen klein Entity types im Singular, normalerweise Nomen, Relationships sind normalerweise Verben. Relatioshipname so wählen, dass gelesen von links nach recht bzw. oben nach unten Sinn ergibt. Alternative Notationen: (min, max) statt Kardinalität und ... participation Wenn ein Entity type E an einer Relationship R teilnimmt, heißt (min, max), dass mindestens „min“ und höchstens „max“ Entities beteiligt sind. partial participation (0,) total participation (1,) Wert für min, max typ. (0,1,*) Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Erweiterungen Spezialisierung, Generalisierung UML Zusammenfassung ER-Diagramme zur konzeptionellen Modellierung Darstellung der semantischen Struktur Abstraktion Übersichtlich Fußballstatistik Sie sollen eine Datenbank zur Analyse von Spielen der Bundesliga erstellen. Jedes Spiel wird von zwei Mannschaften bestritten und von einem Schiedsrichter geleitet Jede Mannschaft hat eine bestimmte Zahl an Spielern, die nicht notwendigerweise an jedem Spiel teilnehmen Ziel ist es zu erfassen, welche Spieler an welchem Spiel in welcher Position teilnahmen, und wie viele Tore, Ecken, Freistöße,... jeder Spieler jeweils in welcher Minute erzielte. Das Ergebnis jedes Spiels soll ebenfalls festgehalten werden. Relationenalgebra Definition Relation: Menge von Tupeln ti aus Attributen mit Namen Ai mit Werten aus Domänen D1,..., Dn. Darstellung: Name, Liste / Menge von Attributen: R(A1,...,An) z.B.: EMP(empno, ename,…) sch(R) = (A1,…, An) = Schema von R R D1 x D2 x ... x Dn Val (R) = {t1,..., tn | ti e R} Relation = = Tabelle soll einen sogenannten Primärschlüssel (PK (für Primary key) haben, der die Zeile / den Tupel eindeutig identifiziert. Dazu wird einer der möglichen Schlüssel ausgewählt PK <> NULL Wenn der Primärschlüssel einer Tabelle in einer anderen als Attribut vorkommt, heißt dieses Fremdschlüssel (FK). Der Wert dieses Attributs muss NULL sein, oder als PK in der ersten Tabelle vorkommen (foreign key constraint) Candidate key C von Relation R 1. C sch (R) 2. Für alle t1, t2 Element von val (R), t1 <> t2 t1 (c) <> t2 (c) 3. C ist minimal, d.h. 2. gilt nicht für C’ echte Teilmenge von C Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Operationen Selektion (z.B. ename=“Smith“ (EMP)) R’ := (R), sch (R’) = = sch(R) Val (R’) := {t Element von val (R) | P (t)} Projektion (z.B. ename (EMP)) R’ := L (R), sch(R’) := sch(R) Schnittmenge L Vereinigung R’ := R vereinigt mit S, sch (R) = sch (S) = sch (r’) Val (R’) := val (R) vereinigt mit val (S) Differenz R’ := R-S, sch (R) = sch (S) = sch (R’) Val (R’) := {t | t Element val (R) und t nicht Element val (S)} Produkt R’ := R x S, sch(r’) := sch (R) disjoint union sch (S) Val (R’) := {t | t (sch (R)) Element von val (R) und t (sch (S)) Element von (S)} Abgeschlossen, vollständig z.B. Name der Abteilung von EMP „Smith“ Mit diesen 5 Operationen kann man alle möglichen weiteren Operationen herleiten. Logisches Datenmodell Konzeptionelles Datenmodell fertig, Übergang zu Relationen (Zunächst ohne Berücksichtigung konkreter Produkte (z.B. Oracle,...) Relationenalgebra Transformationen, Regeln, einfach ER: Entities, Relationships Relationenalgebra: Nur noch Relationen Regeln zur Transformation: 1. „normaler“ Entity Relation R mit allen einfachen Attributen von E Falls E zusammengesetzte Attribute hat atomare Komponenten in R übernehmen. Eins der Schlüsselattribute von E wird Primärschlüssel von R. Falls dies zusammengesetzt ist, werden die Komponenten zusammen Primärschlüssel 2. Weak Entity W Relatioen R (wie oben) zusätzlich den Primärschlüssel des „übergeordneten“ Entities als Fremdschlüssel. Primärschlüssel wird der Fremdschlüssel und (wenn vorhanden) Teilschlüssel von R. Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester 3. binäre 1:1 Relationship R wähle eine der beteiligten Relationen S und T aus (z.B. S) und fügen als Fremdschlüssel den Primärschlüssel von T dazu. Anmerkung: Wenn möglich, sollte S in total participation sein ( dann keine NULL im Fremdschlüssel) 4. binäre 1:N Relationship Relation S, die der „N“-Seite der Relationship entspricht, enthält des Primärschlüssel von T („1“-Seite) als Fremdschlüssel. Falls R Attribute hat zu S dazu 5. binäre M:N Relationship neue Relations S S enthält die Primärschlüssel der beiden beteiligten Relationen als Fremdschlüssel. Falls R Attribute hat zu S dazu 6. mehrwertiges Attribut A neue Relation R mit Attribut A’, das die einzelnen Werte von A enthält, zusätzlich der Primärschlüssel der Relation die A enthält als Fremdschlüssel (k), z.B. Telefonnummern (mehrere) Primärschlüssel ist (A’,K) 7. Relationships höherer Ordnung R neue Relation S S enthält die Primärschlüssel der beteiligten Relationen als Fremdschlüssel. Wenn R Attribute hat zu S dazu Spiel (Schiedsrichtung, Ergebnis, Nummer) Mannschaft (Name) Spieler (Name, Mannschaft) Ereignis (Art, Wann, Spielnummer, Spielername) SpieltMit (Spielername, Spielnummer, Position) Bestreitet (Mannschaftsname, Spielnummer) Beispiel: Sachbearbeiter Dozent Name Pers. Nr. Name Telefon Dozent Name Hänisch Schmidt Gerber Pers. Nr. 123 567 890 Sachbe. Name Müller Maier Maier Sachbe. Tel. 4711 4712 4713 Vofälle: „Hänisch“ hält keine Vorlesungen mehr Eintrag wird gelöscht Welche Telefonnummer hat Müller? Löschanomalie Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Neuer Sachbearbeiter „Kunze“ kommt dazu, betreut aber noch keine Dozenten Wo speichere ich Name und Telefon Einfügeanomalie Abhilfe: Tabelle Sachbearbeiter (Name, Telefonnummer) dazu, Dozent bleibt wie gehabt. SB Müllers Telefonnummer ändert sich 2 Tabellen (an 2 Stellen) müssen geändert werden Änderungsanomalie Problem: Welche Relationen mit welchen Attributen sollen verändert werden, um solche Probleme zu umgehen? Normalisierung, Entwurfstheorie Ursprünglich von Codd (1971) 3 Normalformen, später (BCNF, 4., 5., ...) Jede Stufe stellt eine „Verschärfung“ dar. Normalisierungsschritte und das Vorgehen sind Regeln für den Datenbankentwurf Alle Relationen 1. Normalform 2. Normalform 3. Normalform BCNF Normalisierung führt in der Regel zur Aufspaltung von Relationen. Weitere Theorie (verlustfreie Zerlegung, abhängigkeitsbewahrend,...) z.B. [Elmasi], [Date] Normalformen sind Regeln für einen „guten“ Datenbank-Entwurf, kein „Kochbuch“ Strenge Normalisierung führt in der Regel zu einer großen Zahl von Relationen Normalisierung erfasst Wissen über die Bedeutung der Attribute Normalisierung als „Selbstzweck“ o Nicht erforderlich (überlegen) o Nicht immer praktikabel (Zahl der Relationen, Performance) Vorgehen: Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Vollständige Normalisierung (in 3. Normalform), danach gegebenenfalls „Denormalisierung“) Funktionale Abhängigkeit (functional dependency FD) A B := für alle Val (R), für alle t1 , t2 ist Element von Val (R), t1 (A) = t2 (A) t1(B) = t2 (B) z.B. EMP (empno, ename, deptno, dname, officeno, phone#, officesize) 1 A 42 cs 2 B 42 cs 3 C 42 cs 4 D 42 cs FD’s: Empno ename (aus der Nummer kann man auf den Name schließen) Empno deptno (aus der Nummer kann man auf die Abteilung schließen) Deptno dname Deptno, officeno phone#, officesize 1. Normalform Alle Attribute sind atomar; keine Substruktur der Attribute 2. Normalform Alle Nicht-Schlüsselattribute sind voll funktional abhängig vom (kompletten) Schlüssel (man braucht den ganzen Schlüssel, um auf Full FD: X A iff nicht Element von x’ Teilmenge von x ungleich x: x’ A Beispiel: Angebot (Lieferant #, Artikel #, Lieferantenname) PK (Lieferant #, Artikel #) Lieferant # Lieferantenname (Lieferant #, Artikel) nicht voll funktional abhängig Lieferantenname Nebenbemerkung: Wenn der PK aus einem Attribut besteht (und die Relation in 1. Normalform ist), ist die Relation immer in 2. Normalform. Definition Transitive Abhängigkeit Attribut B heißt transitiv abhängig vom Schlüssel S, wenn: 1. B nicht Teilmenge S 2. es existiert A, B nicht Teilmenge A, S A, (A nicht S), A B 3. Normalform Wenn R in 2. Normalform und keine transitive Abhängigkeit von Nicht-Schlüsselattributen Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester R ist in 2. NF: für alle Y Schl (R), A nicht Element Y, A Nicht-Schlüsselattribut, Y A Y ist Schlüssel von R (Y Schl. (R)) Beilspiel: EMP (empno, ename, deptno, dname) Empno ename Empno deptno Deptno dname Also: empno impliziert dname. Transitive Abhängigkeit Nicht in 3. Normalform Anmerkung: Die 3. Normalform betrifft nur Nicht-Schlüsselattribute, deshalb BCNF, für alle Y Schl (R), A Y : Y A Y enthält Schlüssel Beispiel: Angebot (Lief#, LiefName, Artikel#, Menge) Annahme: LiefName eindeutig exotisch Zusammenfassung: Wenn auf der linken Seite der FDs nur der (vollständige) Schlüssel steht, dann ist 1. und 2. Normalform erfüllt Abhilfe: Zerlegung (decomposition) R (X, Y) R1 (X) R2 (Y) R’ (X, Y) Val (R) = Val (R’) R = R’? Wenn ja, dann “verlustfreie Zerlegung”, in der Regel aber nicht zutreffend (R R’) Normalerweise R’ R, man erhält zusätzliche Tupel Behauptung: R (X, Y) PI X (R) |x| PI Y (R) = R, wenn XY X oder X Y Y z.B.: EMP (empno, ename, deptno, dname) 3. NF verletzt wegen deptno dname, X Y == deptno „Your attributes shall depend on the key, the whole key and nothing but the key, so help me Codd!” Lösung des Beispiels: EMPLOYEE (SSN, FName, MInit, LName, …, Supervisor_SSN, Department_Number) DEPARTMENT (Name, Department_Number, Manager_SSN, Manager_Startdate PROJECT (Name, Project_Number, Location, Department_Number) Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch DEPENDENT (Name, …, Relationship, SSN) LOCATION (Location, Department_Number) WORKS_ON (SSN, Project_Number, Hours) Zugriff auf Daten Darstellung der Relationenalgebra in Form einer Programmiersprache QUEL (Query Language) Ingres (aus den 70er Jahren, Berkley, Stonebreaker, Linux) Daraus hervorgegangen: Postgres Vergleiche RDB von DEC Abfragen: RANGE OF t1 IS R1 RANGE OF ti IS Ri RETRIEVE (t1.A1,…, ti.Ai) WHERE P (t1,…, ti) Ti: Tupelvariablen; Ri: Relation Pi: Prädikat z.B. RANGE OF e IS EMP RANGE OF d IS DEPT RETRIEVE (e.ename, d.dname) WHERE (e.deptno = d.deptno) Daten hinzufügen: APPEND TO emp (empno = 4711, ename = ’smith’) Daten ändern: RANGE OF e IS EMP REPLACE (sal = 1000) WHERE (deptno = 10) QBE (Query by Example) In den 70er Jahren von IBM entwickelt Daten als Tabellen grafische Darstellung Keine Programmierkenntnisse Einfache Alternative zu SQL Prinzip: Anwender füllt „Mustertabelle“ aus, System generiert Anfrage (SQL) Schritte. 1. Auswahl der Tabelle 2. Attribute werden dargestellt 3. Semester Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester 3. Auswählen (Projektion), Bedingungen (Selektion) Bedingungen in einer Zeile werden mit ‚UND’ verknüpft, Bedingungen in unterschiedlichen Zeilen werden ‚ODER’ verknüpft Ende der 80er Jahre wieder aufgegriffen in Paradox, Anfang der 90er Jahre in Access Auch Operationen zum Löschen, Ändern, Einfügen Sehr mächtig, wurde aber schlecht akzeptiert. Warum? Kompliziert für komplexe Probleme SQL (structured query language) IBM, Anfang der 70er Jahre als Abfragesprache für „System R“ Interaktiv für Endanwender (UFI) Programmierschnittstelle (embedded SQL) Wurde deshalb von allen Herstellern übernommen Ende der 80er: Standardisierung (ISO 9075) SQL 89 Anfang der 90er: SQL 92 Standard Elemente: DML (Data Manipulation Language) DDL (Data Definition Language) Einfache (?) Sprache, 4 Statements: SELECT, INSERT, UPDATE, DELETE ( + WHERE clause) SELECT Holt Datensätze aus DB (= erzeugt eine neue temporäre Relation) Grundstruktur: SELECT [ALL|DISTINCT] Attributliste FROM r1,…, Rn [WHERE Prädikat] [ORDER BY A1 [ASC|DESC] [{,Ai [ASC|DESC]}]] Funktion: Attributliste (Prädikat (R1 … Rn)) Attributliste: Rj.Aji, falls eindeutig, reicht auch Aji Oder Rj.Aji [AS] aliasname Oder Rj.* alle Attribute von Rj Oder Konstanten, Funktionen, Ausdrücke z.B. EMP (ename) DEPT (dname, mgr) SELECT EMP.ename, DEPT.dname, DEPT.mgr SELECT EMP.ename AS nachname (Alias) Vorlesung Informationssysteme – Datenbanksysteme Prädikat: Hr. Till Hänisch 3. Semester Attribute, Konstanten, Operatoren (=, <>, >, <, <=, >=,…, AND, OR, NOT) Funktionen Anmerkung: SELECT ist Projektion, WHERE clause ist Selektion Vollständige Definition des SELECT Statements ist zu unübersichtlich, deshalb Darstellung an Beispielen Operationen auf einer Tabelle SELECT * FROM emp Gibt Tabelle emp vollständig aus Projektion A1,...,An (R) == SELECT A1,..., An FRON R Mit Selektion Namen der Mitarbeiter in DEPT 20 SELECT ename FROM emp WHERE deptno = 20 Alle Mitarbeiter mit Gehalt >= 2000 SELECT ename FROM emp WHERE sal >= 2000 Alle Mitarbeiter mit Gehalt zwischen 2000 und 3000 SELECT ename FROM emp WHERE sal >= 2000 AND sal <= 3000 Oder SELECT ename FROM emp WHERE sal BETWEEN 2000 AND 3000 Alle Mitarbeiter die weder in Dept 20 noch in Dept arbeiten SELECT ename FROM emp WHERE deptno <> 20 AND deptno <> 30 Oder SELECT ename FROM emp WHERE NOT (deptno = 20 OR deptno = 30) Oder SELECT ename FROM emp WHERE NOT (deptno IN (20, 30)) Vergleich von Strings Exakter Vergleich mit “=”, dabei keine Wildcards Deshalb LIKE Operator SELECT ename FROM emp WHERE ename LIKE ‘smi%’ % steht für 1 beliebige Zeichenfolge _ steht für 1 beliebiges Zeichen Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester z.B bei Umlauten wie Hänisch (mit Hä, oder Hae...?) WHERE ename LIKE ‚H%nisch’ Vergleich von Datumswerten Schwierig, weil kein einheitliches Datumsformat Länderabhängig Implizierte Konvertierung möglich, aber unsicher z.B. WHERE hiredate > ’01/05/1980’ Deshalb: explizite Konvertierung! Datenbankabhängig Sybase: WHERE hiredat > CONVERT (DATETIME, ’01-05-1980, 110) Oracle: WHERE hiredate > TO_DATE(’01-05-1980’, ‘mm-dd-yyyy’) Arithmetische Ausdrücke In Attributen und WHERE clauses z.B. SELECT sal + comm AS Gehalt FROM emp ORDER BY Gehalt Operatoren: +,-, *, /, und zusätzlich viele Funktionen, die nur teilweise standardisiert) SELECT sin (sal) FROM emp ??? Wichtig: String Funktionen, z.B. SUBSTRING (string, start, ende) DATALENGTH (string) LTRIM, RTRIM, TRIM entfernt Blanks UPPER (string) Usw. JOINS Verknüpfung mehrerer Tabellen über (gemeinsame) Attribute SELECT Attributliste FROM R1,..., Rn WHERE Rj.Ajk = Re.Amn AND … Operator ‘=’ Equi Join, sonst Non-Equi-Join z.B. Mitarbeter mit Gehaltsklasse SELECT ename.sal.grade FROM emp.salgrade WHERE sal >= lograde AND sal < higrade Korrelationsvariablen dienen der Zuordnung von Attributen zu Relationen: SELECT e.ename, d.danem FROM emp e, dept d WHERE e.deptno = d.deptno Insbesondere bei Self-Joins z.B. Alle Mitarbeiter, die mehr verdienen als ihr Manager: SELECT e.ename,e.sal FROM emp e, emp m Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch WHERE e.mgr = m.empno AND e.sal > m.sal Aufgabe: Bringen Sie in die 3. Normalform Buch (Titel, Autor, Typ, Preis, Verleger) Anm. Titel ist eindeutig. FD’s: Titel Verleger, Typ Typ Preis Lösung: BUCH (Titel, Typ, Verleger) PREISLISTE (Typ, Preis) IST_AUTOR (Titel, Autor) Liste aller Angestellten mit dem jeweiligen Manager SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno ‘King’ fehlt, weil dort mgr = NULL ist. emps, die keinen mgr haben, fehlen. Was tun? OUTER JOIN SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr * = m.empno In Oracle: … WHERE e.mgr (+)= m.empno LEFTOUTER JOIN RIGHT JOIN INNER JOIN ist der normale Join Alternative Syntax nach SQL 92 SELECT e.ename, d.dname FROM emp e JOIN dept d ON deptno = d.deptno SUBQUERIES Die WHERE-clause kann sich auf eine andere (Sub-) Query beziehen. Formen: SELECT Attributliste FROM R1,...,Rn WHERE [NOT] EXISTS (SELECT…) SELECT Attributliste FROM R1,...,Rn WHERE Aji : ANY|ALL (SELECT…) SELECT Attributliste FROM R1,...,Rn WHERE Aji [NOT] IN (SELECT…) 3. Semester Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Alle Mitarbeiter mit demselben Job wie ‘Jones’ SELECT ename, job FROM emp WHERE job = ANY ( SELECT job FROM emp WHERE ename = ‘Jones’ ) Alle Mitarbeiter, die mehr verdienen als irgendein Mitarbeiter von dept 30 SELECT ename, sal FROM emp WHERE sal > ANY ( SELECT sal FROM emp WHERE deptno = 30 ) Alle Mitarbeiter, die mehr verdienen, als jeder in dept 30 SELECT ename, sal FROM emp WHERE sal > ALL ( SELECT sal FROM emp WHERE deptno = 30 ) Anmerkungen: IN == “= ANY” NOT IN == „<> ALL“ z.B. alle Mitarbeiter mit einen Job, den es in dept 30 nicht gibt SELECT ename, job FROM emp WHERE job = NOT IN ( SELECT job FROM emp WHERE deptno = ‘30’ ) Alle Mitarebiter, die mindestens einen anderen managen SELECT ename, job FROM emp e WHERE EXISTS ( SELECT * FROM emp WHERE mgr = e.empno ) Anmerkung: Diese Subquery muss für jeden Tupel der Hauptquery ausgeführt werden (da in der FROM-Clause der Subquery auf ein Attribut der Hauptquery Bezug genommen wird). Correlated Subquery Achtung: Performance geht bei großen Datenmengen in den Keller Deshalb: Correlated Subqueries durch Joins ersetzen. SELECT m.ename, m.job FROM emp m, emp e WHERE e.mgr = m.empno Doppelte werden ausgegeben, daher mit DESTINCT: SELECT DESTINCT m.ename, m.job FROM emp m, emp e WHERE e.mgr = m.empno Mitarbeiter mit dem größten Gehalt: SELECT ename, sal FROM emp e WHERE NOT EXISTS ( Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester SELECT * FROM emp WHERE sal > e.sal ) Aggregate Functions Oft sind nicht die einzelnen Tupel, sondern nur eine Zusammenfassung des Inhalts von Interesse. Aggregate Functions Wichtige Aggregate Functions: COUNT (*) Zahl der Tupel COUNT ([DISTINCT] Attribut) Zahl der verschiedenen Attributwerte (<> NULL) MAX () MIN () AVG () Durchschnitt SUM () z.B. Zahl der Mitarbeiter: SELECT COUNT (*) FROM emp Zahl der unterschiedlichen Jobs: SELECT COUNT DISTINCT job FROM emp Angestellter mit höchstem Gehalt: SELECT ename, sal FROM emp WHERE sal >= ( SELECT MAX (sal) FROM emp ) Durchschnittliches Gehalt / Abteilung? Gruppierung von Aggregatfunktion SELECT deptno, AVG (sal) FROM emp GROUP BY ddeptno Struktur: SELECT … FROM … WHERE … GROUP BY Gruppierungsausdruck HAVING Gruppierungsbedingung ORDER BY … GROUP BY bewirkt interne Teilmengenbildung, Aggregatfunktionen wirken jeweils auf diese Teilmengen. Unterschied WHERE vs. HAVING Mit WHERE werden Zeilen eliminiert. Mit HAVING können ganz Teilmengen (Gruppen) eliminiert werden. Durchschnittliches Gehalt je Abteilung mit mindestens 5 Mitarbeitern: SELECT deptno, AVG (sal) FROM emp GROUP BY deptno HAVING COUNT (*) >= 5 Jobs, bei denen Durchschnittsgehalt größer ist, als das Durchschnittsgehalt der Manager Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester SELECT job, AVG (sal) FROM emp GROUP BY job HAVING AVG (sal) > ( SELECT AVG (sal) FROM emp WHERE job = ‘manager’ ) Mengenoperationen mit Query Ergebnissen SELECT-Statements liefern Relationen (= Mengen) zurück, diese können mit den üblichen Mengenoperationen verknüpft werden SELECT statement UNION | INTERCEPT | MINUS/EXCEPT SELECT statement … Name Person Student Dozent Semester Gehalt Verwendung z.B. bei Spezialisierungen etwa: SELECT Name FROM Student UNION SELECT Name FROM Dozent ORDER BY Name Sybase: nur UNION ins implementiert DML (Data Manipulation Language) Insert Fügt Datensätze zu einer Tabelle hinzu INSER INTO name [(col-list)] Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Falls nur für einen Teil der Spalten Werte angegeben werden, werden diese in col-list definiert, Z.B. Tabelle Person (Name, Vormane, Alter) INSERT INTO Person (Name, Vorname) VALUES (’Hänisch’, ’Till’) INSERT INTO Person (Name) SELECT ename FROM emp INSERT INTO Person VALUES (‘Hänisch’, ‘Till’, ‘33’) Achtung gefährlich! Delete Löscht Datensätze aus der Tabelle DELETE FROM table WHERE … z.B: DELETE FROM emp WHERE deptno = 10 Alle Angestellten, die mehr verdienen als ihr Chef DELETE FROM emp WHERE empno IN ( SELECT e.empno FROM emp e, emp m WHERE e.mgr = m.empno AND e.sal > m.sal ) Duplikate löschen: Tabelle Person (id, Name) DELETE FROM Person WHERE id = ( SELECT MAX (id) FROM Perons GROUP BY Name HAVING COIUNT (*) > 1 ) Update Ändert existierende Tupel UPDATE tabble SET col = VALUE [, col = VALUE] [WHERE …] z.B. erhöhen des Gehalts aller Angestelten um 10%, die weniger als Durchschnitt verdienen: UPDATE emp SET sal = 1.1 * sal WHERE sal < ( SELECT AVG (sal) FROM emp ) Anmerkung: Während des Updates ändert sich der Durchschnitt!!! Daher: Die Subquery wird vor dem UPDATE durchgeführt. Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester DDL (Data Definition Language) Definition von Tabellen (Relationen,...) Befehle: CREATE, DROP, ALTER auszugsweise, im wesentlichen CREATE TABLE Der Rest wird in der Vorlesung Datenbanken II Anmerkung: Es existieren grafische Tools für DDL, nur eingeschränkt sinnvoll (insbesondere bei Wiederverwendung,...) „ Spielzeug“ (ausgenommen Spezialfälle) DROP TABLE name Tabelle löschen Tabelle anlagen: Information: Name, Attr. + Datentypen, Constraints Syntax: CREATE TABLE name ( coldef [, coldef] [, tableconstraints] ) Coldef = name, type [länge], [darf NULL oder nicht], [colconstraint] Tableconsraint = CONSTRAINT name constraint Colconstraint = [] constraint CREATE TABLE Person ( name VARCHAR (30), id INT ) Datentypen Auszugsweise, Sybase, Oracle CHAR(n): VARCHAR (n): DATETIME DECIMAL (p,s) FLOAT (l) INT String fester Länge (n), CHAR (1) für Boolean String variabler Länge, max. n Zeichen übliche Zeichenketten, Maximallänge typ, beschränkt, in Oracle VARCHAR2, max. 4000 Zeichen Aussprache: „vare-care“, „var car“ „var char“ Datum + Uhrzeit, bei Oracle: DATE Anmerkung: Format unterschiedlich, einstellbar Festkommazahl (exakt), z.B. bei Geld, Währung DECIMAL (10,2), Oracle: NUMBER Fließkommazahl, eher selten bei Datenbanken ganze Zahl, typ. 32 Bit, Oracle: INTEGER Zusätzlich je nach System Datentypen für: Texte Bilder Video Audio Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester CREATE TABLE Person ( name VARCHAR (100) NOT NULL, vorname VARCHAR (50) NULL, alter INT NULL ) Constraints (optional) PRIMARY KEY definiert Primärschlüssel CREATE TBALE Person ( name VARCHAR (100) NOT NULL, vorname VARCHAR (40) NOT NULL alter INT NULL, PRIMARY KEY (name, vorname) ) FOREIGN KEY um Relationships ins physikalische Modell zu übertragen, definiert man foreign Key Constraints, mit dem Verweis auf den zugehörigen Primary Key z.B. Telefon (Nummer, Art) N:1 Relationship zu Person CREATE TABLE telefon ( nummer VARCHAR (20) NOT NULL, art CHAR (1) NOT NULL, name VARCHAR (100) NOT NULL, vorname VARCHAR (40) NOT NULL, CONSTRAINT fk_telefon_person FOREIGN KEY (name, vorname) REFERENCES person (name, vorname) ) UNIQUE Werte der entsprechenden Columns müssen eindeutig sein (PRIMARY KEY impliziert UNIQUE) Eindeutigkeit von Candidate Keys, z.B. Person (ID) U_Person UNIQUE (name, vorname) CHECK Bedingung für ein Attribut z.B. bei Person ( alter INT NOT NULL CHECK (alter > 0) z.B. Boolean-Ersatz flag CHAR (1) NOT NULL CHECK (flag IN (’Y’, ’N’)) Bei „modernen“ SQL Dialekten auch Sequenzen zulässig, z.B. Preis DECIMAL (10,2) CHECK (preis >= ( SELECT ... ) Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Übung: Großhändler für Computer-Komponenten Artikelverwaltung (für Angebotserstellung) Informationen über Artikel: Nummer, Name, Beschreibung, Hersteller (mindestmenge, Bestellmenge), EK, VK Zusätzlich Artikeldisponentenliste, Informationen z.B. Festplatte: Kapazität, Zugriffszeit, RPM CPU: Taktfrequenz, Cachegröße Mainboard: max. RAM Kapazität, zahl der PCI-Slots Artikel werden in Chargen gekauft, jeweils Datum, Menge, Preis, Preisberechnung; manuell, FIFO, VK = 20% + EK Konzeptionelles Datenmodell Lösung: Mainboard RPM Takt CPU Festplatten d Kapaz. Nummer Prod . Hersteller Artikel Nummer Name Beschreibung Datum liefert Lieferant Menge Preis Logisches Datenmodell Artikel (Nummer, Name, Beschreibung, Bestand, Herstellernummer) Liefert (Lieferungsnummer, Lieferantennummer, Artikelnummer, Menge, Datum, Preis) Lieferant (Nummer,...) Hersteller (Herstellernummer, Name,...) Produziert (Herstellernummer, Artikelnummer) Festplatte (Artikelnummer, Kapazität, RPM, Zugriffszeit) Mainboard (Artikelnummer, RAM-Kapazität, Slots) CPU (Artikelnummer, Taktfrequenz, Cache-Größe) Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Zugriffskontrolle Standard: Nur der Eigentümer von Objekten hat Rechte, andere Benutzer haben keinen Zugriff Eigentümer kann Rechte an seinen Ojbekten freigeben GRANT / REVOKE privileg ON table TO user [, user] / PUBLIC Privileg: Select Insert Update Delete z.B. GRAND SELECT ON emp TO PUBLIC Zugriff auf Tabellen anderer Benutzer durch Qualifizierung mit Namen des Eigentümers z.B. Tabelle emo des Benutzers master SELECT ... FROM master.emp ... Anmerkung: Organisation durch Rollen bei größeren Menge von Tabellen Views View = „virtuelle“, d.h. abgeleitete Relation Aus einer oder mehreren Relationen wird durch Query eine neue „virtuelle“ erzeugt: CREATE VIEW name AS SELECT ... Wenn sich Tupel der Basisrelation(en) ändern, dann ändert sich auch der View. View-Relationen existieren physikalisch nur als Definition, die Tupel werden bei jedem Zugriff berechnet. Performance (analog Makro) Wozu? Vorformulierung von (häufigen, komplexen) Anfragen z.B. Bei emp: Gehalt = sal + comm Abstraktion von Business Rules Denormalisation: CREATE VIEW angst AS SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno Verschiedene Sichten auf Daten (Datenschutz, Übersichtlichkeit) z.B. Tabelle emp enthält Gehalt, dies soll aber nur für die Personalabteilung sichtbar sein. Hausmeister braucht Liste der Angestellten. vertikale Partitionierung nur über bestimmte Spalten Horizontale Aufspaltung: z.B. Sekretärin darf die vollständigen Daten ihrer Abteilung sehen, aber auch nur diese: 1 View /Abteilung CREATE VIEW emp_it AS SELECT * FROM emp WHERE deptno = 50 problematisch! Dehalb: Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Bei Sybase Funktion USER liefert den Namen des aktuellen Users zurück. Anmerkungen: Views sind sehr mächtig, teilweise entstehen Probleme bei der Implementierung (Performance, Einschränkungen) INSERT, UPDATE und DELETE sind mit Einschränkungen auch auf Views möglich (typischerweise: eine Basisrelation, NULLs, keine Expressions, keine Aggregates,...) Oracle: INSTEAD OF Triggers: erlauben weitgehenden Einsatz von Views Open ends Indices: Schneller Zugriff auf bestimmte Datensätze (insbesondere Joins) Wie findet das DBMS einen Datensatz (z.B. WHERE empno = 50) o Sequenzielles Durchlaufen aller Datensätze (full table scan) Vorteil: einfach, „alle“ Funktionen möglich Nachteil: bei großen Tabellen langsam (ab 1000 Datensätze) o Index: geeignete, typisch sortierte Speicherung der vorkommenden Werte (typ. Bäume (B*), Hash-Tables Vorteil: typischerweise schneller Zugriff (Zugriffsgeschwindigkeit ist ungefähr unabhängig von der Zahl der Datensätze, ungefähr linear mit der Zahl der Treffer) Nachteil: Nur bestimmte Funktionen möglich (z.B. nicht: LIKE ’%ahn%’), Overhead (Speicherplatz, Transaktionen) bei DML Operationen SELECT wird schneller, alles andere wird langsamer! CREATE [UNIQUE] INDEX name ON table (attributeliste) Trigger Für Bestimmte Aktionen (INSERT, UPDATE, DELETE) kann Code hinterlegt werden, der davor / danach ausgeführt wird. z.B. zur Konsistenzprüfung, Protokollierung Physikalisches Datenmodell Transformation: Relation Tabelle (trivial) es bleibt: Auswahl von Datentypen Abbildung von Relationships (FK constraints) Ggf. Denormalisierung, z.B. durch Views,... Abbildung von Business Rules (Constraints, Views, Triggens,...) in Zusammenhang mit Anwendungsentwicklung Anlegen eigener Indices Physikalische Speicherung (Verteilung, Blockgröße) Projekt, Datenbanken II Vorlesung Informationssysteme – Datenbanksysteme Hr. Till Hänisch 3. Semester Programmierung Client/Server-Applikationen mit Oracle Daten im Server, was nun? Anwendung muss auf gespeicherte Daten zugreifen Schnittstelle Applikation/Server (SQL) o OCI o Embedden SQL o ODBC o 4GL (PL/SQL) Benutzerschnittstelle o Developer/2000 o Access (Excel), Powerbuilder, Visual Basic OCI Oracle Call Interface (CLI) Kompliziert, mächtig, Oracle spezifisch Bestimmte Funktionen nur mit OCI o Mehrere Transaktionen o BLOBs Im wesentlichen 2 Methoden: Statisch SQL steht im Programmcode (ESQL) Dynamisch SQL variabel (ODBC / SDBC) Embedded SQL Einbettung von SQL in eine „Wirt-“ Programmiersprache (C, Cobol, PL/1, Java) Vor-übersetzung des SQL Anteils in Programmiersprache, Precompiler Syntax ist in der Regel die gleiche, wie beim interaktiven SQL, zusätzliche Konstrukte Einbettung Fehlerbehandlung Übergabe von Variablen SQL und umgekehrt Kennzeichnung von SQL durch EXEC SQL „:“ zur Kennzeichnung von Variablen Vorgehen: Example.cp (C source und SQL) Precompiler Example.c (C Programm mit CL1 Befehlen) Compiler example.o Linker mit ESQL.a Problem: SQL liefert Tupelmenge, Darstellung in C, Cobol,…??? Lösung: ADT Cursor: Iterator, Tupelpointer Operationen: OPEN: Führt SQL Abfrage aus Vorlesung Informationssysteme – Datenbanksysteme FETCH INTO variable: CLOSE: Hr. Till Hänisch 3. Semester holt Atributswerte des aktuellen Tupels und setzt den Zeiger auf den nächsten Tupel Schließt... Problem: SQL muss vorher bekannt sein. Wie wird z.B. isql realisiert? dynamic SQL Tupelweise Verarbeitung unter Umständen nicht effizient genug Array Verarbeitung ESQL standardisiert, wenn aber DB zur Laufzeit ausgewählt wird gibt’s Probleme!!! Klausur: 2 Teile: praktisch: was zu tun, Beschreibung, daraus ER Diagramm, Normalformen überprüfen, Zustandsdiagramm, SQL (aber nicht so viel) theoretisch: Fragen beantworten
