系统分析与设计HW5

a. 阅读 Asg_RH 文档，按用例构建领域模型。

b.数据库建模(E-R 模型)
1.按 Task 3 要求，给出系统的 E-R 模型（数据逻辑模型）

2.导出 Mysql 物理数据库的脚本

/*==============================================================*/
/* DBMS name:      Sybase SQL Anywhere 12                       */
/* Created on:     2018/4/29 0:37:12                            */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_HOTEL_REFERENCE_LOCATION') then
    alter table Hotel
       delete foreign key FK_HOTEL_REFERENCE_LOCATION
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_RESERVAT_REFERENCE_CUSTOMER') then
    alter table Reservation
       delete foreign key FK_RESERVAT_REFERENCE_CUSTOMER
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_RESERVAT_REFERENCE_HOTEL') then
    alter table Reservation
       delete foreign key FK_RESERVAT_REFERENCE_HOTEL
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_RESERVAT_REFERENCE_ROOM') then
    alter table Reservation
       delete foreign key FK_RESERVAT_REFERENCE_ROOM
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_ROOM_REFERENCE_HOTEL') then
    alter table Room
       delete foreign key FK_ROOM_REFERENCE_HOTEL
end if;

drop table if exists Customer;

drop table if exists Hotel;

drop table if exists Location;

drop table if exists Reservation;

drop table if exists Room;

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer 
(
   name                 long varchar                   not null,
   email                long varchar                   null,
   isSmoking            char                           null,
   Customer_ID          int                            not null,
   constraint PK_CUSTOMER primary key clustered (Customer_ID)
);

/*==============================================================*/
/* Table: Hotel                                                 */
/*==============================================================*/
create table Hotel 
(
   name                 long varchar                   not null,
   star                 int                            null,
   address              long varchar                   null,
   hotel_ID             int                            not null,
   "city code"          int                            null,
   constraint PK_HOTEL primary key clustered (hotel_ID)
);

/*==============================================================*/
/* Table: Location                                              */
/*==============================================================*/
create table Location 
(
   code                 int                            not null,
   name                 char                           null,
   hot                  int                            null,
   constraint PK_LOCATION primary key clustered (code)
);

/*==============================================================*/
/* Table: Reservation                                           */
/*==============================================================*/
create table Reservation 
(
   reservation_ID       int                            not null,
   Customer_ID          int                            null,
   hotel_ID             int                            null,
   RoomID               int                            null,
   "in date"            date                           null,
   "out date"           date                           null,
   constraint PK_RESERVATION primary key clustered (reservation_ID)
);

/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/
create table Room 
(
   RoomID               int                            not null,
   hotel_ID             int                            null,
   price                double                         null,
   availability         char                           null,
   type                 long varchar                   null,
   constraint PK_ROOM primary key clustered (RoomID)
);

alter table Hotel
   add constraint FK_HOTEL_REFERENCE_LOCATION foreign key ("city code")
      references Location (code)
      on update restrict
      on delete restrict;

alter table Reservation
   add constraint FK_RESERVAT_REFERENCE_CUSTOMER foreign key (Customer_ID)
      references Customer (Customer_ID)
      on update restrict
      on delete restrict;

alter table Reservation
   add constraint FK_RESERVAT_REFERENCE_HOTEL foreign key (hotel_ID)
      references Hotel (hotel_ID)
      on update restrict
      on delete restrict;

alter table Reservation
   add constraint FK_RESERVAT_REFERENCE_ROOM foreign key (RoomID)
      references Room (RoomID)
      on update restrict
      on delete restrict;

alter table Room
   add constraint FK_ROOM_REFERENCE_HOTEL foreign key (hotel_ID)
      references Hotel (hotel_ID)
      on update restrict
      on delete restrict;

3.简单叙说 数据库逻辑模型 与 领域模型 的异同
领域模型阐述领域中的概念类或词汇，通俗的说，概念类是思想，事物或对象。领域模型不是数据模型（通过对数据模型的定义来表示存储于某处的持久性数据），所以在领域模型内，并不会排除需求中没有明确记录其相关信息的类（这是对关系数据库进行数据建模的常见标准，但与领域建模无关），也不会排除没有属性的概念类，例如，没有属性的概念类是合法的，或者在领域内充当纯行为角色而不是信息角色的概念类也是有效的。