Database Design with PowerDesigner

Data Model

Data model is the basic idea in database domain.Data model is a collection which describes the tools such as data,data's relationships,data semantics and consistecy constraints.
Data model can be divided into four types:Relational Model,Entity-Relationship Model,Object-based Data Model,Semi-structured Data Model,Semi-structured Data Model(XML).

ER Data Model

Entity

Everything in the world can be represented by a group of entities and their relationships.

Entity is an object or an event.Entity can be a real thing or an abstract thing such as loan.Each entity has one or more properties.These entities which have the same properties are called entity set.
The attribute of entity can be simple or composite.Composite attribute consists of some other attributes(either simple or composite attributes).According to the amount of values,attribute can be divided into single-value attribute and multivalued attribute.For example,employee's phone attribute is a multivalued attribute,because one employee can has several phone numbers.If the value of attribute can be figured by the other attribute,it is called derived attribute.
PowerDesigner doesn't support multi-valuable attribute and composite attribute,we have to upgrade them to entity manually.Thus when we design the attribute with PowerDesigner,attribute must be designed as a simple attribute.

Relationship And Association

We call the relation among entities as relationship.The collection of relationships is named as relationship set .Usually each entity that takes part in relationship acts as a role.For example,the relationship between employee and manager is work-for,employee acts as subordinate and manager acts as boss. One entity can take part in the relationship set several times.In this case,the role of each entity is important.A manager can act as both a boss of one employee and the subordinate of CEO.

Relationship can has an attribute,it is called as Association in PowerDesigner.If you want to use Association,you need to set Model Settings. Select Tools > Model Options or right-click the diagram background and select Model Options from the contextual menu.I use 'E/R+Merise' option here.


We name the amount of entities which take part in one relationship as the degree of this relationship set.If the degree is 2,it is a binary relationship set.
Sometimes,we need to decide to select entity set or relationship set.Loan can be designed to an individual entity or a relation between bank and customers.As a principle,when we want to represent the action between entities,we'd better to use relationship set.

Constraint

Constraint asks the data stored in database must meet some preconditions.There are two constraints we often use.One is mapping cardinality,another is participation constraint.
Mapping cardinality express the number of entities to which other entity can be associated via a relationship. It can be of the following types:
1. One to one: An entity in A is associated with at most one entity in B, an entity in B is associated with at most one entity in A.Note,PowerDesigner allows us to set the Dominant role,it can effect that which relation should contain another entity's PK in PDM.
2. One to many: An entity in A is associated with any number(zero or more) of entities in B.An entity in B however can be associated with at most one entity in A.
3. Many to one: An entity in A is associated with at most one entity in B.An entity in B however can be associated with any number(zero or more) of entities in A.
4. Many to many: An entity in A is associated with any number(zero or more) of entities in B & an entity in B is associated with any number(zero or more) of entities in A.

Participation constraint
If all entities in one entity set participate in one relationship set named as R,it's considered as total participation,otherwise it's considered as partial participation.

There is still more complex constraint, it represents the times that each entity can participates in relationship.The side between entity and relationship has minimal and maximal mapping cardinalities. it can be expressed in m..n .M represents minimal cardinality,and n represents maximal cardinality.Warning，we should take care of it in two directions. First we observe it from A->B,then observe it from B->A.

The following figure shows us how to configure the constraints in PowerDesigner.

User and Employee are both entities.One stores the information of system user,and another stores the employee's personal information.
1）I set the basic mapping cardinality in the top group of radio boxes.
2）Dominant role：Relationship has two directions,PowerDesigner let us indicate which direction is dominant.In this case,the selection is disable.If it's enable,the primary key in dominant role will be added in the opposite one after CDM is converted into PDM.
3) Role name：I ignore it.
4) These two Cardinality select items let you configure the times that each entity can participates in relationship.A user can be one employee or not. An employee can has no user account or several user accounts.

Referential integrity

we can use 'foreign key' in creat table statment.It is important to note that foreign key's can be null.

The Key of Entity

如果一个实体集中包含了多个实体，那么我们如何区分每个实体呢？显然，两个实体的属性不能完全相等，否则就没有办法区分开来。

Super Key是一组属性，它们保证实体集的任意两个实体的super key的值不会完全相同。
由于Super Key里面可以包含一些无关紧要的属性，所以我们更多的时候使用Candidate Key，Candidate Key首先是 Super Key，同时也是Super Key的一个子集，Candidate Key中去掉任何一个属性的话，都不能再是Super Key，这样我们就去掉了Super Key的多余的属性。
由于一个Super Key里面可能包含几个Candidate Key,因此我们在设计数据库的时候会选择其中的一个作为Primary Key。应该选择那些极少变化的Candidate Key作为Primary Key。

