Oracle Eigenschaften

Datenbanksysteme Datenbanksysteme I I

„ Dipl.-Inf. , Dipl.-Ing. (FH) Michael Wilhelm

„ Hochschule Harz

„ FB Automatisierung und Informatik

„ mwilhelm@hs-harz.de

„ Raum 2.202

„ Tel. 03943 / 659 338

Inhalt

1. Grundlegende Begriffe der Datenbanktechnologie 2. Datenbankentwurf / Datenmodelle

3. ER-Modell / ER-Diagramm 4. SQL-Sprache

5. Normalisierung

Grundlagen der DBMS

SQL- Grundlagen

Informationsspeicherung und Oracle

„ Jede Behörde, jede Bildungseinrichtung und jedes Unternehmen hat einen gewissen Informationsbedarf

„ Informationen werden in DB erfasst und mittels DBMS verwaltet

– DB: organisierte Sammlung von Daten

– DBMS: Managementsystem für die Daten

„ Oracle ist ein DBMS (Oracle 7 ein relationales, Oracle 8 ein objekt-relationales)

– ein DBMS ist ein Programm, um Daten auf Anforderung in der DB zu speichern, daraus abzurufen und zu bearbeiten

Oracle relational

„ Auf der Basis des Codd‘schen Modelles (relationales Modell)

„ Definition gemäß Oracle- Handbuch

– Ansammlung von Objekten oder Relationen

– Reihe von Operatoren, die auf die Relationen angewendet werden können

– Datenintegrität für Genauigkeit und Konsistenz

– kurz:

„ „eine relationale Datenbank ist eine Reihe von Relationen oder zweidimensionalen Tabellen“

„ Beispiel: Mitarbeiterdatenbank, die in verschiedenen Tabellen unterschiedliche Informationen über die Mitarbeiter speichern:

– Mitarbeitertabelle

– Abteilungstabelle

– Gehaltstabelle

Datenmodelle

„ Datenmodelle werden zu folgenden Zwecken verwendet:

– kommunizieren

– kategorisieren

– beschreiben

– spezifizieren

– untersuchen

– entwickeln

– analysieren

– imitieren

ER-Modell

„ In einem effizienten System sind Daten in bestimmte Kategorien (oder entities) unterteilt.

„ Ein ER-Modell ist eine Darstellung verschiedener entities innerhalb eines Unternehmens und den Beschreibungen zwischen diesen

„ ein ER-Modell wird aus Unternehmensspezifikationen oder Schilderungen abgeleitet und in der Analysephase des

Systementwicklungszyklus erstellt

„ ER-Modelle trennen die für ein Unternehmen erforderlichen Informationen von den in diesem

Unternehmen ausgeführten Aktivitäten - auch wenn sich Aktivitäten ändern, bleiben Art und Struktur der

Informationen weitgehend konstant

ER-Modell und Oracle

„ Oracle unterstützt die ER- Modellierung (Designer)

„ Krähenfuß- Notation

„ es nutzt dabei die folgende Notation (hier m : 1- Beziehung):

ER-Modell

„ Unique identifier: beliebige Kombination von Attributen oder Beziehungen (oder beide), die dazu dienen, das Vorkommen (die Existenz) einer Entität zu unterscheiden. Jede Entität muss eindeutig identifizierbar sein!

„ Jedes Attribut, das Teil der UID ist, wird mit einem Nummernzeichen gekennzeichnet: #

Fachbereich

# Nummer

* Name

° Standort

* = „zwingend“

° = „optional“

Terminologie

„ Zeile oder Tupel

„ Spalte oder Attribut, die/das den Primärschlüssel darstellt

„ Spalte(n) oder Attribut(e), die keine Primärschlüssel darstellen

„ Spalte oder Attribut, das ggf. als Fremdschlüssel fungiert

„ Feld: „Schnittstelle“ zwischen Zeile und Spalte

„ jede Tabelle enthält Daten, die genau eine Entität beschrieben

„ Verknüpfung von Tabellen über Fremdschlüssel (= Spalte oder Gruppe von Spalten, die auf einen Primärschlüssel in derselben oder in einer anderen Tabelle verweisen)

„ diese Terminologie deckt sich mit den Inhalten der Vorlesungsstunde zum relationalen Modell

Oracle Eigenschaften

„ Bietet Zugriffs- und Bearbeitungsmöglichkeiten durch Ausführung von SQL- Anweisungen

„ enthält eine Ansammlung von Tabellen ohne physikalische Zeiger

„ verwendet eine Reihe von Operatoren

„ diese Terminologie deckt sich mit den Inhalten der Vorlesungsstunde zum relationalen Modell

„ der Anwender muss nicht den Zugriffspfad zu Tabellen kennen;

er muss auch nicht wissen, wie die Daten physikalisch angeordnet sind

„ um auf die Datenbank zuzugreifen, führt der Anwender eine SQL- Anweisung aus

– SQL ist ein Standard des ANSI (American National Standards Institute)

– SQL enthält viele Operatoren zum Partitionieren und Kombinieren von Relationen und zur Modifikation der Datenbank

Kommunikation mit dem RDBMS über SQL

SQL> SELECT loc

2 FROM dept; Anweisung geht an Datenbank

LOC

--- NEW YORK RIO

TOKYO

Datenbank

Daten werden angezeigt

Oracle RDBMS

Server

Tabelle A Tabelle B Tabelle C

DataDictionary

Oracle Eigenschaften

„ Relationales Datenbank Managementsystem (RDBMS)

„ SQL

„ PL/SQL

„ Speicherung von Programmeinheiten

„ Sicherheitsfunktionen, die Zugriff und Verwendung der Daten steuern

„ Funktionen zur Gewährleistung der Konsistenz

„ Datenschutzfunktionen

„ Oracle- Anwendungen

– auf demselben Rechner wie der Oracle Server

– alternativ: Benutzer nutzt lokales System; Oracle Server auf remote system (Client/Server)

– Vorteil der Alternative: es kann auf große Rechnerressourcen

zurückgegriffen werden (Bsp.: Fluggesellschaft-Buchungssystem;

Client: Rechner im Reisebüro; Server (remote) verwaltet Flugdaten)

Oracle Eigenschaften

„ Internet- Plattform

„ High Performance Plattform für E- commerce und data warehousing

„ beinhaltet alle Funktionalitäten, die zur Entwicklung, dem

Einsatz und der Verwaltung von Internet- Anwendungen benötigt werden

„ Entwicklungswerkzeuge mit GUI zur Erstellung von

Geschäftsanwendungen einschl. umfangreichen Paketes an Software- Anwendungen für viele Geschäfts- und

Industriebereiche

„ Prozeduren können unter SQL, PL/SQL und

„ besteht aus drei Modulen:

– Browser-basierte Clients zur Verarbeitung der Präsentation (Anzeige)

– Anwendungs-Server zur Ausführung der Unternehmenslogik und Bereitstellung von Präsentationslogik an die Browser-basierten Clients

– Datenbanken zur Ausführung von Datenbank-intensiver Unternehmenslogik und Datenbereitstellung

SQL- Anweisungen

„ Datenabruf

– SELECT

„ Data Manipulation Language (DML)

– INSERT

– UPDATE

– DELETE

„ Data Definition Language (DDL)

– CREATE

– ALTER

– DROP

– RENAME

– TRUNCATE

„ Transaktionssteuerung

– COMMIT

– ROLLBACK

– SAVEPOINT

„ Data Control Language (DCL)

– GRANT

– REVOKE

SQL

„ Oracle SQL entspricht Industriestandards

„ Oracle Corporation garantiert weitere Übereinstimmung mit neu entwickelten Standards durch die aktive

Teilnahme seines Personals an SQL- Standardisierungskommitees

– ANSI (American Standards Institute)

– International Standards Organization (ISO)

