【系统分析与设计】作业4

一、 领域建模

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 物理数据库的脚本
• 简单叙说 数据库逻辑模型 与 领域模型 的异同

答：
①E-R逻辑模型图如下：

②导出SQL脚本如下：

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

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `hotelName` VARCHAR(45) NOT NULL ,
  `address` MEDIUMTEXT NOT NULL ,
  `stars` INT NULL ,
  PRIMARY KEY (`hotelName`) ,
  UNIQUE INDEX `address_UNIQUE` (`address` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`OrderItem`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`OrderItem` (
  `idOrderItem` INT NOT NULL ,
  `adults` INT NOT NULL ,
  `children` INT NOT NULL ,
  PRIMARY KEY (`idOrderItem`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Reservation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Reservation` (
  `idReservation` INT NOT NULL ,
  `reservate date` DATETIME NOT NULL ,
  `check in date` VARCHAR(45) NOT NULL ,
  `check out date` VARCHAR(45) NOT NULL ,
  `rooms count` INT NOT NULL ,
  PRIMARY KEY (`idReservation`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Room` (
  `roomNumber` INT NOT NULL ,
  `roomType` VARCHAR(45) NOT NULL ,
  `availability` TINYINT(1) NOT NULL ,
  `Hotel_hotelName` VARCHAR(45) NOT NULL ,
  `OrderItem_idOrderItem` INT NOT NULL ,
  `Reservation_idReservation` INT NOT NULL ,
  PRIMARY KEY (`roomNumber`, `Hotel_hotelName`, `OrderItem_idOrderItem`, `Reservation_idReservation`) ,
  INDEX `fk_Room_Hotel1_idx` (`Hotel_hotelName` ASC) ,
  INDEX `fk_Room_OrderItem1_idx` (`OrderItem_idOrderItem` ASC) ,
  INDEX `fk_Room_Reservation1_idx` (`Reservation_idReservation` ASC) ,
  CONSTRAINT `fk_Room_Hotel1`
    FOREIGN KEY (`Hotel_hotelName` )
    REFERENCES `mydb`.`Hotel` (`hotelName` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Room_OrderItem1`
    FOREIGN KEY (`OrderItem_idOrderItem` )
    REFERENCES `mydb`.`OrderItem` (`idOrderItem` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Room_Reservation1`
    FOREIGN KEY (`Reservation_idReservation` )
    REFERENCES `mydb`.`Reservation` (`idReservation` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`cardHolder`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`cardHolder` (
  `name` VARCHAR(45) NOT NULL ,
  `address` MEDIUMTEXT NOT NULL ,
  `post code` INT NOT NULL ,
  `phoneNumber` MEDIUMTEXT NOT NULL ,
  PRIMARY KEY (`name`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CreditCard`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`CreditCard` (
  `creditCardNumber` MEDIUMTEXT NOT NULL ,
  `type` VARCHAR(45) NOT NULL ,
  `security code` MEDIUMTEXT NOT NULL ,
  `expiry date` DATE NOT NULL ,
  `cardHolder` VARCHAR(45) NOT NULL ,
  `Payment_idPayment` INT NOT NULL ,
  `Payment_Reservation_idReservation` INT NOT NULL ,
  PRIMARY KEY (`creditCardNumber`, `Payment_idPayment`, `Payment_Reservation_idReservation`) ,
  UNIQUE INDEX `creditCardId_UNIQUE` (`creditCardNumber` ASC) ,
  INDEX `cardHolder_idx` (`cardHolder` ASC) ,
  INDEX `fk_CreditCard_Payment1_idx` (`Payment_idPayment` ASC, `Payment_Reservation_idReservation` ASC) ,
  CONSTRAINT `cardHolder`
    FOREIGN KEY (`cardHolder` )
    REFERENCES `mydb`.`cardHolder` (`name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CreditCard_Payment1`
    FOREIGN KEY (`Payment_idPayment` , `Payment_Reservation_idReservation` )
    REFERENCES `mydb`.`Payment` (`idPayment` , `Reservation_idReservation` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Payment`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Payment` (
  `idPayment` INT NOT NULL ,
  `money amount` INT NOT NULL ,
  `consumer` VARCHAR(45) NOT NULL ,
  `payingCard` MEDIUMTEXT NOT NULL ,
  `Reservation_idReservation` INT NOT NULL ,
  PRIMARY KEY (`idPayment`, `Reservation_idReservation`) ,
  UNIQUE INDEX `idPayment_UNIQUE` (`idPayment` ASC) ,
  INDEX `consumer_idx` (`consumer` ASC) ,
  INDEX `payingCard_idx` (`payingCard` ASC) ,
  INDEX `fk_Payment_Reservation1_idx` (`Reservation_idReservation` ASC) ,
  CONSTRAINT `consumer`
    FOREIGN KEY (`consumer` )
    REFERENCES `mydb`.`Consumer` (`name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `payingCard`
    FOREIGN KEY (`payingCard` )
    REFERENCES `mydb`.`CreditCard` (`creditCardNumber` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Payment_Reservation1`
    FOREIGN KEY (`Reservation_idReservation` )
    REFERENCES `mydb`.`Reservation` (`idReservation` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Consumer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Consumer` (
  `name` VARCHAR(45) NOT NULL ,
  `sex` VARCHAR(10) NOT NULL ,
  `isSmoking` TINYINT(1) NOT NULL ,
  `Payment_idPayment` INT NOT NULL ,
  `Payment_Reservation_idReservation` INT NOT NULL ,
  PRIMARY KEY (`name`, `Payment_idPayment`, `Payment_Reservation_idReservation`) ,
  INDEX `fk_Consumer_Payment1_idx` (`Payment_idPayment` ASC, `Payment_Reservation_idReservation` ASC) ,
  CONSTRAINT `fk_Consumer_Payment1`
    FOREIGN KEY (`Payment_idPayment` , `Payment_Reservation_idReservation` )
    REFERENCES `mydb`.`Payment` (`idPayment` , `Reservation_idReservation` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

③ 对比 数据库逻辑模型 与 领域模型的异同。
答：领域模型是对领域内的概念类或现实世界的对象的一种抽象的可视化表示。它主要关注问题域本身，挖掘问题域中核心的领域概念，并建立领域概念之间的关系。
而逻辑模型就是要将概念模型具体化。要实现概念模型所描述的东西，需要那些具体的功能和处理那些具体的信息。

相同点：抽象概念，将需求抽象为可视化的概念关系。
不同点：领域模型关注现实世界的行为、对象和各种概念；而逻辑模型则是领域模型的进一步细化，需要摒弃与具体实现无关的概念以及将需要的概念进一步细化成数据库、类对象等细节。