DROP SCHEMA IF EXISTS `hobbydb`;
CREATE SCHEMA IF NOT EXISTS `hobbydb` DEFAULT CHARACTER SET utf8;
USE `hobbydb`;

CREATE TABLE IF NOT EXISTS `Member` (
  `mid` INT PRIMARY KEY,
  `givenName` VARCHAR(32) NULL,
  `familyName` VARCHAR(64) NULL,
  `email` VARCHAR(128) NULL
);

CREATE TABLE IF NOT EXISTS `Hobby` (
  `hid` INT PRIMARY KEY,
  `title` VARCHAR(64) NULL,
  `description` TEXT NULL
);

CREATE TABLE IF NOT EXISTS `ActiveHobby` (
  `mid` INT NOT NULL,
  `hid` INT NOT NULL,
  `startDate` DATE NULL,
  `annualBudget` FLOAT NULL,
  PRIMARY KEY (`mid`,`hid`),
  FOREIGN KEY (`mid`) REFERENCES `Member` (`mid`),
  FOREIGN KEY (`hid`) REFERENCES `Hobby` (`hid`)
);

INSERT INTO Member(mid,givenName,familyName,email) VALUES(1,'Pat','Patyk','pp@hobbysta.org');
INSERT INTO Member(mid,givenName,familyName,email) VALUES(2,'Don','Donski','dd@hobbysta.org');
INSERT INTO Member(mid,givenName,familyName,email) VALUES(3,'Eve','Evening','ee@hobbysta.org');
INSERT INTO Member(mid,givenName,familyName,email) VALUES(4,'Tom','Tomas','tt@hobbysta.org');
INSERT INTO Member(mid,givenName,familyName,email) VALUES(5,'Kim','Kimura','kk@hobbysta.org');

INSERT INTO Hobby(hid,title,description) VALUES(1,'Golf Balls','Collect golf balls.');
INSERT INTO Hobby(hid,title,description) VALUES(2,'Coke','Coca Cola memarobilia.');
INSERT INTO Hobby(hid,title,description) VALUES(3,'Post Cards','Collect post cards.');
INSERT INTO Hobby(hid,title,description) VALUES(4,'Stamps','Collect postage stamps.');

INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(1,1,'2016-05-14',500);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(1,2,'2016-07-25',750);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(2,1,'2016-07-28',1200);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(3,1,'2016-08-03',200);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(3,2,'2016-08-22',350);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(3,4,'2016-09-16',800);
INSERT INTO ActiveHobby(mid,hid,startDate,annualBudget) VALUES(4,4,'2016-10-14',100);