系统分析与设计Lesson7作业

个人作业

领域建模

• 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 workbench画系统的E-R模型图：
    

  • 导出 Mysql 物理数据库的脚本。

-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 20:29:38 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`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `Customer_ID` INT NOT NULL,
  `Name` VARCHAR(45) NOT NULL,
  `EmailAddress` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Customer_ID`),
  UNIQUE INDEX `Customer_ID_UNIQUE` (`Customer_ID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ShoppingBasket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ShoppingBasket` (
  `ShoppingBasket_ID` INT NOT NULL,
  `Customer_Customer_ID` INT NOT NULL,
  `Info` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ShoppingBasket_ID`, `Customer_Customer_ID`),
  UNIQUE INDEX `ShoppingBasket_ID_UNIQUE` (`ShoppingBasket_ID` ASC),
  INDEX `fk_ShoppingBasket_Customer_idx` (`Customer_Customer_ID` ASC),
  CONSTRAINT `fk_ShoppingBasket_Customer`
    FOREIGN KEY (`Customer_Customer_ID`)
    REFERENCES `mydb`.`Customer` (`Customer_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,
  `ShoppingBasket_ShoppingBasket_ID` INT NOT NULL,
  `Check-in Date` DATE NOT NULL,
  `Check-out Date` DATE NOT NULL,
  `Price` INT NOT NULL,
  `Note` VARCHAR(45) NULL,
  PRIMARY KEY (`Reservation_ID`, `ShoppingBasket_ShoppingBasket_ID`),
  UNIQUE INDEX `Reservation_ID_UNIQUE` (`Reservation_ID` ASC),
  INDEX `fk_Reservation_ShoppingBasket1_idx` (`ShoppingBasket_ShoppingBasket_ID` ASC),
  CONSTRAINT `fk_Reservation_ShoppingBasket1`
    FOREIGN KEY (`ShoppingBasket_ShoppingBasket_ID`)
    REFERENCES `mydb`.`ShoppingBasket` (`ShoppingBasket_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Address` (
  `Address_ID` INT NOT NULL,
  `Country` VARCHAR(45) NOT NULL,
  `Region` VARCHAR(45) NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `Town` VARCHAR(45) NOT NULL,
  `Code` INT NOT NULL,
  UNIQUE INDEX `Address_ID_UNIQUE` (`Address_ID` ASC),
  PRIMARY KEY (`Address_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `Hotel_ID` INT NOT NULL,
  `Address_Address_ID` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `star` INT NOT NULL,
  `hot_index` FLOAT NOT NULL,
  `Info` VARCHAR(45) NULL,
  PRIMARY KEY (`Hotel_ID`, `Address_Address_ID`),
  UNIQUE INDEX `Hotel_ID_UNIQUE` (`Hotel_ID` ASC),
  INDEX `fk_Hotel_Address1_idx` (`Address_Address_ID` ASC),
  CONSTRAINT `fk_Hotel_Address1`
    FOREIGN KEY (`Address_Address_ID`)
    REFERENCES `mydb`.`Address` (`Address_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `Room_ID` INT NOT NULL,
  `Reservation_Reservation_ID` INT NOT NULL,
  `Hotel_Hotel_ID` INT NOT NULL,
  `num` INT NOT NULL,
  `isAvailable` TINYINT NOT NULL,
  `price` INT NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Room_ID`, `Reservation_Reservation_ID`, `Hotel_Hotel_ID`),
  UNIQUE INDEX `Room_ID_UNIQUE` (`Room_ID` ASC),
  INDEX `fk_Room_Reservation1_idx` (`Reservation_Reservation_ID` ASC),
  INDEX `fk_Room_Hotel1_idx` (`Hotel_Hotel_ID` ASC),
  CONSTRAINT `fk_Room_Reservation1`
    FOREIGN KEY (`Reservation_Reservation_ID`)
    REFERENCES `mydb`.`Reservation` (`Reservation_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Room_Hotel1`
    FOREIGN KEY (`Hotel_Hotel_ID`)
    REFERENCES `mydb`.`Hotel` (`Hotel_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;

• 简单叙说 数据库逻辑模型 与 领域模型 的异同。
  
  相同点：都反映了实体之间的关系，定义了实体名和相关属性。
  
  不同点：
  
  • 领域模型更偏向相关业务概念，来源于相关业务用例的描述，可以帮助更好地理解业务需求。
  • 数据库逻辑模型更偏向于开发者，考虑了系统数据的存储，着重于系统的实现，考虑实体间的约束关系，是领域模型的细化。