项目数据库设计报告名称：关于商品订售的数据字典_Oracle学院：计算机与信息学院目录一、引言 (3)1编写目的 (3)2项目背景 (3)3设计环境 (3)4小组分工 (3)二.、结构设计 (3)1 概念结构设计 (3)2 编码规范 (3)2.1数据库 (3)2.2数据表 (4)2.3 字段 (4)2.4逻辑结构设计 (4)三、E-R图 (4)1 CDM图 (6)2 PDM图 (7)四、生成oracle数据库 (7)五、测试用例 (13)1 员工表 (13)2 顾客表 (13)3 工令表 (13)4 派送表 (14)5 订单表 (14)6 产品表 (14)7 发票表 (14)8 员工表 (14)9 库存表................................................................................................. 错误！未定义书签。

六、授权 (14)七、触发器 (15)一、引言1编写目的此文档仅供本开发小组内部使用。

通过此文档比较直观的说明数据库的结构以及各数据库表之间的关系。

同时规定数据库，数据库表，各字段的命名规范以及表中字段的类型、大小包括字段说明。

2项目背景说明：待开发的数据库暂定名称为商品订购 ,其中包括8张数据表;该数据库设计项目的任务提出者：XXX销售公司用户：程序开发人员注：该软件和数据库最终面向在各销售公司3设计环境PowerDesigner 15.0SQLplus4小组分工二.、结构设计1 概念结构设计本数据库设计为oracle平台2 编码规范2.1数据库数据库命名以数据库相关英文单词命名。

2.2数据表数据表命名2.3 字段字段一般采用英文单词或词组命名(可用翻译软件)，无专业名词可用相近意义单词或词组代替。

