Download VTU BE 2020 Jan CSE Question Paper 17 Scheme 5th Sem 17CS53 Database Management System

Fifth Semester B.E. Degree Examination, Dec.2019/Jan.2020
Database Management System
Time: 3 hrs.
Max. Marks: 100
Note: Answer any FIVE full questions, choosing ONE full question from each module.

--- Content provided by FirstRanker.com ---

th
? , b. Draw an ER diagram to represent the Election Information System based on the following
. .
ri
,

--- Content provided by FirstRanker.com ---

r
description :
8 -
In the Indian national election, a state is divided into a number of constituencies depending
0

--- Content provided by FirstRanker.com ---

... L2
upon the population of the state. Several candidates contest elections in each constituency.
O. , ...
O Candidates may be from some party or independent. The election information system must
? -

--- Content provided by FirstRanker.com ---

? =
record the number of votes obtained by each candidate. The system also maintains the voter
?A
.
FTS

--- Content provided by FirstRanker.com ---

'
4
= list and a voter normally belongs to a particular constituency.
E. 0
-

--- Content provided by FirstRanker.com ---

(..) 1-0
Note that the party details must also be taken care in the design.
7 8
(12 Marks)

--- Content provided by FirstRanker.com ---

----
O -0
co 0
CCI Ct: Module-2
3?

--- Content provided by FirstRanker.com ---

.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,
-'
,

--- Content provided by FirstRanker.com ---

5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)
?, ;- _, -
., b. Enumerate the steps involved in converting the ER constructs to corresponding relational

--- Content provided by FirstRanker.com ---

G
L

tables. (07 Marks)
z ?

--- Content provided by FirstRanker.com ---

E. '
-
',2
0 P .
c. Considering the schema

--- Content provided by FirstRanker.com ---

...... 0 -
t., d'
Sailors (sid , sname , rating , age)
.9
Boats (bid , bname , color)

--- Content provided by FirstRanker.com ---

0
cn
?
:4=
Reserves (sid , bid , day)

--- Content provided by FirstRanker.com ---

m =
,...
V
0 Write relational algebraic queries for the following :
8 -

--- Content provided by FirstRanker.com ---

>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1
C

--- Content provided by FirstRanker.com ---

ii) Find names of sailors who have reserved a red boat.
t -
iii) Find names of sailors who have reserved a red or green boat.
P
?

--- Content provided by FirstRanker.com ---

>
E O . )
u
iv) Find names of sailors who have reserved all boats. (08 Marks)
8

--- Content provided by FirstRanker.com ---

rsi OR
4 a. Explain with examples , the basic constraints that can be specified when a database table is
0
created in SQL. (12 Marks)

--- Content provided by FirstRanker.com ---

b. Write SQL queries for the following relational schema :
O CUSTOMER (CID , CNAME , EMAIL , ADDR , PHONE)
ITEM (ITEM_NO , ITEM_NAME , PRICE , BRAND)
SALES (CID , ITEM_NO , # ITEMS , AMOUNT , SALE_DATE)
SUPPLIER (SID , SNAME , SPHONE , SADDR)

--- Content provided by FirstRanker.com ---

SUPPLY (SID , ITEM_NO , SUPPLY_DATE , QTY)
1 of 3
Module-1
1 a. Compare DBMS and early file systems , bringing out the major advantages of the database
approach. (06 Marks)

--- Content provided by FirstRanker.com ---

b. With a neat block diagram, explain the architecture of a typical DBMS. (10 Marks)
c. What are the responsibilities of the DBA and the database designers? (04 Marks)
t A
? =
OR

--- Content provided by FirstRanker.com ---

2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)
USN
17CS53

--- Content provided by FirstRanker.com ---

FirstRanker.com - FirstRanker's Choice
Fifth Semester B.E. Degree Examination, Dec.2019/Jan.2020
Database Management System
Time: 3 hrs.
Max. Marks: 100

--- Content provided by FirstRanker.com ---

Note: Answer any FIVE full questions, choosing ONE full question from each module.
th
? , b. Draw an ER diagram to represent the Election Information System based on the following
. .
ri

--- Content provided by FirstRanker.com ---

,
r
description :
8 -
In the Indian national election, a state is divided into a number of constituencies depending

--- Content provided by FirstRanker.com ---

0
... L2
upon the population of the state. Several candidates contest elections in each constituency.
O. , ...
O Candidates may be from some party or independent. The election information system must

