--
-- Host: localhost    Database: IT00002
-- ------------------------------------------------------


--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_latvian_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` VALUES (1,'Datu bāzu tehnoloģijas '),(2,'Matemātika');

--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `log` varchar(255) COLLATE utf8_latvian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `log`
--

INSERT INTO `log` VALUES ('0000-00-00 00:00:00','Student was created'),('0000-00-00 00:00:00','Student was created'),('0000-00-00 00:00:00','Student was created'),('0000-00-00 00:00:00','Student was created'),('2010-11-05 13:03:33','Student was created'),('2010-11-05 13:03:33','Student was created'),('2010-11-05 13:03:33','Student was created'),('2010-11-05 13:03:33','Student was created'),('2010-11-05 13:20:52','Student was created'),('2010-11-05 13:24:12','root@localhost'),('2010-11-05 13:24:12','root@localhost'),('2010-11-05 13:24:12','root@localhost'),('2010-11-05 13:24:12','root@localhost'),('2010-11-05 13:29:41','1'),('2010-11-05 13:29:41','2'),('2010-11-05 13:29:41','3'),('2010-11-05 13:29:41','4'),('2010-11-05 13:32:17','1'),('2010-11-05 13:32:17','2'),('2010-11-05 13:32:17','3'),('2010-11-05 13:32:17','4'),('2010-11-05 13:33:54','root@localhost1'),('2010-11-05 13:33:54','root@localhost2'),('2010-11-05 13:33:54','root@localhost3'),('2010-11-05 13:33:54','root@localhost4'),('2010-11-05 13:34:14','root@localhost 1'),('2010-11-05 13:34:14','root@localhost 2'),('2010-11-05 13:34:14','root@localhost 3'),('2010-11-05 13:34:14','root@localhost 4'),('2010-11-05 13:35:18','root@localhost Created root id:1 surname:Uzv'),('2010-11-05 13:35:18','root@localhost Created root id:2 surname:Uzv'),('2010-11-05 13:35:18','root@localhost Created root id:3 surname:Uzv'),('2010-11-05 13:35:18','root@localhost Created root id:4 surname:Uzv'),('2010-11-05 13:35:38','root@localhost Created root id:1 surname:Uzvards1 name:Vards1'),('2010-11-05 13:35:38','root@localhost Created root id:2 surname:Uzvards2 name:Vards2'),('2010-11-05 13:35:38','root@localhost Created root id:3 surname:Uzvards3 name:Vards3'),('2010-11-05 13:35:38','root@localhost Created root id:4 surname:Uzvards4 name:Vards4');

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `surname` varchar(100) COLLATE utf8_latvian_ci NOT NULL,
  `name` varchar(100) COLLATE utf8_latvian_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` VALUES (1,'Uzvards1','Vards1'),(2,'Uzvards2','Vards2'),(3,'Uzvards3','Vards3'),(4,'Uzvards4','Vards4');
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER students_log_create
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
DECLARE log VARCHAR(255);
SET log = CONCAT(CURRENT_USER(), " Created root id:", NEW.id, " surname:", NEW.surname, " name:", NEW.name); 
INSERT INTO log VALUES (DEFAULT, log);
END */;;
DELIMITER ;

--
-- Table structure for table `students_courses`
--

CREATE TABLE `students_courses` (
  `students_id` int(11) NOT NULL,
  `courses_id` int(11) NOT NULL,
  PRIMARY KEY (`students_id`,`courses_id`),
  KEY `fk_students_has_courses_courses1` (`courses_id`),
  CONSTRAINT `fk_students_has_courses_courses1` FOREIGN KEY (`courses_id`) REFERENCES `courses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_students_has_courses_students` FOREIGN KEY (`students_id`) REFERENCES `students` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `students_courses`
--


--
-- Dumping routines for database 'IT00002'
--
/*!50003 DROP PROCEDURE IF EXISTS `create_students` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `create_students`()
BEGIN
DECLARE id INT;
DECLARE name VARCHAR(50);
DECLARE surname VARCHAR(50);
SET id = 1;
DELETE FROM students_courses;
DELETE FROM students;
WHILE id < 5 DO
    SET name = CONCAT ("Vards", id);
    SET surname = CONCAT ("Uzvards", id);
    INSERT INTO students VALUES (id, surname, name);
    SET id = id + 1;
END WHILE;
END */;;
DELIMITER ;


