D Y Patil International University, Akurdi Pune

Established under Maharashtra act No. LXIII of 2017 Sector 29,

Pradhikaran, Akurdi Pune (Maharashtra) India 411044
School of Computer Science Engg. & Application (SCSEA)

NAME: Prathamesh Shailendra More

PRN: 20200802214

Q1. Ans:
d)S1 is a Data Definition (DDL) Query and S2 is a Data Manipulation Query

Q2. Ans:
b) IIattribute_name(relation_name1)∊IIattribute_name(relation_name2)

Q3.
Ans: c) student_id, course_id

Q4.
Ans: c) r ⋈ s (r natural join s)

Q5.
Ans: c) FULL OUTER JOIN

Q6.
Ans: c) R1 – (R1 – R2)

Q7.
Ans: b) A Candidate Key for which no proper subset is also a Candidate
Key is called as Super Key.

Q8.
Ans: b) department_id

Q9.
Ans: b) sap

Q10.
Ans: b) {12, Jessica} is an instance of a relation schema

DBS ASSIGNMENT NO 02

Q1.
Ans: b) employee_id department
c) manager department

Q2.
Ans: d) SELECT pname FROM emp_project WHERE dnum = 4;

Q3.
Ans: c) INSERT INTO customer VALUES (‘Stacey’,’Loan’,10)

Q4.
Ans: b) SELECT branch_name FROM loan
GROUP BY branch_name
HAVING AVG (amount) < 126678

Q5.
Ans: d) ALTER TABLE student
MODIFY fees DEFAULT 1000;
Q6.
Ans: c) 7

Q7.
Ans: b) course NATURAL RIGHT OUTER JOIN prereq
Q8.
Ans: a) Agacy

Q9.
Ans: c) Sahana can grant create index privilege to other users

Q10.
Ans: a) Prints the name of the employees who have salary greater than
atleast one employee of department 5
DBS ASSIGNMENT NO 03

Q1.
Ans: b) LAPTOP

Q2.
Ans: a) {<d> | Ǝs(< d,s > € EMPLOYEE ∧ s >50000)}

Q3.
Ans: d) {t|Ǝd € DOCTORS(t [DOCTORNAME] =d[DOCTORNAME] ∧
Ǝh € HOSPITAL (h[HOSPITALID] =d[HOSPITALID] ^ h[NAME] =
Peerless'))}

Q4.

Ans: c) IIDOCTORNAME(HOSPITAL (NAME='PEERLESS (DOCTORS)))

Q5.
Ans: a) IIRANK(Marks > 80 ∧ SCHOOL = ‘ ABS INTERNATIONAL’(
STUDENTS ))

Q6.
Ans: c) EXEC SQL
DECLARE C CURSOR FOR
SELECT NAME
FROM PRODUCT
WHERE SALE_COUNT> accounted_sale AND MONTH= :target_month
END EXEC

Q7.
Ans: b) A LOAN can be borrowed by N CUSTOMERS and a CUSTOMER can
borrow atmost 1 LOAN.

Q8.
Ans: a) DOGS inherit the attributes of CARNIVORES.
b) DOGS inherit the attributes of ANIMALS.

Q9.
Ans: d) EXEC SQL BEGIN DECLARE SECTION;
int PRODUCTID:
char PRODUCTNAME [50]; EXEC
SQL END DECLARE SECTION;

Q10.
Ans: a)
DBS ASSIGNMENT NO 04

Q1.
Ans: b) (B, D, E) → B
d) {A, B, C} {E, F}

Q2.
Ans: c) X → Y , X → Z

Q3.
Ans: b) Reg No, Class.ID, [Address Phone)

Q4.
Ans: b) Pug

Q5.
Ans: a) FD2 and FD3
c) FD5

Q6.
Ans: a) 1 NF

Q7.
Ans: b) R₁(A, B, C), R₂(A, E, D), R3(A, E, F, G)
Q8.
Ans: d) Both 1 and 2 are lossy

Q9.
Ans: a) { P , Q, R ,S }

Q10.
Ans: b) 2NF
DBS ASSIGNMENT NO 05

Q1.
Ans: b) Approx. 44 milliseconds

Q2.
Ans: b) Level 1

Q3.
Ans: b) CGI (Common Gateway Interface)

Q4.
Ans: d) 99.02%

Q5.
Ans: a) URI (Uniform Resource Identifier)

Q6.
Ans: b) Doctor

Q7.
Ans: a) Test_date, Doc_Id and Pat_Id Q8.
Ans: b) It is used to record the history of operations performed on a file

Q9.
Ans: d)

Expected op:

Q10.
Ans: c) 8
ASSIGNMENT 6

1)Consider a B+tree in which the maximum number of keys in a

node is 5. What is the minimum number of children in any
nonleaf, non-root node? a) 2 b) 3 c) 4 d) 5
Solution: b)

2) A hash table of length 8 uses open addressing with hash

function h(x)= x mod 6, and linear probing. What will be the
content of the table after inserting the six key values 26, 22,
37, 19, 45, 29 in the table:
Solution: a)

3) Consider the following query:

