Relational Algebra and Relational Calculus

Relational model is completely based on relational algebra. It consists of a collection of operators that operate on relations.

Its main objective is data retrieval. It is more operational and very much useful to represent execution plans, while relational calculus is non-operational and declarative. Here, declarative means user define queries in terms of what they want, not in terms of how compute it.

Relational Query Languages

Used for data manipulation and data retrieval. Relational model support simple yet powerful query languages. To understand SQL, we need good understanding of two relational query language (i.e., relational algebra and relational calculus).

Basic Operation in Relational Algebra

The Operations in relational algebra are classified as follows’

Selection

The select operation selection tuples/row that satisfy a given predicate or condition. We use (σ) to denote selection. The predicate/condition appears as a subscropit to σ.

e.g. Consider Me below relation STUDENT

Projection

It selects only required/specified columns/attributes from a relation/table. Projection operator eliminates duplicates (i.e., duplicate rows from the result relation) e.g., consider the STUDENT relation,

Union

If forms a relation from rows/tuple which are apperaring in either or both of the specified relations. For a union operation R U S to be valid. Below two conditions must be satisfied.

· The relations R and S must be of the same entity. i.e they must have the same number of attributes.

· The domains of the I th attributes of R and I th attribute of S must be the same, for all i.

Intersection

It forms a relation of rows/tuples which are present in both the relations R and S. As with the union operation, we must ensure that both relations are compatible.

Set Difference

It allows us to find tuples that are in one relation but are not in another. The expression R – S produces a relation containing those tuples in R but not in S.

Cross Product/Cartesian Product

Assume that we have n1 tuples in R and n2 tuples in S. Then, there are n1*n2 ways of choosing a pair of tuples; one tuple from each relation. So, there wiil be (n, * n2) tuples in result relation P if P = R x S.

Rename operation Relation name

↓

P Acct (Account)

↓

New name

Case of semi-trivial FD

Sid à Sid Sname (semi-trivial)

Because on decomposition, we will get

SidàSid (trivial FD) and

Sid à Sname (non-trivial FD)

Properties of Functional Dependence (FD)

· Reflexivity If X Y, then X Y (trivial)

· Transitivity If X Y and Y Z, then X Z

· Augmentation If X -4 Y, then XZ YZ

· Splitting or Decomposition If X -9 YZ, then X -÷ Y and X –)z

· Union IfX YandX-3Z,thenX YZ

Attribute Closure

Suppose R (X, Y, Z) be a relation having set of attributes i.e., (X, Y, Z), then (x’ be an attribute closure which functionally determines other attributes of the relit:: (if not all then atleast itself).

DBMS versus RDBMS

DBMS

RDBMS

· In DBMS, relationship between two tables or files are maintained programmatically, morganatically

In RDBMS, relationship between two tables or files can be specified at the tire of table creation

· DBMS does not support client/server architecture

· DBMS does not support distributed database

· In DBMS, there is no security of data

· Each table is given an extension in DBMS

Most of the RDBMS supports client/sen:’ architecture

Most of the RDBMS support distributed databases

In RDBMS, there are multiple levels of security

(i) Cogging in at o/s Level

(ii)Comand Level

(iii) Object level

Many tables are grouped in one datab’ in RDBMS