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';

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

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

CREATE  TABLE IF NOT EXISTS `mydb`.`person` (
  `personID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique personID key.' ,
  `firstname` VARCHAR(45) NOT NULL ,
  `lastname` VARCHAR(45) NOT NULL ,
  `dob` DATE NULL ,
  PRIMARY KEY (`personID`) )
ENGINE = InnoDB
COMMENT = 'This table contains information about a person.';


-- -----------------------------------------------------
-- Data for table `mydb`.`person`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `person` (`personID`, `firstname`, `lastname`, `dob`) VALUES (0, 'William', 'Kirkpatrick', '1957-04-01');
INSERT INTO `person` (`personID`, `firstname`, `lastname`, `dob`) VALUES (0, 'Alan', 'Skinner', '1975-08-23');
INSERT INTO `person` (`personID`, `firstname`, `lastname`, `dob`) VALUES (0, 'Trevor', 'Bailey', '1984-02-19');
INSERT INTO `person` (`personID`, `firstname`, `lastname`, `dob`) VALUES (0, 'Nicola', 'Cole', '1977-09-21');
INSERT INTO `person` (`personID`, `firstname`, `lastname`, `dob`) VALUES (0, 'Fiona', 'Webb', '1969-10-24');

COMMIT;

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

CREATE  TABLE IF NOT EXISTS `mydb`.`addressType` (
  `addressType` CHAR(12) NOT NULL COMMENT 'Code for an address type.' ,
  `Description` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`addressType`) )
ENGINE = InnoDB
COMMENT = 'Lookup values for address types.';


-- -----------------------------------------------------
-- Data for table `mydb`.`addressType`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `addressType` (`addressType`, `Description`) VALUES ('HOME', 'Home');
INSERT INTO `addressType` (`addressType`, `Description`) VALUES ('WORK', 'Work');
INSERT INTO `addressType` (`addressType`, `Description`) VALUES ('HOL', 'Holiday Home');

COMMIT;

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

CREATE  TABLE IF NOT EXISTS `mydb`.`address` (
  `addressID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique key for an address.' ,
  `personID` INT NULL ,
  `addressType` CHAR(12) NULL ,
  `line1` VARCHAR(45) NULL ,
  `line2` VARCHAR(45) NULL ,
  `line3` VARCHAR(45) NULL ,
  `county` VARCHAR(45) NULL ,
  `town` VARCHAR(45) NULL ,
  `postcode` VARCHAR(45) NULL ,
  PRIMARY KEY (`addressID`) ,
  CONSTRAINT `fk_address_person`
    FOREIGN KEY (`personID` )
    REFERENCES `mydb`.`person` (`personID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_address_addressType`
    FOREIGN KEY (`addressType` )
    REFERENCES `mydb`.`addressType` (`addressType` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This table contains the address of people.';

CREATE INDEX fk_address_person ON `mydb`.`address` (`personID` ASC) ;

CREATE INDEX fk_address_addressType ON `mydb`.`address` (`addressType` ASC) ;


-- -----------------------------------------------------
-- Data for table `mydb`.`address`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 1, 'HOME', '14 Carrington Road', '', '', '', 'Amersham', 'HP2 3PP');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 1, 'WORK', '57 Buckingham Avenue', '', '', 'Bucks', 'Totteridge', 'BP4 8AS');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 2, 'HOME', '12 Langdon Street', 'Southcourt', '', 'Norfolk', 'Micklefield', 'NA2 8KP');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 3, 'HOME', 'The Knoll', '3 Church Street', '', '', '', 'RT4 7TT');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 3, 'WORK', '79 High Street', '', '', '', 'Earley', 'MN2 4AZ');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 4, 'HOME', '2 Little Pendnor Court', 'Blockwater', '', 'Surrey', '', 'SL7 6GP');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 4, 'WORK', '21 Elm Road', '', '', '', 'Steeple', 'LA3 3PP');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 4, 'HOL', 'Gillies Cottage', 'Manor Road', '', 'Suffolk', 'Marlow', 'OH3 4BG');
INSERT INTO `address` (`addressID`, `personID`, `addressType`, `line1`, `line2`, `line3`, `county`, `town`, `postcode`) VALUES (0, 5, 'HOME', '6 Violet Close', 'Green End Road', '', '', 'Chiswick', '');

COMMIT;


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