数据建模资产管理数据库设计报告

班级：xxxxxxx

姓名：xxx

学号：xxxxxxxx

数据库设计——资产管理    3

1．    逻辑数据库设计    3

1.1 资产管理    3

1.2使用逻辑数据库设计方法    5

2．物理数据库设计    13

1.1设计基本表    13

2.2 设计派生数据的表示    21

2.3 设计派生数据的表示    21

2.4 分析事务    22

2.5 选择文件组织方式    22

2.6 选择索引    22

2.7 设计用户视图    23

2.8 设计访问规则    23

数据库设计——资产管理

1．逻辑数据库设计

1.1 资产管理

    目前能够从事资产管理业务的公司除了证券公司、基金公司、信托公司以外还有第三方理财公司，从某种意义上来说，第三方理财公司在资产管理市场上的拓展和定位有些类似于现如今的私募基金，将专家理财和灵活的合作条款捆绑嫁接作为打开资产管理市场的突破口。像诺亚财富，利得财富都是这样的第三方理财公司。

1.1.1 数据需求

1.员工

员工中每个成员的详细信息包括员工号、姓名、地址（街区、城市、州、邮编）、工作电话号码、家庭电话号码、传真号、职位、性别、薪水、入职日期。在整个业务中，员工号是唯一的。员工包括经理、主管、秘书、普通员工。

    2.资产表

    资产表详细信息表包括资产编号、资产描述、资产序列号、到手日期、进货价格、当前价格、注册日期、下一次维护时间、员工编号、资产种类编号、状态编号。其中资产编号和资产序列号是（唯一的）主键，员工编号、资产种类编号、状态编号都是外键与员工表、资产种类表、状态表进行外键关联。

    3.资产种类表

    资产种类表详细信息包括资产种类编号、资产种类描述。其中资产种类编号是（唯一的）主键。

4.维护表

维护表详细信息表包括维护编号、维护日期、维护时的描述、维护费用、资产编号、员工编号、代理编号。其中维护编号是（唯一的）主键，资产编号、员工编号、代理编号都是外键与资产表、员工表、代理表进行外键关联。

5.服务代理表

服务代理表详细信息包括代理编号、代理名字、代理街、代理城市、代理情形、代理邮编、代理电话号、代理传真号、代理邮箱地址、代理网站地址、联系人姓名、联系人电话号、联系人传真号、联系人邮箱地址。其中代理编号是（唯一的）主键。其中代理名字、代理电话号、代理传真都是唯一的，是代理表的替换键，替换键可以是数据表内不作为主键的其他任何列，只要该键对该数据表唯一即可。换句话说，在唯一列内不允许出现数据重复的现象。

6．资产状态表

资产状态表详细信息包括状态编号、状态描述。其中状态编号为（唯一的）主键。

7.估价表

估价表的详细信息包括估价编号、估价日期、评估价格、产品编号、员工编号。其中估价编号是（唯一的）主键，资产编号和员工编号是外键与资产表和员工表进行外键关联

1.1.2 事务需求

    1.数据库应该支持下述事务

(a) 创建和维护记录关于资产种类、资产状态和资产的详细信息。

    (b) 创建和维护记录代理的详细信息和记录。

    (c) 创建和维护维护过程详细信息以及维护详细花费的记录。

    (d) 创建和维护估价的详细信息和该产品的现有价值。

    (e) 创建和维护员工的详细信息。

    2.数据应该能够支持下述查询事务

    (a) 以表单形式列出资产的名字、资产的进货价格、每个资产的编号，按资产当前价格进行排序。

    (b) 以表单形式列出员工的姓名、电话号，以及他们的家庭住址的详细信息。

    (c) 列出对某一给定的资产进行评估的详细信息。

    (d) 可以列出某个员工管理多少资产的详细信息。

    (e) 以报表形式列出到某一资产的所有详细信息。

1.2使用逻辑数据库设计方法

1.2.1 步骤1.1: 标识实体

    逻辑数据库设计的第一个步骤是标识在数据库中必须表述的主实体。有上面的描述，可以标识如下实体：

