15-445 lecture#1 Relational Model & Relational Algebrae

1 Databases
A database is an organized collection of inter-related data that models some aspect of the real-world (e.g.,modeling the students in a class or a digital music store). People often confuse “databases” with  “database management systems” (e.g., MySQL, Oracle, MongoDB). A database management system (DBMS) is the software that manages a database.

DBMS是管理DB的系统，DB是一组数据

2 Flat File Strawman
Database is stored as comma-separated value (CSV) files that the DBMS manages. Each entity will be stored in its own file. The application has to parse files each time it wants to read or update records. Each entity has its own set of attributes, so in each file, different records are delimited by new lines, while each of the corresponding attributes within a record are delimited by a comma.

将DB以CSV的形式保存为文本文件，应用读写记录时需要自己parse文件的含义

每个record占一行，每个atrr由逗号分开

Issues with Flat File
• Data Integrity
– How do we ensure that the artist is the same for each album entry?
– What if somebody overwrites the album year tith an invalid string?
– How do we store theat there are multiple artists on one album?
• Implementation
– How do you find a particular record?
– What if we now want to create a new application that uses the same database?
– What if two threads try to write to the same file at the same time?
• Durability
– What if the machine crashes while our program is updating a record?
– What if we want to replicate the database on multiple machines for high availability?

↑ 这一段非常重要，以后需要注意DBMS是怎么解决这些问题的

3 Database Management System
A DBMS is a software that allows applications to store and analyze information in a database.
A general-purpose DBMS is designed to allow the definition, creation, querying, updation, and administra-tion of databases.

DBMS的定义，作用

Early DBMSs
Database applications were difficult to build and maintain because there was a tight coupling between logical and physical layers. The logical layer is which entities and attributes the database has while the physical layer is how those entities and attributes are being stored. Early on, the physical layer was defined in the application code, so if we wanted to change the physical layer the application was using, we would have to change all of the code to match the new physical layer.

早期的DB应用没有把逻辑层和物理层分开，耦合在一起，开发很不方便

逻辑层是指DB有哪些entity和attr（?）
物理层则是DB是具体如何存储的

4 Relational Model
Ted Codd noticed that people were rewriting DBMSs every time they wanted to change the physical layer,so in 1970 he proposed the relational model to avoid this. This relational model has three key points:
• Store database in simple data structures (relations).
• Access data through high-level language.
• Physical storage left up to implementation.

在以前，每次人们想要修改DB的物理层，都要重写DBMS，所以提出了关系模型

• 用relation表示DB的存储
• 用高级语言来访问数据
• Physical storage left up to implementation.（？）

A data model is a collection of concepts for describing the data in a database. The relational model is an example of a data model.
A schema is a description of a particular collection of data, using a given data model.

机翻：

数据模型是描述数据库中数据的概念的集合。关系模型是数据模型的一个例子。
模式是使用给定数据模型对特定数据集合的描述。

The relational data model defines three concepts:
• Structure: The definition of relations and their contents. This is the attributes the relations have and the values that those attributes can hold.
• Integrity: Ensure the database’s contents satisfy constraints. An example constraint would be that any value for the year attribute has to be a number.
• Manipulation: How to access and modify a database’s contents.

TODO

A relation is an unordered set that contains the relationship of attributes that represent entities. Since the relationships are unordered, the DBMS can store them in any way it wants, allowing for optimization.
A tuple is a set of attribute values (also known as its domain) in the relation. Originally, values had to be atomic or scalar, but now values can also be lists or nested data structures. Every attribute can be a special value, NULL, which means for a given tuple the attribute is undefined.
A relation with n attributes is called an n-ary relation.

ralation就是指表？tuple指的是表中的一行？

Keys
A relation’s primary key uniquely identifies a single tuple. Some DBMSs automatically create an internal primary key if you do not define one. A lot of DBMSs have support for autogenerated keys so an application does not have to manually increment the keys.
A foreign key specifies that an attribute from one relation has to map to a tuple in another relation.

表中的主键用来唯一确定表中的一个tuple，有的DBMS如果你没有设置主键，会自动创建一个。很多DBMS支持设置自增主键，这样就不需要插入数据的人手动设置

外键TODO

5 Data Manipulation Languages (DMLs)
A language to store and retrieve information from a database. There are two classes of languages for this:
• Procedural: The query specifies the (high-level) strategy the DBMS should use to find the desired result.
• Non-Procedural: The query specifies only what data is wanted and not how to find it.

DML：用来访问DB，一种是命令式，一种是声明式，区别在于后者只需要说明自己需要哪些数据，而前者还需要制定如何去查找这些数据

SQL属于后者

6 Relational Model & Relational Algebra
Relational Algebra is a set of fundamental operations to retrieve and manipulate tuples in a relation. Each operator takes in one or more relations as inputs, and outputs a new relation. To write queries we can “chain” these operators together to create more complex operations.

关系代数是一组用来从relation中存取数据的运算符

Select
Select takes in a relation and outputs a subset of the tuples from that relation that satisfy a selection predicate.
The predicate acts like a filter, and we can combine multiple predicates using conjunctions and disjunctions.
Syntax: σ predicate (R).

选出所有符合条件的tuple

Projection
Projection takes in a relation and outputs a relation with tuples that contain only specifed attributes. You can rearrange the ordering of the attributes in the input relation as well as manipulate the values.
Syntax: π A1,A2,. . . ,An (R).

从relation中选出自己关注的attr（仍然是所有的tuple都在内，但是只返回给定的attr）

Union
Union takes in two relations and outputs a relation that contains all tuples that appear in at least one of the input relations. Note: The two input relations have to have the exact same atttributes.
Syntax: (R ∪ S).

union需要两个relation的attr相同

union的时候，合并两个relation的tuple，不过有一点不解，搜的时候说是要去掉重复的，但是不知道为什么本课的slides中没有去重

Intersection
Intersection takes in two relations and outputs a relation that contains all tuples that appear both of the input relations. Note: The two input relations have to have the exact same atttributes.
Syntax: (R ∩ S).

和上面相同，两个relation需要有相同的attr

取共同的tuple

Difference
Difference takes in two relations and outputs a relation that contains all tuples that appear in the first relation but not the second relation. Note: The two input relations have to have the exact same atttributes.
Syntax: (R − S).

取在R中，不在S中的

Product
Product takes in two relations and outputs a relation that contains all possible combinations for tuples from the input relations.
Syntax: (R × S).

将两个relation中的tuple进行组合，全部的组合

Join
Join takes in two relations and outputs a relation that contains all the tuples that are a combination of two tuples where for each attribute that the two relations share, the values for that attrubite of both tuples is the same.
Syntax: (R ./ S).

如果共同的attr具有相同的值，就可以进行连接

Observation
Relational algebra is a procedural language because it defines the high level-steps of how to compute a query. For example, σ b id=102 (R ./ S) is saying to first do the join of R and S and then do the select,
whereas (R ./ (σ b id=102 (S))) will do the select on S first, and then do the join. These two statements will actually produce the same answer, but if there is only 1 tuple in S with b id=102 out of a billion tuples, then
(R ./ (σ b id=102 (S))) will be significantly faster than σ b id=102 (R ./ S).
A better approach is to say the result you want, and let the DBMS decide the steps it wants to take to compute
the query. SQL will do exactly this, and it is the de facto standard for writing queries on relational model
databases.

关系代数是过程式的，因为写关系代数的时候，写明了查询数据的方式

SQL不一样