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

课程设计任务书

课程名称：高级数据库开发技术

设计题目：家电服务公司数据库管理系统

已知技术参数和设计要求： 需求说明及要求

题目：家电服务公司数据库系统管理

家用电器服务公司是一家提供多种家用电器服务支持的公司（洗碗机、洗衣机、微波炉等等），该公司能提供大量不同公司的不同产品的技术支持。

该公司发现，通过多次沟通，用户通常能够解决大部分事项。这个方式能既省事又省力。一个服务请求可以被用户、技术人员和管理人员创建。

被该公司雇员打开的服务请求能够和某个产品相连的内部信息建立关联（例如包含该产品原有的各种回复、该类产品的特殊问题，等等）。

该公司计划开发一个Web应用程序来接收用户的反馈。现以该公司Oracle数据库管理员的身份为该Web应用程序的开发人员搭建一个数据库系统。

下面这个过程是公司计划的服务请求处理流程： 1、客户通过Web界面提交一个服务请求； 2、管理员指派服务请求给工程师；

3、工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息； 4、客户检查请求，或者关闭请求或者提供进一步信息；

5、管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；

工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。

1、数据库系统设计

该数据库包含五个表和三个序列号（下图的变量类型均为Oracle变量类型）。

II

2、数据库表

USERS：该表存储所有和系统交互过的用户，包括客户、工程师和管理员。要求保存邮箱地址，姓名，街道，城市，邮编和每个用户的所在区。每个用户有唯一一个ID号。

SERVICE_REQUESTS：该表纪录内部和外部请求的一个关于特定产品的活动。任何情况下，每条记录只记录针对一个产品的一个问题的一个解决方案。当服务请求被创建，请求的日期，打开这个请求的个人姓名，相关联的产品都被记录下来。问题的简短描述也被记录。请求被指派给工程师之后，工程师名字和指派日期也被记录。

SERVICE_HISTORIES: 对每一个服务请求，需要记录很多事件。建立服务请求的日期，建立请求的个人姓名，关于事件的特别备注都要被记录。任何内部相关通讯也被跟踪。每个服务请求和他的序列码在每

III

条服务历史上指明。

PRODUCTS: 这个表保存所有该公司服务的产品。对每个产品，名字和描述被记录。如果产品有图片也可以被记录。

EXPERTISE_AREAS: 为了更好地指派工程师给请求，每个工程师的专业领域被预定义。

序列号

USERS_SEQ: 给每个用户一个序列号； PRODUCTS_SEQ: 给每个产品一个序列号；

SERVICE_REQUESTS_SEQ: 给每个服务请求一个序列号。

3、索引及约束

建立上图所描述的所有的索引和约束，同时注意索引的类型和唯一性。

4、项目的独立性

为该项目创建自身独立的表空间、还原表空间、临时表空间、索引表空间，将表和索引分别装入各自的表空间，运用Oracle数据库管理的知识，使为该项目创建的还原表空间成为活动的还原表空间，使为该项目创建的临时表空间成为默认临时表空间，各类型的数据文件尽可能地分散在不同的虚拟磁盘上，其中包括控制文件和重做日志文件的分散性处理，系统中不得留有任何垃圾文件。

各阶段具体要求： 1、需求分析阶段

认真分析题目要求

2、数据库管理阶段

创建该项目的各个表空间并配置Oracle数据库

3、实施阶段

按要求创建和管理表、索引和约束

设计工作量：

（1）系统设计：完成问题陈述中所提到的所有需求功能。

（2）论文：要求撰写不少于3000个文字的文档， 通过罗列Oracle管理员所用 指令及系统输出来详细说明各阶段所作的具体工作。

工作计划：

安排两周时间进行课程设计，系统开发步骤如下，第一周完成1～3，第二周完成4～5，论文同步进行； 1) 分组 2) 需求分析 3) 数据库设计与管理 4) 实施 5) 答辩

IV

计划时间 13-14周

注意事项 ? ? ? ?

指导老师 学生 提交文档

长沙学院课程设计任务书 长沙学院课程设计论文 长沙学院课程设计鉴定表

（每学生1份） （每学生1份） （每学生1份）

指导教师签名： 教研室主任签名： 系主任签名：

V

日期： 日期： 日期：摘要

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

关键词：家电服务公司数据库系统，表空间，序列号，索引，控制文件，ORACLE

VI

目录

第一章 需求分析 ................................................................................................................................................ 1

