4095 2

Section A

1. (a) Consider the following Entity Relationship diagram

(ERO) for n ternary relationship ProjGuide. Map
the given ER diagram to a relation schema.
(3)

P1-0Jttt

IDUtuCltr S1adt11t

(b) Consider the following SQL statements : (3)

(i) CREA TE TABLE

(ii) SELECT

(iii) INSERT

(iv) CREATE VIEW

(v) DELETE

(vi) ALTER TABLE

409 5 3

For each of the abov e comn1ands, indic ate whe ther

it is a Data Man ipul atio n Lan guag e (DM L)
com n1an d, Data Def initi on Lan guag e (DD L)
com man d, or View Defi nitio n Lang uage (VD L)
com man d.

( c) Con side r the follo wing relat ions : (3)

Emp loye e(em pID : inte ger, dept ID: inte ger,
emp Sala ry: integ er, emp Hob by: char (20) )

Dep artm ent( dept ID: integ er, dept N ame: char (20) ,
dept Floo r: integ er)

Whi ch attri bute s will appe ar 1n the outp ut on

exec uting the follo wing SQL quer ies?

(i) SEL ECT * FRO M Emp loye e E NAT URA L

JOIN Dep artm ent D;

(ii) SEL ECT * FRO M Emp loye e E, Dep artm ent

D WH ERE E.de ptID = D.de ptID ;

(d) Con side r the follo wing relat ions for a data base
that keep s track of busi ness trips of sales pers ons
work ing in a sale s offic e : (3)

SAL ESP ERS ON (SS N, nam e, join ingD ate,

supe rviso rs SN)

P.T. 0.
~
4095 4

TRf P (tripJD, SS N,fromCity , toCity, departureDate

return Date) '

EXP ENSE (tripID, account No, am ount)

The sales office maintain s multiple bank accounts.

A trip can be charged to one or ·more accounts.
Specify the forejgn keys for the above relations.

(e) Consider the following relations R and S : (3)

R s
A B C D B D E

15 1 15 a 1 a 15

20 2 25 a 3 a 20

25 4 20 b 1 a ZS
15 1 25 a 2 b 30

30 2 20 b 3 b null

Show the output for the following relational

statements :

