SHARE
TWEET

Untitled

a guest May 16th, 2019 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  2. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  3. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  4.  
  5. -- -----------------------------------------------------
  6. -- Schema mydb
  7. -- -----------------------------------------------------
  8. DROP SCHEMA IF EXISTS `mydb` ;
  9.  
  10. -- -----------------------------------------------------
  11. -- Schema mydb
  12. -- -----------------------------------------------------
  13. CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
  14. USE `mydb` ;
  15.  
  16. -- -----------------------------------------------------
  17. -- Table `mydb`.`k_staff`
  18. -- -----------------------------------------------------
  19. DROP TABLE IF EXISTS `mydb`.`k_staff` ;
  20.  
  21. CREATE TABLE IF NOT EXISTS `mydb`.`k_staff` (
  22.   `staff_num` INT auto_increment,
  23.   `staff_name` VARCHAR(45) NOT NULL,
  24.   `staff_post` VARCHAR(45) NULL,
  25.   `staff_hiredate` DATE NULL,
  26.   `staff_termdate` DATE NULL,
  27.   `K_dept_dept_num` INT NOT NULL,
  28.   PRIMARY KEY (`staff_num`),
  29.   INDEX `fk_k_staff_K_dept1_idx` (`K_dept_dept_num` ASC),
  30.   CONSTRAINT `fk_k_staff_K_dept1`
  31.     FOREIGN KEY (`K_dept_dept_num`)
  32.     REFERENCES `mydb`.`K_dept` (`dept_num`)
  33.     ON DELETE NO ACTION
  34.     ON UPDATE NO ACTION)
  35. ENGINE = InnoDB;
  36.  
  37.  
  38. -- -----------------------------------------------------
  39. -- Table `mydb`.`K_dept`
  40. -- -----------------------------------------------------
  41. DROP TABLE IF EXISTS `mydb`.`K_dept` ;
  42.  
  43. CREATE TABLE IF NOT EXISTS `mydb`.`K_dept` (
  44.   `dept_num` INT auto_increment,
  45.   `dept_full_name` VARCHAR(45) NULL,
  46.   `dept_short_name` VARCHAR(10) NULL,
  47.   `k_staff_staff_num` INT NOT NULL,
  48.   PRIMARY KEY (`dept_num`),
  49.   INDEX `fk_K_dept_k_staff_idx` (`k_staff_staff_num` ASC),
  50.   CONSTRAINT `fk_K_dept_k_staff`
  51.     FOREIGN KEY (`k_staff_staff_num`)
  52.     REFERENCES `mydb`.`k_staff` (`staff_num`)
  53.     ON DELETE NO ACTION
  54.     ON UPDATE NO ACTION)
  55. ENGINE = InnoDB;
  56.  
  57.  
  58. -- -----------------------------------------------------
  59. -- Table `mydb`.`k_firm`
  60. -- -----------------------------------------------------
  61. DROP TABLE IF EXISTS `mydb`.`k_firm` ;
  62.  
  63. CREATE TABLE IF NOT EXISTS `mydb`.`k_firm` (
  64.   `firm_num` INT auto_increment,
  65.   `firm_name` VARCHAR(45) NOT NULL,
  66.   `firm_addr` VARCHAR(45) NULL,
  67.   `firm_phone` VARCHAR(20) NULL,
  68.   PRIMARY KEY (`firm_num`))
  69. ENGINE = InnoDB;
  70.  
  71.  
  72. -- -----------------------------------------------------
  73. -- Table `mydb`.`k_contract`
  74. -- -----------------------------------------------------
  75. DROP TABLE IF EXISTS `mydb`.`k_contract` ;
  76.  
  77. CREATE TABLE IF NOT EXISTS `mydb`.`k_contract` (
  78.   `contract_num` INT auto_increment,
  79.   `contract_date` DATE NULL,
  80.   `contract_type` ENUM('A', 'B', 'C') NULL,
  81.   `k_firm_firm_num` INT NOT NULL,
  82.   `k_staff_staff_num` INT NOT NULL,
  83.   PRIMARY KEY (`contract_num`),
  84.   INDEX `fk_k_contract_k_firm1_idx` (`k_firm_firm_num` ASC),
  85.   INDEX `fk_k_contract_k_staff1_idx` (`k_staff_staff_num` ASC),
  86.   CONSTRAINT `fk_k_contract_k_firm1`
  87.     FOREIGN KEY (`k_firm_firm_num`)
  88.     REFERENCES `mydb`.`k_firm` (`firm_num`)
  89.     ON DELETE NO ACTION
  90.     ON UPDATE NO ACTION,
  91.   CONSTRAINT `fk_k_contract_k_staff1`
  92.     FOREIGN KEY (`k_staff_staff_num`)
  93.     REFERENCES `mydb`.`k_staff` (`staff_num`)
  94.     ON DELETE NO ACTION
  95.     ON UPDATE NO ACTION)
  96. ENGINE = InnoDB;
  97.  
  98.  
  99. -- -----------------------------------------------------
  100. -- Table `mydb`.`k_bill`
  101. -- -----------------------------------------------------
  102. DROP TABLE IF EXISTS `mydb`.`k_bill` ;
  103.  
  104. CREATE TABLE IF NOT EXISTS `mydb`.`k_bill` (
  105.   `bill_num` INT auto_increment,
  106.   `bill_date` DATE NULL,
  107.   `bill_sum` DECIMAL(9,2) NULL,
  108.   `bill_term` DATE NULL,
  109.   `bill_peni` DECIMAL(6,2) NULL,
  110.   `k_contract_contract_num` INT NOT NULL,
  111.   PRIMARY KEY (`bill_num`),
  112.   INDEX `fk_k_bill_k_contract1_idx` (`k_contract_contract_num` ASC),
  113.   CONSTRAINT `fk_k_bill_k_contract1`
  114.     FOREIGN KEY (`k_contract_contract_num`)
  115.     REFERENCES `mydb`.`k_contract` (`contract_num`)
  116.     ON DELETE NO ACTION
  117.     ON UPDATE NO ACTION)
  118. ENGINE = InnoDB;
  119.  
  120.  
  121. -- -----------------------------------------------------
  122. -- Table `mydb`.`k_payment`
  123. -- -----------------------------------------------------
  124. DROP TABLE IF EXISTS `mydb`.`k_payment` ;
  125.  
  126. CREATE TABLE IF NOT EXISTS `mydb`.`k_payment` (
  127.   `payment_num` INT,
  128.   `payment_date` DATE NULL,
  129.   `payment_sum` DECIMAL(9,2) NULL,
  130.   `k_bill_bill_num` INT NOT NULL,
  131.   PRIMARY KEY (`payment_num`, `k_bill_bill_num`),
  132.   INDEX `fk_k_payment_k_bill1_idx` (`k_bill_bill_num` ASC),
  133.   CONSTRAINT `fk_k_payment_k_bill1`
  134.     FOREIGN KEY (`k_bill_bill_num`)
  135.     REFERENCES `mydb`.`k_bill` (`bill_num`)
  136.     ON DELETE NO ACTION
  137.     ON UPDATE NO ACTION)
  138. ENGINE = InnoDB;
  139.  
  140.  
  141. -- -----------------------------------------------------
  142. -- Table `mydb`.`k_price`
  143. -- -----------------------------------------------------
  144. DROP TABLE IF EXISTS `mydb`.`k_price` ;
  145.  
  146. CREATE TABLE IF NOT EXISTS `mydb`.`k_price` (
  147.   `price_num` INT auto_increment,
  148.   `price_name` VARCHAR(45) NOT NULL,
  149.   `price_sum` VARCHAR(45) NULL,
  150.   `price_type` VARCHAR(1) NULL,
  151.   PRIMARY KEY (`price_num`))
  152. ENGINE = InnoDB;
  153.  
  154.  
  155. -- -----------------------------------------------------
  156. -- Table `mydb`.`k_protokol`
  157. -- -----------------------------------------------------
  158. DROP TABLE IF EXISTS `mydb`.`k_protokol` ;
  159.  
  160. CREATE TABLE IF NOT EXISTS `mydb`.`k_protokol` (
  161.   `kolvo` INT NULL,
  162.   `price_sum` DECIMAL(9,2) NOT NULL,
  163.   `k_bill_bill_num` INT NOT NULL,
  164.   `k_price_price_num` INT NOT NULL,
  165.   PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),
  166.   INDEX `fk_k_protokol_k_price1_idx` (`k_price_price_num` ASC),
  167.   CONSTRAINT `fk_k_protokol_k_bill1`
  168.     FOREIGN KEY (`k_bill_bill_num`)
  169.     REFERENCES `mydb`.`k_bill` (`bill_num`)
  170.     ON DELETE NO ACTION
  171.     ON UPDATE NO ACTION,
  172.   CONSTRAINT `fk_k_protokol_k_price1`
  173.     FOREIGN KEY (`k_price_price_num`)
  174.     REFERENCES `mydb`.`k_price` (`price_num`)
  175.     ON DELETE NO ACTION
  176.     ON UPDATE NO ACTION)
  177. ENGINE = InnoDB;
  178.  
  179.  
  180. SET SQL_MODE=@OLD_SQL_MODE;
  181. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  182. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top