系统分析与设计_lesson7

最新推荐文章于 2024-09-04 17:11:13 发布

陈小星_绛菱纱影

最新推荐文章于 2024-09-04 17:11:13 发布

阅读量291

点赞数

分类专栏：系统分析与设计文章标签：数据库设计领域模型应用

本文链接：https://blog.csdn.net/a657997301/article/details/80141981

版权

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

8 篇文章 0 订阅

订阅专栏

领域建模

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 物理数据库的脚本

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

-- MySQL Script generated by MySQL Workbench
-- 04/29/18 18:52:00
-- 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
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

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

CREATE TABLE IF NOT EXISTS `mydb`.`CardHolder` (
  `identity number` VARCHAR(45) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `address` MEDIUMTEXT NOT NULL,
  `phone number` MEDIUMTEXT NOT NULL,
  PRIMARY KEY (`identity number`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `identity` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`CreditCard` (
  `card number` MEDIUMTEXT NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  `expiry date` DATE NOT NULL,
  `CardHolder_identity number` VARCHAR(45) NOT NULL,
  `Customer_id` INT NOT NULL,
  PRIMARY KEY (`card number`, `CardHolder_identity number`),
  INDEX `fk_CreditCard_CardHolder_idx` (`CardHolder_identity number` ASC),
  INDEX `fk_CreditCard_Customer1_idx` (`Customer_id` ASC),
  CONSTRAINT `fk_CreditCard_CardHolder`
    FOREIGN KEY (`CardHolder_identity number`)
    REFERENCES `mydb`.`CardHolder` (`identity number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CreditCard_Customer1`
    FOREIGN KEY (`Customer_id`)
    REFERENCES `mydb`.`Customer` (`id`)
    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(45) NULL,
  `CreditCard_card number1` MEDIUMTEXT NOT NULL,
  `CreditCard_CardHolder_identity number` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`, `CreditCard_card number1`, `CreditCard_CardHolder_identity number`),
  INDEX `fk_Payment_CreditCard1_idx` (`CreditCard_card number1` ASC, `CreditCard_CardHolder_identity number` ASC),
  CONSTRAINT `fk_Payment_CreditCard1`
    FOREIGN KEY (`CreditCard_card number1` , `CreditCard_CardHolder_identity number`)
    REFERENCES `mydb`.`CreditCard` (`card number` , `CardHolder_identity number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `name` INT NOT NULL,
  `address` VARCHAR(45) NULL,
  `star rating` VARCHAR(45) NULL,
  `price` VARCHAR(45) GENERATED ALWAYS AS () VIRTUAL,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`Reservation` (
  `id` INT NOT NULL,
  `order date` DATE NULL,
  `check in date` DATE NULL,
  `check out date` DATE NULL,
  `total rooms' number` INT NULL,
  `Hotel_name` INT NOT NULL,
  `Payment_ID` INT NOT NULL,
  `Payment_CreditCard_card number1` MEDIUMTEXT NOT NULL,
  `Payment_CreditCard_CardHolder_identity number` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `Payment_ID`, `Payment_CreditCard_card number1`, `Payment_CreditCard_CardHolder_identity number`),
  INDEX `fk_Reservation_Customer1_idx` (`order date` ASC),
  INDEX `fk_Reservation_Hotel1_idx` (`Hotel_name` ASC),
  INDEX `fk_Reservation_Payment1_idx` (`Payment_ID` ASC, `Payment_CreditCard_card number1` ASC, `Payment_CreditCard_CardHolder_identity number` ASC),
  CONSTRAINT `fk_Reservation_Customer1`
    FOREIGN KEY (`order date`)
    REFERENCES `mydb`.`Customer` (`name`)
    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,
  CONSTRAINT `fk_Reservation_Payment1`
    FOREIGN KEY (`Payment_ID` , `Payment_CreditCard_card number1` , `Payment_CreditCard_CardHolder_identity number`)
    REFERENCES `mydb`.`Payment` (`ID` , `CreditCard_card number1` , `CreditCard_CardHolder_identity number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `id` INT NOT NULL,
  `type` VARCHAR(45) NULL,
  `price` VARCHAR(45) NULL,
  `Hotel_name` INT NOT NULL,
  PRIMARY KEY (`id`, `Hotel_name`),
  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`.`OrderItem`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`OrderItem` ;

CREATE TABLE IF NOT EXISTS `mydb`.`OrderItem` (
  `id` INT NOT NULL,
  `adult` VARCHAR(45) NULL,
  `children` VARCHAR(45) NULL,
  `Room_id` INT NOT NULL,
  `Room_Hotel_name` INT NOT NULL,
  PRIMARY KEY (`id`, `Room_id`, `Room_Hotel_name`),
  INDEX `fk_OrderItem_Room1_idx` (`Room_id` ASC, `Room_Hotel_name` ASC),
  CONSTRAINT `fk_OrderItem_Room1`
    FOREIGN KEY (`Room_id` , `Room_Hotel_name`)
    REFERENCES `mydb`.`Room` (`id` , `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;

简单叙说数据库逻辑模型与领域模型的异同
- 相同点：
  - 都是抽象概念，将需求抽象为可视化的概念关系。
  - 都关注用例和用户的参与。
- 不同点：
  - 领域模型是按用户的观点对数据和信息建模，是现实世界到信息世界的第一层抽象。
  - 数据库逻辑模型是按计算机系统的观点对数据建模，是现实世界数据特征的抽象，用于DBMS的实现（层次模型，网状模型，关系模型）