Consider the following three transactions:

T1/Read T2/Read T3/Read X=X-1000 display (X)
Write display
Insert shared and exclusive locks in T1, T2 and T3 such that the transactions when executed concurrently, do not encounter any concurrency problem .

1.(b) List all the functional dependencies that hold true for the following relation

A B C
a1 b1 c1
a1 b1 c2
a2 b1 c1
a2 b1 c3

1.(c) Explain the working of GROUP BY clause. What is the difference between the WHERE and HAVING clause in SQL


1.(d) Differentiate between Backward recovery and Forward recovery

1.(d) Differentiate between Serial Schedule and Serializable Schedule

1.(e) Discuss the role of a Database Administrator.

2.(a) Construct an E-R diagram for the following problem definition:
Each company operates four departments, and each department belongs to one company. Each department employs one or more employees, and each employee works for one department. Each of the employees mayor may not have one or more dependants, and each dependant belongs to one employee.

2.(b) What are the different types of constraints which can be imposed on Generalization in an E-R diagram?

2.(c) How would you map the Aggregation E-R constructs into relations Give suitable examples.

2.(c) How would you map the Inheritance E-R constructs into relations Give suitable examples.

2.(c) How would you map the n-ary Relationship E-R constructs into relations Give suitable examples.

3.(a) With the help of an example, explain client server databases. How are they different from distributed databases?

3.(b) Consider the following relation for published books
Book (Book_title, Author_name, Book_type, Listprice, Author_affil, Publisher)

Author_affil refers to the affiliation of the author. Suppose the following dependencies exist:

Book_title Publisher, Book_type
Book_type Listprice
Author_name Author_affil
What Normal Form is the above relation in Justify.

(ii) Normalize the above relation ti1l3NF.

4.(a) Consider the following relations for a database that keeps a track of business trips of salespersons in a sales office:
SALEPERSON Name, Start_Year, Dept_No)
TRIP From_City, To_City, Departure_Date, Return_Date, Trip_ID)
EXPENSE (Trip_ID, Account#, Amount)
Specify the queries in SQL.
Find the details (all attributes of TRIP relation) for trips whose expenses exceeds 2000.
Find the SSN of salesmen who took trips to 'Honolulu'.

(iii) Find the total trip expenses incurred by the salesman with SSN

4.(b) Define a view. How is it different from a table? Write the SQL syntax for creating a view.

5.(a) Discuss the different possible states of a transaction with the help of a diagram.

5.(b) Compare the shadow-paging recovery scheme with the log-based recovery scheme in respect to ease of implementation and overhead cost.

5.(c) Discuss the key control measures that are used to provide security to data in databases.