create database Discussion;

CREATE TABLE `Discussion`.`Author` (
`authorID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`authorName` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL ,
`password` VARCHAR( 50 ) NOT NULL ,
`rating` INT NOT NULL DEFAULT '0',
UNIQUE (
`email`
));

CREATE TABLE `Discussion`.`topic` (
`topicID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 50 ) NOT NULL ,
`content` TEXT NOT NULL ,
`rating` INT NOT NULL DEFAULT '0',
`authorID` INT NOT NULL ,
`date_created` DATE NOT NULL ,
`date_edited` DATE NOT NULL,
 FOREIGN KEY (authorID) REFERENCES author(authorID)
) ;

CREATE TABLE `Discussion`.`post` (
`postID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`content` VARCHAR( 1000 ) NOT NULL ,
`rating` INT NOT NULL ,
`authorID` INT NOT NULL ,
`topicID` INT NOT NULL ,
`date_posted` DATE NOT NULL,
FOREIGN KEY (authorID) REFERENCES author(authorID),
FOREIGN KEY (topicID) REFERENCES topic(topicID)
);

CREATE TABLE `Discussion`.`keywords` (
`keyword` VARCHAR( 25 ) NOT NULL ,
`topicID` INT NOT NULL ,
PRIMARY KEY ( `keyword` , `topicID` ),
FOREIGN KEY (topicID) REFERENCES topic(topicID)
);