====== 1.7.1) DDL - Data Definition Language ======

===== 1.7.1.1) Datentypen =====

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

===== 2.7.1.2) ERZEUGEN von Relationenschemata=====

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.

=== Beispiel ===

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.

<code SQL>

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)
);

</code>

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

=== Beispiel ===

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.

<code sql>
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
);
</code>

===== 2.7.1.3) ÄNDERN von Relationenschemata=====

Die Änderung der Struktur einer Tabelle wird mit dem Befehl "**ALTER TABLE //relationenname//...**" durchgeführt.

== Beispiel - Hinzufügen eines Attributes KALORIEN zur Relation SPEISE ==
<code sql>
ALTER TABLE Speise ADD Kalorien INT;
</code>

== Beispiel - Hinzufügen einer CHECK-Klausel für Kalorien ==
<code sql>
ALTER TABLE Speise ADD CONSTRAINT ck CHECK (Kalorien>=0);
</code>

== Beispiel - Entfernen eines Attributes ==
<code sql>
ALTER TABLE Restaurant DROP Adresse;
</code>

===== 2.7.1.4) ENTFERNEN von Relationenschemata=====

Mit dem Befehl "**DROP TABLE //relationenname//**" wird eine Relation wieder gelöscht.

<code sql>
DROP TABLE Restaurant;
</code>



===== 2.7.1.5) Übungsbeispiel =====

Gegeben ist folgendes ER-Modell:

{{:inf:inf8bi_201819:2:2_07:pasted:20181113-182853.png}}


Gesucht sind die SQL-Statements zur Erstellung der Relationen

<code sql>

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)
);

</code>