数据库原理笔记

Relational data model

Edgar F. Codd proposed the relational data model in 1970.
Some features:

1. Store database in simple data structures
2. Access data through high-level language
3. Physical storage left up to implementation

Physical data independence

Applications should NOT worry about how data is physically structured and stored, and they should work with a logical data model and declarative query language, leave the implementation details and optimization to DBMS.

Data model

A data model is a collection of concepts/tools for describing the data in a database.

Defination of relational data model

1. A database is a collection of relations and each
   relation is an unordered set of tuples (or rows).
2. Each relation has a set of attributes (or columns).
3. Each attribute has a name and a domain and each
   tuple has a value for each attribute of the relation.
4. Values are atomic/scalar.

Schema and instance

• Schema: specifies the logical structure of data.（数据库的组织方式，不包含实际数据）
• Instance: concrete table content w.r.t. a given schema.（数据库在某一时刻的实际数据状态）
  A schema rarely changes after being defined, while an instance often changes rapidly.
  An example:

  • Database scheme

    1. Artists (ID, Artist, Year, City)
    2. Albums (ID, Album, Artist_ID, Year)

  • Database instance

    ID	Album	Artist_ID	Year
    1	The Marriage of Figaro	1	1786
    2	Requiem Mass In D minor	1	1791
    3	Für Elise	2	1867

    Table: Albums(ID, Album, Artist_ID, Year)

    ---

    ID	Artist	Year	City
    1	Mozart	1756	Salzburg
    2	Beethoven	1770	Bonn
    3	Chopin	1810	Warsaw

    Table: Artists(ID, Artist, Year, City)

Keys

$K$ $\subseteq \{A_1, A_2, \ldots, A_n\}$ is a superkey of schema $R(A_1, \ldots, A_n)$ if values for $K$ are sufficient to identify a unique tuple for each possible relation instance of $R(A_1, A_2, \ldots, A_n)$.
A superkey $K$ is a candidate key if $K$ is minimal.

Primary keys

A primary key is a designated candidate key of a relation. Some DBMSs automatically create an internal primary key if we don’t define one.

ID Artist Year City 1 Mozart 1756 Salzburg 2 Beethoven 1770 Bonn 3 Chopin 1810 Warsaw Table: Artists(ID, Artist, Year, City)

create table Artists( ID varchar(8), Artist varchar(20) not null, Year numeric(4,0), City varchar(20), primary key (ID) )

Foreign key

A foreign key specifies that a tuple from one relation must map to a tuple in another relation.
Constraints:

• Referenced attributes must be a PRIMARY KEY.（外部指针一定是另一个表格的 primary key）
• No dangling pointers from the attributes of a foreign key.（没有空指针）

create table Albums(
    ID, varchar(8),
    Album varchar(20) not null,
    Artist_ID varchar (8),
    primary key ID,
    foreign key (Artist_ID) references Artists(ID),
)

ID	Album	Artist_ID	Year
1	The Marriage of Figaro	1	1786
2	Requiem Mass In D minor	1	1791
3	Für Elise	2	1867

Table: Albums(ID, Album, Artist ID, Year)

ID	Artist	Year	City
1	Mozart	1756	Salzburg
2	Beethoven	1770	Bonn
3	Chopin	1810	Warsaw

Table: Artists(ID, Artist, Year, City)

Relational model and algebra

• A language for querying relational data based on
  fundamental relational operations.
• Each operation takes one or more relations (i.e.,
  tables) as its input and output a new relation, so can easily composed to make comples queries.

Selection

• The selection operation selects tuples that satisfy a given predicate.
• Notation: $\sigma_p(R)$
• Example: $\sigma_{\text{dept\_name}=\text{"Physics"}}(\text{instructor})$
• Boolean connectives $=$, $\neq$, $<$, $\leq$, $>$ and $\geq$ are allowed in predicates
• Combine predicates with logical connectives $\wedge$ (and), $\vee$ (or), $\neg$ (not).

Projection

• The projection produces from an input relation $R$ a new $R`$ that has only some of $R`$s attributes.
• Notation: $\Pi_{A_1,...,A_n}(R)$
• $R$ is the input relation and $A_1$, …, $A_n$ are attributes of $R$.
• Example: $\Pi_{\text{ID}, \text{Salary}/12}(\text{instructor})$
• Duplicated output tuples are removed (by definition).

Cartesian product

• The Cartesian product (or just product) of two relations R and S, is the set of all possible combinations of tuples from R and S.
• Notation: $R\times S$

A	B
1	2
3	4

Table: R(A, B)

B	C
2	6
3	8

Table: S(B, C)

R.A	R.B	S.B	S.C
1	2	2	6
1	2	3	8
3	4	2	6
3	4	3	8

Table: R × S