2.7.1) DDL - Data Definition Language

In SQL stehen folgende Datentypen zur Auswahl:

• exakt numerisch
  • INTEGER
    • 4 Bytes
    • Ganzzahlen von 0 bis ~4,3 Mill. oder von -2.147.483.648 bis +2.147.483.647
  • SMALLINT
    • 2 Bytes
    • Ganzzahlen von 0 bis 65.535 oder von -32.768 bis +32.767
  • NUMERIC [(M,D)]
    • M…maximale Anzahl der gezeigten Stellen
    • D…Anzahl der Kommastellen bei einer Dezimalzahl
  • DECIMAL [(M,D)]
• angenähert numerisch
  • DOUBLE
    • 8 Bytes
    • Fließkommazahl
  • REAL
    • Alias für DOUBLE
  • FLOAT
    • 4 Bytes
• Zeichenketten
  • CHAR
    • feste Länge
    • 0-255 Zeichen
  • VARCHAR
    • variable Länge
• Bitketten
  • BLOB (Binary Large Objects)
• Datum und Uhrzeit
  • DATE
    • 3 Bytes
    • Datum im Format 'YYYY-MM-DD'. Wertebereich von 01.01.1000 bis 31.12.9999
  • TIME
    • 3 Bytes
    • Zeit zwischen -838:59:59 und +839:59:59. Ausgabe: 'hh:mm:ss'
  • TIMESTAMP
    • 4 Bytes
    • Zeitstempel. Wertebereich: 1.1.1970 bis 2037.
• Logischer Datentyp
  • BOOLEAN

Mit dem Befehl „CREATE TABLE relationenname (…)“ wird ein Relationenschema definiert. Zu jedem Attribut wird ein Typ angegeben. Optional können eine oder mehrere Integritätsbedingungen angegeben werden. Mögliche Integritätsbedingungen sind:

• die Angabe, dass das Attribut einen Primärschlüssel darstellt (PRIMARY KEY)
• dass das Attribut keinen Nullwert annehmen darf (NOT NULL)
• dass ein Attribut eindeutig sein muss (UNIQUE) - diese Bedingung gilt automatisch für Primärschlüsselattribute
• oder eine durch die Klausel „CHECK (bedingung)“ formulierte Wertebereichseinschränkung

Weiters kann für Attribute durch die Klausel „DEFAULT wert“ ein Vorgabewert angegeben werden.

Es soll eine leere Relation mit dem Namen Restaurant erstellt werden. Es wird festgelegt, dass das Attribut rnr ein Primärschlüssel ist, dass die Attribute name und adresse keine Nullwerte annehmen dürfen, und dass haube nur die Werte (0-4) annehmen darf. Für den Typ eines Restaurants ist als Defaultwert „österreichisch“ festzulegen.

CREATE TABLE restaurant
(
	rnr INT NOT NULL,
	name VARCHAR(100) NOT NULL,
	adresse VARCHAR(100) NOT NULL,
	haube INT CHECK (0<=haube AND haube <=4),
			/*oder CHECK (haube IN (0,1,2,3,4)),*/
			/*oder CHECK (haube BETWEEN 0 AND 4),*/
	typ VARCHAR(100) DEFAULT 'österreichisch',
	CONSTRAINT PK_Restaurant PRIMARY KEY(rnr)
);

Eine spezielle Integritätsbedingung (zu Attributen oder zu Relationen) ist die mittels einer REFERENCES-Klausel angegebene Fremdschlüsselbedingung FOREIGN KEY, die eine Abhängigkeit repräsentiert. Zu jeder Fremdschlüsselbedingung kann durch „ON UPDATE action„ und „ON DELETE action„ angegeben werden, wie auf Verletzungen durch Änderung oder Löschen des referenzierten Schlüsselwertes reagiert werden soll. Mögliche Aktionen sind:

