Topic: Databases (Test 3)



Topic: Databases
Q.1
Which one of the following is NOT a part of the ACID properties of database transactions?
A. Atomicity
B. Consistency
C. Isolation
D. Deadlock-freedom
Answer : Option D
Explaination / Solution:

‘D’ means durability not deadlock freedom.

Workspace
Report
Q.2
 A database of research articles in a journal uses the following schema. (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE) 
The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE)  → TITLE 
(VOLUME, NUMBER                                              → YEAR 
(VOLUME, NUMBER, STARTPAGE, ENDPAGE)  → PRICE
The database is redesigned to use the following schemas. 
(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE) (VOLUME, NUMBER, YEAR) 
Which is the weakest normal form that the new database satisfies, but the old one does not?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
Answer : Option A
Explaination / Solution:

candidate key is (volume, number, start page, end page) (Volume number) → year is a partial dependency. So original table is in 1NF but not in 2NF

Workspace
Report
Q.3
The statement (¬p) ⇒ (¬q) is logically equivalent to which of the statements below?
I. p ⇒ q
II. q ⇒ p 
III. (¬q) ∨ p
IV. (¬p) ∨ q
A. I only
B. I and IV only
C. II only
D. II and III only
Answer : Option D
Explaination / Solution:

By rule of contrapositive, 


Workspace
Report
Topic: Databases Tag: CS GATE 2012
Q.4
Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q : An SQL query can contain a HAVING clause only if it has GROUP BY clause R : All attributes used in the GROUP BY clause must appear in the SELECT clause S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
A. P and R
B. P and S
C. Q and R
D. Q and S
Answer : Option B
Explaination / Solution:

If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. So, option P is true and Q is false. 
S is also true as an example consider the following table and query. 


Workspace
Report
Topic: Databases Tag: CS GATE 2012
Q.5
Given the basic ER and relational models, which of the following is INCORRECT?
A. An attribute of an entity can have more than one value
B. An attribute of an entity can be composite
C. In a row of a relational table, an attribute can have more than one value
D. In a row of a relational table, an attribute can have exactly one value or a NULL value
Answer : Option C
Explaination / Solution:

The term ‘entity’ belongs to ER model and the term ‘relational table’ belongs to relational model. Options A and B both are true since ER model supports both multivalued and composite attributes. As multivalued attributes are not allowed in relational databases, in a row of a relational (table), an attribute cannot have more than one value.

Workspace
Report
Q.6
The following functional dependencies hold true for the relational schema R{V,W,X,Y,Z}:
V → W 
VW → X 
Y → VX 
Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies ?
A. V → W V → X Y → V Y → Z
B. V → W W → X Y → V Y → Z
C. V →W V → X Y → V Y → X Y → Z
D. V → W W → X Y → V Y → X Y → Z
Answer : Option A
Explaination / Solution:



Workspace
Report
Q.7
 Consider a database that has the relation schemas EMP(EmpId, EmpName, DepId). And DEPT(DeptName, DeptId). Note that the DeptId can be permitted to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

Which of the above queries are safe? 
A. (I) and (II) only
B. (I) and (III) only
C. (II) and (III) only
D. (I), (II) and (III)
Answer : Option D
Explaination / Solution:

Query which generates infinite number of tuples is called unsafe query. In the given question all the given queries generate finite number of tuples.

Workspace
Report
Topic: Databases Tag: CS GATE 2012
Q.8
Which of the following is TRUE?
A. Every relation is 3NF is also in BCNF
B. A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
C. Every relation in BCNF is also in 3NF
D. No relation can be in both BCNF and 3NF
Answer : Option C
Explaination / Solution:

Option A is false since BCNF is stricter than 3NF (it needs LHS of all FDs should be candidate key for 3NF condition) Option B is false since the definition given here is of 2NF Option C is true, since for a relation to be in BCNF it needs to be in 3NF, every relation in BCNF satisfies all the properties of 3NF. Option D is false, since if a relation is in BCNF it will always be in 3NF.

Workspace
Report
Q.9
In a database system, unique time stamps are assigned to each transaction using Lamport’s logical clock . Let TS(T1) and TS(T2) be the timestamps of transactions T1 and T2 respectively. Besides, T1 holds a lock on the resource R, and T2 has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp.
if TS(T2) < TS(T1) then
Tis killed
else Twaits.
Assume any transactions that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks? 
A. The database system is both deadlock-free and starvation- free.
B. The database system is deadlock- free, but not starvation-free.
C. The database system is starvation-free but not deadlock- free.
D. The database system is neither deadlock- free nor starvation-free.
Answer : Option A
Explaination / Solution:

Elder kills younger and youngers waits on elder. So both are not waiting for each other. Hence no deadlock and there won’t be any starvation as well because the transaction who got killed will be starting with same time stamp. 

Workspace
Report
Topic: Databases Tag: CS GATE 2012
Q.10
Suppose R1  are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constrains, which of the following is ALWAYS TRUE?
A. ΠB(r1) − ΠC (r2) =∅
B. ΠC (r2 ) − ΠB (r1) =∅
C. ΠB (r1) = ΠC (r2)
D. ΠB (r1) − ΠC (r2) ≠∅
Answer : Option A
Explaination / Solution:
No Explaination.


Workspace
Report