2018年3月6日
09:54
写在前面
本笔记仅仅是本人在上课时的一些随手记录，并不完整也不完全正确。
如有错误，请在评论中或直接联系我指正，谢谢！
原始文件下载：(mht)(pdf)
Outline
- 概念
- 关系代数
- SQL查询语言
Why Study RDM?
- Extremely useful and simple
- 2D tables
- Allows clean yet powerful manipulation languages
- Most widely used model
- Competitors: object-oriented model, semi-structured (XML) model
Example:
Instructor: Relation name
每一行为一个tuple（记录）汉语为元组
Name of all the attributes(属性): ID name dept_name…
每一个实例为一个relation instance
Relation -> table
Tuple -> row
Attribute -> column
Attribute types
The set of allowed values for each attribute is called the domain of the attribute.
Attribute values required to be atomic(indivisible)
Null 空值: the value is unknown or doesn’t exist
Relation Schema and Instance
规范化表达
A1-An Attributes
R=(A1, A2,…) relation schema
Example: instructor = (ID, name, dept_name, salary)
Formally, given sets D1, D2,… a relation r is a subset of D1 x D2 x …
Thus, a relation is a set of n-tuples (a1, a2…) where each ai belongs to Di
The current values (relation instance) of a relation are specified by a table
An element t of r is a tuple, represented by a row in a table
Order of tuples is irrelevant (normally in an arbitrary order)
Attributes of a relation form a set, not a list!
Keys
How tuples within a given relation are distinguished?
K为R子集
- K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation 超级码 区分元组的属性
- By possible r we mean a relation r that could exist in the enterprise we are modeling
- 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 (assuming no two customers can possible have the same name), and no subset of it is a superkey.
- Primary Key: a candidate key chosen as the principle means of
identifying tuples within a relation 主码 被选用为区分元组的候选码- Should choose an attribute whose value never, or very rarely, changes(e.g. email addresses)
- Foreign key: a relation schema may have an attribute that corresponds to the primary key of another relation. The attribute is called a foreign key. 外码
- Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation.
- 将两张表的两个元组联系起来（两张表中可能存在相同的属性列，其在一张表中为主码，另一张表中则为外码）
在录入数据时，先录入主码表
Schema Diagrams
模式图
Relational Query Languages
关系代数
A query language is a language in which a user requests information from the database.
基于关系的一些运算，基于运算符和运算数，访问查询数据库中的数据，一种数学表达
选择运算
σ A=B and D>5(r) 选择A=B且D>5的元组
投影运算
Π A, C (R) 取A，C属性并去重
连接运算 - 笛卡尔乘积
r x s
并集运算
r ∪ s
差集运算
列数相等且同质
r - s
Natural join
r ⋈ s
输出相同列中有相同值的元组的集合