1、. 第四章 数据库设计五、数据表设计: E-R模型5.1数据库结构及说明物流管理系统数据表及视图设计：管理员登陆表：（login）列 名 字符类型 长 度 备 注UserID int 4 主键IDLoginName char 20 用户名UserName char 20 用户姓名PassWord text 16 用户密码MemberID int 4 Member表外码privilege int 4 用户权限对privilege中的值进行说明：系统管理员（1）、财务管理员（2）、业务管理员（3）用户资料表：（Member）列 名 字符类型 长 度 备 注MemberID int 4 主键IDMe
2、mberName char 20 用户姓名MemAddress char 50 用户地址MemPhone char 12 用户电话MemMomo text 16 用户备注建立管理员信息用户视图：（login_VIEW）列 名 字符类型 长 度 备 注UserID int 4 主键IDLoginName char 20 用户名UserName char 20 用户姓名privilege int 4 用户权限MemAddress char 50 用户地址MemPhone char 12 用户电话MemberID int 4 MemberIDBusiness 业务明细总表编号 字段名 类型 说明 备
3、注1 Bus_ID int 业务流水号（自动） 主键2 Bus_GoodID int 业务中货物的流水号 外键3 Bus_TakeUnitID int 托运人的流水号编号 外健4 Bus_RecverID int 收货人的流水号 外键5 Bus_FromStation Char(20) 从哪个站发货6 Bus_FromCity Char(20) 从哪个城市发货7 Bus_ToStation Char(20) 发往哪个联网站8 Bus_ToCity Char(20) 发往哪个城市9 Bus_AcceptTime Datetime 业务接洽时间10 Bus_AcceptOpt Char(20) 业
4、务接洽操作员11 Bus_Cast Float 业务所需费用12 Bus_IsPay text 客户是否付款(现金/到付)13 Bus_Fisished text 业务是否完成 默认未处理BusinessLog 业务操作日志表编号 字段名 类型 说明 备住1 Log_ID int 日志ID 主键2 Log_BusID int 业务流水号 外键3 Log_Time Datetime 日志产生时间4 Log_Info text 日志信息5 Log_OptIP text 业务操作IPCommonWrap常用包装表编号 字段名 类型 说明 备注1 WrapID int 包装类型ID （自动产生） 主键
5、2 WrapName Char(20) 包装类名称3 WrapFeq int 包装使用频率Entrust 托运人明细表编号 字段名 类型 说明 备注1 TakeUnit_ID int 托运业主ID （自动产生） 主键2 EntrustManID Char(18) 托运业主身份证号码3 EntrustMan Char(10) 托运业主的姓名4 EntrustUnit Char(50) 托运业主的单位名称5 EntrustUnitAddr text 业主地址6 EntrustUnitPhone Char(13) 业主联系电话号码7 EntrustUnitPhone Char(7) 业主邮政编码码R
6、eceiver收货人明细表编号 字段名 类型 说明 备注1 Recv_ID int 收货业主ID （自动产生） 主键2 RecverManID Char(18) 收货业主身份证号码3 RecverMan Char(10) 收货业主的姓名4 RecverUnit Char(50) 收货业主的单位名称5 RecverUnitAddr text 收货业主地址6 RecverUnitPhone Char(13) 收货业主联系电话号码7 RecverUnitZone Char(7) 收货业主邮政编码码Good 货物信息明细表编号 字段名 类型 说明 备注1 Good_ID int 货物流水号自动产生）
7、主键2 Good_Name Char(50) 货物名称3 Good_WrapType Char(50) 货物包装类型4 Good_Volume float 货物所占体积5 Good_Weight float 货物重量6 Good_Operator Char(20) 货物录入操作员7 Good_Quantity int 货物数量(件数8 Good_Signal Char(10) 货物的标签9 Good_Arrive Char(10) 货物是否到站 默认在途中Login 登陆信息表编号 字段名 类型 说明 备注1 UserID int 操作员ID(自动产生） 主键2 LoginName Char(
8、20) 操作员登陆名3 UserName Char(20) 操作员名称4 PassWord Char(16) 操作员登陆的密码5 MemberID float 操作员的详细信表ID 外键6 privilege int 操作员的权限 1为最高级LoginLog 登陆日志表编号 字段名 类型 说明 备住1 Log_ID int 日志ID 主键2 LogUser Char(20) 登陆用户3 Log_Time Datetime 日志产生时间4 Log_Info text 日志信息5 Log_IP Char(10) 登陆IPBusinessView视图编号 字段名 类型 说明 备住1 RecverMa
9、nID r.RecverManID 收货身份证 主键2 RecverMan r.RecverMan 收货人姓名3 RecverUnitAddr r.RecverUnitAddr 收货人的地址4 RecverUnitPhone r.RecverUnitPhone 收货人的电话5 EntrustManID e.EntrustManID 发货人身份证6 EntrustMan e.EntrustMan 发货人的姓名7 EntrustUnitPhone e.EntrustUnitPhon 发货人的电话8 EntrustUnitAd e.EntrustUnitAddr 发货人的地址9 Good_Name
10、g.Good_Name 货物的名称10 Good_Signal g.Good_Signal 货物的标签11 Bus_ID b.Bus_ID 业务的流水号12 Bus_GoodID b.Bus_GoodID 货物的ID13 Bus_RecverID b.Bus_RecverID 发货人的ID14 Bus_FromStation b.Bus_FromStation 发货站15 Bus_AcceptTime b.Bus_AcceptTime 业务的时间16 Bus_FromCity b.Bus_FromCity 发货的城市17 Bus_ToStation b.Bus_ToStation 目的站18
11、Bus_ToCity b.Bus_ToCity 目的城市19 Bus_AcceptOpt b.Bus_AcceptOpt 业务操作员20 Bus_Cast b.Bus_Cast 业务的费用21 Bus_IsPay b.Bus_IsPay 付款的方式22 Bus_Fisished b.Bus_Fisished 业务处理状态23 Good_Arrive g.Good_Arrive 货物是否到站24 Bus_TakeUnitID b.Bus_TakeUnitID 发货人的ID备注:r :dbo.Receiver, g: dbo.Good, b:dbo.BusinessOracle数据库应用系统的设计
12、与实现1、数据库的创建12、表设计13、ER图14、数据库关系图15、逻辑结构的实现16、存储过程17、建表18、建立外键约束19、创建序列110、创建触发器11、 数据库的创建2、 表设计用户类型表：编号（主键）、类型名列名 字符类型 长度 约束 说明utID Varchar2 20 主键 用户类型编号utName Varchar2 20 非空 用户类型用户表：编号（主键）、用户名、密码、用户类型编号（外键）、邮箱列名 字符类型 长度 约束 说明userID Varchar2 20 主键 用户编号userName Varchar2 20 唯一 用户名userPassword Varchar2
13、 20 用户密码utID Varchar2 20 外键 用户类型编号email Varchar2 100 唯一 邮箱书籍类型表：编号（主键）、类型名列名 字符类型 长度 约束 说明typeID Varchar2 20 主键 类型编号typeName Varchar2 20 类型名书籍信息表：编号（主键）、名称、价格、库存、图片地址、描述、类型编号（外键）、作者列名 字符类型 长度 约束 说明bookID Varchar2 20 主键 书籍编号bookNumber Int 书籍库存bookTitle Varchar2 250 非空 书籍名称bookPrice Number （10,2） 价格bo
14、kDesc Varchar2 4000 书籍描述typeID Varchar2 20 外键-1 书籍类型编号author Varchar2 20 作者bookStat Varchar2 20 书籍状态购物信息表：用户编号（外键）、商品编号（外键）、商品数量列名 字符类型 长度 约束 说明bookNumber int 商品数量bookID Varchar2 20 外键-3 书籍编号userID Varchar2 20 外键-2 用户编号订单状态表：编号（主键）、状态名列名 字符类型 长度 约束 说明osID Varchar2 20 主键 订单状态编号osName Varchar2 20 订单状态
15、名收货信息表：编号（主键）、用户编号（外键）、联系电话、收货人、收货地址列名 字符类型 长度 约束 说明deliverID Varchar2 20 主键 收货信息编号deliverName Varchar2 40 收货人姓名deliverPhone Varchar2 40 联系电话deliverAddress Varchar2 500 收货地址deliverPostcode Varchar2 20 收货邮编userID Varchar2 20 外键-9 用户编号identityCard Varchar2 60 收货人身份证号订单表：编号（主键）、下单时间、收货信息编号（外键）、用户编号（外键）
16、、订单状态编号（外键）、订单总价、订单结束时间列名 字符类型 长度 约束 说明orderID Varchar2 20 主键 订单编号createDate Date 下单时间orderTotal Number （10,2） 订单总价格endDate Date 订单结束时间deliverID Varchar2 20 外键-7 收货信息编号osID Varchar2 20 外键-8 订单状态编号订单商品信息表：商品编号（外键）、订单编号（外键）、商品数量列名 字符类型 长度 约束 说明bookNumber int 商品数量bookID Varchar2 20 外键-3 商品编号userID Varc
17、har2 20 外键-2 用户编号3、 ER图4、 数据库关系图5、 逻辑结构的实现6、 创建存储过程-存放过程/*删除不报错过程*/CREATE OR REPLACE PROCEDUREifExistDel(sqls varchar2)ASBEGINexecute immediate sqls;exceptionwhen others then dbms_output.put_line(SQLERRM);ENDifExistDel;BEGIN-如果表存在则删除ifExistDel(drop table tb_booksinfo cascade constraints );ifExistDel
18、(drop table tb_book_type cascade constraints );ifExistDel(drop table tb_deliverinfo cascade constraints);ifExistDel(drop table tb_order_main cascade constraints);ifExistDel(drop table tb_order_detail cascade constraints);ifExistDel(drop table tb_order_state cascade constraints);ifExistDel(drop table
19、 tb_user_main cascade constraints);ifExistDel(drop table tb_user_type cascade constraints);ifExistDel(drop table tb_shoppinginfo cascade constraints);-如果序列存在则删除ifExistDel(drop sequence booksinfo_seq);ifExistDel(drop sequence order_main_seq);ifExistDel(drop sequence deliverinfo_seq);ifExistDel(drop s
20、equence user_main_seq);ifExistDel(drop sequence book_type_seq);ifExistDel(drop sequence order_state_seq);END;7、 建表/*书籍信息表*/create table tb_booksinfo(bookID varchar2(20) primary key,bookNumber int, -书籍库存bookTitle varchar2(250) not null, -书籍名称bookPrice number(10,2), -书籍价格bookPicture varchar2(250), -书籍
21、图片地址bookDesc varchar2(4000), -书籍描述typeID varchar2(20), -书籍类型（外键-1）author varchar2(20), -书籍作者bookStat varchar2(20) -书籍状态(下架，上架);/*书籍类型表*/create table tb_book_type(typeID varchar2(20) primary key,typeName varchar2(20) );/*订单表*/create table tb_order_main(orderID varchar2(20) primary key,createDate date
22、, -订单建立时间orderTotal Number(10,2), -订单总价格endDate date, -订单结束时间deliverID varchar2(20), -收货信息编号（外键-7）osID varchar2(20) -订单状态编号（外键-8）);/*收货信息表*/create table tb_deliverinfo(deliverID varchar2(20) primary key, -收货信息编号deliverName varchar2(40), -收货人姓名deliverPhone varchar2(40), -联系电话deliverAddress varchar2(5
23、00), -收货地址deliverPostcode varchar2(20), -收货邮编userID varchar2(20), -用户编号(外键-9)identityCard varchar2(60) -收货人身份证号);/*订单书籍信息表*/create table tb_order_detail(orderID varchar2(20), -订单编号（外键-5）bookNumber int, -书籍购买数量bookID varchar2(20) -书籍编号 （外键-6）);/*订单状态表*/create table tb_order_state(osID varchar2(20) pr
24、imary key,osName varchar2(20) -订单状态（审核中，已发货，已签收，未签收，取消）);/*用户表*/create table tb_user_main(userID varchar2(20) primary key,userName varchar2(20) unique,userPassword varchar2(20),utID varchar2(20), -用户类型编号（外键-4）email varchar2(100) unique);/*用户类型表*/create table tb_user_type( utID varchar2(20) primary k
25、ey,utName varchar2(20) not null);/*购物信息表*/create table tb_shoppinginfo(bookNumber int,bookID varchar2(20), -商品编号（外键-3）userID varchar2(20) -用户编号（外键-2）);8、 建立外键约束/*书籍-书籍类型-1*/alter table tb_booksinfo add constraint f_book_type foreign key(typeID) references tb_book_type(typeID);/*购物信息-用户-2*/alter tabl
26、e tb_shoppinginfo add constraint f_shoppinginfo_user foreign key(userID) references tb_user_main(userID);/*购物信息-书籍-3*/alter table tb_shoppinginfo add constraint f_shoppinginfo_book foreign key(bookID) references tb_booksinfo(bookID);/*用户-用户类型-4*/alter table tb_user_main add constraint f_user_userTyp
27、e foreign key(utID) references tb_user_type(utID);/*订单书籍-订单-5*/alter table tb_order_detail add constraint f_detail_order foreign key(orderID) references tb_order_main(orderID);/*订单书籍-书籍-6*/alter table tb_order_detail add constraint f_detail_booksinfo foreign key(bookID) references tb_booksinfo(bookI
28、D);/*订单-收货信息-7*/alter table tb_order_main add constraint f_order_deliver foreign key(deliverID) references tb_deliverinfo(deliverID);/*订单-订单状态-8*/alter table tb_order_main add constraint f_order_state foreign key(osID) references tb_order_state(osID);/*收货信息-用户-9*/alter table tb_deliverinfo add const
29、raint f_deliver_state foreign key(userID) references tb_user_main(userID);9、 创建序列/*书籍信息表序列*/create sequence booksinfo_seqstart with 1increment by 1maxvalue 200000minvalue 1nocycle;/*订单表序列*/create sequence order_main_seqstart with 1increment by 1maxvalue 200000minvalue 1nocycle;/*订单状态序列*/create seque
30、nce order_state_seqstart with 1increment by 1maxvalue 200minvalue 1nocycle;/*收货信息表序列*/create sequence deliverinfo_seqstart with 1increment by 1maxvalue 200000minvalue 1nocycle; /*用户表序列 */create sequence user_main_seqstart with 1increment by 1maxvalue 2000minvalue 1nocycle; /*书籍类型表序列*/ create sequenc
31、e book_type_seqstart with 1increment by 1maxvalue 200000minvalue 1nocycle; 10、 创建触发器创建触发器 CREATE OR REPLACE TRIGGER test_trigger AFTER DELETE OR INSERT OR UPDATE ON test DECLARE v_type test_log.l_type%TYPE; BEGIN IF INSERTING THEN -INSERT触发 v_type := INSERT; DBMS_OUTPUT.PUT_LINE(记录已经成功插入，并已记录到日志 );
32、ELSIF UPDATING THEN -UPDATE触发 v_type := UPDATE; DBMS_OUTPUT.PUT_LINE(记录已经成功更新，并已记录到日志 ); ELSIF DELETING THEN v_type := DELETE; DBMS_OUTPUT.PUT_LINE(记录已经成功删除，并已记录到日志 ); END IF; INSERT INTO test_log VALUES(user,v_type, TO_CHAR(sysdate,yyyy-mm-dd hh24:mi:ss); END; / -下面我们来分别执行DML语句 INSERT INTO test VALUES(101,zhao,22,M); UPDATE test SET t_age = 30 WHERE t_id = 101; DELETE test WHERE t_id = 101; -然后查看效果 SELECT * FROM test; SELECT * FROM test_log;
