Birla
Institute of Technology & Science, Pilani
Work-Integrated
Learning Programmes Division
Second Semester 2015-2016
Comprehensive
Examination
(EC-3
Regular)
Course No. : SS ZG518
Course Title : DATABASE DESIGN AND
APPLICATIONS
Nature of Exam : Open Book
Weightage : 50%
Duration : 3 Hours
Date
of Exam : 10/04/2016 (AN)
No
of pages: 3
No
of questions: 5
Note:
1. Please
follow all the Instructions to Candidates given on the cover page of the
answer book.
2. All
parts of a question should be answered consecutively. Each answer should start
from a fresh page.
3. Assumptions
made if any, should be stated clearly at the beginning of your answer.
Q.1 (a)
For
a Library of a College, we need to design a database. The business rules are as follows.
We have Library
Users. A Library user can borrow Books from the Library. Each book belongs to a
category like- Engg/Management/Arts/Science (only one category). Each book has
title, bookID (unique), ISBN#, price as
attributes. Books are published by Publishers. A Publisher will have name(unique),
city, contact as attributes. Each book is published by only one publisher. Each
publisher in the database will have one to many books published. One user can borrow up to 5 books. We also
capture date of issue, expected return date when a book is borrowed. We store
only the info of currently issued books, not for the returned ones. Each
library user will have a userID (unique), name, address, and category as
attributes.
i
First
draw an ER diagram for the above requirement. Assume necessary data which is
missing in the question. The model should include- Entity types, relationships, min-max, cardinality,
participation, and other relevant constraints.
ii
Then
design relational schemas to capture the data represented in the ER diagram you
have drawn. [3 + 2 = 5]
Q.1 (b)
Look
at the following Database schema.
River(rid,
rname, length)
State(sname,
capital, population)
RiverState(rid, sname)
//This
relation captures info about what river flows through what states, and here rid is FK to rid of River, and sname is
FK to sname of State
Now,
write both Relational Algebra and SQL query
expressions to:
(i)
Get
the rid and rname for those rivers flow through all states with population
greater than 6 crores.
[1+ 1.5 = 2.5]
(ii)
Give
the sname and capital for those states which do not have river ‘Yamuna’ flowing
through them. [1 + 1.5 = 2.5]
Note:
You don’t have to rename the attributes in the result, use only DML
statements, and don’t use outer joins. Do not define new tables or views.
Q.2 (a)
(i)
Give
the procedure in steps, to decompose a relation in 3NF and not in BCNF, to
BCNF. Now schema of R is given as R(A,B,C,D) and FDs are {ABÃ CD; DÃ A}. As such R is
not in BCNF. Apply the steps to bring R
to BCNF.
(ii)
Assume that we have a relational R
with schema R(A,B,C,D,E,F) , with the following set (F) of functional
dependencies.
F={AÃ B; CÃ {E,F}; AÃ {C,D} } . If R
is decomposed into three relations- R1(A,B),
R2(C,E,F), R3(A,C,D). Now
check if this decomposition is dependency preserving or not.
[Note: Give complete working in steps]. [2 + 3 = 5]
Q.2 (b)
Assume
a situation where we have 8,50,000 records to be stored in a file. The record
length is 90 Bytes and the block size is 1024 Bytes. The address of any disk
block needs 5 Bytes, and the key field of the file is of 4 Bytes length.
Now
do the following.
(i)
If
no indexing is done, give the number of block accesses needed (worst) to
retrieve a record with given key value from the above file. Also give number of
data blocks needed to store the data.
(ii)
Now,
design a multilevel index with only two levels for the above file on the key
attribute. Give how many index blocks are needed at 1st and 2nd
level, and give the number of block
accesses needed to retrieve a record with given key value from the file using two level indexing structure.
Note: Assume unspanned record
organization. [5]
Q.3 (a)
(i)
For the following SQL query, give the query graph.
SELECT S.sid,
S.age, C.cname, C.ceo, P.salary
FROM Student as S, Company as C, Placement as P
WHERE S.sid=P.sid and C.cid=P.cid and
S.cgpa>8.0 and C.city=’DELHI’;
(ii) With a simple example brief how pushing the selection and
projection operations as down as
possible in the query tree, will improve the performance.
[3 + 2 = 5]
Q.3 (b)
(i)
In a certain concurrent schedule, we
have four transactions T1, T2, T3 and T4.
These transactions operate on data items
A, B, and C. The interleaving of
operations of these transactions is given in the below.
Schedule :
{r2(A); r3(B); r1(C); w2(A); r3(C); r4(B); w3(B); w1(B); r2(C); r4(C); w1(C); }
Note:
Here, r2(A); - means that the transaction-2 reads data
item A
w2(C); -
means that the transaction-2 updates
data item C
Now, Determine whether the above schedule is conflict
serializable, by drawing a precedence graph.
(ii) Explain why
undo operation is not required in deferred
modification technique, used in log-based recovery of databases. Give bulleted points. [3 + 2 = 5]
SS ZG518 (EC-3 Regular) Second Semester 2015-2016 Page 3
Q.4 (a)
Assume
that we use Linear hashing technique in some situation and we use the hash
Functions- h0, h1, h2, ... as (K mod 2), (K mod 4), (K mod 8) and so on.
Assume that a
bucket (one block) can accommodate 2
records. Now insert the records
with following keys in same order and
show the dynamic structure of the
hashing scheme after each insertion. Note that a split occurs whenever the File
Load Factor (f) exceeds 0.80. Do
consider overflow buckets also for calculating f .
Keys
to be inserted are: 14, 31, 5, 47, 56, 22,
2.
(Note: use the conventions taught in the
class; complete working is to be given.)
[5]
Q.4 (b)
Assume
an XML document (Production.xml) with
following structure. Root element is Production
which has one or more Part elements.
Each Part element has one Partname element, one Partcode as ID type attribute, zero or more Project elements (to capture info regarding the projects to which
the part is supplied), and one Cost element.
The Project element has Projname, Location, Budget as sub-elements. Further, the Location element has City
and State as sub-elements. Assume all
leaf nodes to have data of the type #PCDATA.
For the above XML scenario:
(i)
Give the XML DTD that specifies the
above structure.
(ii)
Write
an XQuery expression to retrieve partcode
and partname for those parts which
are supplied to at least one project located in ‘DELHI’ and cost greater than 500. [3 +
2 = 5]
Q.5 (a)
(i)
Brief on statistical queries and
related issues w.r.t., privacy protection in statistical databases. [2]
(ii) What are the benefits of using UML for
modeling databases? [3]
Note:
For both (i) and (ii) give answer using bulleted points.
Q.5 (b)
Look
at the following partial schedule involving four transactions T1, T2, T3, and
T4.
Partial Schedule: T2_lock_X(C);
T3_lock_S(B); T2_R(C); T2_lock_X(B); T3_R(B); T1_lock_X(A); T4_lock_X(C); T4_W(C);
T1_R(A); T3_R(B); T3_lock_X(A); T2_R(B); T1_lock_S(B); T1_R(B);
Now check, if this leads to a deadlock
condition, with the help of a wait-for graph.
Note: T2_lock_S(A) – means T2 locks data item A in
Share mode.
T2_lock_X(A) – means T2 locks data
item A in Exclusive mode.
T4_R(B)-
means T4 reads data item B
T3_W(A) -
means T3 writes data item A
Complete working is required.
[5]
********
No comments:
Post a Comment