数据库大作业 物流管理系统附代码

foreign key ydno reference yundan

3.7定义参照完整性约束 huowu foreign key ydno reference yundan on update cascade on delete cascade zhuanyun foreign key ckno reference cangku on update cascade on delete no action foreign key ygno reference yuangong on update cascade on delete no action peisong foreign key shrno reference shouhuoren on update cascade on delete no action foreign key ygno reference yuangong on update cascade on delete no action pingjia foreign key ydno reference yundan on update cascade on delete cascade

3.8其他业务规则

当运单状态为”接受”时，客户可以取消订单；当运单状态为”配送”时，订单生效不可

取消，当状态为”完成”时，客户才可以进行评价。

仓库存储的货物总量不能超过其容量；若超过，则运单自动取消。

4.物理设计

4.1转换全局逻辑数据模型

4.1.1创建基本表

（给出创建每个基本表的SQL语句，应有如下实现：表名、列名、主键/侯选键、外键、参照完整性约束，对每个列，应有数据类型和长度、默认、是否可空等信息） (1)创建客户表 create table kehu(

khno varchar(20)not null primary key, khname varchar(20) not null, khtel varchar(20) not null, khsex varchar(10), khadd varchar(20) not null, check( khsex in('男','女')) )

数据插入

insert into kehu values(10001,'赵一',8800881,'男','上海号') insert into kehu values(10002,'钱二',8800882,'男','广州号') insert into kehu values(10003,'孙三',8800883,'女','北京号') insert into kehu values(10004,'李四',8800884,'男','厦门号') insert into kehu values(10005,'周五',8800885,'男','武汉号')

insert into kehu values(10006,'吴六',8800886,'男','北京号') insert into kehu values(10007,'郑七',8800881,'女','上海号') insert into kehu values(10008,'王八',8800881,'男','厦门号') insert into kehu values(10009,'冯九',8800881,'男','武汉号') insert into kehu values(10010,'陈十',8800881,'男','武汉号')

(2)创建员工表

create table yuangong(

ygno varchar(20) not null primary key, ygname varchar(20) not null, ygpos varchar(20) not null, ygsex varchar(20) not null, ygage int not null, ygtel varchar(20) not null, check(ygsex in ('男','女')),

check(ygage>18 and ygage<65),

check(ygpos in ('经理','主管','快递员')),

)

数据插入

insert into yuangong values(20001,'褚一','经理','男',50,88228801) insert into yuangong values(20002,'卫二','主管','男',48,88228802) insert into yuangong values(20003,'蒋三','主管','男',46,88228803) insert into yuangong values(20004,'沈四','主管','女',39,88228804) insert into yuangong values(20005,'韩五','快递员','男',30,88228805) insert into yuangong values(20006,'杨六','快递员','男',32,88228806) insert into yuangong values(20007,'朱七','快递员','男',31,88228807) insert into yuangong values(20008,'秦八','快递员','男',34,88228808) insert into yuangong values(20009,'尤九','快递员','男',30,88228809) insert into yuangong values(20010,'许十','快递员','男',29,88228810)

(3)创建收货人表

create table shouhuoren(

shrno varchar(20) not null primary key, shrname varchar(20) not null, shrsex varchar(10), shradd varchar(20) not null, shrtel varchar(20) not null, check(shrsex in ('男','女')), )

数据插入

insert into shouhuoren values(30001,'何一','男','上海号',88330001) insert into shouhuoren values(30002,'何二','男','上海号',88330002)

insert into shouhuoren values(30003,'何三','男','北京号',88330003) insert into shouhuoren values(30004,'何四','男','北京号',88330004) insert into shouhuoren values(30005,'何五','女','广州号',88330005) insert into shouhuoren values(30006,'何六','男','广州号',88330006) insert into shouhuoren values(30007,'何七','男','武汉号',88330007) insert into shouhuoren values(30008,'何八','女','武汉号',88330008) insert into shouhuoren values(30009,'何九','男','厦门号',88330009) insert into shouhuoren values(30010,'何十','男','厦门号',88330010)

(4)创建仓库表

create table cangku(

ckno varchar(20) not null primary key, ckadd varchar(20) not null, ckarea varchar(20) not null,

cktel varchar(20) not null, ckcap int not null ,

ckweight int not null default 0,

check(ckarea in ('上海','北京','广州','武汉','厦门')), )

数据插入

insert into cangku values(1,'上海号','上海',88118800,10000,0) insert into cangku values(2,'北京号','北京',88228800,10000,0) insert into cangku values(3,'广州号','广州',88338800,10000,0) insert into cangku values(4,'武汉号','武汉',88448800,10000,0) insert into cangku values(5,'厦门号','厦门',88558800,10000,0)

(5)创建运单表

create table yundan(

ydno varchar(20) not null primary key, ydprice varchar(10) not null,

ydstate varchar(20) not null default ‘接受’, shrno varchar(20) not null, khno varchar(20) not null, sltime varchar(20) not null,

hwweight int not null,

check(ydstate in ('接受','派送','完成','取消')),

foreign key (shrno) references shouhuoren(shrno) on update cascade, )

运单数据插入

insert into yundan values(50001,10000,'接受',30001,10001,2014-05-08,100) insert into yundan values(50002,10000,'接受',30002,10002,2014-05-08,100) insert into yundan values(50003,10000,'接受',30003,10003,2014-05-09,100) insert into yundan values(50004,10000,'接受',30004,10004,2014-05-09,100) insert into yundan values(50005,10000,'接受',30005,10005,2014-05-10,100) insert into yundan values(50006,20000,'接受',30006,10006,2014-05-10,200) insert into yundan values(50007,20000,'接受',30007,10007,2014-05-11,200) insert into yundan values(50008,20000,'接受',30008,10008,2014-05-11,200) insert into yundan values(50009,20000,'接受',30009,10009,2014-05-12,200) insert into yundan values(50010,20000,'接受',30010,10010,2014-05-12,200)

(6)创建转运表

create table zhuanyun(

zyno varchar(20) not null primary key, ydno varchar(20) not null, ckno varchar(20) not null, ygno varchar(20) not null,

qstime varchar(20), rktime varchar(20),

foreign key (ckno) references cangku(ckno), foreign key (ygno) references yuangong(ygno),

foreign key (ydno) references yundan(ydno) on update cascade )

转运数据输入

insert into zhuanyun values(60001,50001,1,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60002,50002,2,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60003,50003,3,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60004,50004,4,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60005,50005,5,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60006,50006,1,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60007,50007,1,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60008,50008,1,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60009,50009,1,20001,2014-05-09,2014-5-10) insert into zhuanyun values(60010,50010,1,20001,2014-05-09,2014-5-10)

(7)创建配送表 create table peisong(