数据库系统复习1. 关系模型

数据库复习1. 关系模型

The Relational Model(关系模型)

• A data model that represents entities and the relations between entities in the form of a two-dimensional table. Relational data model is based on the concept of relation in set theory.关系模型是指用二维表的形式表示实体和实体间联系的数据模型。依托于集合论中的关系的概念。

Relations 关系

1. May be thought as Table.可以被认为是Table。

2. 每一行叫做一个元组，列头叫做属性，每一列可以出现的值叫做定义域。

   • A row is called a tuple.
   • A column header is called an attribute.
   • The data type describing the types of values that can appear in each column is called a domain

3. Each row in table represents collection of related data values. Table中每一行都由相关联的数值组成。

4. Table name and column names are used to help interpret meaning of values in each row. e.g. Little Student ( Name,Age,RegNum,Major ) 表名和列名被用来确定每一行的数值的含义。如Little Student ( Name,Age,RegNum,Major )

Domain(定义域)

• A set of atomic values. Domain是一组原子性值的集合。（原子性的值：不重复的值）
• The domain of an attribute A is denoted by dam ( A ). For example,*dam* ( *Major )* = { CSc,Phy,Chem, Bio,Env }, *dam* ( Age ) = { N} where 16<= n <= 80 and n ε N. A属性的定义域用dam(A）表示。
• A domain has a data type associated with it, e.g. 定义域的数据类型应该是与之相关的，例如
  • Student age must be an Integer 学生年龄一定是Integer型
  • Student name must be a String 学生姓名一定是String型

Relations and Attributes

A relation schema R denoted byR( A1,A2, ..., An )一个关系结构R用R ( A1,A2, ..., An )的形式表示

• A relation name R . R代表关系的名称（即表名）

• A list of attributes A1, A2, …, An. A代表属性名

• e.g. Little Student ( Name,Age,RegNum,Major ) 举例：

Degree 度

The degree of a relation *R* ( A1,A2, ..., An ) is the number of attributes *n* of its relation schema.
Degree指关系结构R中属性A的数量n

e.g. A relation schema for a relation of degree 4 (describing university students) is: LITTLE STUDENT ( Name,Age, RegNum, Major )举例：LITTLE STUDENT ( Name,Age, RegNum, Major )的degree是4.

Tuples(元组)（即Table中的行）

• A relation instance r of the relation schema R ( A1, A2,...,An ), also denoted byr ( R), is a set of n-tuples:*r* = { *t1, t2,* ..., tm }
  一个关系结构R ( A1, A2,...,An )的关系实例被表示为r ( R), r® 是一个n阶元组的集合: r= { *t1, t2,* ..., tm }

• For example, in LITTLE_STUDENT t1 might be ( John, 23, 123456, CSc ), which is a quadruple.

  举例：LITTLE_STUDENT表中 t1 是 四阶元组( John, 23, 123456, CSc ）

• A tuple could alternatively be considered as a set of (<attribute>, <value>)pairs.

  一个元组也可以被写成这种（属性，值）的形式。例如：（RegNum,123456）
  

• each tuple is distinct (unique)

  表中的每个元组必须是不同的。

• Ordering of tuples is immaterial， it could be randomly ordered. e.g.r = { t1, t2, t3} = {t2, t1, t3} But ordering of values in n-tuple (or columns in relation) is Significant, Each n-tuple t is an ordered list of n values*t* = <V1, V2, ..., Vn> 不同元组之间的顺序可以是随机的， 比如r = { t1, t2, t3} = { t2, t1, t3}虽然表中元组顺序可以不同，但是n阶元组t = <V1, V2, ..., Vn> 内部t值的顺序是很重要的，因为它代表了表中列的顺序。
• 简而言之，表中行的顺序可以任意改变但是列的顺序不能任意改变

Superkey 超键

• The set of attributes that nuiqually identify a single tuple in a relationship is called the superkey of the relational schema.

  在关系中能唯一标识元组的属性集称为关系模式的超键。

• A single property can be used as a superkey, or a combination of properties can be used as a superkey.

  一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。

• 假设上表中，{RegNum}是唯一的，那么{RegNum}是一个超键。同时{FName,RegNum}的组合也是唯一的，所以也可以为一个超键。但当插入{FName,RegNum}两个相同值的情况时，就会出错不允许插入。{Lname}存在两个相同的值Simpson和Flanders,所以属性{Lname}不能作为超键。

Key 键（最小超键）

A key K of a relation schema R is a super key of R with the additional property that removing any attribute A from K leaves a set of attributes K’ that is not a super key of R.

Key是一个超键去掉多余属性外剩下的属性。

• In Table Student, {RegNum}is a SuperKey and {FName, RegNum,Dname} is also a SuperKey. Then {FName} and {Dname} are additional property.

  在Student表中，{RegNum} 是一个超键，{FName, RegNum,Dname} 也是超键，但是{RegNum}已经可以唯一标识元组了，因此{FName, Dname}就是多余属性。

• A key is a minimal superkey. Key是最小的超键。

• While the attribute set { FName, RegNum,Dname } is a superkey but not a key because if we get rid of either "Fname” or "Dname” or both, what’s left will still act as a superkey.

{ FName, RegNum,Dname }是一个SuperKey但不是Key因为去掉属性{FName}或{Dname}或同时去掉二者后仍然剩余一个superkey,说明 { FName, RegNum,Dname }不是最小的超键。

Candidate Key 候选键

If a relation schema has more than one key, each of the keys is called a candidate key.

一张表中存在不止一个key时，每一个key被称为候选键。

Primary Key 主键

• One of the candidate keys of a relation is designated as the primary key. 候选键中的一个被人为规定为主键。

• Attributes that define the primary key of a relation schema are underlined,for example.

  作为主键的属性用下划线标识，例如：LITTLE STUDE NT ( Name,Age, <u>RegNum</u>, Major }

• No primary key va lue can be NULL. 主键值不能为NULL

Foreign Key 外键

A tuple in one relation R1refers to a tuple in another relationR2. It is called a Foreign Key.

在一个表中存在的另一个表的主键称为此表的外键。

• A set of attributes FK in relation schema R1 is a foreign key of R1 referencing relation R2 if it satisfies the following two rules:

  由外键关联的两个表必须满足下列两个规则：

1. Rule 1:Attributes in FK of R1 have same domain(s) as primary key attributes PK ofR2. R1的外键和R2的主键定义域相同。
2. Rule2（Referential Integrity Constraints 引用的完整性约束）：Value of FK in tuple t1of current stater1( R1) either occurs as a value of PK for some tuplet2in current state r2( R2)or is NULL，In former caset1 [ FK ] = t2 [ PK ] R1中的外键中某个元组的值必须找到相对应的R2中的主键的某个元组的值

• The tuple t1 references tuple t2：R1 is referencing relation* and R2 is referenced relation.

Integrity Constraints完整性约束

1. Domain constraints定义域的约束

• Data types 数据类型
• Data Formats 数据格式
• Range of values 取值范围

2. Keys 键的约束

• Uniqueness 唯一性
• Non-null primary keys 主键不能含有NULL值

3. Foreign Keys 外键约束

• Referential integrity 引用完整性