系统分析与设计-homework5(lesson7)

1、 领域建模

• a. 阅读 Asg_RH 文档，按用例构建领域模型。
  
  • 按 Task2 要求，请使用工具 UMLet，截图格式务必是 png 并控制尺寸
  • 说明：请不要受 PCMEF 层次结构影响。你需要识别实体（E）和 中介实体（M，也称状态实体)
    
    • 在单页面应用（如 vue）中，E 一般与数据库构建有关， M 一般与 store 模式 有关
    • 在 java web 应用中，E 一般与数据库构建有关， M 一般与 session 有关
      
• b. 数据库建模(E-R 模型)
  
  • 按 Task 3 要求，给出系统的 E-R 模型（数据逻辑模型）
  • 建模工具 PowerDesigner（简称PD）
    
  • 导出 Mysql 物理数据库的脚本

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2018/4/29 9:23:18                            */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Customer') and o.name = 'FK_CUSTOMER_REFERENCE_HOTEL')
alter table Customer
   drop constraint FK_CUSTOMER_REFERENCE_HOTEL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('creditCard') and o.name = 'FK_CREDITCA_REFERENCE_CUSTOMER')
alter table creditCard
   drop constraint FK_CREDITCA_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('room') and o.name = 'FK_ROOM_REFERENCE_HOTEL')
alter table room
   drop constraint FK_ROOM_REFERENCE_HOTEL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('room') and o.name = 'FK_ROOM_REFERENCE_CUSTOMER')
alter table room
   drop constraint FK_ROOM_REFERENCE_CUSTOMER
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Customer')
            and   type = 'U')
   drop table Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('creditCard')
            and   type = 'U')
   drop table creditCard
go

if exists (select 1
            from  sysobjects
           where  id = object_id('hotel')
            and   type = 'U')
   drop table hotel
go

if exists (select 1
            from  sysobjects
           where  id = object_id('room')
            and   type = 'U')
   drop table room
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   account              text                 not null,
   fullName             text                 null,
   emailAddress         text                 not null,
   hotelID              int                  null,
   constraint PK_CUSTOMER primary key (account)
)
go

/*==============================================================*/
/* Table: creditCard                                            */
/*==============================================================*/
create table creditCard (
   cardID               int                  not null,
   account              text                 not null,
   password             text                 not null,
   constraint PK_CREDITCARD primary key (cardID)
)
go

/*==============================================================*/
/* Table: hotel                                                 */
/*==============================================================*/
create table hotel (
   hotelID              int                  not null,
   name                 text                 not null,
   locationID           int                  not null,
   star                 int                  not null,
   constraint PK_HOTEL primary key (hotelID)
)
go

/*==============================================================*/
/* Table: room                                                  */
/*==============================================================*/
create table room (
   roomID               int                  not null,
   hotelID              int                  not null,
   account              text                 null,
   type                 text                 null,
   price                float                not null,
   isAvailable          bit                  not null,
   constraint PK_ROOM primary key (roomID)
)
go

alter table Customer
   add constraint FK_CUSTOMER_REFERENCE_HOTEL foreign key (hotelID)
      references hotel (hotelID)
go

alter table creditCard
   add constraint FK_CREDITCA_REFERENCE_CUSTOMER foreign key (account)
      references Customer (account)
go

alter table room
   add constraint FK_ROOM_REFERENCE_HOTEL foreign key (hotelID)
      references hotel (hotelID)
go

alter table room
   add constraint FK_ROOM_REFERENCE_CUSTOMER foreign key (account)
      references Customer (account)
go

• 简单叙说 数据库逻辑模型 与 领域模型 的异同
  领域模型/概念模型是对领域内的概念类或现实世界中对象的可视化表示。
  数据库逻辑模型是将概念模型转化为具体的数据模型的过程，即按照概念结构设计阶段建立的基本E-R图，按选定的管理系统软件支持的数据模型（层次、网状、关系、面向对象），转换成相应的逻辑模型。
  两者都描述了实体的属性和实体间的关系，数据库逻辑模型还包括了属性的数据类型，主键等信息，而领域模型则包括了中介实体的信息。