-- 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 mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema outerspace -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema outerspace -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `outerspace` DEFAULT CHARACTER SET latin1 ; USE `outerspace` ; -- ----------------------------------------------------- -- Table `outerspace`.`hemelobject` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`hemelobject` ( `objectnaam` VARCHAR(10) NOT NULL, `satellietVan` VARCHAR(10) NULL DEFAULT NULL, `afstand` DECIMAL(10,3) NULL DEFAULT NULL, `diameter` DECIMAL(7,0) NULL DEFAULT NULL, PRIMARY KEY (`objectnaam`), INDEX `fk_satelliet_van_hemelobject` (`satellietVan` ASC), CONSTRAINT `fk_satelliet_van_hemelobject` FOREIGN KEY (`satellietVan`) REFERENCES `outerspace`.`hemelobject` (`objectnaam`)) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- ----------------------------------------------------- -- Table `outerspace`.`reis` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`reis` ( `reisnr` DECIMAL(4,0) NOT NULL, `vertrekdatum` DATETIME NOT NULL, `reisduur` DECIMAL(4,0) NOT NULL, `prijs` DECIMAL(10,2) NULL DEFAULT NULL, PRIMARY KEY (`reisnr`)) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- ----------------------------------------------------- -- Table `outerspace`.`bezoek` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`bezoek` ( `reisnr` DECIMAL(4,0) NOT NULL, `objectnaam` VARCHAR(10) NOT NULL, `volgnr` DECIMAL(2,0) NOT NULL, `verblijfsduur` DECIMAL(4,0) NOT NULL, PRIMARY KEY (`reisnr`, `volgnr`), INDEX `fk_bezoek_aan_hemelobject` (`objectnaam` ASC), CONSTRAINT `fk_bezoek_aan_hemelobject` FOREIGN KEY (`objectnaam`) REFERENCES `outerspace`.`hemelobject` (`objectnaam`) ON UPDATE CASCADE, CONSTRAINT `fk_bezoek_tijdens_reis` FOREIGN KEY (`reisnr`) REFERENCES `outerspace`.`reis` (`reisnr`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- ----------------------------------------------------- -- Table `outerspace`.`klant` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`klant` ( `klantnr` INT(11) NOT NULL AUTO_INCREMENT, `naam` VARCHAR(100) NULL DEFAULT NULL, `geboortedatum` DATETIME NULL DEFAULT NULL, `straat` VARCHAR(45) NULL DEFAULT NULL, `huisnummer` INT(11) NULL DEFAULT NULL, `postcode` VARCHAR(6) NULL DEFAULT NULL, PRIMARY KEY (`klantnr`), UNIQUE INDEX `un_klantnaam` (`naam` ASC)) ENGINE = InnoDB AUTO_INCREMENT = 127 DEFAULT CHARACTER SET = latin1; -- ----------------------------------------------------- -- Table `outerspace`.`deelname` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`deelname` ( `reisnr` DECIMAL(4,0) NOT NULL, `klantnr` INT(11) NOT NULL, PRIMARY KEY (`reisnr`, `klantnr`), INDEX `fk_deelname_door_klant` (`klantnr` ASC), CONSTRAINT `fk_deelname_aan_reis` FOREIGN KEY (`reisnr`) REFERENCES `outerspace`.`reis` (`reisnr`) ON UPDATE CASCADE, CONSTRAINT `fk_deelname_door_klant` FOREIGN KEY (`klantnr`) REFERENCES `outerspace`.`klant` (`klantnr`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- ----------------------------------------------------- -- Table `outerspace`.`telefoon` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `outerspace`.`telefoon` ( `telefoonnr` INT(11) NOT NULL, `klantnr` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`telefoonnr`), INDEX `fk_telefoon_klant_idx` (`klantnr` ASC), CONSTRAINT `fk_telefoon_klant` FOREIGN KEY (`klantnr`) REFERENCES `outerspace`.`klant` (`klantnr`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;