DBMS Multiple Choice Questions with Answers Set-6

This post contains a database management system related to 20 multiple-choice questions (DBMS SET-6) that help you to go through the subject and prepare you for DBMS related questions in competitive exams

DBMS MCQ Test Series Set-6

Total Number of Questions: 20
Each question carries equal marks i.e 1
No Negative Marking

Page 1 of 2

1. Which of the following is true about data?

A.It represents raw facts.

B.You cannot enter data in a computer in various formats.

C.It is collected and stored for future reference.

D.Text, audio, video and images are examples of various forms of data.

E.None of these

2. Consider the following statements S1 and S2 about the relational data model:
S1: A relation scheme can have at most one foreign key.
S2: A foreign key in a relation scheme R cannot be used to refer to tuples of R.
Which one of the following choices is correct?

A.Both S1 and S2 are true.

B.S1 is true and S2 is false.

C.S1 is false and S2 is true.

D.Both S1 and S2 are false.

Consider the following two statements about database transaction schedules:

A strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.
Timestamp-ordering concurrency control protocol with Thomas' Write Rule can generate view serializable schedules that are not conflict serializable.

Which of the above statements is/are TRUE?

A.1 only

B.2 only

C.Both 1 and 2

D.Neither 1 nor 2

4. The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key :

empAge(empNo, age)Consider the following relational algebra expression:

$\Pi _{empNo}\left (emp Age \infty _{age> age1}P_{empNO1.age1}(empAge)\right )$

What does the above expression generate?

A.Employee numbers of only those employees whose age is the maximum.

B.Employee numbers of only those employees whose age is more than the age of exactly one other employee.

C.Employee numbers of all employees whose age is not the minimum

D.Let Employee numbers of all employees whose age is the minimum.

5. Consider a relational database containing the following schemas.

Catalogue

sno	Pno	Cost
S1	P1	150
S1	P2	50
S1	P3	100
S2	P4	200
S2	P5	250
S3	P1	250
S3	P2	150
S3	P5	300
S3	P4	250

Suppliers

sno	sname	location
S1	M/s Royal furniture	Delhi
S2	M/s Balaji furniture	Banglore
S3	M/s Premium furniture	Chennai

Parts

pno	pname	part_spec
P1	Table	Wood
P2	Chair	Wood
P3	Table	Steel
P4	Almirah	Steel
P5	Almirah	Wood

The primary key of each table is indicated by underlining the constituent fields.

SELECT s.sno, s.sname

FROM Suppliers s, Catalogue c

WHERE s.sno=C.sno AND

Cost>(SELECT AVG (cost)

FROM Catalogue

WHERE pno=’P4’

GROUP BY pno);

The number of rows returned by the above SQL query is

A.4

B.5

C.0

D.2

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?

A.Diamonds with double/hold border

B.Rectangles with double/bold border

C.Ovals with double/hold border

D.Ovals that contain underlined identifiers

The relation scheme given below is used to store information about the employees of a company, where empld is the key and deptId indicates the department to which the employees is assigned. Each employee is assigned to exactly one department.
emp(empId, name, gender, salary, deptId)
Consider the following SQL query:
Select deptId, count(*)
From emp
Where gender=”female” and salary>(select avg(salary) from emp)
Group by deptId;
The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

A.employees in the department.

B.employees in the company.

C.female employees in the department

D.female employees in the company

8. Let $r_{i}(z)$ and $w_{i}(z)$ denote read and write operations respectively on a data item z by a transaction Consider the following two schedules.

$S_{1}:r_{1}(x)r_{1}(y)r_{2}(x)r_{2}(y)w_{2}(y)w_{1}(x)$

$S_{2}:r_{1}(x)r_{2}(x)r_{2}(y)w_{2}(y)r_{1}(y)w_{1}(x)$

Which one of the following options is correct?

A.$S_{1}$ is conflict serializable, and $S_{2}$ is not conflict serializable.

B.$S_{1}$ is not conflict serializable, and $S_{2}$ is conflict serializable.

C.Both $S_{1}$ and $S_{2}$ are conflict serializable

