Other Integrity Constraints
■Domain integrity constraint
►An attribute's value must be a value in the domain of this attribute.This is the most basic constraint.All popular RDBMS are able to check domain integrity constraint automatically .
域完整性约束,即属性的每个值必须符合对应值域的要求,比如你把学生的年龄定义成整型,那你插入一个新元组的时候年龄就不能是小数。
■Entity integrity constraint
►Every relation should have a primary key.The value of primary key of each tuple must be unique.Primary key cannot be NULL.This is so-called entity integrity constraint.
实体完整性,即关系的主码不可以为空。因为主码如果为空,就不能唯一地标识一个元组了。
■s1和s2都是水手信息表,模式相同,rating代表的是水手的等级。
■B1是船的信息表。
■R1表是表示水手和船的预定关系表。水手需要预定船出海。这几张表的关系和前面说的学生选课基本一致,但又有差别。在选课表中,学号和课程号构成了它的主码,但在预定关系这张表中,水手号和船号却并非主码,因为同一个水手可以预定不同时间的同一条船。非要说它的主码的话,即我们前面说过的全码。
Relational Algebra
■Basic operations:
►Selection(σ)Selects a subset of rows from relation
►Projection( π)Deletes unwanted columns from relation
►Cross-product(×) Allows us to combine two relations
►Set-difference(-) Tuples in reln.1,but not in reln.2
►Union(∪)Tuples in reln.1 and in reln.2
■{σ,π,×,-,∪} is a complete operation set.Any other relational algebra operation can be derived from them.
■Additional operations:
►Intersection,join,division,outer join,outer union:Not essential ,but (very!)useful.
■Since each operation returns a relation ,operations can be composed !(Algebra is "closed".)
Projection
■Deletes attributes that are not in projection list.
■Schema of result contains exactly the fields in the projection list,with the same names that they had in the input relation.
(投影结果关系的模式就是由投影的属性列组成的)
■Projection operator has to eliminate duplicates!(Why??)
(投影操作要求删除重复元组,在下面的例子中并没有出现重复元组,但是我们如果把S2表投影在年龄这个属性列上的话,就会出现重复元组,在关系型数据库中,两个元组若所有属性值都相同,我们就认为它描述的是现实世界中的同一实体,没有意义,故要求删除重复元组。)
►Note:real systems typically don't do duplicate elimination unless the user explicitly asks for it.(Why not?)
(实际数据库系统在做投影操作的时候,除非用户显式要求消除重复元组,不然它不会自动消除。因为在不知道用户需求的情况下,消除重复元组可能会带来问题。比如说,我把S2表投影到年龄这一列,是为了算水手的平均年龄,若自动消除重复元组,结果就不对了。)
Selection
■Selects rows that satisfy selection condition.
■No duplicates in result!(Why?)
■Schema of result identical to that input relation.
■Result relation can be the input for another relational algebra operation!(Operator composition)