系统分析与设计HW4

1、领域建模

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

        如下图

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

    如下图

导出的脚本内容：

/*==============================================================*/
/* DBMS name:      Sybase SQL Anywhere 12                       */
/* Created on:     2018/4/29 22:08:54                           */
/*==============================================================*/


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

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

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

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

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

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

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

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

drop index if exists customer.customer_PK;

drop table if exists customer;

drop index if exists hotel.hotel_PK;

drop table if exists hotel;

drop index if exists include1.include2_FK;

drop index if exists include1.include1_FK;

drop index if exists include1.include1_PK;

drop table if exists include1;

drop index if exists payment.complete_FK;

drop index if exists payment.pay_FK;

drop index if exists payment.payment_PK;

drop table if exists payment;

drop index if exists reservation.make_FK;

drop index if exists reservation.complete2_FK;

drop index if exists reservation.reservation_PK;

drop table if exists reservation;

drop index if exists room.has_FK;

drop index if exists room.include3_FK;

drop index if exists room.room_PK;

drop table if exists room;

/*==============================================================*/
/* Table: customer                                              */
/*==============================================================*/
create table customer 
(
   customer_id          integer                        not null,
   customer_name        long varchar                   null,
   "email address"      long varchar                   null,
   constraint PK_CUSTOMER primary key (customer_id)
);

/*==============================================================*/
/* Index: customer_PK                                           */
/*==============================================================*/
create unique index customer_PK on customer (
customer_id ASC
);

/*==============================================================*/
/* Table: hotel                                                 */
/*==============================================================*/
create table hotel 
(
   hotel_id             integer                        not null,
   hotel_name           long varchar                   null,
   location             long varchar                   null,
   "lowest price"       float                          null,
   "star rating"        float                          null,
   constraint PK_HOTEL primary key (hotel_id)
);

/*==============================================================*/
/* Index: hotel_PK                                              */
/*==============================================================*/
create unique index hotel_PK on hotel (
hotel_id ASC
);

/*==============================================================*/
/* Table: include1                                              */
/*==============================================================*/
create table include1 
(
   reservation_id       integer                        not null,
   hotel_id             integer                        not null,
   constraint PK_INCLUDE1 primary key clustered (reservation_id, hotel_id)
);

/*==============================================================*/
/* Index: include1_PK                                           */
/*==============================================================*/
create unique clustered index include1_PK on include1 (
reservation_id ASC,
hotel_id ASC
);

/*==============================================================*/
/* Index: include1_FK                                           */
/*==============================================================*/
create index include1_FK on include1 (
reservation_id ASC
);

/*==============================================================*/
/* Index: include2_FK                                           */
/*==============================================================*/
create index include2_FK on include1 (
hotel_id ASC
);

/*==============================================================*/
/* Table: payment                                               */
/*==============================================================*/
create table payment 
(
   payment_id           integer                        not null,
   customer_id          integer                        not null,
   reservation_id       integer                        not null,
   cost                 integer                        null,
   way                  long varchar                   null,
   constraint PK_PAYMENT primary key (payment_id)
);

/*==============================================================*/
/* Index: payment_PK                                            */
/*==============================================================*/
create unique index payment_PK on payment (
payment_id ASC
);

/*==============================================================*/
/* Index: pay_FK                                                */
/*==============================================================*/
create index pay_FK on payment (
customer_id ASC
);

/*==============================================================*/
/* Index: complete_FK                                           */
/*==============================================================*/
create index complete_FK on payment (
reservation_id ASC
);

/*==============================================================*/
/* Table: reservation                                           */
/*==============================================================*/
create table reservation 
(
   reservation_id       integer                        not null,
   payment_id           integer                        not null,
   customer_id          integer                        not null,
   hotel_name           long varchar                   null,
   check_in_date        date                           null,
   check_out_date       date                           null,
   roomtype             long varchar                   null,
   constraint PK_RESERVATION primary key (reservation_id)
);

/*==============================================================*/
/* Index: reservation_PK                                        */
/*==============================================================*/
create unique index reservation_PK on reservation (
reservation_id ASC
);

/*==============================================================*/
/* Index: complete2_FK                                          */
/*==============================================================*/
create index complete2_FK on reservation (
payment_id ASC
);

/*==============================================================*/
/* Index: make_FK                                               */
/*==============================================================*/
create index make_FK on reservation (
customer_id ASC
);

/*==============================================================*/
/* Table: room                                                  */
/*==============================================================*/
create table room 
(
   room_id              integer                        not null,
   hotel_id             integer                        not null,
   reservation_id       integer                        not null,
   price                float                          null,
   roomtype             long varchar                   null,
   num                  long varchar                   null,
   constraint PK_ROOM primary key (room_id)
);

/*==============================================================*/
/* Index: room_PK                                               */
/*==============================================================*/
create unique index room_PK on room (
room_id ASC
);

/*==============================================================*/
/* Index: include3_FK                                           */
/*==============================================================*/
create index include3_FK on room (
reservation_id ASC
);

/*==============================================================*/
/* Index: has_FK                                                */
/*==============================================================*/
create index has_FK on room (
hotel_id ASC
);

alter table include1
   add constraint FK_INCLUDE1_INCLUDE1_RESERVAT foreign key (reservation_id)
      references reservation (reservation_id)
      on update restrict
      on delete restrict;

alter table include1
   add constraint FK_INCLUDE1_INCLUDE2_HOTEL foreign key (hotel_id)
      references hotel (hotel_id)
      on update restrict
      on delete restrict;

alter table payment
   add constraint FK_PAYMENT_COMPLETE_RESERVAT foreign key (reservation_id)
      references reservation (reservation_id)
      on update restrict
      on delete restrict;

alter table payment
   add constraint FK_PAYMENT_PAY_CUSTOMER foreign key (customer_id)
      references customer (customer_id)
      on update restrict
      on delete restrict;

alter table reservation
   add constraint FK_RESERVAT_COMPLETE2_PAYMENT foreign key (payment_id)
      references payment (payment_id)
      on update restrict
      on delete restrict;

alter table reservation
   add constraint FK_RESERVAT_MAKE_CUSTOMER foreign key (customer_id)
      references customer (customer_id)
      on update restrict
      on delete restrict;

alter table room
   add constraint FK_ROOM_HAS_HOTEL foreign key (hotel_id)
      references hotel (hotel_id)
      on update restrict
      on delete restrict;

alter table room
   add constraint FK_ROOM_INCLUDE3_RESERVAT foreign key (reservation_id)
      references reservation (reservation_id)
      on update restrict
      on delete restrict;

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

    相同点：都描述了实体的属性与实体之间的关系

    不同点：数据库能表现出实体间属性的相互关系以及需要包括确定实体的主键

                 领域模型能表达的内容更多，可以包括没有属性的类