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;
简单叙说 数据库逻辑模型 与 领域模型 的异同
相同点:都描述了实体的属性与实体之间的关系
不同点:数据库能表现出实体间属性的相互关系以及需要包括确定实体的主键
领域模型能表达的内容更多,可以包括没有属性的类