From 33613a85afc4b1481367fbe92a17ee59c240250b Mon Sep 17 00:00:00 2001
From: Sven Eisenhauer <sven@sven-eisenhauer.net>
Date: Fri, 10 Nov 2023 15:11:48 +0100
Subject: add new repo

---
 Bachelor/Datenbanken 2/Praktikum1/create.sql | 38 ++++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)
 create mode 100644 Bachelor/Datenbanken 2/Praktikum1/create.sql

(limited to 'Bachelor/Datenbanken 2/Praktikum1')

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;
-- 
cgit v1.2.3