Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- MySQL Script generated by MySQL Workbench
- -- 08/10/16 13:54:47
- -- Model: New Model Version: 1.0
- -- MySQL Workbench Forward Engineering
- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
- SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
- -- -----------------------------------------------------
- -- Schema vacationplaner
- -- -----------------------------------------------------
- -- -----------------------------------------------------
- -- Schema vacationplaner
- -- -----------------------------------------------------
- CREATE SCHEMA IF NOT EXISTS `vacationplaner` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
- USE `vacationplaner` ;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`vacationUser`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`vacationUser` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUser` (
- `userID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `userLogin` VARCHAR(45) NOT NULL COMMENT '',
- `userPassword` VARCHAR(60) NULL COMMENT '',
- `userNameFirst` VARCHAR(45) NOT NULL COMMENT '',
- `userNameLast` VARCHAR(45) NOT NULL COMMENT '',
- `userMailAddress` VARCHAR(45) NOT NULL COMMENT '',
- `userVacationDaysTotal` INT NOT NULL COMMENT '',
- PRIMARY KEY (`userID`) COMMENT '')
- ENGINE = InnoDB;
- CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`vacationUser` (`userID` ASC) COMMENT '';
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`vacationRequest`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`vacationRequest` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationRequest` (
- `vacationRequestID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `userID` INT NOT NULL COMMENT '',
- `vacationRequestDateFrom` DATE NOT NULL COMMENT '',
- `vacationRequestDateTo` DATE NOT NULL COMMENT '',
- `vacationRequestTotalDays` INT NOT NULL COMMENT '',
- `vacationRequestDescription` VARCHAR(45) NULL COMMENT '',
- `vacationRequestStatus` VARCHAR(45) NOT NULL COMMENT '',
- `vacationRequestRequestDate` DATETIME NOT NULL COMMENT '',
- `vacationRequestStatusUpdateDate` DATE NULL COMMENT '',
- `vacationRequestStatusUpdateReason` VARCHAR(255) NULL COMMENT '',
- PRIMARY KEY (`vacationRequestID`) COMMENT '',
- CONSTRAINT `fk_request_user`
- FOREIGN KEY (`userID`)
- REFERENCES `vacationplaner`.`vacationUser` (`userID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE)
- ENGINE = InnoDB;
- CREATE INDEX `x_idx` ON `vacationplaner`.`vacationRequest` (`userID` ASC) COMMENT '';
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`vacationGroup`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`vacationGroup` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationGroup` (
- `groupID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `groupName` VARCHAR(45) NULL COMMENT '',
- PRIMARY KEY (`groupID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`vacationDepartment`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`vacationDepartment` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationDepartment` (
- `departmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `departmentName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL COMMENT '',
- `departmentColor` VARCHAR(45) NOT NULL COMMENT '',
- `departmentMinStock` INT NOT NULL COMMENT '',
- PRIMARY KEY (`departmentID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`statutoryHolidays`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`statutoryHolidays` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`statutoryHolidays` (
- `statutoryHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `statutoryHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
- `statutoryHolidaysDate` DATE NOT NULL COMMENT '',
- PRIMARY KEY (`statutoryHolidaysID`) COMMENT '')
- ENGINE = InnoDB;
- CREATE UNIQUE INDEX `statutoryHolidaysName_UNIQUE` ON `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysName` ASC) COMMENT '';
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`schoolHolidays`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`schoolHolidays` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`schoolHolidays` (
- `schoolHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `schoolHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
- `schoolHolidaysDateFrom` DATE NOT NULL COMMENT '',
- `schoolHolidaysDateTo` DATE NOT NULL COMMENT '',
- PRIMARY KEY (`schoolHolidaysID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`vacationUserAccountHistory`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`vacationUserAccountHistory` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUserAccountHistory` (
- `userAccountHistoryID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `userAccountID` INT NULL COMMENT '',
- `userID` INT NOT NULL COMMENT '',
- `userVacationDaysLeft` INT NOT NULL COMMENT '',
- `userVacationDaysUnconfirmed` INT NOT NULL COMMENT '',
- `userVacationLastUpdated` DATETIME NOT NULL COMMENT '',
- PRIMARY KEY (`userAccountHistoryID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`deputyOfDepartment`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`deputyOfDepartment` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`deputyOfDepartment` (
- `deputyOfDepartmentID` INT NOT NULL COMMENT '',
- PRIMARY KEY (`deputyOfDepartmentID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`userAccount`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`userAccount` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`userAccount` (
- `userAccountID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `userAccountDaysLeft` INT NOT NULL COMMENT '',
- `userAccountDaysUnconfirmed` INT NOT NULL COMMENT '',
- `userAccountLastUpdated` DATETIME NOT NULL COMMENT '',
- PRIMARY KEY (`userAccountID`) COMMENT '')
- ENGINE = InnoDB;
- CREATE UNIQUE INDEX `userAccountID_UNIQUE` ON `vacationplaner`.`userAccount` (`userAccountID` ASC) COMMENT '';
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`responsibleOfDepartment`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`responsibleOfDepartment` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`responsibleOfDepartment` (
- `responsibleOfDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- PRIMARY KEY (`responsibleOfDepartmentID`) COMMENT '')
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `vacationplaner`.`users`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `vacationplaner`.`users` ;
- CREATE TABLE IF NOT EXISTS `vacationplaner`.`users` (
- `usersID` INT NOT NULL AUTO_INCREMENT COMMENT '',
- `userID` INT NOT NULL COMMENT '',
- `departmentID` INT NOT NULL COMMENT '',
- `groupID` INT NOT NULL COMMENT '',
- `deputyOfDepartmentID` INT NULL COMMENT '',
- `responsibleOfDepartmentID` INT NULL COMMENT '',
- `userAccountID` INT NOT NULL COMMENT '',
- PRIMARY KEY (`usersID`) COMMENT '',
- CONSTRAINT `fk_users_user`
- FOREIGN KEY (`userID`)
- REFERENCES `vacationplaner`.`vacationUser` (`userID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT `fk_users_department`
- FOREIGN KEY (`departmentID`)
- REFERENCES `vacationplaner`.`vacationDepartment` (`departmentID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT `fk_users_group`
- FOREIGN KEY (`groupID`)
- REFERENCES `vacationplaner`.`vacationGroup` (`groupID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT `fk_users_deputy`
- FOREIGN KEY (`deputyOfDepartmentID`)
- REFERENCES `vacationplaner`.`deputyOfDepartment` (`deputyOfDepartmentID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT `fk_users_responsible`
- FOREIGN KEY (`responsibleOfDepartmentID`)
- REFERENCES `vacationplaner`.`responsibleOfDepartment` (`responsibleOfDepartmentID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT `fk_users_userAccount1`
- FOREIGN KEY (`userAccountID`)
- REFERENCES `vacationplaner`.`userAccount` (`userAccountID`)
- ON DELETE CASCADE
- ON UPDATE CASCADE)
- ENGINE = InnoDB;
- CREATE INDEX `fk_users_user_idx` ON `vacationplaner`.`users` (`userID` ASC) COMMENT '';
- CREATE INDEX `fk_users_department_idx` ON `vacationplaner`.`users` (`departmentID` ASC) COMMENT '';
- CREATE INDEX `fk_users_group_idx` ON `vacationplaner`.`users` (`groupID` ASC) COMMENT '';
- CREATE INDEX `fk_users_deputy_idx` ON `vacationplaner`.`users` (`deputyOfDepartmentID` ASC) COMMENT '';
- CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`users` (`userID` ASC) COMMENT '';
- CREATE INDEX `fk_users_responsible_idx` ON `vacationplaner`.`users` (`responsibleOfDepartmentID` ASC) COMMENT '';
- CREATE UNIQUE INDEX `uc_userID_departmentID_groupID` ON `vacationplaner`.`users` (`userID` ASC, `departmentID` ASC, `groupID` ASC) COMMENT '';
- CREATE INDEX `fk_users_userAccount1_idx` ON `vacationplaner`.`users` (`userAccountID` ASC) COMMENT '';
- USE `vacationplaner`;
- DELIMITER $$
- USE `vacationplaner`$$
- DROP TRIGGER IF EXISTS `vacationplaner`.`vacationUser_BEFORE_UPDATE` $$
- USE `vacationplaner`$$
- CREATE DEFINER = CURRENT_USER TRIGGER `vacationplaner`.`vacationUser_BEFORE_UPDATE` BEFORE UPDATE ON `vacationUser` FOR EACH ROW
- BEGIN
- INSERT INTO vacationUserHistory SELECT * FROM vacationUser WHERE userID = NEW.userID;
- END
- ;$$
- DELIMITER ;
- -- -----------------------------------------------------
- -- Data for table `vacationplaner`.`vacationGroup`
- -- -----------------------------------------------------
- START TRANSACTION;
- USE `vacationplaner`;
- INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (1, 'Admin');
- INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (2, 'Urlaubs_Verantwortlicher');
- INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (3, 'User');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `vacationplaner`.`vacationDepartment`
- -- -----------------------------------------------------
- START TRANSACTION;
- USE `vacationplaner`;
- INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (1, 'Geschäftsführung', '#1AE5E9', 1);
- INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (2, 'Service', '#EE0BC4', 3);
- INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (3, 'Vertrieb', '#E9F01D', 1);
- INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (4, 'Entwicklung', '#2213F5', 0);
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `vacationplaner`.`statutoryHolidays`
- -- -----------------------------------------------------
- START TRANSACTION;
- USE `vacationplaner`;
- INSERT INTO `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysID`, `statutoryHolidaysName`, `statutoryHolidaysDate`) VALUES (1, 'Neujahrstag', '2016-01-01');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `vacationplaner`.`schoolHolidays`
- -- -----------------------------------------------------
- START TRANSACTION;
- USE `vacationplaner`;
- INSERT INTO `vacationplaner`.`schoolHolidays` (`schoolHolidaysID`, `schoolHolidaysName`, `schoolHolidaysDateFrom`, `schoolHolidaysDateTo`) VALUES (1, 'Summer', '2016-07-11', '2016-08-23');
- COMMIT;
- SET SQL_MODE=@OLD_SQL_MODE;
- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
- SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement