-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: May 06, 2017 at 03:19 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

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: `eurosong2017`
--
CREATE DATABASE IF NOT EXISTS `eurosong2017` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `eurosong2017`;

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

--
-- Table structure for table `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `code` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `capital` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`code`, `country`, `capital`) VALUES
('ALB', 'Albania', 'Tirana'),
('AND', 'Andorra', 'Andorra'),
('ARM', 'Armenia', 'Yerevan'),
('AUT', 'Austria', 'Vienna'),
('AZE', 'Azerbaijan', 'Baku'),
('BEL', 'Belgium', 'Brussels'),
('BGR', 'Bulgaria', 'Sofia'),
('BIH', 'Bosnia and Herzegovina', 'Sarajevo'),
('BLR', 'Belarus', 'Minsk'),
('CHE', 'Switzerland', 'Bern'),
('CYP', 'Cyprus', 'Nicosia'),
('CZE', 'Czech Republic', 'Prague'),
('DEU', 'Germany', 'Berlin'),
('DNK', 'Denmark', 'Copenhagen'),
('ESP', 'Spain', 'Madrid'),
('EST', 'Estonia', 'Tallinn'),
('FIN', 'Finland', 'Helsinki'),
('FRA', 'France', 'Paris'),
('FRO', 'Faroe Islands', 'Torshavn'),
('GBR', 'United Kingdom', 'London'),
('GEO', 'Georgia', 'Tbilisi'),
('GRC', 'Greece', 'Athens'),
('HRV', 'Croatia', 'Zagreb'),
('HUN', 'Hungary', 'Budapest'),
('IRL', 'Ireland', 'Dublin'),
('ISL', 'Iceland', 'Reykjavik'),
('ISR', 'Israel', 'Tel Aviv'),
('ITA', 'Italy', 'Rome'),
('LIE', 'Liechtenstein', 'Vaduz'),
('LTU', 'Lithuania', 'Vilnus'),
('LUX', 'Luxembourg', 'Luxembourg'),
('LVA', 'Latvia', 'Riga'),
('MCO', 'Monaco', 'Monaco'),
('MDA', 'Moldova', 'Chisinau'),
('MKD', 'For. Yugoslav Rep. Macedonia', 'Skopje'),
('MLT', 'Malta', 'Valletta'),
('MNE', 'Montenegro', 'Podgorica'),
('NLD', 'Netherlands', 'Amsterdam'),
('NOR', 'Norway', 'Oslo'),
('POL', 'Poland', 'Warsaw'),
('PRT', 'Portugal', 'Lisboa'),
('ROU', 'Romania', 'Bucharest'),
('RUS', 'Russian Federation', 'Moscow'),
('SMR', 'San Marino', 'San Marino'),
('SRB', 'Serbia', 'Belgrade'),
('SVK', 'Slovakia', 'Bratislava'),
('SVN', 'Slovenia', 'Ljubljana'),
('SWE', 'Sweden', 'Stockholm'),
('TUR', 'Turkey', 'Ankara'),
('UKR', 'Ukraine', 'Kyev');

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

--
-- Table structure for table `journalist`
--

CREATE TABLE IF NOT EXISTS `journalist` (
  `username` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `media` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `web` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`username`),
  UNIQUE KEY `country_2` (`country`),
  KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `journalist`
--

INSERT INTO `journalist` (`username`, `password`, `name`, `country`, `media`, `web`) VALUES
('bbc', 'sifra123', 'Scot Mils', 'GBR', 'BBC One', 'www.bbc.uk'),
('bht', 'sifra123', 'Dejan Kukric', 'BIH', 'BH TV 1', NULL),
('ct1', 'sifra123', 'Libor Boucek', 'CZE', 'CT1', NULL),
('daserste', 'sifra123', 'Peter Urban', 'DEU', 'Das Erste', NULL),
('denmarktv', 'sifra123', 'Ole Topholm', 'DNK', 'DR1', NULL),
('france', 'sifra123', 'Marian James', 'FRA', 'France 2', 'www.france4.fr'),
('hellas', 'sifra123', 'Jorgos Kapusidas', 'GRC', 'ERT1', 'www.ert.gr'),
('hrt', 'sifra123', 'Dusko Curlic', 'HRV', 'HRT 1', 'http://www.hrt.hr'),
('la1', 'sifra123', 'Hulia Varela', 'ESP', 'La Uno', 'http://www.rtve.es'),
('mrt', 'sifra123', 'Karolina Petkovska', 'MKD', 'MRT 1', 'www.mrt.com.mk'),
('orf', 'sifra123', 'Andi Knol', 'AUT', 'ORF', 'http://www.org.at'),
('port', 'sifra123', 'Helder Reis', 'PRT', 'RTP', 'www.rtp.po'),
('rai', 'sifra123', 'Filipo Solibelo', 'ITA', 'RAI 1', 'www.rai.it'),
('rts', 'sifra123', 'Duska Vucinic-Lucic', 'SRB', 'Radio televizija Srbije', 'http://www.rts.rs'),
('russ', 'sifra123', 'Dimitrij Guberiev', 'RUS', 'Russia 1', 'www.russia1.ru'),
('slo', 'sifra123', 'Andrej Hofer', 'SVN', 'RTV Slovenia 1', NULL),
('swt1', 'sifra123', 'Lota Brome', 'SWE', 'SVT 1', NULL),
('tvcg', 'sifra123', 'Tijana Miskovic', 'MNE', 'TV Crna Gora 1', 'http://www.rtcg.me'),
('ukr', 'sifra123', 'Timur Terehova', 'UKR', 'Ukraine Radio', 'http://www.ukrainaradio.uk');

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

--
-- Table structure for table `participant`
--

CREATE TABLE IF NOT EXISTS `participant` (
  `country` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `individual` tinyint(4) NOT NULL,
  `song` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `language` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `firstperform` date NOT NULL,
  `current_sum_points` int(11) DEFAULT '0',
  PRIMARY KEY (`country`),
  KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `participant`
--

INSERT INTO `participant` (`country`, `name`, `individual`, `song`, `language`, `firstperform`, `current_sum_points`) VALUES
('ALB', 'Lindita', 1, 'World', 'english', '2017-05-01', 0),
('ARM', 'Artsvik', 1, 'Fly with Me', 'english', '2017-05-02', 0),
('AUT', 'Nathan Trent', 1, 'Running on Air', 'english', '2017-05-01', 1),
('BEL', 'Blanche', 1, 'City Lights', 'english', '2017-05-02', 0),
('BGR', 'Kristian Kostov', 1, 'Beautiful Mess', 'bulgarian', '2017-05-04', 5),
('BIH', 'Hari Mata Hari', 0, 'Lejla', 'bosanski', '2017-05-05', 0),
('CHE', 'Timebelle', 0, 'Apollo', 'english', '2017-05-04', 0),
('CYP', 'Hovig', 0, 'Gravity', 'english', '2017-05-04', 7),
('CZE', 'Martina Barta', 1, 'My Turn', 'english', '2017-05-05', 0),
('DEU', 'Levina', 1, 'Perfect Life', 'english', '2017-04-08', 0),
('DNK', 'Anja', 1, 'Where I am', 'english', '2017-05-02', 0),
('ESP', 'Azucar Moreno', 0, 'Bandido', 'spanish', '1990-05-05', 2),
('EST', 'Koit Toome and Laura', 0, 'Verona', 'english', '2015-05-06', 0),
('FIN', 'Norma John', 1, 'Blackbird', 'english', '2017-05-05', 0),
('FRA', 'Alma', 1, 'Requiem', 'french', '2017-04-22', 6),
('GBR', 'Lucie Jones', 1, 'Never Give Up on You', 'english', '2017-05-01', 0),
('GRC', 'Sakis Rouvas', 1, 'Shake It', 'english', '2004-05-05', 8),
('HRV', 'Jelena Rozga', 1, 'My Friend', 'italian', '2017-05-04', 7),
('IRL', 'Brendan Murray', 1, 'Dying to Try', 'english', '2017-05-05', 0),
('ISR', 'IMRI', 0, 'I Feel Alive', 'english', '2016-12-12', 3),
('ITA', 'Toto Cotugno', 1, 'United Europe', 'italian', '2017-04-15', 0),
('LTU', 'Fusedmarc', 0, 'Rain of Revolution', 'english', '2017-05-02', 4),
('MKD', 'Karolina Gocheva', 1, 'Jovano Jovanke', 'makedonski', '2017-04-05', 4),
('MNE', 'Rambo Amadeus', 1, 'Euro Neuro', 'srpski', '2012-05-02', 8),
('NOR', 'JOWST', 0, 'Grab the Moment', 'english', '2017-05-01', 0),
('POL', 'Kasta Mos', 1, 'Flashlight', 'english', '2017-05-04', 0),
('PRT', 'Salvador Sobral', 1, 'Amar Pelos Dois', 'portuguese', '2017-04-08', 5),
('RUS', 'Yulia Samoylova', 1, 'Flame is burning', 'english', '2017-04-13', 22),
('SRB', 'Marija Serifovic', 1, 'Molitva', 'srpski', '2008-05-05', 14),
('SVN', 'Omar Naber', 1, 'On My Way', 'english', '2017-05-04', 3),
('SWE', 'ABBA', 0, 'Waterloo', 'english', '1974-05-05', 7),
('UKR', 'Torvald', 0, 'Time', 'english', '2017-04-20', 10);

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

--
-- Table structure for table `vote`
--

CREATE TABLE IF NOT EXISTS `vote` (
  `ident` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `countryParticipant` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `points` int(11) NOT NULL,
  PRIMARY KEY (`ident`),
  KEY `username` (`username`),
  KEY `country` (`countryParticipant`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;

--
-- Dumping data for table `vote`
--

INSERT INTO `vote` (`ident`, `username`, `countryParticipant`, `points`) VALUES
(1, 'bbc', 'HRV', 1),
(2, 'bbc', 'SRB', 2),
(3, 'bbc', 'ISR', 3),
(4, 'bbc', 'LTU', 4),
(5, 'bbc', 'BGR', 5),
(6, 'bbc', 'FRA', 6),
(7, 'bbc', 'CYP', 7),
(8, 'bbc', 'GRC', 8),
(9, 'bbc', 'UKR', 10),
(10, 'bbc', 'RUS', 12),
(11, 'bht', 'AUT', 1),
(12, 'bht', 'ESP', 2),
(13, 'bht', 'SVN', 3),
(14, 'bht', 'MKD', 4),
(15, 'bht', 'PRT', 5),
(16, 'bht', 'HRV', 6),
(17, 'bht', 'SWE', 7),
(18, 'bht', 'MNE', 8),
(19, 'bht', 'RUS', 10),
(20, 'bht', 'SRB', 12);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `journalist`
--
ALTER TABLE `journalist`
  ADD CONSTRAINT `journalist_ibfk_1` FOREIGN KEY (`country`) REFERENCES `country` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `participant`
--
ALTER TABLE `participant`
  ADD CONSTRAINT `participant_ibfk_1` FOREIGN KEY (`country`) REFERENCES `country` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `vote`
--
ALTER TABLE `vote`
  ADD CONSTRAINT `vote_ibfk_2` FOREIGN KEY (`countryParticipant`) REFERENCES `participant` (`country`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `vote_ibfk_1` FOREIGN KEY (`username`) REFERENCES `journalist` (`username`) 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 */;