Реклама:

info.krc.karelia.ru

win -:|:- koi -:|:- iso -:|:- dos -:|:- mac

Start -:|:- Проекты -:|:- О нас

Operations in the Relational Data Model

In the previous section (Relational Data Model Formalities) we defined the mathematical notion of the relational model. Now we know how the data can be stored using a relational data model but we do not know what to do with all these tables to retrieve something from the database yet. For example somebody could ask for the names of all suppliers that sell the part 'Screw'. Therefore two rather different kinds of notations for expressing operations on relations have been defined:

Relational Algebra

The Relational Algebra was introduced by E. F. Codd in 1972. It consists of a set of operations on relations:

For a more detailed description and definition of the relational algebra refer to [Ullman, 1988] or [Date, 1994].

Example 59-3. A Query Using Relational Algebra

Recall that we formulated all those relational operators to be able to retrieve data from the database. Let's return to our example from the previous section (Operations in the Relational Data Model) where someone wanted to know the names of all suppliers that sell the part Screw. This question can be answered using relational algebra by the following operation:

πSUPPLIER.SNAMEPART.PNAME='Screw'(SUPPLIER ∏ SELLS ∏ PART))
      

We call such an operation a query. If we evaluate the above query against the our example tables (The Suppliers and Parts Database) we will obtain the following result:

                             SNAME
                            -------
                             Smith
                             Adams
      

Relational Calculus

The relational calculus is based on the first order logic. There are two variants of the relational calculus:

We want to discuss the tuple relational calculus only because it is the one underlying the most relational languages. For a detailed discussion on DRC (and also TRC) see [Date, 1994] or [Ullman, 1988].

Tuple Relational Calculus

The queries used in TRC are of the following form: x(A) ∣ F(x) where x is a tuple variable A is a set of attributes and F is a formula. The resulting relation consists of all tuples t(A) that satisfy F(t).

If we want to answer the question from example A Query Using Relational Algebra using TRC we formulate the following query:

     {x(SNAME) ∣ x ∈ SUPPLIER ∧ \nonumber
                       ∃ y ∈ SELLS ∃ z ∈ PART (y(SNO)=x(SNO) ∧ \nonumber
                        z(PNO)=y(PNO) ∧ \nonumber
                        z(PNAME)='Screw')} \nonumber
     

Evaluating the query against the tables from The Suppliers and Parts Database again leads to the same result as in A Query Using Relational Algebra.

Relational Algebra vs. Relational Calculus

The relational algebra and the relational calculus have the same expressive power; i.e. all queries that can be formulated using relational algebra can also be formulated using the relational calculus and vice versa. This was first proved by E. F. Codd in 1972. This proof is based on an algorithm ("Codd's reduction algorithm") by which an arbitrary expression of the relational calculus can be reduced to a semantically equivalent expression of relational algebra. For a more detailed discussion on that refer to [Date, 1994] and [Ullman, 1988].

It is sometimes said that languages based on the relational calculus are "higher level" or "more declarative" than languages based on relational algebra because the algebra (partially) specifies the order of operations while the calculus leaves it to a compiler or interpreter to determine the most efficient order of evaluation.