zCAmnE0s

1OLLATE utf8_slovak_ci
2-- Database: `telzoznam`
3--
4
5-- --------------------------------------------------------
6
7--
8-- Table structure for table `adresa`
9--
10
11CREATE TABLE IF NOT EXISTS `adresa` (
12  `id_adresa` int(10) NOT NULL AUTO_INCREMENT,
13  `id_obec` int(10) NOT NULL,
14  `ulica` varchar(50) COLLATE utf8_slovak_ci NOT NULL,
15  `cislo_domu` int(10) NOT NULL,
16  PRIMARY KEY (`id_adresa`),
17  UNIQUE KEY `id_obec` (`id_obec`,`ulica`,`cislo_domu`),
18  FOREIGN KEY `FK_adr_obec` (`id_obec`) REFERENCES obec(id_obec) 
19)  ;
20
21-- --------------------------------------------------------
22
23--
24-- Table structure for table `cislo`
25--
26
27CREATE TABLE IF NOT EXISTS `cislo` (
28  `id_cislo` int(11) NOT NULL AUTO_INCREMENT,
29  `tel_cislo` int(10) NOT NULL,
30  `id_osoba` int(10) NOT NULL,
31  PRIMARY KEY (`id_cislo`),
32  UNIQUE KEY `tel_cislo` (`tel_cislo`),
33  FOREIGN KEY `FK_cis_oso` (`id_osoba`) REFERENCES osoba(id_osoba) 
34)  ;
35
36-- --------------------------------------------------------
37
38--
39-- Table structure for table `obec`
40--
41
42CREATE TABLE IF NOT EXISTS `obec` (
43  `id_obec` int(10) NOT NULL AUTO_INCREMENT,
44  `obec` varchar(40) COLLATE utf8_slovak_ci NOT NULL,
45  `psc` int(5) NOT NULL,
46  PRIMARY KEY (`id_obec`),
47  UNIQUE KEY `obec` (`obec`,`psc`)
48) ;
49
50--
51-- Dumping data for table `obec`
52--
53
54INSERT INTO `obec` (`id_obec`, `obec`, `psc`) VALUES
55( 'Bardejov', 8501),
56( 'Bardejov', 8502),
57( 'Slatvina', 5361);
58
59-- --------------------------------------------------------
60
61--
62-- Table structure for table `osoba`
63--
64
65CREATE TABLE IF NOT EXISTS `osoba` (
66  `id_osoba` int(10) NOT NULL AUTO_INCREMENT,
67  `id_adresa` int(10) NOT NULL,
68  `meno` varchar(15)  NOT NULL,
69  `priezvisko` varchar(15)  NOT NULL,
70  PRIMARY KEY (`id_osoba`),
71  FOREIGN KEY `FK_oso_adr` (`id_adresa`) REFERENCES adresa(id_adresa)
72) ;
73
74-- --------------------------------------------------------
75
76--
77-- Stand-in structure for view `v_vyhladaj`
78--
79CREATE TABLE IF NOT EXISTS `v_vyhladaj` (
80`obec` varchar(40)
81,`psc` int(5)
82,`id_obec` int(10)
83,`id_adresa` int(10)
84,`ulica` varchar(50)
85,`cislo_domu` int(10)
86,`id_osoba` int(10)
87,`meno` varchar(15)
88,`priezvisko` varchar(15)
89,`id_cislo` int(11)
90,`tel_cislo` int(10)
91);
92-- --------------------------------------------------------
93
94--
95-- Structure for view `v_vyhladaj`
96--
97DROP TABLE IF EXISTS `v_vyhladaj`;
98
99CREATE ALGORITHM=UNDEFINED DEFINER=`teluser`@`147.232.179.179` SQL SECURITY DEFINER VIEW `v_vyhladaj` AS select `obec`.`obec` AS `obec`,`obec`.`psc` AS `psc`,`obec`.`id_obec` AS `id_obec`,`adresa`.`id_adresa` AS `id_adresa`,`adresa`.`ulica` AS `ulica`,`adresa`.`cislo_domu` AS `cislo_domu`,`osoba`.`id_osoba` AS `id_osoba`,`osoba`.`meno` AS `meno`,`osoba`.`priezvisko` AS `priezvisko`,`cislo`.`id_cislo` AS `id_cislo`,`cislo`.`tel_cislo` AS `tel_cislo` from (((`obec` join `adresa`) join `osoba`) join `cislo`) where ((`adresa`.`id_obec` = `obec`.`id_obec`) and (`osoba`.`id_adresa` = `adresa`.`id_adresa`) and (`cislo`.`id_osoba` = `osoba`.`id_osoba`));