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

1、资产管理数据库设计报告班级：xxxxxxx姓名：xxx学号：xxxxxxxx数据库设计资产管理31逻辑数据库设计31.1 资产管理31.2使用逻辑数据库设计方法52物理数据库设计131.1设计基本表132.2 设计派生数据的表示212.3 设计派生数据的表示212.4 分析事务222.5 选择文件组织方式222.6 选择索引222.7 设计用户视图232.8 设计访问规则23数据库设计资产管理1 逻辑数据库设计1.1 资产管理目前能够从事资产管理业务的公司除了证券公司、基金公司、信托公司以外还有第三方理财公司，从某种意义上来说，第三方理财公司在资产管理市场上的拓展和定位有些类似于现如今的私募基

2、金，将专家理财和灵活的合作条款捆绑嫁接作为打开资产管理市场的突破口。像诺亚财富，利得财富都是这样的第三方理财公司。1.1.1 数据需求1.员工员工中每个成员的详细信息包括员工号、姓名、地址（街区、城市、州、邮编）、工作电话号码、家庭电话号码、传真号、职位、性别、薪水、入职日期。在整个业务中，员工号是唯一的。员工包括经理、主管、秘书、普通员工。2.资产表资产表详细信息表包括资产编号、资产描述、资产序列号、到手日期、进货价格、当前价格、注册日期、下一次维护时间、员工编号、资产种类编号、状态编号。其中资产编号和资产序列号是（唯一的）主键，员工编号、资产种类编号、状态编号都是外键与员工表、资产种类表、

3、状态表进行外键关联。3.资产种类表资产种类表详细信息包括资产种类编号、资产种类描述。其中资产种类编号是（唯一的）主键。4.维护表维护表详细信息表包括维护编号、维护日期、维护时的描述、维护费用、资产编号、员工编号、代理编号。其中维护编号是（唯一的）主键，资产编号、员工编号、代理编号都是外键与资产表、员工表、代理表进行外键关联。5.服务代理表服务代理表详细信息包括代理编号、代理名字、代理街、代理城市、代理情形、代理邮编、代理电话号、代理传真号、代理邮箱地址、代理网站地址、联系人姓名、联系人电话号、联系人传真号、联系人邮箱地址。其中代理编号是（唯一的）主键。其中代理名字、代理电话号、代理传真都是唯一

4、的，是代理表的替换键，替换键可以是数据表内不作为主键的其他任何列，只要该键对该数据表唯一即可。换句话说，在唯一列内不允许出现数据重复的现象。6资产状态表资产状态表详细信息包括状态编号、状态描述。其中状态编号为（唯一的）主键。7.估价表估价表的详细信息包括估价编号、估价日期、评估价格、产品编号、员工编号。其中估价编号是（唯一的）主键，资产编号和员工编号是外键与资产表和员工表进行外键关联1.1.2 事务需求1.数据库应该支持下述事务(a) 创建和维护记录关于资产种类、资产状态和资产的详细信息。(b) 创建和维护记录代理的详细信息和记录。(c) 创建和维护维护过程详细信息以及维护详细花费的记录。(d

5、) 创建和维护估价的详细信息和该产品的现有价值。(e) 创建和维护员工的详细信息。2.数据应该能够支持下述查询事务(a) 以表单形式列出资产的名字、资产的进货价格、每个资产的编号，按资产当前价格进行排序。(b) 以表单形式列出员工的姓名、电话号，以及他们的家庭住址的详细信息。(c) 列出对某一给定的资产进行评估的详细信息。(d) 可以列出某个员工管理多少资产的详细信息。(e) 以报表形式列出到某一资产的所有详细信息。1.2使用逻辑数据库设计方法1.2.1 步骤1.1: 标识实体逻辑数据库设计的第一个步骤是标识在数据库中必须表述的主实体。有上面的描述，可以标识如下实体：employee（员工）a

6、sset（资产）assetcategory（资产种类）maintenance staff（维护工）serviceagent（服务代理）（资产状态）valuation staff（估价人员）将实体存档实体名描述别名事件employee公司员工员工asset公司拥有的财产（如打印机、汽车、桌子、椅子等）财富每个资产分配各一个员工assetcategory资产分类财富类别辨别资产之间的不同maintenance staff维护工 修理工负责维修公司所拥有的资产serviceagent帮助维护资源的公司维护公司公司有一个或者多个服务代理status资产的状态 状态表示公司所拥有的资产状态valuati

