1.a. Explain the concepts of Referential Constraints and Entity Integrity Constraint with example.

1.b. Write PL\SQL block of code to calculate the factorial value of a number.

2.a. Draw the ER diagram for the college ERP system.

2.b. Write a trigger for overdraft withdrawal from account:

Instead of allowing negative account balances, the bank deals with overdrafts by creating a loan in the amount of the overdraft giving this loan a loan number identical to the account number of the overdraw account setting the account balance to zero. The condition for executing the trigger is an update to the account relation that results in a negative balance value.

3.a. Schema definition for supplier-and-parts database Keys are underlined.

Supplier-(supplier_number, supplier name, status, city)

Parts s-(part_number, part_name, color, weight, city)

Shipments-(supplier_number, part_number, quantity)

write SQL query for following requirements (any 2):

(i) Find shipment information (supplier_number, supplier_name,part_number, part_name, quantity) for those having quantity less than 150.

(ii) List supplier_number, supplier_name, part_number, part_name for those suppliers who made shipment of parts whose quantity is larger than the average quantity.

(iii) Find aggregate quantity of part_number 'A692' of color 'GREEN'for which shipment made by supplier_number who reside in 'MUMBAI'

3.b. Explain 3NF and BCNF. Also enlist their differences.

4.a. Draw and explain overall structure of Database System.

4.b. What is the impact of insert,update and delete anomaly on overall design of database?How normalization is used to remove these anomalies?

5.a. Explain the concept of conflict Serializability. Decide whether following schedule is conflict serializability or not. Justify your answer.

5.b. Explain the two Phase lock Protocol for concurrency control. Also explain its two versions : strict two phases lock protocol and rigorous two phase lock protocol.

6.a. State and explain in brief the ACID Properties. During execution of transaction, a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain why each state transition occurs.

6.b. To ensure atomicity despite failures we use Recovery methods. Explain in detail Log-Based Recovery method.

7.a. Explain in details two important issues Speedup and Scaleup in Parallel Databaseş. Also explain which factors work against efficient parallel operation and can diminish both speedup and scaleup.

7.b. Explain Data Replication and Data Fragmentation in Distributed Data Storage

8.a. Describe Two phases commit (2PC) Protocol. Explain how 2PC protocol responds in different ways to various types of failures.

8.b. What are the different Parallel Database Architectures?Explain with their advantages and disadvantages.

9.a. Explain the difference between SQL and NoSQL.

9.b. Enlist and explain any three NoSQL Database types.

9.c. Explain the HDFS and MapReduce in HADOOP.

10.a. Explain the concepts of NoSQL Database and state its advantages over RDBMS.

10.b. State and explain:

i) CAP Theorem.

ii) BASE properties.

10.c. Analyze the use of NoSQL databases in current social networking environment also explain need of NoSQL databases in social networking environment over RDBMS.