-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2014 at 06:39 AM
-- Server version: 5.5.24-log
-- PHP Version: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `radnalista`
--
CREATE DATABASE `radnalista` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `radnalista`;

-- --------------------------------------------------------

--
-- Table structure for table `osoba`
--

CREATE TABLE IF NOT EXISTS `osoba` (
  `korisnicko_ime` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `lozinka` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `ime` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `prezime` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `godina_rodj` int(4) NOT NULL,
  `eposta` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `sef` tinyint(1) NOT NULL DEFAULT '0',
  `nadredjena_osoba` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`korisnicko_ime`),
  KEY `nadredjena_osoba` (`nadredjena_osoba`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `osoba`
--

INSERT INTO `osoba` (`korisnicko_ime`, `lozinka`, `ime`, `prezime`, `godina_rodj`, `eposta`, `sef`, `nadredjena_osoba`) VALUES
('ana', 'sifra123', 'Aleksandra', 'Marjanovic', 1986, 'aleksandra@etf.rs', 0, 'zeljko'),
('bosko', 'sifra123', 'Bosko', 'Nikolic', 1973, 'nbosko@etf.rs', 1, 'dekan'),
('cmilos', 'sifra123', 'Milos', 'Cvetanovic', 1979, 'cmilos@etf.rs', 0, 'bosko'),
('dekan', 'sifra123', 'Branko', 'Kovacevic', 1951, 'dekan@etf.rs', 1, NULL),
('drasko', 'sifra123', 'Drazen', 'Draskovic', 1986, 'draskovic@etf.rs', 0, 'bosko'),
('katja', 'sifra123', 'Katarina', 'Milenkovic', 1990, 'katja@etf.rs', 0, 'bosko'),
('sanja', 'sifra123', 'Sanja', 'Vujnovic', 1987, 'sanja@etf.rs', 0, 'zeljko'),
('zaki', 'sifra123', 'Zaki', 'Radivojevic', 1979, 'zaki@etf.rs', 0, 'bosko'),
('zeljko', 'sifra123', 'Zeljko', 'Djurovic', 1965, 'zeljko@etf.rs', 1, 'dekan');

-- --------------------------------------------------------

--
-- Table structure for table `projekat`
--

CREATE TABLE IF NOT EXISTS `projekat` (
  `id_projekta` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `tip` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id_projekta`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `projekat`
--

INSERT INTO `projekat` (`id_projekta`, `naziv`, `tip`) VALUES
(1, 'Jedinstveni informacioni sistem prosvete', 'Nacionalni'),
(2, 'Inteligentni softverski sistemi', 'Tehnoloski razvoj'),
(4, 'Povecanje energetske efikasnosti u TENT-A', 'Tehnoloski razvoj'),
(5, 'Softverski sistem za obradu signala kod osoba sa smetnjama u razvoju', 'EU H2020');

-- --------------------------------------------------------

--
-- Table structure for table `rukovodilac_projekta`
--

CREATE TABLE IF NOT EXISTS `rukovodilac_projekta` (
  `korisnik` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`korisnik`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `rukovodilac_projekta`
--

INSERT INTO `rukovodilac_projekta` (`korisnik`, `id`) VALUES
('bosko', 1),
('bosko', 2),
('zeljko', 4),
('bosko', 5),
('zeljko', 5);

-- --------------------------------------------------------

--
-- Table structure for table `zadatak`
--

CREATE TABLE IF NOT EXISTS `zadatak` (
  `id_zadatka` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `zadatak_dodeljen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `zadatak_zavrsen` timestamp NULL DEFAULT NULL,
  `zaduzena_osoba` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `zadatak_dodelio` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `id_projekta` int(11) NOT NULL,
  `komentar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id_zadatka`),
  KEY `zaduzena_osoba` (`zaduzena_osoba`,`zadatak_dodelio`,`id_projekta`),
  KEY `zadatak_dodelio` (`zadatak_dodelio`),
  KEY `id_projekta` (`id_projekta`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `zadatak`
--

INSERT INTO `zadatak` (`id_zadatka`, `naziv`, `status`, `zadatak_dodeljen`, `zadatak_zavrsen`, `zaduzena_osoba`, `zadatak_dodelio`, `id_projekta`, `komentar`) VALUES
(1, 'Analiza korisnickih zahteva - modul Srednje skole', 1, '2014-06-13 07:20:00', '2014-06-12 20:08:58', 'drasko', 'bosko', 1, 'Treba izvrsiti klasifikaciju podataka za sve srednje skole'),
(2, 'Analiza korisnickih zahteva - modul Fakulteti', 1, '2014-06-13 08:00:00', '2014-06-12 20:08:58', 'zaki', 'bosko', 1, 'Treba izvrsiti klasifikaciju podataka za sve fakultete'),
(3, 'Model baze podataka', 2, '2014-06-12 14:20:00', '2014-06-12 23:39:24', 'cmilos', 'bosko', 1, 'Modelovati bazu podataka, kao rezultat ocekujem IE fizicki model podataka i sve tabele sa tipovima'),
(4, 'Implementacija sistema', 1, '2014-06-13 11:46:00', '2014-06-12 23:39:24', 'katja', 'bosko', 1, 'JSF+Spring+Hibernate');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `osoba`
--
ALTER TABLE `osoba`
  ADD CONSTRAINT `osoba_ibfk_1` FOREIGN KEY (`nadredjena_osoba`) REFERENCES `osoba` (`korisnicko_ime`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rukovodilac_projekta`
--
ALTER TABLE `rukovodilac_projekta`
  ADD CONSTRAINT `rukovodilac_projekta_ibfk_2` FOREIGN KEY (`id`) REFERENCES `projekat` (`id_projekta`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `rukovodilac_projekta_ibfk_1` FOREIGN KEY (`korisnik`) REFERENCES `osoba` (`korisnicko_ime`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `zadatak`
--
ALTER TABLE `zadatak`
  ADD CONSTRAINT `zadatak_ibfk_1` FOREIGN KEY (`zaduzena_osoba`) REFERENCES `osoba` (`korisnicko_ime`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `zadatak_ibfk_2` FOREIGN KEY (`zadatak_dodelio`) REFERENCES `osoba` (`korisnicko_ime`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `zadatak_ibfk_3` FOREIGN KEY (`id_projekta`) REFERENCES `projekat` (`id_projekta`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
