System Analysis and Design Job 5(lesson 7)

Domain Modeling

Read the Asg_RH documentation and build a domain model by use case.

Database Modeling (ER Model)

Modeling tool MySQL Workbench

ER Model Diagram

Export MySQL database script

-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 19:59:53 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` (
  `code` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `hot` TINYINT NULL,
  PRIMARY KEY (`code`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hotel` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `star` INT NULL,
  `Location_code` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Hotel_Location_idx` (`Location_code` ASC),
  CONSTRAINT `fk_Hotel_Location`
    FOREIGN KEY (`Location_code`)
    REFERENCES `mydb`.`Location` (`code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`RoomDesc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RoomDesc` (
  `id` INT NOT NULL,
  `desc` VARCHAR(45) NULL,
  `listprice` JSON NULL,
  `Hotel_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_RoomDesc_Hotel1_idx` (`Hotel_id` ASC),
  CONSTRAINT `fk_RoomDesc_Hotel1`
    FOREIGN KEY (`Hotel_id`)
    REFERENCES `mydb`.`Hotel` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
  `id` INT NOT NULL,
  `date` DATE NULL,
  `isAvaliable` TINYINT NULL,
  `RoomDesc_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Room_RoomDesc1_idx` (`RoomDesc_id` ASC),
  CONSTRAINT `fk_Room_RoomDesc1`
    FOREIGN KEY (`RoomDesc_id`)
    REFERENCES `mydb`.`RoomDesc` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Account` (
  `id` INT NOT NULL,
  `username` VARCHAR(45) NULL,
  `passwd` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Sale`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sale` (
  `id` INT NOT NULL,
  `money` DOUBLE NULL,
  `ispaid` TINYINT NULL,
  `Room_id` INT NOT NULL,
  `Account_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Sale_Room1_idx` (`Room_id` ASC),
  INDEX `fk_Sale_Account1_idx` (`Account_id` ASC),
  CONSTRAINT `fk_Sale_Room1`
    FOREIGN KEY (`Room_id`)
    REFERENCES `mydb`.`Room` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sale_Account1`
    FOREIGN KEY (`Account_id`)
    REFERENCES `mydb`.`Account` (`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;

Simple Description of Differences and Differences between Database Logical Model and Domain Model

Same point: There are similarities in the concept of "entity", they all describe the relationship between entities, and each entity has corresponding attributes.

The difference is that the domain model builds the objects for the real-world scenarios and solutions, and the behavior of the objects; while the database model builds the data itself and its storage structure, that is, the storage structure of the data.

Keywords: Database MySQL JSON

Added by pfdesigns on Sat, 28 Mar 2020 18:55:20 +0200