Created
May 18, 2015 10:11
-
-
Save BrongoObenge/a7d6081c475a7a4a50ce to your computer and use it in GitHub Desktop.
a
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 mydb | |
-- ----------------------------------------------------- | |
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; | |
USE `mydb` ; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Student` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Student` ( | |
`studentnummer` INT(7) NOT NULL, | |
`voornaam` VARCHAR(45) NULL, | |
`achternaam` VARCHAR(45) NULL, | |
`tussenvoegsel` VARCHAR(45) NULL, | |
`geboortedatum` VARCHAR(45) NULL, | |
`geslacht` CHAR(1) NULL, | |
`straat` VARCHAR(200) NULL, | |
`huis_nummer` INT(45) NULL, | |
`toevoeging` VARCHAR(5) NULL, | |
`postcode` VARCHAR(7) NULL, | |
`woonplaats` VARCHAR(100) NULL, | |
`telefoonnummer` VARCHAR(17) NULL, | |
PRIMARY KEY (`studentnummer`), | |
UNIQUE INDEX `studentNummer_UNIQUE` (`studentnummer` ASC)) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Docent` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Docent` ( | |
`medewerkerscode` INT(7) NOT NULL, | |
`voornaam` VARCHAR(45) NULL, | |
`achternaam` VARCHAR(45) NULL, | |
`tussenvoegsel` VARCHAR(45) NULL, | |
`geboortedatum` VARCHAR(45) NULL, | |
`geslacht` CHAR(1) NULL, | |
`straat` VARCHAR(200) NULL, | |
`huisnummer` INT(45) NULL, | |
`toevoeging` VARCHAR(5) NULL, | |
`postcode` VARCHAR(7) NULL, | |
`woonplaats` VARCHAR(100) NULL, | |
`telefoonnummer` VARCHAR(17) NULL, | |
PRIMARY KEY (`medewerkerscode`), | |
UNIQUE INDEX `medewerkersCode_UNIQUE` (`medewerkerscode` ASC)) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Groep` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Groep` ( | |
`groepnaam` VARCHAR(200) NOT NULL, | |
`startdatum` DATE NULL, | |
`einddatum` DATE NULL, | |
PRIMARY KEY (`groepnaam`)) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Cursus` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Cursus` ( | |
`cursusCode` VARCHAR(45) NOT NULL, | |
`omschrijving` TEXT NULL, | |
`invoerdatum` DATE NULL, | |
`einddatum` DATE NULL, | |
`Docent_medewerkersCode` INT(7) NOT NULL, | |
PRIMARY KEY (`cursusCode`), | |
UNIQUE INDEX `cursusCode_UNIQUE` (`cursusCode` ASC), | |
INDEX `fk_Cursus_Docent1_idx` (`Docent_medewerkersCode` ASC), | |
CONSTRAINT `fk_Cursus_Docent1` | |
FOREIGN KEY (`Docent_medewerkersCode`) | |
REFERENCES `mydb`.`Docent` (`medewerkerscode`) | |
ON DELETE NO ACTION | |
ON UPDATE NO ACTION) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Rooster` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Rooster` ( | |
`rooster_id` INT NOT NULL, | |
`lokaal` VARCHAR(45) NULL, | |
`begintijd` DATETIME NULL, | |
`eindtijd` DATETIME NULL, | |
PRIMARY KEY (`rooster_id`), | |
UNIQUE INDEX `idRooster_UNIQUE` (`rooster_id` ASC)) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Rooster_has_Cursus` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Rooster_has_Cursus` ( | |
`Rooster_rooster_id` INT NOT NULL, | |
`Cursus_cursusCode` TEXT(20) NOT NULL, | |
PRIMARY KEY (`Rooster_rooster_id`, `Cursus_cursusCode`), | |
INDEX `fk_Rooster_has_Cursus_Cursus1_idx` (`Cursus_cursusCode` ASC), | |
INDEX `fk_Rooster_has_Cursus_Rooster_idx` (`Rooster_rooster_id` ASC), | |
CONSTRAINT `fk_Rooster_has_Cursus_Rooster` | |
FOREIGN KEY (`Rooster_rooster_id`) | |
REFERENCES `mydb`.`Rooster` (`rooster_id`) | |
ON DELETE NO ACTION | |
ON UPDATE NO ACTION, | |
CONSTRAINT `fk_Rooster_has_Cursus_Cursus1` | |
FOREIGN KEY (`Cursus_cursusCode`) | |
REFERENCES `mydb`.`Cursus` (`cursusCode`) | |
ON DELETE NO ACTION | |
ON UPDATE NO ACTION) | |
ENGINE = InnoDB; | |
-- ----------------------------------------------------- | |
-- Table `mydb`.`Student_has_Groep` | |
-- ----------------------------------------------------- | |
CREATE TABLE IF NOT EXISTS `mydb`.`Student_has_Groep` ( | |
`Student_studentnummer` INT(7) NOT NULL, | |
`Groep_groepnaam` VARCHAR(200) NOT NULL, | |
PRIMARY KEY (`Student_studentnummer`, `Groep_groepnaam`), | |
INDEX `fk_Student_has_Groep_Groep1_idx` (`Groep_groepnaam` ASC), | |
INDEX `fk_Student_has_Groep_Student1_idx` (`Student_studentnummer` ASC), | |
CONSTRAINT `fk_Student_has_Groep_Student1` | |
FOREIGN KEY (`Student_studentnummer`) | |
REFERENCES `mydb`.`Student` (`studentnummer`) | |
ON DELETE NO ACTION | |
ON UPDATE NO ACTION, | |
CONSTRAINT `fk_Student_has_Groep_Groep1` | |
FOREIGN KEY (`Groep_groepnaam`) | |
REFERENCES `mydb`.`Groep` (`groepnaam`) | |
ON DELETE NO ACTION | |
ON UPDATE NO ACTION) | |
ENGINE = InnoDB; | |
SET SQL_MODE=@OLD_SQL_MODE; | |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; | |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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 mydb
CREATE SCHEMA IF NOT EXISTS
mydb
DEFAULT CHARACTER SET utf8 ;USE
mydb
;-- Table
mydb
.Student
CREATE TABLE IF NOT EXISTS
mydb
.Student
(studentnummer
INT(7) NOT NULL,voornaam
VARCHAR(45) NULL,achternaam
VARCHAR(45) NULL,tussenvoegsel
VARCHAR(45) NULL,geboortedatum
VARCHAR(45) NULL,geslacht
CHAR(1) NULL,straat
VARCHAR(200) NULL,huis_nummer
INT(45) NULL,toevoeging
VARCHAR(5) NULL,postcode
VARCHAR(7) NULL,woonplaats
VARCHAR(100) NULL,telefoonnummer
VARCHAR(17) NULL,PRIMARY KEY (
studentnummer
),UNIQUE INDEX
studentNummer_UNIQUE
(studentnummer
ASC))ENGINE = InnoDB;
-- Table
mydb
.Docent
CREATE TABLE IF NOT EXISTS
mydb
.Docent
(medewerkerscode
INT(7) NOT NULL,voornaam
VARCHAR(45) NULL,achternaam
VARCHAR(45) NULL,tussenvoegsel
VARCHAR(45) NULL,geboortedatum
VARCHAR(45) NULL,geslacht
CHAR(1) NULL,straat
VARCHAR(200) NULL,huisnummer
INT(45) NULL,toevoeging
VARCHAR(5) NULL,postcode
VARCHAR(7) NULL,woonplaats
VARCHAR(100) NULL,telefoonnummer
VARCHAR(17) NULL,PRIMARY KEY (
medewerkerscode
),UNIQUE INDEX
medewerkersCode_UNIQUE
(medewerkerscode
ASC))ENGINE = InnoDB;
-- Table
mydb
.Groep
CREATE TABLE IF NOT EXISTS
mydb
.Groep
(groepnaam
VARCHAR(200) NOT NULL,startdatum
DATE NULL,einddatum
DATE NULL,PRIMARY KEY (
groepnaam
))ENGINE = InnoDB;
-- Table
mydb
.Cursus
CREATE TABLE IF NOT EXISTS
mydb
.Cursus
(cursusCode
VARCHAR(45) NOT NULL,omschrijving
TEXT NULL,invoerdatum
DATE NULL,einddatum
DATE NULL,Docent_medewerkersCode
INT(7) NOT NULL,PRIMARY KEY (
cursusCode
),UNIQUE INDEX
cursusCode_UNIQUE
(cursusCode
ASC),INDEX
fk_Cursus_Docent1_idx
(Docent_medewerkersCode
ASC),CONSTRAINT
fk_Cursus_Docent1
FOREIGN KEY (
Docent_medewerkersCode
)REFERENCES
mydb
.Docent
(medewerkerscode
)ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table
mydb
.Rooster
CREATE TABLE IF NOT EXISTS
mydb
.Rooster
(rooster_id
INT NOT NULL,lokaal
VARCHAR(45) NULL,begintijd
DATETIME NULL,eindtijd
DATETIME NULL,PRIMARY KEY (
rooster_id
),UNIQUE INDEX
idRooster_UNIQUE
(rooster_id
ASC))ENGINE = InnoDB;
-- Table
mydb
.Rooster_has_Cursus
CREATE TABLE IF NOT EXISTS
mydb
.Rooster_has_Cursus
(Rooster_rooster_id
INT NOT NULL,Cursus_cursusCode
TEXT(20) NOT NULL,PRIMARY KEY (
Rooster_rooster_id
,Cursus_cursusCode
),INDEX
fk_Rooster_has_Cursus_Cursus1_idx
(Cursus_cursusCode
ASC),INDEX
fk_Rooster_has_Cursus_Rooster_idx
(Rooster_rooster_id
ASC),CONSTRAINT
fk_Rooster_has_Cursus_Rooster
FOREIGN KEY (
Rooster_rooster_id
)REFERENCES
mydb
.Rooster
(rooster_id
)ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT
fk_Rooster_has_Cursus_Cursus1
FOREIGN KEY (
Cursus_cursusCode
)REFERENCES
mydb
.Cursus
(cursusCode
)ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table
mydb
.Student_has_Groep
CREATE TABLE IF NOT EXISTS
mydb
.Student_has_Groep
(Student_studentnummer
INT(7) NOT NULL,Groep_groepnaam
VARCHAR(200) NOT NULL,PRIMARY KEY (
Student_studentnummer
,Groep_groepnaam
),INDEX
fk_Student_has_Groep_Groep1_idx
(Groep_groepnaam
ASC),INDEX
fk_Student_has_Groep_Student1_idx
(Student_studentnummer
ASC),CONSTRAINT
fk_Student_has_Groep_Student1
FOREIGN KEY (
Student_studentnummer
)REFERENCES
mydb
.Student
(studentnummer
)ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT
fk_Student_has_Groep_Groep1
FOREIGN KEY (
Groep_groepnaam
)REFERENCES
mydb
.Groep
(groepnaam
)ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table
mydb
.Client
CREATE TABLE IF NOT EXISTS
mydb
.Client
(idClient
INT(11) NOT NULL,PRIMARY KEY (
idClient
))ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- Table
mydb
.Huisarts
CREATE TABLE IF NOT EXISTS
mydb
.Huisarts
(idHuisarts
INT(11) NOT NULL,emailHuisarts
VARCHAR(45) NULL DEFAULT NULL,naamHuisarts
VARCHAR(45) NULL DEFAULT NULL,PRIMARY KEY (
idHuisarts
))ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- Table
mydb
.Patient
CREATE TABLE IF NOT EXISTS
mydb
.Patient
(idPatient
INT(11) NOT NULL,naam
VARCHAR(45) NULL DEFAULT NULL,achternaam
VARCHAR(45) NULL DEFAULT NULL,adres
VARCHAR(45) NULL DEFAULT NULL,geboortedatum
VARCHAR(45) NULL DEFAULT NULL,bsn
VARCHAR(45) NULL DEFAULT NULL,Huisarts_idHuisarts
INT(11) NOT NULL,Client_idClient
INT(11) NOT NULL,PRIMARY KEY (
idPatient
),INDEX
fk_Patient_Huisarts_idx
(Huisarts_idHuisarts
ASC),INDEX
fk_Patient_Client1_idx
(Client_idClient
ASC),CONSTRAINT
fk_Patient_Client1
FOREIGN KEY (
Client_idClient
)REFERENCES
mydb
.Client
(idClient
)ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT
fk_Patient_Huisarts
FOREIGN KEY (
Huisarts_idHuisarts
)REFERENCES
mydb
.Huisarts
(idHuisarts
)ON DELETE NO ACTION
ON UPDATE NO ACTION)
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;