Hw7_20180429

最新推荐文章于 2022-05-18 09:49:01 发布

Llunnn

最新推荐文章于 2022-05-18 09:49:01 发布

阅读量404

点赞数

分类专栏：系统分析与设计

本文链接：https://blog.csdn.net/Llunnn/article/details/80146307

版权

系统分析与设计专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、领域建模

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 模型（数据逻辑模型）
  - 建模工具 PowerDesigner（简称PD）或开源工具 OpenSystemArchitect
  - 不负责的链接
  - 导出 Mysql 物理数据库的脚本
  - 简单叙说数据库逻辑模型与领域模型的异同

这里写图片描述

sql语句：

-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 22:32:58 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`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`location` (
  `loc_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `hot` INT NULL,
  PRIMARY KEY (`loc_id`));


-- -----------------------------------------------------
-- Table `mydb`.`hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hotel` (
  `hotel_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `address` VARCHAR(255) NULL,
  `location_loc_id` INT NOT NULL,
  PRIMARY KEY (`hotel_id`),
  UNIQUE INDEX `id_UNIQUE` (`hotel_id` ASC),
  INDEX `fk_hotel_location1_idx` (`location_loc_id` ASC),
  CONSTRAINT `fk_hotel_location1`
    FOREIGN KEY (`location_loc_id`)
    REFERENCES `mydb`.`location` (`loc_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `mydb`.`room_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room_type` (
  `type_id` INT NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `price` INT NULL,
  PRIMARY KEY (`type_id`));


-- -----------------------------------------------------
-- Table `mydb`.`room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room` (
  `room_id` INT NOT NULL,
  `hotel_id` INT NOT NULL,
  `price` INT NULL,
  `hotel_hotel_id` INT NOT NULL,
  `room_type_type_id` INT NOT NULL,
  PRIMARY KEY (`room_id`),
  INDEX `fk_room_hotel1_idx` (`hotel_hotel_id` ASC),
  INDEX `fk_room_room_type1_idx` (`room_type_type_id` ASC),
  CONSTRAINT `fk_room_hotel1`
    FOREIGN KEY (`hotel_hotel_id`)
    REFERENCES `mydb`.`hotel` (`hotel_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_room_room_type1`
    FOREIGN KEY (`room_type_type_id`)
    REFERENCES `mydb`.`room_type` (`type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `customer_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NULL,
  PRIMARY KEY (`customer_id`));


-- -----------------------------------------------------
-- Table `mydb`.`payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
  `payment_id` INT NOT NULL,
  `reservation_id` INT NOT NULL,
  `amount` FLOAT NOT NULL,
  `customer_customer_id` INT NOT NULL,
  PRIMARY KEY (`payment_id`, `customer_customer_id`));


-- -----------------------------------------------------
-- Table `mydb`.`reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reservation` (
  `reservation_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `children_num` INT NULL,
  `adults_num` INT NULL,
  `arrival_date` DATETIME NULL,
  `departure_date` DATETIME NULL,
  `customer_customer_id` INT NOT NULL,
  `payment_payment_id` INT NULL,
  `payment_customer_customer_id` INT NOT NULL,
  `room_room_id` INT NOT NULL,
  PRIMARY KEY (`reservation_id`),
  INDEX `fk_reservation_customer1_idx` (`customer_customer_id` ASC),
  INDEX `fk_reservation_payment1_idx` (`payment_payment_id` ASC, `payment_customer_customer_id` ASC),
  INDEX `fk_reservation_room1_idx` (`room_room_id` ASC),
  CONSTRAINT `fk_reservation_customer1`
    FOREIGN KEY (`customer_customer_id`)
    REFERENCES `mydb`.`customer` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservation_payment1`
    FOREIGN KEY (`payment_payment_id` , `payment_customer_customer_id`)
    REFERENCES `mydb`.`payment` (`payment_id` , `customer_customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservation_room1`
    FOREIGN KEY (`room_room_id`)
    REFERENCES `mydb`.`room` (`room_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `mydb`.`room_state`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`room_state` (
  `room_state_id` INT NOT NULL,
  `availability` INT NULL,
  `date` DATETIME NULL,
  `room_room_id` INT NOT NULL,
  `reservation_reservation_id` INT NOT NULL,
  PRIMARY KEY (`room_state_id`, `reservation_reservation_id`),
  INDEX `fk_room_state_room_idx` (`room_room_id` ASC),
  INDEX `fk_room_state_reservation1_idx` (`reservation_reservation_id` ASC),
  CONSTRAINT `fk_room_state_room`
    FOREIGN KEY (`room_room_id`)
    REFERENCES `mydb`.`room` (`room_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_room_state_reservation1`
    FOREIGN KEY (`reservation_reservation_id`)
    REFERENCES `mydb`.`reservation` (`reservation_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `mydb`.`credit_card`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`credit_card` (
  `credit_card_id` INT NOT NULL,
  `type` VARCHAR(255) NULL,
  `number` VARCHAR(45) NOT NULL,
  `customer_customer_id` INT NOT NULL,
  PRIMARY KEY (`credit_card_id`),
  INDEX `fk_credit_card_customer1_idx` (`customer_customer_id` ASC),
  CONSTRAINT `fk_credit_card_customer1`
    FOREIGN KEY (`customer_customer_id`)
    REFERENCES `mydb`.`customer` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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