Download DU (University of Delhi) B-Tech 4th Semester 2330 Database Management Systems Question Paper

Download DU (University of Delhi) B-Tech (Bachelor of Technology) 4th Semester 2330 Database Management Systems Question Paper

?Wum ,t .,
[This question paper contains 6 printed pages.]
Sr. No. onuestion Paper : 2330 F?4 Your Roll No ................
Unique Paper Code : 2341403
Name of the Course : B.Tech in Computer Science
Name of the Paper : Database Management Systems (DBMS)
Semester : IV
Duration : 3 Hours Maximum Marks : 75
Instructions for Candidates
1. Write your Roll No. on the top immediately on receipt of this question paper.
2. Q. 1
3.
4.
1 (a)
(b)
(C)
(d)
is compulsory.
Attempt any four questions from Q. 2 to Q. 7.
Parts of a question must be answered together.
What does de?ning, manipulating and sharing of a database mean ? (3)
Differentiate between the following :
(i) Intension and extension of a schema
(ii) Weak and strong entities
(iii) Primary key and foreign key (2X3)
For the relation schemas: R(A, B, C) and S(D, E, F), write an expression in
relational algebra that is equivalent to the following SQL queries :
(i) Select * from R where B = 17
(ii) Select A, E from R, S . . ' ? (3)
What is a key ? Why should the key have time-invariant property ? (3)
P.T.0.

2330
(e)
(0
Q9
2
Give one example each of insertion and deletion anomaly in the relation
schema EMP_DEPT(Ename, Ssn, Bdate, Address, Dno, Dname, Dlocation).
How can these anomalies be removed from this relation ? (1+1+2)
Explain with the help of an example specialization of a class? How is it
represented diagrammatically in an EER diagram ? (4)
Map the following ER diagram into relational tables, specifying the table
names and their attribute names. (4)
-@ @/
(h)
(i)
\
Author m ? n Book
Which constraint can be violated by delete operation in relational model ?
What are the options available if deletion causes a violation ? (4)
Explain the ACID properties of transactions. (4)
Brie?y explain the following type of DBMS users :
(i) database administrators
(ii) na?ive end users
(iii) sophisticated end users (6)
(b) What operators form the complete set of relational algebra operations ?
De?ne join and intersection in terms of these operators. (4)

E
5;:
2t
'4:
12?
i;
2330 3
3.
(a) What is the difference between logical and physical data independence ?
Which is harder to achieve and why ?
V (3+2)
g
(b) Consider the following tables TI and T2 :
? T1 T2
P R P s T
1%
t 10 a 5 10 b 6
i 15 b 8 25 c 3
25 a 6 10 b 5
Show the result of the following operations :
(i) T1 u T2
(ii) Tl Right Outer Join T2 (2+3)
4. Consider the following relations containing airline ?ight information (keys are
underlined) :
F1ight(Flight No, From, To, Distance, Depart Time, Arrival Time, AircraftId)
Aircraft(AircraftId, Aircraft Name, Range)
Certi?edCPilotId. Aircraftld)
Pilot(PilotId, Pname, Salary)
Note that pilots can ?y only those aircrafts which they are certi?ed to ?y.
(a) Write relational algebra queries for the following :
(i) Find PilotIds of pilots who are certi?ed to ?y all aircra?s having range
more than 3000.
(ii) Find the names of pilots who earn more than average salary.
(2X2)
PTO.

2330 4
(b) Write SQL statements for the following :
utes.
(i) Create table Flight. De?ne suitable constraints for the attrib
(ii) Display names of pilots who have ?own aircraft with id = 123 from
?Delhi? to ?Mumbai?.
?P123? to fly aircraft with
(iii) Remove the certi?cation of pilot with id =
(2X3)
id = 286.
5. (a) C0
COLOR), which is abbreviated as REFRIG(
the following set F of functional dependencies :'F=
M, Y, P, MP, C), and
nsider the relation REFRIG(MODEL#, YEAR, PRICE, MANUF_PLANT, I
{M '> MP, {M,Y} '> P) x
l
MP ?-> C}
(i) Evaluate each of the following as a candidate key for REFRIG, giving
reasons why it can or cannot be a key: {M} and {M,Y} (3)
(ii) Indicate in what normal form is R in ? Normalize it till 3NF. (3)
(b) Consider the following relation :
A' B C
a1 b1 cl
al b1 (:2
a2 b1 cl
32 bl c3
For the given relation state, do the following functional dependencies hold
(justify your answer) :
?A>B

2330
6.
(ii) A ?> c
(iii) C -> B
(iv) B ?> A (4)
(a) Draw an ER diagram for the following situation :
(b)
There are various departments in the store. One department sells may items.
Some items may be sold by more than one department. A manager is an
employee who may look after more than one department but a department
may be looked after by only one manager. A supplier may supply more than
one item. All departments sell cettain items. Every department has employees
working in it. All items are supplied by suppliers.
Think of the appropriate attributes for entities involved. Clearly specify
cardinality ratios and participation constraints. State any assumptions that
you make for drawing ER diagram. (6)
Which of the following EER diagram is incorrect and why ? Clearly state any
assumption if any. (4)
E1
E o
1 <>?~? E3
E2
' M R N
R730.

2330 6
7. (a) Draw the query tree for the following expression:
1t ((1t (EMP) ? P
name SS? 5511
(n ?(DEPENDENT))) * EMP)
955
(b) Comment on the following :
(i) Every relation has at least one key.
(ii) Every primary key is a superkey but not vice?versa.
(5)
(2+3)
1
1,
l
(2000)
.,,_, 7 7 wrvavww W.