-- 
-- Database: `students`
-- 

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

-- 
-- Table structure for table `countries`
-- 

CREATE TABLE `countries` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `countries`
-- 

INSERT INTO `countries` VALUES (5, 'France');
INSERT INTO `countries` VALUES (7, 'Germany');
INSERT INTO `countries` VALUES (8, 'Italy');
INSERT INTO `countries` VALUES (6, 'Romania');
INSERT INTO `countries` VALUES (4, 'Russia');
INSERT INTO `countries` VALUES (2, 'UK');
INSERT INTO `countries` VALUES (1, 'USA');

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

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

CREATE TABLE `courses` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

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

INSERT INTO `courses` VALUES (2, 'Chemistry');
INSERT INTO `courses` VALUES (5, 'Geography');
INSERT INTO `courses` VALUES (4, 'Literature');
INSERT INTO `courses` VALUES (3, 'Maths');
INSERT INTO `courses` VALUES (1, 'Physics');

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

-- 
-- Table structure for table `marks`
-- 

CREATE TABLE `marks` (
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `mark` float NOT NULL,
  PRIMARY KEY  (`student_id`,`course_id`),
  UNIQUE KEY `index4` (`student_id`,`course_id`),
  KEY `fk_marks_students` (`student_id`),
  KEY `fk_marks_courses1` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `marks`
-- 

INSERT INTO `marks` VALUES (1, 1, 9);
INSERT INTO `marks` VALUES (1, 2, 10);
INSERT INTO `marks` VALUES (2, 3, 10);
INSERT INTO `marks` VALUES (2, 4, 10);

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

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

CREATE TABLE `students` (
  `id` int(11) NOT NULL auto_increment,
  `country_id` int(11) NOT NULL,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `registration` date default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index2` (`first_name`,`last_name`),
  KEY `fk_students_countries1` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

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

INSERT INTO `students` VALUES (1, 1, 'John', 'Doe', '2008-04-07');
INSERT INTO `students` VALUES (2, 6, 'Mihai', 'Corlan', '2008-04-16');

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `marks`
-- 
ALTER TABLE `marks`
  ADD CONSTRAINT `fk_marks_courses1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_marks_students` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 
-- Constraints for table `students`
-- 
ALTER TABLE `students`
  ADD CONSTRAINT `fk_students_countries1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;