-
Notifications
You must be signed in to change notification settings - Fork 0
/
baza.sql
115 lines (90 loc) · 4.36 KB
/
baza.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
/*
SQLyog Community v13.2.1 (64 bit)
MySQL - 10.4.28-MariaDB : Database - spasilacka_sluzba
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`spasilacka_sluzba` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `spasilacka_sluzba`;
/*Table structure for table `koordinator` */
DROP TABLE IF EXISTS `koordinator`;
CREATE TABLE `koordinator` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`korisnickoIme` varchar(255) NOT NULL,
`lozinka` varchar(255) NOT NULL,
`ime` varchar(255) NOT NULL,
`prezime` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Data for the table `koordinator` */
insert into `koordinator`(`id`,`korisnickoIme`,`lozinka`,`ime`,`prezime`) values
(1,'admin','admin','Admin','Adminovic');
/*Table structure for table `spasilac` */
DROP TABLE IF EXISTS `spasilac`;
CREATE TABLE `spasilac` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ime` varchar(255) NOT NULL,
`prezime` varchar(255) NOT NULL,
`jmbg` varchar(13) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `jmbg` (`jmbg`),
CHECK (octet_length(`jmbg`) = 13 and `jmbg` regexp '^[0-9]+$')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Table structure for table `smena` */
DROP TABLE IF EXISTS `smena`;
CREATE TABLE `smena` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pocetak` int(11) NOT NULL,
`kraj` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`pocetak`,`kraj`),
CONSTRAINT `CONSTRAINT_1` CHECK (`pocetak` < `kraj`),
CONSTRAINT `CONSTRAINT_2` CHECK (`pocetak` > -1),
CONSTRAINT `CONSTRAINT_3` CHECK (`kraj` < 25)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Table structure for table `raspored` */
DROP TABLE IF EXISTS `raspored`;
CREATE TABLE `raspored` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datum` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `datum` (`datum`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Table structure for table `angazovanje` */
DROP TABLE IF EXISTS `angazovanje`;
CREATE TABLE `angazovanje` (
`spasilacId` int(10) unsigned DEFAULT NULL,
`smenaId` int(10) unsigned DEFAULT NULL,
`rasporedId` int(10) unsigned DEFAULT NULL,
UNIQUE KEY `UNIQUE` (`spasilacId`,`smenaId`,`rasporedId`),
KEY `angazovanje_ibfk_1` (`spasilacId`),
KEY `angazovanje_ibfk_2` (`smenaId`),
KEY `angazovanje_ibfk_3` (`rasporedId`),
CONSTRAINT `angazovanje_ibfk_1` FOREIGN KEY (`spasilacId`) REFERENCES `spasilac` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `angazovanje_ibfk_2` FOREIGN KEY (`smenaId`) REFERENCES `smena` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `angazovanje_ibfk_3` FOREIGN KEY (`rasporedId`) REFERENCES `raspored` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Table structure for table `izvestaj` */
DROP TABLE IF EXISTS `izvestaj`;
CREATE TABLE `izvestaj` (
`spasilacId` int(10) unsigned DEFAULT NULL,
`smenaId` int(10) unsigned DEFAULT NULL,
`rasporedId` int(10) unsigned DEFAULT NULL,
`opis` longtext DEFAULT NULL,
UNIQUE KEY `UNIQUE` (`spasilacId`,`smenaId`,`rasporedId`),
KEY `izvestaj_ibfk_1` (`spasilacId`),
KEY `izvestaj_ibfk_2` (`smenaId`),
KEY `izvestaj_ibfk_3` (`rasporedId`),
CONSTRAINT `izvestaj_ibfk_1` FOREIGN KEY (`spasilacId`) REFERENCES `spasilac` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `izvestaj_ibfk_2` FOREIGN KEY (`smenaId`) REFERENCES `smena` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `izvestaj_ibfk_3` FOREIGN KEY (`rasporedId`) REFERENCES `raspored` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;