第8章データ管理

The declarative language used in managing and querying structured data in a RDBMS is called Structured Query Language (SQL). SQL implements the four sublanguages mentioned earlier: DDL, DML, DQL, and DCL. SQL is based on relational algebra with many extensions. SQL supports primary keys, foreign keys, inserting data, deleting data, updating data, indexing, recovery, concurrency, and security. SQL focuses on efficiency in addition to specifying the data needed. The main commands in SQL are CREATE TABLE, which is used to create a new table in a database such as:

which will create a table name Student with three columns: ID, LastName, and ZipCode. DROP TABLE is used to drop an existing table in a database, and ALTER TABLE, which is used to add, delete, or modify columns in an existing table. SQL schemas contain tables (i.e., relations), and the RDBMS catalog stores these schemas. SQL constraints are used to specify rules for the data in a table to ensure the accuracy and reliability of that data. There are additional features of SQL such as triggers, views, nested/recursive queries, privileges, and metadata.

The theoretical framework for querying relational databases that uses unary and binary operators on relations to manipulate and retrieve tuples is called relational algebra. The following set of relational algebra operations is a complete set: Select, Project, Union, Rename, Difference, and Cartesian product. It is a complete set because any relational algebra operation can be expressed as a sequence of operations from this set.

Relational algebra operators include set operators and relational operators. The set operators include Union, Intersection, Difference, and Product, which are all binary operators because they use two relations as operands and produce a new relation as a result. In some cases, the operand relations need to be union compatible, which implies that they have the same number of attribute types defined on the same domains. In what follows, we represent a relation as R and its cardinality or number of tuples as |R|.

The Union operator applied to two union-compatible relations P and Q results into a new relation $R=P\cap Q$ so:

$R=\left\{t\right|t\in P\text{OR}t\in Q\}$ with $\max \left(\right|P|,|Q\left|\right)\le \left|R\right|\le \left|P\right|+\left|Q\right|$

The resulting relation R consists of the tuples that either belong to P or Q, or both. Duplicate tuples are eliminated.

The Intersection operator applied to two union-compatible relations P and Q results in a new relation $R=P\cap Q$ such that:

$R=\left\{t\right|t\in P\text{AND}t\in Q\}$ with $0\le \left|R\right|\le \min \left(\right|P|,|Q\left|\right)$

The resulting relation R consists of the tuples that belong to both P and Q.

The Difference operator applied to two union-compatible relations P and Q results in a new relation $R=P-Q$ such that:

$R=\left\{t\right|t\in P\text{AND}t\notin Q\}$ with $0\le \left|R\right|\le \left|P\right|$

The resulting relation R consists of the tuples that belong to P but not to Q. Note that when using the Difference operator, the order of the operands, is important because it concerns a noncommutative operation.

The Product operator (also known as the Cartesian product, Cross Product, or Cross Join operator) returns the Cartesian Product of two relations. Consider the following two relations: a supplier relation P and a product relation Q. The Cartesian Product of both relations consists of all possible combinations of tuples from P and Q. The resulting relation $R=P\times Q$ consists of a set of tuples $r=pq$ such that:

$R=\{r=pq|p\in P\text{AND}q\in Q\}$ with $\left|R\right|=\left|P\right|\times \left|Q\right|$

The relational operators are Select, Project, Rename, Join, and Division. The Select, Project, and Rename operators are unary operators because they only use one operand. The Join and Division operators are binary operators because they use two operands.

The Select operator is used to return a subset of tuples of a relation based on a selection condition. The latter can consist of predicates combined using Boolean operators (and, or, not). Every predicate consists of a comparison between the values of two domain-compatible attribute types, or between an attribute type and a constant value from the domain of the attribute type.

The result of applying a Select operator with a selection condition S on a relation P can be represented as:

$R={\sigma }_S\left(P\right)$ with Select operator $\sigma$

The Project operator is used to project a relation on a list of attribute types. The result of applying a Project operator with a list L on a relation P can be represented as:

$R={\pi }_L\left(P\right)$ with Project operator $\pi$

Note that applying a Project operator to a relation results in a vertical subset of the relation with $\left|R\right|\le \left|P\right|$ because duplicate tuples will be eliminated.

The Rename operator is used to rename an attribute type of a relation (in case, for example, naming conflicts can occur). The result of applying a Rename operator on a relation P to rename attribute type B to A can be represented as:

$R={\rho }_{A|B}\left(P\right)$ with Rename operator ρ

The Join operator allows combining tuples of two relations based on specific conditions, also called join conditions. The result of a join is the combined effect of a Cartesian product (×) and selection. The result of applying a Join operator to two relations P and Q with join condition j can be represented as follows:

$R=P{\bowtie }_{j}Q$ with Join operator ⋈ and join condition j.

The resulting relation R consists of a set of combinations of pq tuples such that a combined tuple r ∈ R is characterized by:

$R=\{r=pq|p\in P\text{AND}q\in Q\text{AND}j\}$ with $\left|R\right|\le \left|P\right|\times \left|Q\right|$

The Division operator is a binary operator that divides a relation P by a relation Q with $Q\subseteq P$. The result of a Division operator can be represented as:

$R=P÷Q$ with Division operator ÷

The resulting relation R includes all tuples that belong to P combined with every tuple in Q. Note that the Division operator is not directly implemented in SQL.

The Select (σ), Project (π), Difference (–), Product (×), and Union (∪) operators are often referred to as the five primitive operators because all other relational algebra operators can be derived from them (derived operators).

SQL also allows aggregate functions and grouping operations. For join operations, there are many options, such as the following:

• A theta join allows merging two tables based on a theta condition; theta (θ) refers to the comparison operator in the join condition. Depending upon theta, these joins can be distinguished: greater-than-join, less-than-join, greater-than-or-equal-join, less-than-or-equal-join, and an equi-join (if an equals occurs). An equi-join combines tables based on matching values in specified columns; it is equivalent to a theta join, with theta being the equal comparison operator in the join condition.

• A natural join creates an implicit join based on the common columns in two tables; it is a variant of the equi-join in which one of the shared join-attribute types is removed from the result.

• An inner join represents the intersection of two tables; all joins discussed thus far are inner joins because they do not include tuples lacking corresponding join values in the result.

• An outer join represents the union of two tables; the outer join operator will also include tuples lacking corresponding join values in the result.

A distinction can be made between a left outer join, right outer join, and full outer join. A left outer join includes all tuples from the left relation (P) in the result, either matched with a corresponding tuple from the right relation (Q) based on the join condition or augmented with null values in case no match with a tuple from Q can be made. A left outer join can be represented as $R=P{\bowtie }_{j}Q$. A right outer join includes all tuples from the right relation (Q) in the result, either matched with a corresponding tuple from the left relation (P) based on the join condition or augmented with null values in case no match with a tuple from P can be made. A right outer join can be represented as $R=P{\bowtie }_{j}Q$. A full outer join includes all tuples from P and Q in the result either matched with the counterparts according to the join condition or augmented with null values in case no match can be found. A full outer join can be represented as $R=P{\bowtie }_{j}Q$.

An example of data structure representation for the relational algebra expression is a query tree.