DBSY – Datenbankensysteme 3AHITS Typische Anwendungen von großen Datenbankserversystemen: Handelsketten mit vielen Filialen Kreditkartenfirmen Vernetzte Reisebüros Dateibasierter Ansatz eines Systems Jede Anwendung definiert und verwaltet ihre eigenen Dateien. Vertrieb Dateneingabe, Berichte Verarbeitungsroutine Datei Dateindefinition Fertigung Dateneingabe, Berichte Verarbeitungsroutine Datei Dateindefinition Nachteile des dateibasierten Ansatzes: Die Daten sind Isoliert in verschiedenen Dateien Unkontrolliertes Duplizieren von Daten Datenabhängigkeit : Die physische Struktur und die Speicherung der Daten werden im Anwendungsprogramm festgelegt. Dies führ zu großen Schwierigkeiten bei Veränderungen z.B.: ein Namensfeld soll von 20 auf 25 Zeichen geändert werden. Inkompatible Dateiformate: Die Dateien sind von der Programmiersprache abhängig Unkontrollierte Vermehrung von Anwendungsprogrammen Datenbankbasierter Ansatz Vertrieb Dateneingabe Berichte Fertigung Dateneingabe Berichte Datenbanksystem Daten Eine Datenbank ist eine gemeinsame Sammlung von logisch verwandten Daten und eine Beschreibung dieser Daten. Der Systemkatalog (Metadaten, Data Dictionary) enthält die Beschreibung der Daten. Dadurch ist eine Unabhängigkeit zwischen Programm und Daten gesichert. Die interne Definition eines Objektes kann verändert werden, ohne dass es die Benutzer berührt (Datenabstraktion). Holzmann Florian Seite 1 3AHITS 2009/10 DBSY – Datenbankensysteme DBMS – (Datenbankmanagement – System) DBMS ist ein Softwaresystem, das es den Benutzern erlaubt eine Datenbank zu definieren, zu erstellen und zu verändern und einen kontrollierten Zugriff auf diese Datenbank bereitstellt. Beteiligte Personen in einer Datenbankumgebung Datenadministrator: Entwicklung und Einhaltung von Standards für den logischen Datenbankentwurf. Datenbankadministrator (DBA): Ist verantwortlich für die physische Ausführung der Datenbank (Implementierung, Sicherheitskontrolle, Wartung der betrieblichen Systeme, Performance Tuning, Versionsverwaltung, Sicherungen, …) Datenbankdesigner: Beschäftigt sich ebenfalls mit dem logischen Datenbankentwurf. Er arbeitet eng mit Fachabteilungen zusammen. Große Datenbankserver-Anbieter: Oracle(45%), SQL-Server(20%), DB2(20%) Vorteile von DBMS: Keine Mehrfachspeicherung der selben Daten Datenkonsistenz Gewinnung von mehr Information aus der gleichen Datenmenge Gemeinsamer Datenbestand Die Datenbank gehört „den Gesamten Unternehmen“ nicht den einzelnen Abteilungen Verbesserte Datenintegrität Bsp.: Das Gehalt eines Angestellten darf nicht größer als 60.000 € sein. Höhere Sicherheit: Festlegung von Schutzfunktionen (Benutzername, Kennwort) durch den DBA (Admin) Verbesserte Standardisierung Austausch von Daten zwischen Systemen, Abteilungen, … Verbesserte Wirtschaftlichkeit durch vergrößerte Einheiten Bsp.: Für das Unternehmen gibt es eine gesamt Budget, nicht für jede Abteilung erarbeitet eine eigene Lösung Gleichgewichtung zwischen gegensätzlichen Anforderungen Erklärung: Benutzer haben unterschiedliche Bedürfnisse# Verbesserter Zugriff auf die Daten Erklärung: Datenbanksysteme bieten integrierte Abfragewerkzeuge Erhöhung der Produktivität Bei dateibasierten Systemen werden gleiche oder gleichartige Programme immer wieder neu geschrieben. Leichtere Wartung durch Datenunabhängigkeit Erklärung: Die Programme sind nicht direkt von den Daten abhängig Verbesserte Parallelität Verbesserte Datensicherung und Datenwiederherstellung (Backup und Recovery) Bei dateibasierten Systemen ist eine eigene Lösung notwendig für die Sicherung von Daten Holzmann Florian Seite 2 3AHITS 2009/10 DBSY – Datenbankensysteme Nachteile: Komplexität: Gefahr von falschen Entwurfsentscheidungen Die Größe des Datenbanksystems Software hat einen Umfang im Gigabyte-Bereich Die Kosten: Mehr Benutzer-Datenbanksysteme können bis zu 500.000 € kosten. Notwendig für einen professionellen Einsatz ist auch ein Servicevertrag mit dem Hersteller. Die Anschaffung zusätzlicher teurer Hardware kann sinnvoll sein (z.B.: SAN = Storage Area Network) Konvertierungskosten Anpassung vorhandener Anwendungen an das Datenbanksystem, Schulung der Anwender und spezielle Mitarbeiter notwendig für den Betrieb. Leistung: Ein dateibasiertes System kann auf eine bestimmte Anwendung besser angepasst oder zugeschnitten werden (Ein DBMS ist immer ein allgemeines System). ANSI-SPARC Architektur Wikipedia Externe Ebene: Ist die Sicht des Benutzers der Datenbank. Sie beschreibt den für den Benutzer relevanten Teil der Datenbank. Konzeptuelle Ebene: Beschreibt die logische Struktur aus der Sicht des DBA (Admin). Sie beschreibt, welche Daten in der Datenbank gespeichert sind und wie sie zueinander in Beziehung stehen. Interne Ebene: Ist die physische Abbildung der Datenbank und beschreibt, wie die Daten gespeichert sind Funktionen eines Datenbanksystems (Codd, 1982) Extrem wichtig bei Test 1. Der Benutzer soll Daten in der Datenbank speichern, abrufen und aktualisieren können. 2. Die Beschreibung von Datensätzen muss in einem Katalog gespeichert sein und für die Benutzer zugänglich sein 3. Transaktionen: Entweder alle Aktualisierungen/Veränderungen werden durchgeführt oder gar keine. 4. Parallelitätssteuerung: Die Datenbank muss in einem korrekten Zustand bleiben, auch wenn mehrere Benutzer gleichzeitig Veränderungen/Aktualisierungen durchführen. T1 Read Kto = Kto - 10 Write Holzmann Florian T2 Read Kto = Kto + 100 Write Kto. 100€ 100€ 200€ 90€ Seite 3 3AHITS 2009/10 DBSY – Datenbankensysteme 5. Wenn die Datenbank beschädigt ist, muss ein Wiederherstellungsmechanismus zur Verfügung stehen 6. Nur autorisierte Benutzer dürfen auf die Datenbank zugreifen 7. Das Datenbanksystem muss Kommunikationssoftware unterstützen 8. Integrität: Bei Änderungen von Daten müssen bestimmte Regeln eingehalten werden (z.B.: Das Gehalt eines Angestellten in einer Abteilung darf nicht größter sein als 4.000€). 9. Unabhängigkeit der Programme von der aktuellen Struktur der Datenbank. 10. Dienstprogramme: Import/Export von Daten; Statistische Analyseprogramme von Datenbanken; Überwachungsfunktionen (Monitoring) Architektur eines Datenbanksystems 1. Fernverarbeitung (Remote Processing) Terminals Server 2. Dateiserver LAN Dateianforderung Dateirücksendung Server 3. Client/Server Architektur Anstelle Dateirücksendung Datensatzrücksendung Das relationale Datenbild Relation: Attribut: Wertebereich: Tupel: Grad: Kardinalität: Relationale Datenbank: Mathematische Relation: Holzmann Florian Ist eine Tabelle mit Spalten und Zeilen. Benannte Spalte einer Relation. Ist die Menge aller zulässigen Werte für ein oder mehrere Attribute. Ist eine Zeile einer Relation Ist die Anzahl der Attribute Ist die Anzahl der Tupel Ist eine Sammlung normalisierter Relation (Bsp.: Normalisierung) Jede Untermenge des Kartesischen Produkts ist eine Relation Seite 4 3AHITS 2009/10 DBSY – Datenbankensysteme Karte‘sches Produkt: Mengen D1, D2 D1xD2 = {(x, y)/xeD1 und yeD2} D1xD2x … xD4 = {(x1, x2, … x4) / x; eD;} select * form T1, T2, T3; T1xT2xT3 Eigenschaften von Relationen Jede Relation hat einen Namen, der sich von allen anderen Relationsnamen unterscheidet. Jede Zelle einer Relation enthält einen einzelnen Atomaren Wert (1. Normalform) Namen der Attribute müssen unterschiedlich sein Die Werte des Attributs stammen aus dem gleichen Wertebereich Die Reihenfolge der Attribute hat keine Bedeutung Es gibt keine doppelten Tupel Die Anordnung der Tupel hat keine Bedeutung Superschlüssel Ein Attribut oder eine Menge von Attributen, durch das ein Tubel in der Relation eindeutig bestimmt ist. Kann auch zusätzliche Attribute enthalten, die für eine eindeutige Bestimmung nicht notwendig sind. Schlüsselkandidat: Ist ein Superschlüssel, der keine echte Untermenge enthält, die selbst ein Superschlüssel innerhalb der Relation ist (Eindeutigkeit und keine Vereinfachung möglich). Primärschlüssel: (Primary Key) Ist ein Schlüsselkandidat der ausgewählt wird, um Tupel in einer Relation eindeutig zu bestimmen. Fremdschlüssel: (Foreign Key) Ist ein Attribut oder eine Menge von Attributen innerhalb einer Relation, das dem Schlüsselkandidaten irgendeiner Relation (auch der eigenen) entspricht T1 T2 PK M1 = {A, B, C} M2 = {1, 2} Holzmann Florian FK M1xM2 {(A,1), (B,2), (C,2), …} Seite 5 3AHITS 2009/10 DBSY – Datenbankensysteme Systematische Behandlung von NULL-Werten Die NULL stellt den Wert eines Attributs dar, der aktuell nicht bekannt ist. Ist nicht dasselbe wie ein nummerischer Wert oder eine nummerische Zeichenfolge oder eine Zeichenkette mit Leerzeichen. NULL stellt die Abwesenheit eines Wertes dar. Entitätsintegrität In einer Relation kann kein Attribut eines Primärschlüssels NULL sein. Referenzielle Integrität T1 PK T2 FK Wenn ein Fremdschlüssel in einer Relation vorhanden ist, muss sein Wert, dem Wert eines Schlüsselkandidaten eines Tupels in der Heimatrelation entsprechen oder ganz NULL sein. Unternehmensbedingungen (business rules) Sind zusätzliche Regeln und Bedingungen, die von den Benutzern oder Datenbankadministratoren festgelegt werden. Wann ist ein DB-System relational: Antwort von Codd: „In Codd we trust“ Grundregeln: Jedes relationale DMS muss Datenbanken vollständig mit Hilfe seiner Rationalen Fähigkeiten verwalten. R1: Alle Informationen müssen in Tabellen dargestellt sein. R2: Zugriff auf Daten Jeder Wert muss logisch durch eine Kombination von Tabellenname, Primärschlüssel und Attributname auffindbar sein. In jedem Schnittpunkt einer Zeile mit einer Spalte darf nur genau ein Wert stehen. R3: Behandlung von NULL Werten NULL Werte stellen fehlende Information dar und unabhängig von Datentyp des Attributs behandeln. Holzmann Florian Seite 6 3AHITS 2009/10 DBSY – Datenbankensysteme R4: DB Struktur Wird genauso gespeichert wie „normale“ Daten. Die Struktur der Tabellen wird in einem Katalog (Data dictionary) gespeichert. R5: Abfragesprache Muss folgende Funktionen unterstützen: - Definition von Daten - Definition von Views („Sichten“ auf Tabellen) - Definition von Integritätsbedingungen R6: Alle Views, die Theoretisch aktualisiert werden können, können auch vom System aktualisiert werden (kein exzidierendes DB System unterstützt diese Funktion tatsächlich , den es wurden noch keine Bedingungen gefunden, die alle theoretisch aktualisierbaren Views identifiziert). R7: Abfrage oder Veränderungsoperationen müssen als Operanden nicht nur einzelnen Datensätze erlauben. R8: Der Zugriff auf Daten muss unabhängig davon sein, wie die Daten gespeichert werden. R9: Logische Unabhängigkeit der Daten Anwendungen und Zugriffe dürfen sich logisch nicht ändern, wenn die Tabellen so verändert werden, dass alle Informationen enthalten bleiben (z.B.: nach Aufspaltung einer Tabelle in 2 Tabelle bei der Normalisierung). R10: Unabhängigkeit der Integrität: Alle Integrität-Bedingungen müssen in der Abfragesprache definierbar sein. Es muss mindestens die Entitätsintegrität und die referenzielle Integrität geben. R11: Verteilung der Daten Anwendungen und Zugriffe auf eine nicht verteilte Datenbank dürfen sich beim Übergang auf eine verteilte Datenbank logisch nicht ändern. R12: Unterlaufen der Abfragesprache Es darf durch kein Werkzeug kein direkter Zugriff auf die Daten erfolgen. Datenmodellierung Idee ERD (Entiny Relationship Diagram) „Tabellen“ Das ERD wird aus verbalen Spezifikationen generiert. DB-Server Holzmann Florian 3AHITS 2009/10 Seite 7 DBSY – Datenbankensysteme SQL - Structured Query Language Aktueller Standard 1992; Standard 1999 noch nicht in allen Datenbanksystemen. Ist die einzige Möglichkeit mit dem DBS zu kommunizieren. DML Befehle (Data Manipulation language): select Insert update delete merge DDL Befehle (Data Definiton Language): creat, alter, drop, rename, truncate TCL (Transaction Control Language) commit, rollback, savepoint DCL (Data Control Language) grant, revoke Die Verbindung zu einem Datenbankserver erfolgt mit einem Client Programm. Beispiele für Oracel: sqlplus iSqlplus[bis Vers. 10.2] SQL developer Für Schulungszwecke gibt es vordefinierte Datenbankbenutzer. SCOTT/TIGER HR/HR Möglichkeiten mit dem SQL SELECT Befehl: 1. selection 2. projection 3. join T1 T2 SQL-Befehle sind nicht case sensitive SQL-Statements können auf mehreren Zeilen verteilt sein und werden mit „;“ beendet Keywords können nicht abgekürzt werden und dürfen nicht über mehrere Zeilen gehen. Klauseln werden üblicherweise in einer eigenen Zeile angegeben. Einrückungen werden benutzt um die Lesbarkeit zu erhöhen Holzmann Florian Seite 8 3AHITS 2009/10 DBSY – Datenbankensysteme Arithmetische Operatoren +, -, *, / Beispiel (Unvollständig!): select last_name, salary, salary+300 from employ describe employees select last_name, job_id, salary, commission_pct from employees !!Arithmetische Ausdrücke die einen NULL Wert enthalten, werden zu NULL ausgewertet!! select last_name as „Nachname“, job_id, salary * commission_pct as PROVISION from employees Für Attribute können ALIAS-Namen vergeben werden. Concatenation select first_name “Vorname”, last_name “Nachname”, ‘verdient monatlich’ || salary || ‘(viel zu viel)‘ „Gehalt“ from employees Duplicate Rows select distinct department_id form emploees; DISTINCT: entfernt doppelte Zeilen (aus dem Result Set) SQL fehlt: Standard (ANSI) *keine Abkürzungen von Keywörter Holzmann Florian iSQL*plus *eine Client-Umgebung *Oracle spezifisch *Browser *Keyworte teilweise abkürzbar Seite 9 3AHITS 2009/10 DBSY – Datenbankensysteme Selection Beschränkung der angezeigten Zeilen einer Tabelle. select * from employees where salary > 12000 Zeichenketten und Datumswerte Singel qutation marks ´das ist Text´ Standartdatums-Format: DD-MON-RR {bei einer Anzeige von Daten; intern wird MEHR gespeichert, auch Std, Min, Sek} select to_char (sysdate, ´DD-MON-YYYY, HH:MM:SS´) form dual; Vergleichsoperatoren = <> BETWEEN …AND …. IN (…) LIKE …. % bedeutet 0 oder mehr Zeichen, _genau 1 Z IS NULL IS NOT NULL select * form user_tabels where table_name in (´DEPT´,´ EMP´) select * form emp where sal between 2000 and 5000 Alles Namen die mit S beginnen: select * form emp where ename like ´S%´ Namen mit erster Stelle S und vierter Stelle T ename like ´S__T%` select * form emp where omm is not NULL Holzmann Florian Seite 10 3AHITS 2009/10 DBSY – Datenbankensysteme Logische Operatoren AND OR NOT Vorrangregeln bei Auswertung Arithmetic Concatenation Vergleichsoperatoren: IS NULL, IS NOT NULL, LIKE, IN, NOT IN BETWEEN, NOT BETWEEN NOT AND OR select * from emp where (job = ´Salesman´ or job = ´clerk´) and sal < 1000 In zugrunde liegenden Tabellen sind die Daten NICHT sortiert. Erzwingen einer Ordnung mit: ORDER BY .. ASC {ascending = default Fall} ORDER BY .. DESC {descending = absteigend} select * from emp order by sal desc select ename, sal * 14 “Jahrgangsgehalt” from emp order by „Jahresgehalt“ Sortierung auch nach Spalten-Alias möglich Sortierung nach mehreren Spalten möglich select * form emp order by job, sal Holzmann Florian Seite 11 3AHITS 2009/10 DBSY – Datenbankensysteme ERD (Entity Relationship Diagramm) DEPT # deptno * dname * loc ist kann haben EMP #empon * ename … * comm * deptno Single Row Functions Zur Veränderung von Daten, Eingangsparametern und Rückgabewerten Können geschachtelt sein Können den Datentyp verändern Liefern 1 Ergebnis pro Zeile character number FN general date ConverSion Character Funktionen select select select select select LOWER (´HTL Krems´) form dual //alles kleinbuchstaben UPPER (´HTL Krems´) form dual //alles Großbuchstaben lower(ename) form emp //macht ganze Spalte klein INICAP (´aBcD´) form emp //erstes Zeichen wird Groß * from emp where ename=upper(´Blank`) select CONCAT (ename, job) form emp //2 strings zusammengesetzt select SUBSTR (ename, 1, 3) form emp //Substring select LENGTH (ename) form emp //liefert Länge von Zeichen z select INSTR (´Hallo World`,´W´) form dual //liefert Position select LPAD (sal, 12), ´*´) form emp //Ausgabe: ********2850 select RPAD (sal, 10, ´!´) form emp //Ausgabe: 2850!!!!!! select TRIM (´H´ form ´Hallo´)form dual //Ausgabe: allo TRIM nimmt hinten und vorne das angegebene Zeichen weg. Holzmann Florian Seite 12 3AHITS 2009/10 DBSY – Datenbankensysteme NUMBER-Funktionen ROUND, TRUNC (abschneide), MOD (Modulo-Funktion) select ROUND (45.926, 2) form dual //Rundet die Zahl auf auf 2 Stellen select TRUNC (45.926, 2) form dual //Schneidet die Zahlen weg; bei -1 = 40 select MOD (3650, 700) form dual //Ergebnis: 150 DATUMS-Werte Typ date; Interne Speicherung: Jahrhundert; Jahr; Mon; Tag; St; Min; Sek Default Anzeigeformat: DD-MON-RR RR bis 50…21.Jh; RR darüber … 20.Jh; RR = 43 … 2043 RR = 97 … 1997 select sysdate from dual select sysdate from emp //gibt die Systemdaten aus //“nicht aufgepasst…“ Arithmetik mit date-Funktionen select (sysdate + 3/24) – sysdate form dual // ausgabe: 0,125 NLS… National Language Support select to_char(sysdate+3/24, `YYYY-MON-DD, HH:MI:SS´) form dual //2009-OKT-23, 05:20:30 select ename, (sysdate-hiredate) / 7) “Wochen gehackelt” form emp where deptno int (30, 20) order by deptno, „Wochen gehackelt“ desc Datumsfunktionen MONTHS_BETWEEN; ADD_MONTHS; NEXT_DAY; LAST_DAY; Holzmann Florian Seite 13 3AHITS 2009/10 DBSY – Datenbankensysteme select ROUND (sysdate-3/24) form dual //?? select MONTHS_BETWEEN (sysdate, ´12-NOV-2009´) form dual //?? select to_char(sysdate + 200, ´MON´) form dual //Ausgabe MAI select ADD_MONTHS(sysdate, 3) form dual //gibt 3 Monate hinzu select NEXT_DAY (sysdate, ´MO´) form dual //gibt das Datum des nächsten Tag aus select LAST_DAY (sysdate) from dual //letzten tag von Monat Rundungsfunktion auf Monate: select round(sysdate, ´MONTH´) from dual; //Ausgabe: 1.10.09 select round(sysdate, ´YEAR´) from dual; //Ausgabe: 1.1.10 Datentypconversionen: Implizite Datenumwandlung. Zuweisungen: VARCHAR, CHAR NUMBER DATE Auswertung von Ausdrücken NUMBER VARCHAR DATE Explizipe Datentypumwandlung: To_Date To_Char NUMBER Bsp: To_har (date, ´format´) YYYY DY YEAR DAY MM DD MONTH MON DATE CHARACTER To_Number To_Char select to_char(sysdate, `YEAR: MONTH:DD,DAY………YYYY:MM:DY,HH24:MI:SS AM,DDspth´) from dual //Ausgabe: TWO THOUSAND NINE:OKTOBER:30, FREITAG……..2009:10:FR,13:33:04 PM, THIRTIETH select last_name, to_char(hire_date, ´DD MONTH YYYY´)”Aufnahme” from employees Holzmann Florian Seite 14 3AHITS 2009/10 DBSY – Datenbankensysteme http://192.168.10.210:5560/isqlplus/ Benutzername : hr Passwort: hr Content-Bezeichnung: orcl1 select last_name, to_char(hire_date, 'fmDD MONTH YYYY') from employees desc emp //Listet alle Tabellen auf Beispiele für TO_CHAR(number, ´formatmodel´); 9 …. Ziffer 0 …. erzwingend 0 – Anzahl $ … $ Währung L … Lokales Währungssgn. . … dez. Punk , … 1000er Stelle select to_char(salary, '$99,999.00') from employees Funktionen können geschachtelt werden. !!Sehr beliebt bei Prüfungen!! (TEST) NVL-Funktion: Behandlung von NULL-Werten. Wandelt einen NULL in einen anderen Wert um. select last_name, salary + NVL(commission_pct, 0.0001)*salary from employees NVL2-Funktion select last_name, salary, commission_pct, NVL2(commission_pct, ´Gehalt+Provision´, ´Nur Gehalt´) “Einkommen” from employees NULLIF – Funktion select first_name, length(first_name),last_name, length(last_name) from employees COALESCE-Funktion (zusammenfügen) Vorteil dieser Funktion: Mehrere Alternative Werte möglich. Holzmann Florian Seite 15 3AHITS 2009/10 DBSY – Datenbankensysteme IF-THEN-ELSE Logik select last_name, job_id, salary, case job_id when ‘IT_PROG’ then salary * 1.10 when ‘ST_CLERK’ then salary * 1.15 when ‘SA_REP’ then salary * 1.20 else salary * 0.99 end “Neues Gehalt” from employees DECODE – Funktion select last_name, job_od, salary, DECODE (job_id, ‘IT_PROG’, salary * 1.10, ‘ST_CLERK’, salary * 1.15, ‘SA_REP’ salary * 1.20, salary * 0.99 ) “Neues Gehalt” from employees Kartesisches Produkt - keine JOIN Bedingung Jede Zeile einer Tabelle wird verknüpft mit jeder Zeile einer anderen Tabelle select * from emp, dept Arten von JOINS 1) Oracle spezifisch; Version 8i und früher: o equijoin o nonequijoin o outer join o self join 2) SQL-99 Standart: o cross join o natural join o USING Klausel o full outer join o Bedingungen beim outer join select * from emp, dept where emp.deptno = dept.deptno select emp.ename ||' arbeitet in der Abteilung'||dept.dname from emp, dept where emp.deptno = dept.deptno select e.ename ||' arbeitet in der Abteilung'||d.dname from emp e, dept d where e.deptno = d.deptno Holzmann Florian Seite 16 3AHITS 2009/10 DBSY – Datenbankensysteme Beim JOIN von n Tabellen sind mindestens n-1 JOIN Bedingungen notwendig. OUTER JOINS: select e.ename, e.deptno, d.dname from emp e, dept d where e.deptno(+) = d.deptno //Die Einträge von NULL auf der linken Seite werden zum NULL hinzugezählt select e.ename, e.deptno, e.ename, d.deptno, d.dname from emp e, dept d where e.deptno = d.deptno (+) Bsp: Im Schema SCOTT gibt es für die Abteilung 40 keinen Eintrag in der Tabelle EMP. select ang.ename ||'arbeitet für'|| mgr.ename from emp ang, emp mgr where ang.mgr = mgr.empno order by mgr.ename select * from emp CROSS JOIN dept Natural JOIN: Grundlagen sind alle Spalten in Tabellen die den selben Namen haben. select * from emp NATURAL JOIN dept USING Klausel: Wenn verschiedene Spalten den selben Namen haben aber unterschiedliche Datentypen. select e.ename, d.dname from emp e JOIN dept d USING (deptno) select e.ename, d.dname from emp e JOIN dept d ON (e.deptno = d.deptno) //selbe ausgabe wie oberes INNER JOIN – OUTER JOIN: I: Nur Zeilen die in beiden Tabellen eine ensprechenden Eintrag haben. O: Alle Zeilen vom I + Z von einem LEFT/RIGHT O. select e.ename, e.deptno, d.dname from emp e LEFT OUTER JOIN dept d on (e.deptno = d.deptno) {links sind auch Einträge dabei, die rechts gleich NULL sind} Holzmann Florian Seite 17 3AHITS 2009/10 DBSY – Datenbankensysteme Gruppenfunktionen Operieren auf einer Menge von Zahlen - MAX - MIN - AVG{Durchschnitt, ignoriert NULL} - SUM - COUNT (Liefert Anzahl) - VARIANCE - STD select select select select MAX (sal) from emp MIN(sal) from emp avg(sal), min(sal), max(sal), sum(sal) from emp count (deptno) from emp Tag der offenen Tür: Primär Schlüssel Departments #DEPARTMENT_ID … … … LOCATION_ID Primär Schlüssel Employees #EMPLOYEE_ID … … … DEPARTMENT_ID 1:N Locations #LOCATION_ID … … … COUNTRY_ID Countries #COUNTRY_ID … … … REGION_ID Holzmann Florian Fremd Schlüssel Regions #REGION_ID N:1 Seite 18 3AHITS 2009/10 DBSY – Datenbankensysteme select * from user_tables desc employees desc departments desc locations select * from countries, regions Countries = {Brazil, China, …} Regions = {Europe, Asia, …} Countries X Regions = {(Brazil, Europe), (Brazil, Asia), …} Anzahl Elemnete = |Countries|*|Regions| select * from regions join countries using(region_id) order by region_id, country_id select region_name, country_name, city, department_name from regions natural join countries natural join location natural join department natural join employees order by region_id, country_id, city, department select r.region_name, c.country_name, l.city, d.department_name, e.last_name from regions r, countries c, locations l, departments d, employees e where (r.region_id = c.region_id) and (c.country_id = l.country_id) and (l.location_id = d.location_id) and (d.department_id = e.department_id) order by r.region_name, c.country_name, l.city, d.department_name, e.last_name select r.region_name, c.country_name, l.city, d.department_name, e.last_name, sum(e.salary) from regions r, countries c, locations l, departments d, employees e where (r.region_id = c.region_id) and (c.country_id = l.country_id) and (l.location_id = d.location_id) and (d.department_id = e.department_id) group by r.region_name, c.country_name, l.city order by r.region_name, c.country_name, l.city, d.department_name, e.last_name select avg (commission_pct) from employees Holzmann Florian Seite 19 3AHITS 2009/10 DBSY – Datenbankensysteme select department_id, avg(salary) from employees group by department_id order by department_id Jede Spalte oder jeder Ausdurck in einer Select Liste der keine Aggregatszustand ist, muss in der Group by Klausel vorkommen. Bei Group by Klausel werden Einschränkung nicht mit der group by Klause sonder mit der having Klausel gemacht select department_id, job_id, sum(salary) from employees where sum (salary) > 10000 order by department_id, job_id Neue Stunde select department_id, avg(salary) from employees group by department_id order by department_id Jede Spalte oder jeder Ausdruck der in der Select Klausel vorkommt und keine Aggregatsfunktion ist, muss in der Group by Klausel vorkommen. select department_id, first_name, avg(salary) "Durchschnittskröten" from employees group by department_id, first_name order by "Durchschnittskröten" select first_name from employees order by first_name select first_name, salary, department_id from employees where first_name like 'Dav%' order by first_name select sum(salary)/2 from employees where first_name ='David' and department_id=80 select department_id, avg(salary) from employees having avg(salary) > 8000 group by department_id select max (avg(salary)) from employees group by department_id Holzmann Florian Seite 20 3AHITS 2009/10 DBSY – Datenbankensysteme Subqueries (Unterabfragen) Hauptabfrage: welche Schlinel haben ein größeres Gehalt als das Gehalt von Hr. Abel? Unterabfrage: was verdient Hr. Abel select last_name, salary from employees where salary > (select salary from employees where last_name = 'Abel') select last_name, salary from employees where salary > (select min(salary) from employees) Richtlinien für Subqueries: - immer in ( ) - Oerderlog nicht notwendig - Singelrow operatoren bei singelrowsubquieres Multirow operatorn(IN, ANY, AL) bei Multirowsubquieres Singel-row Operatoren: =, <, >, <=, >=, <> Beispiel: Anzeigen von Gehalt, Job_ID von allen Angestellten für die gilt: 1. Die Job_ID == der Job_ID des Angestellten mit der Nummer 141 und das Gehalt ist größer als das Gehalt vom Angestellten mit der Nummer 143 select last_name, job_id, salary from employees where job_id =( select job_id from employees where employee_id = 141) and salary > (select salary from employees where employee_id = 143) select last_name, salary from employees where salary = (select min(salary) from employees) Heading Klausel select department_id, min(salary) from employees group by department_id select department_id, min(salary) Holzmann Florian Seite 21 3AHITS 2009/10 DBSY – Datenbankensysteme from employees group by department_id having min(salary) > ( select min(salary) from employees where department_id = 50) select employee_id, last_name from employees where job_id = ( select job_id from employees where last_name='Abel' ); Multiple-row Operatioren IN {Gleich mit irgendeinem Elemnt aus der Liste} (select salary from employees where job_id = 'IT_PROG' select last_name, salary from employees where salary < ANY (select salary from employees where job_id = 'IT_PROG') Bei ANY kommen auch Werte die größer als 6000 sind. select last_name, salary from employees where salary < ALL (select salary from employees where job_id = 'IT_PROG') select last_name, salary from employees where salary < IN (select salary from employees where job_id = 'IT_PROG') select ename, job, salary from emp where salary > (select salary from emp where ename like 'BL%' ) or salary < (select salary from emp where ename like 'AL%') Holzmann Florian Seite 22 3AHITS 2009/10 DBSY – Datenbankensysteme Substitutionsvariablen: select … from … where …. employee_id = &nr select * from emp where empno = &nr select &was from &wo where &ausdruck1 = &ausdruck2 define nummer = 7499 select * from emp where empno = &nummer Definierte Variablen nur für aktuelle Session gültig undefine nummer Wiederverwendung von Variablen ohne diese erneut eingeben zu müssen: &&nr select empno, ename, &&spalte from emp order by &spalte BTITLE 'Gehalt!' column ename heading 'Nachname' column sal justify left format $99,990.00 set verify on select empno, ename, &&spalte from emp order by &&spalte select * from emp where sal > &&gehalt Holzmann Florian Seite 23 3AHITS 2009/10 DBSY – Datenbankensysteme DML Operationen Insert, update, delete, merge insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (9999, ‘SAM’, ‘DIRECTOR’, null, sysdate - 25*364, 6000, null, null) insert into emp values (9998, ‘SAM2’, ‘DIRECTOR’, null, sysdate - 25*364, 6000, null, null) insert into emp (empno, ename, job, mgr, hiredate, sal) values (9997, ‘SAM3’, ‘DIRECTOR’, null, sysdate - 25*364, 6000, null, null) insert into emp (empno, ename, job, mgr, hiredate, sal) values (9996, ‘SAM4’, ‘DIRECTOR’, null, to_date(‘FEB 3, 1985’, ‘MON DD, YYYY’), 6000, null, null) create table emp9(no number primary key, n varchar2(20), jgehalt number) insert into emp9(no, n, jgehalt) select empno, ename, sal from emp update emp9 set jgehalt=jgehalt*1.3 where n like ‚A%‘; update emp9 set jgehalt=(select jgehalt from emp9 where n=’SAM’), set n =(select n from emp9 where no>9000) where n like ‚A%‘; delete delete from emp9 delete ist transaktionsgeschütztes löschen delete form emp9 where n like ‚S%‘ Stunde nach Test With Ceck Option insert into (select empno, ename, deptno from emp where deptno < 90 WITH CHECK OPTION) values (9991, ‘TAYLOR1’, 99) Holzmann Florian Seite 24 3AHITS 2009/10 DBSY – Datenbankensysteme Eine Sub quere wird benutzt um die Tabelle und die Spalten für eine DML Operation zu bestimmen. Durch WITH CECK OPTION wird verhindert, dass Zeilen verändert werden, die nicht in der Sub quere vorkommen. EXPLICIT DEFAULT insert into …. values (… , … , DEFAULT, …) Defaul ist NULL oder jender Wert der bei der erzeugung der Tabelle für die Spalte angegehen wurde. Merge Ist ein wichtiger DLL Befehl bei DATA Warehouse Anwendungen (wenige aber sehr Komplexe SQL-Statements) Geringe Bedeutung bei OLTP Anwendungen (Outline Transaction Processing; z.B.: Reisebüro) Intern wird ein Update oder Insert durchgeführt (Update wenn die Zeile schon existiert, sonst Insert) Beispiel: Schema hr Tablle employees select * from employees creat table copy_emp as select * from employees drop table merge into copy_emp c using employees e_id on (c.emplyees_id = e.employees_id) when matched then c.first_name = e.first_name, c.last_name = e.last_name when not matched then insert values (e.employees_id, e.first_name,e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager, e.department_id !!Unvollständig!! desc employees merge into cemp c using emp e on (c.empno = e.empno) when matched then update set c.sal = c.sal * 1.1 when not matched then insert values (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno) commit Holzmann Florian Seite 25 3AHITS 2009/10 DBSY – Datenbankensysteme Transaktionen Benutzer: scott select * form emp //alles delete form emp //Löscht alles select * from emp //zeigt nichts mehr an rollback //macht Sachen rückgängeig select * form emp //alles da delete form emp where empno = 8000 select * from emp Eine Datenbanktransaktion besteht aus - mehrere DML Befehle - ein einzelner DDL Befehl - ein einzelnes DCL Commando (z.B.: Gewährung von Rechten) Beendigung einer Transaktion: - durch COMMIT oder ROLLBACK wird eine Transaktion beendet - durch ein DDL oder DCL Kommando (ein automatisches COMMIT) - Benutzer verlässt die SQL-Sitzung (automatisches COMMIT) - Systemcrash (automatisches ROLLBACK) Vorteile von COMMIT und ROLLBACK - Datenkonsistenz ist gesichert Möglichkeit der Beobachtung von Veränderungen, bevor sie Festgeschrieben werden Gruppierung logisch zusammengehöriger Operationen DELETE… DELETE … SAVEPOINT xyz INSTER … UPDATE … MERGE … SAVEPOINT abc INSERT … DELETE … ROLLBACK ROLLBACK TO SAVEPOINT xyz ROLLBACK TO SAVEPOINT abc Implizites COMMIT: - DDL, DCL - Normales Ende der Client-Verbindung Holzmann Florian Seite 26 3AHITS 2009/10 DBSY – Datenbankensysteme Implizites ROLLBACK: - Systemabsturz - Abnomales Ender der C. Zustand der Daten vor dem COMMIT: - Aktueller User (hat Änderung gemacht) sieht immer aktuelle Daten - Andere User sehen vorherigen Zustand - Auf den veränderten Daten liegt ein LOCK (Sperre auf ZEILENEBENE - Row Level Locking) Zustand der Daten nach dem COMMIT: - Veränderungen werden festgeschrieben (früherer Zustand nur durch Einbringung von BACKUP) - Alle User sehen gleichen Zustand - Sperren auf Rows werden aufgehoben - SAVEPOINTs werden gelöscht Zustand der Daten nach dem ROLLBACK: - Veränderungen sind unwirksam - Sperren auf Rows werden aufgehoben Statementlevel ROLLBACK: - Wenn eine DML Operation fehlschlägt, wird nur diese eine Operation zurückgerollt - Technische Lösung: Oracle Server implementiert ein implizites SAVEPOINT nach jedem SQL-Befehl Lesekonsistenz (read consistency) - Veränderungen durch einen Benutzer stehen nicht im Konflikt mit Veränderungen durch einen anderen Benutzer - „Reader“ müssen nicht auf „Writer“ warten, und umgekehrt - Implementierung der Lesekonsitenz durch ROLLBACK segment Neues Semester… Locking beim Oracle DB Server Verhindert zerstörerische Interaktion zwischen konkurrierenden Transaktionen Ist für den Benutzer transparent (braucht sich nicht darum zu kümmern ) Der niedrigste Grad an Einschränkungen wird ausgewählt (keinesfalls Table-Level-Locking, sondern zumindest Row-Level-Locking) Gilt für die gesamte Zeit der Session Explizites und implizites Locking Beim impliziten Locking gibt es 2 Modi: Exclusive: andere Benutzer werden gesperrt Share: andere Benutzer haben Zugriffrechte Hoher Grad an Gleichzeitigkeit (concurrency): DML: Table Share; Row exclusive Queries: Keine Locks notwendig Holzmann Florian Seite 27 3AHITS 2009/10 DBSY – Datenbankensysteme DDL: Schutz der Objekt-Definitionen Locks gelten bis zum commit / rollback Erzeugung und Verwaltung von DB-Objekten Table Name beginnt mit Buchstaben; Länge 1-30; verwendbare zusätzliche Zeichen: 0-9, _, $, *; darf nicht gleich mit einem anderen Namen im selben Schema sein; keine Oracle reserviertes Schlüsselwort; Erzeuger muss das Systemprivileg CREATE TABLE besitzen Bsp: creat table schule (snr number primary key, sname varchar2(20)); danach Kontrolle: describe schule Arten von Trabellen: o User Tables o Data Dictionary (enthält DB Information = Metadaten; Sammlung von Tabellen, die vom DB-Server verwaltet wird). Anzeige aller Tabellen, die einem Benutzer gehören: select table_name from user_tables; Anzeige UNTERSCHIEDLICHER Objekt-Typen, die einem…: select distinct object_type from user_objects; Anzeige aller Tables, views, synonyms & sequences, die einem …: select * from user_catalog; Wichtige Datentypen: varchar2(25) Zeichenfolge variabler Länge, hier max. 25 char(10) Zeichenfolge fixer Länge, hier 10 number (8,2) Zahl, hier mit gesamt 8 Positionen, 2 Komm date Datum & Uhrzeit: dd-mm-yy, hh:mi:se clob Char. Large Object, bis 4 GB nclob National Char. Large Object, bis 4 GB blob Binary Large Object, bis 4 GB; z.B Video bfile Zeiger auf eine externe OS-Datei, bis 4 GB rowid Eindeutige Kennung einer Zeile, in einer Tabelle long Index Sequence View Synonym date Nur genau auf Sekunden, daher weiter Datumstypen ab Version 9.i timestamp Datum mit Sek. Brucht interval year to month interval day to second Erzeugung einer Tabelle durch Ausführung einer Unterabfrage (subquery). Creat Table emp_Part as select ename, sal from emp; Holzmann Florian Seite 28 3AHITS 2009/10 DBSY – Datenbankensysteme Constraints (Beschränkungen, Einschränkungen, Business Rules, …) Erzwingen Einhaltung von Regeln auf Tabellen-Ebene Verhindern Löschoperationen wenn Abhängigkeiten definiert sind Arten von Constraints o NOT NULL o UNIQUE o PRIMARY KEY o FOREIGN KEY o CHECK Richtlinien bei Verwendung o Namen kann vom User vergeben werden oder vom Server (SYS_C…) create table x(n_pk) number constaint x_prim_key primary key o Contraint Definition kann bie Table-Erzeugung oder später erfolgen o C. D kann auf Spalten oder Tabellenebene erfolgen o Info über Constraints im Data Dictionary Tabellenconstrain creat table schulmesse(s_id number, zeit daste, anzuSuasteller numnr NOT NULL, Constraint s_id_pk Primary Key (s_id)); alter table auftrag_pos add constraint pk_auftrag_pos primary key (auftrag_nr, pos); //Zusammengesetzter Primärschlüssel alter table auftrag_pos add constraint ck_auftr_pos_artikel_text check (artikel_nr is not null or text is not null); Unternehmen Name Grund Datum Standort Eigentümer Produkt - Mitarbeiter Vorn Nachn Plz Ort E-Mail Eintritt Name Herstellungskosten (6,2) Verkaufpreis (6,2) Holzmann Florian Seite 29 3AHITS 2009/10 DBSY – Datenbankensysteme New: Eigenständige Objekte im Datenbankserver Create view jg as select ename, sal * 12 “Jahresgehalt” from emp Select * from jg Man weiß nicht ob jg eine Tabelle oder ein View ist. Verletzung der WHERE-Klausel eienr View WITH CHECK OPTION: Erlaubt einfügeoptionne in die Tabelle über die View nur für Werte die durch die View sichtbar sind. Top N Analyse Was sind die 10 bestbezahlten Lehrer? Select *, rownum form employees In der Psoidospalte rownum select rownum as “Gehaltsposition”, last_name, salary from (select last_name, salary from employees order by salary desc) where rownum <= 10 //Es werden die 10 Mitarbeiter mit dem höchsten Gehalt angezeigt. Weiter Oracel-Datenbankobjekte Seguence CREATE SEQUENCE <sequence_name> INCREMENT BY <integer> START WITH <integer> MAXVALUE <integer> / NOMAXVALUE MINVALUE <integer> /NOMINVALUE CYCLE / NOCYCLE CACHE <#> /NOCACHE ORDER / NOORDER select * form user_sequences //Zeigt alle Sequences an select drpoell.currval from dual select drpoell.nextrval from dual Unterschiedliche Abstände bei den Sequencnummern können entstehen: 1. Wenn ein Rollback passiert von der Datenbank 2. Bei einem Systemchrash 3. Die Sequence wird auch in einer anderen Tabelle benutzt Veränderung einer Sequence alter sequence drpoell increment by 33 Veränderung von Sequenceeigenschaften ist nur für den Eigentümer der Sequence möglich. Holzmann Florian Seite 30 3AHITS 2009/10 DBSY – Datenbankensysteme Entfernen einer Sequence drop sequence drpoell Index - Eigenständiges Objekt - Beschleunigt die Abfrageoperationen - Kann Disk I/O vermindern - Ist unabhängig von Tabellen - Wird automatisch vom Oracel Server verwaltet Wie/ wann wird Index erzeugt? - Automatisch: immer bei Definition eines Primary Key oder Unique Key constraints - Manuell: durch Benutzer, als Ergebnis der Systemanalyse create table abc (n number primary key, anz number, product varchar2(20)) Wann ist ein Index sinnvoll? - Ein Attribut einthält einen weitern Bereich von Werten (z.B. Zahlen 1 bis 100.000; nicht sinnvoll: z.B. Attribut “Geschlecht” hat 50% m/w - Ein Attribut hat sehr viele NULL-Werte (z.B. 20% oder mehr) - Attribute werden häufig in einer WHERE-Klausel verwendet, mit “=” oder “ungleich” Vergleichsoperator - Tablelle ist groß (>= 10.000 Zeilen) und Abfragen liefern nicht mehr als 4% der gesammten Zeilenanzahl - Nicht sinnvoll bei häufing DML Operationen auf der Tabelle (insert, update, delete, merge) Info über Index: Data Dictionary Views USER_INDEXES USER_IND_COLUMNS select * form user_ind_columns select ic.index_name, ic.column_name, ic.column_position, ix.uniqueness from USER_INDEXES ix, USER_IND_COLUMNS ic where ic.index_name = ix.index_name and ic.table_name = ‘EMPLOYEES’ Function based Indexes: Grundlage für Index ist ein AUSDRUCK (nicht ein Attribut alleine) create index lower_dn_idx on departments (lower (department_name)) select * form departments where lower (department_name) = ‘sales’ Entfernen eines Indexes mit DROP INDEX n Vorraussetzung: owner oder Recht DROP ANY INDEX Holzmann Florian Seite 31 3AHITS 2009/10 DBSY – Datenbankensysteme SYNONYME: alternative Name CREATE SYSNONYM … FOR … create synonym ang for employees select * from ang create public synonym alle_ang for employees drop synonym ang Benutzer kann public synonyme nur erstellen aber nicht löschen wegen fehlender Berechtigungen. Steuerung des Benutzerzugriffs Absicherung durch Benutzername + Passwort + Privilegien System Privileges {erlauben Zugriff auf DB} Erstellen/Entfernen neuer Benutzer E./E. Tabellen Sichern von Tabellen create user abxy identified by abxy z.B.: GRANT create table, create view TO scoot Object Privileges {erlauben Veränderungen an DB Objekten} z.B.: GRANT select ON hr.employees TO scott; ROLLEN: Zusammenfassung von mehreren Privilegien z.B.: CREATE ROLE mgr; GRANT create table, create view TO mgr; GRANT mgr TO maier, schmied; WITH GRANT OPTION: Recht, das Privileg auch selbst weiterzugeben z.B.: GRANT select, insert ON hr.departments TO scott WITH GRANT OPTION PUBLIC: für alle zugreifbar z.B.: GRANT select ON hr.department TO PUBLIC desc role_sys_privs select * form role_sys_pribs Information über Privilegien: ROLE_SYS_PRIVS {SystPriv gewährt an Rollen} ROLE_TAB_PRIVS {TablePrivs gewährt an Rollen} USER_ROLE_PRIVS {Rollen, benutzbar für User} USER_TAB_PRIVS_MADE {ObjPrivs, die User Objekten gewährt wurden} USER_TAB_PRIVS_RECD{ObjPriv, die einem User gewährt wurden} USER_COL_PRIVS_MADE {ObjPrivs, die User Spalten gewährt wurden} Holzmann Florian Seite 32 3AHITS 2009/10 DBSY – Datenbankensysteme USER_COL_PRIVS_RECD {ObjPrivs, die einem User für eine Spalte gewährt wurde} USER_SYS_PRIVS {SysPrivs, die einem User gewährt wurden} select * from user_role_privs Enzug von Objekt Privs Revoke … ON … FROM … {user/role/PUBLIC} REVOKE select, insert ON departments FROM scott; DATABASE LINKS Erlaubt LOKAL verbundenen Benutzern den Zugriff auf eine entfernte DB (remote database) select * from emp@HQ_ACME.COM CRATE PUBLIC DATABASE LINK hq.acme.com USING ‘sales’; Holzmann Florian Seite 33 3AHITS 2009/10