S2GQPJB7

1-- phpMyAdmin SQL Dump
2-- version 3.5.1
3-- http://www.phpmyadmin.net
4--
5-- Hostiteľ: localhost
6-- Vygenerované: Út 22.Okt 2013, 20:34
7-- Verzia serveru: 5.5.24-log
8-- Verzia PHP: 5.3.13
9
10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12create database lekaren;
13use lekaren;
14
15
16/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
17/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
18/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
19/*!40101 SET NAMES utf8 */;
20
21--
22-- Databáza: `lekaren`
23--
24
25-- --------------------------------------------------------
26
27--
28-- Štruktúra tabuľky pre tabuľku `liek`
29--
30
31CREATE TABLE IF NOT EXISTS `liek` (
32  `id_liek` int(10) unsigned NOT NULL AUTO_INCREMENT,
33  `nazov` varchar(45) COLLATE utf8_slovak_ci NOT NULL,
34  `cena` float NOT NULL,
35  `typ` varchar(45) COLLATE utf8_slovak_ci DEFAULT NULL,
36  `datum_registracie` date DEFAULT NULL,
37  `vyrobca_id_vyrobca` int(10) unsigned NOT NULL,
38  PRIMARY KEY (`id_liek`),
39  UNIQUE KEY `id_liek_UNIQUE` (`id_liek`),
40  KEY `fk_liek_vyrobca1_idx` (`vyrobca_id_vyrobca`)
41) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=11 ;
42
43--
44-- Sťahujem dáta pre tabuľku `liek`
45--
46
47INSERT INTO `liek` (`id_liek`, `nazov`, `cena`, `typ`, `datum_registracie`, `vyrobca_id_vyrobca`) VALUES
48(1, 'Viagra', 23.2, 'vazodilatanciá', '2001-05-01', 5),
49(2, 'Vitamín C', 6.73, 'potravinové doplnky', '2005-11-29', 1),
50(3, 'Heparoid', 1.5, 'antitrombotiká', '2006-02-01', 1),
51(4, 'Voltaren', 3.6, 'analgetiká', '2008-07-15', 3),
52(5, 'Coldrex MaxGrip', 6, 'antipyretiká', '2002-03-04', 4),
53(6, 'Sinupret', 4.2, 'expektorans', '2003-06-18', 2),
54(7, 'Persen', 7.9, 'sedatíva', '2000-08-30', 3),
55(8, 'Calcium', 8, 'potravinové doplnky', '2008-04-30', 2),
56(9, 'Ibalgin', 1.4, 'analgetiká', '2007-12-02', 1),
57(10, 'Nurofen', 1.9, 'antiflogistiká', '2009-10-22', 5);
58
59-- --------------------------------------------------------
60
61--
62-- Štruktúra tabuľky pre tabuľku `pacient`
63--
64
65CREATE TABLE IF NOT EXISTS `pacient` (
66  `id_pacient` int(10) unsigned NOT NULL AUTO_INCREMENT,
67  `meno` varchar(30) COLLATE utf8_slovak_ci NOT NULL,
68  `priezvisko` varchar(30) COLLATE utf8_slovak_ci NOT NULL,
69  `datum_nar` date NOT NULL,
70  `poistovna_id_poistovna` int(10) unsigned NOT NULL,
71  PRIMARY KEY (`id_pacient`),
72  UNIQUE KEY `id_pacient_UNIQUE` (`id_pacient`),
73  KEY `fk_pacient_poistovna_idx` (`poistovna_id_poistovna`)
74) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=11 ;
75
76--
77-- Sťahujem dáta pre tabuľku `pacient`
78--
79
80INSERT INTO `pacient` (`id_pacient`, `meno`, `priezvisko`, `datum_nar`, `poistovna_id_poistovna`) VALUES
81(1, 'Peter', 'Malý', '1980-11-29', 5),
82(2, 'Ján', 'Horvát', '1960-07-08', 1),
83(3, 'Pavol', 'Fekete', '1973-02-11', 4),
84(4, 'Jozef', 'Varga', '1977-05-01', 2),
85(5, 'Michal', 'Takáč', '1981-04-18', 3),
86(6, 'Milan', 'Čič', '1978-09-02', 2),
87(7, 'Tibor', 'Kováč', '1996-08-07', 4),
88(8, 'Alexander', 'Košický', '1983-11-02', 3),
89(9, 'Martin', 'Tóth', '1974-01-05', 1),
90(10, 'Mirko', 'Tancár', '1985-08-14', 5);
91
92-- --------------------------------------------------------
93
94--
95-- Štruktúra tabuľky pre tabuľku `pacient_has_liek`
96--
97
98CREATE TABLE IF NOT EXISTS `pacient_has_liek` (
99  `pacient_id_pacient` int(10) unsigned NOT NULL,
100  `liek_id_liek` int(10) unsigned NOT NULL,
101  `predpisany` date NOT NULL,
102  PRIMARY KEY (`pacient_id_pacient`,`liek_id_liek`),
103  KEY `fk_pacient_has_liek_liek1_idx` (`liek_id_liek`),
104  KEY `fk_pacient_has_liek_pacient1_idx` (`pacient_id_pacient`)
105) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci;
106
107--
108-- Sťahujem dáta pre tabuľku `pacient_has_liek`
109--
110
111INSERT INTO `pacient_has_liek` (`pacient_id_pacient`, `liek_id_liek`, `predpisany`) VALUES
112(1, 3, '2007-07-01'),
113(2, 2, '2008-08-17'),
114(2, 3, '2008-11-10'),
115(2, 7, '2005-11-14'),
116(2, 10, '2005-04-16'),
117(3, 1, '2004-01-08'),
118(3, 2, '2009-11-03'),
119(3, 3, '2005-08-17'),
120(3, 4, '2008-03-27'),
121(3, 7, '2003-07-18'),
122(3, 8, '2007-02-11'),
123(3, 10, '2009-06-15'),
124(4, 4, '2005-05-14'),
125(4, 5, '2007-03-23'),
126(4, 6, '2009-08-19'),
127(4, 8, '2002-02-27'),
128(4, 10, '2008-02-27'),
129(5, 1, '2003-09-17'),
130(5, 3, '2004-09-26'),
131(5, 6, '2009-05-24'),
132(5, 9, '2007-11-08'),
133(6, 1, '2005-10-15'),
134(6, 3, '2010-03-07'),
135(6, 7, '2009-03-05'),
136(6, 9, '2008-02-04'),
137(7, 2, '2004-09-14'),
138(7, 5, '2002-10-23'),
139(8, 2, '2008-11-26'),
140(8, 3, '2009-04-07'),
141(8, 6, '2003-03-03'),
142(8, 8, '2004-11-24'),
143(9, 3, '2008-08-25'),
144(9, 4, '2003-08-06'),
145(9, 9, '2005-10-09'),
146(10, 2, '2002-05-28'),
147(10, 3, '2007-02-09'),
148(10, 5, '2008-10-04'),
149(10, 6, '2010-08-16'),
150(10, 8, '2008-03-07'),
151(10, 9, '2008-07-05');
152
153-- --------------------------------------------------------
154
155--
156-- Štruktúra tabuľky pre tabuľku `poistovna`
157--
158
159CREATE TABLE IF NOT EXISTS `poistovna` (
160  `id_poistovna` int(10) unsigned NOT NULL AUTO_INCREMENT,
161  `nazov` varchar(45) COLLATE utf8_slovak_ci NOT NULL,
162  `adresa` varchar(45) COLLATE utf8_slovak_ci NOT NULL,
163  `pocet_pobociek` int(11) NOT NULL,
164  PRIMARY KEY (`id_poistovna`)
165) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=7 ;
166
167--
168-- Sťahujem dáta pre tabuľku `poistovna`
169--
170
171INSERT INTO `poistovna` (`id_poistovna`, `nazov`, `adresa`, `pocet_pobociek`) VALUES
172(1, 'Union', 'Bratislavská 1/A, Bratislava', 30),
173(2, 'Dôvera', 'Einsteinova 22, Bratislava', 50),
174(3, 'Appolo', 'Moyzesova 13, Košice', 17),
175(4, 'Všetobecná zdravotná poisťovňa', 'Pod záhradami 2/A, Bratislava', 62),
176(5, 'Vojenská zdravotná poisťovňa', 'Železničná 63, Košice', 5),
177(6, 'Spoločná zdravotná poisťovňa', 'Madridská 11, Prešov', 80);
178
179-- --------------------------------------------------------
180
181--
182-- Štruktúra tabuľky pre tabuľku `vyrobca`
183--
184
185CREATE TABLE IF NOT EXISTS `vyrobca` (
186  `id_vyrobca` int(10) unsigned NOT NULL AUTO_INCREMENT,
187  `nazov` varchar(45) COLLATE utf8_slovak_ci NOT NULL,
188  `adresa` varchar(45) COLLATE utf8_slovak_ci NOT NULL,
189  `pocet_zamestnancov` int(11) DEFAULT NULL,
190  PRIMARY KEY (`id_vyrobca`),
191  UNIQUE KEY `id_vyrobca_UNIQUE` (`id_vyrobca`)
192) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci AUTO_INCREMENT=6 ;
193
194--
195-- Sťahujem dáta pre tabuľku `vyrobca`
196--
197
198INSERT INTO `vyrobca` (`id_vyrobca`, `nazov`, `adresa`, `pocet_zamestnancov`) VALUES
199(1, 'Zentiva', 'Malostranská 6, Praha, ČR', 630),
200(2, 'GlaxoSmithKline', 'Galvaniho 7/A, Bratislava', 400),
201(3, 'Bayer', 'Obchodná 2, Bratislava', 470),
202(4, 'MedicProduct', 'Kpt. Nálepku 2, Lipany', 50),
203(5, 'Pfizer', 'Pribinova 25, Bratislava', 1200);
204
205--
206-- Obmedzenie pre exportované tabuľky
207--
208
209--
210-- Obmedzenie pre tabuľku `liek`
211--
212ALTER TABLE `liek`
213  ADD CONSTRAINT `fk_liek_vyrobca1` FOREIGN KEY (`vyrobca_id_vyrobca`) REFERENCES `vyrobca` (`id_vyrobca`) ON DELETE NO ACTION ON UPDATE NO ACTION;
214
215--
216-- Obmedzenie pre tabuľku `pacient`
217--
218ALTER TABLE `pacient`
219  ADD CONSTRAINT `fk_pacient_poistovna` FOREIGN KEY (`poistovna_id_poistovna`) REFERENCES `poistovna` (`id_poistovna`) ON DELETE NO ACTION ON UPDATE NO ACTION;
220
221--
222-- Obmedzenie pre tabuľku `pacient_has_liek`
223--
224ALTER TABLE `pacient_has_liek`
225  ADD CONSTRAINT `fk_pacient_has_liek_pacient1` FOREIGN KEY (`pacient_id_pacient`) REFERENCES `pacient` (`id_pacient`) ON DELETE NO ACTION ON UPDATE NO ACTION,
226  ADD CONSTRAINT `fk_pacient_has_liek_liek1` FOREIGN KEY (`liek_id_liek`) REFERENCES `liek` (`id_liek`) ON DELETE NO ACTION ON UPDATE NO ACTION;
227
228/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
229/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
230/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;