7、on staff评估资产当前价值 估价评估资产当前价格1.2.2 步骤1.2：标识关系标识完实体后，下一步就是标识存在于这些实体间的所有关系。对于资产管理的实体关系，如图所示。实体关系实体employeeassignedassetassetmaintainedbyvaluedbymaintenancevaluationassetcategorycontainsassetmaintenance staffcarriesoutmaintenanceserviceagentperformsvaluationstatusforassetvaluation staffforasset图11. 确定关系的

8、多样性约束标识完要创建的关系后，现在应该确定每个关系的多样性约束。实体多样性关系实体多样性employeeleaderhasassignedemployeemaintenanceasset1.*1.*0.*asset1.11.1containcontainmaintenancevaluation1.*1.*assetcategory1.1containasset1.*maintenance staff0.1carriesout maintenance0.*serviceagent0.1performsmaintenance0.*status1.1forasset1.*valu

9、ation staff1.1forasset1.*图22. 使用实体-关系（er）建模在数据库设计阶段，将创建几个代表资产管理的er模型。其中员工有自身的一对多的关系，属于公司员工，与资产、估价、维护进行外键关联。其中资产具有资产种类、资产状态。其中维护具有代理员工。故有如下er图。图31.2.3 步骤1.3：标识实体或关系的有关属性下一个步骤是标识与已经标识的实体或关系有关的属性。对于资产管理而言，应该标识如图所示的与实体有关的属性。实体属性employeeemployeeno、name、position、salaryserviceagentagentno、agentname、agentst

10、reet、agentcity、agentstate、agentzipcode、agenttelno、agentfaxno、agentemailaddress、agentwebaddress、contactname、contacttelno、contactfaxno、contactemailaddressassetcategoryassetcategoryno、assetcategorydescriptionstatusstatusno,statudescriptionassetassetno、assetdescription、serialno、dataacquired、purchasepric

11、e、currentvalue、datesold、nextmaintenancedate、employeeno、assetcategoryno、statusnomaintenancemaintenanceno、maintenancedate、maintenancedescription、maintenancecost、assetno、employeeno、agentnovaluationvaluationno、valuationdate、valuationprice、assetno、employeeno图41.2.4 步骤1.4：确定属性域1. 资产编号的属性域是一个number类型的数字2.

12、资产描述的属性域是一个最多为200位字符的varchar2类型的字符串3. 资产序列号的属性域是一个最多为50位字符的varchar2类型的字符串4. 资产种类编号的属性域是一个number类型的数字5. 资产种类描述的属性域是不字长的定长字符串6. 资产状态编号的属性域是一个number类型的数字7. 资产状态描述的属性域是一个最多为200位字符的varchar2类型的字符串8. 代理编号的属性域是一个number类型的数字9. 代理姓名的属性域是一个最多为50位字符的varchar2类型的字符串10. 代理所在地区的属性域是一个最多为50位字符的varchar2类型的字符串11. 代理处理

13、情形的属性域是一个最多为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-ma

14、il地址是以注册网站域名.com结尾的50位字符的varchar2类型的字符串19. 员工编号的属性域是一个number类型的数字20. 员工名字的属性域是一个最多为50位字符的varchar2类型的字符串1.2.5 步骤1.5：确定候选键、主键和备用键属性这个步骤主要是为实体标识候选键，然后选择其中之一作为主键。在标识主键的过程中，要特别注意实体是强实体还是弱实体。其中的asset离开了assetcategory将不存在，故assetcategory依赖asset为弱实体；employee离开 asset还是存在的实体，故employee不依赖于asset为强实体；maintenance为强

15、实体。但是assetcategory、valuation都是依赖于asset而存在的，故三者均为弱实体。在标识候选键时，应该注意到各个实体的主键及环境的考虑。如图所示。1.2.6 步骤1.6：特化和泛化实体1.2.7 步骤1.7：检查模型的数据冗余1. 重新检查一对一关系。其中资产实体中的信息已经包含在资产信息表中，故资产实体的存在是多余的。应该删掉资产实体。2. 删除冗余关系。1.2.8 步骤1.8：检查模型是否支持用户事务在这个步骤中，检查已经创建的局部逻辑数据模型是否支持用户所需的事务。检查包括：1.数据模型中是否存在必要的属性。2.如果属性要从多个实体中得到，则两个实体间是否有通路；换

