系统分析与设计-HW5

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

• 按 Task2 要求，请使用工具 UMLet，截图格式务必是 png 并控制尺寸
• 说明：请不要受 PCMEF 层次结构影响。你需要识别实体（E）和 中介实体（M，也称状态实体）
  • 在单页面应用（如 vue）中，E 一般与数据库构建有关， M 一般与 store 模式 有关
  • 在 java web 应用中，E 一般与数据库构建有关， M 一般与 session 有关

                

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

    - 按 Task 3 要求，给出系统的 E-R 模型（数据逻辑模型）
    - 导出 Mysql 物理数据库的脚本
    - 简单叙说 数据库逻辑模型 与 领域模型 的异同

        

-- MySQL Script generated by MySQL Workbench
-- Mon Apr 29 01:47:30 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`.`OrderItem`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`OrderItem` (
  `order id` INT NOT NULL,
  `adults' number` INT NOT NULL,
  `children' number` INT NOT NULL,
  `age of children` INT NOT NULL,
  PRIMARY KEY (`order id`),
  UNIQUE INDEX `idorderItem_UNIQUE` (`order id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `room id` INT NOT NULL,
  `room type` MEDIUMTEXT NULL,
  `availability` TINYINT NOT NULL,
  `OrderItem_order id` INT NOT NULL,
  PRIMARY KEY (`room id`, `OrderItem_order id`),
  UNIQUE INDEX `room number_UNIQUE` (`room id` ASC),
  INDEX `fk_Room_OrderItem1_idx` (`OrderItem_order id` ASC),
  CONSTRAINT `fk_Room_OrderItem1`
    FOREIGN KEY (`OrderItem_order id`)
    REFERENCES `mydb`.`OrderItem` (`order id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `name` TINYTEXT NOT NULL,
  `lacation` LONGTEXT NOT NULL,
  `stars` INT NOT NULL,
  `Room_room number` INT NOT NULL,
  PRIMARY KEY (`name`, `Room_room number`),
  INDEX `fk_Hotel_Room_idx` (`Room_room number` ASC),
  CONSTRAINT `fk_Hotel_Room`
    FOREIGN KEY (`Room_room number`)
    REFERENCES `mydb`.`Room` (`room id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
  `payment id` INT NOT NULL,
  `total money` INT NOT NULL,
  `payment method` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`payment id`),
  UNIQUE INDEX `idpayment_UNIQUE` (`payment id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Credit card`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Credit card` (
  `credit card id` INT NOT NULL,
  `account name` VARCHAR(45) NOT NULL,
  `money left` INT NOT NULL,
  `payment_payment id` INT NOT NULL,
  PRIMARY KEY (`credit card id`, `payment_payment id`),
  UNIQUE INDEX `credit card id_UNIQUE` (`credit card id` ASC),
  INDEX `fk_Credit card_payment1_idx` (`payment_payment id` ASC),
  CONSTRAINT `fk_Credit card_payment1`
    FOREIGN KEY (`payment_payment id`)
    REFERENCES `mydb`.`payment` (`payment id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Reservation` (
  `reservation id` INT NOT NULL,
  `reservate date` VARCHAR(45) NOT NULL,
  `check-in date` VARCHAR(45) NOT NULL,
  `check-out date` VARCHAR(45) NOT NULL,
  `number of roomes` INT NOT NULL,
  `total price` INT NOT NULL,
  `Room_room number` INT NOT NULL,
  `OrderItem_order id` INT NOT NULL,
  `payment_payment id` INT NOT NULL,
  PRIMARY KEY (`reservation id`, `Room_room number`, `OrderItem_order id`, `payment_payment id`),
  UNIQUE INDEX `reservation id_UNIQUE` (`reservation id` ASC),
  INDEX `fk_Reservation_Room1_idx` (`Room_room number` ASC),
  INDEX `fk_Reservation_OrderItem1_idx` (`OrderItem_order id` ASC),
  INDEX `fk_Reservation_payment1_idx` (`payment_payment id` ASC),
  CONSTRAINT `fk_Reservation_Room1`
    FOREIGN KEY (`Room_room number`)
    REFERENCES `mydb`.`Room` (`room id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Reservation_OrderItem1`
    FOREIGN KEY (`OrderItem_order id`)
    REFERENCES `mydb`.`OrderItem` (`order id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Reservation_payment1`
    FOREIGN KEY (`payment_payment id`)
    REFERENCES `mydb`.`payment` (`payment id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `customer id` INT NOT NULL,
  `fullname` VARCHAR(45) NOT NULL,
  `email adress` VARCHAR(45) NOT NULL,
  `is somking` TINYINT NOT NULL,
  `sex` VARCHAR(45) NOT NULL,
  `Reservation_reservation id` INT NOT NULL,
  `Reservation_Room_room number` INT NOT NULL,
  `Reservation_OrderItem_order id` INT NOT NULL,
  PRIMARY KEY (`customer id`, `Reservation_reservation id`, `Reservation_Room_room number`, `Reservation_OrderItem_order id`),
  UNIQUE INDEX `customer id_UNIQUE` (`customer id` ASC),
  INDEX `fk_Customer_Reservation1_idx` (`Reservation_reservation id` ASC, `Reservation_Room_room number` ASC, `Reservation_OrderItem_order id` ASC),
  CONSTRAINT `fk_Customer_Reservation1`
    FOREIGN KEY (`Reservation_reservation id` , `Reservation_Room_room number` , `Reservation_OrderItem_order id`)
    REFERENCES `mydb`.`Reservation` (`reservation id` , `Room_room number` , `OrderItem_order 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;

异同比较：

• 相同点： 都是利用抽象概念的方法，其构成的基本元素都是类，属性以及关联。
• 不同点： 领域建模专注于功能模块、注重整体性，力求将用例中的概念以及关系更清晰地表现出来；
                而数据库建模则更专注于某一个数据库表操作，将概念问题转化为数据的存储问题。