--- Content provided by FirstRanker.com ---

? -
? =
record the number of votes obtained by each candidate. The system also maintains the voter
?A
.

--- Content provided by FirstRanker.com ---

FTS
'
4
= list and a voter normally belongs to a particular constituency.
E. 0

--- Content provided by FirstRanker.com ---

-

(..) 1-0
Note that the party details must also be taken care in the design.
7 8

--- Content provided by FirstRanker.com ---

(12 Marks)
----
O -0
co 0
CCI Ct: Module-2

--- Content provided by FirstRanker.com ---

3?
.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,
-'

--- Content provided by FirstRanker.com ---

,
5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)
?, ;- _, -

--- Content provided by FirstRanker.com ---

., b. Enumerate the steps involved in converting the ER constructs to corresponding relational
G
L

tables. (07 Marks)

--- Content provided by FirstRanker.com ---

z ?
E. '
-
',2
0 P .

--- Content provided by FirstRanker.com ---

c. Considering the schema
...... 0 -
t., d'
Sailors (sid , sname , rating , age)
.9

--- Content provided by FirstRanker.com ---

Boats (bid , bname , color)
0
cn
?
:4=

--- Content provided by FirstRanker.com ---

Reserves (sid , bid , day)
m =
,...
V
0 Write relational algebraic queries for the following :

--- Content provided by FirstRanker.com ---

8 -
>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1
C

--- Content provided by FirstRanker.com ---

ii) Find names of sailors who have reserved a red boat.
t -
iii) Find names of sailors who have reserved a red or green boat.
P

--- Content provided by FirstRanker.com ---

?
>
E O . )
u
iv) Find names of sailors who have reserved all boats. (08 Marks)

--- Content provided by FirstRanker.com ---

8

rsi OR
4 a. Explain with examples , the basic constraints that can be specified when a database table is
0

--- Content provided by FirstRanker.com ---

created in SQL. (12 Marks)
b. Write SQL queries for the following relational schema :
O CUSTOMER (CID , CNAME , EMAIL , ADDR , PHONE)
ITEM (ITEM_NO , ITEM_NAME , PRICE , BRAND)
SALES (CID , ITEM_NO , # ITEMS , AMOUNT , SALE_DATE)

--- Content provided by FirstRanker.com ---

SUPPLIER (SID , SNAME , SPHONE , SADDR)
SUPPLY (SID , ITEM_NO , SUPPLY_DATE , QTY)
1 of 3
Module-1
1 a. Compare DBMS and early file systems , bringing out the major advantages of the database

--- Content provided by FirstRanker.com ---

approach. (06 Marks)
b. With a neat block diagram, explain the architecture of a typical DBMS. (10 Marks)
c. What are the responsibilities of the DBA and the database designers? (04 Marks)
t A
? =

--- Content provided by FirstRanker.com ---

OR
2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)
USN

--- Content provided by FirstRanker.com ---

