Consider a banking database with three labels and primary key underlined as given below:

Customer (CustomerID , CustomerName, Address, Phone, Email)

Borrows (CustomerID, LoanNumber )

Loan ( LoanNumber , LoanType, Amount )

Write both relational algebra and SQL queries:

1. To display name of all customers who live in “Lalitpur” in ascending order of name.
2. To count total number of customers having loan at the bank.
3. To find name of those customers who have loan amount greater than or equal to 500000.
4. To find average loan amount of each accoun’t type.

What are informal design guidelines for relational schemas? why do we need functional dependencies? Explain 2NF, 3NF with suitable example.

Explain deadlock with example. What are different deadlock prevention protocols? Explain deadlock detection and starvation.

What are the characteristics of database approach? Explain.

Define data independence. Explain three-schema architecture.

What is specialization? What are different constraints on specialization?

What is relational database? Explain different characteristic of a relation. Defain domain constraint.

What is tuple realtion calculus ?  Given the following schema, write tuple relational calculus for selecting name and address of employee who are working in a company having Cid=E01

Employee(Eid, Ename, Address, Cid)

Company(Cid, CName)

Explain schedule based on recoverability and serializability.

How timestamp ordering protocol is used for ensuring concurrency control?

Why database recovery is essential? Explain recovery technique based on immediate update.

Write short notes on:

1. Transaction processing
2. Weak entity