• die Änderung bzw. das Löschen zu verhindern (NO ACTION)
• fortzusetzen (CASCADE) oder
• den Wert des referenzierenden Attributes auf einen Nullwert (SET NULL) bzw. den Defaultwert des Attributs (SET DEFAULT) zu setzen

Der nächste SQL-Befehl erzeugt das Relationenschema Speise. Die angeführten Integritätsbedingungen legen u.a. fest, dass die Attribute rnr und name gemeinsam den Primärschlüssel der Relation Speise bilden und dass das Attribut rnr der Relation Speise ein Fremdschlüssel ist, der sich auf das Schlüsselattribut rnr der Relation Restaurant bezieht (Speise[rnr] ⊆ Restaurant[rnr]). Die Angabe ON UPDATE CASCADE legt fest, dass eine Änderung der Nummer eines Restaurants bei den entsprechenden Speisen mitgezogen wird, und die Angabe ON DELETE NO ACTION legt fest, dass ein Restaurant nicht gelöscht werden darf, solange noch Speisen für dieses Restaurant vorhanden sind.

CREATE TABLE Speise
(
	rnr INTEGER,
	name VARCHAR(150),
	CONSTRAINT PK_Speise PRIMARY KEY (name),
	CONSTRAINT FK_Speise FOREIGN KEY (rnr) REFERENCES restaurant(rnr) ON UPDATE CASCADE ON DELETE NO ACTION
);

Die Änderung der Struktur einer Tabelle wird mit dem Befehl „ALTER TABLE relationenname…“ durchgeführt.

ALTER TABLE Speise ADD Kalorien INT;

ALTER TABLE Speise ADD CONSTRAINT ck CHECK (Kalorien>=0);

ALTER TABLE Restaurant DROP Adresse;

Mit dem Befehl „DROP TABLE relationenname“ wird eine Relation wieder gelöscht.

DROP TABLE Restaurant;

Gegeben ist folgendes ER-Modell:

Gesucht sind die SQL-Statements zur Erstellung der Relationen

CREATE TABLE Orte (
	Plz INT NOT NULL,
	Ort VARCHAR(100),
	CONSTRAINT PK_Plz PRIMARY KEY(Plz)
);
 
CREATE TABLE Person (
	ID INT NOT NULL,
	Name VARCHAR(100),
	Plz INT NOT NULL,
	CONSTRAINT FK_Person FOREIGN KEY (Plz) REFERENCES Orte(Plz),
	CONSTRAINT PK_ID PRIMARY KEY (ID)
);
 
CREATE TABLE Kunde (
	ID INT NOT NULL,
	Rabatte INT NOT NULL,
	CONSTRAINT PK_ID PRIMARY KEY (ID),
	CONSTRAINT FK_KUnde FOREIGN KEY (ID) REFERENCES Person(ID) ON DELETE CASCADE
);
 
CREATE TABLE Abteilung(
	AbtNr INT NOT NULL,
	Name VARCHAR(140),
	CONSTRAINT PK_AbtNr PRIMARY KEY(AbtNr)
);
 
CREATE TABLE Mitarbeiter(
	ID INT NOT NULL,
	Gehalt INT NOT NULL,
	AbtNr INT NOT NULL,
	CONSTRAINT PK_ID PRIMARY KEY(ID),
	CONSTRAINT FK_Mitarbeiter_AbtNr FOREIGN KEY(AbtNr) REFERENCES Abteilung(ID),
	CONSTRAINT FK_Mitarbeiter_ID FOREIGN KEY(ID) REFERENCES PERSON(ID)
);
 
CREATE TABLE PKW(
	Marke VARCHAR(100),
	PS INT NOT NULL,
	FahrgestellNr INT NOT NULL,
	ID INT NOT NULL,
	CONSTRAINT PK_fgsNr PRIMARY KEY (FahrgestellNr),
	CONSTRAINT FK_PKW_ID FOREIGN KEY (ID) REFERENCES Mitarbeiter(ID)
);