jCLVJYmE

1/*DROP DATABASE Baufuchs;*/
2CREATE DATABASE IF NOT EXISTS Baufuchs;
3USE Baufuchs;
4
5CREATE TABLE regions (
6			region_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
7			region VARCHAR(10)
8);
9
10CREATE TABLE branch (
11			branch_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
12			name VARCHAR(20),
13			city VARCHAR(20),
14			housenumber VARCHAR(5),
15			street VARCHAR(20),
16			plz INTEGER(5),
17			region_id INTEGER(5),
18			FOREIGN KEY (region_id) REFERENCES regions(region_id) 
19);
20
21CREATE TABLE streets (
22			street_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
23			street VARCHAR(15)
24);
25
26CREATE TABLE citys (
27			city_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
28			city VARCHAR(15)
29);
30
31CREATE TABLE adress_customer (
32			adress_customer_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
33			street_id INTEGER(5),
34			city_id INTEGER(5),
35			plz INTEGER (5),
36			house_number VARCHAR(5),
37
38			FOREIGN KEY (street_id) REFERENCES streets(street_id),
39			FOREIGN KEY (city_id) REFERENCES citys (city_id)
40);
41
42CREATE TABLE adress_supplier (
43			adress_supplier_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
44			street_id INTEGER(5),
45			city_id INTEGER(5),
46			plz INTEGER(5),
47			house_number VARCHAR(5),
48			FOREIGN KEY (street_id)	REFERENCES streets (street_id),
49			FOREIGN KEY (city_id) REFERENCES citys (city_id)
50);
51
52CREATE TABLE customers (
53			customer_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT
54);
55
56CREATE TABLE suppliers (
57			supplier_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
58			name VARCHAR(20),
59			adress_supplier_id INTEGER(5),
60			FOREIGN KEY (adress_supplier_id) REFERENCES adress_supplier (adress_supplier_id)
61);
62
63CREATE TABLE articles (
64			article_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
65			article VARCHAR(20),
66			price Float (9),
67			supplier_id INTEGER(5),
68			region_id INTEGER(5),
69			quantity INTEGER(5),
70			FOREIGN (region_id) REFERENCES regions(region_id),
71			FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id)
72);
73
74CREATE TABLE bills (
75			bill_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
76			article_id INTEGER(5),
77			amount Float(10),
78			customer_id INTEGER(5),
79			FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
80			FOREIGN KEY (article_id) REFERENCES articles (article_id)
81);
82
83CREATE TABLE customercard (
84			customercard_id INTEGER(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
85			customer_id INTEGER(5),
86			givenname VARCHAR(10),
87			surname VARCHAR(10),
88			adress_customer_id INTEGER(5),
89			FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
90			FOREIGN KEY (adress_customer_id) REFERENCES adress_customer(adress_customer_id)
91);
92
93CREATE TABLE branch_selection (
94			branch_id INTEGER(5),
95			article_id INTEGER(5),
96			PRIMARY KEY (branch_id, article_id),
97			FOREIGN KEY (article_id) REFERENCES articles(article_id),
98			FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
99);
100
101CREATE TABLE customer_buys ( 
102			customer_id INTEGER(5),
103			article_id INTEGER(5),
104			PRIMARY KEY (customer_id, article_id),
105			FOREIGN KEY (article_id) REFERENCES articles(article_id),
106			FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
107);
108
109CREATE TABLE branch_order ( 
110			branch_id INTEGER(5),
111			supplier_id INTEGER(5),
112			PRIMARY KEY (branch_id, supplier_id),
113			FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
114			FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
115);