16、而言之，在两个实体间要有已经标识了的关系。如图所示：1.2.9 步骤2.1：创建表在这个步骤中，从逻辑数据模型创建表达用户视图中所描述的实体和关系的表，这时，要为关系数据库使用数据库设计语言（ddl）。将从逻辑数据模型创建表的全部结构都存档。employee(employeeno、name、position、salary、branchno)primary key employeenoassetcategory(assetcategoryno、assetcategorydescription)primary key assetcategorynostatus(statusno,statudesc

17、ription)primary key statusnovaluation(valuationno、valuationdate、valuationprice、assetno、employeeno)primary key valuationnoforeign key assetno references asset(assetno)foreign key employeeno references employee(employeeno)serviceagent（agentno、agentname、agentstreet、agentcity、agentstate、agentzipcode、age

18、nttelno、agentfaxno、agentemailaddress、agentwebaddress、contactname、contacttelno、contactfaxno、contactemailaddress）primary key agentnoalternate key agentnamealternate key agentelnoalternate key agentfaxnoasset(assetno、assetdescription、serialno、dataacquired、purchaseprice、currentvalue、datesold、nextmainten

19、ancedate、employeeno、assetcategoryno、statusno)primary key assetnoalternate key serialnoforeign key employeeno references employee(employeeno)foreign key assetcategoryno references assetcategory(assetcategoryno)foreign key statusno references status(statusno)1.3.0 步骤2.2：用规范化方法检查表结构在这个步骤中，要确保上一步所建的表至少要

20、满足第三范式（3nf）。如果满足不了第三范式，则可能是逻辑数据模型中的某些部分是错误的，或者是从模型产生表的时候产生了错误。1.3.1 步骤2.3：检查模型是否支持用户事务这个步骤与步骤1.8类似，在这个步骤中，除了要检查从实体到表的映射关系并且要确定外键之外，在这个情景下，还可以再次检查从实体到表的映射是否正确地完成，以及所创建的表是否支持标识的用户事务。1.3.2 步骤2.4：检查业务规则业务规则是为了防止数据库不一致而强加的约束。六种完整性约束中，有四种在上一步已经标识了，并且存档在数据字典中。这四种是：需要的数据、属性域的约束、实体完整性和多样性。剩下两种为：参照完整性和其他业务规则。

21、1.参照完整性考虑两点：（1）标识外键是否可以为空（null）。通常，如果关系中子表部分是强制的，那么就不允许为空。如果子表部分是可选的，那么就允许为空。（2）标识现有约束条件，表明外键的插入、更新或者产出情况。通常，要为每个外键明确说明两个动作：一个是on update动作，另一个是on delete动作，用于表明当在父表中更新或删除一条记录时，如何保证参照完整性。如图所示：maintenancenceforeign key assetno references asset(assetno) on update cascade on delete no actionforeign key e

22、mployeeno references employee(employeeno) on update cascade on delete no actionforeign key agentno references serviceagent(agentno) on update cascade on delete no actionassetforeign key employeeno references employee(employeeno) on update cascade on delete no actionforeign key assetcategoryno refere

23、nces assetcategory(assetcategoryno) on update cascade on delete no actionforeign key statusno references status(statusno) on update cascade on delete no actionvaluationforeign key assetno references asset(assetno) on update cascade on delete no actionforeign key employeeno references employee(employ

24、eeno) on update cascade on delete no action2物理数据库设计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 va

25、rchar2(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 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace sys

26、tem 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, assetcateg

27、oryno 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 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace system enable, constraint serialno

28、 unique (serialno) using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace system enable, constraint fk_employee foreign key (employeeno) references employee (empl

29、oyeeno) 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 (assetcat

30、egoryno 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 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tabl

31、espace 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

32、 primary key (maintenanceno) using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace system enable, constraint fk_employeem foreign key (employeeno) references emp

33、loyee (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 numbe

34、r, 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), conta

35、ctfaxno 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 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace s

36、ystem enable, constraint agentname unique (agentname) using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace system enable, constraint agenttelno unique (agenttel

37、no) using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 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

38、 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 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), constrain

39、t pk_status primary key (statusno) using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace system enable )在plsql中使用create table 语句在oracle中建立status空表的截图valuation：cr

40、eate 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 2147483645 pctincr

41、ease 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 设计派生数据的表示如果一个列的值可以从其他列得到，则此列就称为派生列或计算列。例如，下述列都是派生列：财产进行维护所花费的总费用。财产进行维护的总次数。从物理数据库设计的角度看，将派生列存储在数据库中还是每当需要时再进行计算，需要进行一下权衡。主要从以下两点考虑：存储派生数据的代价以及维护它与派生它数据的一致性的代价。每次计算它需要的代价。根据性能约束来选择一种代价低的方式。对于上面的两个派生列中的第一个来说，应该在ass