为了正常的体验网站,请在浏览器设置里面开启Javascript功能!

kc第2讲-关系模型

2011-12-11 40页 ppt 872KB 15阅读

用户头像

is_437135

暂无简介

举报
kc第2讲-关系模型null第2讲: (第2章) 关 系 模 型 重庆大学计算机学院第2讲: (第2章) 关 系 模 型 重庆大学计算机学院课程名称: 数据库系统 --------------------第2讲:关系模型第2讲:关系模型项目驱动目标: 关系模型的特点及在应用开发中的地位: 一、关系模型  二、关系的键和外键  三、关系数据查询  四、空值Null的特殊作用  五、关系数据更新  六、关系模型在应用开发中的地位  主要讨论...
kc第2讲-关系模型
null第2讲: (第2章) 关 系 模 型 重庆大学计算机学院第2讲: (第2章) 关 系 模 型 重庆大学计算机学院课程名称: 数据库系统 --------------------第2讲:关系模型第2讲:关系模型项目驱动目标: 关系模型的特点及在应用开发中的地位: 一、关系模型  二、关系的键和外键  三、关系数据查询  四、空值Null的特殊作用  五、关系数据更新  六、关系模型在应用开发中的地位  主要讨论问: 什么是关系模型,主要特点是什么? 什么是关系的键和外键,起到什么作用? 如何查询关系数据中的数据,有何理论基础? 空值Null是指什么,使用时有哪些约束? 如何更新关系数据库中的数据? 关系模型有哪些优点?Exercise 2关系的例子关系的例子一 关系模型1-1 什么是关系?一种简单的二维表!问题3答案关系的(内部)结构关系的(内部)结构Formally, given sets D1, D2, …. Dn (属性)a relation r is a subset of D1 x D2 x … x Dn Thus, a relation R is a set of n-tuples (a1, a2, …, an) where each ai  Di Example: If customer_name = {Jones, Smith, Curry, Lindsay, …} /* Set of all customer names */ customer_street = {Main, North, Park, …} /* set of all street names*/ customer_city = {Harrison, Rye, Pittsfield, …} /* set of all city names */ Then r = { (Jones, Main, Harrison), (Smith, North, Rye ), (Curry, North, Rye ) } is a relation (a table) 1-2 关系模型如何描述数据的内部结构? Relation Schema(模式): R(D1, D2,…, Dn) relation instance (实例): The current values of relation R, denoted r(R) 1-3 什么叫模式和实例?一 关系模型元组属性类型(域)属性类型(域)Each attribute of a relation has a name The set of allowed values for each attribute is called the domain of the attribute Attribute values are (normally) required to be atomic; that is, indivisible E.g. the value of an attribute can be an account number, but cannot be a set of account numbers Domain is said to be atomic if all its members are atomic The special value null is a member of every domain The null value causes complications in the definition of many operations We shall ignore the effect of null values in our main presentation and consider their effect later1-4 属性如何取值?一 关系模型Relations are Unordered (关系的重要特征1)Relations are Unordered (关系的重要特征1)Order of tuples is irrelevant (tuples may be stored in an arbitrary order ) Example: account relation with unordered tuples一 关系模型1-5 关系的元组有序吗?答案: No! 遗留问题1:原因何在?Relational Database-关系数据库Relational Database-关系数据库A ralational database consists of multiple relations Information about an enterprise is broken up (划分) into parts, with each relation storing one part of the information: account: stores information about accounts depositor: stores information about which customer owns which account customer: stores information about customers1-6 关系数据库指什么?遗留问题2:如何划分数据? (答案:见规范化设计-以后介绍)一 关系模型Keys - 键Keys - 键Let K  R (R的一组属性) 超键:K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R). Example: {customer_name, customer_street}, and {customer_name} are both superkeys of Customer, if no two customers can possibly have the same name 后选键:K is a candidate key if K is minimal(属性不能再少). Example: {customer_name} is a candidate key for Customer, since it is a superkey and no subset of it is a superkey. 主键:Primary key is a candidate key chosen as the principal means of identifying tuples within a relation.二 关系的键和外键2-1 如何识别关系的元组?遗留问题1的答案: 元组用主键识别!问题2答案外 键外 键A relation schema may have an attribute that corresponds to the primary key of another relation. The attribute is called a foreign key. E.g. (customer_name,account_number) attributes of depositor are foreign keys to customer and account respectively. Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation.2-2 用关系如何描述数据间关联(外部结构)?二 关系的键和外键查询语言查询语言Query Languages are the Languages in which user requests information from the database. Categories of languages’ Procedural(过程化):包括’数据获取路径’或’运算次序’[3] Non-procedural, or declarative(说明的):仅描述’要什么’而不指明’怎么做’ [1] ([3]《关系数据库》石树刚,p.60 & [2]《数据系统原理教程》王珊,p67) “Pure” languages: Relational algebra(关系代数) 过程化(规定运算顺序)[3]p.60 *Tuple relational calculus 说明性[3]p.60 三者运算能力等价[3]p.60 *Domain relational calculus 说明性[3]p.60 Pure languages form underlying basis of query languages that people use.三 关系数据查询3-1 如何查看所需关系数据?答案:提供查询语言! (关系代数or关系演算)3.1 基本查询操作3-2 过程化查询语言与说明性查询语言有何区别?问题3答案六种关系代数基本操作六种关系代数基本操作关系代数是Procedural language Six basic operators select:  (选择) project:  (投影) union:  (并) set difference: – (差) Cartesian product: x (笛卡尔积) rename:  (重命名) These operators take one or two relations as inputs and produce a new relation as a result.3.1 基本查询操作3-3 关系代数提供哪些基本查询操作?Select Operation – 挑选合符条件的元组(行)Select Operation – 挑选合符条件的元组(行)3-4 何谓选择操作? 结果有何特点?结果仍是关系!形式化定义: p(r) = {t | t  r and p(t)} p is called the selection predicate Where p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not) Each term is one of: op or where op is one of: =, , >, . <.  Example :  branch_name=“Perryridge”(account)3.1 基本查询操作Project Operation – 挑选指定属性(列)Project Operation – 挑选指定属性(列)Relation r: A,C (r)3-5 何谓投影操作?形式化定义: where A1, A2 are attribute names and r is a relation name. The result is defined as the relation of k columns obtained by erasing the columns that are not listed Duplicate rows removed from result Example: account_number, balance (account) 结果有何特点?结果仍是关系!关系的属性有序吗?无序,用属性名识别! (关系的又一重要特征)3.1 基本查询操作消除重复!Union Operation – 将元组放在一起Union Operation – 将元组放在一起Relations r: s:r  s:3-6 何谓并操作?形式化定义: r  s = {t | t  r or t  s} r, s must have the same number of attributes The attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s) Example: to find all customers with either an account or a loan customer_name (depositor)  customer_name (borrower)结果有何特点?结果仍是关系!3.1 基本查询操作消除重复!Set Difference Operation – 去掉相同元组Set Difference Operation – 去掉相同元组Relations r: s:r – s:3-7 何谓差操作?结果有何特点?结果仍是关系!形式化定义:r – s = {t | t  r and t  s} r and s must have the same number of attributes attribute domains of r and s must be compatible3.1 基本查询操作Cartesian Product Operation – 两表的元组连接Cartesian Product Operation – 两表的元组连接Relations r: s:r x s:3-8 何谓笛卡尔积操作?形式化定义:r x s = {t q | t  r and q  s} Assume that attributes of r(R) and s(S) are disjoint. (That is, R  S = ). If attributes of r(R) and s(S) are not disjoint, then renaming must be used.结果有何特点?结果仍是关系!3.1 基本查询操作这些操作可以灵活复合这些操作可以灵活复合Can build expressions using multiple operations Example: A=C(r x s) 假设:r,s同前页,则该操作结果为 因r x s: 3-9 上述五种基本操作能够满足应用需要?或者问:这五种操作表达能力足够吗?这五种操作形成完备集!故足够用! [3] 《关系数据库》石树刚p.353.1 基本查询操作Rename Operation - 表/属性的重命名Rename Operation - 表/属性的重命名Allows us to name, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Example:  x (E) returns the expression E under the name X If a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with the attributes renamed to A1 , A2 , …., An .3-10 何谓重命名操作?有何用途?适应不同部门的习惯! 利于遗留系统的移植!3.1 基本查询操作Additional Operations - 附加操作Additional Operations - 附加操作Set intersection - 交 Natural join - 自然连接 Division* - 除 Assignment* - 赋值3.2 附加的查询操作3-11 还需要其它查询操作?数据操作能力有所增强?并不增加操作能力! 但可简化应用操作!We define additional operations that do not add any power to the relational algebra, but that simplify common queries.答案:Yes! 应用常用到下述操作!Set Intersection Operation - 保留相同元组Set Intersection Operation - 保留相同元组形式化定义: r  s = { t | t  r and t  s } Assume: r, s have the same arity attributes of r and s are compatible3-12 何谓交操作?为何不增强操作能力?r  s = r – (r – s) 3.2 附加的查询操作Natural Join Operation – 元组按属性值相同粘贴Natural Join Operation – 元组按属性值相同粘贴例: 假设 R = (A, B, C, D) S = (E, B, D) 则有 r s = r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))3-13 何谓自然连接操作?为何不增强操作能力?3.2 附加的查询操作消除重复属性!*Division Operation*Division OperationNotation: Suited to queries that include the phrase “for all”. Let r and s be relations on schemas R and S respectively where R = (A1, …, Am , B1, …, Bn ) S = (B1, …, Bn) The result of r  s is a relation on schema R – S = (A1, …, Am) r  s = { t | t   R-S (r)   u  s ( tu  r ) } Where tu means the concatenation of tuples t and u to produce a single tupler  s *3-14 何谓除操作?不讲解!自学!3.2 附加的查询操作 Division Example Example 1 Example 2 为何不增强操作能力?仍可用基本操作表示!Division ExampleDivision ExampleRelations r  s:3.2 附加的查询操作Another Division ExampleAnother Division ExampleRelations r  s:3.2 附加的查询操作Division Operation的特性Division Operation的特性Property Let q = r  s Then q is the largest relation satisfying q x s  r Definition in terms of the basic algebra operation Let r(R) and s(S) be relations, and let S  R r  s = R-S (r ) – R-S ( ( R-S (r ) x s ) – R-S,S(r )) To see why R-S,S (r) simply reorders attributes of r R-S (R-S (r ) x s ) – R-S,S(r) ) gives those tuples t in R-S (r ) such that for some tuple u  s, tu  r.3.2 附加的查询操作**Assignment Operation**Assignment OperationThe assignment operation () provides a convenient way to express complex queries. Write query as a sequential program consisting of a series of assignments followed by an expression whose value is displayed as a result of the query. Assignment must always be made to a temporary relation variable. Example: Write r  s as temp1  R-S (r ) temp2  R-S ((temp1 x s ) – R-S,S (r )) result = temp1 – temp2 The result to the right of the  is assigned to the relation variable on the left of the . May use variable in subsequent expressions.**3-14 何谓赋值操作?不讲解!自学内容!3.2 附加的查询操作Extended RA Operations - 扩展的关系代数操作Extended RA Operations - 扩展的关系代数操作Generalized Projection - 广义投影 Aggregate Functions - 聚集数 Outer Join - 外连接3.3 扩展的查询操作3-15 想想看,还有什么查询操作非常重要?增加数据库查询操作有什么准则?方便用户, 应用需要, 使用价值!下述查询操作 应用中频繁用到!猜猜看:数据操作能力有所增强?答案:Yes!(从后定义可知) 为了处理之前未考虑的情况!Generalized ProjectionGeneralized Projection形式化定义: It allows arithmetic functions to be used in the projection list. E is any relational-algebra expression Each of F1, F2, …, Fn are are arithmetic expressions involving constants and attributes in the schema of E. 例子 Given relation credit_info(customer_name, limit, credit_balance), find how much more each person can spend: customer_name, limit – credit_balance (credit_info)3-16 何谓广义投影操作?结果有何特点?结果仍是关系!3.3 扩展的查询操作 Aggregate Functions & Operations 形式化定义如下: Aggregate Functions & Operations 形式化定义如下:Aggregation function:takes a collection of values and returns a single value as a result. avg: average value – 求平均值 min: minimum value – 求最小值 max: maximum value – 求最大值 sum: sum of values – 求和 count:number of values – 求元组数 Aggregate operation:in relational algebra E is any relational-algebra expression G1, G2 …, Gn is a list of attributes on which to group (can be empty) Each Fi is an aggregate function Each Ai is an attribute name3-17 何谓聚集函数操作?3.3 扩展的查询操作例子Aggregate Operation ExampleAggregate Operation ExampleRelation r:聚集函数如何工作(操作的含义)?以sum为例,直观说明!branch_name g sum(balance) as sum_balance (account)Result of aggregation does not have a name Can use rename operation to give it a name For convenience, we permit renaming as part of aggregate operation3.3 扩展的查询操作求和!分组求和!Outer Join – 还要保留无连接条件的元组Outer Join – 还要保留无连接条件的元组形式化描述: R S An extension of the join operation that avoids loss of information. Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. Uses null values: - null signifies that the value is unknown or does not exist - All comparisons involving null are (roughly speaking) false by definition. 注:We shall study precise meaning of comparisons with nulls later3-18 何谓外连接操作?外连接的例子 假设有两关系: loan borrower3.3 扩展的查询操作待续null有几种外连接操作? 与自然连接的区别?答案:三种! 用例子说明差别!3.3 扩展的查询操作Null Values - 表示未知或不存在Null Values - 表示未知或不存在It is possible for tuples to have a null value, denoted by null, for some of their attributes null signifies an unknown value or that a value does not exist. The result of any arithmetic expression involving null is null. Aggregate functions simply ignore null values (as in SQL) For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same (as in SQL)四 空值Null的特殊作用4-1 为什要引入Null?4-2 数据运算中碰到Null是如何处理的?问题4答案Null Values & unknown(一种真值“未知”)Null Values & unknown(一种真值“未知”)Comparisons with null values return the special value: unknown Not (A < 5) would not be equivalent to A >= 5 (A是unknown) Three-valued logic using the truth value unknown: OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown NOT: (not unknown) = unknown In SQL “P is unknown” evaluates to true if predicate P evaluates to unknown Result of select predicate is treated as false if it evaluates to unknown4-3 使用Null还有什么需要注意?四 空值Null的特殊作用Modification of the DatabaseModification of the DatabaseThe content of the database may be modified using the following operations: Deletion - 删除元组 Insertion - 插入元组 Updating - 修改元组 All these operations are expressed using the assignment operator.五 关系数据更新7.1 你认为关系数据的更新需要哪些操作?问题5答案ExamplesExamplesA delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database. Can delete only whole tuples; cannot delete values on only particular attributes A deletion is expressed in relational algebra by: r  r – E where r is a relation and E is a relational algebra query.Deletion - 删除多个元组7.2如何删除数据?五 关系数据更新ExamplesExamplesTo insert data into a relation, we either: specify a tuple to be inserted write a query whose result is a set of tuples to be inserted in relational algebra, an insertion is expressed by: r  r  E where r is a relation and E is a relational algebra expression. The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple. Insertion - 插入一/多个元组7.3如何插入数据?五 关系数据更新ExamplesExamplesA mechanism to change a value in a tuple without charging all values in the tuple Use the generalized projection operator to do this task Each Fi is either the i th attribute of r, if the i th attribute is not updated, or, if the attribute is to be updated Fi is an expression, involving only constants and the attributes of r, which gives the new value for the attributeUpdating - 修改多个元组7.4 如何修改数据?五 关系数据更新Conclution –关系模型特点小结Conclution –关系模型特点小结模型描述简洁 简单的二维表结构 数据的内、外部结构统一用关系描述 数据操作简单 可采用有关系代数表示 数据查询采用描述性语言 数据操作简单,操作能力强 数据存取采用键和属性名 元组识别采用键值 (故关系表中行无序) 属性识别采用属性名 (故关系表中列无序) 注释 关系数据库还有很多优点(后面会陆续学习了解) 关系模型的奠基人E.F.Codd(IBM公司职员)因此模型的提出获得图灵奖六 关系模型在应用开发中的地位6-1 相比其它数据模型,你认为它在应用占到何地位?(现阶段)占有绝对主导地位! (关系数据库产品一统天下) 这得益于关系模型的众多优点!问题6答案练习 2:项目驱动目标: 如何建立和查询一个关系数据库: 一、数据库语言及应具备的处理能力 二、数据定义子语言DDL 三、数据查询子语言DQL1(基本查询) 四、数据查询子语言DQL2(复杂查询) 五、数据操纵子语言DML 主要讨论问题: 为什么需要数据库语言 什么是SQL语言,有何特点 数据库定义子语言提供那些功能 如何定义数据结构 数据库查询语言提供那些基本操作 为什么还需要嵌套子查询 为什么还需要视图 什么是关系连接,有哪些连接方式 数据更新子语言提供哪些处理能力练习 2:P.45 2.5 b) d), 2.7 a), P.45 2.9 b), 2.11 b)Thank you !End !预习下一讲内容(3章)
/
本文档为【kc第2讲-关系模型】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。 本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。 网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。

历史搜索

    清空历史搜索