SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
SHOW WARNINGS;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`student` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `mydb`.`student` (
  `stu_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `stu_fname` VARCHAR(15) NOT NULL ,
  `stu_lname` VARCHAR(30) NOT NULL ,
  `stu_note` VARCHAR(255) NULL ,
  PRIMARY KEY (`stu_id`) )
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`activity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`activity` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `mydb`.`activity` (
  `act_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `act_name` VARCHAR(45) NOT NULL ,
  `act_fee` DECIMAL(5,2) NOT NULL ,
  `act_start_date` DATE NOT NULL ,
  `act_end_date` DATE NOT NULL ,
  `act_note` VARCHAR(255) NULL ,
  PRIMARY KEY (`act_id`) )
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `mydb`.`participant`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`participant` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `mydb`.`participant` (
  `stu_id` SMALLINT UNSIGNED NOT NULL ,
  `act_id` SMALLINT UNSIGNED NOT NULL ,
  `par_start_date` DATE NOT NULL ,
  `par_end_date` DATE NOT NULL ,
  `par_note` VARCHAR(255) NULL ,
  PRIMARY KEY (`stu_id`, `act_id`) ,
  INDEX `fk_participant_student` (`stu_id` ASC) ,
  INDEX `fk_participant_activity1` (`act_id` ASC) ,
  CONSTRAINT `fk_participant_student`
    FOREIGN KEY (`stu_id` )
    REFERENCES `mydb`.`student` (`stu_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_participant_activity1`
    FOREIGN KEY (`act_id` )
    REFERENCES `mydb`.`activity` (`act_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`student`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`student` (`stu_id`, `stu_fname`, `stu_lname`, `stu_note`) VALUES (1, 'carlton', 'tassell', NULL);
INSERT INTO `mydb`.`student` (`stu_id`, `stu_fname`, `stu_lname`, `stu_note`) VALUES (2, 'vincent', 'besner', NULL);
INSERT INTO `mydb`.`student` (`stu_id`, `stu_fname`, `stu_lname`, `stu_note`) VALUES (3, 'laura', 'casali', NULL);
INSERT INTO `mydb`.`student` (`stu_id`, `stu_fname`, `stu_lname`, `stu_note`) VALUES (4, 'clark', 'foster', NULL);
INSERT INTO `mydb`.`student` (`stu_id`, `stu_fname`, `stu_lname`, `stu_note`) VALUES (5, 'mickey', 'o\'rourke', NULL);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`activity`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`activity` (`act_id`, `act_name`, `act_fee`, `act_start_date`, `act_end_date`, `act_note`) VALUES (1, 'basketball', 15.15, '2014-05-06', '2014-08-06', NULL);
INSERT INTO `mydb`.`activity` (`act_id`, `act_name`, `act_fee`, `act_start_date`, `act_end_date`, `act_note`) VALUES (2, 'golf', 78.99, '2014-05-06', '2014-08-06', NULL);
INSERT INTO `mydb`.`activity` (`act_id`, `act_name`, `act_fee`, `act_start_date`, `act_end_date`, `act_note`) VALUES (3, 'skydiving', 90.99, '2014-05-06', '2014-08-06', NULL);
INSERT INTO `mydb`.`activity` (`act_id`, `act_name`, `act_fee`, `act_start_date`, `act_end_date`, `act_note`) VALUES (4, 'pottery', 40.79, '2014-05-15', '2014-08-15', NULL);
INSERT INTO `mydb`.`activity` (`act_id`, `act_name`, `act_fee`, `act_start_date`, `act_end_date`, `act_note`) VALUES (5, 'underwater basketweaving', 999.99, '2014-05-15', '2014-08-15', NULL);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`participant`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (1, 2, '2014-05-05', '2014-08-20', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (2, 3, '2014-05-01', '2015-08-16', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (3, 4, '2014-04-29', '2015-04-29', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (4, 5, '2014-05-01', '2014-08-07', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (5, 4, '2014-01-01', '2015-01-01', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (1, 4, '2014-05-05', '2014-08-20', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (2, 5, '2014-05-01', '2015-08-16', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (3, 5, '2014-04-29', '2015-04-29', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (5, 1, '2014-01-01', '2015-01-01', NULL);
INSERT INTO `mydb`.`participant` (`stu_id`, `act_id`, `par_start_date`, `par_end_date`, `par_note`) VALUES (1, 5, '2014-05-05', '2014-08-20', NULL);

COMMIT;