externe Ebene

Kapitel 5

Datendefinition im Relationenmodell DDL in Oracle

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

1

Daten-Definitionssprache (DDL)

Zusammenhänge: Realwelt -> ERM -> Relationen-Modell -> normalisiertes Relationen-Modell (kommt später – Wir gehen im Praktikum davon aus, dass die Relationen normalisierte Relationen sind.) -> Formulierung in DDL zur Definition der Objekttypen im konkreten Datenmodell bzw. Datenbanksystem (im Relationenmodell: Relationen) Nicht Einfügung von Daten!

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

2

3-Schichten-Architektur (ANSI / SPARC-Modell)

Was ist ein Schema? Im konkreten Datenbanksystem unterscheiden wir 3 Ebenen: Anwendung A

Anwendung B

externes Schema 1

Anwendung C

externes Schema 2

.........

konzeptionelles Schema

internes / physisches Schema

Anwendung Z

externes Schema n

externe Ebene Datensicht für einzelne User konzeptionelle Ebene (Analyse- /DesignModell der Gesamtheit der Daten) Sicht auf die Gesamtheit aller Daten und ihre Beziehungen untereinander interne / physische Ebene physiche Speicherstrukturen

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

3

Bedeutung der 3 Ebenen

- Änderungen auf einer Ebene können vorgenommen werden, ohne dass eine benachbarte Ebene davon berührt ist. => Verarbeitungsprogramme sind physisch als auch logisch datenunabhängig (mit Einschränkungen). d. h. Änderungen in Schemas der - physischen, - konzeptuellen, - externen Ebene haben keine Auswirkungen auf die Verarbeitungsprogramme (mit Einschränkungen: z. B. Löschung eines Attributs hat evtl. Auswirkungen, z. B. Hinzufügen eines Attributs hat keine Auswirkungen.)

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

4

Daten-Definitionssprache (DDL)

•

DDL Teil von SQL (Structured Query Language), der Standardsprache im Bereich relationale DBMS :

•

DDL dient in relationalen Datenbanksystemen der Definition von - Sichten (externe Ebene): Anweisungen: create view, drop view - Relationen (konzeptuelle Ebene): Anweisungen: create table, alter table, drop table … - internen Strukturen (Interne Ebene): Anweisungen: create index, alter index, drop index …

•

Mit CASE-Tools: Automatische Ableitung vom eERD bis zu DDL-Anweisungen. Aber: Prüfung auf Normalisierung muss manuell durchgeführt werden.

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

5

DDL - Grundstruktur

create table Basisrelationenname ( Attribut1 Wertebereich1 [not null], ... AttributN WertebereichN [not null], [primary key (AttributH, …, AttributJ),] [{foreign key (AttributF, …, AttributG) references Basisrelationenname [(AttributD, …, AttributE)]}] ) anzugeben, falls andere Attributnamen als AttributF, ... , AttributG

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

6

DDL in Oracle

Kann erst definiert werden, nachdem Produkt und Markt definiert wurden

!

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

!

7

Erlaubte Wertebereiche in ANSI-SQL

nicht in Oracle! In Oracle: Datum und Zeit! Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

8

Weitere erlaubte Wertebereiche in create table in Oracle NUMBER(p,s): Fest- und Fließkommazahlen mit bis zu 38 Stellen. p: Präzision = Anzahl der Stellen (max. 38) s: Skalierung = Anzahl Nachkommastellen (-84 bis 127) - ohne s bedeutet: s=0 ~ Integertyp - ohne Angabe, ohne Klammern: E-Schreibweise (1E-84 bis 9.99E125) CHAR(n): Zeichenketten fester Länge (n max. 2000) - CHAR ~ CHAR(1) VARCHAR(n): Zeichenketten mit variabler Länge (Leerzeichen nicht gespeichert) - (n max. 4000) CLOB, BLOB: Variabel lange Dokumente (Large Objects) als Character CLOB oder Binary BLOB (Max. 4 GB) DATE: Datumsangabe mit Uhrzeit ('DD-MM-YYYY', 'HH24:MI:SS') Änderung Anzeige-/Eingabe-Format des Datums: alter session set NLS_DATE_FORMAT='DD-MM-YYYY'; (25-03-2011) alter session set NLS_DATE_FORMAT='DD-Mon-YYYY hh24:mi'; select TO_CHAR (a, 'hh24:mi') as Abflugzeitpunkt from tab3;

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

9

