P3VbT6Nr

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6
7-- -----------------------------------------------------
8-- Table `mydb`.`Produkt`
9-- -----------------------------------------------------
10CREATE  TABLE IF NOT EXISTS `mydb`.`Produkt` (
11  `idProdukt` INT NOT NULL ,
12  `anzahlEingekauft` INT NOT NULL DEFAULT 0 ,
13  `gesamtpreis` DOUBLE NOT NULL ,
14  `einkaufsdatum` DATE NULL ,
15  `name` VARCHAR(45) NOT NULL ,
16  `anzahlUebrig` INT NOT NULL DEFAULT 0 ,
17  PRIMARY KEY (`idProdukt`) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `mydb`.`Investor`
23-- -----------------------------------------------------
24CREATE  TABLE IF NOT EXISTS `mydb`.`Investor` (
25  `idInvestor` INT NOT NULL ,
26  `name` VARCHAR(45) NOT NULL ,
27  `kontostand` DOUBLE NULL DEFAULT 0 ,
28  `anteile` FLOAT NULL DEFAULT 0 ,
29  `idProduktForeign` INT NOT NULL ,
30  `investiertEur` DOUBLE NOT NULL DEFAULT 0 ,
31  PRIMARY KEY (`idInvestor`) ,
32  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
33  INDEX `idProduktForeign` (`idProduktForeign` ASC) ,
34  CONSTRAINT `idProduktForeign`
35    FOREIGN KEY (`idProduktForeign` )
36    REFERENCES `mydb`.`Produkt` (`idProdukt` )
37    ON DELETE CASCADE
38    ON UPDATE CASCADE)
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `mydb`.`Kunde`
44-- -----------------------------------------------------
45CREATE  TABLE IF NOT EXISTS `mydb`.`Kunde` (
46  `idKunde` INT NOT NULL ,
47  `email` VARCHAR(45) NULL ,
48  `telefon` VARCHAR(45) NULL ,
49  `handy` VARCHAR(45) NULL ,
50  `vorname` VARCHAR(45) NULL ,
51  `nachname` VARCHAR(45) NULL ,
52  `strasse` VARCHAR(45) NULL ,
53  `hausnummer` VARCHAR(45) NULL ,
54  `plz` VARCHAR(45) NULL ,
55  `stadt` VARCHAR(45) NULL ,
56  `land` VARCHAR(45) NULL ,
57  PRIMARY KEY (`idKunde`) )
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `mydb`.`Rechnung`
63-- -----------------------------------------------------
64CREATE  TABLE IF NOT EXISTS `mydb`.`Rechnung` (
65  `idRechnung` INT NOT NULL ,
66  `idKunde` INT NOT NULL ,
67  `datum` DATE NULL ,
68  `portoversand` DOUBLE NULL ,
69  PRIMARY KEY (`idRechnung`) ,
70  INDEX `idKunde` (`idKunde` ASC) ,
71  CONSTRAINT `idKunde`
72    FOREIGN KEY (`idKunde` )
73    REFERENCES `mydb`.`Kunde` (`idKunde` )
74    ON DELETE NO ACTION
75    ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`gekauftesProdukt`
81-- -----------------------------------------------------
82CREATE  TABLE IF NOT EXISTS `mydb`.`gekauftesProdukt` (
83  `idgekauftesProdukt` INT NOT NULL ,
84  `anzahl` VARCHAR(45) NULL ,
85  `idRechnungForeign` INT NOT NULL ,
86  `rabatt` FLOAT NULL DEFAULT 1 ,
87  PRIMARY KEY (`idgekauftesProdukt`) ,
88  INDEX `idgekauftesProdukt` (`idgekauftesProdukt` ASC) ,
89  INDEX `idRechnungForeign` (`idRechnungForeign` ASC) ,
90  CONSTRAINT `idgekauftesProdukt`
91    FOREIGN KEY (`idgekauftesProdukt` )
92    REFERENCES `mydb`.`Produkt` (`idProdukt` )
93    ON DELETE NO ACTION
94    ON UPDATE NO ACTION,
95  CONSTRAINT `idRechnungForeign`
96    FOREIGN KEY (`idRechnungForeign` )
97    REFERENCES `mydb`.`Rechnung` (`idRechnung` )
98    ON DELETE NO ACTION
99    ON UPDATE NO ACTION)
100
101
102CREATE USER root IDENTIFIED BY 'root'
103
104
105ENGINE = InnoDB;
106
107
108
109SET SQL_MODE=@OLD_SQL_MODE;
110SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
111SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;