SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `university` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `university`;

/* Tabellen anlegen */
DROP TABLE IF EXISTS `studiengang`;
CREATE TABLE `studiengang` (
  `ID` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Art` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `modul`;
CREATE TABLE `modul` (
  `ID` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Semester` int(11) DEFAULT NULL,
  `CP` int(11) NOT NULL,
  `SWS` int(11) NOT NULL,
  `StudiengangID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `modul2studiengang`;
CREATE TABLE `modul2studiengang` (
  `ID` int(11) NOT NULL,
  `ModulID` int(11) NOT NULL,
  `StudiengangID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `pruefung`;
CREATE TABLE `pruefung` (
  `ID` int(20) NOT NULL,
  `ModulID` int(20) DEFAULT NULL,
  `Semester` int(20) DEFAULT NULL,
  `Jahr` int(20) DEFAULT NULL,
  `Dozent` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `ID` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Vorname` varchar(255) NOT NULL,
  `Matrikelnr` int(11) NOT NULL,
  `StudiengangID` int(11) NOT NULL,
  `Semester` int(11) DEFAULT NULL,
  `Timestmp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `student2pruefung`;
CREATE TABLE `student2pruefung` (
  `ID` int(11) NOT NULL,
  `StudentID` int(11) DEFAULT NULL,
  `PruefungID` int(11) DEFAULT NULL,
  `Note` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* Daten einfügen */
INSERT INTO `studiengang` (`ID`, `Name`, `Art`) VALUES
(1, 'Elektrotechnik', 'Master'),
(2, 'Elektrotechnik', 'Bachelor'),
(3, 'Maschinenbau', 'Master'),
(4, 'Maschinenbau', 'Bachelor');

INSERT INTO `modul` (`ID`, `Name`, `Semester`, `CP`, `SWS`, `StudiengangID`) VALUES
(1, 'Mathematik', 1, 5, 4, 1),
(2, 'Numerische Methoden', 1, 5, 4, 1),
(3, 'Theoretische Elektrotechnik', 1, 5, 1, 1),
(4, 'Physik', 2, 5, 4, 1);

INSERT INTO `modul2studiengang` (`ID`, `ModulID`, `StudiengangID`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 1, 2);

INSERT INTO `student` (`ID`, `Name`, `Vorname`, `Matrikelnr`, `StudiengangID`, `Semester`) VALUES
(1, 'Fischer', 'Jan', 12346, 2, 2),
(2, 'Lang', 'Elke', 12347, 1, 2),
(3, 'Weber', 'Michael', 12348, 1, 1),
(4, 'Schuster', 'Thomas', 12349, 2, 1),
(5, 'Muster', 'Max', 12345, 3, 1);

INSERT INTO `pruefung` (`ID`, `ModulID`, `Semester`, `Jahr`, `Dozent`) VALUES
(1, 1, 1, 2023, 'Steiner'),
(2, 2, 1, 2023, 'Wiegand'),
(3, 3, 1, 2023, 'Kiss'),
(4, 3, 1, 2024, 'Kiss'),
(5, 3, 1, 2025, 'Kiss');

INSERT INTO `student2pruefung` (`ID`, `StudentID`, `PruefungID`, `Note`) VALUES
(1, 2, 1, 2.3),
(2, 3, 3, 3.3),
(3, 1, 3, 1.3),
(4, 2, 4, 1.3),
(5, 3, 4, 3.7),
(7, 4, 4, 2);

/* Primärschlüssel anlegen */
ALTER TABLE `studiengang`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `modul`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `FK_MODUL_STUDIENGANG` (`StudiengangID`);

ALTER TABLE `modul2studiengang`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `FK_MODUL2STUDIENGANG_MODUL` (`ModulID`),
  ADD KEY `FK_MODUL2STUDIENGANG_STUDIENGANG` (`StudiengangID`);

ALTER TABLE `pruefung`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `FK_Pruefung_Modul` (`ModulID`);

ALTER TABLE `student`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `Matrikelnr` (`Matrikelnr`),
  ADD KEY `FK_STUDENT_STUDIENGANG` (`StudiengangID`);

ALTER TABLE `student2pruefung`
  ADD PRIMARY KEY (`ID`);

/* Auto-Inkrement mit aktuellen Werten initialisieren */

ALTER TABLE `modul`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `modul2studiengang`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `pruefung`
  MODIFY `ID` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `student2pruefung`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `student`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

/* Fremdschlüssel anlegen */
ALTER TABLE `modul`
  ADD CONSTRAINT `FK_MODUL_STUDIENGANG` FOREIGN KEY (`StudiengangID`) REFERENCES `studiengang` (`ID`);

ALTER TABLE `modul2studiengang`
  ADD CONSTRAINT `FK_MODUL2STUDIENGANG_MODUL` FOREIGN KEY (`ModulID`) REFERENCES `modul` (`ID`),
  ADD CONSTRAINT `FK_MODUL2STUDIENGANG_STUDIENGANG` FOREIGN KEY (`StudiengangID`) REFERENCES `studiengang` (`ID`);
ALTER TABLE `pruefung`
  ADD CONSTRAINT `FK_Pruefung_Modul` FOREIGN KEY (`ModulID`) REFERENCES `modul` (`ID`);

ALTER TABLE `student`
  ADD CONSTRAINT `FK_STUDENT_STUDIENGANG` FOREIGN KEY (`StudiengangID`) REFERENCES `studiengang` (`ID`);

ALTER TABLE `student2pruefung` 
  ADD CONSTRAINT `FK_STUDENT2PRUEFUNG_PRUEFUNG` FOREIGN KEY (`PruefungID`) REFERENCES `pruefung`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `student2pruefung` 
ADD CONSTRAINT `FK_STUDENT2PRUEFUNG_STUDENT` FOREIGN KEY (`StudentID`) REFERENCES `student`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

/* Views anlegen */
DROP VIEW IF EXISTS `noten_et`;
CREATE VIEW `noten_et`  AS  
(select `s`.`Name` AS `name`,`s`.`Vorname` AS `vorname`,`s`.`Matrikelnr` AS `matrikelnr`,`m`.`Name` AS `modulname`,`p`.`Jahr` AS `jahr`,`sp`.`Note` AS `note` 
from (((`student2pruefung` `sp` join `student` `s`) join `pruefung` `p`) join `modul` `m`) 
where ((`sp`.`StudentID` = `s`.`ID`) and (`p`.`ModulID` = `m`.`ID`) and (`sp`.`PruefungID` = `p`.`ID`) and (`sp`.`PruefungID` = (select `p`.`ID` from (`pruefung` `p` join `modul` `m`) where ((`p`.`ModulID` = `m`.`ID`) and (`m`.`Name` like '%elektrotechnik%')))))) ;

DROP VIEW IF EXISTS `studentenet`;
CREATE VIEW `studentenet`  AS  
(select `s`.`Name` AS `Name`,`s`.`Vorname` AS `Vorname`,`s`.`Matrikelnr` AS `MatrikelNr`,`stg`.`Name` AS `STG_Name` 
from (`student` `s` join `studiengang` `stg`) 
where ((`stg`.`ID` = `s`.`StudiengangID`) and (`stg`.`Name` like '%Elektro%'))) ;

COMMIT;