Relational Model　

Data Model

• Relational
• NoSQL
  • Key/Value
  • Graph
  • Document
  • Column-family
• Machine Learning
  • Array/Matrix
• Obsolete/Legacy/Rare
  • Hierarchical
  • Network
  • Multi-Value

Relational Model

• Structure: 数据库关系和内容的定义
• Integrity: 确保数据库内容满足其约束
• Manipulation: 数据库内容增删改查的编程接口

Relation

An unordered set that contains the relationship of attributes that represent entries.

$\displaystyle{ n }$-ary relation = Table with $\displaystyle{ n }$ columns

Tuple

A set of attribute values (also known as domain) in the relation.

• Values are (normally) atomic/scalar (原子量/标量)
• The special value NULL is a member of every domain

Primary key

Uniquely identifies a single tuple.

Some DBMSs automatically create an internal primary key if a table does not define one.

Auto-generation of unique integer primary keys

• SEQUENCE
• AUTO_INCREMENT

Foreign keys

Artist (id, name, year, country)

id	name	year	country
123	Wu-Tang	1992	USA
456	Notorious BIG	1992	USA

Album (id, name, artist, year)

id	name	artists	year
11	Enter the Wu-Tang	123	1993
22	St.Ides Mix Tape	???	1994

Create a new Table: ArtistAlbum (artist_id, album_id), the ids will be linked to the previous tables.

artist_id	album_id
123	11
123	22
456	22

Data Manipulation Languages (DML)

• Procedural
  • The query specifies the (high-level) strategy the DBMS should use to find the desired result.
• Non-Procedural (Declarative)
  • Not how to find it.
  • What data is wanted.

Relational Algebra

Select

Choose a subset of the tuples that satisfies a selection predicate.

Syntax: $\displaystyle{ \sigma _{ \text{predict} } \left( R \right) }$

select * from R
where a_id='a2' and b_id>102;

Projection

Generate a relation with tuples that contains only the specified attributes.

Syntax: $\displaystyle{ \Pi _{ A _{ 1 } , A _{ 2 } , \ldots , A _{ n } } R \left( \right) }$

select b_id-100, a_id
from R where a_id='a2';

$\displaystyle{ \Pi _{ \mathtt{ b _{ i d } - 100 , a _{ i d } } } \left( \sigma _{ \mathtt{ a _{ i d } = ^{\prime} a 2 ^{\prime} } } \right) \left( R \right) }$

Union

Syntax: $\displaystyle{ \left( R \cup S \right) }$

(select * from R) union all (select * from S);

Intersection

Syntax: $\displaystyle{ \left( R \cap S \right) }$

(select * from R) intersection (select * from S);

Difference

Syntax: $\displaystyle{ \left( R - S \right) }$

(select * from R) except (select * from S);

Product

Generate a relation that contains all possible combinations of tuples from the input relations.

两个表相乘，组成所有可能的组合，Tuple $\times$ Tuple

Syntax: $(R \times S)$

select * from R cross join S;
select * from R, S;

Join

Generate a relation that contains all tuples that are a combination of two tuples (one from each input relation) with a common value(s) for one or more attributes.

Syntax: $\displaystyle{ \left( R \bowtie S \right) }$

R (a_id, b_id)

a_id	b_id
a1	101
a2	102
a3	103

S (a_id, b_id)

a_id	b_id
a3	103
a4	104
a5	105

$\displaystyle{ R \bowtie S }$

a_id	b_id
a3	103