Oracle-家电服务公司管理系统的数据库设计

1、课程设计任务书课程名称： 高级数据库开发技术设计题目： 家电服务公司数据库管理系统已知技术参数和设计要求：需求说明及要求题目：家电服务公司数据库系统管理家用电器服务公司是一家提供多种家用电器服务支持的公司（洗碗机、洗衣机、微波炉等等），该公司能提供大量不同公司的不同产品的技术支持。该公司发现，通过多次沟通，用户通常能够解决大部分事项。这个方式能既省事又省力。一个服务请求可以被用户、技术人员和管理人员创建。被该公司雇员打开的服务请求能够和某个产品相连的内部信息建立关联（例如包含该产品原有的各种回复、该类产品的特殊问题，等等） 。该公司计划开发一个 Web应用程序来接收用户的反馈。 现以该公司 O

2、racle 数据库管理员的身份为该 Web应用程序的开发人员搭建一个数据库系统。下面这个过程是公司计划的服务请求处理流程：1、客户通过 Web 界面提交一个服务请求；2、管理员指派服务请求给工程师；3、工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；4、客户检查请求，或者关闭请求或者提供进一步信息；5、管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。1、数据库系统设计该数据库包含五个表和三个序列号（下图的变量类型均为Oracle 变量类型）。精品文档你我共享2、

3、数据库表USERS ： 该表存储所有和系统交互过的用户，包括客户、工程师和管理员。要求保存邮箱地址，姓名，街道，城市，邮编和每个用户的所在区。每个用户有唯一一个ID 号。SERVICE_REQUESTS：该表纪录内部和外部请求的一个关于特定产品的活动。任何情况下， 每条记录只记录针对一个产品的一个问题的一个解决方案。当服务请求被创建，请求的日期， 打开这个请求的个人姓名，相关联的产品都被记录下来。问题的简短描述也被记录。请求被指派给工程师之后，工程师名字和指派日期也被记录。SERVICE_HISTORIES: 对每一个服务请求，需要记录很多事件。建立服务请求的日期，建立请求的个人姓名，关于事件

4、的特别备注都要被记录。任何内部相关通讯也被跟踪。每个服务请求和他的序列码在每AAAAAA: 给每个产品一个序列号；精品文档你我共享条服务历史上指明。PRODUCTS : 这个表保存所有该公司服务的产品。对每个产品，名字和描述被记录。如果产品有图片也可以被记录。EXPERTISE_AREAS: 为了更好地指派工程师给请求，每个工程师的专业领域被预定义。序列号USERS_SEQ : 给每个用户一个序列号；PRODUCTS_SEQSERVICE_REQUESTS_SEQ: 给每个服务请求一个序列号。3、索引及约束建立上图所描述的所有的索引和约束，同时注意索引的类型和唯一性。4、项目的独立性为该项目创

5、建自身独立的表空间、还原表空间、临时表空间、索引表空间， 将表和索引分别装入各自的表空间，运用Oracle 数据库管理的知识，使为该项目创建的还原表空间成为活动的还原表空间，使为该项目创建的临时表空间成为默认临时表空间，各类型的数据文件尽可能地分散在不同的虚拟磁盘上，其中包括控制文件和重做日志文件的分散性处理，系统中不得留有任何垃圾文件。各阶段具体要求：1、需求分析阶段认真分析题目要求2、数据库管理阶段创建该项目的各个表空间并配置Oracle 数据库3、实施阶段按要求创建和管理表、索引和约束设计工作量：（1）系统设计：完成问题陈述中所提到的所有需求功能。（2）论文：要求撰写不少于 3000 个

6、文字的文档， 通过罗列 Oracle 管理员所用指令及系统输出来详细说明各阶段所作的具体工作 。工作计划：安排两周时间进行课程设计，系统开发步骤如下，第一周完成 13，第二周完成 45，论文同步进行；分组需求分析数据库设计与管理实施答辩AAAAAA精品文档你我共享计划时间指导老师学生13-14 周注意事项提交文档长沙学院课程设计任务书（每学生 1 份）长沙学院课程设计论文（每学生 1 份）长沙学院课程设计鉴定表（每学生 1 份）指导教师签名：日期：教研室主任签名：日期：系主任签名：日期：AAAAAA精品文档你我共享摘要家电服务公司数据库系统管理是对多种家电服务的管理。公司计划的服务请求处理流程

