Sardar Patel University 2007 B.E Computer Science CP311 DATABASE MANAGEMENT SYSTEMS(Internal ) - Question Paper

• Example: Consider the relation R and S as provided below:

Relation R Relation S
A B A C
a1 b1 a2 c2
a2 b2 a4 c4

R left outer join S R right outer join S
R.A R.B S.A S.C R.A R.B S.A S.C
a2 b2 a2 c2 a2 b2 a2 c2
a1 b1 Null null null null a4 c4
• As we can see that a1 and b1 are included in outcome of left outer join operation even though they donot satisfy the join condition.
• Same is with a4 and c4 in right outer join.

Marks Distribution: one mark every for explanation with example of left and right outer join.
[02]
b) State the whether the subsequent statements are actual or false. Justify your ans.
1) Union compatibility is needed for division operation.
• Above statement is false
• If we are performing R(X) ÷ S(Z) then we require that X is subset of Z.
2) Super key is an alternate key.
• Above statement is false.
• Super key have uniqueness property but may or may not be irreducible set where as alternate key is having both uniqueness and irreducible property.
3) There must be atleast 1 attribute in a relation.
• Above statement is false.
• Every relation has a set of attributes. The empty set is also a set means it is possible for a relation to have the empty set of attributes or no attributes at all.

Marks Distribution: one mark for mentioning every actual or false correctly with justification. No marks will be provided if justification is not provided.
[03]
c) How we can join 2 tables without using JOIN operation?
• We can 1st apply Cartesian product or cross product on both relation.
• Then we have to apply choose operation to choose specific data by giving the condition that just like join condition.
• So join operation = cross product + choose operation

Marks Distribution: one mark for giving above mentioned 2 points.
[01]
Q-3 a) What do you mean by self-referencing? provide 1 example.
A relvar having foreign key whose values are needed to match the values of a few candidate key in the identical relvar is called self-referencing.

Example: Employee(ssn P.K. , Ename , superssn F.K. referencing to ssn)
[01]
b) Consider a relation R (A, B, C, D, E) with the subsequent dependencies.
A -> BC, CD -> E, B -> D, E -> A
What is a key for R? obtain the irreducible set for provided FDs. obtain the closure of {ACD}.

Sol. key = A, BC, CD, E

{ACD}+ = {A, B, C, D, E}

Irreducible set:

A->B, A->C, D->E, B->D, E->A

OR

A->B, A->C, C->E, B->D, E->A
[03]
c) Consider the relations provided below:

Hostel(H#, Hname, Haddress, Total_capacity, Rector_name)
Room(H#, R#, Rtype, No_of_students, Status)
Charges(H#, Rtype, Rcharges)
Student(Sid, Sname, Saddress, Department, Class, H#, R#)
Fees(Sid, Fdate, Famount)

Rtype: Room kind whether room is single-seated or double-seated
For single-seated room value of Rtype = ‘s’
For double-seated room value of Rtype = ‘d’
Status: whether room is occupied or vacant.
For occupied values of Status = ‘o’
For vacant values of Status = ‘v’
Class: whether learner is studying in FY, SY, TY or Final Year.

Write SQL Queries for the following:

(1) Display hostel details, which have at lowest 1 single-seated room.

Ans. choose * from Hostel where H# in (select distinct(H#) from Room
where Rtype = ’s’);

(2) Display the Rector name & hostel address of students of IT department.


Ans. choose Sname,Class,Hname,Haddress,Rectorname from learner s,
Hostel h where s.H# = h.H# and department = ‘IT’;

(3) Display those hostel details where single-seated or double-seated rooms are vacant.

Ans. choose * from Hostel where H# in (select H# from Room where
Rtype in(‘s’,’d’) and Status = ‘v’);

(4) Display hostel details where there are at lowest 10 vacant rooms.

Ans. choose * from Hostel where H# in (select H# from Room where
Status = ‘v’ group by H# having count(*) >= 100);

(5) Display those hostels where single-seated room is the costliest.

Ans. choose * from Hostel where H# in (select H# from Charges where
Rtype = ‘s’ and Rcharges = ( choose max(Rcharges) from
Charges where Rtype = ‘s’ ));



















[01]





[01]







[01]





[1.5]





[1.5]

Earning:   Approval pending.