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'; CREATE SCHEMA IF NOT EXISTS `muecke_2` DEFAULT CHARACTER SET utf8 ; USE `muecke_2` ; -- ----------------------------------------------------- -- Table `muecke_2`.`orte` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`orte` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`orte` ( `ortnr` INT(10) NOT NULL , `plz` VARCHAR(5) NULL DEFAULT NULL , `ort` VARCHAR(50) NULL DEFAULT NULL , PRIMARY KEY (`ortnr`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`banken` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`banken` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`banken` ( `blz` INT(10) NOT NULL , `bankname` VARCHAR(58) NULL DEFAULT NULL , `ortnr` INT(10) NOT NULL , PRIMARY KEY (`blz`) , INDEX `Bankenortnr` (`ortnr` ASC) , INDEX `OrteBanken_idx` (`ortnr` ASC) , CONSTRAINT `OrteBanken` FOREIGN KEY (`ortnr` ) REFERENCES `muecke_2`.`orte` (`ortnr` )) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`lieferbedingung` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`lieferbedingung` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`lieferbedingung` ( `lbnr` INT(10) NOT NULL , `bedingung` VARCHAR(20) NULL DEFAULT NULL , PRIMARY KEY (`lbnr`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`konditionen` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`konditionen` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`konditionen` ( `konditnr` INT(10) NOT NULL , `skonto` INT(5) NOT NULL , `tage` INT(5) NULL , `rabatt` INT(5) NULL DEFAULT NULL , PRIMARY KEY (`konditnr`, `skonto`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`kunden` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`kunden` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`kunden` ( `kundennr` INT(10) NOT NULL , `firma` VARCHAR(50) NULL DEFAULT NULL , `strasse` VARCHAR(50) NULL DEFAULT NULL , `ortnr` INT(10) NULL DEFAULT NULL , `telefon` VARCHAR(20) NULL DEFAULT NULL , `fax` VARCHAR(20) NULL DEFAULT NULL , `email` VARCHAR(50) NULL DEFAULT NULL , `kontonr` VARCHAR(12) NULL DEFAULT NULL , `blz` INT(10) NOT NULL , `steuernr` VARCHAR(20) NULL DEFAULT NULL , `ust_idnr` VARCHAR(11) NULL DEFAULT NULL , `erstlief` DATE NULL DEFAULT NULL , `personalnr` INT(10) NULL DEFAULT NULL , `lbnr` INT(10) NOT NULL , `konditnr` INT(10) NOT NULL , PRIMARY KEY (`kundennr`) , INDEX `BankenKUNDEN_idx` (`blz` ASC) , INDEX `KUNDENblz` (`blz` ASC) , INDEX `LieferbedingungKUNDEN_idx` (`lbnr` ASC) , INDEX `OrteKUNDEN_idx` (`ortnr` ASC) , INDEX `PERSONALKUNDEN` (`personalnr` ASC) , INDEX `fk_kunden_konditionen1_idx` (`konditnr` ASC) , CONSTRAINT `BankenKUNDEN` FOREIGN KEY (`blz` ) REFERENCES `muecke_2`.`banken` (`blz` ) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `LieferbedingungKUNDEN` FOREIGN KEY (`lbnr` ) REFERENCES `muecke_2`.`lieferbedingung` (`lbnr` ), CONSTRAINT `OrteKUNDEN` FOREIGN KEY (`ortnr` ) REFERENCES `muecke_2`.`orte` (`ortnr` ), CONSTRAINT `fk_kunden_konditionen1` FOREIGN KEY (`konditnr` ) REFERENCES `muecke_2`.`konditionen` (`konditnr` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`auftraege` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`auftraege` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`auftraege` ( `auftragsnr` INT(10) NOT NULL , `auftragsdat` DATE NULL DEFAULT NULL , `kundennr` INT(10) NOT NULL , PRIMARY KEY (`auftragsnr`) , INDEX `fk_auftraege_kunden1_idx` (`kundennr` ASC) , CONSTRAINT `fk_auftraege_kunden1` FOREIGN KEY (`kundennr` ) REFERENCES `muecke_2`.`kunden` (`kundennr` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`zuschlaege` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`zuschlaege` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`zuschlaege` ( `zuschlagsnr` INT(10) NOT NULL , `zuschlagssatz` INT(10) NULL DEFAULT NULL , PRIMARY KEY (`zuschlagsnr`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`fertigerzeugnisse` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`fertigerzeugnisse` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`fertigerzeugnisse` ( `fertigerznr` INT(10) NOT NULL , `bezeichnung` VARCHAR(50) NULL DEFAULT NULL , `produktgruppe` VARCHAR(3) NULL DEFAULT NULL , `lagerbestand` INT(10) NULL DEFAULT NULL , `herstellkosten` DOUBLE NULL DEFAULT NULL , `zuschlagsnr` INT(10) NOT NULL , PRIMARY KEY (`fertigerznr`) , INDEX `fk_fertigerzeugnisse_zuschlaege1_idx` (`zuschlagsnr` ASC) , CONSTRAINT `fk_fertigerzeugnisse_zuschlaege1` FOREIGN KEY (`zuschlagsnr` ) REFERENCES `muecke_2`.`zuschlaege` (`zuschlagsnr` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`auftragspositionen` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`auftragspositionen` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`auftragspositionen` ( `auftragsposnr` INT(10) NOT NULL , `posnr` INT(10) NULL DEFAULT NULL , `fertigerznr` INT(10) NOT NULL , `menge` INT(10) NULL DEFAULT NULL , `status` VARCHAR(1) NULL DEFAULT NULL , `auftragsnr` INT(10) NOT NULL , PRIMARY KEY (`auftragsposnr`) , INDEX `FertigerzeugnisseAuftragspositi` (`fertigerznr` ASC) , INDEX `fk_auftragspositionen_auftraege1_idx` (`auftragsnr` ASC) , CONSTRAINT `FertigerzeugnisseAuftragspositionen` FOREIGN KEY (`fertigerznr` ) REFERENCES `muecke_2`.`fertigerzeugnisse` (`fertigerznr` ), CONSTRAINT `fk_auftragspositionen_auftraege1` FOREIGN KEY (`auftragsnr` ) REFERENCES `muecke_2`.`auftraege` (`auftragsnr` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `muecke_2`.`personal` -- ----------------------------------------------------- DROP TABLE IF EXISTS `muecke_2`.`personal` ; CREATE TABLE IF NOT EXISTS `muecke_2`.`personal` ( `personalnr` INT(10) NOT NULL , `name` VARCHAR(20) NULL DEFAULT NULL , `vorname` VARCHAR(15) NULL DEFAULT NULL , `geschlecht` VARCHAR(1) NULL DEFAULT NULL , `durchwahl` VARCHAR(3) NULL DEFAULT NULL , `ortnr` INT(10) NOT NULL , `strasse` VARCHAR(25) NULL DEFAULT NULL , `gebtag` DATE NULL DEFAULT NULL , `eintritt` DATE NULL DEFAULT NULL , `steuerklasse` VARCHAR(5) NULL DEFAULT NULL , `gehalt` DOUBLE NULL DEFAULT NULL , `kostnr` VARCHAR(2) NULL DEFAULT NULL , PRIMARY KEY (`personalnr`) , INDEX `OrtePERSONAL_idx` (`ortnr` ASC) , CONSTRAINT `OrtePERSONAL` FOREIGN KEY (`ortnr` ) REFERENCES `muecke_2`.`orte` (`ortnr` )) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;