– (beide haben SQL als Standardsprache für Datenbanken akzeptiert)

Oracle/SQL-Operationen

„

Tabellen erstellen

„

Daten einfügen

„

Daten abrufen

„

Daten bearbeiten

„

Tabellen ändern

Oracle/SQL-Grundfunktionalität

Oracle: Beispieltabellen

„

EMPLOYEES

–

Daten aller Mitarbeiter einer Firma

„

DEPARTMENTS

–

Daten über die Abteilungen der Firma

„

LOCATIONS

–

Adressen

„

JOB_GRADES

– Gehaltsangaben

Grundlage der SQL-Übungen:

„ ORACLE-Beispieltabellen (s.o.)

„ ORACLE: Professioneller Einstieg in Oracle SQL (Teil I SQL Grundlagen, Band 1) 2001/2002

DBMS: Sprachen, SQL

oder: wie arbeitet man mit Datenbanken?

Datenbanksprachen

„ Storage Structure Language (SSL)

Dateiorganisation (Systemadministrator)

„ Data Definition Language (DDL)

Schema erzeugen (Datenbankadministrator (DBA))

„ View Definition Language (VDL)

Sichten erzeugen (Anwendungsadministrator)

„ Interactive Query Language (IQL) oder Data Manipulation Language (DML)

Daten abfragen und editieren (ausgebildete Endanwender)

„ Data Base Programming Language (DBPL)

Anwendungen erstellen (Programmierer)

„ Schnittstellen der Anwendungen (Menüs, Masken usw.)

Daten abfragen und editieren (Endanwender ohne DB-Kenntnisse)

Structured Query Language (SQL)

„ SQL wird von den meisten relationalen DBMS unterstützt (Oracle, Dbase, Microsoft Access, MySQL, SQL Server, etc.)

„ gewinnt für Client/Server Anwendungen zunehmend an Bedeutung

„ vom ANSI (American National Standard Institute) entwickelt und als Standardsprache für relationale DBMS erklärt

– ABER: viele DBS-Hersteller haben den Sprachumfang von SQL für ihre eigenen Systeme erweitert

„ es gibt also ANSI-SQL

„ und Systemhersteller-spezifische Erweiterungen/Modifikationen von SQL

Structured Query Language (SQL)

„ Relationales Modell

„ deklarativ (spezifizieren, was man sucht, nicht wie es gesucht werden soll)

„ Datenbankschema muss bekannt sein

„ Standard (z. B. SQL2 von 1992, gegenwärtig: SQL3 von 1999)

„ Weiterentwicklung

– SQL3 (Objektorientierung)

– GeoSQL (Geoobjekte, Geometrie und Topologie)

SQL-Beispiel

select NAME, VORNAME from MITARBEITER

where GEHALT > 3000

selektiert die Spalten NAME und VORNAME aus der Tabelle MITARBEITER

für die Mitarbeiter, deren Gehalt höher ist als 3000 (€

brutto im Monat)

andere Sprachen...

„ QUEL (QUEry Language, Ingres) hat gegen SQL „verloren“

„ QBE (Query by Example)

Beispieleinträge in Tabellengerüsten

„ Access

ähnlich wie QBE

„ OO Systeme: kein einheitlicher Sprachstandard

Kommandoeingabe

„ graphische Oberfläche (Befehle werden automatisch erzeugt), besser als ein reiner Kommandoeditor

„ Kommandos als Text eingeben

„ DB-Anwendung: Nutzer wendet SQL an, ohne dies explizit zu wissen...

SQL

„ Tabellen

– erzeugen

– mit Daten füllen

– indizieren

– “updaten”

– abfragen

– ...

„ und in Tabellen

– für Konsistenz sorgen

– für Vollständigkeit sorgen

– für Aktualität sorgen

– ...

Begriffe

„ Data dictionary

„ DDL

„ DML

„ Index

„ Integrity constraints

„ table

„ trigger

„ view

„ role

