数据库之逻辑设计阶段(候选码、主码、外码、范式…)
1.总览数据库的生命周期
1.1 需求分析阶段
分析用户需求,是整个数据库设计的基础。
阶段产出:
①分析用户活动,产生业务流程图。
②确定系统范围,产生系统关联图。
③分析用户活动涉及的数据,产生数据流图。
④分析系统数据,产生数据字典。数据字典包括数据项、数据结构、数据流、数据存储和处理过程5个部分。
1.2 概念设计阶段
通过对用户需求的集成、归纳和抽象,形成一个独立于数据库管理系统的概念模型。
阶段产出:
ER实体模型
1.3 逻辑设计阶段
将概念结构转换为DBMS支持的数据模型,将ER模型转换为关系模型。
阶段产出:
关系模型
1.4 物理设计阶段
为关系模型选择最适合应用程序环境的物理结构(包括存储结构和存取方法)。
阶段产出:
包括存储结构和存取方法的物理结构
1.5 数据库实现阶段
根据逻辑设计和物理设计的阶段产出,使用数据库管理系统提供的数据语言、工具和主机语言,建立数据库,编写调试应用程序,组织数据仓库,并进行试运行。
1.6 数据库运营与维护
数据库应用系统经过试运行后即可投入正式运行。
在数据库系统运行过程中必须不断地对其进行评价、调整与修改。
2.逻辑设计阶段
2.1 认识各种键\码
首先得明白一点,键即是码,如主键=主码,外键=外码等等,因此以下都称为码。
①候选码:能够唯一标识一条记录的最小属性集合,注意最小最小最小,并且一张表中候选码不止一个。
②全码:当表中所有的属性共同构成一个候选码时,这时称该候选码为全码。
③主码:从若干个候选码中选择一个为主码,随你喜好,但是最好符合人类阅读习惯。
④外码:将本表(表1)中的某属性与另一张表(表2)中的主码关联在一起,则该属性称为外码。并且要注意,在为表1添加外码项数据时,添加的属性值必须是表2主码中属性值里有的值。
2.2 将ER实体图中每个强实体、弱实体的属性分列
分列过程是为了使表逐一符合第一范式、第二范式、第三范式、BC范式、第四范式、第五范式。
一般数据库设计只需符合第三范式就足够了。
①第一范式(1NF)要求:关系模型表中每列都是最基本的数据项,不可再分,每列中不能出现两个值。
②第二范式(2NF)要求:设置主码,关系模型中不存在非主属性对主码的部分依赖。
问1:什么是非主属性?
答1:非主属性是相对于主属性来定义的,是指该关系中不包含在任何一个候选码中的属性。
问2:什么是部分依赖?
答2:若关系中主码为(a,b),存在非主属性c,函数依赖集中存在b一>c或a—>c,就称c部分依赖于(a,b)。
③第三范式(3NF)要求:关系模型中不存在非主属性对主码的传递依赖。
问1:什么是传递依赖?
答1:若关系中主码为(a,b),存在非主属性c、d,函数依赖集中存在(a,b)—>c与c一>d,就称d传递依赖于(a,b)。
④BC范式(BCNF)要求:关系模型中不存在任何属性(包括主属性)对主码的传递依赖与部分依赖。
⑤第四、第五范式不介绍,将关系模型分太多列属性反而会降低向数据库中添加\删除数据的效率。
2.3 例题
下表捕获了有关电子商务书店的以下事实:名为EmpName、ID为EmpID的员工已在ShippedDate日期将订单(订单号为OrderNo)发送到地址ShipToAddr。装运的跟踪号为TrackingNum。TrackingNum由提货的快递公司提供。书店只使用一家快递公司。请注意,单个订单可以根据所订购项目的可用性分为多个装运。只有一名员工处理一批货物。但是,如果订单分多次发货,则多个员工可以处理订单。
(以上为翻译,原题:The following table captures the following fact about an E-Commerce bookstore: the employee whose name is EmpName
and whose ID is EmpID
has shipped the order (whose Order Number is OrderNo
) to the address ShipToAddr
on the date ShippedDate
. The tracking number for the shipment is TrackingNum
. The TrackingNum
is provided by the courier company that picks up the shipment. The bookstore uses only one courier company. Note that a single order could be split up into multiple shipments based on the availability of the ordered items. Only one employee handles a shipment. However, multiple employees could handle an order if the order is shipped in multiple shipments.)
1.列出主键。
2.列出所有FD。
3.列出所有更新异常并提供每个异常的示例。
4.这种关系的范式是什么?解释一下。
5.逐步对其应用规范化,使关系达到3NF。也就是说,如果关系是非规范化的,则将其转换为第一个范式,然后将刚刚创建的第一个范式转换为第二个范式,然后将第二个范式转换为第三个范式。
答:
1.列出主键:TrackingNum.
2.列出所有FD.。
EmpID->EmpName
OrderNo->ShipToAddr,ShippedDate
TrackingNum->EmpID,OrderNo
3.列出所有更新异常并提供每个异常的示例。
插入异常:在插入新的订单数据时还必须插入员工的数据(如插入订单223信息时还需插入ID为1234,名为Joe的员工信息)
删除异常:在删除订单224时还必须删除ID为2134,名为Jones的员工信息。
更新异常:用户在更改订单223的地址时还需要更新224的地址。
4.这种关系的范式是什么?解释一下。
符合第二范式
数据库表的每一列(即每个属性)都是不可分割的基本数据项,同一列中没有多个值,即实体中的某个属性没有多个值。故符合第一范式。
数据库表中的属性不存在非主属性对主码的部分依赖,故符合第二范式。
数据库表中的关系还存在非主属性对主码的传递依赖,不符合第三范式。
综上,表处于第二范式阶段。
5.逐步对其应用规范化,使关系达到3NF。也就是说,如果关系是非规范化的,则将其转换为第一个范式,然后将刚刚创建的第一个范式转换为第二个范式,然后将第二个范式转换为第三个范式。
依据现有的FDs将表拆分成三个表
1.Employee(EmpID,EmpName)
primary key:EmpID
FDs:EmpID->EmpName
2.Order(OrderNo,ShipToAddr,ShippedDate)
primary key:OrderNo
FDs:OrderNo->ShipToAddr
OrderNo->ShippedDate
3.Shipment(TrackingNum,EmpID,OrderNo)
primary key:TrackingNum
FDs:TrrackingNum->OrderNo
TrackingNum->EmpID
以上三个关系模式中都没有非主属性对主码的传递依赖,故符合第三范式。