Code: Select all
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `computer` DEFAULT CHARACTER SET latin1 ;
USE `computer` ;
-- -----------------------------------------------------
-- Table `computer`.`repair_actions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `computer`.`repair_actions` (
`idrepair_actions` INT NOT NULL AUTO_INCREMENT ,
`repair_id` INT(5) NOT NULL ,
`actioned_by` VARCHAR(20) NOT NULL ,
`repair_actions` TEXT(200) NOT NULL ,
`completes_repair` CHAR(1) NOT NULL COMMENT 'Will store a Y or N for completes repair?' ,
PRIMARY KEY (`idrepair_actions`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `computer`.`repairs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `computer`.`repairs` (
`computer_id` INT(5) NULL DEFAULT NULL ,
`repair_id` INT(5) NOT NULL ,
`client_id` INT(5) NULL DEFAULT NULL ,
`date_processed` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`issue_cat_id` INT(2) NULL DEFAULT NULL ,
`issue_desc` TEXT NULL DEFAULT NULL ,
`notes` VARCHAR(300) NULL DEFAULT NULL ,
`date_completed` DATETIME NULL DEFAULT NULL ,
`repair_actions_idrepair_actions` INT NOT NULL ,
PRIMARY KEY (`repair_id`) ,
INDEX `computer_id` (`computer_id` ASC) ,
INDEX `fk_repairs_repair_actions1` (`repair_actions_idrepair_actions` ASC) ,
CONSTRAINT `fk_repairs_repair_actions1`
FOREIGN KEY (`repair_actions_idrepair_actions` )
REFERENCES `computer`.`repair_actions` (`idrepair_actions` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `computer`.`computers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `computer`.`computers` (
`machine_serial` VARCHAR(30) NULL DEFAULT NULL ,
`computer_id` INT(5) NOT NULL ,
`machine_type` ENUM('L','D','O') NOT NULL ,
`machine_OS` VARCHAR(50) NULL DEFAULT NULL ,
`repairs_repair_id` INT(5) NOT NULL ,
PRIMARY KEY (`computer_id`) ,
UNIQUE INDEX `machine_serial` (`machine_serial` ASC) ,
INDEX `fk_computers_repairs` (`repairs_repair_id` ASC) ,
CONSTRAINT `fk_computers_repairs`
FOREIGN KEY (`repairs_repair_id` )
REFERENCES `computer`.`repairs` (`repair_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `computer`.`clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `computer`.`clients` (
`client_id` INT(5) NOT NULL AUTO_INCREMENT ,
`fname` VARCHAR(30) NOT NULL ,
`sname` VARCHAR(30) NOT NULL ,
`tel1` INT(15) NULL ,
`e-mail` VARCHAR(60) NULL ,
`street1` TEXT(50) NOT NULL ,
`postcode` VARCHAR(10) NOT NULL ,
`repairs_repair_id` INT(5) NOT NULL ,
PRIMARY KEY (`client_id`) ,
INDEX `fk_clients_repairs1` (`repairs_repair_id` ASC) ,
CONSTRAINT `fk_clients_repairs1`
FOREIGN KEY (`repairs_repair_id` )
REFERENCES `computer`.`repairs` (`repair_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `computer`.`issue_categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `computer`.`issue_categories` (
`issue_cat_id` INT(2) NOT NULL AUTO_INCREMENT ,
`cat_short_title` VARCHAR(20) NULL ,
`repairs_repair_id` INT(5) NOT NULL ,
PRIMARY KEY (`issue_cat_id`) ,
INDEX `fk_issue_categories_repairs1` (`repairs_repair_id` ASC) ,
CONSTRAINT `fk_issue_categories_repairs1`
FOREIGN KEY (`repairs_repair_id` )
REFERENCES `computer`.`repairs` (`repair_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;