DROP DATABASE IF EXISTS redbeanify;
CREATE DATABASE IF NOT EXISTS redbeanify CHARACTER SET = 'utf8';

USE redbeanify;

-- CAPS-and-Underscores conventions
CREATE TABLE WARM_BLOODED_ANIMAL
(NAME VARCHAR (60) NOT NULL PRIMARY KEY,
 SPECIES VARCHAR (60) NOT NULL
);

INSERT INTO WARM_BLOODED_ANIMAL VALUES ('Greg','Human');
INSERT INTO WARM_BLOODED_ANIMAL VALUES ('Mother','Human');
INSERT INTO WARM_BLOODED_ANIMAL VALUES ('BJ','Canine');
INSERT INTO WARM_BLOODED_ANIMAL VALUES ('Missy','Canine');
INSERT INTO WARM_BLOODED_ANIMAL VALUES ('Suzie','Feline');

-- All lower-case conventions
CREATE TABLE human
(name         VARCHAR (100) NOT NULL PRIMARY KEY,
 eyecolor VARCHAR (60) NULL,
 FOREIGN KEY (name) REFERENCES WARM_BLOODED_ANIMAL(NAME)
);

INSERT INTO human VALUES ('Greg','Gray');
INSERT INTO human VALUES ('Mother','Gray');

-- Mixed-case conventions
CREATE TABLE Pet
(Name         VARCHAR (100) NOT NULL PRIMARY KEY,
 EyeColor VARCHAR (60) NULL,
 FOREIGN KEY (Name) REFERENCES WARM_BLOODED_ANIMAL(NAME)
);

INSERT INTO Pet VALUES ('BJ','Brown');
INSERT INTO Pet VALUES ('Missy','Brown');
INSERT INTO Pet VALUES ('Suzie','Blue');

-- Mixed-case conventions, two FOREIGN KEYs
CREATE TABLE PetsOfHumans
(HumanName         VARCHAR (100) NOT NULL,
 PetName VARCHAR (100) NOT NULL,
 PRIMARY KEY (HumanName,PetName),
 FOREIGN KEY (HumanName) REFERENCES human(name),
 FOREIGN KEY (PetName) REFERENCES Pet(Name)
);

INSERT INTO PetsOfHumans VALUES ('Greg','BJ');
INSERT INTO PetsOfHumans VALUES ('Greg','Missy');
INSERT INTO PetsOfHumans VALUES ('Mother','Suzie');

-- Two references to same referenced table
CREATE TABLE COMFORTERS
(GiverName VARCHAR (100) NOT NULL,
 RecipientName VARCHAR(100) NOT NULL,
 PRIMARY KEY (GiverName,RecipientName),
 FOREIGN KEY (GiverName) REFERENCES WARM_BLOODED_ANIMAL(NAME),
 FOREIGN KEY (RecipientName) REFERENCES WARM_BLOODED_ANIMAL(NAME)
);

INSERT INTO COMFORTERS VALUES ('BJ','Greg');
INSERT INTO COMFORTERS VALUES ('Suzie','Mother');
INSERT INTO COMFORTERS VALUES ('Mother','Greg');

-- FOREIGN KEY references table which will be excluded
CREATE TABLE ComfortEvent
(GiverName VARCHAR (100) NOT NULL,
 RecipientName VARCHAR (100) NOT NULL,
 EventDescription VARCHAR (250) NOT NULL,
 PRIMARY KEY (GiverName,RecipientName,EventDescription),
 FOREIGN KEY (GiverName,RecipientName) REFERENCES COMFORTERS(GiverName,RecipientName)
);

-- Already has an AUTO_INCREMENT column
CREATE TABLE PET_FRIENDLY_EVENTS
(PK INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 EVENT_DATE DATE NOT NULL,
 EVENT_LOCATION VARCHAR (250) NOT NULL,
 EVENT_DESCRIPTION TEXT NOT NULL
);

INSERT INTO PET_FRIENDLY_EVENTS (EVENT_DATE,EVENT_LOCATION,EVENT_DESCRIPTION) VALUES ('2012-04-01','Regional Park','Bark In The Bay! A great timne will be had by all.');

-- Already has an ID column (no PRIMARY KEY)
CREATE TABLE Authorized_Users
(ID VARCHAR (24) NOT NULL,
 User_Name VARCHAR (60) NULL
);

INSERT INTO Authorized_Users VALUES ('gregorinator', 'Gregory');