Data dictionary

„ Enthält alle Informationen über die Art und Weise der

Speicherung der Daten, über die Lokalisierung der Daten und über Zugangsbedingungen/- berechtigungen

„ „tables about tables“

„ Metadaten

DDL

„ Data Definition Language

– SQL

– create, revoke, grant, drop

– SQL statements, die Einsatz finden, um eine Tabelle zu erzeugen, oder zu löschen

– SQL statements, die Zugangsberechtigungen regeln

DML

„ Data Manipulation Language

„ select, insert, delete, update

„ SQL statements, die mit der Manipulation der Datenbank zu tun haben

Index

„ Eine Art „Kopie“ einer Oracle- Tabelle, die in einer sortierten Form vorgehalten wird

„ Indizes erlauben den schnellen, gezielten Zugriff auf Daten in Tabellen

„ Vergleich: das Inhaltsverzeichnis eines Buches; Telefonbuch; ...

Integrity constraints

„ Regeln, die die „Integrität“ der Datenbank sichern. Unter

„Integrität“ werden DB- Eigenschaften wie Vollständigkeit, logische Konsistenz, Einhaltung von Wertebereichen, etc.

verstanden

„ Beispiel: in einer Kunden- DB MUSS eine Kunde eine ID haben, sonst liefert das DBMS eine Fehlermeldung; hinter dieser

Fehlermeldung verbirgt sich eine entsprechend implementierte Regel.

table

„ Zentraler Bestandteil jedes relationalen DBMS: die Tabelle (“a database object that holds your data”)

„ Informationen über die Tabellen werden im Oracle data dictionary vorgehalten

„ unter Nutzung der Informationen im data dictionary wird das Management der in den Tabellen gespeicherten Daten möglich

„ Basis: das relationale Modell! Entwurf: über das konzeptionelle Datenmodell, z. B. mit ER- Diagrammen!

trigger

„ Trigger sind Programme, die Bestandteil der Datenbank sind

„ diese Programme werden bei Eintritt bestimmter „events“

getriggert („angestoßen“, sie „feuern“)

„ Beispiel: das Einfügen einer neuen Zeile in einer Tabelle kann ein „event“ sein, das die Auslösung eines triggers zur Folge hat, der z. B. Bedingungen prüft, unter denen eine neue Zeile

eingefügt werden darf

view

„ Views ermöglichen die Sicht eines DBMS- Nutzers auf eine oder mehrere Tabellen einer Datenbank

„ views werden mit SQL- Skripten generiert, die in der DB gespeichert wird

„ wenn auf einen view zugegriffen wird, wird das SQL statement ausgeführt; die Ergebnisse des statements werden dem Nutzer angezeigt

role

„ Eine Rolle beschreibt mehrere Privilegien eines Nutzers

„ Privilegen werden einer Rolle zugeordnet:

– „Once privileges are granted to a role, a user inherits the role‘s privileges by becoming a membr of that role“

Tabellen

„ „every piece of information that gets loaded into an Oracle database must be placed inside an Oracle table“

„ zu unterscheiden:

– tables: alle Tabellen zur Speicherung der Informationen

– data dictionary tables: alle Tabellen, die Informationen über die sonstigen Oracle Tabellen enthalten („tables about tables“)

„ Tabellen: Zeilen, Spalten

– Zeilen: Identifkator MUSS eindeutig sein; hat einen Typ (z. B.

varchar2, date, number)

– Oracle ermöglicht die Zuordnung von integrity rules und triggers zu Tabellen

Tabellen erzeugen

„ Create

SQL> CREATE TABLE students

Student_ID INTEGER PRIMARY KEY Student_name VARCHAR2 (25)

Student_subject VARCHAR2 (30) SQL> /

Tabellen aus existierenden Tabellen erzeugen (1)

„ Create table as

SQL> CREATE TABLE mass_newhire

2 AS select *

3 from newhire

4* where state_cd = 'MA' SQL> /

Table created

