### SET 3

Question 1

**Consider a relational table with a single record for each registered student with the following attributes.**

1. Registration_Number: Unique registration number for each registered student

2. UID: Unique Identity number, unique at the national level for each citizen

3. BankAccount_Number: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attributes stores the primary account number

4. Name: Name of the Student

5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

A : BankAccount_Number is a candidate key

B : Registration_Number can be a primary key

C : UID is a candidate key if all students are from the same country

D : If S is a superkey such that S UID ? is NULL then S ? UID is also a superkey

Answer Discuss it!

.

Correct answer is :A

Solution :

In case two students hold joint account then BankAccount_Num will not uniquely determine other attributes.

Question 2

**Consider a relational table r with sufficient number of records, having attributes A1, A2,…, An and let 1 <= p <= n. Two queries Q1 and Q2 are given below.**

The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?

A : Ordered indexing will always outperform hashing for both queries

B : Hashing will always outperform ordered indexing for both queries

C : Hashing will outperform ordered indexing on Q1, but not on Q2

D : Hashing will outperform ordered indexing on Q2, but not on Q1.

Answer Discuss it!

.

Correct answer is :C

Question 3

**Database table by name Loan_Records is given below.
**
Borrower Bank_Manager Loan_Amount
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00

What is the output of the following SQL query?

SELECT Count(*)
FROM ( (SELECT Borrower, Bank_Manager
FROM Loan_Records) AS S
NATURAL JOIN (SELECT Bank_Manager,
Loan_Amount
FROM Loan_Records) AS T );

A : 3

B : 9

C : 5

D : 6

Answer Discuss it!

.

Correct answer is :C

Question 4

**Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X= 1, Y=l) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X=7;**

A : 127

B : 255

C : 129

D : 257

Answer Discuss it!

.

Correct answer is :A

Question 5

**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 Discuss it!

.

Correct answer is :B

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.

Question 6

**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 Discuss it!

.

Correct answer is :C

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.

Question 7

**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 Discuss it!

.

Correct answer is :C

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.

Question 8

**Suppose R1 (**__A__, B) and R2 (__C__, D) 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 Discuss it!

.

Correct answer is :A

Solution :

Since B is a foreign key referring C,values under B will be subset of values under C ( π_{B} (r1 ) ⊆ π_{C}(r2) => π_{B}(r1) - π_{C}(r2) = ∅ )

Question 9

**Consider the following transactions with data items P and Q initialized to zero: **

T1 : read (P) ;

read (Q) ;

if P = 0 then Q : = Q + 1 ;

write (Q).

T2 : read (Q) ;

read (P)

if Q = 0 then P : = P + 1 ;

write (P).

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

A : a serializable schedule

B : a schedule that is not conflict serializable

C : a conflict serializable schedule

D : a schedule for which precedence graph cannot be drawn

Answer Discuss it!

.

Correct answer is :B

Question 10

**Consider the following relations A, B and C:**

How many tuples does the result of the following SQL query contain?

SELECT A.Id

FROM A

WHERE A.Age > ALL(SELECT B.Age

FROM B

WHERE B.Name = ‘Arun’)

A : 4

B : 3

C : 0

D : 1

Answer Discuss it!

.

Correct answer is :B

Solution :

As the result of subquery is an empty table, ‘>ALL’ comparison is true . Therefore, all the three row id’s of A will be selected from table A.

Question 11

**Consider the following relations A, B and C:**

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of A.

(A U B) <> _{A.id>40 ∨ C.id <15 } C

A : 7

B : 4

C : 5

D : 9

Answer Discuss it!

.

Correct answer is :A

Question 12

**An index is clustered, if**

A : it is on a set of fields that form a candidate key

B : it is on a set of fields that include the primary key

C : the data records of the file are organized in the same order as the data entries of the index

D : the data records of the file are organized not in the same order as the data entries of the index

Answer Discuss it!

.

Correct answer is :C

Solution :

Clustered index is built on ordering non key field and hence if the index is clustered then the data records of the file are organized in the same order as the data entries of the index.

Question 13

**Consider the following relational schema. **

Students(rollno: integer, sname: string)

Courses(courseno: integer, cname: string)

Registration(rollno: integer, courseno; integer, percent: real)

Which of the following queries are equivalent to this query in English?

“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname

FROM Students as S, Registration as R

WHERE R.rollno=S.rollno AND R.Courseno=107 AND R.percent>90

(II) πsname (σ_{courseno =107 } ∧ percent> 90 (Registration Students))