7、：客户通过Web 界面提交一个服务请求；管理员指派服务请求给工程师；工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；客户检查请求， 或者关闭请求或者提供进一步信息；管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。建立家电的本地表空间APPLIANCE_SERVICE，在表空间分别创建USERS 表用来存储所有和系统交互过的用户；SERVICE_HISTORIES表用来对每一个服务请求，需要记录很多事件；SERVICE_REQUESTS 表用来纪录内部和外部请求的

8、一个关于特定产品的活动；PRODUCTS 表用来保存所有该公司服务的产品； EXPERTISE_AREAS 表用来更好地指派工程师给请求。这几张表是进行管理的基础，一切活动均是建立在这几张表的基础之上。关键词： 家电服务公司数据库系统，表空间，序列号，索引，控制文件，ORACLEAAAAAA精品文档你我共享目录第一章需求分析 .11.1家电服务公司数据库系统的背景.11.2家电服务公司数据库系统的内容.11.3家电服务公司数据库系统管理的目的.11.4表之间的关系分析 .11.5关系图 .2第二章设计与实现 .32.1创建用户 .32.2创建表空间 .32.3创建表 .52.4键和约束 .72

9、.4.1五个主键 .72.4.2七个外键 .92.4.3一个唯一键 .102.4.4四个 check 约束 .102.5创建序列号 .122.6管理重做日志文件 .122.7控制文件备份 .13参考文献 .15AAAAAA第一章需求分析1.1 家电服务公司数据库系统的背景家用电器服务公司是一家提供多种家用电器服务支持的公司（洗碗机、洗衣机、微波炉等等），该公司能提供大量不同公司的不同产品的技术支持。该公司发现，通过多次沟通，用户通常能够解决大部分事项。这个方式能既省事又省力。一个服务请求可以被用户、 技术人员和管理人员创建。被该公司雇员打开的服务请求能够和某个产品相连的内部信息建立关联（例如包

10、含该产品原有的各种回复、该类产品的特殊问题，等等）。1.2 家电服务公司数据库系统的内容客户通过Web界面提交一个服务请求；管理员指派服务请求给工程师；工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；客户检查请求， 或者关闭请求或者提供进一步信息；管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。1.3 家电服务公司数据库系统管理的目的家电服务公司数据库系统是利用数据库软件编制一个管理软件，用以实现用户、 技术人员， 管理人员以及日常服务请求等多项管理。同时对整个

11、系统的分析、设计过程给出一个完整论证。家电服务公司数据库系统是一种基于集中统一规划的数据库数据管理新模式。在对用户、 服务的管理， 其实是对用户、服务请求数据的管理。本系统的建成无疑会为管理者对家电服务公司系统提供极大的帮助。1.4 表之间的关系分析该 数 据 库包 含五 个 表 , 分 别 为USERS， SERVICE_REQUESTS，SERVICE_HISTORIE， PRODUCTS和EXPERTISE_AREAS。下图展示了在可视化工具power designer中创建各表的属性及其类型和范围：Table: USERS图 1-1Table: SERVICE_REQUESTSAAAA

12、AA精品文档你我共享图 1-2Table: SERVICE_HISTORIE图 1-3Table: PRODUCTS图 1-4Table: EXPERTISE_AREAS图 1-51.5 关系图图 1-6AAAAAA精品文档你我共享第二章设计与实现2.1 创建用户1）创建一个fuxing用户密码为abc ，并赋予它系统权限2）连接到 fuxing用户2.2 创建表空间创建了一个位于 I:oracleDisk4 目录下名为 appliance_service ，大小为 10M的本地表空间，每个区间的大小是 1M。SQL create tablespace appliance_service2 d

13、atafile I:oracleDisk4appliance_service.dbf size 10m3 extent management local4 uniform size 1m;表空间已创建。创建了一个位于 I:oracleDisk7 目录下名为 appliance_service_temp ，大小为 10M的临时表空间，每个区间的大小是 2MSQL create temporary tablespace appliance_service_temp 2 tempfile I:oracleDisk7appliance_service_temp.dbf3 size 10m4 exten

14、t management local5 uniform size 2m;表空间已创建。AAAAAA精品文档你我共享3)创建了一个位于I:oracleDisk4目录下名为appliance_service_undo，大小为 20M的还原表空间SQL create undo tablespace appliance_service_undodatafile I:oracleDisk4appliance_service_undo.dbfsize 20m;表空间已创建。4)创建了一个位于I:oracleDisk5目录下名为appliance_service_index，大小为 10M的索引表空间，每个

