Comparison of Relational Calculus and Relational Algebra

SOLVED PROBLEMS

Problem 1. Consider the following database schema:

Opening (AccountNumber, OpenDate, OpeningBalance, TotalDeposit, TotalWithdrawal, ClosingBalance, ClosingBalanceDate, LastDepositDate, LastWithdrawalDate)

Deposit (AccountNumber, Date, Amount, Mode)

Withdrawal (AccountNumber, Date, Amount, Mode)

Account Holder (AccountNumber, Name, BuildingNumber, AreaName, StreetNumber, CityCode, PinCode, StateCode)

Cities (CityCode, CityName, StateCode)

State (StateCode, StateName)

Write the following queries in Relational algebra, tuple relational calculus and domain relational calculus.

1. List of all Account Number having no deposits in Jan, 2000.
2. List of all Account Number having neither any deposit nor any withdrawal in Jan, 2000.
3. List of Account Number and Name of Account holders from city ROHTAK whose opening balance is not less than 9999.
4. List of all cities of the state HARYANA.
5. List of all Account Number who belongs to state Haryana.
6. List of all city Names from  which there  is no Account  Holders.
7. List of all Account Number, which do not have any transaction after opening.
8. List of all city name and their pin code for cities of state RAJASTHAN.

Solution. Relational Algebra

Tuple Relational Calculus

Domain Relational Calculus

The following  naming  convention is  used  in the  queries  of  domain relational  calculus:

• A relation is denoted by first letter of his name Relation Opening is denoted by O, Withdrawal is denoted by  W.
• Each attribute of relation is denoted by first letter of its relation, “.”, and then first letter of its name Attribute “AccountNumber” in relation “Opening” is denoted by O.AN.

Problem 2. Consider the  following relations:

Compute the result of the following relational algebra expression:

Problem 3. Consider the relation schemas R = (A, B, C) and S = (D, E, F). Let relations r(R) and s(S) be given. Give an expression in the tuple relational calculus that is equivalent to each of the following:

Problem 4. Let R = (A, B, C), and let r₁ and r₂ both be relations on schema R. Give an expression  in domain  relational calculus  that is  equivalent to  each of  the  following:

Problem 5. Consider the relation schemas R = (A, B) and S = (A, C), and let r(R) and s(S) be relations. Write relational algebra expressions equivalent to the following domain- relational calculus  expressions:

Problem 6. Consider the relation schemas R =(A, B, C) and S =(D, E, F). Let relations r(R)and s(S) be given. Give an expression in the tuple relational calculus that is equivalent to each of the following:

Problem 7.  Consider the  following collection  of relation schemas:

professor(profname, deptname

department(deptname, building)

committee(commname, profname)

1. Find all the professors who are in any one of the committees that  Professor David is in.
2. Find all the professors who are in at least all those committees that Professor David is in.
3. Find all the professors who are in exactly (e., no more and no less) all those committees that Professor  David  is  in.
4. Find all the professors who have offices in at least all those buildings that Professor David has offices in.

Solution.

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)