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

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

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

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

4、列码在每条服务历史上指明。PRODUCTS: 这个表保存所有该公司服务的产品。对每个产品，名字和描述被记录。如果产品有图片也可以被记录。EXPERTISE_AREAS: 为了更好地指派工程师给请求，每个工程师的专业领域被预定义。序列号USERS_SEQ: 给每个用户一个序列号；PRODUCTS_SEQ: 给每个产品一个序列号；SERVICE_REQUESTS_SEQ: 给每个服务请求一个序列号。3、索引及约束建立上图所描述的所有的索引和约束，同时注意索引的类型和唯一性。4、项目的独立性为该项目创建自身独立的表空间、还原表空间、临时表空间、索引表空间，将表和索引分别装入各自的表空间，运用Orac

5、le数据库管理的知识，使为该项目创建的还原表空间成为活动的还原表空间，使为该项目创建的临时表空间成为默认临时表空间，各类型的数据文件尽可能地分散在不同的虚拟磁盘上，其中包括控制文件和重做日志文件的分散性处理，系统中不得留有任何垃圾文件。各阶段具体要求：1、需求分析阶段认真分析题目要求2、数据库管理阶段创建该项目的各个表空间并配置Oracle数据库3、实施阶段按要求创建和管理表、索引和约束设计工作量：（1）系统设计：完成问题陈述中所提到的所有需求功能。（2）论文：要求撰写不少于3000个文字的文档， 通过罗列Oracle管理员所用指令及系统输出来详细说明各阶段所作的具体工作。工作计划：安排两周时

6、间进行课程设计，系统开发步骤如下，第一周完成13，第二周完成45，论文同步进行；1)分组 2)需求分析3)数据库设计与管理4)实施 5)答辩计划时间指导老师学生13-14周注意事项提交文档长沙学院课程设计任务书（每学生1份）长沙学院课程设计论文（每学生1份）长沙学院课程设计鉴定表（每学生1份）指导教师签名： 日期： 教研室主任签名： 日期：系主任签名： 日期：摘要家电服务公司数据库系统管理是对多种家电服务的管理。公司计划的服务请求处理流程：客户通过Web界面提交一个服务请求；管理员指派服务请求给工程师；工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；客户检查请求，或者关闭请求

7、或者提供进一步信息；管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。建立家电的本地表空间APPLIANCE_SERVICE，在表空间分别创建USERS表用来存储所有和系统交互过的用户；SERVICE_HISTORIES表用来对每一个服务请求，需要记录很多事件；SERVICE_REQUESTS表用来纪录内部和外部请求的一个关于特定产品的活动；PRODUCTS表用来保存所有该公司服务的产品；EXPERTISE_AREAS表用来更好地指派工程师给请求。这几张表是进行管理的基础，一切活动

8、均是建立在这几张表的基础之上。关键词：家电服务公司数据库系统，表空间，序列号，索引，控制文件，ORACLE目录 TOC o 1-3 h z u HYPERLINK l _Toc294625754 第一章 需求分析 PAGEREF _Toc294625754 h 1 HYPERLINK l _Toc294625755 1.1家电服务公司数据库系统的背景 PAGEREF _Toc294625755 h 1 HYPERLINK l _Toc294625756 1.2家电服务公司数据库系统的内容 PAGEREF _Toc294625756 h 1 HYPERLINK l _Toc294625757 1

9、.3家电服务公司数据库系统管理的目的 PAGEREF _Toc294625757 h 1 HYPERLINK l _Toc294625758 1.4表之间的关系分析 PAGEREF _Toc294625758 h 1 HYPERLINK l _Toc294625759 1.5关系图 PAGEREF _Toc294625759 h 2 HYPERLINK l _Toc294625760 第二章 设计与实现 PAGEREF _Toc294625760 h 3 HYPERLINK l _Toc294625761 2.1创建用户 PAGEREF _Toc294625761 h 3 HYPERLINK

10、l _Toc294625762 2.2创建表空间 PAGEREF _Toc294625762 h 3 HYPERLINK l _Toc294625763 2.3创建表 PAGEREF _Toc294625763 h 5 HYPERLINK l _Toc294625764 2.4键和约束 PAGEREF _Toc294625764 h 7 HYPERLINK l _Toc294625765 五个主键 PAGEREF _Toc294625765 h 7 HYPERLINK l _Toc294625766 七个外键 PAGEREF _Toc294625766 h 9 HYPERLINK l _Toc

11、294625767 一个唯一键 PAGEREF _Toc294625767 h 10 HYPERLINK l _Toc294625768 四个check约束 PAGEREF _Toc294625768 h 10 HYPERLINK l _Toc294625769 2.5创建序列号 PAGEREF _Toc294625769 h 12 HYPERLINK l _Toc294625770 2.6管理重做日志文件 PAGEREF _Toc294625770 h 12 HYPERLINK l _Toc294625771 2.7控制文件备份 PAGEREF _Toc294625771 h 13 HYPE