15、区间的大小是1M。SQL get a.sqlcreate tablespace appliance_service_indexdatafile I:oracleDisk5appliance_service_index.dbf size 10mextent management local4*uniform size 1m;SQL start a.sql表空间已创建。5)完成创建表空间以后，要查看所建的表空间，输入如下语句， 得到如图2-1 结果，表明所有的表空间都已经建立了。SQL select tablespace_name,contents from dba_tablespaces;TAB

16、LESPACE_NAMECONTENTS- -SYSTEMPERMANENTUNDOTBS1UNDOSYSAUXPERMANENTTEMPTEMPORARYUSERSPERMANENTEXAMPLEPERMANENTAPPLIANCE_SERVICEPERMANENTAPPLIANCE_SERVICE_UNDOUNDOAPPLIANCE_SERVICE_TEMPTEMPORARYAPPLIANCE_SERVICE_INDEXPERMANENT已选择 10 行。AAAAAA精品文档你我共享为了不使 system 表空间碎片化，从而降低数据库系统的效率，下一步就是要将临时表空间设置为默认临时表空间

17、。alter database default temporary tablespace appliance_service_temp;查看表空间SQL select property_name,property_value from database_properties 2 where property_name like DEFAULT_TEMP%;PROPERTY_NAMEPROPERTY_VALUE- -DEFAULT_TEMP_TABLESPACEAPPLIANCE_SERVICE_TEMP可以验证，已经将appliance_service_temp设置为默认临时表空间还原表空间的