1.1家电服务公司数据库系统的背景 ....................................................................................................... 1 1.2家电服务公司数据库系统的内容 ....................................................................................................... 1 1.3家电服务公司数据库系统管理的目的 ............................................................................................... 1 1.4表之间的关系分析 ............................................................................................................................... 1 1.5关系图 ................................................................................................................................................... 2 第二章 设计与实现 ............................................................................................................................................ 3

2.1创建用户 ............................................................................................................................................... 3 2.2创建表空间 ........................................................................................................................................... 3 2.3创建表 ................................................................................................................................................... 5 2.4键和约束 ............................................................................................................................................... 7

2.4.1五个主键 ..................................................................................................................................... 7 2.4.2七个外键 ..................................................................................................................................... 9 2.4.3一个唯一键 ............................................................................................................................... 10 2.4.4四个check约束 ........................................................................................................................ 10 2.5创建序列号 ......................................................................................................................................... 12 2.6管理重做日志文件 ............................................................................................................................. 12 2.7控制文件备份 ..................................................................................................................................... 13 参考文献 ............................................................................................................................................................ 15

VII

第一章 需求分析

1.1家电服务公司数据库系统的背景

家用电器服务公司是一家提供多种家用电器服务支持的公司（洗碗机、洗衣机、微波炉等等），该公司能提供大量不同公司的不同产品的技术支持。

该公司发现，通过多次沟通，用户通常能够解决大部分事项。这个方式能既省事又省力。一个服务请求可以被用户、技术人员和管理人员创建。被该公司雇员打开的服务请求能够和某个产品相连的内部信息建立关联（例如包含该产品原有的各种回复、该类产品的特殊问题，等等）。

1.2家电服务公司数据库系统的内容

客户通过Web界面提交一个服务请求；管理员指派服务请求给工程师；工程师审查服务请求，然后或者给出一个解决方案或者询问用户更多信息；客户检查请求，或者关闭请求或者提供进一步信息；管理员能够审查人指派给工程师的任一个请求而且如果需要可以将其指派给另外一个工程师；工程师能标识他们专业领域的产品知识的级别。管理员能够根据这个信息用于指派服务请求。

1.3家电服务公司数据库系统管理的目的

家电服务公司数据库系统是利用数据库软件编制一个管理软件，用以实现用户、技术人员，管理人员以及日常服务请求等多项管理。同时对整个系统的分析、设计过程给出一个完整论证。家电服务公司数据库系统是一种基于集中统一规划的数据库数据管理新模式。在对用户、服务的管理，其实是对用户、服务请求数据的管理。本系统的建成无疑会为管理者对家电服务公司系统提供极大的帮助。

1.4表之间的关系分析

该数据库包含五个表,分别为USERS，SERVICE_REQUESTS，SERVICE_HISTORIE，PRODUCTS和EXPERTISE_AREAS。下图展示了在可视化工具power designer中创建各表的属性及其类型和范围：

Table: USERS

图1-1

Table: SERVICE_REQUESTS

1

图1-2

Table: SERVICE_HISTORIE

图1-3

Table: PRODUCTS

图1-4

Table: EXPERTISE_AREAS

图1-5

1.5关系图

图1-6

2

第二章 设计与实现

2.1创建用户

1）创建一个fuxing用户密码为abc，并赋予它系统权限

2）连接到fuxing用户

2.2创建表空间

1) 创建了一个位于I:\\oracle\\Disk4目录下名为appliance_service，大小为10M的本地表空间，每

个区间的大小是1M。

SQL> create tablespace appliance_service

2 datafile 'I:\\oracle\\Disk4\\appliance_service.dbf' size 10m 3 extent management local 4 uniform size 1m;

表空间已创建。

2) 创建了一个位于I:\\oracle\\Disk7目录下名为appliance_service_temp，大小为10M的临时表空

间，每个区间的大小是2M

SQL> create temporary tablespace appliance_service_temp 2 tempfile 'I:\\oracle\\Disk7\\appliance_service_temp.dbf' 3 size 10m

4 extent management local 5 uniform size 2m;

表空间已创建。

3

3) 创建了一个位于I:\\oracle\\Disk4目录下名为appliance_service_undo ，大小为20M的还原表空

间

SQL> create undo tablespace appliance_service_undo 2 datafile 'I:\\oracle\\Disk4\\appliance_service_undo.dbf' 3 size 20m;

表空间已创建。

4) 创建了一个位于I:\\oracle\\Disk5目录下名为appliance_service_index，大小为10M的索引表空

间，每个区间的大小是1M。 SQL> get a.sql

1 create tablespace appliance_service_index

2 datafile 'I:\\oracle\\Disk5\\appliance_service_index.dbf' size 10m 3 extent management local 4* uniform size 1m; SQL> start a.sql

