NGS toolkit memo

CREATE database ngs;

CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';

GRANT SELECT,INSERT,UPDATE,DELETE ON ngs.* TO 'mysql'@'localhost' IDENTIFIED BY 'mysql';

CREATE TABLE teams (
 teamID INT NOT NULL auto_increment PRIMARY KEY,
 teamName VARCHAR (50),
 teamPolicy VARCHAR (50),
 teamDesc varchar(100)
) ENGINE=InnoDB;

CREATE TABLE users (
         userID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         userName VARCHAR(50),
         userPasswd VARCHAR (50),
         teamID INT NOT NULL,
         userRole VARCHAR (50),
         userDesc VARCHAR(100)
       ) ENGINE=InnoDB;

ALTER TABLE users
ADD CONSTRAINT FK_users
FOREIGN KEY (teamID) REFERENCES teams(teamID)
ON UPDATE CASCADE
ON DELETE CASCADE;

INSERT INTO teams (teamID, teamName, teamPolicy, teamDesc) VALUES ('1','team1','RWX','our first team');
INSERT INTO users (userName, userPasswd, teamID, userRole, userDesc) VALUES('zhengyuan','nih@8346','1','admin','wang zhengyuan is the administrator of the software.');