大型共享数据库的关系数据模型

Tianqing Zhang SCU
3
Key Concepts
• 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 最简单 二维表
– A set of relation schemas, each with a distinct names.
Tianqing Zhang SCU
8
Properties of Tables
• A distinct relation name 关系不重名 • Every cell is an atomic (single) value 单元格不可再分 • A distinct attribute name 属性不重名 • An attribute values are from the same domain 一个属性
• Entity Integrity 实体完整性
– (Candidate) keys must be unique. 码值唯一 – In a relation, no attribute of a primary key can be null.
基本表主码的诸属性均不能为空
Tianqing Zhang SCU
– The cardinality of a relation is the number of tuples it cwenku.baidu.comntains.
Tianqing Zhang SCU
7
Relational Data Structure 3
• Relational database 关系数据库
– A collection of normalized relations with distinct relation names.
来自同一个域
• no duplicate tuples 元组不重复 • the order of attributes has no significance 列的顺序无所
谓
• the order of tuples has no significance （理论上）行的顺 序无所谓（实际会影响元组访问效率）
Tianqing Zhang SCU
10
Keys 2
– First, the semantics of the application 应用语义
– Secondly, pragmatic considerations such as the storage requirements of the values and the cost of comparisons 存储、代价
• Extension 外延
– The tuples are called extension (or state)
• Degree (arity) 目，度
– The degree of a relation is the number of attributes it contains.
• Cardinality 基数
• Foreign key 外码
– An attribute or a set of attributes within a relation that matches the candidate key of some relation
• Example:CKs? PKs? FKs?
– Bank(BankNo,BankName,TelephoneNumber) – Branch (BankNo,BranchNo, Street, City, Postcode) – Customer(CustomerNo, Fname, Lname) – Account(CustmerNo,BranchNo,Balance)
13
Relation Integrity 2
• Referential Integrity 参照完整性
– If a foreign key exists in a relation, either 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. 外码取参照码值或取空值
– 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.
• Example 1:
Tianqing Zhang SCU
15
Relation Integrity 4
• Example 2:
Tianqing Zhang SCU
16
1.2.2 Relational Operations
• DDL
• DML
– Update Operations 更新
• Tuple (row, record) 元组（行，记录）
– A tuples is a row of relation.
Tianqing Zhang SCU
6
Relational Data Structure 2
• Intension 内涵
– The structure of a relation, together with a specification of the domains and any other restrictions on possible values
• Brief 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…
(412) 5556782
5216
(412)
Beckwo 376-
od #3 8888
5589
(412)
Joplin 555-
#23
0962
Tianqing Zhang SCU
5
Relational Data Structure 1
• Relation
– A relation is a table with columns and rows. 二维表
Tianqing Zhang SCU
12
Relation Integrity 1
• Nulls 空值
– Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.
Tianqing Zhang SCU
2
1.2.1 Key Concepts
• Data Model
– An integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on data in an organization.
1.2 Relational Databases
Tianqing Zhang School of Computer (School of Software) SCU 2006
Background
• The relational Database Management System (RDBMS) has become the dominant dataprocessing software in use today.
• A value of type integer requires less space than any character string of length greater than 4, and comparing numbers is much cheaper than comparing character strings. 整数常常比长字符串好
– A data model can be thought of as comprising three
components: 数据模型三要素
• Data structure 数据结构 • Data manipulation 数据操纵 • Constraints of data integrity 数据的完整性约束
• 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，备选码）
• 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 关系数据库模式
Tianqing Zhang SCU
9
Keys 1
• Super key 超关键字（超码）
– One or more columns whose values are sufficient to identify a row.
– Key is a property of a table schema.
Tianqing Zhang SCU
4
Member
MemNo
DriverLi c
Fname
MI
101 5876588 John M.
102 6878599 Susan W.
106 4290955 Julia C.
Lname McMilla
n
Jones
Liu
Address
PhoneNu mber
711 Piney Woods
• A composite key, which consists of more than one column, is more expensive than a simple, single-attribute key.单属性主 码比复合主码好
Tianqing Zhang SCU
11
Keys 3
• Enterprise constraints 用户定义的完整性
– Additional rules specified by the users or database administrators of a database
Tianqing Zhang SCU
14
Relation Integrity 3
• 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.
– The most formal of all data models. It is based on the mathematical concept of a relation in set theory: a table is a relation 集合论为理论基础
– Example：the MEMBER table