软件开发技术--数据库

数据库技术,第五章,数据库设计过程,E/R图到关系模式的转换,关系模式的规范化,需求分析,概念模式设计,逻辑模式设计,数据库实施,E/R图UML,RDBMS模式,存储模式,数据库运行和维护,什么是概念数据建模,对系统中数据的描述描述类间的各种关系,概念数据建模过程,概念数据建模方法,UMLER,概念数据建模-UML,类被表示为由3个部分组成的矩形上面部分给出了类的名称，类名用粗体，背景颜色较深。中间部分给出了该类对象的属性。下面部分给出了对象的约束条件以及可以应用到这些对象的操作。其中第二和第三个部分之间，用一条线间隔开来。,UML例子,UML关系表达,关系是一个或多个类的对象之间的语义连接一个关系可以是因为实体之间的自然联系而造成的结果，例如，一个教师属于某一个院系；也可能是由于发生了某一个事件，例如一个学生选了某门课。,关系的度（或元数）,参与一个关系的类的数目,一元关系,二元关系,关系的重数,一个类的对象与另一个类的对象存在的数目上的关系0.1：表示最小值是0，最大值是1。*（或0.*）：表示范围从0到无穷大。0.n：最小为0，最大为n。单个1：代表1.1，表示关系中参与的对象数目恰好是1,关系的重数举例,关联,关联是一种关系关联的2个类间产生属性,概念模式设计ER图,实体联系模型（Entity-RelationshipModel，简称E-R模型）P.P.Chen于1976年在“TheEntity-RelationshipModel:TowardaUnifiedViewofData”,ACMTransactionsonDatabaseSystems,Vol1,Num1(1976).论文中提出的。模型直接从现实世界中抽象出实体类型及实体联系.用实体联系图即E-R表示数据模型。其核心就是设计E-R图（Entity-RelationshipDiagram）。,E-R模型的基本概念,实体(entity)：客观存在，可以相互区别的事物称为实体。实体集(entityset)：性质相同的同类实体的集合，称实体集。属性(attribute)：实体有很多特性，每一个特性称为属性。每一个属性有一个值域，其类型可以是整数型、实数型、字符串型等。实体标识符(identifier)：能惟一标识实体的属性或属性集，称为实体标识符。有时也称为关键码(key)，或简称为键。联系(relationship)是实体之间的相互关系。数据模型:能表示实体类型及实体间联系的模型称为“数据模型”。,实体表达,E-R图的基本组成部分,E/R图有四个主要部分：（1）实体集，用矩形表示。（2）属性，用椭圆形表示。（3）联系，用菱形表示。（4）直线，表示联系的类型及实体与实体之间的联系；,数据联系,联系（relationship）是实体之间的相互关系。二元联系有以下三种类型：一对一联系：实体集E1中每个实体至多和实体集E2中的一个实体有联系，反之亦然，那么实体集E1和E2的联系称为“一对一联系”，记为“1:1”。一对多联系：实体集E1中每个实体可以与实体集E2中任意个（零个或多个）实体间有联系，而E2中每个实体至多和E1中一个实体有联系，那么称E1对E2的联系是“一对多联系”，记为“1:N”。多对多联系：如果实体集E1中每个实体可以与实体集E2中任意个（零个或多个）实体有联系，反之亦然，那么称E1和E2的联系是“多对多联系”，记为“M:N”。,三种联系的E-R图,三种联系的E-R图实例,E-R图实例,例：设计一个学生选课数据库。要求包括学生(student)、课程(course)和教师(teacher)的信息。实体中存在如下联系：一个学生可以选修多门课，而每门课也能由多个学生选修；一位教师可以教多门课，每门课也可以由多个教师讲授；用E-R图来描述此数据库。,学生信息：学号(no)、学生姓名(name)、年龄(age)、地址(dddress),课程信息：课程号(no)、课程名(name)、课程学分(credits),教师信息：工号(no)、姓名(name)、教师的地址(address),学生-课程-教师E-R图,注意：如果一个联系具有属性，则这些属性也与该联系连接起来。,TeachCourseNo,条码,白酒,容量,度数,编号,销售商,联系人,地址,电话,名称,销售,数量,单价,E-R图实例白酒销售,IBM研究员E.F.Codd（EdgarFrankCodd，1924-2003）于1970发表了业界第一篇关于关系数据库理论的论文“ARelationalModelofDataforLargeSharedDataBanks”，首次提出了关系模型的概念。这篇论文是计算机科学史上最重要的论文之一，也奠定了Codd博士“关系数据库之父”的地位。E.F.Codd因此获得ACM图灵奖（1981年）。,关系数据模型,关系描述,关系名(关键属性，属性1，属性2，属性N)Student(studentNo，studentName，birthday，sex),关系基本概念,关系：对应通常所说的二维表。元组：表中的一行即为一个元组属性：表中的一列即为一个属性，例如下表有4列，就有4个属性（studentNo,studentName,birthday,sex）。主键：表中的某个属性组(可以多属性组合成主键)，它可以唯一确定一个元组，例如，studentNo可以唯一确定一个学生，也就成为学生关系的主键；域：属性的取值范围，例如，大学生性别域是（男，女）；分量：元组中的一个属性个数。例如，学生关系分量为4。关系模式：对关系的描述，一般表示为：关系名(属性1，属性2，属性N)。,关系模型实例,关系,关系名,元组：表中的一行即为一个元组或记录；字段：表中的一列，列值称为字段值，列名称为字段名值域：字段值的取值范围,主键：能唯一标识一个元组的属性集合。外键：若属性组F不是关系R1的主键，但它是关系R2的主键，则F称是关系R1的外键。,关系模型查询的实现,通过关联表的方式实现,良构关系,良构关系包含最小的数据冗余，允许操作者插入、删除、修改表中的行而不会引起错误或数据的不一致,良构关系-关系的规范化,1、问题,数据冗余修改异常插入异常删除异常,学号课程学分100人工智能3125文化学2150市场营销学2175人工智能3190文化学2,更新异常：删除学号为150的学生的选课记录，丢掉了学生150选修“市场营销学”的事实，还失去了“市场营销学”的学分是2的事实。插入异常：若有一门“法律”课，学分为3，但无学生选修时，不能输入。,消除异常的方法：模式分解,若将以上的学生关系分解成学生-选课、课程-学分关系，更新异常、插入异常就都可消除了，对关系进行分解的过程就是规范化过程。,学生选课关系,课程学分关系,属性间有三种联系,1)函数依赖：给定一个属性的值，可以获得另一个属性的值。例如，若已知“课程名”的值，便可知道“授课学时”。“授课学时”依赖于“课程名”，或“课程名”决定“授课学时”。记作“课程名授课学时”。,2)部分依赖：下表的主键是属性集合学号，课程号。主键学号，课程号决定了“学分”的值。但“学分”只由“课程号”决定，与“学号”无关，也就是“学分”属性只由主键学号，课程号的一部分而不是全部来决定，把这种依赖称为部分依赖。记作“学分学号，课程号”。,3)传递依赖：下表的主键是“学号”，学生住宿的楼号依赖于学号。但是，学生应交的住宿费是由楼号决定的，也就是说，“收费”依赖于“楼号”，这是一种新的依赖关系：“楼号”依赖于“学号”，而“收费”又依赖于“楼号”。一般把这种依赖关系称为“传递依赖”。记作“学号收费”。,1）第一范式（FirstNormalForm，1NF）,定义：如果一个关系模式R的所有属性都是不可分的基本数据项，则关系R属于第一范式。注意：集合、数组和结构都不能为第一范式关系中属性的类型。符合第一范式的关系可能有插入、删除异常。例如：在关系“选课”中，存在着部分依赖：学分学号，课程号将导致插入、删除异常。,p,2）第二范式（SecondNormalForm，2NF）,定义：若关系模式R属于第一范式，且每个非主属性都完全函数依赖于主键，则R属于第二范式。注意：单个属性作为主键的关系自动就是第二范式。第二范式中的关系存在更新异常.例如：(学号、楼号、收费)，虽然学号是单属性主键，属于第二范式，而楼号、收费都由学号决定，存在传递依赖关系：“学号收费”将导致更新异常。,t,3）第三范式,定义：如果一个关系R是第二范式的，并且每个非主属性都不传递依赖于主键，则R属于第三范式。例如：学生住宿关系可以分解为两个关系：学生-楼号关系（学号，楼号）和楼号-收费关系（楼号，收费）。这两个关系属于第三范式。,UML到关系模型的转换类转换,系模式名就是类名，关系模式的属性由原类中的各属性组成，关系模式的主键就是原类的主键,Student(studentNo,studentName,sex,birthday),UML到关系模型的转换关系转换,1:1关联(二元关系重数=1:1)两个类转换成两个关系模式在其中任意一个关系模式的属性集中加入另一个关系模式的主键1:N关联(二元关系重数=1:N)两个类转换成两个关系模式在N端的类转换成的关系模式中加入1端关系模式的主键,1:N关联转换实例,Teacher(TeacherId,teacherName,sex),Deaprtment(DeptId,deptName,location),Teacher(TeacherId,teacherName,sex,deptId),UML到关系模型的转换关联类,关联两端的对象各转换成一个关系模式关联本身也产生一个关系模式，其主键用关联两端对象的主键形成复合键,关联类转换实例,Student(studentNo,.),Course(courseNo,),selectCourse(studentNo,courseNo,grade),4、E-R模型到关系模式的转换,1）实体(对象)到关系模式的转换2）联系(关系)到关系模式的转换,1）实体到关系模式的转换,规则：将实体的名称作为关系的名称，将实体的属性作为关系的属性，并用下划线标识出主属性。,student（sNo,sName,sex,age），表示student关系，其中sNo为主键。course（cNo,cName,credits），表示course关系，其中cNo为主键。,2）联系到关系模式的转换1:1,若实体间联系是1:1，可以在两个实体转换成的两个关系模式后，然后在其中任意一个关系模式的属性集中加入另一个关系模式的主键和联系自身的属性。也就是说1:1联系不单独转换为一个独立的关系。,2）联系到关系模式的转换1:N,若实体间联系是1:N，则在N端实体转换成的关系模式中加入1端实体的主键和联系的属性。同样1:N联系也不独立构成一个关系。,工作,二元关系,2）联系到关系模式的转换M:N,若实体间联系是M:N，则将联系转换成一个关系模式，其属性为两端实体的主键加上联系自身的属性，而联系关系的主键为一复合键，通常是两端实体主键的组合。,对于“学生选课”E/R图的课程和学生之间的多对多的联系，可转换成关系模式：take（sNo,cNo,grade）主键为复合键sNo,cNo。,例：,关系的完整性,域完整性实体完整性参照完整性,关系数据库的组织,表1表2表3,DB,ColumnorField,Record,SQL2000数据库的组成,关系图表视图存储过程用户角色,SQL2000创建数据库,执行“开始”“程序”“MicrosoftSQLServer”“企业管理器”后，进入SQLServer管理在窗口的左边展开数据库，右击数据库项目列表的空白处，在弹出的菜单中选择“新建数据库”，出现“数据库属性”窗口，在该窗口的“常规”选项卡上，输入数据库的名字，例如：ScoreManager。作业创建数据库ScoreManager,SQL2000字段类型,smalldatetime,二战四巨头,富兰克林德拉诺罗斯福约瑟夫维萨里昂诺维奇斯大林温斯顿伦纳德斯宾塞丘吉尔蒋介石,字段的属性,字段长度对于字符类型，例如char和varchar，定义字段的同时可以指定字段存放的字符的个数，例如定义姓名字段为namevarchar(20)，表示该字段最多存放20个字符。数值型的字段，除了decimal由用户自己定义长度和小数位数外，其他的如int,float等，系统自定义其占用的字节数。小数位数用于decimal和货币类型的字段标识种子用于数据库表的记录号的自动增加，经常用于做表中记录的主键。用于整型数值.默认值当表中新增加一个记录时，又不想具体指定某个字段的值。可以给它一个默认的值。非空限制用于限制字段的值不能为空，例如学生的姓名字段，每一个记录都必须有值，因为学生不能没有名字。索引对字段进行索引，可以通过设定该字段为查询条件来加快数据的检索速度和统计、分组的速度。关键字关系模式中的主键，用于唯一标识数据库表中的记录。,进入企业管理器展开数据库，展开表右键，选择创建表输入字段名，确定每个字段的类型保存，为表起名作业：在scoreManager中创建上表字段的属性和约束该如何定义？,Tablet_studentVsCourse创建表作业,展开数据库，然后用鼠标右击ScoreManager数据库选择所有任务生成SQL脚本。出现图导出SQL脚本对话框在”常规”卡上，用鼠标点击”全部显示”按钮，然后选中”编写全部对象”复选框。在“设置格式”卡中，一般选择”为每个对象生成Create命令”和”为每个对象生成Drop命令”两项就可以。在“选项”卡上，根据需要，把“表脚本选项”下的选择项都选中最后选择“确定”按钮，输入脚本的名字作业：上交你创建的数据库scoreManager的SQL脚本.创建三个符合设计规范的表。并输入数据内容，自己保存数据库的备份，待以后使用,获得数据库的SQL脚本*,用脚本迅速恢复数据库*,先创建数据库进入查询分析器把脚本调入到查询分析器执行，恢复数据库,简单的SQL查询语句Select,SELECT字段1,字段2,|*FROM表名WHERE条件表达式,在查询分析器中执行SQL语句,从开始菜单进入查询分析器选择数据库输入Select语句执行,学生课程成绩表?有何缺陷,Tablet_studentVsCourse,建立符合范式的表(作业内容),学生表tblStudent课程表tblCourse学生修课表tblSelectCourse,结构化查询语言SQL,SQL语言是一种关系数据库语言，是结构化查询语言（StructuredQueryLanguage）的缩写，它包括查询定义操纵控制SQL的使用方法有两种用户交互的方式联机使用，称为交互式SQL；另一种是作为子语言嵌入到其他程序设计语言中使用，称为嵌入式SQL。,SQL表达式,SQL的表达式可分为两类：值表达式值表达式包括几种：数值表达式：实现数值型数据的算术运算；字串值表达式完成了字串的操作；日期时间值表达式处理日期时间数据。2.逻辑表达式。逻辑运算包括与（AND）、或（OR）、否（NOT）。比较谓词（=、!=、=）、范围谓词（IN、LIKE、IS）和存在谓词（EXIST）。,函数,SQL内置函数有三类：统计函数:COUNT、SUM、AVG、MAX和MIN字串处理函数len,substring(studentName,1,2)大小写转换函数（Upper,Lower）时间日期处理函数。getDate(返回当前日期时间)。Year(dateTime类型字段)Month(dateTime类型字段)Day(dateTime类型字段),数据检索语句select,语法格式为：SELECTALL|DISTINCT目标列FROM表1,表2,表n（或视图）as表别名inner|Left|right|fullouterjoin表on关系表达式WHERE条件表达式GROUPBY列名HAVING条件表达式ORDERBY列名ASC/DESC说明DISTINCT为去掉查询结果中重复的行，默认为ALL，既列出所有的记录；目标列的格式可以写为：列名1as别名1,列名nas别名n，省略别名，返回的结果集中以列名标记，否则以别名标记，如果目标列用*表示，则返回表中的所有的列；From子句指明了从那些表或视图中得到数据，所有的列名都必须存在于From后的表中,或出现于leftjoin,innerjoin,fullouterjoin之后的表，如果列名存在于From后的多个表中，则必须标明该列来源于哪个表，格式为表名.列名；目标列可以是SQL中的库函数或常数表达式。如Sum、Count、Avg等函数。,Select语句,Where子句有双重作用，一是返回需要的记录，另一种是建立多表之间的内连接联合查询。GROUPBY用来对查询结果进行分组，把某一列值相同的记录分成一组，与统计函数如Sum、Count等联合使用；在有Group子句时，可以进一步使用HAVING子句对分组后的记过进行过滤显示，是分组提取条件；ORDERBY是查询结果的排序方式，后面跟ASC为升序，DESC为降序,默认为升序。,关系实例,学生表tblStudent课程表tblCourse学生修课表tblSelectCourse,SQL例,例1：查看学生表中的全部数据，即列出所有的学生Select*fromtblStudent这里*代表表中所有的列，上述的语句等价如下的语句：SelectstudentNo,studentName,birthday,sexFromtblStudent这时，表中有多少条记录，数据库系统就列出多少条记录。,Where子句子,例2：查询学生表tblStudent中的全部女生的学号、姓名和年龄SelectstudentNo,studentName,birthdayfromtblStudentwheresex=女例3：查询学生修课表中成绩大于80分的学生：Select*fromtblSelectCoursewherescore80在where条件中，可以使用逻辑运算符和比较关系符，包括：=、!=、=。也可以使用关键字，如：between、like、in等。注意，如果值是字符串或者字符、日期类型的，就要用单引号把它括起来，例如例2中的where条件。例4查询操作系统成绩在70到80之间的学生的学号selectstudentNofromtblSelectCoursewherescorebetween70and80andcourseNo=J003,日期型数据处理,罗列出每个同学的年龄现在有了生日，当前日期可以用getDate()函数获得，日期型字段的年份可以用Year函数获得Selectyear(getDate()-year(birthday)FromtblStudent,orderby子句,例5：查询02级学生的选课信息，按学号从小到大排列。由于学号的头两位代表学生的年级，所以可以这么完成SQL语句的编写：SelectstudentNo,courseNofromtblSelectCoursewherestudentNolike02%orderbystudentNo这里%代表任意个字符。,分组groupby,例6：求每个学生的课程总分Selectsc.studentNo,studentName,sum(score)astotfromtblSelectCoursesc,tblStudentswheres.studentNo=sc.studentNogroupbysc.studentNo,studentName问题:统计男女生人数怎么求?,groupby和Having子句,例6：求每个学生的课程总分(大于260分的同学罗列)Selectsc.studentNo,studentName,sum(score)astotfromtblSelectCoursesc,tblStudentswheres.studentNo=sc.studentNogroupbysc.studentNo,studentNamehavingsum(score)260改为havingtot260对不对？请你去测试一下!例7:列出选修课程大于等于三门课程的学生，并按学号排序selects.studentName,sc.studentNo,count(*)ascoursesfromtblStudents,tblSelectCoursescwheresc.studentNo=s.studentNogroupbysc.studentNo,s.studentNamehavingcount(*)=3orderbysc.studentNo,内连接查询,例8查找高数一成绩在80分以上的学生的姓名、学号、年龄以及具体成绩。分析：分析tblSelectCourse和tblStudent，通过tblSelectCourse可以得到学生的学号，但无法得到学生的名字和年龄，但缺少的信息可以通过表tblStudent得到，表tblStudent和tblSelectCourse的studentNo如果统一起来，就可以唯一得到所需要的信息Selects.studentNo,studentName,year(getDate()-year(birthday),scorefromtblStudents,tblSelectCoursescwheres.studentNo=sc.studentNoandcourseNo=S01andscore80,例8的另外的写法,Selects.studentNo,studentName,birthday,scorefromtblStudentsinnerjointblSelectCoursescons.studentNo=sc.studentNowherecourseNo=S01andscore80,嵌套查询,例9：找出成绩(不分课程)大于80分的学生的姓名SelectstudentNamefromtblStudentwherestudentNoin(selectstudentNofromtblSelectCoursewherescore80)这里，selectstudentNofromscwherescore80返回一个集合，第二个查询使用In关键字，判定表S中的studentNo是否在返回结果集中。,关于分页查询,Selecttop5studentNo,studentNameFromtblStudentwherestudentNonotin(selecttop5studentNofromtblstudent)这样的语句就能翻到下一页,左连接查询,问题：班主任查询班级学生某门课的成绩表，如果一个学生没选该课程，用内连接查询能得到什么结果？,学生表tblStudent课程表tblCourse学生修课表tblSelectCourse,左连接查询Leftjoin,SelectstudentName,a.studentNo,b.courseNo,courseNameFromtblStudentaLeftjointblSelectCoursebon(a.studentNo=b.studentNoAndb.courseNo=F001)LeftjointblCourseconb.courseNo=c.courseN