1.a. Consider a database used to record the marks that students get in different exams of different course offerings.

Construct an E-R diagram that models exams as entities, and uses a ternary relationship, for the above database.

1.b. For the database system to be usable, it must retrieve data efficiently. The need of efficiency has led designers to use complex data structures to represent data in the database. Developers hides this complexity from the database system users through several levels of abstraction. Explain those levels of abstraction in detail.

2.a. Construct an alternative E-R diagram for above requirements given in Q.1(a) that uses only a binary relationship between students and courseofferings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering.

2.b. Write PL/SQL trigger for following requirement :

Event: Deletion of row from stud(roll_no, name, class) table.

Action: after deletion of values from stud table, values should be inserted into cancel_admission( roll_no, name) table.

Note: for every row to be deleted, action should be performed

3.a. Consider insurance database with following schema :

person(driver-id, name, address)

car(license, model, year)

accident (report - no, date, location)

owns(driver-id,license)

participated(driver-id,car,report-no,damage-amount)

Write a query in SQL for following requirements (any 2) :

i) Find the total no. of people who owned cars that were involved in accidents in 2016.

ii) Retrieve the name of person whose address contains Pune.

iii) Find the name of persons having more than two cars.

3.b. Any database system to be good relational database system, codds have proposed 12 rules, explain any 2 rules proposed by codd with example.[

4.a. What is normalization? What s the need of normalized database?

4.b. The organization has decided to increase the salary of employees by 10% of existing salary, whose existing salary is less than Rs. 10000/- Write a PL/SQ block to update the salary as per above requirement, display an appropriate message based on the no. of rows affected by this update (using implicit cursor status variables).

5.a. Consider the Transaction (T3), Transaction (T4) and Transaction (T6) are any hypothetical transactions working on data item Q. Schedule explaining the execution of T3, T4 and T6 are given below. Decide whether following schedule is conflict serializable or not? Justify your answer.

5.b. Transaction during its execution should be in one of the different states at any point of time, explain the different states of transactions during its execution.

6.a. Suppose a transaction Ti issues a read command on data item Q. How time-stamp based protocol decides whether to allow the operation to be executed or not using time-stamp based protocol of concurrency control.

6.b. A transaction may be waiting for more time for an Exclusive (X)lock on an item, while a sequence of other transactions request and are granted as Shared (S) lock on the same item. What is this problem? How it is solved by two phase lock protocol?

7.a. Explain speed-up and scale-up parameters of parallel systems. What are the different factors limiting the speed-up and scae-up parameters.

7.b. In both, Shared nothing parallel architecture and distributed system architecture resources are not shared, then how shared nothing parallel systems are different than distributed systems? Also explain in brief other parallel system architecture

8.a. How two phase commit protocol to ensure the atomicity in distributed transaction, handles the following failures:

i) Failure of participating site

ii) Failure of coordinator

iii) Failure due to network partition

8.b. For concurrency control in distributed transaction distributed lock manager approach is used, explain in detail different approaches for dealing with replication of data items in distributed lock manager approach.

9.a. BASE Transactions ensures the properties like Basically Available, Soft State, Eventual Consistency. What is soft state of any system, how it is depend on Eventual consistency property?

9.b. List the different NOSQL data Models. Explain document store NOSQL data model with example

10.a. Explain how NOSQL databases are different than relational databases?

10.b. Write short note on Hadoop: HDFS, MapReduce.