Employee（员工）

Asset（资产）

AssetCategory（资产种类）

Maintenance Staff（维护工）

ServiceAgent（服务代理）

（资产状态）

Valuation Staff（估价人员）

将实体存档

    标识完实体后，下一步就是标识存在于这些实体间的所有关系。对于资产管理的实体关系，如图所示。

1.确定关系的多样性约束

标识完要创建的关系后，现在应该确定每个关系的多样性约束。

2.使用实体-关系（ER）建模

在数据库设计阶段，将创建几个代表资产管理的ER模型。其中员工有自身的一对多的关系，属于公司员工，与资产、估价、维护进行外键关联。其中资产具有资产种类、资产状态。其中维护具有代理员工。故有如下ER图。

图3

1.2.3 步骤1.3：标识实体或关系的有关属性

    下一个步骤是标识与已经标识的实体或关系有关的属性。对于资产管理而言，应该标识如图所示的与实体有关的属性。

1.2.4 步骤1.4：确定属性域

1.资产编号的属性域是一个number类型的数字

2.资产描述的属性域是一个最多为200位字符的varchar2类型的字符串

3.资产序列号的属性域是一个最多为50位字符的varchar2类型的字符串

4.资产种类编号的属性域是一个number类型的数字

5.资产种类描述的属性域是不字长的定长字符串

6.资产状态编号的属性域是一个number类型的数字

7.资产状态描述的属性域是一个最多为200位字符的varchar2类型的字符串

8.代理编号的属性域是一个number类型的数字

9.代理姓名的属性域是一个最多为50位字符的varchar2类型的字符串

10.代理所在地区的属性域是一个最多为50位字符的varchar2类型的字符串

11.代理处理情形的属性域是一个最多为200位字符的varchar2类型的字符串

12.代理电话的属性域是一个11位字符的定长varchar2类型的字符串

13.代理传真号的属性域是一个12位字符长的定长varchar2类型的字符串

14.代理E-mail地址是以@注册网站域名.com结尾的50位字符的varchar2类型的字符串

15.代理网址的属性域是一个以http://www.开头的 最多为50位字符的varchar2类型的字符串

16.联系人电话的属性域是一个11位字符的定长varchar2类型的字符串

17.联系人传真号的属性域是一个12位字符长的定长varchar2类型的字符串

18.联系人E-mail地址是以@注册网站域名.com结尾的50位字符的varchar2类型的字符串

19.员工编号的属性域是一个number类型的数字

20.员工名字的属性域是一个最多为50位字符的varchar2类型的字符串

1.2.5 步骤1.5：确定候选键、主键和备用键属性

这个步骤主要是为实体标识候选键，然后选择其中之一作为主键。在标识主键的过程中，要特别注意实体是强实体还是弱实体。其中的Asset离开了AssetCategory将不存在，故AssetCategory依赖Asset为弱实体；Employee离开 Asset还是存在的实体，故Employee不依赖于Asset为强实体；Maintenance为强实体。但是AssetCategory、Valuation都是依赖于Asset而存在的，故三者均为弱实体。

在标识候选键时，应该注意到各个实体的主键及环境的考虑。如图所示。

1.2.6 步骤1.6：特化和泛化实体

1.2.7 步骤1.7：检查模型的数据冗余

1.重新检查一对一关系。

其中资产实体中的信息已经包含在资产信息表中，故资产实体的存在是多余的。应该删掉资产实体。

2.删除冗余关系。

1.2.8 步骤1.8：检查模型是否支持用户事务

    在这个步骤中，检查已经创建的局部逻辑数据模型是否支持用户所需的事务。检查包括：

    1.数据模型中是否存在必要的属性。

    2.如果属性要从多个实体中得到，则两个实体间是否有通路；换而言之，在两个实体间要有已经标识了的关系。如图所示：

1.2.9 步骤2.1：创建表

    在这个步骤中，从逻辑数据模型创建表达用户视图中所描述的实体和关系的表，这时，要为关系数据库使用数据库设计语言（DDL）。将从逻辑数据模型创建表的全部结构都存档。