如表所示：字段命名示例2.4逻辑结构设计一、相关数据字典（增加处红色标示，删除处蓝色标示，黄色为原因）三、E-R图1 CDM图2 PDM图四、生成oracle数据库以下为代码部分：/*==============================================================*/ /* DBMS name: ORACLE Version 10g */ /* Created on: 2013-12-4 18:18:37 *//*==============================================================*/alter table "delivery"drop constraint FK_DELIVERY_DELTOORD_ORD;alter table "emp"drop constraint FK_EMP_EMP_DEPT_DEPART;alter table "invoice"drop constraint FK_INVOICE_INTOORD_ORD;alter table "ord"drop constraint FK_ORD_REQUEST_CUSTOMER;alter table "ord"drop constraint FK_ORD_SERVE_EMP;alter table "orderdetail"drop constraint FK_ORDERDET_GOODSTODE_PROSTOCK;alter table "orderdetail"drop constraint FK_ORDERDET_ORDERTODE_ORD;alter table "wip"drop constraint FK_WIP_WIP_HAPPE_PROSTOCK;drop table "customer" cascade constraints;drop index "deltoord_FK";drop table "delivery" cascade constraints;drop table "depart" cascade constraints;drop index "emp_dept_FK";drop table "emp" cascade constraints;drop index "intoord_FK";drop table "invoice" cascade constraints;drop index "request_FK";drop index "serve_FK";drop table "ord" cascade constraints;drop index "goodstodetail_FK";drop index "ordertodetail_FK";drop table "orderdetail" cascade constraints;drop table "prostock" cascade constraints;drop index "wip_happend_FK";drop table "wip" cascade constraints;/*==============================================================*/ /* Table: "customer" *//*==============================================================*/ create table "customer" ("custid" VARCHAR2(10) not null,"custname" VARCHAR2(22),"custadd" VARCHAR2(20),"custphone" VARCHAR2(12),"custfax" VARCHAR2(12),constraint PK_CUSTOMER primary key ("custid"));/*==============================================================*/ /* Table: "delivery" *//*==============================================================*/ create table "delivery" ("delino" VARCHAR2(10) not null,"ordid" VARCHAR2(10),"carno" VARCHAR2(10),constraint PK_DELIVERY primary key ("delino"));/*==============================================================*/ /* Index: "deltoord_FK" *//*==============================================================*/ create index "deltoord_FK" on "delivery" ("ordid" ASC);/*==============================================================*/ /* Table: "depart" *//*==============================================================*/ create table "depart" ("deptno" NUMBER(2) not null, "deptname" VARCHAR2(10),"loc" VARCHAR2(13),constraint PK_DEPART primary key ("deptno")/*==============================================================*/ /* Table: "emp" */ /*==============================================================*/ create table "emp" ("empid" VARCHAR2(10) not null, "deptno" NUMBER(2),"empname" VARCHAR2(10),"empadd" VARCHAR2(20),"empphone" VARCHAR2(12),"email" VARCHAR2(20),"mgr" VARCHAR2(10),"hiredate" DATE,"sal" NUMBER(10),"comm" NUMBER(7,2),"pro" VARCHAR2(12),"educa" VARCHAR2(12),"exper" VARCHAR2(20),constraint PK_EMP primary key ("empid"));/*==============================================================*/ /* Index: "emp_dept_FK" */ /*==============================================================*/ create index "emp_dept_FK" on "emp" ("deptno" ASC);/*==============================================================*/ /* Table: "invoice" *//*==============================================================*/ create table "invoice" ("invid" VARCHAR2(10) not null,"ordid" VARCHAR2(10),"money" NUMBER(10),constraint PK_INVOICE primary key ("invid"));/*==============================================================*/ /* Index: "intoord_FK" *//*==============================================================*/ create index "intoord_FK" on "invoice" ("ordid" ASC/*==============================================================*/ /* Table: "ord" *//*==============================================================*/ create table "ord" ("ordid" VARCHAR2(10) not null,"empid" VARCHAR2(10),"custid" VARCHAR2(10),"orddate" DATE,"d_date" DATE,constraint PK_ORD primary key ("ordid"));/*==============================================================*/ /* Index: "serve_FK" *//*==============================================================*/ create index "serve_FK" on "ord" ("empid" ASC);/*==============================================================*/ /* Index: "request_FK" *//*==============================================================*/ create index "request_FK" on "ord" ("custid" ASC);/*==============================================================*/ /* Table: "orderdetail" *//*==============================================================*/ create table "orderdetail" ("ordid" VARCHAR2(10) not null,"proid" VARCHAR2(10) not null,"o_qty" NUMBER(20),constraint PK_ORDERDETAIL primary key ("ordid", "proid"));/*==============================================================*/ /* Index: "ordertodetail_FK" *//*==============================================================*/ create index "ordertodetail_FK" on "orderdetail" ("ordid" ASC);/*==============================================================*/ /* Index: "goodstodetail_FK" *//*==============================================================*/ create index "goodstodetail_FK" on "orderdetail" ("proid" ASC);/*==============================================================*/ /* Table: "prostock" *//*==============================================================*/ create table "prostock" ("proid" VARCHAR2(10) not null,"proname" VARCHAR2(20),"stiqty" NUMBER(10),"insqty" NUMBER(10),constraint PK_PROSTOCK primary key ("proid"));/*==============================================================*/ /* Table: "wip" */ /*==============================================================*/ create table "wip" ("wipid" VARCHAR2(10) not null, "proid" VARCHAR2(10),"w_qty" NUMBER(10),constraint PK_WIP primary key ("wipid"));/*==============================================================*/ /* Index: "wip_happend_FK" */ /*==============================================================*/ create index "wip_happend_FK" on "wip" ("proid" ASC);alter table "delivery"add constraint FK_DELIVERY_DELTOORD_ORD foreign key ("ordid") references "ord" ("ordid");alter table "emp"add constraint FK_EMP_EMP_DEPT_DEPART foreign key ("deptno") references "depart" ("deptno");alter table "invoice"add constraint FK_INVOICE_INTOORD_ORD foreign key ("ordid")references "ord" ("ordid");alter table "ord"add constraint FK_ORD_REQUEST_CUSTOMER foreign key ("custid")references "customer" ("custid");alter table "ord"add constraint FK_ORD_SERVE_EMP foreign key ("empid")references "emp" ("empid");alter table "orderdetail"add constraint FK_ORDERDET_GOODSTODE_PROSTOCK foreign key ("proid") references "prostock" ("proid");alter table "orderdetail"add constraint FK_ORDERDET_ORDERTODE_ORD foreign key ("ordid")references "ord" ("ordid");alter table "wip"add constraint FK_WIP_WIP_HAPPE_PROSTOCK foreign key ("proid")references "prostock" ("proid");五、测试用例1 部门表insert into "depart"("deptno","deptname","loc") values ('36','后勤部','上海');2 顾客表Insert INTO "customer" values ('C026','现代炸药股份有限公司','上海市德平路','(021)-122222','(020)-133333');3 工令表insert into "wip"("wipid","proid","w_qty")values('W026','P005','30');4 派送表insert into "delivery" values('D026','CR26','A1-1111');5 订单表insert into "ord" ("ordid","empid","custid","orddate","d_date") values('CR26','00807','C026',to_date('30/09/91','dd/mm/yy'),to_date('11/10/91','dd/mm/yy'));6 产品表insert into "prostock" ("proid","proname","stiqty","insqty") values('P006','f','30','10');7 发票表insert into "invoice" ("invid","ordid","money") values('I006','CR26','60000');8 员工表Insert INTO "emp" ("empid","deptno","empname","empadd","empphone","email","mgr","hiredate","sal","comm"," pro","educa","exper")values ('00807','36','Wuzj','上海市人民路','(021)-123456','xxx@','Abel',to_date('29/09/91','dd/mm/yy'),'32000','1000','领班','本科','一年');六、授权以下是代码部分：create user czh identified by czhgrant create session to czhgrant select on system."emp" to czhgrant select on system."prostock" to czhgrant select on system."wip" to czhgrant select on system."depart" to czhgrant select on system."customer" to czhgrant select on system."ord" to czhgrant select on system."delivery" to czhgrant select on system."invoice" to czhcreate user hzn identified by hzngrant create session to hzngrant update,insert,delete on system."emp" to hzngrant update,insert,delete on system."prostock" to hzngrant update,insert,delete on system."wip" to hzngrant update,insert,delete on system."depart" to hzngrant update,insert,delete on system."customer" to hzngrant update,insert,delete on system."ord" to hzngrant update,insert,delete on system."delivery" to hzngrant update,insert,delete on system."invoice" to hzn七、触发器以下是代码部分：create trigger insertwip after insert on ord for each rowdeclarev_num number(7,0);v_id wip.wipid%type;beginselect sum(stiqty) into v_num from stock where proid=:new.proid;select max(wipid) into v_id from wip;if v_num<:new.ordqty theninsert into wip values(v_id+1,:new.proid,:new.ordqty-v_num,sysdate,:new.d_date);end if;end;。