12、RLINK l _Toc294625772 参考文献 PAGEREF _Toc294625772 h 15第一章 需求分析1.1家电服务公司数据库系统的背景家用电器服务公司是一家提供多种家用电器服务支持的公司（洗碗机、洗衣机、微波炉等等），该公司能提供大量不同公司的不同产品的技术支持。该公司发现，通过多次沟通，用户通常能够解决大部分事项。这个方式能既省事又省力。一个服务请求可以被用户、技术人员和管理人员创建。被该公司雇员打开的服务请求能够和某个产品相连的内部信息建立关联（例如包含该产品原有的各种回复、该类产品的特殊问题，等等）。1.2家电服务公司数据库系统的内容客户通过Web界面提交一个服务请

13、求；管理员指派服务请求给工程师；工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；客户检查请求，或者关闭请求或者提供进一步信息；管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。1.3家电服务公司数据库系统管理的目的家电服务公司数据库系统是利用数据库软件编制一个管理软件，用以实现用户、技术人员，管理人员以及日常服务请求等多项管理。同时对整个系统的分析、设计过程给出一个完整论证。家电服务公司数据库系统是一种基于集中统一规划的数据库数据管理新模式。在对用户、服务的管理，

14、其实是对用户、服务请求数据的管理。本系统的建成无疑会为管理者对家电服务公司系统提供极大的帮助。 1.4表之间的关系分析该数据库包含五个表,分别为USERS，SERVICE_REQUESTS，SERVICE_HISTORIE，PRODUCTS和EXPERTISE_AREAS。下图展示了在可视化工具power designer中创建各表的属性及其类型和范围：Table: USERS 图1-1Table: SERVICE_REQUESTS图1-2Table: SERVICE_HISTORIE图1-3Table: PRODUCTS图1-4Table: EXPERTISE_AREAS 图1-51.5关系

15、图图1-6第二章 设计与实现2.1创建用户1）创建一个fuxing用户密码为abc，并赋予它系统权限 2）连接到fuxing用户2.2创建表空间创建了一个位于I:oracleDisk4目录下名为appliance_service，大小为10M的本地表空间，每个区间的大小是1M。SQL create tablespace appliance_service 2 datafile I:oracleDisk4appliance_service.dbf size 10m 3 extent management local 4 uniform size 1m;表空间已创建。创建了一个位于I:oracle

16、Disk7目录下名为appliance_service_temp，大小为10M的临时表空间，每个区间的大小是2MSQL create temporary tablespace appliance_service_temp 2 tempfile I:oracleDisk7appliance_service_temp.dbf 3 size 10m 4 extent management local 5 uniform size 2m;表空间已创建。创建了一个位于I:oracleDisk4目录下名为appliance_service_undo ，大小为20M的还原表空间SQL create undo

17、 tablespace appliance_service_undo 2 datafile I:oracleDisk4appliance_service_undo.dbf 3 size 20m;表空间已创建。 创建了一个位于I:oracleDisk5目录下名为appliance_service_index，大小为10M的索引表空间，每个区间的大小是1M。SQL get a.sql 1 create tablespace appliance_service_index 2 datafile I:oracleDisk5appliance_service_index.dbf size 10m 3 e

18、xtent management local 4* uniform size 1m;SQL start a.sql表空间已创建。完成创建表空间以后，要查看所建的表空间，输入如下语句，得到如图2-1结果，表明所有的表空间都已经建立了。SQL select tablespace_name,contents from dba_tablespaces;TABLESPACE_NAME CONTENTS SYSTEM PERMANENTUNDOTBS1 UNDOSYSAUX PERMANENTTEMP TEMPORARYUSERS PERMANENTEXAMPLE PERMANENTAPPLIANCE_S

19、ERVICE PERMANENTAPPLIANCE_SERVICE_UNDO UNDOAPPLIANCE_SERVICE_TEMP TEMPORARYAPPLIANCE_SERVICE_INDEX PERMANENT已选择10行。为了不使system表空间碎片化，从而降低数据库系统的效率，下一步就是要将临时表空间设置为默认临时表空间。alter database default temporary tablespace appliance_service_temp;查看表空间SQL select property_name,property_value from database_proper

20、ties 2 where property_name like DEFAULT_TEMP%;PROPERTY_NAME PROPERTY_VALUE DEFAULT_TEMP_TABLESPACE APPLIANCE_SERVICE_TEMP可以验证，已经将appliance_service_temp设置为默认临时表空间7) 还原表空间的使用SQL alter system set undo_tablespace = appliance_service_undo;系统已更改。SQL get a.sql 1 select name,value 2 from v$parameter 3* wher

