sm49Jssb

1DROP DATABASE IF EXISTS nbb;
2-- DROP DATABASE NBB;
3-- DROP DATABASE NBB;
4/* DROP DATABASE NBB; */
5
6CREATE DATABASE nbb;
7
8USE nbb;
9
10#SET utf8;
11
12-- ---------------------------------------------------
13-- Table tblKunde
14-- -----------------------------------------------------
15CREATE TABLE tblKunde 
16(
17  kid INT NOT NULL AUTO_INCREMENT,
18  name VARCHAR(45),
19  vorname VARCHAR(45),
20  strasse VARCHAR(45),
21  plz CHAR(5),
22  stadt VARCHAR(45),
23  email VARCHAR(45),
24  PRIMARY KEY (kid)
25) ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table tblArtikel
30-- -----------------------------------------------------
31CREATE TABLE tblArtikel (
32  aid INT NOT NULL AUTO_INCREMENT,
33  name VARCHAR(45),
34  beschreibung VARCHAR(255),
35  PRIMARY KEY (aid))
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table tblVersand
41-- -----------------------------------------------------
42CREATE TABLE tblVersand (
43  vid INT NOT NULL AUTO_INCREMENT,
44  name VARCHAR(45),
45  beschreibung VARCHAR(45),
46  kosten VARCHAR(45),
47  PRIMARY KEY (vid))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table tblZahlungsart
53-- -----------------------------------------------------
54CREATE TABLE tblZahlungsart (
55  zid INT NOT NULL AUTO_INCREMENT,
56  name VARCHAR(45),
57  PRIMARY KEY (zid))
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table tblRechnung
63-- -----------------------------------------------------
64CREATE TABLE tblRechnung (
65  rid INT NOT NULL AUTO_INCREMENT,
66  datum DATE,
67  vid INT,
68  zid INT,
69  kid INT,
70  PRIMARY KEY (rid),
71  INDEX fk_tblRechnung_tblVersand1_idx (vid ASC),
72  INDEX fk_tblRechnung_tblZahlungsart1_idx (zid ASC),
73  INDEX fk_tblRechnung_tblKunde1_idx (kid ASC),
74  CONSTRAINT fk_tblRechnung_tblVersand1
75    FOREIGN KEY (vid)
76    REFERENCES tblVersand (vid)
77    ON DELETE NO ACTION
78    ON UPDATE NO ACTION,
79  CONSTRAINT fk_tblRechnung_tblZahlungsart1
80    FOREIGN KEY (zid)
81    REFERENCES tblZahlungsart (zid)
82    ON DELETE NO ACTION
83    ON UPDATE NO ACTION,
84  CONSTRAINT fk_tblRechnung_tblKunde1
85    FOREIGN KEY (kid)
86    REFERENCES tblKunde (kid)
87    ON DELETE NO ACTION
88    ON UPDATE NO ACTION)
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table tblRechnung_has_tblArtikel
94-- -----------------------------------------------------
95CREATE TABLE tblRechnung_has_tblArtikel (
96  a2rid INT NOT NULL AUTO_INCREMENT,
97  rid INT NOT NULL,
98  aid INT NOT NULL,
99  INDEX fk_tblRechnung_has_tblArtikel_tblArtikel1_idx (aid ASC),
100  INDEX fk_tblRechnung_has_tblArtikel_tblRechnung1_idx (rid ASC),
101  PRIMARY KEY (a2rid),
102  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblRechnung1
103    FOREIGN KEY (rid)
104    REFERENCES tblRechnung (rid)
105    ON DELETE NO ACTION
106    ON UPDATE NO ACTION,
107  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblArtikel1
108    FOREIGN KEY (aid)
109    REFERENCES tblArtikel (aid)
110    ON DELETE NO ACTION
111    ON UPDATE NO ACTION)
112ENGINE = InnoDB;