The Key of Association

联系集同样也需要将多个联系区分开来。如果联系没有属性，那么参与该联系的所有实体的PrimaryKey的集合就描述了该联系。如果联系有自己的属性，那么就和所有实体的PrimaryKey一起描述了该属性。因此，不管联系有没有自己的属性，参与该联系的所有实体的 PrimaryKey的集合就是该联系集的SuperKey。
联系的PrimaryKey依赖于联系的映射基数。如果两个实体A、B参与联系R，多对多时，联系的PrimaryKey由两个实体的 PrimaryKey组成；一对一时，A或者B的PrimaryKey可以作为R的PrimaryKey；一对多时，B的PrimaryKey作为R的 PrimayKey；多对一时，A的PrimaryKey作为R的PrimayKey。
对于非二元联系，在没有度的限制下，所有参与实体的PrimaryKey构成联系的PrimaryKey。

Extended Characteristics of ER

How to enable inheritance relationship button on CDM

Click "Tools"->"Model Options" menu,and show the following dialog:

Set Notation's value to E/R+Merise。

Inheritance

Like Object Oriented Design,if some entities have 'Is A' relationships to one entity,we can use inheritance relationship in ER diagram. In the example below, the Account entity represents all the bank accounts in the information system. There are two subtypes: checking accounts and savings accounts.

Right click on the inheritance icon(the semicircle icon) and select properties menu,the properties window will show.In this window,we can add/remove childrens,and prevent PowerDesigner from generating parent model in PDM.

Design Issues

1）不要将实体集的PrimaryKey作为联系集的属性
2）两个实体集的关系应该用联系表示，而不是将一个实体集的PrimaryKey作为另一个实体集的属性。联系可以明确的表示出实体集的关系。
3）如果实体的属性较为复杂，可以考虑将该属性升级为实体。比如电话号码。
4）当描述发生在实体集之间的行为时采用联系集。
5）非二元联系总是可以使用一组二元联系来替代。在PowerDesigner的帮助下，我们可以直接设计多元联系，当生成PDM的时候，会自动转换成对应的关系模型。

解决PowerDesigner里允许字段重名约束的设置问题

The solution is as follows:http://www.cnblogs.com/justinw/archive/2006/05/10/396709.html

举例如下：

我使用PowerDesigner12.5来管理我的数据库设计。这个软件很方便，可以创建ER模型，然后根据ER模型自动生成关系模型，根据关系模型自动创建数据库（需要配置odbc的dsn)。可惜Linux版本没有。先建立一个workspace，名字为OA，新建一个Conceptual Data Model（用来设计ER模型）OA，在里面添加一个内嵌文件，文件名为数据库描述.txt。该文件的内容就是我们对系统要存储信息的描述：（一个清晰的描述文档对解释数据库的设计由来非常重要，该文档重点解释了系统存储的信息是什么，不要使用数据库相关术语）

关于PowerDesigner的使用，可以参考： http://hi.baidu.com/shiytower/blog/item/39dbab7b10c82ef00bd18777.html

一个IT公司有员工，部门和项目组：
1）系统要存放员工的信息（工号，姓名，出生年月，性别，学历，职务，部门，电话，email,家庭住址，户口，亲属联系方式等）
同时还要存放key和数据块---为了提高安全性，系统不存放密码，只存放key和数据块。三者的关系是key+密码经过加密算法产生数据块。如果以后用户要登陆，必须提供用户名和密码，系统根据用户名查找到对应的key，然后用同样的加密算法计算出数据块，然后和保存的数据块进行比对，如果符合，则登陆成功。不符合则密码不正确。如果用户忘记了密码，只能申请管理员帮助提供默认密码，登陆后自己再修改密码。

2）系统也要存放部门信息（部门名称，部门负责人，部门描述）。

3）员工和部门之间要建立联系。一个员工只能属于一个部门，一个部门可以有多个员工。

4）员工之间也有联系，有上下级关系。一个员工可以有几个上级（参与不同的项目），一个员工也可以有多个下级。

