（补交）系统分析与设计

1.领域建模

a.

b.
c.

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';

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

USE `mydb` ;

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;

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;

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;

CREATE TABLE IF NOT EXISTS `mydb`.`creditcard` (

`idcreditcard` INT NOT NULL,

`password` VARCHAR(45) NULL,

`type` VARCHAR(45) NULL,

PRIMARY KEY (`idcreditcard`))

ENGINE = InnoDB;

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;

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` ;

CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT);

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;