SET FOREIGN_KEY_CHECKS= 0 ;

-- Drop table kafedra
DROP TABLE IF EXISTS `kafedra`;

CREATE TABLE `kafedra` (
  `id_kaf` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazv_kaf` char( 50 ) NOT NULL,
  PRIMARY KEY(`id_kaf`)
)
ENGINE=INNODB;

-- Drop table dolgnosti
DROP TABLE IF EXISTS `dolgnosti`;

CREATE TABLE `dolgnosti` (
  `id_dolgn` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazv_dolgn` char( 70 ) NOT NULL,
  PRIMARY KEY(`id_dolgn`)
)
ENGINE=INNODB;

-- Drop table spec
DROP TABLE IF EXISTS `spec`;

CREATE TABLE `spec` (
  `id_spec` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazv_spec` char( 70 ) NOT NULL,
  PRIMARY KEY(`id_spec`)
)
ENGINE=INNODB;

-- Drop table gruppa
DROP TABLE IF EXISTS `gruppa`;

CREATE TABLE `gruppa` (
  `id_grup` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazv_grup` char( 50 ),
  `id_spec` int( 11 ) UNSIGNED NOT NULL,
  `kurs` int( 11 ) UNSIGNED NOT NULL,
  PRIMARY KEY(`id_grup`),
  CONSTRAINT `Ref_07` FOREIGN KEY (`id_spec`)
    REFERENCES `spec`(`id_spec`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table prepod
DROP TABLE IF EXISTS `prepod`;

CREATE TABLE `prepod` (
  `id_prep` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `FIO` char( 80 ) NOT NULL,
  `id_dolgn` int( 11 ) UNSIGNED NOT NULL,
  `id_kaf` int( 11 ) UNSIGNED NOT NULL,
  `telefon` char( 50 ),
  PRIMARY KEY(`id_prep`),
  CONSTRAINT `Ref_04` FOREIGN KEY (`id_dolgn`)
    REFERENCES `dolgnosti`(`id_dolgn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Ref_08` FOREIGN KEY (`id_kaf`)
    REFERENCES `kafedra`(`id_kaf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table student
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id_stud` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_name` char( 50 ) NOT NULL,
  `s_name` char( 50 ) NOT NULL,
  `fat_name` char( 50 ) NOT NULL,
  `nz` int( 6 ) UNSIGNED NOT NULL,
  `id_grup` int( 11 ) UNSIGNED NOT NULL,
  PRIMARY KEY(`id_stud`),
  CONSTRAINT `Ref_03` FOREIGN KEY (`id_grup`)
    REFERENCES `gruppa`(`id_grup`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table kursova
DROP TABLE IF EXISTS `kursova`;

CREATE TABLE `kursova` (
  `id_kursovoi` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tema` char( 200 ) NOT NULL,
  `id_prep` int( 11 ) UNSIGNED NOT NULL,
  `lim_stud` int( 11 ) UNSIGNED NOT NULL,
  `lim_client` int( 11 ) UNSIGNED NOT NULL,
  PRIMARY KEY(`id_kursovoi`),
  CONSTRAINT `Ref_06` FOREIGN KEY (`id_prep`)
    REFERENCES `prepod`(`id_prep`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

-- Drop table result
DROP TABLE IF EXISTS `result`;

CREATE TABLE `result` (
  `id_stud` int( 11 ) UNSIGNED NOT NULL,
  `id_kursovoi` int( 11 ),
  INDEX `ind_stud_kursov`(`id_stud`, `id_kursovoi`),
  CONSTRAINT `Ref_01` FOREIGN KEY (`id_kursovoi`)
    REFERENCES `kursova`(`id_kursovoi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Ref_02` FOREIGN KEY (`id_stud`)
    REFERENCES `student`(`id_stud`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

SET FOREIGN_KEY_CHECKS= 1 ;