Define the primary key. Explain the following relational operations with suitable example : (i) Select (ii) Project (iii) Union (iv) Intersection (v) Division

 A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows in that table. For example, in the table below, CustomerNo, which displays the ID number assigned to different customers, is the primary key.

Relational Operations

Given this simple and restricted data structure, it is possible to define some very powerful relational operators which, from the users' point of view, act in parallel' on all entries in a table simultaneously, although their implementation may require conventional processing.

Codd originally defined eight relational operators.

·         SELECT

·         PROJECT

·         UNION

·         INTERSECT

·         DIVIDE

The most important of these are (1), (2), (3) and (8), which, together with some other aggregate functions, are powerful enough to answer a wide range of queries. The eight operators will be described as general procedures - i.e. not in the syntax of SQL or any other relational language. The important point is that they define the result required rather than the detailed process of obtaining it - what but not how.

SELECT
RESTRICTS the rows chosen from a table to those entries with specified attribute values.

     SELECT item
     FROM stock_level
     WHERE quantity > 100

constructs a new, logical table - an unnamed relation - with one column per row (i.e. item) containing all rows from stock_level that satisfy the WHERE clause.

PROJECT
Selects rows made up of a sub-set of columns from a table.

     PROJECT stock_item
     OVER item AND description

produces a new logical table where each row contains only two columns - item and description. The new table will only contain distinct rows from stock_item; i.e. any duplicate rows so formed will be eliminated.

UNION
Builds a relation consisting of all rows appearing in either or both of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A: a     B: a     =>     A union B: a
          b        e                       b
          c                                 c
                                           e

 

INTERSECT
Builds a relation consisting of all rows appearing in both of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A:  a     B: a     =>     A intersect B: a
          b         e
          c

 

DIVIDE
Takes two relations, one binary and one unary, and builds a relation consisting of all values of one column of the binary relation that match, in the other column, all values in the unary relation.

     A: a  x     B: x     =>     A divide B: a
          a  y        y
          a  z
          b  x
          c  y