18、使用SQL alter system set undo_tablespace = appliance_service_undo;系统已更改。SQL get a.sqlselect name,valuefrom v$parameter3* where name like %undo%;SQL start a.sqlNAMEVALUE- -undo_managementAUTOundo_tablespaceAPPLIANCE_SERVICE_UNDOundo_retention9002.3 创建表SQL get a.sqlcreate table scott.service_histories(s

19、vr_id number(8,0),line_no number(4,0),svh_date timestamp(6),notes varchar2(4000),svh_type varchar2(10),created_by number(8,0)8* tablespace appliance_service;SQL start a.sqlAAAAAA精品文档你我共享表已创建。SQL get a.sqlcreate table scott.service_requests(svr_id number(8,0),status varchar2(20),request_date timestam

20、p(6),problem_description varchar2(4000),prod_id number(8,0),created_by number(8,0),assigned_to number(8,0)9* tablespace appliance_service;SQL start a.sql表已创建。SQL get a.sqlcreate table ducts(prod_id number(8,0),name varchar2(50),image varchar2(4000),description varchar2(4000)6* tablespace ap

21、pliance_service;SQL start a.sql表已创建。SQL get a.sqlcreate table scott.users(user_id number(8,0),user_role varchar2(10),email varchar2(50),first_name varchar2(30),last_name varchar2(30),street_address varchar2(40),city varchar2(30),state_province varchar2(25),postal_code varchar2(12),country_id char(2)

22、12* tablespace appliance_service;SQL start a.sql表已创建。AAAAAA精品文档你我共享SQL get a.sqlcreate table scott.expertise_areas(prod_id number(8,0),user_id number(8,0),expertise_level varchar2(30),notes varchar2(4000)6* tablespace appliance_service;SQL start a.sql表已创建。SQL select table_name from user_tables;TABLE

23、_NAME-USERSEXPERTISE_AREASDEPTEMPBONUSSALGRADESERVICE_REQUESTSSERVICE_HISTORIESPRODUCTS已选择 9 行。2.4 键和约束五个主键SQL get a.sqlalter table scott.service_historiesadd constraint svh_pkprimary key (svr_id,line_no)using indexstorage(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表

24、已更改。SQL get a.sqlAAAAAA精品文档你我共享alter table scott.service_requestsadd constraint svr_pkprimary key (svr_id)using indexstorage(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get a.sqlalter table scott.usersadd constraint usr_pkprimary key (user_id)using indexstora

25、ge(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get a.sqlalter table scott.expertise_areasadd constraint exa_pkprimary key (prod_id,user_id)using indexstorage(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get a.sqlalter t

26、able ductsadd constraint prd_pkprimary key (prod_id)using indexstorage(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表已更改。AAAAAA精品文档你我共享七个外键SQL get a.sqlalter table scott.service_historiesadd constraint svh_svr_fk3* foreign key (svr_id) references service_reque

27、sts (svr_id); SQL start a.sql表已更改。SQL get a.sqlalter table scott.service_historiesadd constraint svh_usr_fk3* foreign key (created_by) references users (user_id);SQL start a.sql表已更改。SQL get a.sqlalter table scott.service_requestsadd constraint svr_created_by_usr_fk3* foreign key (created_by) referen

28、ces users (user_id);SQL start a.sql表已更改。SQL get a.sqlalter table scott.service_requestsadd constraint svr_assigned_to_usr_fk3* foreign key (assigned_to) references users (user_id);SQL start a.sql表已更改。SQL get a.sqlalter table scott.service_requestsadd constraint svr_prd_fk3* foreign key (prod_id) ref

29、erences products(prod_id);SQL start a.sql表已更改。SQL get a.sql1alter table scott.expertise_areasAAAAAA精品文档你我共享2add constraint exa_prd_fk3* foreign key (prod_id) references products(prod_id);SQL start a.sql表已更改。SQL get a.sqlalter table scott.expertise_areasadd constraint exa_usr_fk3* foreign key (user_i

30、d) references users(user_id);SQL start a.sql表已更改。一个唯一键SQL get a.sqlalter table scott.usersadd constraint usr_ukunique (email)using indexstorage(initial 300k next 300k)6* tablespace appliance_service_index;SQL start a.sql表已更改。四个 check 约束SQL get a.sqlalter table scott.usersadd constraint user_role_chk

31、3* check (user_role in (customer,engineer,administrator); SQL start a.sql表已更改。SQL get a.sqlalter table scott.expertise_areasadd constraint expertise_level_chk3* check (expertise_level A and expertise_level start a.sql表已更改。AAAAAA精品文档你我共享SQL get a.sqlalter table scott.service_historiesadd constraint h

32、istory_type_chk 3* check ( svh_type in ( A ,B);SQL start a.sql表已更改。SQL get a.sqlalter table scott.service_requestsadd constraint svr_status_chk3* check ( status in ( Operating ,Waiting);SQL start a.sql表已更改。SQL select constraint_name from user_constraints;CONSTRAINT_NAME-SVR_STATUS_CHKHISTORY_TYPE_CH

33、KEXPERTISE_LEVEL_CHKUSER_ROLE_CHKEXA_USR_FKEXA_PRD_FKSVR_PRD_FKSVR_ASSIGNED_TO_USR_FKSVR_CREATED_BY_USR_FKSVH_USR_FKSVH_SVR_FKCONSTRAINT_NAME-FK_DEPTNOSVH_PKSVR_PKUSR_PKEXA_PKPRD_PKUSR_UKPK_DEPTPK_EMPAAAAAA精品文档你我共享已选择 20 行。SQL spool off2.5 创建序列号USERS_SEQ:给每个用户一个序列号；PRODUCTS_SEQ:给每个产品一个序列号；SERVICE_RE

34、QUESTS_SEQ:给每个服务请求一个序列号。创建序列号a)创建序列号USERS_SEQ，最小值为1，最大值为999999999999，从 1 开始每次增加1，并且不缓存create sequence USERS_SEQminvalue 1maxvalue 999999999999start with 1increment by 1nocache;按照各序列号的要求创建剩下的两个序列号。序列号的使用，以 USERS_SEQ为例，得到如图 2-5 所示结果。insert into USERS (USER_ID) values(USERS_SEQ.NEXTVAL);产生序列的下一个值 :SELE

35、CT USERS_SEQ.NEXTVAL FROM DUAL;产生序列的当前值：SELECT USERS_SEQ.CURRVAL FROM DUAL;图 2-1产生的值为4 的原因是在之前自增了几次。2.6 管理重做日志文件1)从 v$log 查看日志文件，如图2-12SELECT group#, sequence#, members, bytes, status, archivedFROM v$log;AAAAAA精品文档你我共享图 2-2从 v$logfile 查看日志文件，如图 2-13SELECT * FROM v$logfile;图 2-3更改日志文件到 disk8 中，如图 2-14ALTER DATABASE ADD LOGFILE MEMBER D:123Disk8redo01b.log TO GROUP 1, D:123Disk8redo02b.log TO GRO