Download Visvesvaraya Technological University (VTU) BE ( Bachelor of Engineering) CSE 2017 Scheme 2020 January Previous Question Paper 5th Sem 17CS53 Database Management System
Database Management System
Time: 3 hrs.
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
. .
ri
,
r
description :
8 -
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. , ...
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
.
FTS
'
4
= list and a voter normally belongs to a particular constituency.
E. 0
-
(..) 1-0
Note that the party details must also be taken care in the design.
7 8
(12 Marks)
----
O -0
co 0
CCI Ct: Module-2
3?
.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,
-'
,
5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)
?, ;- _, -
., b. Enumerate the steps involved in converting the ER constructs to corresponding relational
G
L
tables. (07 Marks)
z ?
E. '
-
',2
0 P .
c. Considering the schema
...... 0 -
t., d'
Sailors (sid , sname , rating , age)
.9
Boats (bid , bname , color)
0
cn
?
:4=
Reserves (sid , bid , day)
m =
,...
V
0 Write relational algebraic queries for the following :
8 -
>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1
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.
P
?
>
E O . )
u
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
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)
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
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
? =
OR
2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)
USN
17CS53
FirstRanker.com - FirstRanker's Choice
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.
th
? , b. Draw an ER diagram to represent the Election Information System based on the following
. .
ri
,
r
description :
8 -
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. , ...
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
.
FTS
'
4
= list and a voter normally belongs to a particular constituency.
E. 0
-
(..) 1-0
Note that the party details must also be taken care in the design.
7 8
(12 Marks)
----
O -0
co 0
CCI Ct: Module-2
3?
.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,
-'
,
5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)
?, ;- _, -
., b. Enumerate the steps involved in converting the ER constructs to corresponding relational
G
L
tables. (07 Marks)
z ?
E. '
-
',2
0 P .
c. Considering the schema
...... 0 -
t., d'
Sailors (sid , sname , rating , age)
.9
Boats (bid , bname , color)
0
cn
?
:4=
Reserves (sid , bid , day)
m =
,...
V
0 Write relational algebraic queries for the following :
8 -
>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1
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.
P
?
>
E O . )
u
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
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)
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
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
? =
OR
2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)
USN
17CS53
(10 Marks)
i) List the items purchased by customer `Prasanth'.
ii) Retrieve items supplied by all suppliers starting from 1s
t
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.
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)
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)
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
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
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
bi ci di ei
b2 ci d
i
e
i
az b, c
i
d, e3
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)
OR
8 a. Normalize the below relation upto 3NF :
Module Dept Lecturer Text
MI Dl LI TI
MI DI LI T2
M2 Dl LI Ti
M2 DI LI T3
M3 DI L2 T4
M4 D2 L3 T I
M4 D2 L3 T5
M5 D2 L4 T6
2 of 3
FirstRanker.com - FirstRanker's Choice
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.
th
? , b. Draw an ER diagram to represent the Election Information System based on the following
. .
ri
,
r
description :
8 -
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. , ...
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
.
FTS
'
4
= list and a voter normally belongs to a particular constituency.
E. 0
-
(..) 1-0
Note that the party details must also be taken care in the design.
7 8
(12 Marks)
----
O -0
co 0
CCI Ct: Module-2
3?
.2
3 a. Define the following terms : i) Key ii) Super key iii) Candidate key
I .,
-'
,
5 7,,
,
iv) Primary key v) Foreign key. (05 Marks)
?, ;- _, -
., b. Enumerate the steps involved in converting the ER constructs to corresponding relational
G
L
tables. (07 Marks)
z ?
E. '
-
',2
0 P .
c. Considering the schema
...... 0 -
t., d'
Sailors (sid , sname , rating , age)
.9
Boats (bid , bname , color)
0
cn
?
:4=
Reserves (sid , bid , day)
m =
,...
V
0 Write relational algebraic queries for the following :
8 -
>, ?-?
i) Find names of sailors who have reserved boat # 103.
t r 1
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.
P
?
>
E O . )
u
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
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)
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
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
? =
OR
2 a. Define the following terms :
II
i) Data model ii) Schema iii) Instance iv) Canned Transaction. (08 Marks)
USN
17CS53
(10 Marks)
i) List the items purchased by customer `Prasanth'.
ii) Retrieve items supplied by all suppliers starting from 1s
t
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.
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)
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)
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
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
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
bi ci di ei
b2 ci d
i
e
i
az b, c
i
d, e3
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)
OR
8 a. Normalize the below relation upto 3NF :
Module Dept Lecturer Text
MI Dl LI TI
MI DI LI T2
M2 Dl LI Ti
M2 DI LI T3
M3 DI L2 T4
M4 D2 L3 T I
M4 D2 L3 T5
M5 D2 L4 T6
2 of 3
17CS53
b. Define Multi valued Dependency and Join Dependency. Explain 4NF and 5NF with
examples. (10 Marks)
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.
{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.
(06 Marks)
(06 Marks)
(08 Marks)
3 of 3
FirstRanker.com - FirstRanker's Choice
This post was last modified on 02 March 2020