Einführung - Till Hänisch

CREATE TABLE
Syntax:
CREATE TABLE name (
coldef [, coldef]
[, tableconstraints]
)
coldef := name type [länge], [[NOT]NULL], [colconstraint]
tableconstraint := CONSTRAINT name constraint-definition
colconstraint := constraint-definition (später mehr)
CREATE TABLE Person (
name VARCHAR (30),
id INT
);
mysql> describe person;
+-------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES |
| NULL
|
|
| id
| int(11)
| YES |
| NULL
|
|
+-------+-------------+------+-----+---------+-------+
IS: Datenbanken, © Till Hänisch 2000
Constraints


Constraints legen Bedingungen für Konsistenz fest,
etwa
Primary key


Foreign key



Der referenzierte Datensatz muß existieren
(NOT) NULL


Keine doppelten Werte
Wert muß vorhanden sein
Bei Verletzung wird das entsprechende SQLStatement nicht ausgeführt (Fehlermeldung)
Alternative: Automatische Aktion, z.B. DELETE
CASCADE
IS: Datenbanken, © Till Hänisch 2000
Constraints
PRIMARY KEY
CREATE TABLE Person (
name VARCHAR (100) NOT NULL,
vorname VARCHAR (40) NOT NULL,
PersonAlter INTEGER NULL,
PRIMARY KEY (name, vorname)
);
Besserer Stil (constraints sollten benannt werden):
CREATE TABLE Person (
name VARCHAR (100) NOT NULL,
vorname VARCHAR (40) NOT NULL,
PersonAlter INTEGER NULL,
CONSTRAINT pk_person PRIMARY KEY (name, vorname)
);
Da Entities und Relationships auf Relationen abgebildet werden,
sind Beziehungen (Relationships) nicht mehr direkt erkennbar !!
Um diese zu definieren (und insb. nur gültige Beziehungen zuzulassen,
können FOREIGN KEY Constraints verwendet werden.
FKs verweisen auf den PK einer anderen Relation.
IS: Datenbanken, © Till Hänisch 2000
FOREIGN Key constraints
CREATE TABLE telefon (
nummer VARCHAR (20) NOT NULL,
art CHAR (1) NOT NULL,
name VARCHAR (100) NOT NULL,
vorname VARCHAR (40) NOT NULL,
CONSTRAINT fk_telefon_person FOREIGN KEY (name, vorname)
REFERENCES person (name, vorname)
);
Bei nicht zusammengesetzten PKs ist einfachere Schreibweise möglich:
CREATE TABLE Telefon (
Nummer VARCHAR(30) NOT NULL,
Art CHAR(1) NOT NULL,
Person INTEGER CONSTRAINT fk_telefon_person REFERENCES Person(ID)
);
IS: Datenbanken, © Till Hänisch 2000
andere Constraints
UNIQUE
Werte der entsprechenden Columns müssen eindeutig sein
(PRIMARY KEY impliziert UNIQUE)
Eindeutigkeit von Candidate Keys, z.B. Person (ID)
U_Person UNIQUE (name, vorname)
CHECK
Bedingung für ein Attribut
z.B. bei Person (
PersonAlter INT NOT NULL CHECK (alter > 18)
etwa bei CHAR(1) als Boolean-Ersatz
flag CHAR (1) NOT NULL CHECK (flag IN (’Y’, ’N’))
Bei „modernen“ SQL Dialekten auch Subqueries zulässig, z.B.
Preis DECIMAL (10,2) CHECK (preis >= (SELECT stmt.))
IS: Datenbanken, © Till Hänisch 2000
Zugriffskontrolle
Jeder Benutzer hat volle Verfügungsgewalt (Lesen, Schreiben, Ändern,...)
über die von ihm erzeugten Objekte, andere Benutzer haben keinen Zugriff
Benutzer kann Rechte an seinen Objekten für andere freigeben
(und wieder sperren)
GRANT/REVOKE Privileg {,Privileg} ON objekt TO user {,user}
spezieller "Benutzer" PUBLIC steht für alle Benutzer
Privileg = SELECT,INSERT,UPDATE,DELETE
z.B.
GRANT SELECT ON emp TO PUBLIC;
Zugriff auf Objekte anderer Benutzer erfolgt durch Qualifizierung mit
dem Benutzernamen, z.B. Tabelle "geheim" des Benutzers "max"
SELECT * FROM max.geheim;
IS: Datenbanken, © Till Hänisch 2000
Views
View = „virtuelle“, d.h. abgeleitete Relation
Aus einer oder mehreren Relationen wird durch Query eine neue „virtuelle“ erzeugt:
CREATE TABLE name SELECT ...
physikalische Relation
CREATE VIEW name AS SELECT ...
virtuelle Relation
Wenn sich Tupel der Basisrelation(en) ändern, dann ändert sich automatisch
auch der Inhalt des Views.
Anmerkung:
View-Relationen existieren physikalisch nur als Definition,
die Tupel werden bei jedem Zugriff berechnet.  Performance
View -> Abkürzung (ähnlich Makro)
Wozu?
Vorformulierung von (häufig benötigten, komplexen) Anfragen
z.B. Gehalt der Angestellten
CREATE VIEW Gehalt AS SELECT sal+ISNULL(comm,0) AS Total FROM emp; (Sybase)
CREATE VIEW Gehalt AS SELECT sal+NVL(comm,0) AS Total FROM emp; (Oracle)
Abstraktion von Details (NULL,…), Definition von Business Rules
(Gehalt = sal+comm) an einer Stelle
IS: Datenbanken, © Till Hänisch 2000
Views contd.
Denormalisierung, z.B. emp,dept
CREATE VIEW Angest AS
SELECT e.empno,e.ename, d.deptno,d.dname
FROM emp e, dept d WHERE e.deptno=d.deptno;
Verschiedene Sichten auf Daten (Datenschutz, Vertraulichkeit, Übersicht,...)
z.B. Tabelle emp enthält Gehalt, dies darf jedoch nur für Personalabteilung sichtbar
sein. Sekretariate benötigen aber Liste der Mitarbeiter. Lösung ?
Entweder zusätzliche (redundante) Tabelle, die nur die zugänglichen Informationen
enthält, oder
CREATE VIEW emp_base AS
SELECT ename,empno,deptno,job FROM emp;
(vertikaler Ausschnitt)
oder: Sekretariat soll vollst. emp-Tabelle, aber nur für die eigene Abteilung sehen
(Die Funktion USER liefert unter Sybase den Namen des aktuellen Users zurück)
CREATE TABLE userdept (name varchar(20), deptno INTEGER);
INSERT INTO userdept VALUES(‘mueller',30);
CREATE VIEW myemp AS
SELECT * FROM emp WHERE deptno = ANY (SELECT deptno FROM userdept WHERE name=USER)
(horizontaler Ausschnitt)
IS: Datenbanken, © Till Hänisch 2000