商品定购系统的案例及习题
1．按照下列要求设计售货系统的数据库
设有商业销售系统数据库。一个顾客（顾客编号，姓名，性别，单位，电话号码）可以定购多种商品，一种商品（商品编号，名称，型号，厂商，单价）可以提供给多个顾客。顾客定购商品时需要确定定购商品的数量，并记录定购日期与取货日期。 (1) 试画出该系统的实体-联系模型E-R图。 顾客编号姓名 性别 单位 电话 定购日期 取货日期 厂商
(2) 给出相应的关系数据模型
顾客(编号, 姓名, 性别, 单位, 电话) 商品(编号, 名称, 型号, 单价, 厂商)
定购(顾客编号, 商品编号, 定购数量, 定购日期, 取货日期)
顾客 m n 商品 数量 编号 名称 型号 单价 定购 2．已知有顾客定购商品信息的三张表：顾客表Customer、定购表Order、商品表Commodity。按要求创建三张表
(1) 表名：Customer
属性：ID 字符型 最大10个字符 ——顾客编号
NAME 字符型 最大16个字符 ——顾客姓名 SEX 字符型 最大2个字符 ——性别
MOBILE 字符型 最大11个字符 ——移动电话 ADDRESS 字符型 最大50个字符 ——家庭住址
约束： ID——主码； NAME——非空属性； SEX——取值“男”或“女”；
MOBILE——唯一性； ADDRESS——默认为UNKOWN；
Create table Customer (
ID char(10) primary key, Name not null,
Sex char(2) check(sex=’男’ or sex=’女’), Mobile char(11) unique,
Address default ‘UNKOWN’
1
)
(2) 表名：OrderBook
属性：CSID 字符型 最大10个字符 ——顾客编号 CMID 字符型 最大12个字符 ——商品编号 COUNT 整型 ——定购数量 BOOKDATE 日期型 ——订货日期 TAKEDATE 日期型 ——交货日期
约束：CSID，CMID——主码； 定购数量要大于0； 订货日期要小于交货日期； CSID——外码，引用Customer表的ID； CMID——外码，引用Commodity表的ID； Create Table OrderBook (
CSID char(10) foreign key (CSID) references Customer(ID), CMID char(10) foreign key (CMID) references Commodity(ID), Count int check(count>0), BookDate datetime, TakeDate datetime,
Check(Takedate>bookdate), Primary key(CSID, CMID) )
(3) 表名：Commodity
属性：ID 字符型 最大12个字符 ——商品编号
NAME 字符型 最大20个字符 ——商品名称
MANUFACTURE 字符型 最大20个字符 ——生产厂商
PRICE 小数型 最大不超过4位数，保留2位小数 ——商品单价 约束：ID——主码； NAME——非空； Create Table Commodity (
ID char(12) primary key, Name varchar(20) not null, MANUFACTUR varchar(20), Price decimal(6,2) )
3. 针对上面的三个基本表做如下练习：
(1) 往基本表Customer中插入顾客元组（”0421F901”，”WU”，”女”，13980011001） insert into Customer(id,name,sex, Mobile) values(‘0421F901’, ‘WU’, ‘女’, ‘13980011001’) (2) 往基本表Commodity中插入一条商品记录（“03110408591”，“牙膏”，“保洁公司”，
5.00）
isnert into Commodity(id, name, manufacture, price) values(‘03110408591’, ‘牙膏’, ‘保洁公司’, 5)
(3) 修改“WANGYAN”顾客定购商品的记录交货日期为2005-12-25。
Update order set taketime=’2005-12-25’ where
2
csid in (select id from customer where name=’wangyan’) (4) 把“雀巢奶粉”的定购商品记录全部删去。
Delete from order where cmid in (select id from commodity where name=’雀巢奶粉’) (5) 查询“ANAN”顾客的手机号和住址。
Select mobile, address from customer where name=’anan’ (6) 查询商品的平均价格高于75元钱的厂商名称。
Select manufacture from commodity group by manufacture having avg(price) >75 (7) 查询顾客的定购信息，并按订货日期升序排列，若订货日期相同，则按定购数量降序排
列。
Select * from orderbook order by bookdate,count desc (8) 查询定购数量超过100的顾客姓名、电话和住址。
Select name, mobile, address from customer where id in(select csid from orderbook where count>100)
(9) 查询没有订单的商品编号、名称和厂商。
Select id,name, manufacture from comodity where id not in(select csid from orderbook)
(10) 查询定购了商品号为“M900532481”的顾客总人数和最高数量。 Select count(*) ,max(count) from order where cmid=’m900532481’ (11) 查询定购了“可比克”商品的顾客编号、姓名。
Select id,name from customer where id in(select csid from order where cmid in(select id from comodity where name=’ 可比克’))
(12) 查询商品名中包含“糖”的所有商品的编号、厂商、单价。
Select id,manufacture ,price from comodity where name like ‘%糖%’ (13) 查询至少有两位顾客定购的商品的名称。
Select name from comodity where id in(select cmid from order group by cmid having count(*)>=2)
(14) 查询截至2005年底所有商品订单最晚的交货日期。
SELECT taketime from order where taketime = (select max(taketime) from order where taketime<’2005-12-31’)
(15) 查询住址中含有“软件园”三个字的顾客人数。
Select count(*) from customer where address like’%软件园%’
(16) 建立定购商品号为“M900532481”的顾客的编号、住址、订货数量、交货日期的视图
View001。
Create view view001 (编号,住址,订货数量,交货日期) As
Select csid,address,count,taketime from orderbook, Customer
where Customer .id= orderbook .csid AND cmid=’m900532481’ (17) 建立视图（View002），包含每位顾客的编号、订单数量、平均订购数量、最早交货日
期和最晚交货日期。
Create view view002(顾客编号,订单数量,平均订购数量,最早交货日期,最晚交货日期) As
Select csid, count(*), avg(count), min(taketime), max(taketime) from order group by csid
(18) 利用上述视图进行查询：列出平均数量大于80的顾客编号及其订单数量。
3
Select顾客编号,订单数量 from view002 where平均订购数量>80
4. 针对上面的三个基本表创建如下触发器：
（1）为Customer创建一触发器Ctr,该触发器的功能是：保护该表中的数据不被修改和删除
（2）为Commodity r创建一触发器Mtr, 该触发器的功能是：如果该表中的商品编号发生改变，则级联修改定购表OrderBook中的商品编号
4