####
**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**.

- List of all Account Number having no deposits in Jan, 2000.
- List of all Account Number having neither any deposit nor any withdrawal in Jan, 2000.
- List of Account Number and Name of Account holders from city ROHTAK whose opening balance is not less than 9999.
- List of all cities of the state HARYANA.
- List of all Account Number who belongs to state Haryana.
- List of all city Names from which there is no Account Holders.
- List of all Account Number, which do not have any transaction after opening.
- 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*_{1} and *r*_{2} 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**)

- Find all the professors who are in any one of the committees that Professor David is in.
- Find all the professors who are in at least all those committees that Professor David is in.
- Find all the professors who are in exactly (
*e.,*no more and no less) all those committees that Professor David is in. - 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)