5）员工分为若干等级－－PG(项目程序员）,PL（项目小组长）,PM（项目经理）,TB（担当部长）,B（部长），HB（本部部长），CEO等。（按照日本公司的习惯举例）

6）系统要管理项目信息（项目名称，项目负责人，项目描述，项目开始时间，项目结束时间）

7）员工和项目组的联系类似于员工和部门的联系

8）项目组可以细分为Team。每个Team都有PL，也有开始时间和结束时间，还有职责描述和TeamID以及名称。

9）员工日报记录员工每天参与了那些项目，每个项目工作时间从几点到几点，以及相对应的工作内容。(注意，员工日报原来作为员工的一个复杂属性，上升为一个实体）
这是一个简单的例子，我们看看如何用上面的规则来设计一个好的E－R模型。这段描述有点像面向对象分析开始时使用的描述，但是不同之处在于这里的描述关注系统要管理的信息，而面向对象系统分析的描述关注的是系统要完成的业务。因此我们不必在这里描述业务关系。

下图展示了PowerDesigner的画面：

Employee实体代表员工,有如下属性

Property	Type	Restriction
WorkID	String	PK,0<10
Name	String	not null,0<20
Birthday	DateTime
Gender	Char	只能是这三种字Male（男）,Female（女），X(代表两性人）,not null
EducationBackground	String	length<200
Level	String,多值属性	legnth<50,not null,只能从PG,PL,PM,TB,B,HB,CEO中取值
Telephone	多值属性
Email	多值属性
HomeAddress	String	length<200
RegisteredPermanentResidence	String	legnth<50

由于Level,Telephone和Email属性为多值属性，将它们升级为实体。修改后如下：
Level实体

Property	Type	Restriction
Level	String	PK,只能从PG,PL,PM,TB,B,HB,CEO中取值

Empolyee实体

Property	Type	Restriction
WorkID	String	PK,length<10
Name	String	not null,length<20 8）项目组可以细分为Team。每个Team都有PL，也有开始时间和结束时间，还有职责描述和TeamID以及名称。
Birthday	Date
Gender	Char	只能是这三种字Male（男）,Female（女），X(代表两性人）,not null
EducationBackground	String	length<200
HomeAddress	String	length<200
RegisteredPermanentResidence	String	legnth<50

EmpolyeeTelephone实体

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体WorkID所有值之一
Telephone	String	PK,0<20
Type	String	not null,值为Home,Mobile,Bussiness所有值之一

EmpolyeeEmail实体

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体WorkID所有值之一
Email	String	PK,0<20
Type	String	not null,值为Home,Business所有值之一

DailyReport实体

Property	Type	Restriction
WorkID	String	PK,length<10,派生属性，值为Empolyee实体WorkID所有值之一
Date	String	PK
ProjectID	String	PK,派生属性，值为Project实体ProjectID所有值之一
StartTime	DateTime	not null
FinishTime	DateTime	not null
Description	String	not null

Branch实体：

Property	Type	Restriction
BranchID	String	PK,0<10，B字母开头
Name	String	not null,0<20
ManagerID	String	派生属性，值为Empolyee实体的ProjectWorkID所有值之一
Description	String	0<400删除异常的情况，读者可自行分析得知。

Project实体

Property	Type	Restriction
ProjectID	String	PK,0<10，P字母开头
Name	String	not null,0<20
ManagerID	String	派生属性，值为Empolyee实体的WorkID所有值之一
Description	String	0<400
StartTime	DateTime
FinishTime	DateTime

ProjectTeam实体

Property	Type	Restriction
TeamID	String	PK,0<10，T字母开头
Name	String	not null,0<20
Leader	String	派生属性，值为Empolyee实体的WorkID所有值之一
Description	String	0<400
StartTime	DateTime
FinishTime	DateTime

电子邮件四个属性

现在我们考虑一下实体间的联系。由于我使用的Google Document功能比较简单，这里就不绘制联系符号。但是可以用文字和表格归纳一下：
Level和Employee的联系是授予，是多对多的，一个员工可以有多个职位，一个职位可以授予多个员工。
Employee和EmployeeEmail的联系是一对多的。Employee和EmployeeTelephone的联系也是一对多的。


员工和员工的联系 WorkFor---
A 员工 work for B 员工。多对多关系。该联系属性如下：

	Property	Type	Restriction
	WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
	BossID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
	StartTime	DateTime
	FinishTime	DateTime

员工和部门的联系BelongTo---
A 员工 属于 某个部门。多对一关系。
该联系属性如下：

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
BranchID	String	派生属性，值为Branch实体的BranchID所有值之一
StartTime	DateTime
FinishTime	DateTime

员工和项目的联系SiteIn---
A 员工在B项目组工作。多对多关系。

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
ProjectID	String	PK,派生属性，值为Project实体的ProjectID所有值之一
StartTime	DateTime
FinishTime	DateTime

员工和Team的联系Service,多对多关系。

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
TeamID	String	PK,派生属性，值为ProjectTeam实体的TeamID所有值之一
StartTime	DateTime
FinishTime	DateTime

ProjectTeam属于Project。

PowerDeisgner似乎不支持派生属性，因此都要转为联系来表示。

E-R模型映射到关系模型（本节未完成）

PowerDesigner可以自动将CDM转换成PDM，也就是依据ER模型生成关系模型。但是我们先有必要理解一下关系模型的概念。
关系模型是主要的数据库模型。关系模型的属性就是表的字段（列首）。每个属性的值域称为该属性的域。关系（relation)就是表，元组(tuple)就是行。关系的每个属性的域必须是原子的，不可再分的。这和前面的ER模型中的简单属性是一个意思。


由于我们的实体已经只包含简单属性，所以每个实体可以转换为一个关系模型。关系模型中的每个字段都使用实体的约束。注意，有些字段要设成外键，因为它们依赖被引用关系的某些字段。比如EmpolyeeTelephone实体的WorkID字段。注意，这个WorkID既是主键也是外键。
这里的联系也转换为关系模型。

E-R模型转换为关系数据模型的方法

1.实体集合转换成关系。实体集合的属性作为关系集合的属性。实体集合的关键字作为关系集合的关键字

2.联系转换为关系。所有参加联系的实体集合的关键字作为关系的属性，关系的关键字由联系类型决定

3.合并关系，把具有共同关键字的关系合并，属性并作为新关系的属性

4.不能表示"is a"关系。处理办法：在实体的集合增加属性来表示他们的层次关系。

E-R模型向关系模型转换的原则

1.将每个实体转换为一个关系

2.所有主码必须定义为非空

3.一个1:1的联系可以转换位一个独立的关系也可以与任意一端对应的关系合并

4.一个1:n的联系可以转换为一个独立的关系，也可以与n端对应的关系合并

5.一个m:n的联系转换为一个关系

6.三个或三个以上实体间的一个多元联系可以转换为一个关系

7.将超类和子类分别转换为一个关系，超类转换的关系为父表，子类转换的关系为子表，然后将父表的主码作为子表的外码，实现父表与子表的联系

运用范式检查关系模型

第一范式（1NF）：关系中的字段都是原子的，不可再分。

经过第一范式检查，我发现 Empolyee 实体的FamilyContact字段不是原子的，很明显是我在做E-R模型时遗漏了。该字段可以分成亲属姓名，关系，电话,email四个属性。电话和 email又是复合属性，但是由于出了电话和email以外的其他字段都是主键，因此电话和email不再单独建立实体。
因此我们创建下面的实体：
EmpolyeeFamily实体

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
FamilyName	String	PK,0<20
Relation	String	PK,0<20
Telephone	String	0<20
Email	String	0<20PK,派生属性，值为Empolyee实体的WorkID所有值之一

Empolyee实体删除FamilyContact属性。

Property	Type	Restriction
WorkID	String	PK,派生属性，值为Empolyee实体的WorkID所有值之一
Name	String	not null,0<20 8）项目组可以细分为Team。每个Team都有PL，也有开始时间和结束时间，还有职责描述和TeamID以及名称。
Birthday	DateTime
Gender	Char	只能是这三种字M（男）,W（女），X(代表两性人）,not null
EducationBackground	String	length<200
Level	String	legnth<50,not null,只能是PG,PL,PM,TB,B,HB,CEO之一
BranchID	String	not null,0<10，派生属性，值为Branch实体的BranchID的所有值之一
HomeAddress	String	length<200
RegisteredPermanentResidence	String	length<50

第二范式（2NF）：关系中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

检查所有PK数目不止一个的关系，考虑是否可能这些PK的某个子集可以单独决定该关系的其他非PK字段。如果有，则违反第二范式。违反第二范式会造成数据冗余，更新异常，插入异常和删除异常。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的关系应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y

这里没有不符合第三范式的关系模型。满足范式要求的数据库设计是结构清晰的，同时可避免数据冗余和操作异常。这并意味着不符合范式要求的设计一定是错误的，在E-R模型中存在1：1或1：N关系这种较特殊的情况下，合并导致的不符合范式要求反而是合理的。（这里还有些疑问）

具体设计技巧：

1）由于powerdesigner有一些问题，所以每次重新设计数据库后生成数据库时要先把mysql中的数据库删除，再建立

2）一般来讲，Entity的primary key应该根据逻辑设计，不一定非要都制定一个ID属性作为PK，但是在mysql运用过程中，如果有些pk长度较大，会导致索引长度溢出错误，这是mysql的bug，这种情况下，只能用ID来作为pk，程序员自己需要保证真正的pk的唯一性。