资源描述
数据库设计过程E/RE/R图到关图到关系模式的系模式的转换转换关系模式关系模式的规范化的规范化需求分析需求分析概念模式设计概念模式设计逻辑模式设计逻辑模式设计数据库实施数据库实施E/RE/R图图UMLUMLRDBMSRDBMS模式模式存储模式存储模式数据库运行和维护数据库运行和维护第1页/共57页概念模式设计概念模式设计ER图图实体联系模型（实体联系模型（Entity-Relationship Model，简称，简称E-R模型）模型）第2页/共57页E-R模型的基本概念模型的基本概念 实体实体(entity)：客观存在，可以相互区别的事物称为实体。：客观存在，可以相互区别的事物称为实体。 实体集实体集(entity set)：性质相同的同类实体的集合，称实体集。：性质相同的同类实体的集合，称实体集。 属性属性(attribute)：实体有很多特性，每一个特性称为属性。每：实体有很多特性，每一个特性称为属性。每一个属性有一个值域，其类型可以是整数型、实数型、字符串型一个属性有一个值域，其类型可以是整数型、实数型、字符串型等。等。 实体标识符实体标识符(identifier)：能惟一标识实体的属性或属性集，称：能惟一标识实体的属性或属性集，称 为实体标识符。有时也称为关键码为实体标识符。有时也称为关键码(key)，或简称为，或简称为键键。 联系联系(relationship)是实体之间的相互关系。是实体之间的相互关系。 数据模型数据模型: 能表示实体类型及实体间联系的模型称为能表示实体类型及实体间联系的模型称为“数据模数据模型型”。 第3页/共57页E-R图的基本组成部分图的基本组成部分 E/RE/R图有四个主要部分：图有四个主要部分：（1 1）实体集，用矩形表示。）实体集，用矩形表示。（2 2）属性，用椭圆形表示。）属性，用椭圆形表示。（3 3）联系，用菱形表示。）联系，用菱形表示。（4 4）直线，表示联系的类型及实体与实体之间的联系；）直线，表示联系的类型及实体与实体之间的联系；第4页/共57页数据联系数据联系 联系（联系（relationshiprelationship）是实体之间的相互关系。）是实体之间的相互关系。 二元联系有以下三种类型：二元联系有以下三种类型：一对一联系一对一联系：实体集：实体集E1E1中每个实体至多和实体集中每个实体至多和实体集E2E2中中的一个实体有联系，反之亦然，那么实体集的一个实体有联系，反之亦然，那么实体集E1E1和和E2E2的的联系称为联系称为“一对一联系一对一联系”，记为，记为“1:11:1”。一对多联系一对多联系：实体集：实体集E1E1中每个实体可以与实体集中每个实体可以与实体集E2E2中中任意个（零个或多个）实体间有联系，而任意个（零个或多个）实体间有联系，而E2E2中每个实中每个实体至多和体至多和E1E1中一个实体有联系，那么称中一个实体有联系，那么称E1E1对对E2E2的联系的联系是是“一对多联系一对多联系”，记为，记为“1:N1:N”。 多对多联系多对多联系：如果实体集：如果实体集E1E1中每个实体可以与实体集中每个实体可以与实体集E2E2中任意个（零个或多个）实体有联系，反之亦然，中任意个（零个或多个）实体有联系，反之亦然，那么称那么称E1E1和和E2E2的联系是的联系是“多对多联系多对多联系”，记为，记为“M:NM:N”。 第5页/共57页三种联系的三种联系的E-R图图实体实体1 1实体实体2 2联系联系实体实体1 1实体实体2 2联系联系实体实体1 1实体实体2 2联系联系 1 11 1联系联系 1 1n n联系联系 m mn n联系联系第6页/共57页三种联系的三种联系的E-R图图客人客人飞机座位飞机座位号号联系联系院系院系教师教师联系联系课程课程学生学生联系联系 1 11 1联系联系 1 1n n联系联系 m mn n联系联系第7页/共57页E-R图实例图实例例：设计一个学生选课数据库。要求包括学生例：设计一个学生选课数据库。要求包括学生(student)(student)、课程、课程(course)(course)和教师和教师(teacher)(teacher)的信息。的信息。 实体中存在如下联系：实体中存在如下联系： 一个学生可以选修多门课，而每门课也能由多个学生选修；一个学生可以选修多门课，而每门课也能由多个学生选修；一位教师可以教多门课，每门课也可以由多个教师讲授；用一位教师可以教多门课，每门课也可以由多个教师讲授；用E-RE-R图来描述此数据库。图来描述此数据库。 学生信息：学号(sNo)、学生姓名(sName)、年龄(age)、地址(sAddress)课程信息：课程号(cNo)、课程名(cName)、课程学分(credit)教师信息：工号(tNo)、姓名(tName)、教师的地址(tAddress)第8页/共57页学生学生-课程课程-教师教师E-R图图studentcourseteachertaketeachcreditscNamecNumsAddressagesNamesNotAddresstNametNoscore注意：如果一个联系具有属性，则这些属性也与该注意：如果一个联系具有属性，则这些属性也与该联系连接起来。联系连接起来。TeachCourseNo第9页/共57页概念模式设计概念模式设计UML从用例图分析出对象StudentStudentstudentNostudentNostudentNamestudentNamesexsexdepartmentIddepartmentIdmajormajor第10页/共57页概念模式设计概念模式设计UMLTeacherteacherIdteacherNamesex对象之间的关系DepartmentDepartmentIddeptNamelocation工作第11页/共57页概念模式设计概念模式设计UML对象之间的关系分类一元关系第12页/共57页概念模式设计概念模式设计UMLTeacherteacherIdteacherNamesex对象之间的关系分类DepartmentDepartmentIddeptNamelocation工作二元关系第13页/共57页概念模式设计概念模式设计UMLTeacherteacherIdteacherNamesex关系的重数DepartmentDepartmentIddeptNamelocation1.n1重数1.n重数1第14页/共57页概念模式设计概念模式设计UML关联2个对象关联产生属性第15页/共57页关系模型的结构关系模型的结构 - - 数据模型三要素之一数据模型三要素之一 一个关系的逻辑结构是一张二维表，它由行和列组成。一个关系的逻辑结构是一张二维表，它由行和列组成。关关系系关系名关系名第16页/共57页l 元组元组：表中的一行即为一个元组或记录；：表中的一行即为一个元组或记录；l 字段字段：表中的一列，列值称为字段值，列名称为字段名：表中的一列，列值称为字段值，列名称为字段名l 值域值域：字段值的取值范围：字段值的取值范围元组元组 ( (记录）记录）属性属性 ( (字段字段) )第17页/共57页l主键主键：能唯一标识一个元组的属性集合。：能唯一标识一个元组的属性集合。l外键外键：若属性组：若属性组F F不是关系不是关系R1R1的主键，但它是关系的主键，但它是关系R2R2的主的主 键，则键，则F F称是关系称是关系R1R1的外键。的外键。主键主键主键主键外键外键第18页/共57页关系的完整性关系的完整性域完整性域完整性 实体完整性实体完整性 参照完整性参照完整性 第19页/共57页1）第一范式）第一范式（First Normal Form，1NF） 定义：定义： 如果一个关系模式如果一个关系模式R的所有属性都是不可分的基的所有属性都是不可分的基 本本 数据项，则关系数据项，则关系R属于第一范式。属于第一范式。p第20页/共57页2）第二范式）第二范式（ Second Normal Form，2NF ） 定义：定义：若关系模式若关系模式R属于第一范式，且每个非主属性都完属于第一范式，且每个非主属性都完 全函数依赖于主键，全函数依赖于主键，则则R属于第二范式属于第二范式。t第21页/共57页3 3）第三范式）第三范式定义：定义：如果一个关系如果一个关系R是第二范式的，并且每个非主是第二范式的，并且每个非主属性属性 都不传递依赖于主键，则都不传递依赖于主键，则R属于第三范属于第三范式。式。 第22页/共57页4 4、E-R(UML)E-R(UML)模型到关系模式的转换模型到关系模式的转换1）实体）实体(对象对象)到关系模式的转换到关系模式的转换2）联系）联系(关系关系)到关系模式的转换到关系模式的转换第23页/共57页1）实体到关系模式的转换）实体到关系模式的转换 规则：规则：将实体的名称作为关将实体的名称作为关系的名称，将实体系的名称，将实体的属性作为关系的的属性作为关系的属性，并用下划线属性，并用下划线标识出主属性。标识出主属性。 studentstudentcoursecoursetaketakesNosNosnamesnamesaddresssaddressageage cNocNo cnamecnamecreditscreditsgradegradestudent（sNo,sName,sex,age），表示student关系，其中sNo为主键。course（cNo,cName,credits），表示course关系，其中cNo为主键。第24页/共57页2）联系到关系模式的转换）联系到关系模式的转换 1:1 若实体间联系是若实体间联系是1:11:1，可以在两个实体转换成的两个，可以在两个实体转换成的两个关系模式后，然后在其中任意一个关系模式的属性集中关系模式后，然后在其中任意一个关系模式的属性集中加入另一个关系模式的主键和联系自身的属性。加入另一个关系模式的主键和联系自身的属性。 也就也就是说是说1:11:1联系不单独转换为一个独立的关系。联系不单独转换为一个独立的关系。第25页/共57页2）联系到关系模式的转换）联系到关系模式的转换 1:N 若实体间联系是若实体间联系是1:N1:N，则在，则在N N端实体转换成的关端实体转换成的关系模式中加入系模式中加入1 1端实体的主键和联系的属性。端实体的主键和联系的属性。 同样同样1:N1:N联系也不独立构成一个关系。联系也不独立构成一个关系。TeacherteacherIdteacherNamesexDepartmentDepartmentIddeptNamelocation工作二元关系第26页/共57页2）联系到关系模式的转换）联系到关系模式的转换 M:N若实体间联系是若实体间联系是M:NM:N，则将联系转换成一个关系模式，其属性为两端实体的主键加，则将联系转换成一个关系模式，其属性为两端实体的主键加上联系自身的属性，而联系关系的主键为一复合键，通常是两端实体主键的组合。上联系自身的属性，而联系关系的主键为一复合键，通常是两端实体主键的组合。 第27页/共57页对于对于“学生选课学生选课”E/R图的课程图的课程和学生之间的多对多的联系，和学生之间的多对多的联系，可转换成关系模式：可转换成关系模式： take（sNo,cNo,grade） 主键为复合键主键为复合键sNo,cNo。 studentstudentcoursecoursetaketakesNosNosnamesnamesaddresssaddressageage cNocNo cnamecnamecreditscreditsgradegrade例：例：第28页/共57页关系数据库的组织表1表2表3DBColumn or FieldRecord第29页/共57页SQL 2000SQL 2000字段类型Char varchar int dateTime第30页/共57页字段的属性 请与Insertq 字段长度 对于字符类型，例如char和varchar，定义字段的同时可以 指 定 字 段 存 放 的 字 符 的 个 数 ， 例 如 定 义 姓 名 字 段 为 n a m e varchar(20)，表示该字段最多存放20个字符。数值型的字段，除了decimal由用户自己定义长度和小数位数外，其他的如int, float等，系统自定义其占用的字节数。q 小数位数 用于decimal和货币类型的字段q 标识种子 用于数据库表的记录号的自动增加，经常用于做表中记录的主键。用于整型数值.q 默认值 当表中新增加一个记录时，又不想具体指定某个字段的值。可以给它一个默认的值。q 非空限制 用于限制字段的值不能为空，例如学生的姓名字段，每一个记录都必须有值，因为学生不能没有名字。q 索引 对字段进行索引，可以通过设定该字段为查询条件来加快数据的检索速度和统计、分组的速度。q 关键字 关系模式中的主键，用于唯一标识数据库表中的记录。第31页/共57页简单的SQL 查询语句 Select SELECT 字段1,字段2,| * FROM 表名 WHERE 条件表达式第32页/共57页结构化查询语言SQLSQLSQL语言是一种关系数据库语言，是结构化查询语言（Structured Query Language）的缩写，它包括q 查询q 定义q 操纵q 控制SQL的使用方法有两种q 用户交互的方式联机使用，称为交互式SQL；q 另一种是作为子语言嵌入到其他程序设计语言中使用，称为嵌入式SQL。第33页/共57页SQL表达式SQL的表达式可分为两类：1. 值表达式2. 逻辑表达式。值表达式包括几种：1. 数值表达式：实现数值型数据的算术运算；2. 字串值表达式完成了字串的操作；3. 日期时间值表达式处理日期时间数据。逻辑运算包括与（AND）、或（OR）、否（NOT）。SQL还提供了比较谓词（=、!=、=）、范围谓词（IN、LIKE、IS）和存在谓词（EXIST）。第34页/共57页函数SQL内置函数有三类：1. 统计函数:COUNT、SUM、AVG、MAX和MIN2. 字串处理函数 len, substring(studentName,1,2)大小写转换函数（Upper,Lower）3. 时间日期处理函数。 时间日期处理函数用于返回当前的日期与时间。常用的有三个：getDate (返回当前日期时间)。Year, Month, Day函数第35页/共57页数据检索语句select 语法格式为： SELECT ALL|DISTINCT 目标列FROM 基本表 （或视图）as 表别名WHERE 条件表达式GROUP BY 列名 HAVING 条件表达式ORDER BY 列名 ASC/DESC说明 DISTINCT为去掉查询结果中重复的行，默认为ALL，既列出所有的记录；目标列的格式可以写为：列名1 as 别名1,列名n as 别名n，省略别名，返回的结果集中以列名标记，否则以别名标记，如果目标列用*表示，则返回表中的所有的列；From子句指明了从那些表或视图中得到数据，所有的列名都必须存在于From后的表中,或出现于left join, inner join, full outer join之后的表，如果列名存在于From后的多个表中，则必须标明该列来源于哪个表，格式为 表名.列名；目标列可以是SQL中的库函数或常数表达式。如Sum、Count、Avg等函数。第36页/共57页Select 语句Where 子句有双重作用，一是返回需要的记录，另一种是建立多表之间的内连接联合查询。 GROUP BY 用来对查询结果进行分组，把某一列值相同的记录分成一组，与统计函数如Sum、Count等联合使用；在有Group子句时，可以进一步使用HAVING子句对分组后的记过进行过滤显示，是分组提取条件；ORDER BY 是查询结果的排序方式，后面跟ASC为升序，DESC为降序,默认为升序。第37页/共57页关系实例studentNonamebirthdaysexcourseNocourseNamestudentNocourseNoscore030318古待1986-1-2女S01高数一020301F0998020301张名1989-1-1男L02数值算法010101L0278010201张简1981-2-2女J04操作系统010101J0489030501许昌1987-9-8男F09德语010201J0179030502刘放1988-1-9男F01日语010101J0491010101李灿1981-9-2男J01数据库010101J0199010503顾争1988-2-7男020002郭洁1988-1-5女学生表 tblStudent 课程表tblCourse 学生修课表 tblSelectCourse第38页/共57页SQL例例1 1：查看学生表中的全部数据，即列出所有的学生Select * from tblStudent这里 * 代表表中所有的列，上述的语句等价如下的语句：Select studentNo, studentName, birthday, sex From tblStudent这时，表中有多少条记录，数据库系统就列出多少条记录。第39页/共57页Where子句子 例2：查询学生表tblStudent中的全部女生的学号、姓名和年龄Select studentNo, studentName, birthdayfrom tblStudent where sex=女 例3： 查询学生修课表中成绩大于80分的学生：Select * from tblSelectCourse where score80在where条件中，可以使用逻辑运算符和比较关系符，包括：=、!=、=。也可以使用关键字，如：between、like、in等。注意，如果值是字符串或者字符、日期类型的，就要用单引号把它括起来，例如例2中的where条件。 例4 查询操作系统成绩在70到80之间的学生的学号select studentNo from tblSelectCourse where score between 70 and 80 and courseNo=J003第40页/共57页日期型数据处理 罗列出每个同学的年龄现在有了生日，当前日期可以用getDate()函数获得，日期型字段的年份可以用Year函数获得Select year(getDate()- year(birthday)From tblStudent第41页/共57页order by 子句 例5：查询02级学生的选课信息，按学号从小到大排列。由于学号的头两位代表学生的年级，所以可以这么完成SQL语句的编写：Select studentNo,courseNofrom tblSelectCourse where studentNo like 02% order by studentNo这里%代表任意个字符。第42页/共57页group by 和Having子句 例6：求每个学生的课程总分(大于260分的同学罗列)Select sc.studentNo, studentName, sum(score) as totfrom tblSelectCourse sc, tblStudent s where s.studentNo=sc.studentNo g ro u p b y s c . s t u d e n t N o , s t u d e n t N a m e h a v i n g sum(score) 260改为 having tot 260 对不对？例7:列出选修课程大于等于三门课程的学生，并按学号排序select s.studentName,sc.studentNo, count(*) as courses from tblStudent s, tblSelectCourse sc where sc.studentNo=s.studentNo group by sc.studentNo, s.studentName having count(*)=3 order by sc.studentNo第43页/共57页内连接查询 例8 查找软件开发课程成绩在80分以上的学生的姓名、学号、年龄以及具体成绩。分析：直接观察表tblSelectCourse和tblStudent，可以发现，通过tblSelectCourse可以得到学生的学号，但无法得到学生的名字和年龄，但缺少的信息可以通过表tblStudent得到，表tblStudent和tblSelectCourse的studentNo如果统一起来，就可以唯一得到所需要的信息 S e l e c t s . s t u d e n t N o , s t u d e n t N a m e , year(getDate()-year(birthday), score from tblStudent s, tblSelectCourse sc where s.studentNo=sc.studentNo and courseNo=J001 and score80第44页/共57页例8的另外的写法Select s.studentNo, studentName, birthday, score from tblStudent sinner join tblSelectCourse sc on s.studentNo=sc.studentNo where courseNo=J001 and score80第45页/共57页嵌套查询 例9：找出成绩(不分课程)大于80分的学生的姓名Select studentName from tblStudent where studentNo in (select studentNo from tblSelectCourse where score80)这 里 ， s e l e c t s t u d e n t N o f ro m s c w h e re score80返回一个集合，第二个查询使用In关键字，判定表S中的studentNo是否在返回结果集中。第46页/共57页左连接查询问题：班主任查询班级学生某门课的成绩表，如果一个学生没选该课程，用内连接查询能得到什么结果？studentNonamebirthdaysexcourseNocourseNamestudentNocourseNoscore030318古待1986-1-2女S01高数一020301F0998020301张名1989-1-1男L02数值算法010101L0278010201张简1981-2-2女J04操作系统010101J0489030501许昌1987-9-8男F09德语010201J0179030502刘放1988-1-9男F01日语010101J0491010101李灿1981-9-2男J01数据库010101J0199010503顾争1988-2-7男020002郭洁1988-1-5女学生表 tblStudent 课程表tblCourse 学生修课表 tblSelectCourse第47页/共57页左连接 查询 Left joinSelect studentName, a.studentNo, b.courseNo, courseNameFrom tblStudent aLeft join tblSelectCourse b on (a.studentNo = b.studentNoAnd b.courseNo=F001)Left join tblCourse c on b.courseNo=c.courseNo第48页/共57页什么时候用左连接n 左表在右表中对应的记录可能有，也可能没有的情况下， 第49页/共57页SQL数据更新语句 语法数据更新语句 Updaten SQL的数据更新语句Update修改表中原有的数据记录n 与Select 语句不同，Update语句不存在多表联合操作，每个语句一次只能针对一个表。Update语法格式为：update 表 set 字段名 = 表达式, 字段名=表达式,. where条件第50页/共57页Update 例子例 1111：将所有学生的年龄加1岁update tblStudent set age = age+1update tblStudent set age = age+1 例1212：将学号为011101的学生的J01课程成绩加5分update tblSelectCourse set score= score+5 update tblSelectCourse set score= score+5 where studentNo=where studentNo=011101011101 and courseNo = and courseNo = J01J01第51页/共57页插入新记录语句 Insert 语法insert intoinsert into 表名(字段名，字段名2) valuesvalues (值1,值2,值n)注：1、不允许为空的字段，并且没有字段默认值的字段,必须出现在字段列表中.2、如果不写字段名列表,则必须按表结构定义的字段顺序,在values后定义值的序列.第52页/共57页Insert 举例例13 向学生表中插入一个新同学，数据为 0300101， 张三，男，21岁 insert into tblStudent values(030101, 张三, 21, 男)比较下例 insert into tblStudent(studentName, studentNo) values(汪于人, 980145)放弃了性别和年龄的输入，如果表tblStudent的age和sex字段允许为空，则上面的语句向数据库中插入的记录中，这两个字段的值是Null。如果age和sex有默认值,则取默认值. 例14 向修课表中插入一个新的选课记录，成绩暂时不定 insert into tblSelectCourse(studentNo, courseNo) values(030101, J04)第53页/共57页删除记录 delete 语法 delete from 表名 where 条件第54页/共57页Delete 举例例15 删除表tblSelectCourse中011101的德语选课记录delete from tblSelectCourse where studentNo=010101 and courseNo=F09第55页/共57页Sql作业 写SQL语句1.将数据库恢复 (自己的准备工作)2.罗列出所有可选的课程的课程号，课程名。 3.罗列出所有学生的姓名、学号，年龄(库里是生日)。4.罗列出学号、姓名，所选课程的总分，按总分从大到小次序排列。5.罗列出学号、姓名、课程号、课程名、成绩，按学号从小到大排列 第56页/共57页感谢您的观看！第57页/共57页
展开阅读全文