Employee(employeeNo、name、position、salary、branchNo)

    在这个步骤中，要确保上一步所建的表至少要满足第三范式（3NF）。如果满足不了第三范式，则可能是逻辑数据模型中的某些部分是错误的，或者是从模型产生表的时候产生了错误。

1.3.1 步骤2.3：检查模型是否支持用户事务

    这个步骤与步骤1.8类似，在这个步骤中，除了要检查从实体到表的映射关系并且要确定外键之外，在这个情景下，还可以再次检查从实体到表的映射是否正确地完成，以及所创建的表是否支持标识的用户事务。

1.3.2 步骤2.4：检查业务规则

    业务规则是为了防止数据库不一致而强加的约束。六种完整性约束中，有四种在上一步已经标识了，并且存档在数据字典中。这四种是：需要的数据、属性域的约束、实体完整性和多样性。剩下两种为：参照完整性和其他业务规则。

    1.参照完整性

    考虑两点：

    （1）标识外键是否可以为空（NULL）。通常，如果关系中子表部分是强制的，那么就不允许为空。如果子表部分是可选的，那么就允许为空。    

（2）标识现有约束条件，表明外键的插入、更新或者产出情况。通常，要为每个外键明确说明两个动作：一个是ON UPDATE动作，另一个是ON DELETE动作，用于表明当在父表中更新或删除一条记录时，如何保证参照完整性。如图所示：

Maintenancence

Foreign Key assetNo references Asset(assetNo) ON UPDATE CASCADE ON DELETE NO ACTION

Foreign Key employeeNo references Employee(employeeNo) ON UPDATE CASCADE ON DELETE NO ACTION

1.1设计基本表

1.1.1 在oracle 10g 中创建基本表 用户为sjjm密码为sjjm

使用create table 语句，因此可以这样定义：

◆主键，使用PRIMARY KEY子句。

◆备用键，使用UNIQUE关键字。

◆默认值，使用DEFAULT子句。

◆非空列，使用NOT NULL关键字。

◆外键，使用FOREIGN KEY子句。

◆其他列或表约束，使用CHECK和CONSTRAINT子句。

Employee：

CREATE TABLE "EMPLOYEE" 

  ("EMPLOYEENO" NUMBER, 

   "NAME" VARCHAR2(50), 

   "POSITION" VARCHAR2(50), 

   "SALARY" VARCHAR2(50), 

   CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPLOYEENO")

   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

   TABLESPACE "SYSTEM" ENABLE

   )

在plsql中使用create table 语句在oracle中建立Employee空表的截图

Asset：

