
Quick Contact
DBMS Tutorial
- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
What is Relational Calculus?
Relational calculus is the Non-Procedural Query Language. It is a query system wherein queries are expressed as formulas consisting of several variables and an expression involving these variables. Such formulas describe the properties of the required result relation without specifying the method of evaluating it. So, in relational calculus, there are no definitions of how to calculate the query; a relational calculus defines what is to fetch quite than how to fetch it.
Example: Consider the three tables
- S (Suppliers) TableThe S table contains for each supplier, a supplier no., name, status code, and location.
- P (Parts) TableThe P table contains for each part, a part number, name, color, weight, and location where the part is stored.
- SP (Shipments) TableThe SP table contains for each shipment, a supplier no., a part number and the quantity shipped as shown in the figure:
S Table
S# | SNAME | STATUS | CITY |
---|---|---|---|
S101 | Gaurav | 20 | Karnal |
S201 | Mahesh | 10 | Rohtak |
S301 | Ramesh | 30 | Rohtak |
P Table
P# | PNAME | COLOR | WEIGHT | CITY |
---|---|---|---|---|
P101 | Nut | Red | 12 | Karnal |
201 | Bolt | Green | 17 | Rohtak |
P301 | Screw | Blue | 12 | Ambala |
P401 | Screw | Red | 14 | Karnal |
SP Table
S# | P# | QTY |
---|---|---|
S101 | P101 | 300 |
S101 | P201 | 200 |
S101 | P301 | 400 |
S201 | P101 | 300 |
S201 | P201 | 400 |
S301 | P201 | 200 |
Types of Relational Calculus
There are two types of relational calculus which are as follows.
Tuple Relational Calculus (TRC)
Tuple Relational Calculus is the Non-Procedural Query Language. It was originally proposed by Dr.E.F. Codd in 1972. It defines the desired record without giving a particular procedure for obtaining the records.
Syntax of Tuple Relational Calculus (TRC)
{T | P (T)} or {T | Condition (T)}
Where
T is the following tuples
P (T) is the condition/formulas used to retrieve T.
A formula in tuple relational calculus is made out of atoms. An atom has one of the following structures:
- s ∈ r, where s is tuple variable, and r is the relation.
- s[x]θ u[y], where s and u are tuple variables, x is an attribute on which s is described, y is an attribute on which u is described, and θ is a comparison operator(<,≤,>,≥,=,≠). The attributes x and y should have domains that can be compared by q.
- s[x]θ c, where s is a tuple variable, x is an attribute on which s is described, q is a comparison operator, and c is the constant from the domain of attribute x.
A formula is built from atoms using the following rules:
-
- An atom is the formula.
If P1 is a formula, then P1 and (P1) are also formulae.
- If P1 and P2 are formulae, then P1⋀P2,P1∨P2 and P1⟹P2 are also formulae.
- If P1(s) is a formula including free tuple variables s and r is the relation, then ∃ s ∈ r (P1(s))and ∀ s∈r (P1(s)) are also formulae.
The following formulae are similar:
- P1⋀P2 is similar to ¬(¬P1∨P2).
- ∀t∈r (P1(t)) is similar to ¬∃ t∈r(¬P1(t)).
- P1⟹P2 is similar to ¬P1⋁P2.
The basic construct of tuple calculus is a tuple calculus expression. Tuple calculus expressions are made up of the following constructs or elements.
-
Tuple Variable
A tuple variable is a variable that ‘ranges over’ some named relation, i.e., a variable whose only permitted values are tuples of that relation. For example, Get supplier number for suppliers in Karnal can be expressed as:
RANGE OF SX is S
RETRIEVE (SX.S#) WHERE SX.CITY=”KARNAL”.The tuple variable here is SX, which ranges over relation S.
Tuple variables are denoted by uppercase letters. For example,T,U,V, etc. If the tuple variable T represents tuples T (at a given time), then the expression T.A represents the A components of T (at that time), where A is an attribute of the relation over which T ranges.
-
Conditions
Conditions are of the form x*y, where * is any relational operator =,!=(not equal to), <,≤,>,≥ and at least one of the x & y is an expression of the form T.A, and other is either a similar expression or a constant.
Example
-
- SX.CITY=”KARNAL”.
- SX.S#=SPY.S#
-
-
Well-Formed Formulas (WFFs)
A WFF is constructed from conditions, Boolean Operators (AND, OR, NOT), and quantifier (∃,∀) according to the following rules:
- Every condition is WFF.
- If f is a WFF, then (f) and NOT (f) are also WFFs.
- If f & g are WFFs, then (f AND g) and (f OR g) are also WFFs.
- If f is WFF in which T occurs as a free variable, then ∃ T(f)and ∀ T(f) is WFFs.
- Nothing else is a WFF.
Example
-
- SX.CITY=”KARNAL”.
-
- NOT (SX.CITY=”KARNAL”.
-
- SX.S#=SPY.S# AND SPX.P#=”P101″.
- Free & Bound Variables: Each occurrence of a tuple variable within a WFF is either free or bound.
-
Criteria for Free & Bound Variables
- Within a condition, all tuple variable occurrences are free.
- Tuple variable occurrence in the WFFs (f), NOT (f) are free/bound according to as they are free/ bound in f.
- Tuple variable occurrence in the WFFs (f AND g), (f OR g) are free/bound according to as they are free/ bound in f or g.
- Occurrences of T that are free in f are bound in the WFFs ∃ T(f),∀ T(f). Other tuple variable occurrences in f are free/bound in these WFFs as they are free/bound in f.
-
Tuple Calculus Expression
Form of expression is
Where T, U,………V are tuple variables; A, B,……..C are attributes of the associated relations, and f is a WFF containing exactly T, U,………V as free variables. The value of this expression is a projection of that subset of the Cartesian product T X U X…..X V for which f calculates to true. If “where f” is omitted, then the value is a projection of the entire cartesian product.
T.A,U.B,…………,V.C [WHERE f]Example
Let SX is tuple variable range over relation S
-
- SX.S#: It denotes the set of all supplier’s numbers in relation to S.
-
- SX.S#, WHERE SX.CITY=”KARNAL”: It denotes that the subset of those supplier number for which city is KARNAL.
- To make supplier integer for suppliers who supply portion P201.
SX.S# WHERE (SX.S#=SPX.S# AND SPX.P#=”P201″)
-
Query Examples for Tuple Relational Calculus
Example: Consider the schema given below:
Deposit (Cust-Name, Account-No)
Loan (Cust-Name, Loan-No, amount)
-
- Get information on the loans that have amount>100000.
{t|t∈loan ⋀t[amount]>100000}
-
- Find the loan numbers of the loans for which the amount is more than 100000.
{t|∃ s∈loan (t[loan-number]=s[loan-number]⋀s[amount]>100000)}
-
- Find the names of the customers who are having a loan or account or both.
{█(t|∃ s∈loan (t[cust-name]=s[cust-name]⋀@∃ u∈deposit (u[cust-name]=s[cust-name])))}
Domain Relational Calculus (DRC)
It was suggested by Lacroix and Pirotte in 1977. The domain relational calculus differs from the tuples calculus in that its variable ranges over domain rather than relations.
Syntax of Domain Relational Calculus (DRC)
{< x1,x2,……xn>|P(x1,x2,……xn)}
Where
x1,x2,……xn represent domain variables.
P symbolize a formula, which is collected of atoms, as in the method of tuple relational calculus.
An atom in the domain relational calculus has one of the following forms:
- < x1,x2,……xn>∈r, where r is a relation on n attributes and x1,x2,……xn are domain variables or domain constants corresponding to the respective domains of n attributes of relation r.
- xθy,where x and y are domain variables and θ is a comparison operator (<,≤,>,≥,=,=∕=), x and y should have domains which can be compared.
- xθc,where x is the domain variable, θ is the comparison operator, and c is the constant in the domain of x.
A formula is built from atoms using the following rules:
- An atom is the formula.
- If P1 is the formula, then so are ¬ P1 and (P1).
- If P1 and P2 are formulae, then P1⋀P2,P1∨P2 and P1⟹P2.
- If P1(t) is a formula in t, where t is a free domain variable, then, ∃ t (P1(t)) and ∀ t(P1(t)) are also formulae.
Expression of the domains calculus are constructed from the following elements:
-
-
Domain Variables
Domain variables are denoted by uppercase letters. For example,D,E,F, etc. Each domain variable is constrained to range over some specified domain.
-
Conditions
The condition can take two forms:
-
- Simply Comparisons: The form is x * y, same as for the tuple calculus, except that x & y are now domain variables (or constants).
- Membership Condition: The form is R (term, term,…..). Here R is a relation, and each “term” is a pair A: V, where A is an attribute of R, and V is either a domain variable or a constant.
Example
SP (S#:’S1’, P#: ‘P1’) (which evaluates to true if and only if there exists an SP tuple having S#=’S1’& P#=’P1’)
-
- Well-Formed Formulas (WFFs)
-
It is similar to tuple calculus.
-
- Free & Bound Variables
It is also similar to tuple calculus.
-
- Domain Calculus Expression
A domain calculus expression is then an expression of form D, E,….F [WHERE f] where D, E,…..F are domain variables & f is a WFF containing exactly D, E,….F are free variables. The values of this expression is that subset of the Cartesian product D x E x …….x F (where D, E,….F range over all their possible values) for which f evaluated to true or if “WHERE f\””is omitted that entire Cartesian product.
Example
SX
SX WHERE S (S#: SX)
SX WHERE S(S#: SX, CITY:”KARNAL”)
The first of these denotes the set of all supplier numbers; the second denotes the set of all supplier numbers in relation to S, and the third denotes the set of all supplier numbers from relation S for suppliers located in Karnal.
Query Examples for Domain Relational Calculus
Example: Consider the schema given below:
Deposit (Cust-Name, Account-No)
Loan (Cust-Name, Loan-No.amount)
- Get information on the loans that have amount>100000.{< c,l,a>|< c,l,a>∈loan^a>100000}
- Find the loan numbers of the loans for which the amount is more than 100000.{< l>| ∃ c,a (< c,l,a>∈loan ⋀a>100000)}
- Find the names of the customers who are having a loan or account or both.{< c>| ∃ l,a (< c,l,a>∈loan)∨∃ ano(< c,ano>∈deposit)}
Enroll Yourself in Live Training: DBMS Training