系统分析与设计 - Homework5 - Summer的博客

领域建模

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

构建的领域模型图如下：

homework7_a

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

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

对Task3进行数据逻辑建模结果如下：

homework7_b

导出Mysql物理数据库脚本如下：

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
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

-- -----------------------------------------------------
-- Table `mydb`.`Customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Customer` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `identity` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Hotel` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `name` VARCHAR(50) NOT NULL,
  `lowestPrice` INT NOT NULL,
  `starRating` INT NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Room` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `#room` VARCHAR(10) NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  `price` INT NOT NULL,
  `Hotel_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`#room`, `Hotel_name`),
  UNIQUE INDEX `#room_UNIQUE` (`#room` ASC),
  INDEX `fk_Room_Hotel1_idx` (`Hotel_name` ASC),
  CONSTRAINT `fk_Room_Hotel1`
    FOREIGN KEY (`Hotel_name`)
    REFERENCES `mydb`.`Hotel` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CardHolder`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`CardHolder` ;

CREATE TABLE IF NOT EXISTS `mydb`.`CardHolder` (
  `ID` VARCHAR(50) NOT NULL,
  `name` VARCHAR(10) NOT NULL,
  `phone number` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CreditCard`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`CreditCard` ;

CREATE TABLE IF NOT EXISTS `mydb`.`CreditCard` (
  `card number` VARCHAR(50) NOT NULL,
  `type` VARCHAR(10) NOT NULL,
  `Customer_ID` INT NOT NULL,
  `CardHolder_ID` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`card number`, `CardHolder_ID`),
  INDEX `fk_CreditCard_Customer1_idx` (`Customer_ID` ASC),
  INDEX `fk_CreditCard_CardHolder1_idx` (`CardHolder_ID` ASC),
  CONSTRAINT `fk_CreditCard_Customer1`
    FOREIGN KEY (`Customer_ID`)
    REFERENCES `mydb`.`Customer` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CreditCard_CardHolder1`
    FOREIGN KEY (`CardHolder_ID`)
    REFERENCES `mydb`.`CardHolder` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Reservation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Reservation` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Reservation` (
  `ID` INT NOT NULL,
  `#adult` INT NULL,
  `#children` INT NULL,
  `check-in date` VARCHAR(45) NOT NULL,
  `check-out date` VARCHAR(45) NOT NULL,
  `Customer_ID` INT NOT NULL,
  `Hotel_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`ID`, `Customer_ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),
  INDEX `fk_Reservation_Customer1_idx` (`Customer_ID` ASC),
  INDEX `fk_Reservation_Hotel1_idx` (`Hotel_name` ASC),
  CONSTRAINT `fk_Reservation_Customer1`
    FOREIGN KEY (`Customer_ID`)
    REFERENCES `mydb`.`Customer` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Reservation_Hotel1`
    FOREIGN KEY (`Hotel_name`)
    REFERENCES `mydb`.`Hotel` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Payment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Payment` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Payment` (
  `ID` INT NOT NULL,
  `total price` INT NOT NULL,
  `CreditCard_card number` VARCHAR(50) NOT NULL,
  `CreditCard_CardHolder_ID` VARCHAR(50) NOT NULL,
  `Reservation_ID` INT NOT NULL,
  `Reservation_Customer_ID` INT NOT NULL,
  PRIMARY KEY (`ID`, `CreditCard_card number`, `CreditCard_CardHolder_ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),
  INDEX `fk_Payment_CreditCard1_idx` (`CreditCard_card number` ASC, `CreditCard_CardHolder_ID` ASC),
  INDEX `fk_Payment_Reservation1_idx` (`Reservation_ID` ASC, `Reservation_Customer_ID` ASC),
  CONSTRAINT `fk_Payment_CreditCard1`
    FOREIGN KEY (`CreditCard_card number` , `CreditCard_CardHolder_ID`)
    REFERENCES `mydb`.`CreditCard` (`card number` , `CardHolder_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Payment_Reservation1`
    FOREIGN KEY (`Reservation_ID` , `Reservation_Customer_ID`)
    REFERENCES `mydb`.`Reservation` (`ID` , `Customer_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Customer_has_Hotel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Customer_has_Hotel` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Customer_has_Hotel` (
  `Customer_ID` INT NOT NULL,
  `Hotel_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`Customer_ID`, `Hotel_name`),
  INDEX `fk_Customer_has_Hotel_Hotel1_idx` (`Hotel_name` ASC),
  INDEX `fk_Customer_has_Hotel_Customer_idx` (`Customer_ID` ASC),
  CONSTRAINT `fk_Customer_has_Hotel_Customer`
    FOREIGN KEY (`Customer_ID`)
    REFERENCES `mydb`.`Customer` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customer_has_Hotel_Hotel1`
    FOREIGN KEY (`Hotel_name`)
    REFERENCES `mydb`.`Hotel` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Customer_has_Hotel1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Customer_has_Hotel1` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Customer_has_Hotel1` (
  `Customer_ID` INT NOT NULL,
  `Hotel_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`Customer_ID`, `Hotel_name`),
  INDEX `fk_Customer_has_Hotel1_Hotel1_idx` (`Hotel_name` ASC),
  INDEX `fk_Customer_has_Hotel1_Customer1_idx` (`Customer_ID` ASC),
  CONSTRAINT `fk_Customer_has_Hotel1_Customer1`
    FOREIGN KEY (`Customer_ID`)
    REFERENCES `mydb`.`Customer` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Customer_has_Hotel1_Hotel1`
    FOREIGN KEY (`Hotel_name`)
    REFERENCES `mydb`.`Hotel` (`name`)
    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;

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

领域模型：是在了解用户的需求，用户的业务领域工作情况后，经过分析和总结，提炼出来的用于描述用户业务需求的一些概念性的东西。
数据库逻辑模型：将概念模型具体化，要实现概念模型所描述的东西，需要建立几个数据表，需要包括哪几个功能。同：都是在产品开发过程中比较重要的建模过程，都需要分离出具体的类及其属性。

领域建模

FEATURED TAGS