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]
********
Awesome...
ReplyDeleteSurvival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Now
Delete>>>>> Download Full
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download LINK
>>>>> Download Now
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Full
>>>>> Download LINK Ye
i tried mailing you , but it is not going through. Can you please share the answers to this question paper if you have.
ReplyDeleteThanks
Mail me at ashishjainblogger@gmail.com
DeleteMention the subject(s) for which you want help.
Thanks for putting up the content in such organized manner.Great Help. Thumbs UP !!
ReplyDeleteRecorded lecture links are not working please help
ReplyDeleteIs there a group/whatsapp group for Mtech in computing systems and Infrastructure?
ReplyDeleteVery useful .. Thank you very much
ReplyDeleteAre you Looking for Smart Device Development? QuantaEd Solutions is the Best Smart Device Development Company, We design and develop smart devices that suit the healthcare requirements. For any inquiry call us we will provide all kind of assistance. For more details visit- https://quantaedsolutions.com
ReplyDeleteThis post is so interactive and informative.keep updating more information...
ReplyDeleteSoftware Testing Courses in Mumbai
Software Testing Training in Ahmedabad
Software Testing Courses in Kochi
Software Testing Courses in Trivandrum
Software Testing Courses in Kolkata
Thanks for the blog article.Thanks Again. Keep writing.
ReplyDeletejava online training hyderabad
java online training in india
Thanks for the blog article.Much thanks again. Fantastic.
ReplyDeleteonline training in java
online training on java
AI & ML in Dubai
ReplyDeletehttps://www.nsreem.com/ourservices/ai-ml/
Artificial intelligence is very widespread today. In at least certainly considered one among its various forms has had an impact on all major industries in the world today, NSREEM is #1 AI & ML Service Provider in Dubai
1634348519669-9
Thank you for giving valuable information about software for portable device, we can also develop custom software from pixabulous design.
ReplyDeleteNice Blog!!!
ReplyDeleteServiceNow Training
ServiceNow Online Training in Hyderabad
This article explains in a clear manner. Nice way of explaining. Thanks for sharing. cloud engineering services
ReplyDeleteI really liked your blog post.Much thanks again. Awesome.
ReplyDeletejava online training
java training
Data Science Training In Noida
ReplyDeleteData Science course In Noida
WILP is a set of educational programs designed in such a way that they can be easily integrated into your work life. Earlier, only highly developed nations like the US and Europe were indoctrinating WILPs but now the WILP in India have also gained a lot of popularity.
ReplyDeleteCandidates who wish to take the BITSAT should begin studying as soon as possible. Due to the high level of competition, it is critical to follow the best BITSAT 2022 preparation tips recommended by professionals. This blog post contains BITSAT 2022 study suggestions as well as exam pattern and syllabus information. Continue reading to get answers to all of your questions. To know more information visit @ SSSi Online Tutoring Services.
ReplyDeleteSurvival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Now
ReplyDelete>>>>> Download Full
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download LINK
>>>>> Download Now
Survival8: Bits Wilp Software Development For Portable Devices 2017-H2 Handout >>>>> Download Full
>>>>> Download LINK bO
"Thanks for sharing this informative blog on Best Software Development company in chennai,Software Development Company in chennai,
ReplyDeleteBest Software Development company in india,
Top software development company in chennai,
Software Development Company in india"
The BITS Pilani Admission Process is designed to select the brightest minds for its world-class programs. With its independent entrance exam, BITSAT, and direct admission opportunities for board toppers, the institute ensures that only the most deserving candidates secure a place.
ReplyDelete