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