TDO - Skript - Datenbanken - SQL - ERD - Access RELATIONALE DATENBANKEN RELATIONAL DATABASE bedeutet eine Beziehung zwischen Tabellen. Mita rbe ite r (=T abellenname) Spalte/column: Eigenschaften (Attribut/attribute) Mitarbeiter# Name Job Mitarb# des Vorgesetzten Abteilungs# 120 19 1 Mayer Knoll Messias Analytiker Projektleiter Boss NULL 1030 1030 007 . . . . . . . . . . 19 1 Zeile/row: Datensatz row=record Attributswert Relation: = Beziehung/Zusammenhang zwischen Objekten Funktion: = eindeutige Relation Entität (Entity): = wenn eine Zeile (=Datensatz) eindeutig identifizierbar ist. z.B.: Buch von Philipp Entity Type: = ein Überbegriff z.B.: Buch Rembold HIERARCHISCHE DATENBANK keine Verknüpfungen möglich (Information, Struktur streng hierarchisch) NETZWERKMODELL Verknüpfung der einzelnen Objekte möglich, Ziel kann jedoch über verschiedene Verknüpfungen erreicht werden. by K. Zimmermann Seite: 1 1995/96 TDO - Skript SQL Structured Query Language (strukturierte Abfrage Sprache) SQL = normierte Abfragesprache ISQL (Interactiv SQL): Abfrage wird eingegeben, Computer gibt das Ergebnis aus. Nächste Abfrage wird eingegeben, ... usw. ESQL (Embedded SQL): hier wird programmiert z.B.: im C-Programm wird eine SQL-Anweisung ausgeführt embedded ... eingebettet SQL-Anweisungen unterteilt in 3 Gruppen: •) DML - data manipulating language (für Datensätze) select update delete insert selektieren ändern löschen einfügen •) DDL - data definition language (für Tabellen) create drop alter erstellen löschen ändern (löscht Tabelle samt Inhalt) •) DCL - data control language (Benutzerrechte) grant revoke select was from woher where Bedingung erteilen entziehen (=Attribut, Attribut, ...) (=Tabellenname, Tab.name, ...) Bsp: select * from Schüler where Alter>15 by K. Zimmermann (* gibt den ganzen Datensatz aus) ( Tabelle Schüler) (es sollen nur die Schüler ausgegeben werden, die älter als 15 sind) Seite: 2 1995/96 TDO - Skript OPERATOREN < > = <> (!=) and or not in like is Negationen: Wichtig: 0 <= >= between z.B.: not in, not between, is not, ... ... Ziffer NULL ... nix % _ Bsp: ... beliebig viele Zeichen ... genau ein Zeichen Name derjenigen, deren Gehalt zwischen 1000 und 2000 liegt. select ename, sal from emp where sal between 1000 and 2000 Bsp: Welche Personen haben den Job CLERK, SALESMAN oder ANALYST. select ename, job from emp where job in ('CLERK', 'SALESMAN', 'ANALYST') Bsp: Alle Namen, die mit A anfangen. select ename from emp where ename like 'A%' Bsp: Namen derjenigen, die keine Provision bekommen. select ename from emp where comm is NULL by K. Zimmermann Seite: 3 1995/96 TDO - Skript ORDER BY Bsp: Ausgabe der Angestelltennummer in der Abteilung 10, sortiert nach Namen. 1 2 select empno, ename from emp where deptno=10 order by ename (oder order by 2) Nach order by können mehrere Kriterien angegeben werden, z.B.: order by No, Gericht No Gericht 1411 1411 Spaghetti Spinat ASC ... ascending (aufsteigend) DESC ... descending (absteigend) ÄNDERN VON "ÜBERSCHRIFTEN" select empno Mitarbeiternr, ename Name from emp MITARBEITERNR NAME 7396 7499 SMITH ALLEN Mitarbeiternr → "Mitarbeiternr" → MITARBEITERNR Mitarbeiternr AUSDRÜCKE UND FUNKTIONEN + - * / || Grundrechnungsarten Zusammenhängen von Zeichenfolgen ABS (<numerischer Ausdr.>) Absolutbetrag SIGN (<numerischer Ausdr.>) Vorzeichen LENGTH (<String>) Länge SUBSTR (<String>, von, Länge) Teilstring von einer Zeichenkette NVL (<Ausdruck>, Ersatz) Konvertierung von NULL-Werten by K. Zimmermann Seite: 4 NVL ... NULL-value 1995/96 TDO - Skript Bsp: Alle Namen, die an der 3. Stelle ein A haben. select ename from emp where substr (ename, 3, 1)='A' Bsp: ENAME BLAKE CLARK ADAMS Ausgabe vom Namen und Gehalt+Provision. select ename, sal+nvl (comm, 0) from emp ENAME SMITH ALLEN WARD JONES MARTIN SAL+NVL (COMM, 0) 800 1900 1750 2975 2650 . . . . . . SAL+COMM 1900 1750 2650 . . . NULL wird für diese Berechnung zu 0 konvertiert, um sie dann mit sal zu addieren. Bsp: Zusammenhängen von Name und Beruf aus der Abteilung 30. select ename || '-' || job "NAME-BERUF" from emp where deptno=30 NAME-BERUF ALLEN-SALESMAN WARD-SALESMAN . . . GRUPPENFUNKTIONEN I) SUM Jahresgehalt pro Person? select sal*12 from emp SAL*12 9600 . Jahresgehalt aller. select sum (sal*12) from emp SUM (SAL*12) 348300 II) MAX max (<Ausdruck>) III) MIN min (<Ausdruck>) IV) AVG avg (<Ausdruck>) Bei SUM, MAX, MIN und AVG werden NULL-Werte ignoriert. by K. Zimmermann Seite: 5 1995/96 TDO - Skript V) COUNT a) COUNT (*) Zählt alle vorhandenen Datensätze. select count (*) from emp COUNT (*) 14 b) COUNT (<Ausdruck>) Abzählen von Datensätzen ungleich NULL. select count (mgr) from emp COUNT (MGR) 13 c) COUNT (distinct <Ausdruck>) select count (distinct job) from emp COUNT (DISTINCT JOB) 5 GROUP BY Bsp: Durchschnittsgehalt pro Filiale. select distinct deptno from emp select avg (sal) from emp where deptno=10 DISTINCT DEPTNO 10 20 30 AVG (SAL) 2916,67 select avg (sal) from emp where deptno=20 . . . einfacher: select deptno, avg (sal) from emp group by deptno DEPTNO 10 20 30 AVG (SAL) 2916,67 2175 1566,67 HAVING Nach der group by - Klausel kann kein where benutzt werden. SQL bietet dafür den Filter having an. Having definiert eine Bedingung, welche sich auf das Ergebnis der Gruppierung bezieht. Es ist somit möglich, je Gruppe die Entscheidung zu treffen, ob diese in die Ausgabe mit aufgenommen werden soll oder nicht. In having können Gruppenfunktionen (min, max, ...) benutzt werden, die in where nicht zulässig sind. by K. Zimmermann Seite: 6 1995/96 TDO - Skript JOINS = Abfrage mehrerer Tabellen EMP DEPT empno, ename, deptno, ... select ... from emp, dept → ename, deptno, ... KARTESISCHES PRODUKT wird gebildet (= 14*4 Datensätze) select emp.deptno from ... Tabellenname (von der das Attribut angegeben wird) INNER JOIN = zur Vermeidung des kartesischen Produkts select ... from emp, dept where emp.deptno = dept.deptno → in diesem Beispiel wären es nur noch 14 Datensätze OUTER JOIN hier werden Datensätze miteinbezogen, die in einer anderen Tabelle nicht vorkommen (wird selten benutzt). select ... from emp, dept where emp.deptno (+) = dept.deptno → Datensatz. Filiale 40 hat in der Tabelle EMP keinen entsprechenden Durch das Anführen von (+), werden bei dieser Tabelle Datensätze mit dem Wert NULL generiert. Die Benennung des Joins erfolgt dadurch, ob links (LEFT OUTER JOIN) oder rechts (RIGHT OUTER JOIN) NULL-Werte angefügt werden (emp.deptno (+) = dept.deptno → LOJ / dept.deptno = emp.deptno (+) → ROJ) EQUI JOIN = die Verknüpfung innerhalb einer Tabelle select ... from emp E1, emp E2 where E1.deptno = E2.deptno by K. Zimmermann → → E1 und E2 = Synonyme Verknüpfung mit sich selbst (um z.B. ein Liste aller Mitarbeiter und deren Vorgesetzten auszugeben) Seite: 7 1995/96 TDO - Skript SUBSELECTS man darf selects miteinander verschachteln select ename, job, deptno from emp where job = (select job from emp where ename = 'JONES') Ein SUBSELECT kann auch nach group by verwendet werden ( having avg (sal) > (select ...) ) Operatoren die nur ein Ergebnis zurückliefern: = > < >= <= <> (!=) Operatoren, bei denen ein subselect mehrere Ergebniszeilen zurückliefert: any all in = any ( in ) != all ( not in ) > any ( > min ) < any ( < max ) > all ( > max ) VIEW = Ansicht, Lupe od. Fenster einer oder mehrerer Tabellen, für vollständige bzw. Teil-Ansichten In der VIEW sind keine Datensätze; sie besteht nur aus einem select - Statement. Werden in der/den zugehörigen Tabelle(n) Daten verändert, so ändert sich auch die VIEW. Soll die Originaltabelle geschützt werden oder soll verhindert werden Daten zu Manipulieren und zu Ändern, so wird eine VIEW angelegt. Vorteil einer View ist, daß man wichtige Daten die öfters benötigt werden zusmmenfassen kann. Sollen einige Daten für andere Benutzer gesperrt bleiben (z.B.: Gehaltsdaten), so kann dies mittels eines selects erfolgen. Tabelle A Tabelle B A1 A2 A3 A4 create select from where View Z B1 B2 B3 B4 B5 A1 B2 B4 view Z as A1, B2, B4 A, B A4=B5 order by darf nicht bei der Erstellung einer View vorkommen. Wird im select - Statement ein Berechnungsausdruck verwendet, so muß als Attribut ein Name gewählt werden [ create view X (maxsal, ...) as select max(sal), ... from ... ]. Ansonst wird mit einer View wie mit einer Tabelle gearbeitet: löschen einer View: by K. Zimmermann drop select * from Z view Z Seite: 8 1995/96 TDO - Skript TABLE = Tabelle, in der die Daten gespeichert sind erstellen einer Tabelle: create table name löschen einer Tabelle: drop Bsp: table name Erstellen einer Tabelle mit verschiedenen Attributen create table allgemein (MITNR number (4) not null, NAME char (12), DATUM date, GEHALT number (7,2)) Als Attribute gelten: char (n) Zeichenfolge mit max. Länge n number (n,d) num. Wert mit gesamt n Stellen und d Nachkommastellen date Datum not null bedeutet, daß in dem Feld immer ein Wert stehen muß der ungleich null ist. Soll eine vorhandene Tabelle erweitert werden, so muß alter verwendet werden: alter table allgemein add (PROZENT number (6,2)) Unsere Tabelle sieht jetzt so aus: ALLGEMEIN MITNR NAME DATUM GEHALT PROZENT Es gibt verschiedene Möglichkeiten, um Datensätze in einer Tabelle anzulegen: I) insert ... into insert into allgemein values (12, 'JAMES', '24-APR-93', 1234.76, 3.25) Eintragen in bestimmte Felder: insert into allgemein (MITNR, NAME, GEHALT) ... ALLGEMEIN MITNR NAME DATUM GEHALT PROZENT 12 JAMES 24.04.93 1234.76 3.25 by K. Zimmermann Seite: 9 1995/96 TDO - Skript Bei insert into werden einzelne Datensätze angelegt. II) insert ... select insert into allgemein (MITNR, NAME, DATUM, GEHALT) select empno, ename, hiredate, sal from emp Bei insert select werden Datensätze von vorhandenen Tabellen übernommen. Zu beachten ist die richtige Reihenfolge der Attribute und die Übereinstimmung des Datentyps. Es können nicht nur neue Datensätze angelegt werden, sondern auch vorhandene nachträglich verändert werden: update allgemein set gehalt = 5000 where prozent = 3.25 Wie bei insert select kann auch bei update subselects verwendet werden. Hier besteht die Möglichkeit, daß nach set ein subselect folgt, um einen variablen Wert einzufügen. SYNONYME Synonyme werden verwendet, um eigene Tabellen mit gekürztem Tabellennamen darzustellen, oder um sie anderen Benutzern zugänglich zu machen (inklusive Berechtigung). erstellen eines Synonyms: create (public) synonym name for username.tabellenname löschen eines Synonyms: drop public .. allgemein zugänglich synonym name INDEX In einer SQL - Datenbank werden die einzelnen Datensätze in undefinierter Reihenfolge gespeichert. Wird ein Datensatz gesucht, so muß die Tabelle sequentiell durchsucht werden. Bei größeren u/o miteinander verknüpften Tabellen können dadurch längere Wartezeiten entstehen. Ein Nachteil des Verfahrens ist der Mehraufwand, da neben der Tabelle auch die Indexdatei gewartet werden muß. erstellen eines Indexes: create (unique) index name on tablename (columnname, ... asc/desc ) löschen eines Indexes: drop index name TRANSAKTIONEN In (ORACLE - ) SQL werden Änderungen temporär ausgeführt. Dies hat den Vorteil, daß z.B. bei einem Stromausfall die Hauptdatenbank nicht verändert wird. Will man nach einer Transaktion auf die Hauptdatenbank schreiben, so muß COMMIT eingegeben werden. Ist man mit den Änderungen nicht zufrieden, so muß ROLLBACK eingegeben werden. Dieser Befehl erlaubt es, alle Änderungen bis zum letzten COMMIT zurückzunehmen. by K. Zimmermann Seite: 10 1995/96 TDO - Skript Bis zu dieser Seite ist der Lernstoff im Skript Einführung in SQL v3.0 nachzulesen. ENTITY RELATIONSHIP DIAGRAMM ERD = die graphische Darstellung der Beziehungen zwischen den Tabellen Die Beziehungen werden im Uhrzeigersinn betrachtet. Bsp: I) II) Lehrer unterrichtet min. 1 und max. mehrere Schüler Schüler wird unterrichtet von min. 1 und max. mehreren Lehrern Bezeichnung immer singular: der Lehrer, der Schüler, ... ENTITIES Beziehungen zwischen Entities: 1 : 1 Schule - Direktor Ehemann - Ehefrau 1 : m Direktor - Lehrer Mutter - Kind m : n Lehrer - Schüler I) FUNDAMENTALE ENTITÄT Die Entität hat für sich betrachtet eine Bedeutung, ohne Abhängigkeit von einer anderen Entität. II) ATTRIBUTIVE ENTITÄT Sie ergänzt eine fundamentale Entität. III) ASSOZIATIVE ENTITÄT by K. Zimmermann Seite: 11 1995/96 TDO - Skript Sie beschreibt die Beziehung zwischen den Entitäten (m : n Beziehungen auflösen). Bsp: ___ ... Primary Key m : n Beziehungen will man im ERD nicht haben → assoziative Entität Regel: -) m : n Beziehungen müssen im ERD aufgelöst werden -) Relationen haben immer einen Namen SCHLÜSSEL (key) I) PRIMÄR SCHLÜSSEL (primary key) ist ein Schlüssel (Attribut), der den Datensatz eindeutig identifiziert "not null" ist nicht erlaubt/möglich II) SEKUNDÄR SCHLÜSSEL (secondary key) muß den Datensatz nicht mehr eindeutig identifizieren, hilft ihn aber schneller zu finden III) SCHLÜSSELKANDITAT (kanditatkey) wenn mehrere Attribute als Primärschlüssel benutzt werden können Mitarbeiter MNr 4711 4712 ... MName SCRO PIFF ... by K. Zimmermann MAdr Ungarg. 69 Ungarg. 69 ... Projekt PNr 3749 3867 ... Seite: 12 PName ... Internet ... ISDN ... Projektbeteiligung MNr 4711 4711 4712 PNr 3749 3867 3867 Dauer 300 20 90 1995/96 TDO - Skript Regel: -) primary keys müssen gesucht werden (und werden im ERD immer unterstrichen) NORMALISIERUNG = wie ERD, jedoch ohne graphische Darstellung Die Aufgabe der Normalisierung ist es, Denundanzen (Datenüberfluß) und die damit verbundenen Probleme aus der Datenbank fernzuhalten. MNr 4712 4712 1. Aussage: 2. 3. 4. . . . 12. Aussage: MName PIFF PIFF MAdr ... ... Ort WIEN WIEN PBez Internet ISDN → Redundanz, wenn sich z.B. die Madr ändert Ein Mitarbeiter hat einen Namen Ein Mitarbeiter hat einen Wohnort Ein Mitarbeiter ist in einer Abteilung tätig Ein Mitarbeiter arbeitet an mehreren Produkten Ein Mitarbeiter arbeitet eine bestimmte Zeit am Produkt Jede Abteilung trägt eine eigene Bezeichnung Jedes Produkt trägt eine eigene Bezeichnung In einer Abteilung sind mehrere Mitarbeiter tätig An einem Produkt arbeiten mehrere Mitarbeiter Jeder Mitarbeiter hat eine Mitarbeiternummer Jede Abteilung hat eine Abteilungsnummer Jedes Produkt hat eine Produktnummer → Tabelle: MName Motti Wohnort Zürich Abt. Physik Prodt. A, B Zeit 60, 40 MNr 101 PNr 11, 12 Abtnr 1 Regel: 1. Normalform (1. NF) Kreuzungspunkt zwischen Spalten und Zeile darf max. 1 Wert aufweisen. Eine Relation ist in 1. NF, wenn jedes Attribut der Relation vom Schlüssel funktional abhängig ist. D.h. jedes Attribut hat zu jeden Schlüsselwert nur einen bestimmten Attributswert, man sagt auch: jedes Attribut ist auch atomar (= elementar). Es kann zu jedem Schlüsselwert genau ein Attributswert genannt werden (kann auch leer sein). Attribute Relation R (A1, A2, A3, ...) = Zuordnung 1. NF R (S1, S2, A1, A2, A3, ...) Maßnahmen für die 1. NF Schlüsselkanditat finden; bei Kreuzungspunkt nur einen Eintrag by K. Zimmermann Seite: 13 1995/96 TDO - Skript by K. Zimmermann Seite: 14 1995/96