CREATE TABLE dependancyType (
  idDependancyType INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL,
  PRIMARY KEY(idDependancyType)
);

CREATE TABLE Groups (
  idGroups INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  groupName VARCHAR(20) NULL,
  PRIMARY KEY(idGroups)
)
TYPE=InnoDB;

CREATE TABLE projects (
  idprojects INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Groups_idGroups INTEGER UNSIGNED NOT NULL,
  projectName VARCHAR(20) NULL,
  startDate DATETIME NULL,
  endDate DATETIME NULL,
  slipage DATETIME NULL,
  PRIMARY KEY(idprojects),
  INDEX projects_FKIndex2(Groups_idGroups),
  FOREIGN KEY(Groups_idGroups)
    REFERENCES Groups(idGroups)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE groupUsers (
  idGroupUsers INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Groups_idGroups INTEGER UNSIGNED NOT NULL,
  userName VARCHAR(20) NULL,
  fullName VARCHAR(45) NULL,
  PRIMARY KEY(idGroupUsers),
  INDEX groupUsers_FKIndex1(Groups_idGroups),
  FOREIGN KEY(Groups_idGroups)
    REFERENCES Groups(idGroups)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE Activity (
  idActivity INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  groupUsers_idGroupUsers INTEGER UNSIGNED NOT NULL,
  Groups_idGroups INTEGER UNSIGNED NOT NULL,
  projects_idprojects INTEGER UNSIGNED NOT NULL,
  activityName VARCHAR(20) NULL,
  activityDesc VARCHAR(255) NULL,
  startDate DATETIME NULL,
  endDate DATETIME NULL,
  slipage DATETIME NULL,
  PRIMARY KEY(idActivity),
  INDEX Activity_FKIndex1(projects_idprojects),
  INDEX Activity_FKIndex2(Groups_idGroups),
  INDEX Activity_FKIndex3(groupUsers_idGroupUsers),
  FOREIGN KEY(projects_idprojects)
    REFERENCES projects(idprojects)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(Groups_idGroups)
    REFERENCES Groups(idGroups)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(groupUsers_idGroupUsers)
    REFERENCES groupUsers(idGroupUsers)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE diary (
  iddiary INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Activity_idActivity INTEGER UNSIGNED NOT NULL,
  diaryType VARCHAR(20) NULL,
  description VARCHAR(20) NULL,
  message VARCHAR(255) NULL,
  date DATETIME NULL,
  PRIMARY KEY(iddiary),
  INDEX diary_FKIndex1(Activity_idActivity),
  FOREIGN KEY(Activity_idActivity)
    REFERENCES Activity(idActivity)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

CREATE TABLE dependacies (
  idDependacies INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Activity_idActivity INTEGER UNSIGNED NOT NULL,
  dependancyType_idDependancyType INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(idDependacies),
  INDEX dependacies_FKIndex1(dependancyType_idDependancyType),
  INDEX dependacies_FKIndex2(Activity_idActivity),
  FOREIGN KEY(dependancyType_idDependancyType)
    REFERENCES dependancyType(idDependancyType)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(Activity_idActivity)
    REFERENCES Activity(idActivity)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);