· 6 years ago · Jan 18, 2020, 05:42 PM
1DROP DATABASE IF EXISTS jedalen;
2CREATE DATABASE jedalen CHARACTER SET utf8 COLLATE utf8_SLOVAK_ci;
3
4USE jedalen;
5
6CREATE TABLE pohlavie(
7 IDpohlavie int AUTO_INCREMENT PRIMARY KEY,
8 pohlavie varchar(4) NOT NULL
9)ENGINE = InnoDB;
10
11CREATE TABLE rocnik(
12 IDrocnik int AUTO_INCREMENT PRIMARY KEY,
13 rocnik int NOT NULL
14)ENGINE = InnoDB;
15
16CREATE TABLE mesto(
17 IDmesto int AUTO_INCREMENT PRIMARY KEY,
18 mesto varchar(30) NOT NULL
19)ENGINE = InnoDB;
20
21CREATE TABLE polievka(
22 IDpolievka int AUTO_INCREMENT PRIMARY KEY,
23 polievka varchar(30) NOT NULL
24)ENGINE = InnoDB;
25
26CREATE TABLE hljedlo(
27 IDhljedlo int AUTO_INCREMENT PRIMARY KEY,
28 hljedlo varchar(30) NOT NULL
29)ENGINE = InnoDB;
30
31CREATE TABLE dezert(
32 IDdezert int AUTO_INCREMENT PRIMARY KEY,
33 dezert varchar(30) NOT NULL
34)ENGINE = InnoDB;
35
36CREATE TABLE hodnota_polievka(
37 IDhodnota_polievka int AUTO_INCREMENT PRIMARY KEY,
38 hodnota_polievka int NOT NULL
39)ENGINE = InnoDB;
40
41CREATE TABLE hodnota_hljedlo(
42 IDhodnota_hljedlo int AUTO_INCREMENT PRIMARY KEY,
43 hodnota_hljedlo int NOT NULL
44)ENGINE = InnoDB;
45
46CREATE TABLE hodnota_dezert(
47 IDhodnota_dezert int AUTO_INCREMENT PRIMARY KEY,
48 hodnota_dezert int NOT NULL
49)ENGINE = InnoDB;
50
51CREATE TABLE ziak(
52 IDziak int AUTO_INCREMENT PRIMARY KEY,
53 IDcip int NOT NULL,
54 meno varchar(30) NOT NULL,
55 priezvisko varchar(30) NOT NULL,
56 IDpohlavie int NOT NULL,
57 IDrocnik int NOT NULL,
58 adresa varchar(30) NOT NULL,
59 IDmesto int NOT NULL,
60 email varchar(30) NOT NULL,
61 FOREIGN KEY (IDmesto) REFERENCES mesto(IDmesto),
62 FOREIGN KEY (IDrocnik) REFERENCES rocnik(IDrocnik),
63 FOREIGN KEY (IDpohlavie) REFERENCES pohlavie(IDpohlavie)
64)ENGINE = InnoDB;
65
66CREATE TABLE obed(
67 IDobed int AUTO_INCREMENT PRIMARY KEY,
68 IDpolievka int NOT NULL,
69 IDhljedlo int NOT NULL,
70 IDdezert int NOT NULL,
71 datum date NOT NULL,
72 FOREIGN KEY (IDpolievka) REFERENCES polievka(IDpolievka),
73 FOREIGN KEY (IDhljedlo) REFERENCES hljedlo(IDhljedlo),
74 FOREIGN KEY (IDdezert) REFERENCES dezert(IDdezert)
75)ENGINE = InnoDB;
76
77CREATE TABLE hodnotenie(
78 IDziak int NOT NULL,
79 IDobed int NOT NULL,
80 IDhodnota_polievka int NOT NULL,
81 IDhodnota_hljedlo int NOT NULL,
82 IDhodnota_dezert int NOT NULL,
83 datum date NOT NULL DEFAULT CURRENT_TIMESTAMP,
84 PRIMARY KEY (IDziak,IDobed),
85 FOREIGN KEY (IDziak) REFERENCES ziak(IDziak),
86 FOREIGN KEY (IDobed) REFERENCES obed(IDobed),
87 FOREIGN KEY (IDhodnota_polievka) REFERENCES hodnota_polievka(IDhodnota_polievka),
88 FOREIGN KEY (IDhodnota_hljedlo) REFERENCES hodnota_hljedlo(IDhodnota_hljedlo),
89 FOREIGN KEY (IDhodnota_dezert) REFERENCES hodnota_dezert(IDhodnota_dezert)
90)ENGINE = InnoDB;
91
92INSERT INTO pohlavie VALUES
93 (0,"Muž"),
94 (0,"Žena");
95
96INSERT INTO rocnik VALUES
97 (0,1),
98 (0,2),
99 (0,3),
100 (0,4);
101
102INSERT INTO mesto VALUES
103 (0,"Košice"),
104 (0,"Prešov"),
105 (0,"Moldava nad Bodvou"),
106 (0,"Trebišov"),
107 (0,"Humenné"),
108 (0,"Rožňava"),
109 (0,"Gelnica"),
110 (0,"Spišská Nová Ves");
111
112INSERT INTO polievka VALUES
113 (0,"Zeleninová"),
114 (0,"Hrášková"),
115 (0,"Šošovicová"),
116 (0,"Guláš"),
117 (0,"Brokolicová"),
118 (0,"Karfiolová"),
119 (0,"Špenátová"),
120 (0,"Cesnaková"),
121 (0,"Vajíčková"),
122 (0,"Paradajková");
123
124INSERT INTO hljedlo VALUES
125 (0,"Zemiakové fliačky"),
126 (0,"Rezeň so zemiakmi"),
127 (0,"Lolos so zeleninou"),
128 (0,"Vyprážaný syr so zemiakmi"),
129 (0,"Steak so zemiakmi a zeleninou"),
130 (0,"Palacinky s lekvárom"),
131 (0,"Cestovina s tvarohom"),
132 (0,"Kuracie mäso na kocky"),
133 (0,"Buchty na pare"),
134 (0,"Pečené buchty s lekvárom");
135
136INSERT INTO dezert VALUES
137 (0,"Jablkový kompót"),
138 (0,"Šalát"),
139 (0,"Jahodový puding"),
140 (0,"Marhuľový kompót"),
141 (0,"Džús"),
142 (0,"Výživa"),
143 (0,"Jogurt"),
144 (0,"Cvikla"),
145 (0,"Pomaranč"),
146 (0,"Čokoládové mlieko");
147
148INSERT INTO hodnota_polievka VALUES
149 (0,1),
150 (0,2),
151 (0,3),
152 (0,4),
153 (0,5);
154
155INSERT INTO hodnota_hljedlo VALUES
156 (0,1),
157 (0,2),
158 (0,3),
159 (0,4),
160 (0,5);
161
162INSERT INTO hodnota_dezert VALUES
163 (0,1),
164 (0,2),
165 (0,3),
166 (0,4),
167 (0,5);
168
169INSERT INTO ziak VALUES
170 (0,1151,"Janko","Hraško",1,1,"Trieda SNP 51",1,"hrasko@azet.sk"),
171 (0,5358,"Ivan","Veľký",1,3,"Zimná 42",3,"ivan.velky@gmail.com"),
172 (0,2410,"Mirka","Skalská",2,3,"Považská 12",8,"skalska@centrum.sk"),
173 (0,3086,"Marek","Jelínek",1,4,"Jarmočná 32",6,"marekjelinek@azet.sk"),
174 (0,4428,"Helena","Matná",2,2,"Kováčska 2",4,"matna@gmail.com"),
175 (0,7043,"Ivana","Plačná",2,1,"Svätoplukova 87",7,"placnaivana@centrum.sk"),
176 (0,8469,"Peter","Andrášči",1,2,"Moskovská 65",1,"andrasci@gmail.com"),
177 (0,9721,"Samuel","Čistý",1,3,"Ostrá 44",2,"samuelcisty@centrum.sk"),
178 (0,5425,"Anna","Olivová",2,4,"Tajomná 28",5,"anna@gmail.com"),
179 (0,6641,"Natália","Franská",2,1,"Letecká 54",3,"franska@centrum.sk"),
180 (0,1845,"Oliver","Jalský",1,4,"Nižná 10",4,"jalskyoliver@azet.sk"),
181 (0,3588,"Renáta","Blažná",2,2,"Chminianská 72",6,"blazna@centrum.sk"),
182 (0,2591,"Pavel","Enderson",1,3,"Popradská",7,"enderson@gmail.com"),
183 (0,6756,"Marek","Známy",1,2,"Lesklá 6",8,"znamymarek@gmail.com"),
184 (0,9572,"Emil","Klzký",1,1,"Pepečná 84",5,"klzky@azet.sk"),
185 (0,8850,"Natália","Lesná",2,4,"Ovesná 38",4,"lesnanatalia@azet.sk"),
186 (0,9232,"Erik","Mastný",1,4,"Renátska 75",3,"mastny@gmail.com"),
187 (0,2507,"Oliver","Gerušin",1,3,"Indická 40",1,"gerusinoliver@centrum.sk"),
188 (0,6527,"Vivien","Jasná",2,1,"Hradná 68",1,"jasna@gmail.com"),
189 (0,1785,"Róbert","Denko",1,2,"Senecká cesta 41",2,"denko@azet.sk");
190
191INSERT INTO obed VALUES
192 (0,1,5,6,"2020-01-01"),
193 (0,4,8,2,"2020-01-05"),
194 (0,3,4,4,"2020-01-04"),
195 (0,10,5,3,"2020-01-02"),
196 (0,7,6,8,"2020-01-08"),
197 (0,2,5,9,"2020-01-10"),
198 (0,7,3,8,"2020-01-09"),
199 (0,8,10,1,"2020-01-03"),
200 (0,9,1,7,"2020-01-07"),
201 (0,5,5,10,"2020-01-06");
202
203INSERT INTO hodnotenie VALUES
204 (15,4,1,2,3,"2020-01-01"),
205 (2,4,3,5,4,"2020-01-01"),
206 (10,4,4,1,2,"2020-01-01"),
207 (11,4,5,2,2,"2020-01-01"),
208 (1,8,2,4,3,"2020-01-02"),
209 (3,8,3,1,1,"2020-01-02"),
210 (5,1,2,3,4,"2020-01-03"),
211 (2,3,1,1,2,"2020-01-04"),
212 (10,3,5,5,4,"2020-01-04"),
213 (8,10,4,3,2,"2020-01-05");