SELECT S.sid, COUNT (*) FROM STUDENT S GROUP BY
S.sid
What is the correct property of the index if an index on sid is
available and the query can be answered by scanning only the
index?
a) the index is only hash b) the index is only B+ tree c) index
can be hash or B+ tree
d) index cannot be hash or B+ tree.
Solution: c)

4)There are 5 records in a database.

There is an index file associated with this table and it contains

the values 1, 4, 2, 3, 5. From which attribute is the index built?
a) Age b) Job c) Name
d) Salary
Solution: b)

5)Consider the Employee Table.

Consider the bitmap index for the Occupation field, with F1, F2,
F3, and F4 representing different Occupation. If the bitmap
index value of F1, F2 and F3 are as follows:

Identify the correct option representing F1, F2 and F3.

a) Clerk, Salesman, and Manager

b) Salesman, Analyst and Clerk.
c) Analyst, Salesman and Manager d) Analyst, Salesman and
Clerk Solution: d)
6)Suppose that one block in a disk can store either 5 records or
15 key pointers. If a database contains 1000 records, how many
blocks do we need to store the data file and the dense index? a)
260 b) 200 c) 50 d) 267 Solution: d)

7) Which is the correct 2-3-4 tree with the following keys:

11, 33, 44, 22, 77, 66, 99, 55, 88, and 90?
Solution: b)

8) What is the maximum possible height of any balanced

binary search tree with 7 nodes? Assume that the height of a
tree with a single node is 0. a) 2 b) 3 c) 4
d) 5
Solution: b)

9) Which of the following is dense index? a) Primary index b)

Clusters index c) Secondary index d) Sparse index Solution: c)

10) If a table has an index table like the following:

Select the correct option for the above indexing: a) Primary

Indexing b) Secondary Indexing c) Clustering Indexing
d) Non-Clustering Indexing Solution: c)
Assignment 7

1. Consider the following schedule

R(X) denotes read operation on data X by transaction Ti W(X) denotes write

operation on the data item X by Transition Ti Choose the correct option for the
above transaction schedule
ANS : (a) View Serializable

If we see the precedence graph there is a loop/cycle. Hence this schedule cant
be conflict serializable since this is not conflict serializable this schedule is view
serializable.
2.

ANS : (a) i-T, ii-T, iii-F

If we consider the precedence diagram there is no loop/cycle so it is conflict

serializable and Hence there is no failure point the schedule is recoverable.

3.

ANS : (b) Concurrency Control System Explanation

:
Recovery manager is used to recover the data when there is failure in the
database. The DBMS creates a security system that enforces user security and
data privacy. Security rules determine which users can access the database, which
data items each user can access, and which data operations (read, add, delete, or
modify) the user can perform. Query optimization techniques works with the
queries so the answer is Concurrency Control System

4.

ANS : (d) T4,T3,T2,T1

Explanation :
If we observe the graph T3 should be executed first but without executing T4
T3 won’t be executed therefore T4 will executed first after T4 , T3 will be
executed followed by T2 and T1.
5.

ANS: (c) if TS(Ti) < W-timestamp(Q),the operation is rejected and Ti is

rolled back Explanation : if TS(Ti) >= R-timestamp(Q),the operation is
rejected this case to be true it should be TS(Ti) > R-timestamp(Q),the
operation is rejected if TS(Ti) < W-timestamp(Q),the operation will be
executed
Hence if TS(Ti) < W-timestamp(Q),the operation is rejected and Ti is rolled
back is the correct option.

6.

ANS : (a) if any schedule is neither conflict serializable nor view

serializable then that schedule must be inconsistent Explanation : if any
schedule is neither conflict serializable nor view serializable then that
schedule may be or may not be consistent

7.
ANS : (c) 3
There can be 3 conflict serializable schedules
T3 T1 T2
T1 T3 T2
T1 T2 T3

8.
ANS : (b)
Explanation :
Since B is not given it may be inconsistent sometimes.

9.

ANS : (c) T2 T4 T3 T1 Explanation

:

If we observe the precedence graph the tasks will be executed as follows T2

T4 T3 T1

10.
ANS : (a)Partially Committed State Explanation
:
Partially Committed state is When a transaction executes its final operation, it is
said to be in a partially committed state.
ASSIGNMENT 8

1.

ANS : undo (T2) then redo (T0) then redo(T1) Explanation

:

2.
ANS : (a) 1465 block transfer is required

3.

ANS : (d) T0: No action and T1 : redo

Explanation : If we have to recover transaction T1 we only have to redo T1 because
there is no crash in the transaction T0
4.

ANS : (d) After recovery completion the value of B will be 400 and value of
A will be 1500 and the value of C will be 1000

5.

ANS : (c)If a {Ti, Oj , operation-end,U} record is not found then only

physical undo is required
6.

ANS : (c)Index : A Cost : B; Index : B Cost : B

7.

ANS : (b) The queries are equivalent as projection is moved down past the
equijoins and new projection operations are created as required.
8.

ANS :
9.

ANS : (a)

10.

ANS : (b) Q1 and Q2 give the same result