（补交）系统分析与设计-HW5

1.领域建模

a.

b.
c.

-- MySQL Script generated by MySQL Workbench
-- Sun Jul  1 03:35:22 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`.`hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hotel` (
  `idhotel` INT NOT NULL,
  `hotelname` VARCHAR(45) NULL,
  `location` VARCHAR(45) NULL,
  `level` VARCHAR(45) NULL,
  PRIMARY KEY (`idhotel`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`rooms`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`rooms` (
  `idrooms` INT NOT NULL,
  `roomnumber` VARCHAR(45) NULL,
  `roomtype` VARCHAR(45) NULL,
  `price` VARCHAR(45) NULL,
  `emptyornot` VARCHAR(45) NULL,
  PRIMARY KEY (`idrooms`),
  CONSTRAINT `idrooms`
    FOREIGN KEY ()
    REFERENCES `mydb`.`hotel` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reservation` (
  `idreservation` INT NOT NULL,
  `hotelname` VARCHAR(45) NULL,
  `roomnumber` VARCHAR(45) NULL,
  `datein` VARCHAR(45) NULL,
  `dateout` VARCHAR(45) NULL,
  PRIMARY KEY (`idreservation`),
  CONSTRAINT `hotelname`
    FOREIGN KEY ()
    REFERENCES `mydb`.`hotel` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `roomnumber`
    FOREIGN KEY ()
    REFERENCES `mydb`.`rooms` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`creditcard`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`creditcard` (
  `idcreditcard` INT NOT NULL,
  `password` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idcreditcard`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
  `idpayment` INT NOT NULL,
  `total` VARCHAR(45) NULL,
  `typeofpayment` VARCHAR(45) NULL,
  `details` VARCHAR(45) NULL,
  PRIMARY KEY (`idpayment`),
  CONSTRAINT `typeofpayment`
    FOREIGN KEY ()
    REFERENCES `mydb`.`creditcard` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `idpayment`
    FOREIGN KEY ()
    REFERENCES `mydb`.`reservation` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `customerid` VARCHAR(45) NOT NULL,
  `username` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `phonenumber` VARCHAR(45) NULL,
  PRIMARY KEY (`customerid`),
  CONSTRAINT `customerid`
    FOREIGN KEY ()
    REFERENCES `mydb`.`reservation` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `customerid`
    FOREIGN KEY ()
    REFERENCES `mydb`.`payment` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT);

-- -----------------------------------------------------
-- View `mydb`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`view1`;
USE `mydb`;


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