国开《数据库基础与应用》第五章 5-2-3：逻辑设计

5-2-3：逻辑设计

根据上一步概念设计得到的4个基本表：即客房表、住宿表、消费卡表和旅客登记表，以及5个视图表：即客房空闲表、住宿费表、伙食费表、娱乐费表和催补款表，以及它们之间的内在联系。

create database 旅店管理 /*建立旅店管理数据库*/

use 旅店管理 /*使旅店管理数据库成为当前库*/

（1）向旅店管理数据库中插入基本表

1）客房表

create table 客房表

（

　　房序号 int primary key,

　　楼号 int not null,

　　楼层号 int not null,

　　房间号 int not null,

　　床位号 int default 1,

　　房间电话 char(5),

　　价格 int not null,

　　已用 int default 0, check(已用=0 or 已用=1)

）

2）消费卡表

create table 消费卡表

（

　　卡号 char(6) primary key,

　　资金总额 float default 0 check(资金总额>=0),

　　住宿费 float default 0 check( 住宿费>=0),

　　伙食费 float default 0 check(伙食费>=0),

　　娱乐费 float default 0 check(娱乐费>=0),

　　余额 as 资金总额-住宿费-伙食费-娱乐费 /*余额为计算字段*/

3）旅客登记表

create table 旅客登记表

（

　　姓名 char(8) not null,

　　性别 char(2) not null check(性别='男' or 性别='女'),

　　来源地 char(10),

　　单位 char(10),

　　身份证号 char(18) not null,

　　登记日期 datetime not null,

　　联系电话 char(13),

　　消费卡号 char(6) not null,

　　foreign key(消费卡号) references 消费卡表(卡号)

）

4）住宿表

create table 住宿表

(

　　卡号 char(6) not null,

　　房序号 int not null unique,

　　foreign key(房序号) references 客房表(房序号),

　　foreign key(卡号) references 消费卡表(卡号)

)

（2）视图

1）客房空闲表

它通过客房表产生出来，是客房表中已用字段为0的所有空床位的投影。

在视图定义语句中，若视图名后省略列名表，则该视图默认包含着其查询子句中投影出的所有列。在此处定义的客房空闲表就包含有客房表中的所有列的定义和“已用”列值为0的所有行的内容。

create view 客房空闲表

　as select *

　　from 客房表

　　where 客房表.已用=0

2）住宿费表

它通过消费卡表产生出来，是消费卡表中在卡号、住宿费和余额这3个字段的投影。

create view 住宿费表

　as select 卡号,住宿费,余额

　　from 消费卡表