Tabellen aus existierenden Tabellen erzeugen (2)

„ Die Originaltabelle

SQL> select * from newhire;

LNAME ST HIRE_DATE SALARY --- -- --- --- tom MA 15-AUG-01 20000 dick NJ 02-FEB-01 30000 harry NJ 19-MAY-01 35000 lisa MA 08-MAY-01 55000 alastair TX 12-DEC-01 60000

Tabellen aus existierenden Tabellen erzeugen (3)

„ Die Originaltabelle

SQL> select * from mass_newhire;

LNAME ST HIRE_DATE SALARY --- -- --- --- tom MA 15-AUG-01 20000 lisa MA 08-MAY-01 55000

SQL> CREATE TABLE mass_newhire

2 AS select *

3 from newhire

4* where state_cd = 'MA'

SQL> /

Index

„ Effiziente Suche nach Informationen in komplexen

Datenbanken, die aus einer Vielzahl von Tabellen aufgebaut sind

„ Beispiel:

– Tabelle „ALPHABET“ mit 26 Spalten (A - Z) mit 800000 Zeilen

– Es wird ein Index auf die ersten 3 Spalten (ABC) gesetzt

– Name des Index: ABC

– Aufgabe: „Finde alle Zeilen, in denen in der Spalte A das Wort

„Soup“ steht

Index

„ Effiziente Suche nach Informationen in komplexen

Datenbanken, die aus einer Vielzahl von Tabellen aufgebaut sind

„ Beispiel:

– Tabelle „ALPHABET“ mit 26 Spalten (A - Z) mit 800000 Zeilen

– Es wird ein Index auf die ersten 3 Spalten (ABC) gesetzt

– Name des Index: ABC

– Aufgabe: „Finde alle Zeilen, in denen in der Spalte A das Wort

„Soup“ steht

– 1. Möglichkeit

„ gesamte Tabelle durchsuchen, alle Zeilen, alle Spalten

– 2. Möglichkeit

„ nur die „interessierenden“ Spalten durchsuchen, oder vorhandene Indizes, z. B. ABC:

– 800000 Zeilen über 26 Spalten prüfen O D E R

– 800000 Zelen nur über die Spalten A, B und C prüfen

Primärschlüssel (primary key)

„ Jede Tabelle sollte einen Primärschlüssel aufweisen; dies gewährleistet die eindeutige Identifizierbarkeit von

Tabellenattributen (bzw. die Vermeidung von redundanten Attributen, da ein Attribut nur einen Primärschlüssel haben kann)

„ Beispiel: Kundendatei

– Attribute: Kunde_ID, Kunde_Vorname, Kunde_Nachname, Kunden_Straße, Kunde_Stadt, Kunde_Telefon

– Jeder Kunde hat eine eindeutige Identifikationsnummer

(Kunde_ID); Kunde_ID ist eindeutig und kommt nur einmal in der Tabelle vor: dieses Attribut ist als Primärschlüsselattribut geeignet

– Vorname, Nachname, Straße, etc. sind denkbar ungeeignet, da sie redundant in der Kundendatei vorkommen können und daher nicht mehr Eindeutigkeit gewährleistet ist

SELECT [DISTINCT|ALL] {*, column [alias], expr …}

FROM table, …

[WHERE condition(s)]

[GROUP BY expr [, expr] …]

[HAVING condition(s)]

[ORDER BY {column, expr, alias} [ASC|DESC]];

Oracle/SQL-Aufbau

SELECT Liste mit einer oder mehreren Spalten

DISTINCT Schlüsselwort, um vorkommende Zeilen auszuschließen

* Auswahl aller Spalten aller in der FROM-Klausel aufgeführten Tabellen, Views oder Snapshots column Auswahl der benannten Spalte

alias Ausgewählte Spalten erhalten andere Überschriften expr Zeichenkette oder errechneter Ausdruck

FROM table Schlüsselwort zur Angabe der Tabelle, View, oder des Snapshot mit den Spalten

