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;