系统分析与设计

homework4

Posted by Richbabe on April 28, 2018

1.领域建模

  • a.阅读 Asg_RH 文档,按用例构建领域模型。
    • 按 Task2 要求,请使用工具 UMLet,截图格式务必是 png 并控制尺寸
    • 说明:请不要受 PCMEF 层次结构影响。你需要识别实体(E)和 中介实体(M,也称状态实体)
      • 在单页面应用(如 vue)中,E 一般与数据库构建有关, M 一般与 store 模式 有关
      • 在 java web 应用中,E 一般与数据库构建有关, M 一般与 session 有关

image

  • b.数据库建模(E-R 模型)

(1)按 Task 3 要求,给出系统的 E-R 模型(数据逻辑模型)

  • E-R图 image
  • 物理模型 image

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

drop table if exists Card_holder;

drop table if exists Consumer;

drop table if exists Credit_card;

drop table if exists Hotel;

drop table if exists Payment;

drop table if exists Reservation;

drop table if exists Room;

/*==============================================================*/
/* Table: Card_holder                                           */
/*==============================================================*/
create table Card_holder
(
   Card_holder_name     char(10) not null,
   Address              varchar(30),
   Post_code            char(10),
   Daytime_telephone    char(20),
   Evening_telephone    char(20),
   primary key (Card_holder_name)
);

/*==============================================================*/
/* Table: Consumer                                              */
/*==============================================================*/
create table Consumer
(
   Payment_id           int not null,
   Consumer_id          int not null,
   First_name           char(10),
   Last_name            char(20),
   Sex                  char(10),
   Email_address        char(20),
   Is_smoking           smallint,
   primary key (Payment_id, Consumer_id)
);

/*==============================================================*/
/* Table: Credit_card                                           */
/*==============================================================*/
create table Credit_card
(
   Payment_id           int not null,
   Credit_card_id       int not null,
   Card_holder_name     char(10),
   Card_type            char(20),
   Card_security_code   char(20),
   Expiry_date          char(10),
   primary key (Payment_id, Credit_card_id)
);

/*==============================================================*/
/* Table: Hotel                                                 */
/*==============================================================*/
create table Hotel
(
   Hotel_name           char(20) not null,
   Hotel_address        varchar(30),
   Hotel_star           int,
   primary key (Hotel_name)
);

/*==============================================================*/
/* Table: Payment                                               */
/*==============================================================*/
create table Payment
(
   Hotel_name           char(20) not null,
   Reservation_id       int not null,
   Payment_id           int not null,
   Consumer_id          int,
   Credit_card_id       int not null,
   Total_price          int,
   primary key (Credit_card_id, Hotel_name, Reservation_id, Payment_id)
);

/*==============================================================*/
/* Table: Reservation                                           */
/*==============================================================*/
create table Reservation
(
   Hotel_name           char(20) not null,
   Reservation_id       int not null,
   Payment_id           int not null,
   Check_in_date        char(10),
   Check_out_date       char(10),
   Total_room_number    int,
   Special_requirement  varchar(100),
   primary key (Hotel_name, Payment_id, Reservation_id)
);

/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/
create table Room
(
   Hotel_name           char(20) not null,
   Room_id              char(5) not null,
   Res_Hotel_name       char(20),
   Reservation_id       int,
   Room_type            char(10),
   Available_room_number int,
   primary key (Hotel_name, Room_id)
);

alter table Consumer add constraint FK_r8 foreign key (, , , Payment_id)
      references Payment (Credit_card_id, Hotel_name, Reservation_id, Payment_id) on delete restrict on update restrict;

alter table Credit_card add constraint FK_r1 foreign key (Card_holder_name)
      references Card_holder (Card_holder_name) on delete restrict on update restrict;

alter table Credit_card add constraint FK_r10 foreign key (, , , Payment_id)
      references Payment (Credit_card_id, Hotel_name, Reservation_id, Payment_id) on delete restrict on update restrict;

alter table Payment add constraint FK_r6 foreign key (Hotel_name, , Reservation_id)
      references Reservation (Hotel_name, Payment_id, Reservation_id) on delete restrict on update restrict;

alter table Payment add constraint FK_r7 foreign key (, Consumer_id)
      references Consumer (Payment_id, Consumer_id) on delete restrict on update restrict;

alter table Payment add constraint FK_r9 foreign key (, Credit_card_id)
      references Credit_card (Payment_id, Credit_card_id) on delete restrict on update restrict;

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

alter table Reservation add constraint FK_r5 foreign key (, , , Payment_id)
      references Payment (Credit_card_id, Hotel_name, Reservation_id, Payment_id) on delete restrict on update restrict;

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

alter table Room add constraint FK_r4 foreign key (Res_Hotel_name, , Reservation_id)
      references Reservation (Hotel_name, Payment_id, Reservation_id) on delete restrict on update restrict;

(3) 简单 叙说数据库逻辑模型 与 领域模型 的异同

领域模型是一个商业建模范畴的概念,他和软件开发并无一丝一毫的关系,即使一个企业他不开发软件,他也具备他的业务模型,所有的同行业的企业他们的业务模型必定有非常大的共性和内在的规律性,由这个行业内的各个企业的业务模型再向上抽象出来整个行业的业务模型,这个东西即“领域模型”。在领域模型中最重要的对象是实体和关系。

数据模型是系统设计,以及实现的一部分,描述的是对用户需求在技术上的实现方法。用户不需要关心系统的数据模型,但是必须关注领域模型,因为领域模型反映的是问题域的相关业务概念以及其关系,领域模型是用户业务描述的高度抽象,来源于业务需求的描述,同时又可以帮助用户和需求分析人员更好的理解业务需求。数据库逻辑模型是一个抽象的宏观业务模型。

  • 相同点:都描述了实体间的关系,每个实体有对应的属性。
  • 不同点:数据库逻辑模型更加具体,其具有属性的类型、主键、外键等约束信息,在局部方面表达能力更强。而领域模型则关注了中间实体的信息,在全部方面表达能力更强。