1、高级数据库开发技术第2章 Data Modeling Theory2 从本章开始介绍数据建模,分四个专题:建模的理论(Data Modeling Theory)业务需求(Business Requirements)创建逻辑模型(Creating the Logical Model)创建物理模型(Creating the Physical Model)3References A Developers Guide to Data Modeling for SQL Serverby Eric Johnson&Joshua jones (SQL Server 数据建模开发者指南)4Contents 介
2、绍数据建模要用到的理论基础:概述(为什么正确的数据模型如此重要,数据建模过程)逻辑数据模型中用到的元素 数据模型中的物理元素 数据模型的规范化5数据建模概况 数据建模是指出怎样把数字化的信息存入数据库的过程。（data modeling is the process of figuring out how to store digitized information in a logically structured computer database.）建立正确的模型有很多工作要做。包括理解并映射业务信息到能存入数据库的逻辑对象上。要求建模人员具有多种能力。不仅是能理解建模过程，还得做数据侦
3、探，要善于提问并找出对客户真正有用的内容。要找出客户怎样使用信息。怎样把逻辑模型转换成物理数据库。6必须知道的概念 数据库 关系数据库管理系统7为什么正确的数据模型那么重要?数据建模非常的费时，事实上，对于一个应用项目，它是最费时间的部分。有人希望削减建模时间，“一旦可以就建立数据库”，这是不正确的。数据建模极端重要。数据建模就是建库的方案，如果方案都是错的，显然没法得到好的数据库。好的数据模型带来的好处包括：数据一致性（Data Consistency）可伸缩性(Scalability)满足业务需求(Meeting Business Requirement)方便数据检索(Easy Data
4、Retrieval)有利于性能调整(Performance tuning)8数据建模的过程 准备通过一个例子Mountain View Music在线商店建模来说明建模过程，最后用icrosoft SQL Server 2005来实施，不讲太多的理论（本章）。一些设计决策是与平台有关的，这里基于SQL Server。建模理论（逻辑元素，物理元素，规范化）业务需求（需求收集，需求解释）建立逻辑模型（创建模型，建模中的问题）建立物理模型（创建模型，索引，建立抽象层）9逻辑数据建模中用到的元素(1)工欲善其事，必先利其器。实体(Entities)属性(Attributes)数据类型(Data Typ
5、es)主码与外码(Primary and Foreign keys)域(Domains)单值和多值属性(single-Valued and Multivalued Attributes)参照完整性(Referential Integrity)10逻辑数据建模中用到的元素(2)联系(Relationships)联系类型(Relationship Types)联系选项(Relationship Options)标识与非标识联系(Identifying versus Non-Identifying Relationships)非强制与强制联系(Optional versus Mandatory Re
6、lationships)基数(Cardinality)使用子类型和超类型(Using Subtypes and Supertypes)超类型和子类型定义(Supertypes and Subtypes Defined)何时使用子类型簇(When to Use Subtype Clusters):作为子类型中的一组属性,决定子类型簇可能很费时.标识联系标识联系:子实体的主码包含了主实体的主码子实体的主码包含了主实体的主码.强制联系强制联系:要求每个实体必须参与联系要求每个实体必须参与联系11数据模型的物理元素(1)物理存储(Physical Staorage)表(Tables)视图(Views)
7、(也许是sql server中最未充分使用的功能)数据类型(Data Types)参照完整性(Referential Integrity)主码(Primary Keys)外码(Foreign Keys)约束(Constraints)编程(Programming)存储过程(Stored Procedures)用户定义函数(User-Defined Functions)触发器(Triggers)与.NET框架公共语言运行时集成(CLR Integration)12数据模型的物理元素(2)实现超类型和子类型(Implementing Supertypes and Subtypes)13SQL Ser
8、ver 2008中的数据类型(1)Numeric数据类型 Bigint 8 bytes Bit 1byte Decimal 5-17bytes Float 4 or 8bytes Int 4bytes Money 8bytes Numeric 5-17bytes (与decimal完全相同)Real 4bytes Smallint 2bytes Smallmoney 4 bytes Tinyint 1byte14SQL Server 2008中的数据类型(2)Date and Time Data Types Datetime:1753-1-1 to 9999-12-31，8bytes Smal
9、ldatetime:1900-1-1 to 2079-6-6 精确到分,4bytes Date(new):3bytes Time(new):最多5bytes Datetime2(new):8bytes,可指定秒精度 Datetimeoffset(new):10bytes,除日期时间外，可存储正负14小时的偏移量。15SQL Server 2008中的数据类型(3)String Data Types Char:non-unicode Varchar:non-unicode Nchar :unicode Nvarchar:unicode Binary:存储二进制信息（如文件或图像）varbinar
10、y Text,Ntext,and Image:为了与旧版本兼容注：Text=varchar(max),Ntext=Nvarchar(max)Image=varbinary(max)16SQL Server 2008中的数据类型(4)Other Data Types(new):Sql_variant:不同元组的该字段可存放不同类型数据 Timestamp:当表有插入或更新发生时，该列值自动增1,记录各操作的发生顺序 Uniqueidentifier：存储用NEWID()得到的独一无二的字符串 Xml：存放XML数据 Table：只能用到T-SQL程序中存放查询结果 Hierarchyid：系统提
11、供的数据类型存储分级数据。Spatial data types：存储平面坐标或球面坐标。User-Defined Data Types 用前述数据类型定义用户自己的类型，鼓励使用17参照完整性(1)主码(Primary Keys)避开字符串属性 尽可能选数值属性,最好整数 少用甚至不用复合属性 最好给主码取一个名字，例：CREATE TABLE Products(sku int NOT NULL,modelnumber varchar(25)NOT NULL,CONSTRAINT pk_product_sku PRIMARY KEY(sku)18参照完整性(2)外码(Foreign keys)
12、CREATE TABLE dbo.vehicle(objid int IDENTITY(1,1)NOT NULL,make varchar(50)NOT NULL,model varchar(50)NOT NULL,year char(4)NOT NULL,employee_objid int NOT NULL,CONSTRAINT PK_vehicle PRIMARY KEY(objid),CONSTRAINT FK_vehicle_employee FOREIGN KEY(employee_objid)REFERENCES employee(objid)19参照完整性(3)约束(Const
13、raints)显然主码和外码也是约束 唯一性约束(Unique constraints):与主码的区别在于允许一个NULL值 CONSTRAINT UNQ_vehicle_vin UNIQUE NONCLUSTERED(vin_number)check约束(Check Constraints):用逻辑表达式对值加以限制 同一属性可以有多个check约束 多个属性可以有同一个check约束 NULL值不会被检查(即会被接受)20参照完整性(4)约束(Constraints)Check约束(Check Constraints):表达式可能比较长ALTER TABLE dbo.ProductsADD
14、 CONSTRAINT chk_non_negative_valuesCHECK(weight=0 AND(shippingweight=0 AND shippingweight=weight)AND height=0 AND width=0 AND depth=0)21参照完整性(5)实现参照完整性(Implementing Referential Integrity)通过主码/外码来实现 若外码设置为NOT NULL,则为强制(Mandatory)联系,否则为非强制(optional)联系.若是1对1联系,记住把外码设置为唯一的(unique)Implementing Advanced C
15、ardinality 例如一个人最多有5辆车 可以用触发器实现22Programming:Stored Procedures 是一段T-SQL代码,允许有参数,分输入和输出,可以有一个整数返回值,如果没有指定返回值,则返回0.Like views,SPs reduce your databases complexity for users and are more efficient than simply running T-SQL repeatedly.Again,SPs remove the need to update application code if you need to c
16、hange your database.As long as the SP accepts the same parameters and returns the same data after you make changes,your application code does not have to change.23Programming:User-Defined Functions 与存储过程的不同:调用方式 存储过程:用户定义函数:函数调用可以嵌入到其它代码中DECLARE num_in_stock intEXEC sp_check_product_stock sku=458735
17、3,stock_level=num_in_stock OUTPUTPRINT num_in_stockDECLARE num_in_stock intSET num_in_stock=dbo.CheckProductStock(4587353)PRINT num_in_stockINSERT INTO employee(employeeid,firstname,lastname)VALUES(dbo.GetNewEmployeeID(),Eric,Johnson)24Programming:User-Defined Functions 与存储过程的不同:可以返回单一值(scalar funct
18、ions),也可返回一个关系(table-valued functions)UDFs are used primarily by developers who write T-SQL code against your database,but you can use UDFs to implement business rules in your model.UDFs also can be used in check constraints or triggers to help you maintain data integrity.25Programming:Triggers 触发器是
19、除约束外的另一种常用的实施数据完整性和业务规则的方法.有两类触发器:AFTER trigger:只发生在表上，操作完成时执行，对校验业务规则很有用。INSTEAD OF triggers:可以定义在视图或表上,它可以用来代替引发触发器的DML语句，可以克服具有多个基表的视图的一些限制。26Implementing Supertypes and Subtypes You have three options.The first is to create a single table that represents the attributes of the supertype and also
20、contains the attributes of all the subtypes.Your second option is to create tables for each of the subtypes,adding the supertype attributes to each of these subtype tables.Third,you can create the supertype table and the subtype tables,effectively implementing the subtype cluster in the same way it
21、was logically modeled.To determine which method is correct,you must look closely at the data being stored.We will walk through each of these options and look at the reasons you would use them,along with the pros and cons of each.若超类型与子类型数据差别很小时，采用第一种方案若超类型与子类型数据差别很小时，采用第一种方案若用第二种方案，查询时需求确定待找物品的类型，以便
22、确定去若用第二种方案，查询时需求确定待找物品的类型，以便确定去哪张表中找哪张表中找实际工作中，可能须通过试验来决定采用哪种方案更合适实际工作中，可能须通过试验来决定采用哪种方案更合适27NORMALIZING A DATA MODEL Data normalization is probably one of the most talked-about aspects of database modeling.什么是规范化？决定适用哪个范式？去规范化(Denormalization)28什么是规范化？At its most basic level,normalization is the pr
23、ocess of simplifying your data into its most efficient form by eliminating redundant data.消除冗余数据的规则就是范式 包括:第12345范式和BC范式,也曾有对第6范式的研究,它超出了本课的范畴规范化是通过消除冗余数据把数据简化为最有效的形式的过程规范化是通过消除冗余数据把数据简化为最有效的形式的过程29First Normal Form(1NF)在关系模式R中的每一个具体关系r中，如果每个属性值 都是不可再分的最小数据单位，则称R是第一范式的关系。例：如职工号，姓名，电话号码组成一个表（一个人可能有一个
24、办公室电话 和一个家里电话号码）规范成为1NF有三种方法：一是重复存储职工号和姓名。这样，关键字只能是电话号码。二是职工号为关键字，电话号码分为单位电话和住宅电话两个属性 三是用两个表,分别存储职工信息和电话信息。以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。30Second Normal Form(2NF)如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R 是属于第二范式的。例：选课关系 SCI（SNO，CNO，GRADE，CREDIT）其中SNO为学号，CNO为课程号，GRADEGE 为成绩，CREDIT 为学分。由以上条件，关键字为组合关键字
25、（SNO，CNO）在应用中使用以上关系模式有以下问题：a.数据冗余，假设同一门课由40个学生选修，学分就 重复40次。b.更新异常，若调整了某课程的学分，相应的元组CREDIT值都要更新，有可能会出现同一门课学分不同。c.插入异常，如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。d.删除异常，若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保存。原因：非关键字属性CREDIT仅函数依赖于CNO，也就是CREDIT部分依赖组合关键字（SNO，CNO）而不是完全依赖。解决方法：分成两个关系模式 SC1（SNO，CNO，GRA
26、DE），C2（CNO，CREDIT）。新关系包括两个关系模式，它们之间通过SC1中的外关键字CNO相联系，需要时再进行自然联接，恢复了原来的关系 32Third Normal Form(3NF)如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。例：如S1（SNO，SNAME，DNO，DNAME，LOCATION）各属性分别代表学号，姓名，所在系，系名称，系地址。关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储，插入，删除
27、和修改时也将产生类似以上例的情况。原因：关系中存在传递依赖造成的。即SNO-DNO。而DNO-SNO却不存在，DNO-LOCATION,因此关键辽 SNO 对 LOCATION 函数决定是通过传递依赖 SNO-LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。解决目地：每个关系模式中不能留有传递依赖。解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）注意：关系S中不能没有外关键字DNO。否则两个关系之间失去联系。34Boyce-Codd Normal Form(BCNF)如果关系模式R（U，F）的所有属性（包括主属性
28、和非主属性）都不传递依赖于R的任何候选关键字，那么称关系R是属于BCNF的。或是关系模式R，如果每个决定因素都包含关键字（而不是被关键字所包含），则BCNF的关系模式。例：配件管理关系模式 WPE（WNO，PNO，ENO，QNT）分别表仓库号，配件号，职工号，数量。有以下条件 a.一个仓库有多个职工。b.一个职工仅在一个仓库工作。c.每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件。d.同一种型号的配件可以分放在几个仓库中。分析：由以上得 PNO 不能确定QNT，由组合属性（WNO，PNO）来决定，存在函数依赖（WNO，PNO）-ENO。由于每个仓库里的一种配件由专人负责，而一个人
29、可以管理几种配件，所以有组合属性（WNO，PNO）才能确定负责人，有（WNO，PNO）-ENO。因为 一个职工仅在一个仓库工作，有ENO-WNO。由于每个仓库里的一种配件由专人负责，而一个职工仅在一个仓库工作，有（ENO，PNO）-QNT。找一下候选关键字，因为（WNO，PNO）-QNT，（WNO，PNO）-ENO，因此（WNO，PNO）可以决定整个元组，是一个候选关键字。根据ENO-WNO，（ENO，PNO）-QNT，故（ENO，PNO）也能决定整个元组，为另一个候选关键字。属性ENO，WNO，PNO 均为主属性，只有一个非主属性QNT。它对任何一个候选关键字都是完全函数依赖的，并且是直接依
30、赖，所以该关系模式是3NF。分析一下主属性。因为ENO-WNO，主属性ENO是WNO的决定因素，但是它本身不是关键字，只是组合关键字的一部分。这就造成主属性WNO对另外一个候选关键字（ENO，PNO）的部 分依赖，因为（ENO，PNO）-ENO但反过来不成立，而P-WNO，故（ENO，PNO）-WNO 也是传递依赖。虽然没有非主属性对候选关键辽的传递依赖，但存在主属性对候选关键字的传递依赖，同样也会带来麻烦。如一个新职工分配到仓库工作，但暂时处于实习阶段，没有独立负责对某些配件的管理任务。由于缺少关键字的一部分PNO而无法插入到该关系中去。又如某个人改成不管配件了去负责安全，则在删除配件的同时
31、该职工也会被删除。解决办法：分成管理EP（ENO，PNO，QNT），关键字是（ENO，PNO）工作EW（ENO，WNO）其关键字是ENO 缺点：分解后函数依赖的保持性较差。如此例中，由于分解,函数依赖（WNO，PNO）-ENO 丢失了,因而对原来的语义有所破坏。没有体现出每个仓库里一种部件由专人负责。有可能出现 一部件由两个人或两个以上的人来同时管理。因此，分解之后的关系模式降低了部分完整性约束。一个关系分解成多个关系，要使得分解有意义，起码的要求是分解后不丢失原来的信息。这些信息不仅包括数据本身，而且包括由函数依赖所表示的数据之间的相互制约。进行分解的目标是达到更高一级的规范化程度，但是分解
32、的同时必须考虑两个问题：无损联接性和保持函数依赖。有时往往不可能做到既有无损联接性，又完全保持函数依赖。需要根据需要进行权衡。374NF and 5NF 第4范式:在第3范式的基础上,要求关系模式中无多值依赖 第5范式:专用于处理多个实体间联系.38决定适用哪个范式？评估一个项目的第一步是确定其数据库所属范式.确定现有模型的范式有如下几步:1.引导需求会谈(Conduct requirements interviews.):防止偏见,形成文档2.构造基本逻辑模型(Develop a basic model)3.找出范式(Find the normal form):比较新旧模型.Make sur
33、e to consider every attribute:does it depend entirely on the primary key?Does it depend only on the primary key?Is there only one candidate primary key in the entity?Whenever you find that the answer to these questions is no,be sure to look at creating a separate entity from the existing entity.39去规范化(Denormalization)Denormalization is the process of reversing the steps taken to achieve a normal form.在有的情况下,为了提高效率,可能要求有意地复制一些数据或把一些无关的属性组织在一个实体中.
