· 7 years ago · Dec 11, 2018, 01:56 PM
1DROP DATABASE IF EXISTS testdb;
2
3CREATE DATABASE testdb;
4
5USE testdb;
6
7CREATE TABLE sammelstelle
8 (
9 sland VARCHAR(100) NOT NULL,
10 snr CHAR(3) NOT NULL,
11 sort VARCHAR(100) NOT NULL,
12 PRIMARY KEY (snr)
13 );
14
15CREATE TABLE fabrik
16 (
17 fnr CHAR(3) NOT NULL,
18 fland VARCHAR(100) NOT NULL,
19 fort VARCHAR(100) NOT NULL,
20 PRIMARY KEY (fnr)
21 );
22
23CREATE TABLE kaffeesorte
24 (
25 knr CHAR(3) NOT NULL,
26 kname VARCHAR(100) NOT NULL,
27 PRIMARY KEY (knr)
28 );
29
30
31
32CREATE TABLE abgabemoeglich
33 (
34 snr CHAR(3) NOT NULL,
35 knr CHAR(3) NOT NULL,
36 PRIMARY KEY (snr, knr),
37 FOREIGN KEY (snr ) REFERENCES sammelstelle (snr),
38 FOREIGN KEY (knr ) REFERENCES kaffeesorte (knr)
39 );
40
41CREATE TABLE lieferungmoeglich
42 (
43 snr CHAR(3) NOT NULL,
44 knr CHAR(3) NOT NULL,
45 fnr CHAR(3) NOT NULL,
46 PRIMARY KEY (snr, knr, fnr),
47 FOREIGN KEY (snr ) REFERENCES sammelstelle (snr),
48 FOREIGN KEY (knr ) REFERENCES kaffeesorte (knr),
49 FOREIGN KEY (fnr ) REFERENCES fabrik (fnr )
50 );
51
52 CREATE TABLE lieferung
53 (
54 snr CHAR(3) NOT NULL,
55 fnr VARCHAR(3) NOT NULL,
56 knr VARCHAR(3) NOT NULL,
57 lnr VARCHAR(3) NOT NULL,
58 lanzkg VARCHAR(100) NOT NULL,
59 ldatum DATE NOT NULL,
60 UNIQUE KEY (snr, fnr, knr, lnr),
61 FOREIGN KEY (snr,knr,fnr) REFERENCES lieferungmoeglich (snr,knr,fnr)
62 );
63
64CREATE TABLE abgabe
65 (
66 snr CHAR(3) NOT NULL,
67 knr CHAR(3) NOT NULL,
68 anr CHAR(3) NOT NULL,
69 adatum DATE NOT NULL,
70 aanzkg DECIMAL(10, 3) NOT NULL,
71 UNIQUE KEY (snr, knr, anr),
72 FOREIGN KEY (snr,knr) REFERENCES Abgabemoeglich (snr,knr)
73 );
74
75 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('1','ch','zh');
76 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('2','ch','bs');
77 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('3','ch','bl');
78 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('4','ch','ag');
79 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('5','br','lon');
80 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('6','it','rom');
81 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('7','de','brl');
82 INSERT INTO Sammelstelle (SNr, SLand, SOrt ) VALUES ('8','de','stutt');
83
84 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('1','ch','zh');
85 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('2','ch','bs');
86 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('3','ch','bl');
87 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('4','ch','ag');
88 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('5','br','lon');
89 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('6','it','rom');
90 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('7','de','brl');
91 INSERT INTO Fabrik (FNr, Fland, Fort ) VALUES ('8','de','stutt');
92
93 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('1','q');
94 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('2','w');
95 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('3','e');
96 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('4','r');
97 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('5','r');
98 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('6','t');
99 INSERT INTO KaffeeSorte (KNr, Kname ) VALUES ('7','z');
100
101 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('1','2','2');
102 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('5','5','5');
103 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','2','1');
104 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('6','6','3');
105 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('8','7','4');
106 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('7','4','5');
107 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('5','1','6');
108 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('2','3','7');
109 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('1','6','7');
110
111 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','1','4');
112 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','2','4');
113 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','3','4');
114 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','4','4');
115 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','5','4');
116 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','6','4');
117 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','7','4');
118 INSERT INTO Lieferungmoeglich (SNr, FNr , KNr ) VALUES ('3','8','4');
119
120
121
122 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','1','4', '1' ,'14' ,'1945-01-01');
123 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','2','4', '4' ,'14' ,'1945-01-01');
124 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','3','4', '4' ,'14' ,'1945-01-01');
125 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','4','4', '4' ,'14' ,'1945-01-01');
126 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','5','4', '4' ,'14' ,'1945-01-01');
127 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','6','4', '4' ,'14' ,'1945-01-01');
128 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','7','4', '4' ,'14' ,'1945-01-01');
129 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('3','8','4', '4' ,'14' ,'1945-01-01');
130
131
132 INSERT INTO Lieferung (SNr, FNr , KNr , LNr , LAnzKg ,LDatum ) VALUES ('1','2','2', '2' ,'14' ,'1945-01-01');
133 INSERT INTO Lieferung (SNr, FNr , KNr , LNr , LAnzKg ,LDatum) VALUES ('5','5','5','3' ,'13' ,'1946-01-01');
134 INSERT INTO Lieferung(SNr, FNr , KNr , LNr , LAnzKg ,LDatum) VALUES ('6','6','3','5' ,'14' ,'1946-01-01');
135
136
137
138 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('7','2');
139 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('4','2');
140 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('5','1');
141 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('2','6');
142 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('3','5');
143 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('4','4');
144 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('8','3');
145 INSERT INTO Abgabemoeglich (SNr,KNr ) VALUES ('8','2');
146
147
148
149 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('7','2','1' ,'14' ,'1941-01-01');
150 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('4','2','2' ,'12' ,'1942-01-02');
151 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('5','1','3' ,'13' ,'1943-01-03');
152 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('2','6','4' ,'14' ,'1944-01-04');
153 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('3','5','5' ,'15' ,'1945-01-05');
154 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('4','4','6' ,'16' ,'1946-01-06');
155 INSERT INTO Abgabe (SNr, KNr ,ANr , AAnzKg ,ADatum ) VALUES ('8','3','1' ,'17' ,'1947-01-07');