关系数据库管理系统基础知识

单击此处编辑母版标题样式,单击此处编辑母版文本样式,*,第,1,章,关系数据库管理系统基础知识,1.1 数据库系统概述,1.2 数据模型,1.3 关系数据库及其设计过程,1.4,关系数据库的规范化,1.5,数据表的关联与数据的完整性,1.6,关系数据库应用实例电脑器材销售管理,1.7,实训要求与习题,第,1,章,关系数据库管理系统基础知识,学习目的与要求,数据库系统的基本概念,数据模型的基本概念,实体联系模型,关系模型的概念和性质,数据库系统的规范化理论等内容,设计创建一个电脑器材销售管理数据库模型,1.1 数据库系统概述,计算机应用从科学计算进入数据处理是一个重大转折，数据处理是指对各种形式的数据进行收集、储存、加工和传播的一系列活动，其基本环节是数据管理。数据管理指的是对数据的分类、组织、编码、储存、检索和维护。数据管理方式多种多样，其中数据库技术是在应用需求的推动下，在计算机硬件、软件高速发展的基础上出现的高效数据管理技术。数据库系统在计算机应用中起着越来越重要的作用，从小型单项事务处理系统到大型信息系统，从联机事务处理(OLTP)到联机分析处理(OLAP)，从传统的企业管理到计算机辅助设计与制造(CAD/CAM)、现代集成制造系统(CIMS)、办公信息系统(OIS)、地理信息系统(GIS)等，都离不开数据库管理系统。正是这些不断涌现的应用要求，又不断地推动了数据库技术的更新换代。,1.1.1 数据库技术的产生与发展,1.,数据库技术的产生,从20世纪60年代后期开始，计算机技术从科学计算迅速扩展到数据处理领域，随着数据处理的不断深入，数据处理的规模越来越大，数据量也越来越多，数据处理成为最大的计算机应用领域。数据处理技术也不断地完善，经历了人工管理、文件系统和数据库系统三个阶段。,(1)人工管理阶段,计算机在其诞生初期，人们还是把它当作种计算工具，主要用于科学计算。通常是在编写的应用程序中给出自带的相关数据，将程序和相关数据同时输入计算机。不同用户针对不同问题编制各自的程序，整理各自程序所需要的数据。数据的管理完全由用户自己负责。,人工管理阶段程序与数据的关系,特点：,数据不能单独保存。,数据无独立性。,数据冗余不能共享。,(2) 文件系统阶段,在文件系统中，把数据组织成相互独立的数据文件，利用“按文件名访问，按记录存取”的管理技术，程序和数据分别存储为程序文件和数据文件。数据文件是独立的，可以长期保存在外存储器上多次存取。数据的存取以记录为基本单位，并出现了多种文件组织形式，如顺序文件、索引文件、随机文件等。,文件系统阶段程序与数据的关系,数据与程序缺乏独立性。,数据的冗余和不一致性。,数据的无结构性。,特点：,(3)数据库系统阶段,为了从根本上解决数据与程序的相关性，把数据作为一种共享的资源进行集中管理，为各种应用系统提供共享服务，数据库技术应运而生，使信息管理系统的重心从以加工数据的程序为中心转向以数据共享、统一管理为核心。,数据库系统阶段程序与数据的关系, 数据结构化, 数据能够共享, 数据冗余度小，易扩充, 数据与程序的独立性较高, 对数据实行集中统控制,优点：,2.,数据库技术的发展,随着计算机科学的不断发展，数据库技术大致上经历了三个发展时期：,(1) 20世纪60年代的萌芽期,(2) 20世纪70年代的发展期,(3) 20世纪80年代的成熟期,1.1.2 数据库,1、数据库,数据库(Database，简称DB)，顾名思义，是存放数据的仓库。只不过这个仓库是创建在计算机存储设备上，如硬盘就是一类最常见的计算机大容量存储设备。数据必须按一定的格式存放，以利于以后使用。可以说数据库就是长期存储在计算机内、与应用程序彼此独立的、以定的组织方式存储在一起的、彼此相互关联的、具有较少冗余的、能被多个用户共享的数据集合。在这里要特别注意数据库不是简单地将一些数据堆积在一起，而是把相互间有一定关系的数据，按一定的结构组织起来的数据集合。,2,、,数据库体系结构,为了有效地组织、管理数据，人们为数据库设计了一个严谨的体系结构，包括了内模式、模式和外模式三级模式结构，这三级模式反映了看待数据库的三种不同的数据观点。,1.1.3 数据库管理系统,数据库管理系统（DataBase Management System，简称DBMS）是位于用户与计算机操作系统之间的一个系统软件，由一组计算机程序组成。它能够对数据库进行有效的组织、管理和控制，包括数据的存储、数据的安全性与完整性控制等。 DBMS主要功能：,1.数据定义功能,2.数据操纵功能,3. 数据库的运行控制与管理,4. 数据库的建立和维护功能,5.数据通信接口,1.1.4 数据库系统,数据库系统（Database System，简称DBS）是指在计算机系统中引入数据库后的系统，带有数据库的计算机系统硬件和软件层次如图所示。,数据库系统构成,硬件平台,数据库,软件,数据库管理系统（DBMS）、支持DBMS运行的操作系统、具有数据库接口的高级语言及其编译系统、以DBMS为核心的应用开发工具。,为特定应用环境开发的数据库应用系统。,相关人员,数据库管理员（DBA）、系统分析员、数据库设计员、应用程序员、用户,1.2 数据模型,概念数据模型：独立于计算机系统的数据模型，,用来描述所使用的信息结构。,逻辑数据模型：现实世界的第二层抽象，反映数据的逻辑结构。,物理数据模型：反映数据在计算机中的存储结构。,在数据库中用数据模型来抽象、表示和处理现实世界中的数据和信息。根据数据抽象层次，针对不同的数据对象和应用目的，可以分为三类：,1.2.1数据模型的组成要素,数据结构：所研究的对象类型的集合，这些对象是数据库的组成成分。,数据操作：指对数据库中不同数据结构的对象所允许执行的操作的集合，包括操作及操作规则。,完整性约束：数据的完整性约束条件是一组完整性规则的集合。,1.2.2 概念模型,具体的数据库管理系统DBMS所支持的逻辑数据模型不便于非计算机专业人员理解和应用，在开始设计数据模型时，可以先用概念数据模型将现实世界中的客观事物用某种信息结构表示出来，再转化为用计算机表示的逻辑数据模型，如图所示。,概念模型涉及的概念,(1) 实体：现实世界中客观存在并可相互区分的事物称为实体。,(2) 属性：实体所具有的某一特性称为属性。,(3)实体和属性的型与值：型是结构，用实体名及其属性名集合描述同类实体，称为实体型；值是数据，不同的实体有不同的属性内容（属性值）。,(4) 实体集：具有相同实体型的实体值的集合称为实体集。,(5) 关键字：在实体属性中，能区别实体集合中不同个体的某一个或某几个属性的组合，称为关键字。,2. 实体间的联系,两个实体集之间的联系可以分为三类：,一对一联系(1：1),一对多联系(1：N),多对多联系(M：N),3. 概念模型的表示方法,概念模型的表示方法很多，其中最著名最常用的是于1976年提出的实体-联系方法（E-R方法）。该方法用E-R图描述信息世界的概念模型。,方法如下：,实体型：用矩形表示，矩形框内写上实体名。,属性：用椭圆形或圆角矩形表示，图形内写上属性名，并用直线将其与相应的实体连接起来。,联系：用菱形表示，菱形框内写上表示联系行为的动词表示联系名，并用直线分别与有关实体连接起来，同时在直线旁边标上联系的类型(1：1，1：,N,或,M：N)。,如果一个联系具有属性，则这些属性也要用直线与该联系连接起来,。,学生实体,通常在表示单个实体的E-R图中，除了要标明实体名外，还要标明实体所具有的属性。例如学生实体具有学号、姓名、性别、出生日期和所在系的属性，其E-R图如下：,实体之间的联系的表示,在表示实体之间的联系时，每个实体只标明其实体名，而把实体属性单独用图表示或把所有实体集用列表表示。,两个以上的实体集之间联系的表示,同一个实体集内部联系的表示,1.2.3 层次模型,在数据库中，对满足以下两个条件的数据模型称为层次模型。,有且仅有一个节点无双亲，这个节点称为“根节点”。,其他节点有且仅有一个双亲。,层次模型中相关概念,在层次模型中，同一双亲的子女节点称为兄弟节点；,没有子女的节点称为叶节点；,双亲节点与其任意一个子女节点都构成一个基本层次联系，表示一对多的关系。,层次模型特点,层次模型的优点：,层次模型数据结构简单，对具有一对多的层次关系的描述非常自然、直观、容易理解。记录之间的联系通过指针来实现，查询效率较高。,层次模型的缺点：,上一层记录类型和下一层记录类型只能表示一对多联系，无法实现多对多联系。如果要实现多对多联系，则非常复杂，效率非常低，使用也不方便。,1.2.4 网状模型,在网状模型中，允许：,一个以上的节点无双亲。,一个节点可以有多于一个的双亲。,网状模型的特点,网状模型的优点：,记录之间联系通过指针实现，具有良好的性能，存取效率较高。能够更为直接地描述现实世界，如一个节点可以有多个双亲。,网状模型的缺点：,随着应用环境的扩大，数据库的结构会变得越来越复杂，编写应用程序也会更加复杂，程序员必须熟悉数据库的逻辑结构。与层次模型一样，现在的数据库管理系统已经很少使用网状模型了。,1.2.5 关系模型,关系模型是三种数据模型中最重要的模型，是当前使用最广泛的数据模型。Microsoft SQL Server 2000数据库管理系统也是基于关系模型的。关系模型是建立在数学概念基础上的，它的主要特征是使用关系来表示实体以及实体之间的联系。,1.关系模型的基本术语,(1) 关系一个关系模型的逻辑结构是二维表，它由行和列组成。,(2) 元组表中的一行称为一个元组，在数据库中也称为记录。,(3) 属性表中的一列称为一个属性，用来描述事物的特征，属性分为属性名和属性值。在数据库中属性也称为字段。,(4) 域属性的取值范围。,(5) 关系模式关系模式描述关系的信息结构和语义限制，是型的概念；而关系是关系模式中的个实例，是值的概念。关系模式的描述形式-关系名（属性1，属性2，属性n）。,1.关系模型的基本术语,(6) 关系数据库 使用关系模型表示和处理数据的数据库，是一些相关的表和其他数据库对象的集合。,(7) 关键字/码若关系中的某一个属性或属性组的值惟一地决定其它所有属性，则这个属性或属性组称为该关系的关键字。,(8) 候选键/候选关键字/侯选码如果一个关系中有多个属性或属性组都能用来标识该关系的元组，那么这些属性或属性组都称为该关系的候选关键字。,1.关系模型的基本术语,(9) 主键/主关键字/主码在一个关系的多个候选关键字中指定其中一个作为该关系的关键字，则称它为主关键字或主键、主码。,(10)外键/外关键字/外码如果一个关系R中的某个属性或属性组F并非该关系的关键字，但它和另外一个关系S的关键字K相对应，则称F为关系R的外键，同时要求外键F的值要参照关系S中主键K的值。有时，R和S可能为同一个关系。,2.关系模型三要素,（1） 数据结构关系关系模型中数据的逻辑结构就是一张二维表格。在关系数据库中，关系模式是型（二维表格），关系是值（元组的集合），关系模式必须指出这个元组集合的结构，即它由哪些属性构成，这些属性采用何种类型、来自哪些域，以及属性与域之间的映像关系。,（2） 关系操作关系模型中常用的关系操作有数据查询和数据更新两大部分，其中数据查询包括选择、投影、连接、除、并、交、差；数据更新包括插入、删除、修改操作。,（3） 关系完整性约束关系模型允许定义三类完整性约束：实体完整性、参照完整性和数据类型的域完整性。实体完整性和参照完整性是关系模型必须满足的约束条件，由关系系统自动支持；数据类型的域完整性是数据取值要遵循的约束条件。,3. 关系模型的特点,(1)关系模型具有严格的理论基础(2)关系模型的数据结构单一(3)关系模型存取简单,1.3 关系数据库及其设计过程,关系数据库是目前使用最广泛的数据库，现实世界信息结构复杂、应用环境千变万化，如何构造一个合理的数据库系统，使之能够有效地存储数据，满足各种用户的需求是我们要解决的首要问题，本节将结合应用实例电脑器材销售管理研究关系数据库的设计过程。,1.3.1 关系与表格,关系模型是建立在集合代数基础上的，关系是有严格的数学定义的，并不是所有的二维表格都可以称为关系，这个表格应该具有如下一些性质。(1) 关系必须是规范化的关系(2) 表中的“行”是惟一的(3) 行的次序可以任意(4)表中的“列名”是惟一的(5) 列的次序可以任意(6) 必须满足完整性约束条件,1.3.2 数据表基本概念,关系数据库中包含若干关系二维数据表，可分为基本的数据表、查询结果集、视图等，其中数据表是最重要的一类关系，其它的对象大都依附于数据表。数据表是数据库中最基本的对象，用来在数据库中存储用户的全部数据。数据库中可以有多个数据表，每个数据表可代表用户某类有意义的需求信息。例如在一个学校“教学管理”数据库中有“教师信息表”、“学生信息表”和“课程成绩表”等。数据表中的每一行代表不同需求的一个实体对象。例如“学生信息表”中用一个学生的档案信息属性作为一个实体；而在“课程成绩表”中用一个学生的课程成绩信息属性作为一个实体。数据表中的每一列都代表实体对象的一个属性特征，如学生的姓名、住址等。数据表经设计完成并创建之后，就一直存储在数据库文件中，直到被删除为止。,1.3.3 数据库设计过程,数据库设计所要解决的问题是建立数据表及其应用系统，具体说，就是指对一个给定的需求和应用环境，构造最优化的数据库模式，建立数据库及其应用系统，满足用户的各种应用需求。大型数据库的设计和开发是一项庞大的工程，是多学科的综合性技术。数据库设计步骤大致可以分为需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护几个阶段。设计一个完善的数据库应用系统往往是上述六个阶段的不断反复、逐步完善的过程，如图所示。,1.3.3 数据库设计过程,1.,需求分析,需求分析的任务是由计算机人员（系统分析员）和用户双方共同通过详细的调查研究，充分了解用户的组织机构、业务规则、数据需求、完整性约束条件、事务处理和安全性要求等，为概念设计奠定基础。这个阶段的许多方法与系统分析设计、软件工程的方法类似，画出组织机构图、业务流程图，详细描述用户应用环境的业务流程、数据需求。,2. 概念结构设计,最常用的概念模型表示方法是实体-联系模型，简称E-R模型。设计E-R模型应遵循的原则：(1) 首先针对特定用户的应用，确定实体、属性和实体间的联系，做出局部E-R模型。(2) 综合各个用户的局部E-R模型，在消除冗余联系并保证数据完整性的前提下，产生能准确地反映原来的局部E-R模型（包括实体、属性及实体间的相互联系）、反映数据库整体概念、不存在相互表达矛盾的总体E-R模型。一个系统的E-R模型并不是惟一的，强调不同侧面和不同联系，做出的E-R模型可能会有很大的差别。,1.3.3 数据库设计过程,3. 逻辑结构设计,逻辑结构设计的任务就是把概念模型转换成DBMS所支持的数据模型，如关系模型、层次模型或网状模型。我们只讨论E-R模型向关系模型的转换原则。,(1) 实体向关系模型的转化,(2)一对一联系向关系模型的转化,(3) 一对多联系向关系模型的转化(4)多对多联系向关系模型的转化用E-R模型转换得到的关系模型要利用关系规范化理论进行进一步的规范化设计，使之符合DBMS的要求。,4. 物理结构设计,在物理结构设计时，应考虑以下几个方面：(1) 确定数据的存储结构(2) 索引结构设计(3) 数据存储位置的考虑(4) 系统配置的优化,1.3.3 数据库设计过程,5. 数据库实施,确定了数据库的逻辑结构和物理结构，就可以利用DBMS提供的数据定义语言建立数据库的结构。数据库的结构建立好之后，就可以向数据库中装载数据。(1) 根据确定的逻辑结构与物理结构，用DBMS提供的数据定义语言定义数据库结构。(2) 数据装载数据库结构建立后向数据库中装载数据(也称为数据加载)。,6. 数据库的运行与维护,数据库设计与应用开发工作完成之后，系统进入运行与维护阶段，主要任务如下。(1)维护数据库的安全性和数据完整性(2) 数据库的转储和恢复(3)监测并改善数据库性能(4)数据库的重新组织,1.3.4 电脑器材销售管理数据库的模型设计,本书以某电脑公司的电脑器材销售管理数据库应用系统为例贯穿全书，本节主要介绍如何设计该数据模型，在以后各章节均以该数据库为【实例练习】进行操作，通过该实例的学习使读者掌握SQL Server数据库系统与数据库应用技术。,【实例练习1-1】某电脑公司电脑器材销售管理数据库应用系统的模型设计,该电脑公司的需求分析可以简单概括为：“商品购进库存管理商品销售”三大环节。,1,、概念模型：,在概念模型中可以初步将商品、供货商、客户、员工对象作为实体，其属性的描述：,商品,（商品编号或条形编码、商品名称、规格尺寸或型号、计量单位、供货厂家、进货价格、销售参考价格、库存数量）,供货商,（供货厂家编号、厂家名称、厂家地址、进货商品名称、进货日期、进货数量、进货价格、厂家账户、厂家联系人、收货员工）,客户,（客户名称、销售商品名称、规格、计量单位、销售日期、销售单价、销售数量、销售金额、销售员工）,员工,（员工编号、姓名、性别、年龄或出生日期、部门、工龄或工作时间、照片、个人简历）,在一个关系数据库中允许有多个不同的关系数据表，并对所有的数据表进行统一管理。我们可以根据各个不同职能部门的实际工作需要，把概念模型中的一个实体集作为一个关系，初步设计成关系数据库逻辑模型中的4个二维数据表格。,2、逻辑模型,（1）公司管理层使用的简单商品一览表,该表是公司所经营和准备扩展经营的全部商品明细，为规范化经营管理，由公司决策部门对商品统一进行分类，制定统一的商品编号，用“货号”表示，并指定统一的商品名称，商品一览表的主要数据项见表1.5。,货号,货名,规格,单位,平均进价,参考价格,库存量,其中：,“平均进价”根据从不同厂家购进同种商品的不同价格，或从同一厂家因进货时间不同而价格不同时，由数据库系统按加权平均方法自动计算的平均“进货价格”。,“参考价格”是公司为该商品制定的销售指导价格，允许销售员在5%的范围内自主灵活销售。,2、逻辑模型,（2）进货部门按进货记录填写的每年度一张的进货表xxxx,该表用于保存详细的进货记录，由收货人每次进货时填写，采用以一个年度（或月份）为单位使用一张“进货表”，表的名称后缀年份（或年月）标志加以区分（若前缀数字作数据表名不符合标识符命名规则）。,本例题进货表2006表示2006年的“进货表”，2006年度结束到2007年时自动创建并使用进货表2007。,进货日期,货号,货名,规格,单位,进价,进货数量,供货商,厂家地址,账户,联系人,收货人,2、逻辑模型,（,3,）销售部门按销售记录填写每年度一张的销售表,xxxx,该表用于保存详细的销售记录，由销售员每次销售商品时填写。2006年使用销售表2006，2006年度结束到2007年时自动创建并使用销售表2007。,销售日期,客户名称,货号,货名,规格,单位,销售单价,销售数量,销售金额,销售员,2、逻辑模型,（,4,）全公司职工的员工表,结合整个公司的人事管理，能表示进货表xxxx中“收货人”和销售表xxxx中“销售员”的详细信息。,其中“出生日期”即代表年龄，如果使用“年龄”数据项则每年都需要改变，采用“出生日期”可以由数据库的函数自动计算并显示每年的当前年龄。,员工编号ID,姓名,性别,出生日期,部门,工作时间,照片,个人简历,1.4 关系数据库的规范化,在1.3.1节中我们介绍关系与表格时曾指出并不是所有的二维表格都可以称为关系，在关系数据库中，每一个表格必须满足一定的要规范条件。,数据模型是数据库应用系统的基础和核心，合理设计数据模型是数据库应用系统设计的关键，使用规范化的优点是：,l,大大改进数据库的整体组织结构,l,减少数据冗余,l,增强数据的一致性和正确性,l,提高数据库设计的灵活性,更好的处理数据库的安全性,1.4.1,数据库的三个规范化形式,数据模型应进行规范化处理，一个数据库可以有三种不同的规范化形式，即：,l,第一规范化形式 1NF,l,第二规范化形式 2NF,l,第三规范化形式 3NF,1.,第一规范化形式1NF,第一规范化形式简称第一范式：在一个关系（数据表）中没有重复的数据项，每个属性都是不可分割的最小数据元素。即每列的列名（字段名）都是惟一的，一个关系中不允许有两个相同的属性名，同一列的数据具有相同的数据类型，列的顺序交换后不能改变关系的实际意义。,字段：,就是数据表中的列，一列叫做一个字段，表示关系中实体的一个属性。,简单说第一范式就是指数据表中没有相同的列字段惟一。,关系数据库中所有的数据表都必须满足1NF。,2、第二规范化形式 2NF,第二规范化形式简称第二范式：在已满足,1NF,的关系中，一行（数据元组、记录）中所有非关键字数据元素都完全依赖于关键字（记录惟一）。即一个关系中不允许有两个相同的实体，行的顺序交换后不能改变数据表的实际意义。,关键字：也叫关键字段或主键，是所有数据都是惟一不重复的字段或字段的组合。,记录：,数据表中的一行叫做一条记录，由表中各列的数据项组成，是一组多个相关数据的集合，也称为数据元组。,如果指定一个关键字，则可以在这个数据表中惟一确定一条记录（行），比如在学生信息表里指定“学号”为关键字，则每个学号都惟一的表示一个学生的信息，其他属性都完全依赖于“学号”。,简单说第二范式就是数据表中没有相同的行，通过关键字使记录惟一。,不满足2NF的数据表，将导致数据插入或删除的异常，稍有不慎会使数据不一致，规范化的数据表都必须满足2NF。,【实例练习1-2】,根据第二范式要求，不难发现电脑器材销售管理数据库模型表1.7销售表xxxx不满足第二范式：,因为公司每天可以销售多种商品，则“销售日期”不惟一；一个客户可能多次购买不同的商品，则“客户名称”不惟一；同一货号的商品会多次销售给不同的客户。就是说销售表xxxx中没有一列的值是保证不可重复的，无法指定关键字段使某条销售记录惟一，即不能保证没有相同的行。,解决方法：,增加一列“序号”作为该表的关键字，该列数据没有重复的值，则可以保证没有重复的记录（行），以满足2NF。,序号,销售日期,客户名称,货号,货名,规格,单位,销售单价,销售数量,销售金额,销售员,3、第三规范化形式 3NF,第三规范化形式简称第三范式：在已满足2NF的关系中，不存在传递依赖于关键字的数据项。,传递依赖：,某些列的数据不是直接依赖于关键字，而是通过某个非关键字间接的依赖于关键字。,简单说第三范式就是表中没有间接依赖关键字的数据项。,实现第三范式的方法就是将不依赖关键字的列删除，单独创建一个数据表存储。,规范化的数据库应尽量满足3NF，一个满足3NF的数据库将有效地减少数据冗余。,注意：三个范式不是独立的，3NF包含2NF，2NF又包含1NF。,【实例练习1-3】,进货表xxxx既不满足第二范式也不满足第三范式。,第一：由于每列数据都不能惟一标识一条进货记录，增加一列“序号”作为关键字，即可以满足第二范式。,第二：“厂家地址、账户、联系人”不是“进货记录”实体中必不可少的数据项，它们只依赖于“供货商”，通过“供货商”而间接依赖于关键字，是具有传递依赖的数据项。,解决方法：,将进货表xxxx中有传递依赖的“厂家地址、账户、联系人”三列删除，单独建立一个存储进货厂家信息的供货商表，指定“供货商ID”字段为主键，使其满足第二范式（也满足第三范式）。,供货商ID,供货商,厂家地址,账户,联系人,【实例练习1-3】,增加供货商表后，可在进货表xxxx中只保留一个“供货商ID”字段，以便与供货商表建立关联。,注意：,u,修改后的进货表xxxx在与供货商表建立关联后（在后面介绍），我们就可以通过“供货商ID”字段在供货商表中找到该厂家的所有信息。,u,修改后的进货表xxxx仅仅消除了一部分传递依赖，仍不满足第三范式，因为“货名”“规格”“单位”也是依赖于“货号”具有传递依赖的数据项，同样修改后的表1.9销售表xxxx也不满足第三范式，我们将在后面再进一步规范。,序号,进货日期,货号,货名,规格,单位,进价,进货数量,供货商ID,收货人,1.4.2,数据库规范化设计的原则,规范化数据库的设计原则：,l,保证数据库中的所有数据表都能满足2NF，力求绝大多数数据表满足3NF；,l,保证数据的完整性；,l,尽可能减少数据冗余。,1.4.3 规范化的缺点及非规范化,数据库的设计都需要一定程度的规范化，但规范化的缺点是降低了数据库的性能，由于规范化数据库要连接不同表中的数据，必须给所关联的数据表进行定位、这会占用更多的CPU、内存和输入输出，自然对数据库性能产生一定的负面影响。,所谓“非规范化”是对已经规范化的数据库做适当的修改，允许有限度的冗余性，比如允许在一个表中使用少量频率较高的重复数据，则有利于提高数据库的性能。,注意：非规范化的数据库不同于没有规范化过的原始数据库。,1.5 数据表的关联与数据的完整性,1.5.1 表的关联,对相关的数据表进行连接建立关联，可以使不同的数据表成为一个逻辑上的整体，以便统一管理。,在SQL Server 中数据表的连接有,交叉连接、内连接、外连接、自连接,等4种方式。,假设有学生信息表学生成绩表，我们通过这两个简单数据表的连接来理解交叉连接、内连接、外连接和自连接。,学号,姓名,1001,吕川页,1002,郑学敏,1003,于 丽,1004,孙立华,学号,成绩,1003,92,1004,78,1005,85,1、,交叉连接,交叉连接也称为非限制连接、无条件连接或笛卡尔连接，就是将两个表不加任何限制的组合在一起，其连接方法是将第一个表中的每条记录（行）分别与第二个表中的每条记录（行）连接成一条新的记录（行），连接结果是具有两个表记录数乘积的逻辑数据表。,两个表采用交叉连接没有实际意义，仅用于说明表直接的连接原理。,【例1-3】将学生信息表和学生成绩表进行交叉连接，结果见表1.14。,2、,内连接,内连接也称为自然连接，就是只将两个表中满足指定条件的记录（行）连接成一条新记录，舍弃所有不满足条件没有连接的记录。,内连接实际上是把交叉连接的结果按指定条件进行筛选后的结果，是数据表最常用的连接方式。,【例1-4】将学生信息表和学生成绩表按“学号=学号”进行内连接显示全部字段，可得到两个表中共有的记录信息。结果见表1.15。,注意：学生信息表中的1001、1002号学生没有对应的考试成绩，学生成绩表中的1005号学生尚没有在学生信息表中注册（实际中这种情况是不可能存在的）或已经转学离开，这些记录不满足连接条件，所以不会出现在结果集中。,3、,外连接,外连接分为左外连接、右外连接、全外连接三种。,（1）左外连接,左外连接可以得到左表（指定的第一个表）的全部记录信息及右表（指定的第二个表）相关的记录信息。就是取左表的全部记录按指定的条件与右表中满足条件的记录连接成一条新记录（相当于内连接），但该条件不限制左表，左表的全部记录都包括在结果集中，若右表中没有满足条件的记录与之连接，则在相应的结果字段中填入NULL（Bit类型填0），以保持左表的完整性。,【例1-5】将学生信息表和学生成绩表按“学号=学号”进行左外连接显示全部字段。结果见表1.16。,（2）右外连接,右外连接可以得到右表（指定的第二个表）的全部记录信息及左表（指定的第一个表）相关的记录信息。右外连接与左外连接方法相同，只是把两个表的顺序颠倒了一下，就是取右表的全部记录按指定的条件与左表中满足条件的记录连接成一条新记录，但该条件不限制右表，右表的全部记录都包括在结果集中，若左表中没有满足条件的记录与之连接，则在相应的字段上填入NULL（Bit类型填0），以保持右表的完整性。,【例1-6】将学生信息表和学生成绩表按“学号=学号”进行右外连接显示全部字段。结果见表1.17。,（3）全外连接,全外连接可以得到左表与右表的全部记录信息。相当于先左外连接再右外连接的综合连接，就是取左表（第一个表）的全部记录按指定的条件与右表（第二个表）中满足条件的记录连接成一条新的记录，右表中若没有满足条件的记录则在相应的字段上填入NULL，再将左表不符合条件记录的相应字段填入NULL，以保持两个表的完整性。,【例1-7】将学生信息表和学生成绩表按“学号=学号”进行全外连接显示全部字段。结果见表1.18。,4、,自连接,自连接就是一张表看成两个副本，对同一数据表的两个副本按指定条件进行内连接。,使用自连接可以将一张表中满足条件的不同记录连接起来。,我们将在5.2.4节实现表的连接时举例说明自连接。,1.5.2,数据的完整性及约束,1、,数据的完整性,数据的完整性泛指数据的正确性和一致性，包括实体完整性、参照完整性和域完整性。,（1）实体完整性,实体完整性是指数据表中的所有行都是惟一的确定的，所有记录都是可以区分的（满足2NF）。实体完整性规则规定了表中的主键值惟一，所有主要属性都不能取空值。,例如，在员工表中，“员工编号”可作为主键，同时“员工编号、姓名、性别、出生日期、部门、工作时间”都不能取空值才能保证每个“员工”实体的完整性。,（2）参照完整性,当一个表中的某列数据（外键）依赖并引用另一个表中的某列数据时，这两个表之间的相关数据必须保持一致性。,例如在进货表xxxx中的“供货商ID”（外键）的取值必须参照供货商表中的“供货商ID”（主键）的有效值，与其保持一致性。,再例如进货表xxxx中的“收货人”、销售表xxxx中的“销售员”必须与员工表中的某个“姓名”属性值保持一致。,（3）域完整性,域完整性是指表中每列的数据具有正确的数据类型、格式和有效的取值范围，保证数据的正确性。,例如员工的性别只能取值为“男”或“女”；厂家账户只能是数字字符；进货数量只能是大于0的正整数。,2、,约束,(1),主键约束（Primary key）,设置主键约束的字段称为关键字段，主键值是记录的惟一标识，主键约束可以保证数据的实体完整性，使表中的记录是惟一可区分和确定的（满足2NF）。,规范化的数据库每个表都必须设置主键约束，主键有以下特点和限制：,l,主键的字段值必须是惟一的，不允许重复。,l,主键的字段值必须是确定的，不允许为空。,l,一个表只能定义一个主键，主键可以是单一字段，也可以是多个字段的组合，用多个列的组合作主键时每个列上的数据都可以重复，但其组合值不允许重复。,l,Text、Ntext和Image类型的字段不能做主键。,不论输入数据的顺序如何，数据表将按记录的主键值从小到大进行物理升序排序。,【实例练习1-4】,为电脑器材销售管理数据库模型中的数据表设置主键：,销售表xxxx进货表xxxx将“序号”字段设置为主键；,商品一览表将“货号”设置为主键；,供货商表将“供货商ID”设置为主键；,员工表将“员工ID”设置为主键。,(2),惟一约束（Unique）,惟一约束可以指定一列数据或几列数据的组合值在数据表中是惟一不能重复的。,惟一约束用于保证主键以外的字段值不能重复，用以保证数据的实体完整性，但惟一约束的字段不是主键，其区别是：,l,一个表可以定义多个惟一约束，而主键约束只能定义一个；,l,定义为惟一约束的字段可以允许为空值（只能有一个），而主键约束的字段不允许为空值；,l,记录按主键值的指定顺序存储，而惟一约束的字段值不改变记录的物理位置，仅仅保证该字段的值不重复。,例如供货商表中指定了“供货商ID”为主键，若还需保证“供货商”“厂家地址”“账户”各字段的值不能重复，则可以设置为惟一约束。,(3),外键约束（Foreign key）,如果一个表中某个字段的数据只能取另一个表中某个字段值之一，则必须为该字段设置外键约束，设置外键约束字段的表称为子表，它所引用的表称为父表。,外键约束可以使一个数据库中的多个数据表之间建立关联，外键的取值必须是被引用表中主键的值，通过外键约束可以使父表与子表建立一对多的逻辑关系。,外键约束可以保证数据的参照完整性和域完整性性。,l,外键可以是单一字段，也可以是多个字段的组合。,l,外键所引用父表中的字段必须是创建了主键约束或惟一约束的列。,l,外键允许空值，可以有重复值，但必须是父表引用列中的数据之一，也就是说父表中没有的数据子表不可以添加。,l,子表中外键字段添加的新数据，必须先在父表中添加，再在子表中添加。,l,子表中引用父表数据的记录未删除，则父表中被引用的数据不能被删除。,【实例练习1-4】,进货表销售表中的“货号”是公司在商品一览表中统一规定的，它们的取值必须是商品一览表主键“货号”的字段值之一，必须设置外键。,进货表中的“供货商ID”是连接生产厂家有关信息的，必须设置外键约束引用父表主键“供货商ID”的数据。,进货表中的“收货人”和销售表中的“销售员”都是本公司员工，它的取值必须是员工表“姓名”字段值之一，必须设置外键约束。,注意：,因为员工表指定了“员工ID”为主键，所以子表外键所引用的“姓名”字段必须设置惟一约束以满足外键约束的要求。,(4),检查约束（Check）,检查约束是用指定的条件（逻辑表达式）检查限制输入数据的取值范围是否正确，用以保证数据的参照完整性和域完整性。例如：,商品一览表的“货号”只能使用数字。,供货商表的“供货商ID”只能使用大写字母，账户只能使用数字或“-”号。,员工表的“性别”只能取值为“男”或“女”；,销售表xxxx的“数量”“销售单价”必须大于0；“销售单价”只能在公司“参考价格”的5%范围内下浮或上调。,(5),默认值约束（Default）,默认值约束是给某个字段绑定一个默认的初始值（可以是常量、表达式或系统内置函数），输入记录时若没有给出该字段的数据，则自动填入默认值以保证数据的域完整性。,对于事先不知道数据或需要自动计算产生数据但又不允许为空的字段，使用默认值约束尤其方便并提高效率。,对设置了默认值约束的字段若输入数据时则以输入的数据为准。,商品一览表的“库存量”在创建数据库初期是没有数据的，在系统运行时能自动计算，可以设置默认值为0。,进货表的“进货日期”和销售表中的“销售日期”，可用系统当前的日期设置默认值。,(6),空值约束（NULL）,空值NULL是不知道或不能确定的特殊数据，不等同于数值0和字符的空格。,空值约束就是设置某个字段是否允许为空，用以保证数据的实体完整性和域完整性性。,必须有确定值的字段（尤其是数值字段）可设置空值约束为“否”，即不允许为空；可以允许有不确定值的字段设置空值约束为“是”，则允许为空。,商品一览表的“平均进价”“参考价格”需要根据进价和市场情况才可以制定，在确定经营某种商品初期，开始输入商品信息时可以允许为空，其他字段则不允许为空。,进货表填写进货记录时所有字段均不允许为空，必须设置空值约束为“否”。,员工表中的“照片”“个人简历”允许为空，空值约束可设置为“是”。,1.6 关系数据库应用实例电脑器材销售管理,1.6.1电脑器材销售管理数据库的规范化设计,【实例练习1-5】电脑器材销售管理数据库的进一步规范化设计,1.,满足第一范式,电脑器材销售管理全部数据表都已经满足了1NF，即各表中没有重复的数据项各表的字段名是惟一的，没有相同的列。,2.,满足第二范式,在【实例练习1-4】为每个表指定了关键字创建主键约束后，全部数据表也都已经满足了2NF，即各表中的关键字段都可以惟一区分不同的记录，没有重复的行。,【实例练习1-5】,3、,满足第三范式减少数据冗余,（1）主表商品一览表、供货商表、员工表中都没有传递依赖的字段，所有字段都直接依赖关键字，因此都能满足第三范式。,（2）修改后的进货表xxxx还存在与商品一览表中“货号、货名、规格、单位”相重复的字段，其中“货名、规格、单位”是有传递依赖的字段，不能满足第三范式。,解决方法：,将表1.11进货表xxxx中的重复字段“货名、规格、单位”删掉，只保留“货号”字段，并设置为外键约束引用父表商品一览表的主键“货号”。满足第三范式的进货表xxxx见表1.19。,【实例练习1-5】,（3）在表1.9销售表xxxx中只保留“货号”并设置为外键，引用父表商品一览表的主键“货号”。,考虑销售时有“商品名称”字段比较方便，可保留“货名”字段，允许有限度的冗余，有利提高数据库的性能。不满足第三范式非规范化的销售表xxxx见表1.20。,电脑器材销售管理数据库逻辑数据模型,1、,员工表（见表1.21）,n,“员工ID”设置主键约束、只允许5位数字的检查约束。,n,“姓名”设置空值约束不允许为空；设置惟一约束。由于有同名现象，一般不设置惟一约束，本书实例为了演示在进货表销售表中直观使用“收货人”和“销售员”并设置了外键约束与员工表关联，为了满足外键约束“姓名”必须设置惟一约束。如果出现同名，应想办法予以区分。,n,“性别”不允许为空；设置检查约束只允许输入1和0表示“男”“女”。,n,“部门”不允许为空；根据人数最多的部门设置默认值约束“销售科”。,n,“照片”和“个人简历”设置空值约束“是”，允许为空。,2、,商品一览表（见表1.22）,n,“货号”设置为关键字；设置只允许4位数字的检查约束。,n,“货名”“规格”“单位”设置空值约束“否”，不允许为空。,n,“货名”设置默认值“计算机”。,n,“平均进价”“参考价格”“库存量”在准备经营某种产品初期可以暂时没有数据，允许为空；检查约束不能为负值，即大于等于0。,n,“平均进价”在运行时应根据不同“进价”和“数量”按一定的公式自动计算。,n,“库存量”设置默认值0，运行时根据进货“数量”和销售“数量”自动计算。,在现实应用中，进货入库形成的是库存数量，再由仓库调拨到柜台形成柜台库存数量，还应该有记录商品上柜调拨信息的数据表，限于篇幅本书未考虑该部分。,3、,供货商表（见表1.23）,n,除“联系人”以外其余字段均设置空值约束不允许为空。,n,“供货商ID”为主键；设置检查约束只允许输入4位英文字母或数字字符。,n,“供货商”“厂家地址”“账户”设置为惟一约束。,n,“账户”设置检查约束只允许输入数字字符和“-”；设置默认值“0000-0000-0000”。,4、,进货表2006（见表1.24）,n,所有字段均不允许为空。,n,“序号”为主键；并设置为自动产生序号的自动编号字段“标识列”。,n,“进货日期”设置系统当前日期为默认值约束。,n,“货号”设置外键约束引用商品一览表“货号”值并与该表建立关联。,n,“数量”“进价”设置检查约束必须大于0。,n,“供货商ID”设置外键约束引用供货商表“供货商ID”值并建立关联。,n,“收货人”设置外键约束引用员工表“姓名”值并与该表建立关联。,5、,销售表2006（见表1.25）,n,“货名”“金额”允许为空，其余字段均不允许为空。,n,“序号”为主键；并设置为自动产生序号的自动编号字段“标识列”。,n,“销售日期”设置系统当前日期为默认值约束。,n,“货号”设置外键约束引用商品一览表“货号”值并与该表建立关联。,n,“单价”“数量”设置检查约束必须大于0。,n,“金额”运行时由“单价*数量”自动计算，可设置默认值为0。,n,“销售员”设置外键约束引用员工表“姓名”值并与该表建立关联。,1.7 实训要求,1、理解数据库、数据库管理系统、数据库系统以及数据模型等数据库的基本理论。2、按照【实例练习1-11-5】的方法独立绘制出电脑器材销售管理数据库的各个数据表，通过电脑器材销售管理数据模型的创建，理解数据库的关系模型、数据表的基本概念与数据库设计过程。学会合理设计数据表、确定数据表的关联、保证数据的完整性，掌握关系数据库的规范化设计。3、医院住院部有若干科室，每个科有若干医生和病房，病人住在病房中由某个医生负责治疗。每个医生只能属于一个科，每个病房也只能属于一个科。一个病房可住多个病人，一个病人由固定的医生负责治疗，一个医生负责多个病人。设计该住院部系统的E-R模型和关系模型。,1.7 练习题,1.1、关系数据模型中，实体用_来表示，实体间的联系用_来表示。1.2、_是位于用户与操作系统之间的一层数据管理软件。数据库在建立、使用和维护时由其统一管理、统一控制。1.3、目前最常用的数据模型有_、_和_。20世纪80年代以来，_逐渐占主导地位。1.4、数据模型的三要素包括_、_和_。1.5、关系的主键是,，外键是,。1.6、数据库的实体完整性要求表中所有_惟一，可通过创建_、_、_、_等约束来实现。,1.7、数据库的参照完整性要求有关联的两个或两个以上数据表之间的数据_。数据库参照完整性可通过创建_和_约束来实现。,1.7 练习题,1.8、数据库域完整性可保证表中指定字段中数据的_。要求表中指定列的数据具有正确的_、_和_。1.9、在一个表上能创建_个主键约束，主键值_为空。在一个表上能创建_个惟一约束，惟一值_为空。1.10、外键约束用来建立两个表之间的关联。外键列的取值可以为_，可以有_值，但其值必须是引用列的值之一。引用列必须是创建了_或_的列。1.11、若为某公司开发一个逻辑模型：公司有10个部门，每个部门有6-7个员工，但每个员工可能会在不止一个部门工作。下面所给的模型正确的是（ ）。a) 部门和员工之间是一种确定的一对多的关系b) 建立一个关联表，从该关联表到员工建立一个一对多的关系，然后再从该关联表到部门表建立一个一对多的关系c) 建立一个关联表，从员工表到该关联表建立一个一对多的关系，然后再从部门表到该关联表建立一个一对多的关系d) 这种情况不能建立正常的数据库模型,1.7 练习题,1.12、假设有一个学生信息表（StuInfo）的设计如下： StuID（学号） Name（姓名） Address（家庭住址） Department（所在系） DepartmentHead（系主任）该表最高满足第（ ）范式。a) 一 b) 二c) 三 d) 不满足任何范式；DBA的职责有哪些？1.13、数据处理技术经历了哪几个阶段？1.14、数据库管理系统有哪些主要功能？1.15、数据库系统通常由哪几部分组成？1.16、数据库系统的体系结构如何？1.17、有哪些常用的数据模型？各有什么特点？1.18、数据完整性包括哪些内容？1.19、解释以下术语。 关系 属性 元组 关键字 主键 外键 候选键,1.7 练习题,1.20、SQL Server 数据库中有哪几种约束？各有什么作用？1.21、以班级为单位设计一个学生学籍管理数据库，包括学生信息表以及若干个第X学期成绩表。,1.22、指出下列关系各属于第几范式。(1) 学生（学号，姓名，课程号，成绩）(2) 学生（学号，姓名，性别）(3) 学生（学号，姓名，所在系名，所在系地址）(4) 员工（员工编号，基本工资，岗位级别，岗位工资，奖金，工资总额）(5) 供货商（供货商编号，零件号，零件名，单价，数量）,