not-null-Klausel, references-Angabe in foreign-keyKlausel not null: Nullwerte sind in diesem Attribut nicht erlaubt, d. h. hier muss immer ein Wert stehen. z. B. bei Produkt könnte stehen: Bezeichnung varchar(20) not null, (Primary keys sind immer automatisch not null) Falls bei foreign key die Fremdschschlüssel und Primärschlüssel gleiche Namen haben kann bei references die Angabe der Namen der Attribute in der referenzierten Relation weg fallen. Frage: Was kann im Beispiel S. 7 wegfallen?

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

10

Integritätsbedingungen: primary key-, check-, default-Klausel create table Vorlesungen ( V-Bezeichnung varchar(80) primary key, SWS smallint check (SWS > 0), Semester smallint check (Semester between 0 and 6), Studiengang varchar(20) default ‘Informatik‘ check (Studiengang value in ( ‘Informatik‘, ‘Mathematik‘, ‘Elektrotechnik‘, ‘Maschinenbau‘)) ) Bemerkung: Die aufgeführten Klauseln entsprechen der SQL-Norm, funktionieren aber teilweise nicht in ORACLE und anderen DB-Systemen

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

11

alter, drop table in SQL-DDL alter table Produkt add gewicht decimal(8,2); (gewicht wird bei jedem schon existierenden Tupel mit NULL belegt) alter table Haendler add kreditlimit decimal(8,2) default 10000 check (kreditlimit < jahresumsatz / 2); (jahresumsatz sei Attribut der Relation Haendler) alter table Haendler drop kreditlimit restrict (restrict: Es dürfen keine Sichten oder Integritätsbed. wie z. B Fremdschlüssel existieren Statt restrict cascade: gleichzeitige Löschung der Sichten und Integritätsbed.) drop table Haendler restrict (oder cascade. Bedeutung analog drop bei Attributen) Siehe auch alter table bei der Erzeugung in Power-Designer (foreign-key Klausel). Warum mit alter table? Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

12

create view in SQL-DDL

• •

•

Sichten werden verwendet zur Definition externer DB-Schemata (ANSI-SPARC-Ansatz). Sicht = Berechnungsvorschrift für virtuelle Relation (Sicht wird zum Zeitpunkt der Verwendung aus beteiligten Relationen durch SQLAbfrage dynamisch von Relation abgeleitet). Vorteile: - Vereinfachung von Anfragen - Strukturierung der Datenbank - logische Datenunabhängigkeit (Sichten stabil bei Änderungen der DB-Struktur) - Beschränkung von Zugriffen (Datenschutz)

create view GebrauchsProdukte as select Nummer, Bezeichnung from Produkt where Funktion = ‘Gebrauch‘ (- Hinter as kann beliebige select-Anweisung stehen (select-Anw.: siehe später in Vorl.) - Mit Sichten (Views) arbeitet man wie mit normalen Relationen - Änderung von Daten über Views sind kritisch (update etc. über Views: später in Vorl.)) Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

13

create index in SQL-DDL

• •

Anweisung gehört zur Definition der physischen Ebene. Anweisung definiert Zugriffspfad (=schneller Zugriff über Werte einer Attributkombination auf Tupel). Genauer: später in dieser Vorlesung

create [unique] index Indexname Falls unique definiert, ist gewährleistet, dass Werte der on Relation im Index def. Attributkombination nur einmal vorkommen (Spalte_1 [ordnung_1], ...

Spalte_n [ordnung_n])

Falls Attribut(kombination) Primärschlüssel =>Index wird von System automatisch angelegt.

Beispiel: create index Produktnamenindex on Produkt (Produktbezeichnung asc, Groesse ) (asc = aufsteigend, desc = absteigend, asc default)

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

14

Aufgabe DDL in Oracle

1. Definieren Sie die DDL-Anweisung zum Kreieren der Relation Veranstalter. Veranstalter

Geschaefspartner-Nr

Adresse

Bezeichnung

Typ

2. Realisieren Sie die 1:n-Beziehung zwischen Veranstalter und Markt. Was muss in Veranstalter bzw. Markt geändert / ergänzt werden? Oder muss zusätzliche Relation für Beziehung eingeführt werden? (1,1)

Markt

(0,*) 8

Veranstalter

Markt (Bezeichnung, Standort, Kategorie) Veranstalter (Geschaefspartner-Nr, Adresse, Bezeichnung, Typ) 8: Ansprechpartner

Definieren Sie die Relationen mit Fremdschlüsseln durch DDL-Statements.

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

15

Aufgabe DDL in Oracle

3. Führen Sie die DDL-Definitionen für die Relation durch, die Fremdschlüssel enthält.

Teilprodukt (0,*)

Produkt

Nummer

...

(0,*) Produktgruppe Anzahl

4 4: besteht aus Teil

Prof. Dr. Wolfgang Weber, Vorlesung Datenbanken

16