Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- MySQL Script generated by MySQL Workbench
- -- 12/12/16 14:56:51
- -- 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 mydb
- -- -----------------------------------------------------
- -- -----------------------------------------------------
- -- Table `klant`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `klant` ;
- CREATE TABLE IF NOT EXISTS `klant` (
- `klantID` VARCHAR(25) NOT NULL,
- `naam` VARCHAR(45) NOT NULL,
- `telefoonnummer` VARCHAR(45) NOT NULL,
- `postadres` VARCHAR(45) NOT NULL,
- `emailadres` VARCHAR(45) NULL,
- PRIMARY KEY (`klantID`))
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `factuur`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `factuur` ;
- CREATE TABLE IF NOT EXISTS `factuur` (
- `factuurID` VARCHAR(25) NOT NULL,
- `klantID` VARCHAR(25) NOT NULL,
- `totale_prijs` DOUBLE NOT NULL,
- `datum` DATE NOT NULL,
- PRIMARY KEY (`factuurID`),
- INDEX `fk_factuur_klant_idx` (`klantID` ASC),
- CONSTRAINT `fk_factuur_klant`
- FOREIGN KEY (`klantID`)
- REFERENCES `klant` (`klantID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `orders`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `orders` ;
- CREATE TABLE IF NOT EXISTS `orders` (
- `orderID` VARCHAR(25) NOT NULL,
- `orderstatus` VARCHAR(45) NOT NULL,
- `datum` DATE NOT NULL,
- `klantID` VARCHAR(25) NOT NULL,
- PRIMARY KEY (`orderID`),
- INDEX `fk_order_klant1_idx` (`klantID` ASC),
- CONSTRAINT `fk_order_klant1`
- FOREIGN KEY (`klantID`)
- REFERENCES `klant` (`klantID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `robot`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `robot` ;
- CREATE TABLE IF NOT EXISTS `robot` (
- `robotID` VARCHAR(25) NOT NULL,
- `gang` INT NOT NULL,
- PRIMARY KEY (`robotID`))
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `verzamelen`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `verzamelen` ;
- CREATE TABLE IF NOT EXISTS `verzamelen` (
- `orderID` VARCHAR(25) NOT NULL,
- `robotID` VARCHAR(25) NOT NULL,
- PRIMARY KEY (`orderID`, `robotID`),
- INDEX `fk_order_has_robot_robot1_idx` (`robotID` ASC),
- INDEX `fk_order_has_robot_order1_idx` (`orderID` ASC),
- CONSTRAINT `fk_order_has_robot_order1`
- FOREIGN KEY (`orderID`)
- REFERENCES `orders` (`orderID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT `fk_order_has_robot_robot1`
- FOREIGN KEY (`robotID`)
- REFERENCES `robot` (`robotID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `product`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `product` ;
- CREATE TABLE IF NOT EXISTS `product` (
- `productID` VARCHAR(25) NOT NULL,
- `naam` VARCHAR(45) NOT NULL,
- `prijs` DOUBLE NOT NULL,
- PRIMARY KEY (`productID`))
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `bestelling`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `bestelling` ;
- CREATE TABLE IF NOT EXISTS `bestelling` (
- `orderID` VARCHAR(25) NULL,
- `productID` VARCHAR(25) NULL,
- PRIMARY KEY (`orderID`, `productID`),
- INDEX `fk_order_has_product_product1_idx` (`productID` ASC),
- INDEX `fk_order_has_product_order1_idx` (`orderID` ASC),
- CONSTRAINT `fk_order_has_product_order1`
- FOREIGN KEY (`orderID`)
- REFERENCES `orders` (`orderID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT `fk_order_has_product_product1`
- FOREIGN KEY (`productID`)
- REFERENCES `product` (`productID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `leverancier`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `leverancier` ;
- CREATE TABLE IF NOT EXISTS `leverancier` (
- `kvknummer` VARCHAR(45) NOT NULL,
- `emailadres` VARCHAR(45) NULL,
- `adres` VARCHAR(45) NOT NULL,
- `contactpersoon` VARCHAR(45) NOT NULL,
- `telefoonnummer` VARCHAR(30) NOT NULL,
- PRIMARY KEY (`kvknummer`))
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `gekocht_bij`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `gekocht_bij` ;
- CREATE TABLE IF NOT EXISTS `gekocht_bij` (
- `productID` VARCHAR(25) NULL,
- `kvknummer` VARCHAR(45) NULL,
- PRIMARY KEY (`productID`, `kvknummer`),
- INDEX `fk_product_has_leverancier_leverancier1_idx` (`kvknummer` ASC),
- INDEX `fk_product_has_leverancier_product1_idx` (`productID` ASC),
- CONSTRAINT `fk_product_has_leverancier_product1`
- FOREIGN KEY (`productID`)
- REFERENCES `product` (`productID`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT `fk_product_has_leverancier_leverancier1`
- FOREIGN KEY (`kvknummer`)
- REFERENCES `leverancier` (`kvknummer`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `leveren`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `leveren` ;
- CREATE TABLE IF NOT EXISTS `leveren` (
- `kvknummer` VARCHAR(45) NOT NULL,
- `productID` VARCHAR(25) NOT NULL,
- `prijs` DOUBLE NOT NULL,
- PRIMARY KEY (`kvknummer`, `productID`),
- INDEX `fk_leverancier_has_product_product1_idx` (`productID` ASC),
- INDEX `fk_leverancier_has_product_leverancier1_idx` (`kvknummer` ASC),
- CONSTRAINT `fk_leverancier_has_product_leverancier1`
- FOREIGN KEY (`kvknummer`)
- REFERENCES `leverancier` (`kvknummer`)
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT `fk_leverancier_has_product_product1`
- FOREIGN KEY (`productID`)
- REFERENCES `product` (`productID`)
- 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;
- -- -----------------------------------------------------
- -- Data for table `klant`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES ('01', 'Pieter Pater', '06-78945232', 'Paterstraat 69', 'pieterpater@msn.com');
- INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES ('02', 'Peter Pan', '06-45786213', 'Panterstraat 20', 'peterpannetje@gmail.com');
- INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES ('03', 'Robin van Persie', '06-12367996', 'Tijgerlaan 11', 'rvp4life@live.nl');
- INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES ('04', 'Arjen Robben', '06-14782235', 'Olifantendorp 87', NULL);
- INSERT INTO `klant` (`klantID`, `naam`, `telefoonnummer`, `postadres`, `emailadres`) VALUES ('05', 'Klaas Taart', '06-47851236', 'Taartenstraat 23', 'klaasappeltaart@hotmail.com');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `factuur`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `factuur` (`factuurID`, `klantID`, `totale_prijs`, `datum`) VALUES ('00000001', '01', 569.00, '2016-01-05');
- INSERT INTO `factuur` (`factuurID`, `klantID`, `totale_prijs`, `datum`) VALUES ('00000002', '02', 210.00, '2016-12-12');
- INSERT INTO `factuur` (`factuurID`, `klantID`, `totale_prijs`, `datum`) VALUES ('00000003', '03', 177.00, '2016-09-05');
- INSERT INTO `factuur` (`factuurID`, `klantID`, `totale_prijs`, `datum`) VALUES ('00000004', '04', 99.00, '2016-04-22');
- INSERT INTO `factuur` (`factuurID`, `klantID`, `totale_prijs`, `datum`) VALUES ('00000005', '05', 20.00, '2016-02-12');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `orders`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `orders` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES ('000001', 'verzonden', '2016-11-15', '01');
- INSERT INTO `orders` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES ('000002', 'geleverd', '2016-11-04', '02');
- INSERT INTO `orders` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES ('000003', 'uitverkocht', '2016-12-04', '03');
- INSERT INTO `orders` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES ('000004', 'verzonden', '2016-12-05', '04');
- INSERT INTO `orders` (`orderID`, `orderstatus`, `datum`, `klantID`) VALUES ('000005', 'uitverkocht', '2016-11-29', '05');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `robot`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `robot` (`robotID`, `gang`) VALUES ('001', 1);
- INSERT INTO `robot` (`robotID`, `gang`) VALUES ('002', 2);
- INSERT INTO `robot` (`robotID`, `gang`) VALUES ('003', 3);
- INSERT INTO `robot` (`robotID`, `gang`) VALUES ('004', 4);
- INSERT INTO `robot` (`robotID`, `gang`) VALUES ('005', 5);
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `verzamelen`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES ('000001', '001');
- INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES ('000002', '002');
- INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES ('000003', '003');
- INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES ('000004', '004');
- INSERT INTO `verzamelen` (`orderID`, `robotID`) VALUES ('000005', '005');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `product`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES ('100058', 'paracetamol', 15.00);
- INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES ('100049', 'vitamine c', 5.00);
- INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES ('100064', 'melatonine', 45.00);
- INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES ('100095', 'ritalin', 20.00);
- INSERT INTO `product` (`productID`, `naam`, `prijs`) VALUES ('110005', 'rennie', 2.00);
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `bestelling`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `bestelling` (`orderID`, `productID`) VALUES ('000001', '100058');
- INSERT INTO `bestelling` (`orderID`, `productID`) VALUES ('000002', '100049');
- INSERT INTO `bestelling` (`orderID`, `productID`) VALUES ('000003', '100064');
- INSERT INTO `bestelling` (`orderID`, `productID`) VALUES ('000004', '100095');
- INSERT INTO `bestelling` (`orderID`, `productID`) VALUES ('000005', '110005');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `leverancier`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES ('54826351', 'dylanschaafstra@gmail.com', 'fransstraat 10', 'Dylan Schaafstra', '0318-654879');
- INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES ('54826332', 'michielbuyserd@gmail.com', 'klaasstraat 10', 'Michiel Buyserd', '073-987456');
- INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES ('54826400', 'wesleynoorlander@gmail.com', 'pjotrstraat 12', 'Wesley Noorlander', '042-587963');
- INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES ('54826650', NULL, 'vincentstraat 15', 'Patrick Jansen', '020-4669876');
- INSERT INTO `leverancier` (`kvknummer`, `emailadres`, `adres`, `contactpersoon`, `telefoonnummer`) VALUES ('54826701', 'klaasappeltaartje@gmail.com', 'klaaslaan 22', 'Klaas Taart', '010-4578642');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `gekocht_bij`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES ('100058', '54826351');
- INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES ('100049', '54826332');
- INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES ('100064', '54826400');
- INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES ('100095', '54826650');
- INSERT INTO `gekocht_bij` (`productID`, `kvknummer`) VALUES ('110005', '54826701');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `leveren`
- -- -----------------------------------------------------
- START TRANSACTION;
- INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES ('54826351', '100058', 1.50);
- INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES ('54826332', '100049', 3.00);
- INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES ('54826400', '100064', 10.00);
- INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES ('54826650', '100095', 12.50);
- INSERT INTO `leveren` (`kvknummer`, `productID`, `prijs`) VALUES ('54826701', '110005', 0.50);
- COMMIT;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement