12CS54 / 16CS52
USN
RV COLLEGE OF ENGINEERING®
(An Autonomous Institution affiliated to VTU)
V Semester B. E. Fast Track Examinations July-19
Computer Science and Engineering
DATABASE DESIGN
Time: 03 Hours Maximum Marks: 100
Instructions to candidates:
1. Answer all questions from Part A. Part A questions should be answered
in first three pages of the answer book only.
2. Answer FIVE full questions from Part B.In Part B question number 2, 7
and 8 are compulsory. Answer any one full question from 3 and 4 & one
full question from 5 and 6
PART-A
1 1.1 _________ is concerned with rearrangement and possible reordering of
operations, use of correct algorithms and indexes during execution of
queries in component modules of . 01
1.2 Capacity to change internal schema without having to change
conceptual schema is called _________. 01
1.3 Justify with example that weak entity type always has a total
participation constraint with respect to its identifying relationship. 02
1.4 Define complex attribute with example. 02
1.5 Name any two unary and binary relational operations. 02
1.6 What are the reasons for any attribute to be , give example. 02
1.7 Prove that , Ε using Armstrong’s rule. 02
1.8 “ . . ” deletes the _________ along with all of its indexes. 01
1.9 Collections are allocated space inside each data file in chunks called
_________. 01
1.10 Node is a _________ of elastic search, while _________ consists of one
or more nodes. 02
1.11 Compare “failed state” and “terminated state” of transaction. 02
1.12 _________ algorithm was proposed to try to reduce the number of
needless aborts/ restarts in transaction processing. 01
1.13 Two tables are needed for efficient transaction recovery are _________
and _________. 01
PART-B
2 a Compare characteristics of Database approach and file system
approach. 04
b With a neat diagram, illustrate three schema architecture. 06
c Design an - diagram for a database by assuming minimum
of entities with minimum of attribute each. Mention
cardinality ratios and participation constraints for each relationships. 06
3 a For a given database schema, solve the following queries in relational
algebra.
! , "#$ , #%!"&, &
! , ! , #'
( ! , "#$ , ) *
Above schema contain entity which stores details of sailors
who has ( ) reserved boat on particular day and ( entity
includes details of Boats.
i) Find the names of sailors who have reserved boat #213.
ii) Find the names of sailors who have reserved blue boat.
iii) Find the colors of boats reserved by a sailor “ ”.
iv) Find the names of sailors who have reserved red or green boat.
v) Find the names of sailors who have reserved at least one boat
and age is greater than 20. 08
b Illustrate 0 to relational mapping steps with example database
schema. 08
OR
4 a Solve following queries in relational algebra for given database
schema.
1 % * 2 % * , 2 % * #$ , )!%'
$ $ , 1 % * , %' , !)
3!"& 2 % * , & % , #% $, #% ( , $
4 % & % ,4 % #$ , & %
i) List all single rooms with price below 2000 per day
ii) List names and address of all guests.
iii) List price and type of all rooms at 4 hotel.
iv) List all guests currently staying at 4 hotel.
v) List all hotels. 08
b Compare following set theory operations in relational algebra with
example.
i) and (
ii) ( 5( and . 08
5 a For a given Employee database, solve the queries in ; .
$ #$ , ", , 6, #*# ', "
% "#$ , " $, & ", & %# % #%#%
%_* ) " $, * )
8 9 )% 8"#$ , 8" $, 8* ), " $
: 3 _ " ", 8" , 1
" "% ", _"#$ , 6, #% , *#%! "
i) Retrieve all employees whose address is in ‘Bengaluru’
ii) Show the resulting salaries if every employee working on ‘ ’
project is given 10% raise.
iii) Display Dnum, name of employee and Pno for all employees
who work on projects which belongs to their department.
iv) Display all the employees who have two or more dependents. 08
b Compute the following:
i) Compute closure of and with respect to
=> , 5, 5 ?
ii) Compute minimal cover of
= >A 5, 5, , 5, 5 ?
iii) Prove that and 4 are equivalent.
=> , 5, 5 ?
4 = >A , 5, ? 08
OR
6 a For given database of Book dealer, solve the queries in ; .
(1 # %2 _! , "#$ , )!%', ) "% '
8 *! 2 *! 2_! , "#$ , )!%', ) "% '
5#% & ' )#% & ' 0 ! , ) ! %! "
5#%#* & 3 0 ! , %!%* , # %2 0 ! , *! 2 0 ! , )#% & ' 0 ! , ' # , !)
order-details(order_no, book_id, quantity)
i) Give details of authors who have two or more books in catalog
and price of books is greater than average price of books in
catalog and year of publication is after year 2000.
ii) Find author of book which has maximum sales
iii) Demonstrate how you increase the price of books published
by s specific publisher by 10%. 08
b Evaluate the following:
i) Prove that with example, “Every relation in 5 is in 3 , but
every relation in 3 is not in 5 ”.
ii) Prove that following decomposed relation R into @ , A and B is
lossless decomposition with given .
= > , , 5, , , ?
@ => , ?
A = >5, , ?
B = > , 5, ?
F={ , 5 > , ?, > , 5? ? 08
7 a Evaluate the following operations with example in MongoDB.
i) Adding user to collection
ii) Insert a document
iii) Delete all documents/data in collection
iv) Update a document. 08
b Differentiate between indexing a document and retrieving a part of
document in Elasticscarch. 08
8 a Justify that following schedules are serializable or not.
(@ (A
:
:
5 $$!%
5 $$!%
(@ (A
:
5 $$!%
:
5 $$!% 04
b Compare the following with respect to recovery
i) No-steal and No-force approach
ii) Checkpointing and fuzzy checkpointing 08
C Justify that, shadow paging is used as recovery method in
transaction processing with neat diagram. 04
