NBB

DROP DATABASE IF EXISTS nbb;
# DROP DATABASE NBB;
CREATE DATABASE nbb;

USE nbb;

SET utf8 ;


-- -----------------------------------------------------
-- Table tblKunde
-- -----------------------------------------------------
CREATE TABLE tblKunde (
  kid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  vorname VARCHAR(45),
  strasse VARCHAR(45),
  plz CHAR(5),
  stadt VARCHAR(45),
  email VARCHAR(45),
  PRIMARY KEY (kid)
  ) ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblArtikel
-- -----------------------------------------------------
CREATE TABLE tblArtikel (
  aid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  beschreibung VARCHAR(45),
  PRIMARY KEY (aid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblVersand
-- -----------------------------------------------------
CREATE TABLE tblVersand (
  vid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  beschreibung VARCHAR(45),
  kosten VARCHAR(45),
  PRIMARY KEY (vid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblZahlungsart
-- -----------------------------------------------------
CREATE TABLE tblZahlungsart (
  zid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  PRIMARY KEY (zid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblRechnung
-- -----------------------------------------------------
CREATE TABLE tblRechnung (
  rid INT NOT NULL AUTO_INCREMENT,
  datum DATE,
  vid INT,
  zid INT,
  kid INT,
  PRIMARY KEY (rid),
  INDEX fk_tblRechnung_tblVersand1_idx (vid ASC),
  INDEX fk_tblRechnung_tblZahlungsart1_idx (zid ASC),
  INDEX fk_tblRechnung_tblKunde1_idx (kid ASC),
  CONSTRAINT fk_tblRechnung_tblVersand1
    FOREIGN KEY (vid)
    REFERENCES tblVersand (vid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_tblZahlungsart1
    FOREIGN KEY (zid)
    REFERENCES tblZahlungsart (zid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_tblKunde1
    FOREIGN KEY (kid)
    REFERENCES tblKunde (kid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblRechnung_has_tblArtikel
-- -----------------------------------------------------
CREATE TABLE tblRechnung_has_tblArtikel (
  a2rid INT NOT NULL AUTO_INCREMENT,
  rid INT NOT NULL,
  aid INT NOT NULL,
  INDEX fk_tblRechnung_has_tblArtikel_tblArtikel1_idx (aid ASC),
  INDEX fk_tblRechnung_has_tblArtikel_tblRechnung1_idx (rid ASC),
  PRIMARY KEY (a2rid),
  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblRechnung1
    FOREIGN KEY (rid)
    REFERENCES tblRechnung (rid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblArtikel1
    FOREIGN KEY (aid)
    REFERENCES tblArtikel (aid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;