系统分析与设计-hw5

系统分析与设计作业（五）–领域建模

1. 领域建模

a. 阅读 Asg_RH 文档，按用例构建领域模型

b. 数据库建模(E-R 模型)

ER图

说明：

• 由于一个reservation并不一定会在创建的时候就马上付款，所以reservation和payment之间是1对0或1的关系，为了实现这个，在reservation的外键不能设为NOT NULL

数据库逻辑模型 与 领域模型 的异同

• 领域模型是一个商业建模范畴的概念，他和软件开发没有关系，是用来表述某一领域的业务模型
• 而数据库逻辑模型是一个软件开发领域的概念，虽然能在一定程度上表示业务体系，但是主要是为了表述数据库中的结构
• 数据库逻辑模型一般由领域模型得到

数据库脚本

-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 20:42:32 2018
-- 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 mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`traveler`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`traveler` (
  `idtraveler` INT NOT NULL,
  `favourites` VARCHAR(45) NULL,
  `gender` TINYINT NULL,
  `full name` VARCHAR(45) NULL,
  `email address` VARCHAR(45) NULL,
  PRIMARY KEY (`idtraveler`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`location` (
  `idlocation` INT NOT NULL,
  `code` VARCHAR(45) NULL,
  `name` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idlocation`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hotel` (
  `idhotel` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `rating` VARCHAR(45) NULL,
  `location_idlocation` INT NOT NULL,
  PRIMARY KEY (`idhotel`),
  INDEX `fk_hotel_location1_idx` (`location_idlocation` ASC),
  CONSTRAINT `fk_hotel_location1`
    FOREIGN KEY (`location_idlocation`)
    REFERENCES `mydb`.`location` (`idlocation`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room` (
  `idroom` INT NOT NULL,
  `type` VARCHAR(45) NULL,
  `price` INT NULL,
  `hotel_idhotel` INT NOT NULL,
  PRIMARY KEY (`idroom`),
  INDEX `fk_room_hotel_idx` (`hotel_idhotel` ASC),
  CONSTRAINT `fk_room_hotel`
    FOREIGN KEY (`hotel_idhotel`)
    REFERENCES `mydb`.`hotel` (`idhotel`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`room_state`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room_state` (
  `idroom_state` INT NOT NULL,
  `start_date` DATETIME NULL,
  `end_date` DATETIME NULL,
  `status` VARCHAR(45) NULL,
  `room_idroom` INT NOT NULL,
  PRIMARY KEY (`idroom_state`),
  INDEX `fk_room_state_room1_idx` (`room_idroom` ASC),
  CONSTRAINT `fk_room_state_room1`
    FOREIGN KEY (`room_idroom`)
    REFERENCES `mydb`.`room` (`idroom`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payment_tool`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payment_tool` (
  `idpayment_tool` INT NOT NULL,
  `type` VARCHAR(45) NULL,
  `traveler_idtraveler` INT NOT NULL,
  PRIMARY KEY (`idpayment_tool`),
  INDEX `fk_payment_tool_traveler1_idx` (`traveler_idtraveler` ASC),
  CONSTRAINT `fk_payment_tool_traveler1`
    FOREIGN KEY (`traveler_idtraveler`)
    REFERENCES `mydb`.`traveler` (`idtraveler`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
  `idpayment` INT NOT NULL,
  `payment_tool_idpayment_tool` INT NOT NULL,
  `amount` REAL NULL,
  PRIMARY KEY (`idpayment`),
  INDEX `fk_payment_payment_tool1_idx` (`payment_tool_idpayment_tool` ASC),
  CONSTRAINT `fk_payment_payment_tool1`
    FOREIGN KEY (`payment_tool_idpayment_tool`)
    REFERENCES `mydb`.`payment_tool` (`idpayment_tool`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reservation` (
  `idreservation` INT NOT NULL,
  `room_state_idroom_state` INT NOT NULL,
  `price` INT NULL,
  `traveler_idtraveler` INT NOT NULL,
  `room_idroom` INT NOT NULL,
  `start_date` DATETIME NULL,
  `end_date` DATETIME NULL,
  `payment_idpayment` INT NULL,
  `add_time` DATETIME NULL,
  PRIMARY KEY (`idreservation`, `room_state_idroom_state`),
  INDEX `fk_reservation_traveler1_idx` (`traveler_idtraveler` ASC),
  INDEX `fk_reservation_room1_idx` (`room_idroom` ASC),
  INDEX `fk_reservation_room_state1_idx` (`room_state_idroom_state` ASC),
  INDEX `fk_reservation_payment1_idx` (`payment_idpayment` ASC),
  CONSTRAINT `fk_reservation_traveler1`
    FOREIGN KEY (`traveler_idtraveler`)
    REFERENCES `mydb`.`traveler` (`idtraveler`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservation_room1`
    FOREIGN KEY (`room_idroom`)
    REFERENCES `mydb`.`room` (`idroom`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservation_room_state1`
    FOREIGN KEY (`room_state_idroom_state`)
    REFERENCES `mydb`.`room_state` (`idroom_state`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservation_payment1`
    FOREIGN KEY (`payment_idpayment`)
    REFERENCES `mydb`.`payment` (`idpayment`)
    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;