3TheRelationalModel_龚勋(2010-9-16)111.ppt

1、Chapter 3 The Relational Model,四川大学计算机（软件）学院 龚勋 交流：gongxun_ 交作业：gongxun_,Agenda,3.1 Background 3.2 Relational Model Terminology 3.3 Relation Integrity 关系完整性 3.4 Views 视图,3.1 Background,The relational Database Management System (RDBMS) has become the dominant data-processing software in use today. Br

2、ief history of the Relational Model E.F.Codd “A Relational Model of Data for Large Shared Data Banks” 大型共享数据库数据的关系模型 IBM RDBMS System R 第一个商品DBMS Ingres, IBM DB2, Oracle, Sybase, MS SQL Server,3.2 Relational Model Terminology,Data Model 数据模型（参见2.3节） An integrated collection of concepts for describin

3、g and manipulating data, relationships between data, and constraints on data in an organization. A data model can be thought of as comprising three components: 数据模型三要素（三个组件） Data structure 数据结构（PPT的3.2.1节） Data manipulation 数据操作（PPT的第4章） Constraints of data integrity 数据的完整性约束（PPT的3.3节）,3.2 Relationa

4、l Model Terminology,The Relational Model (Codd 1976) is currently the most popular model for database systems. The simplest and most uniform data structure: data are stored in tables 最简单 二维表 The most formal of all data models. It is based on the mathematical concept of a relation in set theory: a ta

5、ble is a relation 集合论为理论基础,3.2.1 Relational Data Structure,Relation 关系 A relation is a table with columns and rows. 二维表 只应用到逻辑结构（外部层和概念层） Attribute (columns, field) 属性（列，字段） An attribute is a named column of a relation. Domain 域 A domain is the set of allowable values for one or more attributes. Tup

6、le (row, record) 元组（行，记录） A tuples is a row of relation.,7,3.2.1 Relational Data Structure,可选术语,3.2.1 Relational Data Structure,Intension 内涵：关系结构+域+值约束 The structure of a relation, together with a specification of the domains and any other restrictions on possible values Extension 外延：元组 The tuples a

7、re called extension (or state) Degree 维，目，度：属性的数目 The degree of a relation is the number of attributes it contains. Cardinality 基数：元组的数目 The cardinality of a relation is the number of tuples it contains.,3.2.2 Database Relational,Relational database 关系数据库 A collection of normalized relations with di

8、stinct relation names. Relation schema 关系模式（关系框架） A named relation defined by a set of attribute and domain name pairs. 对型的描述 （关系模式是型，关系是值） Ri (A1:d1,A2:d2,An:dn) Relational database schema 关系数据库模式 A set of relation schemas, each with a distinct names.,其集合,3.2.3 Properties of Relations,A distinct re

9、lation name 关系不重名 Every cell is an atomic (single) value 单元格不可再分 A distinct attribute name 属性不重名 An attribute values are from the same domain 一个属性来自同一个域 no duplicate tuples 元组不重复 the order of attributes has no significance 列的顺序无所谓 the order of tuples has no significance （理论上）行的顺序无所谓（实际会影响元组访问效率）,3.2

10、.4 Keys,Super key 超关键字（超码） One or more columns whose values are sufficient to identify a row. Key is a property of a table schema. Candidate key 候选关键字 （CK 候选码） A super key but no proper subset is a super key A relation may has one or more CKs. primary key vs. alternate keys 主关键字（PK，主码）与备选关键字（AK，备选码）

11、 Select one of the candidate keys to use primarily as primary key. The remaining candidate keys are called alternate keys. A relation can has only one PK.,3.2.4 Keys,Foreign key 外部关键字，外码 An attribute or a set of attributes within a relation that matches the candidate key of some relation Example:CKs

12、? PKs? FKs? Bank(BankNo,BankName,TelephoneNumber) Branch (BankNo,BranchNo, Street, City, Postcode) Customer(CustomerNo, Fname, Lname) Account(CustmerNo,BranchNo,Balance),13,3.2.5 Examples,例子：属性、维、基数、主关键字、外部关键字,14,3.2.5 Examples,例子：属性域,3.3 Relation Integrity,Nulls 空值 Null represents a value for an at

13、tribute that is currently unknown or is not applicable for this tuple. Entity Integrity 实体完整性 (Candidate) keys must be unique.关键字值唯一 In a relation, no attribute of a primary key can be null. 基本表主关键字的诸属性均不能为空,3.3 Relation Integrity,Referential Integrity 参照完整性 If a foreign key exists in a relation, ei

14、ther the foreign value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null. 外部关键字取参照候选关键字值或取空值 Enterprise constraints 企业约束 Additional rules specified by the users or database administrators of a database 用户和管理员定义的附加规则,17,3.4 Views 视图,Base

15、Relation 基本关系 Named relation corresponding to an entity in conceptual schema, whose tuples are physically stored in database View 视图 Dynamic result of one or more relational operations operating on base relations to produce another relation,基本关系和视图的区别,18,3.4 Views 视图,A virtual relation that does not

16、 necessarily actually exist in the database but is produced upon request, at time of request. Contents of a view are defined as a query on one or more base relations. Views are dynamic, meaning that changes made to base relations that affect view attributes are immediately reflected in the view.,视图的

17、特点,19,3.4 Views 视图,Provides powerful and flexible security mechanism by hiding parts of database from certain users. Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time. Can simplify complex operations on base relations.,

18、视图的用途,20,3.4 Views 视图,All updates to a base relation should be immediately reflected in all views that reference that base relation. 基本关系修改视图修改 If view is updated, underlying base relation should reflect change. 视图修改基本关系修改,视图的更新,21,3.4 Views 视图,There are restrictions on types of modifications that can be made through views: Updates are allowed if query involves a single base relation and contains a candidate key of base relation. 可以更新的情况：一个基本关系，含候选关键字 Updates are n