表空间已创建。

5) 完成创建表空间以后，要查看所建的表空间，输入如下语句，得到如图2-1结果，表明所有的表空

间都已经建立了。

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS ------------------------------ --------- SYSTEM PERMANENT UNDOTBS1 UNDO SYSAUX PERMANENT TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT APPLIANCE_SERVICE PERMANENT APPLIANCE_SERVICE_UNDO UNDO APPLIANCE_SERVICE_TEMP TEMPORARY APPLIANCE_SERVICE_INDEX PERMANENT

已选择10行。

4

6) 为了不使system表空间碎片化，从而降低数据库系统的效率，下一步就是要将临时表空间设置为

默认临时表空间。

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_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* where name like '%undo%'; SQL> start a.sql

NAME VALUE

-------------------- -------------------- undo_management AUTO

undo_tablespace APPLIANCE_SERVICE_UNDO undo_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), 7 created_by number(8,0)) 8* tablespace appliance_service; SQL> start a.sql

5

表已创建。

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 assigned_to number(8,0)) 9* tablespace appliance_service; SQL> start a.sql

表已创建。

SQL> get a.sql

1 create table scott.products 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.users 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> start a.sql

表已创建。

6

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 EMP BONUS SALGRADE SERVICE_REQUESTS SERVICE_HISTORIES PRODUCTS

已选择9行。

2.4键和约束

2.4.1五个主键

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 a.sql

7

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 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

表已更改。

SQL> get a.sql

1 alter table scott.products 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

表已更改。

8

2.4.2七个外键

SQL> get a.sql

1 alter table scott.service_histories 2 add constraint svh_svr_fk

3* foreign key (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_created_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_requests 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

9

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.expertise_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.sql

表已更改。

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_level < 'B'); SQL> start a.sql

表已更改。

10

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

表已更改。

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

11

已选择20行。

SQL> spool off

2.5创建序列号

USERS_SEQ: 给每个用户一个序列号； PRODUCTS_SEQ: 给每个产品一个序列号；

SERVICE_REQUESTS_SEQ: 给每个服务请求一个序列号。 1) 创建序列号

a) 创建序列号USERS_SEQ，最小值为1，最大值为999999999999，从1开始每次增加1，并

且不缓存

create sequence USERS_SEQ minvalue 1

maxvalue 999999999999 start with 1 increment by 1 nocache;

b) 按照各序列号的要求创建剩下的两个序列号。

2) 序列号的使用，以USERS_SEQ为例，得到如图2-5所示结果。 insert into USERS (USER_ID) values(USERS_SEQ.NEXTVAL);

a) 产生序列的下一个值: SELECT USERS_SEQ.NEXTVAL FROM DUAL;

b) 产生序列的当前值： SELECT USERS_SEQ.CURRVAL FROM DUAL;

图2-1

产生的值为4的原因是在之前自增了几次。

2.6管理重做日志文件

1) 从v$log查看日志文件，如图2-12

SELECT group#, sequence#, members, bytes, status, archived FROM v$log;

12

图2-2

2) 从v$logfile查看日志文件，如图2-13 SELECT * FROM v$logfile;

图2-3

3) 更改日志文件到disk8中，如图2-14 ALTER DATABASE ADD LOGFILE MEMBER 'D:\\123\\Disk8\\redo01b.log' TO GROUP 1, 'D:\\123\\Disk8\\redo02b.log' TO GROUP 2, 'D:\\123\\Disk8\\redo03b.log' TO GROUP 3;

图2-4

2.7控制文件备份

1) 查看控制文件信息，如图2-15

select * from v$controlfile;

13

图2-5

2) 备份控制文件到D:\\123\\backup目录下，如图2-16

ALTER DATABASE BACKUP CONTROLFILE TO 'D:\\123\\backup\\contro2.bak';

图2-6

3) 将创建控制文件的命令保存在一个追踪文件里

ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 4) 转移控制文件到disk5，如图2-17

host copy D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORACLE\\CONTROL01.CTL D:\\123\\disk5\\CONTROL01.CTL host copy D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORACLE\\CONTROL02.CTL D:\\123\\disk5\\CONTROL02.CTL host copy D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORACLE\\CONTROL03.CTL D:\\123\\disk5\\CONTROL03.CTL

图2-7

14

参考文献

[1] 何明. Oracle DBA培训教程[M]. 北京：清华大学出版社,2009.07 [2] 郑阿奇. Oracle实用教程[M]. 北京：电子工业出版社,2003.10

15

本文来源：https://www.bwwdw.com/article/04u5.html