(III) {T | ∃S ∈ Students, ∃R ∈ Registration (S.rol ln o= R.rol ln o ∧ R.courseno =107 ∧ R.percent > 90 ∧ T.sname = S.name)}

(IV) { ~~N | ∃S~~_{R} ∃ R_{P} ( ~~R,S~~_{N}> ∈ Students ∧ < S_{R},107,R_{P}>∈ Registration ∧ R_{P} > 90 )}

A : I, II, III and IV

B : I, II and III only

C : I, II, and IV only

D : II, III and IV only

Answer Discuss it!

.

Correct answer is :A

Solution :

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.

Question 14

**Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F ={CH -> G, A ->BC, B->CFH, E->A, F->EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R **

How many candidate keys does the relation R have?

A : 3

B : 4

C : 5

D : 6

Answer Discuss it!

.

Correct answer is :B

Solution :

Candidate keys are AD, BD, ED and FD

Question 15

**Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F ={CH -> G, A ->BC, B->CFH, E->A, F->EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R **

The relation R is

A : in INF, but not in 2NF

B : in 2NF, but not in 3NF

C : in 3NF, but not in BCNF

D : in BCNF

Answer Discuss it!

.

Correct answer is :A

Solution :

A->BC,B->CFH and F->EG are partial dependencies. Hence it is in 1NF but not in 2NF

Question 1

1. Registration_Number: Unique registration number for each registered student

2. UID: Unique Identity number, unique at the national level for each citizen

3. BankAccount_Number: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attributes stores the primary account number

4. Name: Name of the Student

5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

.

Correct answer is :A

Solution :

In case two students hold joint account then BankAccount_Num will not uniquely determine other attributes.

Question 2

The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?

.

Correct answer is :C

Question 3

Borrower Bank_Manager Loan_Amount Ramesh Sunderajan 10000.00 Suresh Ramgopal 5000.00 Mahesh Sunderajan 7000.00

What is the output of the following SQL query?

SELECT Count(*) FROM ( (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T );

.

Correct answer is :C

Question 4

.

Correct answer is :A

Question 5

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

.

Correct answer is :B

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.

Question 6

.

Correct answer is :C

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.

Question 7

.

Correct answer is :C

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.

Question 8

__A__, B) and R2 (

__C__, D) 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?

.

Correct answer is :A

Solution :

Since B is a foreign key referring C,values under B will be subset of values under C ( π

_{B}(r1 ) ⊆ π

_{C}(r2) => π

_{B}(r1) - π

_{C}(r2) = ∅ )

Question 9

T1 : read (P) ;

read (Q) ;

if P = 0 then Q : = Q + 1 ;

write (Q).

T2 : read (Q) ;

read (P)

if Q = 0 then P : = P + 1 ;

write (P).

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

.

Correct answer is :B

Question 10

How many tuples does the result of the following SQL query contain?

SELECT A.Id

FROM A

WHERE A.Age > ALL(SELECT B.Age

FROM B

WHERE B.Name = ‘Arun’)

.

Correct answer is :B

Solution :

As the result of subquery is an empty table, ‘>ALL’ comparison is true . Therefore, all the three row id’s of A will be selected from table A.

Question 11

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of A.

(A U B) <>

_{A.id>40 ∨ C.id <15 }C

.

Correct answer is :A

Question 12

.

Correct answer is :C

Solution :

Clustered index is built on ordering non key field and hence if the index is clustered then the data records of the file are organized in the same order as the data entries of the index.

Question 13

Students(rollno: integer, sname: string)

Courses(courseno: integer, cname: string)

Registration(rollno: integer, courseno; integer, percent: real)

Which of the following queries are equivalent to this query in English?

“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname

FROM Students as S, Registration as R

WHERE R.rollno=S.rollno AND R.Courseno=107 AND R.percent>90

(II) πsname (σ

_{courseno =107 }∧ percent> 90 (Registration Students))

(III) {T | ∃S ∈ Students, ∃R ∈ Registration (S.rol ln o= R.rol ln o ∧ R.courseno =107 ∧ R.percent > 90 ∧ T.sname = S.name)}

(IV) {

_{R}∃ R

_{P}(

_{N}> ∈ Students ∧ < S

_{R},107,R

_{P}>∈ Registration ∧ R

_{P}> 90 )}

.

Correct answer is :A

Solution :

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.

Question 14

How many candidate keys does the relation R have?

.

Correct answer is :B

Solution :

Candidate keys are AD, BD, ED and FD

Question 15

The relation R is

.

Correct answer is :A

Solution :

A->BC,B->CFH and F->EG are partial dependencies. Hence it is in 1NF but not in 2NF