star schema data warehouse

star_schema

-- MySQL Script generated by MySQL Workbench
-- Tue Dec 13 14:41:45 2016
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema nhs1
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema nhs1
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `nhs1` DEFAULT CHARACTER SET utf8 ;
USE `nhs1` ;

-- -----------------------------------------------------
-- Table `nhs1`.`Patient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Patient` (
  `Patient_ID` INT NOT NULL,
  `Patient_DoB` DATE NULL,
  `Patient_Country` VARCHAR(70) NULL,
  `Patient_City` VARCHAR(70) NULL,
  `Patient_Occupation` VARCHAR(70) NULL,
  `Patient_Name` VARCHAR(255) NULL,
  `Patient_TelNo` VARCHAR(10) NULL,
  `Patient_Address` VARCHAR(255) NULL,
  `Patient_Email` VARCHAR(255) NULL,
  `Patient_County` VARCHAR(70) NULL,
  `Treat_ID` VARCHAR(70) NULL,
  `Op_Code` INT NULL,
  `Patient_NextOfKin` VARCHAR(255) NULL,
  `Patient_QtyOfMissedAppts` INT NULL,
  `ContdCare_ID` INT NULL,
  `Patient_AtRisk` VARCHAR(45) NULL,
  PRIMARY KEY (`Patient_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Treatment_Unit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Treatment_Unit` (
  `Unit_ID` INT NOT NULL,
  `Unit_Type` VARCHAR(45) NULL,
  `Unit_County` VARCHAR(70) NULL,
  `Unit_City` VARCHAR(70) NULL,
  `Treatment_Cost` VARCHAR(45) NULL,
  PRIMARY KEY (`Unit_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Staff` (
  `Staff_ID` INT NOT NULL,
  `Staff_Level` VARCHAR(20) NULL,
  `Staff_Specialisation` VARCHAR(70) NULL,
  `Staff_Name` VARCHAR(255) NULL,
  `Staff_WeeklyHrs` INT NULL,
  `Staff_Salary` INT NULL,
  `Staff_OvertimeRate` INT NULL,
  `Staff_OnCall` TINYINT(1) NULL,
  `Unit_City` VARCHAR(70) NULL,
  PRIMARY KEY (`Staff_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Drug_Stock`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Drug_Stock` (
  `DrugStock_ID` INT NOT NULL,
  `DrugStock_Cost` VARCHAR(45) NULL,
  `DrugStock_Type` VARCHAR(45) NULL,
  `DrugStock_Dosage` VARCHAR(45) NULL,
  `DrugStock_Manufacturer` VARCHAR(45) NULL,
  `DrugStock_Restrictions` VARCHAR(45) NULL,
  `DrugStock_Trial` VARCHAR(45) NULL,
  `DrugStock_ReceivedDate` DATE NULL,
  `DrugStock_IssueDate` VARCHAR(45) NULL,
  `DrugStock_ExpiryDate` VARCHAR(45) NULL,
  PRIMARY KEY (`DrugStock_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Treatment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Treatment` (
  `Treat_ID` INT NOT NULL,
  `Treat_Type` VARCHAR(45) NULL,
  `Patient_ID` VARCHAR(45) NULL,
  `Diagnosis` VARCHAR(45) NULL,
  `Unit_Code` VARCHAR(45) NULL,
  `Staff_ID` VARCHAR(45) NULL,
  `Treat_WaitTime` DATETIME NULL,
  PRIMARY KEY (`Treat_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Operation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Operation` (
  `Operation_Code` INT NOT NULL,
  `Operation_Type` VARCHAR(45) NULL,
  `Operation_Cost` VARCHAR(45) NULL,
  `Operation_Time` VARCHAR(45) NULL,
  `Operation_Equipment` VARCHAR(45) NULL,
  `Operation_Budget` VARCHAR(45) NULL,
  `Patient_ID` INT NULL,
  `Staff_ID` INT NULL,
  `Unit_Code` INT NULL,
  PRIMARY KEY (`Operation_Code`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Treatment_Drug`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Treatment_Drug` (
  `Drug_ID` INT NOT NULL,
  `TD_Date` VARCHAR(45) NULL,
  `TD_Duration` VARCHAR(45) NULL,
  `TD_Cost` VARCHAR(45) NULL,
  `Patient_ID` VARCHAR(45) NULL,
  `Unit_Code` VARCHAR(45) NULL,
  PRIMARY KEY (`Drug_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Continuing_Care`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Continuing_Care` (
  `ContCare_ID` INT NOT NULL,
  `Patient_ID` VARCHAR(45) NULL,
  `Unit_Code` VARCHAR(45) NULL,
  `Staff_ID` VARCHAR(45) NULL,
  `Treat_ID` VARCHAR(45) NULL,
  `ContCare_ApptDate` VARCHAR(45) NULL,
  `ContCare_ApptLength` VARCHAR(45) NULL,
  `ContCare_TotalNumberOfAppts` VARCHAR(45) NULL,
  PRIMARY KEY (`ContCare_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nhs1`.`Treatment_Fact`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nhs1`.`Treatment_Fact` (
  `TreatmentFact_ID` INT NOT NULL,
  `Patient_ID` INT NULL,
  `Unit_ID` INT NULL,
  `Staff_ID` INT NULL,
  `Operation_ID` INT NULL,
  `Drug_ID` INT NULL,
  `ContCare_ID` INT NULL,
  `DrugStock_ID` INT NULL,
  `Treat_ID` INT NULL,
  `Treatment_Date` DATETIME NULL,
  PRIMARY KEY (`TreatmentFact_ID`),
  INDEX `Operation_ID_idx` (`Operation_ID` ASC),
  INDEX `Drug_ID_idx` (`Drug_ID` ASC),
  INDEX `ContCare_ID_idx` (`ContCare_ID` ASC),
  INDEX `Staff_ID_idx` (`Staff_ID` ASC),
  INDEX `Treat_ID_idx` (`Treat_ID` ASC),
  INDEX `Unit_ID_idx` (`Unit_ID` ASC),
  CONSTRAINT `Patient_ID`
    FOREIGN KEY (`Patient_ID`)
    REFERENCES `nhs1`.`Patient` (`Patient_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Treat_ID`
    FOREIGN KEY (`Treat_ID`)
    REFERENCES `nhs1`.`Treatment` (`Treat_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Unit_ID`
    FOREIGN KEY (`Unit_ID`)
    REFERENCES `nhs1`.`Treatment_Unit` (`Unit_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Staff_ID`
    FOREIGN KEY (`Staff_ID`)
    REFERENCES `nhs1`.`Staff` (`Staff_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Treat_WaitTime`
    FOREIGN KEY ()
    REFERENCES `nhs1`.`Treatment` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Operation_ID`
    FOREIGN KEY (`Operation_ID`)
    REFERENCES `nhs1`.`Operation` (`Operation_Code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Drug_ID`
    FOREIGN KEY (`Drug_ID`)
    REFERENCES `nhs1`.`Treatment_Drug` (`Drug_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ContCare_ID`
    FOREIGN KEY (`ContCare_ID`)
    REFERENCES `nhs1`.`Continuing_Care` (`ContCare_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Drug_ID`
    FOREIGN KEY (`Drug_ID`)
    REFERENCES `nhs1`.`Treatment_Drug` (`Drug_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;