1 files changed, 38 insertions, 0 deletions
diff --git a/Bachelor/Datenbanken 2/Praktikum1/create.sql b/Bachelor/Datenbanken 2/Praktikum1/create.sql
new file mode 100644
index 0000000..f04288c
--- /dev/null
+++ b/Bachelor/Datenbanken 2/Praktikum1/create.sql
@@ -0,0 +1,38 @@
+DROP DATABASE IF EXISTS OPPlanung;
+CREATE DATABASE OPPlanung;
+USE OPPlanung;
+
+CREATE TABLE `OPPlanung`.`Chirurg` (
+  `PersNr` CHAR(8)  NOT NULL DEFAULT '',
+  `Name` VARCHAR(20)  NOT NULL DEFAULT '',
+  PRIMARY KEY(`PersNr`)
+)
+ENGINE = InnoDB;
+
+CREATE TABLE `OPPlanung`.`Operation` (
+   `OPNr` CHAR(8)  NOT NULL DEFAULT '',
+   `OPDatum` DATE  NOT NULL DEFAULT 0,
+   `Anfang` TIME  NOT NULL DEFAULT 0,
+   `PersNr` CHAR(8)  NOT NULL DEFAULT '',
+   PRIMARY KEY(`OPNr`),
+   CONSTRAINT `leiter` FOREIGN KEY `leiter` (`PersNr`)
+     REFERENCES Chirurg (`PersNr`)
+     ON DELETE RESTRICT
+     ON UPDATE RESTRICT
+ ) ENGINE = InnoDB;
+
+CREATE TABLE `OPPlanung`.`Assistenz` (
+  `PersNr` CHAR(8)  NOT NULL DEFAULT '',
+  `OPNr` CHAR(8)  NOT NULL DEFAULT '',
+  `Rolle` VARCHAR(20)  NOT NULL DEFAULT '',
+  PRIMARY KEY(`PersNr`, `OPNr`),
+  CONSTRAINT `assi-pers` FOREIGN KEY `assi-pers` (`PersNr`)
+    REFERENCES `Chirurg` (`PersNr`)
+    ON DELETE RESTRICT
+    ON UPDATE RESTRICT,
+  CONSTRAINT `assi-op` FOREIGN KEY `assi-op` (`OPNr`)
+    REFERENCES `Operation`  (`OPNr`)
+    ON DELETE RESTRICT
+    ON UPDATE RESTRICT
+)
+ENGINE = InnoDB;