Datenbanken - Blatt 03

1 files changed, 54 insertions, 0 deletions

diff --git a/ws2015/datenbanksysteme/blaetter/03/abgabe.md b/ws2015/datenbanksysteme/blaetter/03/abgabe.md
new file mode 100644
index 0000000..df6a9b2
--- /dev/null
+++ b/ws2015/datenbanksysteme/blaetter/03/abgabe.md
@@ -0,0 +1,54 @@
+# SQL-DDL
+
+a)
+    ~~~ {.sql}
+    CREATE TABLE L
+      ( lnr VARCHAR(10) PRIMARY KEY
+      , lname VARCHAR(40) NOT NULL
+      , sitz VARCHAR(40)
+      );
+
+    CREATE TABLE T
+      ( tnr VARCHAR(10) PRIMARY KEY
+      , tname VARCHAR(40) NOT NULL
+      , farbe VARCHAR(40)
+      , gewicht INTEGER
+      , preis VARCHAR(40)
+      );
+
+    CREATE TABLE P
+      ( pnr VARCHAR(10) PRIMARY KEY
+      , pname VARCHAR(40) NOT NULL
+      , ort VARCHAR(40)
+      );
+
+    CREATE TABLE LTP
+      ( lnr VARCHAR(10)
+      , tnr VARCHAR(10)
+      , pnr VARCHAR(10)
+      , menge INTEGER
+      , FOREIGN KEY (lnr) REFERENCES L(lnr)
+      , FOREIGN KEY (tnr) REFERENCES T(tnr)
+      , FOREIGN KEY (pnr) REFERENCES P(pnr)
+      , PRIMARY KEY (lnr, tnr, pnr)
+      );
+    ~~~
+b)
+    ~~~ {.sql}
+    ALTER TABLE L ADD status INTEGER;
+    ~~~
+c)
+    ~~~ {.sql}
+    ALTER TABLE T MODIFY preis float(2);
+    ~~~
+d)
+    ~~~ {.sql}
+    ALTER TABLE T DROP COLUMN preis;
+    ~~~
+e)
+    ~~~ {.sql}
+    DROP TABLE LTP;
+    DROP TABLE L;
+    DROP TABLE T;
+    DROP TABLE P;
+    ~~~