CREATE TABLE "ASSET" 

   ("ASSETNO" NUMBER, 

    "ASSETDESCRIPTION" VARCHAR2(200), 

    "SERIALNO" VARCHAR2(50), 

    "DATEACQUIRED" DATE, 

    "PURCHASEPRICE" VARCHAR2(50), 

    "CURRENTVALUE" VARCHAR2(50), 

    "DATESOLD" DATE, 

    "NEXTMAINTENANCEDATE" DATE, 

    "EMPLOYEENO" NUMBER, 

    "ASSETCATEGORYNO" NUMBER, 

    "STATUSNO" NUMBER, 

     CONSTRAINT "PK_ASSET" PRIMARY KEY ("ASSETNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "SERIALNO" UNIQUE ("SERIALNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "FK_EMPLOYEE" FOREIGN KEY ("EMPLOYEENO")

     REFERENCES "EMPLOYEE" ("EMPLOYEENO") ENABLE, 

     CONSTRAINT "FK_ASSETCATEGORY" FOREIGN KEY ("ASSETCATEGORYNO")

     REFERENCES "ASSETCATEGORY" ("ASSETCATEGORYNO") ENABLE, 

     CONSTRAINT "FK_STATUS" FOREIGN KEY ("STATUSNO")

     REFERENCES "STATUS" ("STATUSNO") ENABLE

   )

在plsql中使用create table 语句在oracle中建立Asset空表的截图

AssetCategory：

CREATE TABLE "ASSETCATEGORY" 

   ("ASSETCATEGORYNO" NUMBER, 

    "ASSETCATEGORYDESCRIPTION" VARCHAR2(200), 

     CONSTRAINT "PK_ASSETCATEGORY" PRIMARY KEY ("ASSETCATEGORYNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE

   )

在plsql中使用create table 语句在oracle中建立AssetCategory空表的截图

Maintenance：

CREATE TABLE "MAINTENANCE" 

   ("MAINTENANCENO" NUMBER, 

    "MAINTENANCEDATE" DATE, 

    "MAINTENANCEDESCRIPTION" VARCHAR2(200), 

    "MAINTENANCECOST" VARCHAR2(50), 

    "ASSETNO" NUMBER, 

    "EMPLOYEENO" NUMBER, 

    "AGENTNO" NUMBER, 

     CONSTRAINT "PK_MAINTENANCE" PRIMARY KEY ("MAINTENANCENO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "FK_EMPLOYEEM" FOREIGN KEY ("EMPLOYEENO")

     REFERENCES "EMPLOYEE" ("EMPLOYEENO") ENABLE, 

     CONSTRAINT "FK_ASSETM" FOREIGN KEY ("ASSETNO")

     REFERENCES "ASSET" ("ASSETNO") ENABLE, 

     CONSTRAINT "FK_SERVICEAGENTM" FOREIGN KEY ("AGENTNO")

  REFERENCES "SERVICEAGENT" ("AGENTNO") ENABLE

   )

在plsql中使用create table 语句在oracle中建立Maintenance空表的截图

ServiceAgent：

CREATE TABLE "SERVICEAGENT" 

   (    "AGENTNO" NUMBER, 

    "AGENTNAME" VARCHAR2(50), 

    "AGENTSTREET" VARCHAR2(50), 

    "AGENTCITY" VARCHAR2(50), 

    "AGENTSTATE" VARCHAR2(200), 

    "AGENTZIPCODE" VARCHAR2(50), 

    "AGENTTELNO" VARCHAR2(11), 

    "AGENTFAXNO" VARCHAR2(12), 

    "AGENTEMAILADDRESS" VARCHAR2(50), 

    "AGENTWEBADDRESS" VARCHAR2(50), 

    "CONTACTNAME" VARCHAR2(50), 

    "CONTACTTELNO" VARCHAR2(11), 

    "CONTACTFAXNO" VARCHAR2(12), 

    "CONTACTEMAILADDRESS" VARCHAR2(50), 

     CONSTRAINT "PK_SERIVICEAGENT" PRIMARY KEY ("AGENTNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "AGENTNAME" UNIQUE ("AGENTNAME")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "AGENTTELNO" UNIQUE ("AGENTTELNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "AGENTFAXNO" UNIQUE ("AGENTFAXNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE

   )

在plsql中使用create table 语句在oracle中建立ServiceAgent空表的截图

Status：

CREATE TABLE "STATUS" 

   ("STATUSNO" NUMBER, 

    "STATUSDESCRIPTION" VARCHAR2(200), 

     CONSTRAINT "PK_STATUS" PRIMARY KEY ("STATUSNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE

   )

在plsql中使用create table 语句在oracle中建立Status空表的截图

Valuation：

CREATE TABLE "VALUATION" 

   ("VALUATIONNO" NUMBER, 

    "VALUATIONDATE" DATE, 

    "VALUATIONPRICE" VARCHAR2(50), 

    "ASSETNO" NUMBER, 

    "EMPLOYEENO" NUMBER, 

     CONSTRAINT "PK_VALUATION" PRIMARY KEY ("VALUATIONNO")

     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 

     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474835

     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

     TABLESPACE "SYSTEM"  ENABLE, 

     CONSTRAINT "FK_ASSETV" FOREIGN KEY ("ASSETNO")

     REFERENCES "ASSET" ("ASSETNO") ENABLE, 

     CONSTRAINT "FK_EMPLOYEEV" FOREIGN KEY ("EMPLOYEENO")

     REFERENCES "EMPLOYEE" ("EMPLOYEENO") ENABLE

   )    

在plsql中使用create table 语句在oracle中建立Valuation空表的截图

2.2 设计派生数据的表示

如果一个列的值可以从其他列得到，则此列就称为派生列或计算列。例如，下述列都是派生列：

●财产进行维护所花费的总费用。

●财产进行维护的总次数。

从物理数据库设计的角度看，将派生列存储在数据库中还是每当需要时再进行计算，需要进行一下权衡。主要从以下两点考虑：

●存储派生数据的代价以及维护它与派生它数据的一致性的代价。

●每次计算它需要的代价。

根据性能约束来选择一种代价低的方式。对于上面的两个派生列中的第一个来说，应该在Asset表中存储附加的列来标识每个财产目前为止进行维护的总费用。Maintenance表和Asset表有新的派生列。

2.3 设计派生数据的表示

2.4 分析事务

创建了基本表、完整性约束和业务规则后，下一步就是分析事务为每个基本表确定合适的文件组织方式和索引。在逻辑数据库设计方法中标识了事务，为了集中在可能会出现问题的区域，使用如下方法：

1．将所有的事务路径映射到表

    添加外键关联，设置备用键

2．确定最常被访问的表

    最长被访问的表示Asset表、Maintenance表、Valuation表

3．分析涉及到这些表的事务

    可以列出资产的详细信息、维护信息、资产类别、资产状态等的报表

2.5 选择文件组织方式

物理数据库设计的主要目标之一就是以有效方式存储数据。例如，如果想按照姓名以字母顺序检索员工记录，则用员工姓名对文件排序就是很好的文件组织方式。但是，如果想要检索所有所有男性员工，则按照员工姓名排序的文件就不是好的文件组织方式。

一些文件组织方式对成批处理数据进入数据库是很有效率的。

因此，如果目标DBMS允许，这步的目标是为每个表选择最佳的文件组织方式。在许多方面，可能发现关系DBMS基本不允许选择文件组织方式，尽管有些DBMS允许创建索引。

2.6 选择索引

为表选择正确的文件组织方式的一种方法是保持记录的无序性并且创建所需数目的二级索引。另一种方法是通过指定主键或聚簇索引使表中记录为有序的。这种情况下，应该选择如下列来排序后者聚簇索引记录。

●经常用于连接操作的列，因为这样使连接更有效率。

●在表中经常按某列的顺序访问记录的列。

如果选择排序的列是表的键，那么该索引就是主索引；如果排序的列不是键，那么该索引就是聚簇索引。记住每个文件只能有一个主索引或者是一个聚簇索引，而不能两者兼有。

2.7 设计用户视图

create [or replace][force | noforce] view [user.] viewName

(column [,column2]...)

as query

[with check option[Constraint constraint]]

[with read only]

or replace 表示如果同名的视图存在，则使用新视图替代已有的视图。

force 强制创建视图，不考虑基表是否存在，是否具有使用基表数据的权限。

noforce 只有基表存在且具有权限，才可以创建视图。

user    表示创建视图的用户名。

viewName  表示将要在数据库上创建的视图名称。

column  指定视图中的列名。

query 表示生成视图的select语句。

with check option 指定强制检查通过视图修改数据的操作。

constraint  表示指定的约束名称。

with read only： 表示创建的视图只能检索数据，不能修改数据。

2.8 设计访问规则

作为数据库分析阶段的一部分，需要确定用户的类型，也就是谁将使用系统，以及为完成他们指定的任务所必须赋给他们的访问级别。数据库的安全性通常包括系统安全和数据安全。

权限是执行特定类型的SQL语句或访问其他用户的对象的权限。一些权限的例子包括：

●连接到数库

●创建表

●从其他用户的表中查询行

将权限授权给用户后，这些用户就可以完成他们需要的任务。过度授权会降低安全性，因此应该只授权给绝对需要该权限完成工作的用户。

如图所示在plsql中用图形化界面给用户sjjm用户进行授权：