21、e name like %undo%;SQL start a.sqlNAME VALUE undo_management AUTOundo_tablespace APPLIANCE_SERVICE_UNDOundo_retention 900 2.3创建表SQL get a.sql 1 create table scott.service_histories 2 (svr_id number(8,0), 3 line_no number(4,0), 4 svh_date timestamp(6), 5 notes varchar2(4000), 6 svh_type varchar2(10),

22、 7 created_by number(8,0) 8* tablespace appliance_service;SQL start a.sql表已创建。SQL get a.sql 1 create table scott.service_requests 2 (svr_id number(8,0), 3 status varchar2(20), 4 request_date timestamp(6), 5 problem_description varchar2(4000), 6 prod_id number(8,0), 7 created_by number(8,0), 8 assign

23、ed_to number(8,0) 9* tablespace appliance_service;SQL start a.sql表已创建。SQL get a.sql 1 create table ducts 2 (prod_id number(8,0), 3 name varchar2(50), 4 image varchar2(4000), 5 description varchar2(4000) 6* tablespace appliance_service;SQL start a.sql表已创建。SQL get a.sql 1 create table scott.u

24、sers 2 (user_id number(8,0), 3 user_role varchar2(10), 4 email varchar2(50), 5 first_name varchar2(30), 6 last_name varchar2(30), 7 street_address varchar2(40), 8 city varchar2(30), 9 state_province varchar2(25), 10 postal_code varchar2(12), 11 country_id char(2) 12* tablespace appliance_service;SQL

25、 start a.sql表已创建。SQL get a.sql 1 create table scott.expertise_areas 2 (prod_id number(8,0), 3 user_id number(8,0), 4 expertise_level varchar2(30), 5 notes varchar2(4000) 6* tablespace appliance_service;SQL start a.sql表已创建。SQL select table_name from user_tables;TABLE_NAME USERS EXPERTISE_AREAS DEPT E

26、MP BONUS SALGRADE SERVICE_REQUESTS SERVICE_HISTORIES PRODUCTS 已选择9行。2.4键和约束五个主键SQL get a.sql 1 alter table scott.service_histories 2 add constraint svh_pk 3 primary key (svr_id,line_no) 4 using index 5 storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get

27、a.sql 1 alter table scott.service_requests 2 add constraint svr_pk 3 primary key (svr_id) 4 using index 5 storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.users 2 add constraint usr_pk 3 primary key (user_id) 4 using index 5

28、storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.expertise_areas 2 add constraint exa_pk 3 primary key (prod_id,user_id) 4 using index 5 storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.sql表已更

29、改。SQL get a.sql 1 alter table ducts 2 add constraint prd_pk 3 primary key (prod_id) 4 using index 5 storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.sql表已更改。2.4.2七个外键SQL get a.sql 1 alter table scott.service_histories 2 add constraint svh_svr_fk 3* foreign k

30、ey (svr_id) references service_requests (svr_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.service_histories 2 add constraint svh_usr_fk 3* foreign key (created_by) references users (user_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.service_requests 2 add constraint svr_crea

31、ted_by_usr_fk 3* foreign key (created_by) references users (user_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.service_requests 2 add constraint svr_assigned_to_usr_fk 3* foreign key (assigned_to) references users (user_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.service_re

32、quests 2 add constraint svr_prd_fk 3* foreign key (prod_id) references products(prod_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.expertise_areas 2 add constraint exa_prd_fk 3* foreign key (prod_id) references products(prod_id);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.exper

33、tise_areas 2 add constraint exa_usr_fk 3* foreign key (user_id) references users(user_id);SQL start a.sql表已更改。2.4.3一个唯一键SQL get a.sql 1 alter table scott.users 2 add constraint usr_uk 3 unique (email) 4 using index 5 storage(initial 300k next 300k) 6* tablespace appliance_service_index;SQL start a.s

34、ql表已更改。2.4.4四个check约束SQL get a.sql 1 alter table scott.users 2 add constraint user_role_chk 3* check (user_role in (customer,engineer,administrator);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.expertise_areas 2 add constraint expertise_level_chk 3* check (expertise_level A and expertise_le

35、vel start a.sql表已更改。SQL get a.sql 1 alter table scott.service_histories 2 add constraint history_type_chk 3* check ( svh_type in ( A ,B);SQL start a.sql表已更改。SQL get a.sql 1 alter table scott.service_requests 2 add constraint svr_status_chk 3* check ( status in ( Operating ,Waiting);SQL start a.sql表已

36、更改。SQL select constraint_name from user_constraints;CONSTRAINT_NAME SVR_STATUS_CHK HISTORY_TYPE_CHK EXPERTISE_LEVEL_CHK USER_ROLE_CHK EXA_USR_FK EXA_PRD_FK SVR_PRD_FK SVR_ASSIGNED_TO_USR_FK SVR_CREATED_BY_USR_FK SVH_USR_FK SVH_SVR_FK CONSTRAINT_NAME FK_DEPTNO SVH_PK SVR_PK USR_PK EXA_PK PRD_PK USR_UK PK_DEPT PK_EMP 已选择20行。SQL spool off2.5创建序列号USERS_SEQ: 给每个用户一个序列号；PRODUCTS_SEQ: 给每个产品一个序列号；SERVICE_REQUESTS_SEQ: 给每个服务请求一个序列号。创建序列号创建序列号USERS_SEQ，