1.a. Write a PL\SQL block using user defined exceptions for following requirements :

The Bank_Account table records the current balance for an account, which is updated whenever, any deposits or withdrawal takes places. if the withdrawal attempted is more than the current balance held in the account. The user defined exceptions is raised , displaying an appropriate message.

Note : Assume table :- Bank_account ( Account_NO,Balance)

1.b. Consider Employee database with following schema:

Employee(Emp_Id,First_Name,Last_Name,Salary,Joining_date,Department)

Bonus(Emp_Ref,Bonus_Amount,Bonus_date)

Designation(Emp_Ref_Id,Emo_Designation,Affected_From)

Write queries in SQL for the following requirements (any 2)

i) To fetch the departments that have less than five people in it.

ii) To print the name of employees having the highest salary in each department.

iii) Write an SQL query to print details of the employee who are also Managers.

2.a. Ramesh's family owns and operates a 100-acre farm for several generations, Since the farm business is growing, Ramesh is thinking to build a database that would make easier the management of the activities in the farm. He is considering the following requirements for the database:

(i) For each livestock classification group (for example, cow, horse etc.), Ramesh keeps track of the following: identification number, classification, total number of livestock per classification group (for example, number of cows, number of horses etc.)

(ii)For each crop the following information is recorded Crop identification number and classification.

ii) Ramesh has recorded the yield of each crop classification group during the last ten years. The records consist of the year, yield, sales, price of the crop and the amount of money earned.

iv) Ramesh has recorded the yield of each livestock classification group during the last ten years. The records consist of the following historical data: the year, (historical) selling price per head, number of livestock in the end of the year, number of livestock sold during one-year period, and the total amount of money earned.

Draw an E-R diagram for this application. Specify the key attribute

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

3.a. Consider the schema:

student_fees_detail(name,total_fees_deposited,till_date)

Answer the following :

i) Write SQL query to display the total fees deposited by students whose minimum 3 character name starts with aj.

ii) Write database trigger to preserve the old values of student fees details before updating in table. It is easy to create index on all attributes of any relation,why index is not created on all attributes?

3.b. It is easy to create index on all attributes of any relations , why it is not recommended to create index on all attributes?

4.a. Draw the overall Database System structure. Explain its structure components.

4.b. Explain whats is meant by repetition of information and inability to represent information.Explain why each of these properties may indicate a bad relational database design.

5.a. Explain the Concept of Conflict Serializability with example. Since every conflict-serializable schedule is view serializable, why do we emphasize conflict serializability rather than view serializability?

5.b. Explain the Two Phase lock Protocol and show how it ensures conflict serializability Two Phase lock protocol does not ensure freedom from deadlock explain with necessary example. Also explain its two versions: strict two phase lock protocol and rigorous two phase' lock protocol.

6.a. State and explain the ACID Properties. During its execution, a transaction _passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. |8]Explain the situations when each state transition occurs.

6.b. Check whether following schedule is view serializable or not. Justify your answer. (Note: T1 & T2 transactions). Also explain the concept of view equivalent schedules and conflict equivalent schedule considering 191.the example schedule given below:

7.a Explain in details two important issues Speedup and Speedup in Parallel Databases. 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. Also explain the advantages of each type of distributed data storage.

8.a. What are the different Parallel Database architecture ? Explain with their advantages and disadvantages.

8.b. Describe Two Phase Commit(2PC) Protocol. Explain how 2PC protocol responds in different ways to various types of failures like site failure, coordinator failure and network partition

9.a. Explain the different SQL and NoSQL.

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

9.c Explain the HDFS and MapReduce un HADOOP.

10.a. Explain the CAP theorem referred during the development of any distributed application.

10.b BASE Transaction ensures the properties like Basically Available,soft State,Eventual Consistency explain each property with its significance. How soft state of system is depend on Eventual consistency property?