17CS53
(10 Marks)
i) List the items purchased by customer `Prasanth'.
ii) Retrieve items supplied by all suppliers starting from 1s
t

--- Content provided by FirstRanker.com ---

Jan 2019 to 30
th
Jan 2019.
iii) Get the details of customers whose total purchase of items worth more than 5000 rupees.
iv) List total sales amount, total items , average sale amount of all items.

--- Content provided by FirstRanker.com ---

v) Display customers who have not purchased any items. (08 Marks)
Module-3
5 a. What are assertions and triggers in SQL? Write a SQL program to create an assertion to
specify the constraint that the salary of an employee must not be greater than the salary of
the department. The employee works for in the COMPANY database. (07 Marks)

--- Content provided by FirstRanker.com ---

b. Write a trigger in SQL to call a stored procedure INFORM_SUPERVISOR( ) whenever a
new record is inserted or updated, check whether an employee's salary is greater than the
salary of his or her direct supervisor in the COMPANY database. (07 Marks)
c. How do you create a view in SQL? Give examples. Can you update a view table? If yes,
how? If not, why not? Discuss. (06 Marks)

--- Content provided by FirstRanker.com ---

OR
6 a. With real world examples, explain the following : i) JDBC ii) Correlated queries
iii) Stored Procedure iv) Schema change statements in SQL. (12 Marks)
b. Write a complete high level language program (in Java or C) to display the rows of a
customer table created in oracle having < custid , custname , balance > columns with

--- Content provided by FirstRanker.com ---

embedded SQL. (08 Marks)
Module-4
7 a. What are the problems caused by insertion , updation and deletion anomalies? Discuss with
an example. (06 Marks)
b. For the below given relation R (A, B, C, D, E) and its instance , check whether the FDs

--- Content provided by FirstRanker.com ---

given hold or not. Give reasons.
i) A B B --) C iii) D ?+ E iv) CD E. (04 Marks)
A B O D E
a
l

--- Content provided by FirstRanker.com ---

bi ci di ei
b2 ci d
i
e
i

--- Content provided by FirstRanker.com ---

az b, c
i
d, e3
a

--- Content provided by FirstRanker.com ---

,
b3 C3 dz ez
c. Using the minimal cover algorithm , find the minimal cover for the following FDs :
F= IAB-4C,A--+ ID,BD?C,D?BG,AE?>F I. (10 Marks)
OR

--- Content provided by FirstRanker.com ---

8 a. Normalize the below relation upto 3NF :
Module Dept Lecturer Text
MI Dl LI TI
MI DI LI T2
M2 Dl LI Ti

--- Content provided by FirstRanker.com ---

M2 DI LI T3
M3 DI L2 T4
M4 D2 L3 T I
M4 D2 L3 T5
M5 D2 L4 T6

--- Content provided by FirstRanker.com ---

2 of 3
FirstRanker.com - FirstRanker's Choice
Fifth Semester B.E. Degree Examination, Dec.2019/Jan.2020
Database Management System
Time: 3 hrs.

--- Content provided by FirstRanker.com ---

Max. Marks: 100
Note: Answer any FIVE full questions, choosing ONE full question from each module.
th
? , b. Draw an ER diagram to represent the Election Information System based on the following
. .

--- Content provided by FirstRanker.com ---

ri
,
r
description :
8 -

--- Content provided by FirstRanker.com ---

In the Indian national election, a state is divided into a number of constituencies depending
0
... L2
upon the population of the state. Several candidates contest elections in each constituency.
O. , ...

--- Content provided by FirstRanker.com ---

O Candidates may be from some party or independent. The election information system must
? -
? =
record the number of votes obtained by each candidate. The system also maintains the voter
?A

--- Content provided by FirstRanker.com ---

.
FTS
'
4
= list and a voter normally belongs to a particular constituency.

--- Content provided by FirstRanker.com ---

E. 0
-

(..) 1-0
Note that the party details must also be taken care in the design.

--- Content provided by FirstRanker.com ---

7 8
(12 Marks)
----
O -0
co 0

--- Content provided by FirstRanker.com ---

CCI Ct: Module-2
3?
.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,

--- Content provided by FirstRanker.com ---

-'
,
5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)

--- Content provided by FirstRanker.com ---

?, ;- _, -
., b. Enumerate the steps involved in converting the ER constructs to corresponding relational
G
L

--- Content provided by FirstRanker.com ---

tables. (07 Marks)
z ?
E. '
-
',2

--- Content provided by FirstRanker.com ---

0 P .
c. Considering the schema
...... 0 -
t., d'
Sailors (sid , sname , rating , age)

--- Content provided by FirstRanker.com ---

.9
Boats (bid , bname , color)
0
cn
?

--- Content provided by FirstRanker.com ---

:4=
Reserves (sid , bid , day)
m =
,...
V

--- Content provided by FirstRanker.com ---

0 Write relational algebraic queries for the following :
8 -
>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1

--- Content provided by FirstRanker.com ---

C

ii) Find names of sailors who have reserved a red boat.
t -
iii) Find names of sailors who have reserved a red or green boat.

--- Content provided by FirstRanker.com ---

P
?
>
E O . )
u

--- Content provided by FirstRanker.com ---

iv) Find names of sailors who have reserved all boats. (08 Marks)
8

rsi OR
4 a. Explain with examples , the basic constraints that can be specified when a database table is

--- Content provided by FirstRanker.com ---

0
created in SQL. (12 Marks)
b. Write SQL queries for the following relational schema :
O CUSTOMER (CID , CNAME , EMAIL , ADDR , PHONE)
ITEM (ITEM_NO , ITEM_NAME , PRICE , BRAND)

--- Content provided by FirstRanker.com ---

SALES (CID , ITEM_NO , # ITEMS , AMOUNT , SALE_DATE)
SUPPLIER (SID , SNAME , SPHONE , SADDR)
SUPPLY (SID , ITEM_NO , SUPPLY_DATE , QTY)
1 of 3
Module-1

--- Content provided by FirstRanker.com ---

1 a. Compare DBMS and early file systems , bringing out the major advantages of the database
approach. (06 Marks)
b. With a neat block diagram, explain the architecture of a typical DBMS. (10 Marks)
c. What are the responsibilities of the DBA and the database designers? (04 Marks)
t A

--- Content provided by FirstRanker.com ---

? =
OR
2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)

--- Content provided by FirstRanker.com ---

USN
17CS53
(10 Marks)
i) List the items purchased by customer `Prasanth'.
ii) Retrieve items supplied by all suppliers starting from 1s

