drop database `mytweeter`;
create database  `mytweeter`;
use `mytweeter`;
CREATE TABLE users(       
       nick VARCHAR (15) NOT NULL,
       name VARCHAR (50),
       description TEXT,
       PRIMARY KEY (nick)
);
CREATE UNIQUE INDEX index_nick ON users (nick);

CREATE TABLE follow(       
       nick VARCHAR (15) NOT NULL,
       follows VARCHAR (15) NOT NULL,
       FOREIGN KEY (nick) REFERENCES users(nick),
       FOREIGN KEY (follows) REFERENCES users(nick)
);
CREATE INDEX index_nick ON follow (nick);
CREATE INDEX index_follows ON follow (follows);

CREATE TABLE tweets(
       tweet_id INT UNSIGNED NOT NULL AUTO_INCREMENT,       
       nick VARCHAR (15) NOT NULL,
	   tweet VARCHAR (175) NOT NULL,
       FOREIGN KEY (nick) REFERENCES users(nick),
       PRIMARY KEY (tweet_id)
);
CREATE INDEX index_nick ON tweets (nick);

insert into users (nick,name,description) values ('John','John Lennon','Great composer dead too early');
insert into follow (nick,follows) values ('John','John');
insert into users (nick,name,description) values ('Amy','Amy Winehouse','Great singer dead too early');
insert into follow (nick,follows) values ('Amy','Amy');
insert into users (nick,name,description) values ('Marylin','Norma Jean Baker','Great Actress dead too early');
insert into follow (nick,follows) values ('Marylin','Marylin');
insert into users (nick,name,description) values ('Aaron','Aaron Swartz','Great Internet programmer dead too early');
insert into follow (nick,follows) values ('Aaron','Aaron');
insert into users (nick,name,description) values ('Bruce','Bruce Lee','Great karate fighter dead too early');
insert into follow (nick,follows) values ('Bruce','Bruce');
insert into users (nick,name,description) values ('Diana','Diana Spencer','Great princess dead too early');
insert into follow (nick,follows) values ('Diana','Diana');


insert into follow (nick,follows) values ('Aaron','Amy');
insert into follow (nick,follows) values ('Aaron','John');
insert into tweets (nick,tweet) values ('Aaron','1st tweet of Aaron');
insert into tweets (nick,tweet) values ('Amy','1st tweet of Amy');
insert into tweets (nick,tweet) values ('Aaron','2nd tweet of Aaron');
insert into tweets (nick,tweet) values ('Aaron','3th tweet of Aaron');
insert into tweets (nick,tweet) values ('Amy','2nd tweet of Amy');
insert into tweets (nick,tweet) values ('John','1st tweet of John');
insert into tweets (nick,tweet) values ('Amy','3th tweet of Amy');
insert into tweets (nick,tweet) values ('Marylin','1st tweet of Marylin');

