· 9 years ago · Feb 14, 2017, 01:48 PM
1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3
4/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
5/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
6/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
7/*!40101 SET NAMES utf8mb4 */;
8
9CREATE DATABASE IF NOT EXISTS `festivali` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
10USE `festivali`;
11
12DROP TABLE IF EXISTS `festival`;
13CREATE TABLE IF NOT EXISTS `festival` (
14 `idFes` bigint(20) NOT NULL,
15 `naziv` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
16 `datumOd` date NOT NULL,
17 `datumDo` date NOT NULL,
18 `mesto` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
19 `detalji` text COLLATE utf8_unicode_ci NOT NULL,
20 `kapacitetPoDanu` int(11) NOT NULL,
21 `maxKarataPoKorisniku` int(11) NOT NULL,
22 `cenaPaket` int(11) NOT NULL,
23 `cenaDan` int(11) NOT NULL,
24 `pregledi` bigint(20) NOT NULL DEFAULT '0',
25 `facebook` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
26 `twitter` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
27 `instagram` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
28 `youtube` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
29 `otkazano` tinyint(1) NOT NULL DEFAULT '0',
30 `brojKupljenih` int(11) NOT NULL DEFAULT '0'
31) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
32
33DROP TABLE IF EXISTS `galerija`;
34CREATE TABLE IF NOT EXISTS `galerija` (
35 `idGal` bigint(20) NOT NULL,
36 `idFes` bigint(20) NOT NULL,
37 `putanja` varchar(511) COLLATE utf8_unicode_ci NOT NULL,
38 `tip` int(11) NOT NULL
39) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
40
41DROP TABLE IF EXISTS `korisnik`;
42CREATE TABLE IF NOT EXISTS `korisnik` (
43 `idKor` bigint(20) NOT NULL,
44 `ime` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
45 `prezime` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
46 `username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
47 `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
48 `telefon` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
49 `email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
50 `admin` tinyint(1) NOT NULL DEFAULT '0',
51 `odobren` tinyint(1) NOT NULL DEFAULT '0',
52 `poslednjaPrijava` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
53 `brojUpozorenja` int(11) NOT NULL DEFAULT '0'
54) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
55
56INSERT INTO `korisnik` (`idKor`, `ime`, `prezime`, `username`, `password`, `telefon`, `email`, `admin`, `odobren`, `poslednjaPrijava`, `brojUpozorenja`) VALUES
57(1, 'Ana', 'Pesko', 'peska', 'Sifra123', '0601234567', 'ana.pesko@yahoo.com', 1, 1, '2017-02-13 16:17:37', 0),
58(2, 'Petar', 'Djukic', 'petar', 'Sifra123', '302390293', 'petar@petar.com', 0, 0, '2017-02-13 19:01:02', 0),
59(3, 'Branislava', 'Ivkovic', 'brana', 'Sifra123', '089298129', 'brana@yahoo.com', 0, 0, '2017-02-13 19:05:36', 0),
60(4, 'Drazen', 'Draskovic', 'drasko', 'Sifra123', '4983204', 'drazen@etf.rs', 0, 0, '2017-02-13 19:08:55', 0),
61(5, 'Nikola', 'Djukic', 'nikola', 'Sifra123', '9302', 'nikola@guru.com', 0, 0, '2017-02-13 19:13:37', 0),
62(6, 'Sanja', 'Delcev', 'sanja', 'Sifra123', '3483', 'sanjad@etf.rs', 0, 0, '2017-02-13 19:18:50', 0),
63(7, 'Sanja', 'Delcev', 'sanja5', 'Sifra123', '392083', 'sanjad@etf.rs', 0, 0, '2017-02-13 19:36:26', 0),
64(8, 'Levi', 'Account', 'potpuno', 'Sifra123', '493284091', 'levi@account.potpuno', 0, 0, '2017-02-13 19:39:11', 0),
65(9, 'Korisnik', 'Korisnik', 'korisnik', 'Sifra123', '49304812', 'nebitno@gmail.com', 0, 0, '2017-02-13 19:43:14', 0);
66
67DROP TABLE IF EXISTS `nastup`;
68CREATE TABLE IF NOT EXISTS `nastup` (
69 `idNas` bigint(20) NOT NULL,
70 `izvodjac` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
71 `datumOd` date NOT NULL,
72 `datumDo` date NOT NULL,
73 `vremeOd` time NOT NULL,
74 `vremeDo` time NOT NULL,
75 `idFes` bigint(20) NOT NULL,
76 `otkazano` tinyint(1) NOT NULL DEFAULT '0'
77) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
78
79DROP TABLE IF EXISTS `notifikacija`;
80CREATE TABLE IF NOT EXISTS `notifikacija` (
81 `idNot` bigint(20) NOT NULL,
82 `idKor` bigint(20) NOT NULL,
83 `tekst` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
84 `crniSinGrehMePrati` tinyint(1) NOT NULL DEFAULT '0'
85) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
86
87DROP TABLE IF EXISTS `ocena`;
88CREATE TABLE IF NOT EXISTS `ocena` (
89 `idOce` bigint(20) NOT NULL,
90 `idKor` bigint(20) NOT NULL,
91 `idFes` bigint(20) NOT NULL,
92 `ocena` int(11) NOT NULL,
93 `komentar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
94 `imeFestivala` varchar(45) COLLATE utf8_unicode_ci NOT NULL
95) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
96
97DROP TABLE IF EXISTS `ulaznica`;
98CREATE TABLE IF NOT EXISTS `ulaznica` (
99 `idUla` bigint(20) NOT NULL,
100 `idFes` bigint(20) NOT NULL,
101 `datumNastupa` date DEFAULT NULL,
102 `idKor` bigint(20) NOT NULL,
103 `kolicina` int(11) NOT NULL,
104 `vremeKreiranja` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
105 `prodato` tinyint(1) NOT NULL DEFAULT '0',
106 `isteklo` tinyint(1) NOT NULL DEFAULT '0'
107) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
108
109
110ALTER TABLE `festival`
111 ADD PRIMARY KEY (`idFes`);
112
113ALTER TABLE `galerija`
114 ADD PRIMARY KEY (`idGal`),
115 ADD KEY `idFes` (`idFes`);
116
117ALTER TABLE `korisnik`
118 ADD PRIMARY KEY (`idKor`),
119 ADD UNIQUE KEY `username` (`username`);
120
121ALTER TABLE `nastup`
122 ADD PRIMARY KEY (`idNas`),
123 ADD KEY `idFes` (`idFes`);
124
125ALTER TABLE `notifikacija`
126 ADD PRIMARY KEY (`idNot`),
127 ADD KEY `idKor` (`idKor`);
128
129ALTER TABLE `ocena`
130 ADD PRIMARY KEY (`idOce`),
131 ADD KEY `idKor` (`idKor`),
132 ADD KEY `idFes` (`idFes`);
133
134ALTER TABLE `ulaznica`
135 ADD PRIMARY KEY (`idUla`),
136 ADD KEY `idFes` (`idFes`),
137 ADD KEY `idKor` (`idKor`);
138
139
140ALTER TABLE `festival`
141 MODIFY `idFes` bigint(20) NOT NULL AUTO_INCREMENT;
142ALTER TABLE `galerija`
143 MODIFY `idGal` bigint(20) NOT NULL AUTO_INCREMENT;
144ALTER TABLE `korisnik`
145 MODIFY `idKor` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
146ALTER TABLE `nastup`
147 MODIFY `idNas` bigint(20) NOT NULL AUTO_INCREMENT;
148ALTER TABLE `notifikacija`
149 MODIFY `idNot` bigint(20) NOT NULL AUTO_INCREMENT;
150ALTER TABLE `ocena`
151 MODIFY `idOce` bigint(20) NOT NULL AUTO_INCREMENT;
152ALTER TABLE `ulaznica`
153 MODIFY `idUla` bigint(20) NOT NULL AUTO_INCREMENT;
154
155ALTER TABLE `galerija`
156 ADD CONSTRAINT `galerija_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);
157
158ALTER TABLE `nastup`
159 ADD CONSTRAINT `nastup_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);
160
161ALTER TABLE `notifikacija`
162 ADD CONSTRAINT `notifikacija_ibfk_1` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`);
163
164ALTER TABLE `ocena`
165 ADD CONSTRAINT `ocena_ibfk_1` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`),
166 ADD CONSTRAINT `ocena_ibfk_2` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);
167
168ALTER TABLE `ulaznica`
169 ADD CONSTRAINT `ulaznica_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`),
170 ADD CONSTRAINT `ulaznica_ibfk_2` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`);
171
172/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
173/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
174/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;