系统分析与设计第五次作业

• a. 阅读 Asg_RH 文档，按用例构建领域模型。
  
  • 按 Task2 要求，请使用工具 UMLet，截图格式务必是 png 并控制尺寸
    
• b. 数据库建模(E-R 模型)
  
  • 按 Task 3 要求，给出系统的 E-R 模型（数据逻辑模型）
    
  • 导出 Mysql 物理数据库的脚本

drop table if exists Customer;

drop table if exists hotel;

drop table if exists location;

drop table if exists request;

drop table if exists reservation;

drop table if exists room;

drop table if exists "room-desc";

drop table if exists "shopping-busket";

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer 
(
   name                 text                           null,
   "phone number"       text                           null,
   email                text                           null,
   id                   integer                        not null,
   constraint PK_CUSTOMER primary key clustered (id)
);

/*==============================================================*/
/* Table: hotel                                                 */
/*==============================================================*/
create table hotel 
(
   name                 text                           not null,
   star                 binary                         null,
   "hot index"          integer                        null,
   address              text                           null,
   constraint PK_HOTEL primary key clustered (name)
);

/*==============================================================*/
/* Table: location                                              */
/*==============================================================*/
create table location 
(
   id                   integer                        null,
   region               text                           null,
   city                 text                           null,
   town                 text                           null
);

/*==============================================================*/
/* Table: request                                               */
/*==============================================================*/
create table request 
(
   id                   integer                        not null,
   "customer id"        integer                        null,
   destination          text                           null,
   "check out date"     date                           null,
   "number of night"    integer                        null,
   constraint PK_REQUEST primary key clustered (id)
);

/*==============================================================*/
/* Table: reservation                                           */
/*==============================================================*/
create table reservation 
(
   id                   integer                        not null,
   "hotel name"         text                           null,
   city                 text                           null,
   "check in date"      date                           null,
   "check out date"     date                           null,
   "room type"          text                           null,
   "number of rooms"    integer                        null,
   constraint PK_RESERVATION primary key clustered (id)
);

/*==============================================================*/
/* Table: room                                                  */
/*==============================================================*/
create table room 
(
   id                   integer                        not null,
   num                  integer                        null,
   data                 integer                        null,
   constraint PK_ROOM primary key clustered (id)
);

/*==============================================================*/
/* Table: "room-desc"                                           */
/*==============================================================*/
create table "room-desc" 
(
   type                 text                           not null,
   "day price"          float                          null,
   "room id"            integer                        null,
   constraint "PK_ROOM-DESC" primary key clustered (type)
);

/*==============================================================*/
/* Table: "shopping-busket"                                     */
/*==============================================================*/
create table "shopping-busket" 
(
   id                   integer                        not null,
   "customer id"        integer                        null,
   "reservation id"     integer                        null,
   constraint "PK_SHOPPING-BUSKET" primary key clustered (id)
);

alter table request
   add constraint FK_REQUEST_REFERENCE_CUSTOMER foreign key ("customer id")
      references Customer (id)
      on update restrict
      on delete restrict;

alter table reservation
   add constraint FK_RESERVAT_REFERENCE_HOTEL foreign key ("hotel name")
      references hotel (name)
      on update restrict
      on delete restrict;

alter table reservation
   add constraint "FK_RESERVAT_REFERENCE_ROOM-DES" foreign key ("room type")
      references "room-desc" (type)
      on update restrict
      on delete restrict;

alter table "room-desc"
   add constraint "FK_ROOM-DES_REFERENCE_ROOM" foreign key ("room id")
      references room (id)
      on update restrict
      on delete restrict;

alter table "shopping-busket"
   add constraint FK_SHOPPING_REFERENCE_CUSTOMER foreign key ("customer id")
      references Customer (id)
      on update restrict
      on delete restrict;

alter table "shopping-busket"
   add constraint FK_SHOPPING_REFERENCE_RESERVAT foreign key ("reservation id")
      references reservation (id)
      on update restrict
      on delete restrict;

• 简单叙说 数据库逻辑模型 与 领域模型 的异同
  
  • 相同点：两种模型都是描述实体、实体拥有的属性以及实体之间的关系。
  • 不同点：领域建模更加注重的是业务领域的内容，表达能力更强；而数据库逻辑模型更加具体，描述了属性的类型、表的主键、表之间的约束等问题。