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