(i) R )><{ R.B = S.B S

(ii) R t><J R.C = S.E S

4095 5
(f) Consider the following rel ati
on al sch em a: (3)
Su pp lie rs( sID : int eg er, sN am e:
str ing , add res s:
string)

Pa rts (pI D: int ege r, pN am e: str ing

, col or: str ing )
Ca tal og (sl D: int ege r, plD : int ege
r, cos t: rea l)

Wr ite rel ati on al alg ebr a exp res sio

ns to per for m
the fol low ing :

(i) Fin d the nam es of sup pli ers wh

o sup ply a
red par t.

(ii) Lis t the ID s and nam es of par

ts wi th an
ent ry in the tab le Ca tal og .

(g) Ide nti fy mu ltiv alu ed and com po

sit e attributes from
the fol low ing com ple x att rib ute :
(3)

{H ob by sta ts (N am e (Fi rst _n am

e, La st_ nam e),
{Phone (Area_code, Phone_number)},
{Hobbies})}

(h) In the giv en sch ed ule , wh at

is the pro ble m
en co un ter ed du e to co nc urr en
t ex ecu tio n of
tra nsa cti on s Tl and T2? As sum ing
the initial value
X=S, wh at wi ll be the val ue of X aft
er the schedule
is exe cut ed? (3)

P.T .0.
4095 6

Times tamp
1
Tl
read(X)
T2 --
----
2 X-X+lO
3 Read(X) ~

-
4 X=X+20
5 Write(X)
6 Write(X)
7 Commit
8 Commit

(i) Consider the following relation StudentCourse.

(3)

StudentC ourse
studentID studentName CGPA courseID course Name credits

123 Shyarn 9 Cl18 C++ 4

132 Shyam 8.5 Cl21 Java 4

131 Mohan 7,5 Cll8 C++ 4

135 Vijay 8 Cl18 C++ 4

Which of the following commands result in an

update anomaly? Justify your answer.

(i) DELETE FROM StudentCourse WHERE

studentID = 132

(ii) UPDATE StudentCourse SET credits = 3

WHERE courseID = ltf 8
7
4095
E, F, G, H,
ons ide r the rel ati on R = {A, B, C, D,
(j) C end enc ies
I, J} an d the set of fun cti on al dep
{B} ➔ {F} ,
F == { {A, B} ➔ {C }, {A} ➔ {D, E} ,
(3)
{F} ~ {G , H} , {D} ➔ {I, J}} .

(i) Fin d the clo sur e of {A, B} .

key , doe s
(ii) As sum ing {A, B} as the pri ma ry
enc y?
the rel ati on R exh ibi t par tia l dep end
Jus tify yo ur ans we r.

Se cti on B

giv en rel ati on

2. (a) Co nsi der a rel ati on R(A , B). Is the
(3)
in BC NF ? Wh y or wh y not ?

(b) Co nsi der the fol low ing ER dia

gra m and ans we r
tha t fol low : (5)
the que stio ns

ST UD EN T

P.T .0 .
40 95 8
the
(i) Sp ec ify the ro le na me s for given
rel ati on shi p.

(ii) D ete rm ine the car din ali ty rat io • Jus t'f
1 Y you r

an sw er.

con str ain t.

(iii) De ter mi ne the pa rti cip ati on
Ju sti fy yo ur an sw er.

for which {Car#,

( c) ro ns ide r the fol low ing rel ati on
As sum e that all
Sa les pe rso n# } is the pri ma ry key .
Als o, assume
att rib ute s are sim ple an d ato mi c.
le salespersons.
tha t a ca r ma y be sol d by mu ltip
, Commission%,
Ca rSa le( Ca r#, dateSold, Salesperson#
Di sco un tA mt )
s are :
Ad dit ion al fun cti on al de pe nd en cie

{d ate So ld ➔ Di sco un tA mt }, {S ale spe rso n# ➔

%} (3+4)
Co mm iss ion

(i) Ba sed -0n the giv en pn ma

ry ke y, che ck
in 2N F.
wh eth er the ab ov e sch em a is
Jus tif y yo ur an sw er.

rel ati on up
(ii) If req uir ed , no rm ali ze the giv en
ste ps.
to 3N F. Sh ow all the int erm ed iat e
9
4095
(a) consider the foll ow ing rela tion sch em
).
a: (4)

Studen t(SS N, Nam e, Ma jor, Bir thd ate )

Course( Cou rse ld, Cou rse N am e)

Enr oll( SSN , Cou rse ld, Du rati on)

Which of the rela tion al mo del con stra ints

may be
vio late d by the foll ow ing ope rati ons ? Jus
tify you r
answer in eac h cas e,

(i) Ins ert a rec ord in the Enr oll tab le.

(ii) Del ete a rec ord from the Stu den t tab
le.

(b) Des crib e the thre e-s che ma arc hite ctu
re wit h the
hel p of a suit abl e dia gra m. In this con tex
t, giv e a
suit abl e exa mp le of dat a ind epe nde nce .
(3+2)

~ (c) Sta te and pro ve the Pse udo trai lsit ive
infe ren ce
rule.
(1+3+2)

App ly the abo ve rule to infe r ON E add

itio nal
fun ctio nal dep end enG y for the giv en
set F =
{M ➔ P, MY ➔ P, YP ➔ C}

4.
(a) Consider a rela tion R(A , B, C, D, E)
with the
following dep end enc ies :
(2)

P.T .0.
4095 10

{AB ➔ C, CD ➔ E DE
, -; B}

Is AB a candidate key of this .

re 1ation? J
your answer. · Ustify

.
(b) Consiaer the following SQL statement :
(4)

Create table Student

(Rollno INT,

Name VAR CHAR( 15),

Marks DECIMAL(3,2),

Age INT CHECK(Age>=l 7 and Age <=25),

DOB DATE);

Which of the following values entered for the

columns holds valid? Justify your answer for each
case.

(i) '14-12-2002' for DOB

(ii) 34. 75 for Marks

(iii) 16 for Age

(iv) '21' for RollNo

11

(c) Consider the following relation schema : (9)

Student (sNum: integer, sName: string, maJor:

string, level: string, age: integer)

Class (cName: string, room: string, fID: integer)

Enrolled (sNum: integer, cName: string)

Write SQL statements to perform the following :

(i) Find the names of all classes that either

meet in room 'R12' or have five or more
students enrolled.

(ii) For all levels except 'JR', display the level

and the average age of students for that
level.

(iii) Find the names of students not enrolled in

any class.

5. (a) Consider the following relational schema: (3)

retiredEmployee
empID empName oasicSalary k:leptName payGrade
101 Rahul 25000 Finance 1\BC
102 Rohit 35000 Admin DEF
103 Naman 15000 Research ABC
104 Sreejee 40000 Finance DEF
!_Q5 Pranay 22000 Admin PQR
106 Dheeraj 45000 Research l?QR
-
107 IAarav 14000 Finance ~BC

P.T .0.
4095 12

pensionGrade
payGrade Amount
ABC 2500
DEF 3000
PQR 3500

Show the result for each of the following on the

tables :

(i) SELECT deptName, COUNT (*), SUM

(basicSalary)

FROM retiredEmployee

GROUP BY deptName;

(ii) SELECT empID, empName, deptName

FROM retiredEmployee

WHERE empName LIKE '_a%';

(b) Consider the following ER diagram to conceptualize

a database that can be used to keep track of
transport ships and their locations. (6)

(i) Map the given ER diagram into a relational

schema.

(ii) Specify the primary key and foreign keys

for each relation.
13

~O VE ME N~

(c) Co ns ide r the fa llo wi ng tw

o tab les , T 1 an d T2 :
(6)

Tl
p T2
Q R A
10 B C
a 5 10
15 b 6
b 8
25 15 C 3
a 6 10 b 5

Show the re su lts of th e fo llo

wi ng op er ati on s :

(i) PT (T l X T2 )

(ii) Tl - T2

(iii) Tl n T2

P. T. 0.
, 4095 14

. (a) Com pare and cont rast the tradi tiona l file
6
proce ssing appro ach with the datab ase approach
in the conte xt of the self-d escrib ing nature of the
datab ase syste m. (2)

(b) Why can a datab ase allow at most one primary

index on a file but sever al secon dary indexes?
(3)

(c) Consi der a relati on R with three attrib utes {A, B,

C}. It is decom posed into relati ons RI with
attrib utes {A, B} and R2 with attrib utes {B, C}.
State the cond ition (usin g relati onal algeb ra
notation) that should be met for this decomposition
to satisf y lossle ss-joi n prope rty. (4)

(d) Considering the below given state of R(A, B, C, D):

A B C D (6)
1 2 3 4
1 2 3 5
6 7 8 2
2 1 3 4

Which of these FDs may hold on R? Justif y your

answer.

(i) D ➔ A

(ii) BC ➔ D

(iii) BC ➔ A
15

(a) How does multilevel indexing improve the

efficiency of searching an index file? (3)

(b) Sup pose that we have an ordered file with

r == 10 ,000 records stored on a disk. The records
are of fixed size and are unspanned. The search
key field in each record is V = 9 byt~s long. The
remaining attributes of the record are 91 bytes in
total. The block size for the disk is B = 1024
bytes.

Compute the following :

(i) record length (R)

(ii) blocking factor (bfr)

(iii) number of file blocks (b)

(iv) number of block accesses required during

binary search on the data (4)

( c) ABC shipping company prides itself on having up-

to-date information on the processing and current
location of each shipped item. To do this, the
company relies on its database management
system.

Shipped items can be characterized by item

number (unique), weight, dimensions, insurance

P.T.0.
4095 16

amount, destination, and final delivery date.

Shipped iten1s are received into the system at a
single retail center. Retail centers are characterized
by their type, unique ID, and address. Shipped
items arrive at their destination via one or more
standard transportation events (i.e., flights, truck
deliveries). These transportation events are
characterized by a unique schedule number, a type
(e.g., flight, truck), and a deliveryRoute. ·

Create an Entity Relationship diagram that

captures this information about the company. Also,
indicate the primary key, cardinality, and
participation constraints. (8)