WHERE Klausel zur Einschränkung der auszuwählenden Zeilen entsprechend einer Bedingung

GROUP BY zur Gruppierung ausgewählter Zeilen und zur Rückgabe einer zusammenfassenden Zeile

HAVING gibt an, welche durch die GROUP-BY-Klausel definierten Zeilengruppen von der Abfrage zurückgegeben werden ORDER BY Reihenfolge zur Anzeige der abgerufenen Zeilen.

ASC: aufsteigend (Standard), DESC: absteigend

Oracle/SQL-Syntax

Folgende einfache Regeln und Richtlinien müssen einhalten

werden, damit gültige Anweisungen konstruiert werden, die leicht lesbar und einfach zu editieren sind:

Oracle/SQL-Syntax

„ SQL-Anweisungen unterscheiden keine Groß- und Kleinbuchstaben.

„ Klauseln stehen gewöhnlich zur leichteren Lesbarkeit und Bearbeitung in separaten Zeilen.

„ Schlüsselwörter werden gewöhnlich in Großbuchstaben angegeben;

alle anderen Wörter, wie z.B. Tabellennamen und Spalten, werden in Kleinbuchstaben geschrieben.

Erste Schritte: Select from where

SELECT ... FROM

SQL> select * from newhire;

SELECT ... FROM ... WHERE SQL> select *

from newhire

where state_cd = 'MA';

Select .. from .. where

SELECT

- Auswahl der Information (bzw. die Namen der Spalten, die die relevante Information enthalten)

FROM

- wo ist die Information zu finden (bzw. der Name der Relation/Tabelle, in der die relevante Information zu finden ist)

WHERE

- Definition von Bedingungen oder Selektionskriterien;

hier kann auch spezifiziert werden, wie zwei Tabellen miteinander verknüpfbar sind

GROUP BY

- wie sollen die Daten gruppiert werden?

ORDER BY

- wie sollen die Daten sortiert werden?

Select from where

SELECT

KNr, Kursbezeichnung FROM

Kurse WHERE

Kursbezeichnung = 'Informatik' GROUP BY

KNr;

KNr Kursbezeichnung Informatik

Steuerungstechnik Informatik Informatik Informatik Elektrotechnik 255

257 345 348 401 444

Oracle: SQL*Plus

„ Aufruf über Menü „Oracle9i“

- Benutzerkennung - Password

- Host-Zeichenfolge

„ Zugriff von der Befehlszeile:

SQL>

1 2 3

– Aufruf aus dem Menü

– Beenden über „exit“

– DDL und DML sofort einsetzbar; gesamter Sprachumfang von SQL*Plus

}

Oracle: SQL*Plus

„ Jede Tabelle hat einen eindeutigen Namen

„ alle Spalten haben eindeutige Namen

„ für jede Spalte ist ein Datentyp anzugeben

„ mit jedem Datentyp sind bestimmte Regeln verbunden, die Datenbank-Engine von Oracle9i unterstützt diese Regeln

„ Datentypen

„ char(size) Zeichenfolgen mit fixer Länge

„ varchar(size) Zeichenfolgen mit variabler Länge

„ number(l,d) Numerische Daten (l = Länge, d = Dezimalstellen)

„ blob großes Binärobjekt (bis zu 4GB)

„ raw(size) Datentyp mit variabler Länge; für Binärdaten oder Byte-strings wie Grafiken, sounds, Dokumente, etc.

„ date Datumsangaben

„ long Zeichendaten mit variabler Länge; Spalten, die als long definiert sind können bis zu 2GB aufnehmen

„Übersetzen“ Sie in SQL:

Ö Zeige mir alle Daten der Tabelle „Mitarbeiter“

Ö Zeige mir den Mitarbeiter „Müller“ in der Tabelle

`Mitarbeiter` an

Ö Zeige mir aus der Tabelle „Kursteilnehmer“ alle Personen, die den Kurs „Datenbanken“ belegt haben