Total No. of Questions : 10] SEAT No.

8
23
P1752 [Total No. of Pages : 4

ic-
tat
[5460] - 582

2s
T.E. (Computer Engg.)

3:1
DATABASE MANAGEMENT SYSTEM

01 91
9:1
(2015 Pattern)

0
80
4/1 13
Time : 2½ Hours] [Max. Marks :70
0
2/2
.23 GP

Instructions to the candidates:

1) Answer Q.1 or Q.2, Q.3 or Q.4, Q.5 or Q.6, Q.7 or Q.8, Q.9 or Q.10.
E
80

8
2) Neat diagrams must be drawn wherever necessary.
C

23
ic-
3) Figures to the right indicate full marks.
16

tat
4) Assume suitable data, if necessary
8.2

2s
.24

3:1
91
49

9:1
Q1) a) Write a PL/SQL block using user defined exception for following
30

requirements: [5]
80

The Bank_Account table records the current balance for an account,

01
01

which is updated whenever, any deposits or withdrawals takes place. If

2/2
GP

the withdrawal attempted is more than the current balance held in the
4/1

account. The user defined exception is raised, displaying an appropriate

CE
80

message.

8
23
Note : Assume table :- Bank_Account (Account_No, Balance)
.23

ic-
16

b) Consider Employee database with following schema : [5]

tat
8.2

Employee(Emp_Id, First_Name, Last_Name, Salary, Joining_Date,

2s

Department)
.24

3:1

Bonus(Emp_Ref_Id, Bonus_Amount, Bonus_Date)

91
49

9:1

Designation(Emp_Ref_Id, Emp_Designation, Affected_From)

30

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

80
01
01

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

ii) To print the name of employees having the highest salary in each
4/1

department.
CE
80

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

Managers.
16
8.2
.24

P.T.O.
49
 OR

8
23
Q2) a) Ramesh’s family owns and operates a 100-acre farm for several generations.

ic-
Since the farm business is growing, Ramesh is thinking to build a database

tat
that would make easier the management of the activities in the farm. He is

2s
considering the following requirements for the database: [5]

3:1
i) For each livestock classification group (for example, cow, horse

01 91
9:1
etc.), Ramesh keeps track of the following: identification number,

0
classification, total number of livestock per classification group (for

80
4/1 13
example, number of cows, number of horses etc.)
0
2/2
ii) For each crop the following information is recorded Crop
.23 GP

identification number and classification.

E
80

iii) Ramesh has recorded the yield of each crop classification group

8
C

23
during the last ten years. The records consist of the year, yield,

ic-
sales, price of the crop and the amount of money earned.
16

tat
8.2

iv) Ramesh has recorded the yield of each livestock classification group

2s
during the last ten years. The records consist of the following
.24

3:1
historical data: the year, (historical) selling price per head, number
91
49

9:1
of livestock in the end of the year, number of livestock sold during
30
80

one-year period, and the total amount of money earned.

01
01

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

of each entity type.

GP
4/1

b) Explain 3NF and BCNF. Also enlist their differences. [5]

CE
80

8
23
.23

ic-
Q3) a) Consider the schema: [5]
16

tat
student_fees_detail (name, total_fees_deposited, till_date)
8.2

2s

Answer the following :

.24

3:1

i) Write a SQL query to display the total fees deposited by students

91

whose minimum 3 character name starts with aj.

49

9:1

ii) Write Database Trigger to preserve the old values of student fees
30
80

details before updating in table. It is easy to create index on all

01
01

attributes of any relation, why index is not created on all attributes?

2/2
GP

b) It is easy to create index on all attributes of any relation, why it is not

4/1

recommended to create index on all attributes? [5]

CE
80
.23

OR
16
8.2

[5460] - 582 2
.24
49
Q4) a) Draw the overall Database System Structure. Explain its structure

8
components. [5]

23
ic-
b) Explain what is meant by repetition of information and inability to

tat
represent information. Explain why each of these properties may indicate

2s
a bad relational database design. [5]

3:1
01 91
9:1
Q5) a) Explain the Concept of Conflict Serializability with example. Since every

0
80
conflict-serializable schedule is view serializable, why do we emphasize
4/1 13
conflict serializability rather than view serializability? [8]
0
2/2
.23 GP

b) Explain the Two Phase lock Protocol and show how it ensures conflict
serializability. Two Phase lock protocol does not ensure freedom from
E
80

deadlock explain with necessary example. Also explain its two versions:

8
C

23
strict two phase lock protocol and rigorous two phase lock protocol.[9]

ic-
16

tat
OR
8.2

2s
Q6) a) State and explain the ACID Properties. During its execution, a transaction
.24

3:1
passes through several states, until it finally commits or aborts. List all
91
49

9:1
possible sequences of states through which a transaction may pass.
30

Explain the situations when each state transition occurs. [8]

80
01
01

b) Check whether following schedule is view serializable or not. Justify

2/2

your answer. (Note:T1 & T2 are transactions). Also explain the concept
GP

of view equivalent schedules and conflict equivalent schedule considering

4/1

the example schedule given below: [9]

CE
80

8
23
.23

ic-
16

tat
8.2

2s
.24

3:1
91
49

9:1
30
80
01
01
2/2
GP
4/1
CE
80
.23
16
8.2

[5460] - 582 3
.24
49
Q7) a) Explain in details two important issues Speedup and Scaleup in Parallel

8
Databases. Also explain which factors work against efficient parallel

23
operation and can diminish both speedup and scaleup. [9]

ic-
tat
b) Explain Data Replication and Data Fragmentation in Distributed Data

2s
Storage. Also explain the advantages of each type of distributed data

3:1
storage. [8]

01 91
9:1
0
OR

80
4/1 13
Q8) a) What are different Parallel Database Architectures? Explain with their
0
advantageous and disadvantageous. [8]
2/2
.23 GP

b) Describe Two Phase Commit(2PC) Protocol. Explain how 2PC protocol

E

responds in different ways to various types of failures like site failure,

80

8
C

23
coordinator failure and network partition. [9]

ic-
16

tat
8.2

2s
Q9) a) Explain the difference SQL Vs NoSQL. [4]
.24

3:1
91
b) Enlist and explain any three NoSQL Database Models. [6]
49

9:1
30
80

c) Explain the HDFS and MapReduce in HADOOP. [6]

01
01

OR
2/2
GP
4/1

Q10) a) Explain the CAP theorem referred during the development of any
CE

distributed application. [7]

80

8
23
.23

b) BASE Transactions ensures the properties like Basically Available, Soft

ic-
State, Eventual Consistency explain each property with its significance.
16

tat
How soft state of system is depend on Eventual consistency property?[9]
8.2

2s
.24

3:1
91
49

9:1
30
80

tttt
01
01
2/2
GP
4/1
CE
80
.23
16
8.2

[5460] - 582 4
.24
49