QUESTIONS
1. Consider the following two transactions :-
T1: read (A)
read (B)
A = 0 then B= B + 1
Write (B)
T2: read (B)
read (A)
B = 0 then A= A + 1
Write (A)
Let the consistency requirement be
A=0VB=0
with A = B = 0 the initial values.
(i) Show that every serial execution involving these two transactions preserves the
consistency of the database.
(ii) Show a concurrent execution of T1 and T2 that produces a non-serializable schedule.
(iii) Is there a concurrent execution of T1 and T2 that produces a serializable schedule?
2. Consider the following set of requirements for a Bank Database :-
"A large bank has several branches at different places. Each branch maintains the
account details of the customers. The customers may open joint as well as single
accounts. The bank also provides the loan to the customer for different purposes.
Bank keeps record of each transaction by the customer to his account. All of the
branches have employees and some employees are managers."
(a) Draw an E-R Diagram that captures this Information.
(b) Transform this E-R Diagram to Relational Database Schemas.
(c) Write SQL-DDL statement to implement Bank Database Schema.
3. Consider the following relational DATABASE. Give an expression in SQL for each
following queries Underline records are Primary Key
Employee( person_name , street , city) Works(person_name, Company_name ,salary)
Company(Company_name , city) Manages( person_name, manager_name)
i). Finds the names of all employees who works for the ABC bank
ii). Finds the name of all employees who live in the same city and on the same street as do
their managers
iii). Find the name street address and cities of residence of all employees who work for ABC
bank and earn more than 10,000 per annum
iv). Find the name of all employee who earn more than every employee of XYZ v). Give all
Employees of corporation ABC a 10% salary raise .
vi). Delete all tuples in the works relation for employees of ABC
vii). Find the name of all employees in this DATABASE who live in the same city as the
company for which they work.
4. What is Conflict Serializable Schedule? Check the given Schedule S1 is Conflict
Serializable or not? S1: R1(X), R2(X),R2(Y),W2(Y),R1(Y),W1(X)
5. Let R = {A, B, C, D, E,} be a A schema and let M be a set of following multivalued
dependencies A→→BC, B→→CD, E→→AD. List the nontrivial dependencies in M+.
6. Consider the following relational database employee (employee-name, street, city
works (employee-name, company-name, salary) company (company-name, city)
manages (employee-name, manager-name).
Give an expression in SQL to express each of the following queries:
i) Find the names and cities of residence of all employees who work for XYZ
bank.
ii) Find the names, street address, and cities of residence of all employees who
work for XYZ Bank and earn more than Rs. 10,000 per annum.
iii) Find the names of all employees in this database who live in the same city as
the company for which they work.
7. Consider the following relation. The Primary key is Rollno, Isbn, Student(RollNo,
Name, Branch), Book(Isbn, Title, Author, Publisher) Issue(Rollno, Isbn, te_of_issue).
Write the query in Relational algebra and SQL of the following-
i) List the Roll Number and Name of All CSE Branch Student.
ii) Find the name of students who have issued a book of publication ‘BPB’.
iii) List the title and author of all books which are issued by a student name
started with a.
iv) List the title of all books issued on or before 20/09/2012.
v) List the name of student who will read the book of author named ‘Sanjeev’.
8. Suppose there are two relations R(A, B, C), S( D, E, F). Write TRC and SQL for the
following Ras.
i) ΠA , B ( r )
ii) σB=45 ( r )
iii) ΠA , F (σC=D( r x s ))
9. What do you mean by multi granularity? How the concurrency is maintained in this
case. Write the concurrent transactions for the following graph.
T1 wants to access Item C in read mode
T2 wants to access item D in Exclusive mode
T3 wants to read all the children of item B
T4 wants to access all items in read mode
10. Give the following queries in the relational algebra using the relational schema:
student(id, name)
enrolled(id, code)
subject(code, lecturer)
i). What are the names of students enrolled in cs3020?
ii). Which subjects is Hector taking?
iii). Who teaches cs1500?
iv). Who teaches cs1500 or cs3020?
v). Who teaches at least two different subjects?
vi). What are the names of students in cs1500 or cs3010? vii). What are the names of
students in both cs1500 and cs1200?
