· 6 years ago · Jan 15, 2020, 09:16 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 hodnota_polievka(
22 IDhodnota_polievka int AUTO_INCREMENT PRIMARY KEY,
23 hodnota_polievka int NOT NULL
24)ENGINE = InnoDB;
25
26CREATE TABLE hodnota_hljedlo(
27 IDhodnota_hljedlo int AUTO_INCREMENT PRIMARY KEY,
28 hodnota_hljedlo int NOT NULL
29)ENGINE = InnoDB;
30
31CREATE TABLE hodnota_dezert(
32 IDhodnota_dezert int AUTO_INCREMENT PRIMARY KEY,
33 hodnota_dezert int NOT NULL
34)ENGINE = InnoDB;
35
36CREATE TABLE ziak(
37 IDziak int AUTO_INCREMENT PRIMARY KEY,
38 IDcip int NOT NULL,
39 meno varchar(30) NOT NULL,
40 priezvisko varchar(30) NOT NULL,
41 IDpohlavie int NOT NULL,
42 IDrocnik int NOT NULL,
43 adresa varchar(30) NOT NULL,
44 IDmesto int NOT NULL,
45 email varchar(30) NOT NULL,
46 FOREIGN KEY (IDmesto) REFERENCES mesto(IDmesto),
47 FOREIGN KEY (IDrocnik) REFERENCES rocnik(IDrocnik),
48 FOREIGN KEY (IDpohlavie) REFERENCES pohlavie(IDpohlavie)
49)ENGINE = InnoDB;
50
51CREATE TABLE obed(
52 IDobed int AUTO_INCREMENT PRIMARY KEY,
53 polievka varchar(30),
54 hljedlo varchar(30),
55 dezert varchar(30)
56)ENGINE = InnoDB;
57
58CREATE TABLE hodnotenie(
59 IDziak int NOT NULL,
60 IDobed int NOT NULL,
61 IDhodnota_polievka int NOT NULL,
62 IDhodnota_hljedlo int NOT NULL,
63 IDhodnota_dezert int NOT NULL,
64 datum date NOT NULL DEFAULT CURRENT_TIMESTAMP,
65 PRIMARY KEY (IDziak,IDobed),
66 FOREIGN KEY (IDziak) REFERENCES ziak(IDziak),
67 FOREIGN KEY (IDobed) REFERENCES obed(IDobed),
68 FOREIGN KEY (IDhodnota_polievka) REFERENCES hodnota_polievka(IDhodnota_polievka),
69 FOREIGN KEY (IDhodnota_hljedlo) REFERENCES hodnota_hljedlo(IDhodnota_hljedlo),
70 FOREIGN KEY (IDhodnota_dezert) REFERENCES hodnota_dezert(IDhodnota_dezert)
71)ENGINE = InnoDB;
72
73INSERT INTO pohlavie VALUES
74 (0,"Muž"),
75 (0,"Žena");
76
77INSERT INTO rocnik VALUES
78 (0,1),
79 (0,2),
80 (0,3),
81 (0,4);
82
83INSERT INTO mesto VALUES
84 (0,"Košice"),
85 (0,"Prešov"),
86 (0,"Moldava nad Bodvou"),
87 (0,"Trebišov"),
88 (0,"Humenné"),
89 (0,"Rožňava"),
90 (0,"Gelnica"),
91 (0,"Spišská Nová Ves");
92
93INSERT INTO hodnota_polievka VALUES
94 (0,1),
95 (0,2),
96 (0,3),
97 (0,4),
98 (0,5);
99
100INSERT INTO hodnota_hljedlo VALUES
101 (0,1),
102 (0,2),
103 (0,3),
104 (0,4),
105 (0,5);
106
107INSERT INTO hodnota_dezert VALUES
108 (0,1),
109 (0,2),
110 (0,3),
111 (0,4),
112 (0,5);
113
114INSERT INTO ziak VALUES
115 (0,1151,"Janko","Hraško",1,1,"Trieda SNP 51",1,"hrasko@azet.sk"),
116 (0,5358,"Ivan","Veľký",1,3,"Zimná 42",3,"ivan.velky@gmail.com"),
117 (0,2410,"Mirka","Skalská",2,3,"Považská 12",8,"skalska@centrum.sk"),
118 (0,3086,"Marek","Jelínek",1,4,"Jarmočná 32",6,"marekjelinek@azet.sk"),
119 (0,4428,"Helena","Matná",2,2,"Kováčska 2",4,"matna@gmail.com"),
120 (0,7043,"Ivana","Plačná",2,1,"Svätoplukova 87",7,"placnaivana@centrum.sk"),
121 (0,8469,"Peter","Andrášči",1,2,"Moskovská 65",1,"andrasci@gmail.com"),
122 (0,9721,"Samuel","Čistý",1,3,"Ostrá 44",2,"samuelcisty@centrum.sk"),
123 (0,5425,"Anna","Olivová",2,4,"Tajomná 28",5,"anna@gmail.com"),
124 (0,6641,"Natália","Franská",2,1,"Letecká 54",3,"franska@centrum.sk"),
125 (0,1845,"Oliver","Jalský",1,4,"Nižná 10",4,"jalskyoliver@azet.sk"),
126 (0,3588,"Renáta","Blažná",2,2,"Chminianská 72",6,"blazna@centrum.sk"),
127 (0,2591,"Pavel","Enderson",1,3,"Popradská",7,"enderson@gmail.com"),
128 (0,6756,"Marek","Známy",1,2,"Lesklá 6",8,"znamymarek@gmail.com"),
129 (0,9572,"Emil","Klzký",1,1,"Pepečná 84",5,"klzky@azet.sk"),
130 (0,8850,"Natália","Lesná",2,4,"Ovesná 38",4,"lesnanatalia@azet.sk"),
131 (0,9232,"Erik","Mastný",1,4,"Renátska 75",3,"mastny@gmail.com"),
132 (0,2507,"Oliver","Gerušin",1,3,"Indická 40",1,"gerusinoliver@centrum.sk"),
133 (0,6527,"Vivien","Jasná",2,1,"Hradná 68",1,"jasna@gmail.com"),
134 (0,1785,"Róbert","Denko",1,2,"Senecká cesta 41",2,"denko@azet.sk");
135
136INSERT INTO obed VALUES
137 (0,"Polievka zeleninová","Zemiakové fliačky","Džús"),
138 (0,"Polievka zemiaková","Rezeň so zemiakmi","Mlieko"),
139 (0,"Polievka hubová","Ryba so zeleninou","šalát"),
140 (0,"Polievka paradajková","Vyprážaný syr so zemiakmi","Džús"),
141 (0,"Guláš","Steak so zemiakmi a zeleninou","voda"),
142 (0,"Polievka bryndzová","Palacinky s lekvárom, kompót","Lekvár - jahodový"),
143 (0,"Polievka karfiolová","Žemľovka","Jablko"),
144 (0,"Polievka brokolicová","Cestovina s tvarohom","Mlieko"),
145 (0,"Polievka syrová","Kuracie mäso na kocky","Šalát"),
146 (0,"Polievka šošovicová","Buchty na pare","Banán");
147
148INSERT INTO hodnotenie VALUES
149 (15,4,1,2,3,"2020-01-01"),
150 (2,4,3,5,4,"2020-01-01"),
151 (10,4,4,1,2,"2020-01-01"),
152 (11,4,5,2,2,"2020-01-01"),
153 (1,8,2,4,3,"2020-01-02"),
154 (3,8,3,1,1,"2020-01-02"),
155 (5,1,2,3,4,"2020-01-03"),
156 (2,3,1,1,2,"2020-01-04"),
157 (10,3,5,5,4,"2020-01-04"),
158 (8,10,4,3,2,"2020-01-05");