D.Neither $S_{1}$ or $S_{2}$ are conflict serializable

9. let S be the following schedule of operations of three transactions $T_1,T_2 and T_4$ in a relational database system:
$R_2 (Y),R_1 (X),R_3 (Z),R_1 (Y),W_1 (X),R_2 (Z),W_2 (Y),R_3 (X),W_3 (Z)$
Consider the statements P and Q below:
P:S is conflict-serialzable.
Q: if $T_3$ commits before $T_1$ finishes, S is recoverable.
Which one of the following choices is correct?

A.Both P and Q are true.

B.P is true and Q is false.

C.P is false and Q is true

D.Both P and Q are false.

10. Consider the relation R(P, Q, S, T, X, Y, Z, W) with the following functional dependencies.

PQ\rightarrow X; P\rightarrow YX; Q\rightarrow Y; Y\rightarrow ZW

Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.

$D_1$ : R = [P,Q, S, T]; (P,T,X); (Q,Y); (Y,Z,W)]

$D_2$ : R = [P,Q,S]; (T,X); (Q,Y); (Y,Z,W)]

Which one of the following options is correct?

A.$D_1$ is a lossless decomposition, but $D_2$ is a lossy decomposition.

B.$D_1$ is a lossy decomposition, but $D_2$ is a lossless decomposition.

C.Both $D_1$ and $D_2$ are lossy decomposition

D.Both $D_1$ and $D_2$ are lossless decomposition

Page 2 of 2

11.

Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?

A.R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.

B.R has a nontrivial functional dependency X→A, where X is not s superkey and A is a non–prime attribute and X is not a proper subset of any key.

C.R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.

D.A cell in E holds a set instead of an atomic value.

12. Which of the following is/are the advantage(s) of a DBMS?

A.DBMS reduces data redundancy

B.DBMS reduces data inconsistency

C.DBMS allows data sharing

D.DBMS ensures data security

E.All of these

13. Which of the following is created by a user to retrieve specific data from tables present in a database?

A.Queries

B.Forms

C.Wizard

D.Forms and Wizard

E.None of these

14. Suppose the following functional dependencies hold on a relation U with attributes P, Q, R, S, and T:

$P\rightarrow QR$

$RS\rightarrow T$

Which of the following functional dependencies can be inferred from the above functional dependencies?

A.PS\rightarrowT

B.R\rightarrowT

C.P\rightarrowR

D.PS\rightarrowQ

15.

Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery.

Which of the following statements is/are correct?

A.The same undo and redo list will be used while recovering again.

B.The system cannot recover any further.

C.All the transactions that are already undone and redone will not be recovered again.

D.The database will become inconsistent.

16. Which of the following statements in SQL suspends the execution of a stored procedure or transaction for a specified time?

A.BREAK

B.GOTO

C.RETURN

D.WAITFOR

E.None of these

17. __________ enables the joining of two or more tables on the basis of a particular column value which is not equal to a specific column value in another table.

A.Natural Join

B.Outer Join

C.Equi-join

D.Non-equijoin

E.Left outer Join

18. Which of the following is correct for the SELECT statement that includes views of multiple tables in the top-level FROM clause?

A.Datable Join

B.Join

C.Updatable Join

D.Inner Join

E.Outer Join

19.

A data file consisting of 1,50,000 student records is stored on a hard disk with a block size of 4096 bytes. The data file is sorted on the primary key Roll No. The size of a record pointer for this disk is 7 bytes. Each student record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file with records consisting of two fields, ANum value and the record pointer to the corresponding student record, is built and stored on the same disk. Assume that the records of the data file and index file are not split across disk blocks. The number of blocks in the index file is

A.700

B.648

C.720

D.698

20. A relation r(A, B) in a relational database has 1200 tuples. Attribute A has integer values ranging from 6 to 20, and attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed.

The estimated number of tuples in the output of σ_{(A>10)v(B=18)}(r) is

A.between 819 to 820

B.between 799 to 800

C.between 699 to 700

D.between to 899 to 900

DBMS MCQ Test Series Set-6

Total Number of Questions: 20

Each question carries equal marks i.e 1

No Negative Marking

Related