--- Content provided by FirstRanker.com ---

t
Jan 2019 to 30
th
Jan 2019.
iii) Get the details of customers whose total purchase of items worth more than 5000 rupees.

--- Content provided by FirstRanker.com ---

iv) List total sales amount, total items , average sale amount of all items.
v) Display customers who have not purchased any items. (08 Marks)
Module-3
5 a. What are assertions and triggers in SQL? Write a SQL program to create an assertion to
specify the constraint that the salary of an employee must not be greater than the salary of

--- Content provided by FirstRanker.com ---

the department. The employee works for in the COMPANY database. (07 Marks)
b. Write a trigger in SQL to call a stored procedure INFORM_SUPERVISOR( ) whenever a
new record is inserted or updated, check whether an employee's salary is greater than the
salary of his or her direct supervisor in the COMPANY database. (07 Marks)
c. How do you create a view in SQL? Give examples. Can you update a view table? If yes,

--- Content provided by FirstRanker.com ---

how? If not, why not? Discuss. (06 Marks)
OR
6 a. With real world examples, explain the following : i) JDBC ii) Correlated queries
iii) Stored Procedure iv) Schema change statements in SQL. (12 Marks)
b. Write a complete high level language program (in Java or C) to display the rows of a

--- Content provided by FirstRanker.com ---

customer table created in oracle having < custid , custname , balance > columns with
embedded SQL. (08 Marks)
Module-4
7 a. What are the problems caused by insertion , updation and deletion anomalies? Discuss with
an example. (06 Marks)

--- Content provided by FirstRanker.com ---

b. For the below given relation R (A, B, C, D, E) and its instance , check whether the FDs
given hold or not. Give reasons.
i) A B B --) C iii) D ?+ E iv) CD E. (04 Marks)
A B O D E
a

--- Content provided by FirstRanker.com ---

l
bi ci di ei
b2 ci d
i
e

--- Content provided by FirstRanker.com ---

i

az b, c
i
d, e3

--- Content provided by FirstRanker.com ---

a
,
b3 C3 dz ez
c. Using the minimal cover algorithm , find the minimal cover for the following FDs :
F= IAB-4C,A--+ ID,BD?C,D?BG,AE?>F I. (10 Marks)

--- Content provided by FirstRanker.com ---

OR
8 a. Normalize the below relation upto 3NF :
Module Dept Lecturer Text
MI Dl LI TI
MI DI LI T2

--- Content provided by FirstRanker.com ---

M2 Dl LI Ti
M2 DI LI T3
M3 DI L2 T4
M4 D2 L3 T I
M4 D2 L3 T5

--- Content provided by FirstRanker.com ---

M5 D2 L4 T6
2 of 3
17CS53
b. Define Multi valued Dependency and Join Dependency. Explain 4NF and 5NF with
examples. (10 Marks)

--- Content provided by FirstRanker.com ---

Module-5
9 a. Describe the database inconsistency problems : Lost update , dirty read and blind write.
(06 Marks)
b. With a neat diagram, explain the various states of a transaction execution. (07 Marks)
c. Check whether the below schedule is conflict serializable or not.

--- Content provided by FirstRanker.com ---

{b2 , r2(X) , bl , rl(X) , wl(X) , rl(Y), wl(Y), w2(X) , el, cl, e2, c21. (07 Marks)
OR
10 a. What is 2PL? Explain with an example.
b. How do you detect a deadlock during concurrent transaction execution?
c. Explain the various database recovery techniques, with examples.

--- Content provided by FirstRanker.com ---

(06 Marks)
(06 Marks)
(08 Marks)

3 of 3

--- Content provided by FirstRanker.com ---

FirstRanker.com - FirstRanker's Choice