SQLServer与数据库应用开发课件.ppt

SQL Server与数据库应用开发,第1章 数据库技术基础,1.1.数据管理技术及发展阶段1.2.数据库技术基本理论1.3.关系型数据库基础1.4.关系型数据库的设计,主要内容,【知识要求】：1.了解数据管理技术及其发展阶段的特点。2.掌握数据库技术基本概念与数据库设计的基础知识。【能力要求】：1.具备进行数据库需求分析和数据抽象的能力。2.能根据实际应用情景进行数据库模型设计。,知识与能力要求,1.1.1 信息与数据 1.信息 信息是客观世界中的人或者事物的性质或者特征在人脑中的反映。2.数据 数据是信息的表示形式，是信息存在的载体。信息是数据的灵魂。,1.1 数据管理技术及其发展阶段,1.1.2 数据管理 数据管理（Data Management）就是指对数据进行收集、组织、存储、加工、传播和利用的一系列活动的总和。由于技术背景不同，数据管理经历了人工管理、文件管理、数据库管理三个阶段。1.人工管理利用简单记录工具或者纸张来记录数据，20世纪50年代中期出现了计算机，没有专门数据管理软件。特点：（1）系统没有专用的软件对数据进行管理（2）数据不具有独立性。（3）数据不能共享,1.1 数据管理技术及其发展阶段,2.文件管理阶段20世纪50年代后期到60年代中期，数据管理方式是把计算机中的数据组织成相互独立的、被命名的数据文件。特点：（1）文件系统实现文件系统的结构化，文件内部没有结构化。（2）数据冗余度大，维护困难（3）数据共享性、独立性差。,图1.1文件系统管理数据,1.1 数据管理技术及其发展阶段,3.数据库系统管理阶段20世纪60年代后期，出现了大容量磁盘，使数据能为尽可能多的应用程序服务。数据不再只针对某一个特定的应用，而是面向全组织，具有整体的结构性，共享性高，冗余度减小，具有一定的程序与数据之间的独立性，并且对数据进行统一的控制。特点：（1）数据结构化。（2）数据共享性、独立性高（3）数据冗余小。（4）数据由数据库管理系统（DBMS）软件统一管理和控制,图1.2数据库系统管理数据,数据库管理系统提供以下四方面的数据管理功能。（1）数据安全性控制：防止因不合法使用数据而造成数据的泄露和破坏，保证数据的安全和机密。（2）数据的完整性控制：系统通过设置一些完整性规则，以确保数据的正确性、有效性和兼容性。（3）并发控制：多用户同时读取或修改数据库时，防止相互干扰而给用户提供不正确的数据（脏数据），并使数据库受到破坏。（4）数据恢复：当数据库被破坏或数据不可靠时，系统有能力将数据库从错误状态恢复到最近某一时刻的正确状态。,1.1 数据管理技术及其发展阶段,1.2.1 数据库基本概念1.数据库（Database,DB）数据库是指长期存储在计算机内的、有组织的、可共享的数据集合。2.数据库管理系统（Database Management System,DBMS）数据库管理系统是数据库的管理机构，它是一个系统软件，负责数据库中的数据组织、数据操纵、数据维护、控制及保护和数据服务等。负责数据库创建、维护、安全管理的人员，叫数据库管理员（Database Administrator,DBA）3.数据库系统（Database System,DBS）是指引进数据库技术后的整个计算机系统，它能够实现有组织地、动态地存储大量相关数据，提供数据处理和信息资源共享的便利手段。,1.2 数据库基本理论,图1.3 数据库系统结构图,1.2.2 数据模型数据模型是现实世界特征的数据化抽象，是对现实世界的数字化模拟与表示。数据模型应满足三方面的要求：一是能针对性地模拟现实世界中事物的主要特征及其相互关系；二是容易理解和表示；三是便于在计算机系统中实现。将数据模型可以分为两类：一类是概念模型，它是面向现实世界来对数据和信息进行抽象，主要用于数据库设计，是数据模型建立的第一个阶段；另一类是结构数据模型，它是面向计算机系统，便于在计算机中实现，主要用于在DBMS的实现，是数据模型建立的第二个阶段。,1.2 数据库基本理论,1.概念模型概念模型是现实世界到信息世界的第一次特征化的抽象，用于信息世界的建模，是数据库设计人员的重要工具，（1）实体（entity）：就是我们关心的数据对象,同种实体的集合，就是实体集。（2）属性（Attributes）:实体所具有的某一特性称为实体的属性。（3）联系（Relationship）：联系在信息世界中表现为实体型内部各属性之间的联系以及实体型之间的联系。,一对一联系（1:1）：若对于实体集A中的每一个实体，实体集B中至多有一个实体与之联系，反之亦然，则称实体集A与实体集B具有一对一的联系。一对多联系（1:n）：若对于实体集A中的每一个实体，实体集B中有n（n2）个实体与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B有一对多的联系。多对多联系（m:n）：若对于实体集A中的每一个实体，实体集B中有n（n2）个实体与之联系，反过来，对于实体集B中的每一个实体，实体集A中也有m（m2）个实体与之联系，则称实体集A与实体集B有多对多的联系。,1.2 数据库基本理论,（4）E-R图E-R图提供了表示实体型、属性和联系的方法。实体：用矩形表示，矩形框内写实体名。属性：用椭圆形表示，椭圆内写属性名，用无向边将属性与实体连接起来。联系：用菱形表示，菱形框内写联系名，用无向边与有关实体连接起来，同时在无向边上注明联系类型。联系也具有属性，也要用无向边将联系与有关实体连接起来。,【案例1.1】用班长、班级、学生、课程实体的E-R图说明实体之间的联系类型。（参考结果E-R如图1-4所示）,1.2 数据库基本理论,图1-4 实体及联系图,【案例1.2】设计“学生”与“班级”之间的详细E-R图。参考结果E-R如图1-5所示。,1.2 数据库基本理论,图1-5“班级”与“学生”E-R图,1.2 数据库基本理论,1.结构数据模型在数据库技术中，包含数据结构、数据操作和数据的完整性约束的数据模型称为结构数据模型。数据结构是所研究的对象类型的集合，用于描述系统的静态特性，是数据模型的基础。数据操作是对数据进行的添加、删除、查询、修改等各种操作。完整性约束是保证数据库中数据在语法与语义上的正确性、一致性。结构数据模型主要类型有：层次模型、网状模型、关系模型。（1）层次模型：呈树状结构，只有一个根结点没有父结点，其它所有元素有且只有一个父结点。（2）网状模型：呈网状结构，可以有一个以上结点没有父结点，其它所有元素可有多个父结点。（3）关系模型：用二维表来表示实体及实体之间联系的模型称为关系模型，支持关系模型的数据库管理系统称为关系型数据库管理系统。,基于关系模型创建和管理的数据库就称为关系数据库。关系数据库以其结构简单，描述性强、使用方便、完备的理论基础等优点，成为当今使用最为广泛的数据库。1.3.1 关系模型关系模型由三部分组成：数据结构、关系操作、关系的完整性。在介绍三个组成部分之前，先来了解关系模型的基本术语。（1）关系模型（relational model）：用二维表格结构来表示实体及实体间联系的模型称为关系模型（如图1-6）。（2）关系模式（relation schema）：在二维表格中，一个二维表就是一个关系，对表中各列名称和数据类型的定义称为关系模式。（3）属性（attribute）和值域（domain）：在二维表中的列称为属性，列值称为属性值，属性值的取值范围称为值域。（4）元组（tuple）：在二维表中的行（记录）称为元组。,1.3 关系型数据库,（5）主键（primary key）或主关键字：在关系的属性中，能够用来唯一标识元组的属性或属性组合称为主键或主关键字。关系中的元组由主键或主关键字的值唯一确定，主键不能为空。例如，学生表中的学生编号可以作为主键。（6）候选关键字（candidate key）：如果一个关系中，存在着多个属性或属性的组合都能用来唯一标识该关系的元组，这些属性或属性的组合称为该关系的候选关键字。（7）外键（foreign key）：当关系中的某个属性或属性组合虽不是该关系的主键或只是主键的一部分，但却是另一个关系的主键字时，该属性或属性组合称为这个关系的外键。主键所在的表称为主表，外键所在的表称为从表。,1.3 关系型数据库,1.3 关系型数据库,图1-6 关系模型主要概念示意图,1.关系模型的数据完整性数据完整性是指关系模型中数据的正确性与一致性。关系模型允许定义四类完整性约束：实体完整性约束、域完整性、参照完整性约束和用户自定义完整性约束。关系型数据库系统提供了对实体完整性约束、域完整性约束、参照完整性约束的自动支持机制，也就是在插入、修改、删除、更新操作时，数据库系统自动保证数据的正确性与一致性。（1）实体完整性规则（entity integrity rule）：这条约束要求关系中的元组在组成主键的属性列上的值的唯一性，且不能为空。例如，学生信息表中的学号值保证了记录的唯一性，它不能为空。（2）域完整性规则（domian integrity rule）：这条约束要求关系中的属性列上的值一定范围内取值才有意义。例如，学生信息表中性别只能取“男”或者“女”。（3）参照完整性规则（reference integrity rule）：这条约束要求在从表中不能有主表中不存在的元组，即，外键值在主表中必须存在。（4）用户自定义完整性规则（user-defindedintegrity rule）：用户自定义完整性规则是根据应用领域的需要，由用户自己定义的约束方式，体现了具体应用领域的语义与数据一致性约束。,1.3 关系型数据库,2关系操作关系操作的数学基础是关系数学，为关系数据库的数据操作提供了数学依据。在数据库中表现在关系数据库的操作语言之中。SQL（Structured Query Language）语言是关系数据库的标准语言，它是以关系代数为基础，是提供了数据定义、数据查询和访问控制功能的语言。（1）数据定义语言(Data Definition Language，DDL)：用于定义和修改数据库、数据库对象的语言。主要用到关键字有：CREATE、ALTER、DROP。（2）数据操纵语言(Data Manipulation Language，DML)：主要包括SELECT、INSERT、DELETE、UPDATE四个关键字。（3）数据控制语言(Data Control Language,DCL)：主要用来控制用户对数据的存取权力。某个用户对数据库的操作是由数据库管理员来决定和分配的，数据库访问控制功能保证这些安全策略的正确执行。SQL通过授权语句GRANT和回收语句REVOKE来实现访问控制功能。（4）SQL语言使用方式：SQL具有两种使用方式，既可以作为独立的语言在终端交互方式下使用，又可以将SQL语句嵌入某种高级语言（如C、C+、Java等）之中使用。嵌入SQL的高级语言称为主语言或宿主语言。,1.3 关系型数据库,1.3.2 关系数据理论对一个具体的数据库应用问题，根据E-R图，应该构造几个关系模式？每个关系由哪些属性组成？即如何构造适合于它的数据模式，这是关系数据库逻辑设计的问题。1问题的提出对于在线考试系统的数据库设计，假如根据概念模型的E-R图得出了三个关系：学生关系：学生（学号，姓名，性别，出生日期，班级编号，系部编号）。课程关系：课程（课程号，课程名）。成绩关系：成绩（学号，课程号，成绩）。那么这三个关系应当形成几个关系模式？（1）只有一个关系模式：学生-课程-成绩（学号，姓名，性别，出生日期，班级编号，系部编号，课程号，课程名，成绩）。a）数据冗余：增加一门新课程必须添加学生信息，多门课时，同一学生信息多次添加。b）修改异常：修改课程名称，要修改所有学生的记录，否则异常c）插入异常：不添加学生信息无法添加课程d）删除异常：删除学生成绩，学生信息会被 删除。,1.3 关系型数据库,（2）有三个关系模式：学生，课程，成绩。消除了数据冗余，也消除了插入、删除、修改异常。但又出现了另外一些问题，如果要查找考过“数据库技术”课程的学生姓名，则需要进行三个关系的连接操作，这样代价很高。相比之下，学生-课程-成绩关系直接投影、选择就可以完成，代价较低。如何找到一个好的数据库模式？如何判断是否消除了上述问题？这就是关系数据理论研究的问题。其中核心问题就是关系模式的规范化。,1.3 关系型数据库,2、关系模式的规范化 关系数据库中的关系要满足一定的要求，满足不同程度要求就是满足不同的范式，满足最低要求的叫第一范式，简称1NF，在第一范式中进一步满足一些要求的为第二范式，其余范式依此类推。在关系数据库设计中，关系模式应当至少满足第三范式。,1.3 关系型数据库,（1）第一范式：如果关系模式R的每一个属性都是不可分解的，则R为第一范式的模式，记为：R1NF。例如，有关系：学生（学号，姓名，性别，出生日期，班级编号，系部编号，亲属），“学生”关系不满足第一范式，因为亲属可以再分解为“父亲”、“母亲”等属性。解决的方法是将“学生”关系分解为学生（学号，姓名，性别，出生日期，班级编号，系部编号）和家庭（学号，亲属姓名，亲属关系）两个关系模式。第一范式的功能是实现属性的不可再分性，也就是原子性，不满足第一范式，就不能称之为关系数据库。不是1NF的关系都是非规范化关系，满足1NF的关系称为规范化的关系。数据库理论研究的关系都是规范化的关系。1NF是关系数据库的关系模式应满足的最起码的条件。,1.3 关系型数据库,（2）第二范式：如果关系模式R是第一范式，且每个非主属性都完全函数依赖于关键字，则称R为满足第二范式的模式，记为：R2NF。例如，有关系：成绩（学号，姓名，性别，出生日期，课程号，成绩）。“成绩”关系不满足第二范式，因为“成绩”属性完全依赖于主关键字（学号，课程号），而“姓名”、“出生日期”属性只部分依赖于主关键字“学号”，所以，不是每一个非主关键字属性都完全函数依赖于主关键字属性，出现部分依赖。部分依赖会导致数据冗余、和插入、删除、修改异常。解决的方法是将“成绩”关系投影分解为成绩（学号，课程号，成绩）和学生（学号，姓名，性别，出生日期）两个关系模式。第二范式的功能就是防止有非主属性部分依赖于主关键字，如果存在，就应当将其分离出来做为另一实体的属性。,1.3 关系型数据库,（3）第三范式：如果关系模式R是第二范式，且没有一个非关键字属性是传递函数依赖于候选关键字属性，则称R为满足第三范式的模式，记为：R3NF。例如，有关系：学生（学号，姓名，性别，出生日期，班级编号，系部编号，系部名称，系主任），非主关键字“系部编号”完全依赖于“学号”，“系主任”完全依赖于“系部编号”，导致“系主任”传递依赖于“学号”。传递依赖会导致数据冗余、和插入、删除、修改异常解决方法是将“学生”关系分解为学生（学号，姓名，性别，出生日期，班级编号，系部编号）和系部（系部编号，系部名称，系主任）。第三范式功能是防止出现属性依赖于非主属性，也就是不能包含其它关系已经包含的非主关键字。,1.3 关系型数据库,1.4.1 数据库设计的任务对数据库进行合理的逻辑设计和有效的物理设计才能开发出完善而高效的信息系统。数据库设计是信息系统开发和信息系统建设的重要组成部分。从一个应用软件开发的角度讲，一般一个数据库应用系统的开发流程如图1-7所示。,1.4 数据库设计,图1-7 数据库应用系统开发流程图,1.4.2 需求分析需求分析阶段的任务主要包括以下几个方面。(1)分析用户活动，产生业务流程图。了解用户当前的业务活动和职能，理清其处理流程。把用户业务分成若干个子处理过程，使每个处理功能明确、界面清楚，并画出业务流程图。(2)确定系统范围，产生系统范围图。在和用户经过充分讨论的基础上，确定计算机所能进行数据处理的范围，确定哪些工作由人工完成，哪些工作由计算机系统完成，即确定人机界面。(3)分析用户活动所涉及的数据，产生数据流图。深入分析用户的业务处理，以数据流图形式表示出数据库的方向和对数据所进行的加工。数据流图有四个基本成分：数据流、加工或处理、文件、外部实体。(4)分析系统数据，产生数据字典。数据字典提供对数据库中数据描述的集中管理，其功能是存储和检索各种数据描述。数据字典是数据收集和数据分析的主要成果，在数据库设计中占有很重要的地位。,1.4 数据库设计,2.需求分析的方法主要的方法有自顶向下和自底向上两种，其中自顶向下的结构化分析方法(Structured Analysis，SA)是一种简单实用的方法。通过SA方法，得到数据流图，然后用数据字典描述数据项。数据流图表达了数据和处理的关系，数据字典则是系统中各类数据描述的集合，是各类数据结构和属性的清单。最终形成的数据流图和数据字典是“系统需求分析说明书”的主要内容，是下一步进行概念结构设计的基础。,1.4 数据库设计,【案例1.3】对在线考试系统的用户进行角色分析。在线考试管理数据库系统通常的用户为学生、教师、管理员。他们实际都是考试系统的用户，只是角色不同，登录后访问不同的界面，进行不同的操作。,1.4 数据库设计,【案例1.4】对在线考试系统用户业务和数据流分析。业务分析：（1）教师业务分析：教师的业务有试题录入、核对题库的任务，被授权后通过用户名与密码验证登录。在监考时，要求登录并根据监考要求设置试卷、变动考生机位、考试结束向系统发出阅卷指令。（2）管理员业务分析：管理员负责对注册教师的身份确认，有自己的登录户名与密码验信息。（3）学生业务分析：学生注册后通过用户名与密码验证登录。在考试时，学生根据自动生成的试卷做题、提交试卷、考试结束可查阅成绩。,【案例1.5】设计完成在线考试系统的数据流图。,1.4 数据库设计,图1-8 在线考试系统数据流图,1.4.3 概念结构设计1概念结构设计的方法和步骤概念结构设计的方法有如下四种：（1）自顶向下：首先定义全局概念结构的框架，然后逐步细化。（2）自底向上：先定义每一局部应用的概念结构，然后按一定的规则将其集成，得到全局的概念结构。（3）逐步扩张：首先定义核心结构，然后向外扩展。（4）混合结构：就是将自顶向下和自底向上结合起来，先确定概念结构的框架，再用自底向上设计局部概念结构，最后结合起来。,1.4 数据库设计,（1）设计局部E-R模型在设计E-R模型的过程中应遵循一个原则：现实世界中的事物能作为属性对待的，尽量作为属性对待。可以作为属性对待的事物有下列两类：作为属性，不能是再具有需要描述的性质。属性不能与其他实体具有联系。,1.4 数据库设计,（2）设计全局E-R模型：将所有局部的E-R图集成为全局的E-R图，一般采用两两集成的方法，即先将具有相同实体的E-R图，以该相同的实体为基准进行集成，如果还有相同的实体，就再次集成，这样一直继续下去，直到所有具有相同实体的局部E-R图都被集成，从而得到全局的E-R图。在集成的过程中，要消除属性、结构、命名三类冲突，做到合理的集成。,（3）全局E-R模型的优化：一个好的全局的E-R模型除了能反映用户的功能需求外，还应做到实体个数尽可能少，实体类型所含属性尽可能少，实体类型间的联系无冗余。全局E-R模型的优化就是要达到这三个目的。可以采用以下优化方法：（1）合并相关的实体类型：把1:1的两个实体类型合并，合并具有相同键的实体类型。（2）消除冗余属性与联系：消除冗余主要采用分析法，并不是所有的冗余都必须消除，有时为了提高效率，可以保留部分冗余。,1.4 数据库设计,【案例1.6】根据需求分析与数据流图1.8，设计出在线考试系统数据库的E-R图。,1.4 数据库设计,图1-10 在线考试系统全局E-R图,1.4 数据库设计,1.4.4 逻辑结构设计概念结构是独立于任何数据模型的信息结构。逻辑结构设计的任务就是将概念模型E-R模型转化成特定的DBMS系统所支持的数据库的逻辑结构。1逻辑结构设计的步骤关系数据库逻辑结构设计一般分为以下三步：（1）将概念结构向一般的关系模型转换。（2）将转换来的关系模型向特定的RDBMS支持的数据模型转换。（3）对数据模型进行优化。,1.4 数据库设计,2E-R模型向关系数据库的转换规则E-R模型向关系数据库的转换规则是：（1）一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的关键字就是关系的关键字。（2）一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式，则相连的每个实体的关键字及该联系的属性是该关系模式的属性，每个实体的关键字是该关系模式的候选关键字。（3）一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。如果转换为一个独立的关系模式，与该联系相连的各实体的关键字及联系本身的属性均转换为关系的属性，而关系的关键字为n端实体的关键字。（4）一个m:n联系转换为一个关系模式，与该联系相连的各个实体的关键字及联系本身的属性转换为关系的属性，而该关系的关键字为各实体的关键字的组合。（5）三个以上的实体间的一个多元联系可以转换为一个关系模式，与该多元联系相连的各实体的关键字及联系本身的属性转换为关系的属性，而该关系的关键字为各实体关键字的组合。,1.4 数据库设计,3关系数据库的逻辑设计关系数据库逻辑设计的过程如下：（1）导出初始的关系模式：将E-R模型按规则转换成关系模式。（2）规范化处理：消除异常，改善完整性、一致性和存储效率，一般达到3NF即可。（3）模式评价：检查数据库模式是否能满足用户的要求，包括功能评价和性能评价。（4）优化模式：采用增加、合并、分解关系的方法优化数据模型的结构，提高系统性能。（5）形成逻辑设计说明书。,1.4 数据库设计,【任务1.7】完成在线考试系统数据库的关系模式（1）用户、系统、班级关系模式对于实体教师、学生、管理员三者属性基本相似，主要用来表示并存储身份验证、状态、部门信息，可以将三者通过角色区分，归纳到一个用户关系，得到如下关系。用户（用户编号，用户姓名，用户密码，用户角色，状态，机位，班级，系部）其中，对于教师与管理员，其班级信息无明确意义，但融合到一个关系中，便于数据访问，机位用IP地址来标识。教师要添加考试课程、班级、系部信息，这时不需要添加用户信息，根据第二、三范式，建立单独的系部与班级关系，最后得到以下三个关系：系部（系部编号（主键），系部名称）班级（班级编号（主键），班级名称，系部编号）用户（用户编号（主键），用户姓名，用户密码，用户角色，状态，IP地址，班级编号，系部编号）,1.4 数据库设计,（2）课程关系课程（课程编号（主键），课程名称）（3）单选题、多选题、判断题关系如果在一个题库关系中存储单选题、多选题、判断题，会由于各种类型题目的属性不同而无法统一，也不利于生成试卷，因此，将三种试题用三个关系存储：单选题（课程编号，题目序号（主键：课程编号+题目序号），题目，选项A,，选项B,，选项C,，选项D,参考答案）多选题（课程编号，题目序号（主键：课程编号+题目序号），题目，选项A,，选项B,，选项C,，选项D,参考答案）判断题（课程编号，题目序号（主键：课程编号+题目序号），题目，,参考答案）,1.4 数据库设计,（4）试卷设置关系实际上试卷设置关系是个导出实体。由于在题库中有三种类型的题目，在试卷设置中就有三种类型的题目各各自的分值，而且一门课在同一天每个人只能考试一次，完善后得到如下关系：试卷设置（课程编号，考试日期（主键：课程编号+考试日期），单选题数目，单选分值，多选题数目，多选分值，判断题数目，判断分值）,1.4 数据库设计,（5）试卷关系由于同一门课程，同一天每个人只能考试一次，优化试卷实体的属性后，得到如下关系：试卷（用户编号，课程编号，考试日期（主键：用户编号+课程编号+考试日期），试题类型，试题在试卷中序号，试题在题库中序号，考生答案）（6）成绩关系由于一个考生，对同一门课只有一个成绩，成绩表中就不需要日期区分。得到成绩关系：成绩（用户编号，课程号（主键，用户编号+课程号），成绩）,1.4 数据库设计,【案例1.7】考虑到后期数据库学习需要，请设计一个学生关系，包含较多的学生属性信息。学生(学号，姓名，性别，生日，班级编号，系部编号),见习题与答案,习题,