Category: Database Management System Objective Questions

Database Multiple Choice Questions on “Snapshot Isolation”.

1. Snapshot isolation is a particular type of ______________ scheme.
a) Concurrency-control
b) Concurrency-allowance
c) Redirection
d) Repetition-allowance

Answer: a
Clarification: It has gained wide acceptance in commercial and open-source systems, including Oracle, PostgreSQL, and SQL Server.

2. Snapshot isolation is used to give
a) Transaction a snapshot of the database
b) Database a snapshot of the transaction
c) Database a snapshot of committed values in the transaction
d) Transaction a snapshot of the database and Database a snapshot of committed values in the transaction

Answer: d
Clarification: The data values in the snapshot consist only of values written by committed transactions.

3. Lost update problem is
a) Second update overwrites the first
b) First update overwrites the second
c) The updates are lost due to conflicting problem
d) None of the mentioned

Answer: a
Clarification: Lost update problem has to be resolved.

4. Under first updater wins the system uses a __________ mechanism that applies only to updates.
a) Close
b) Read
c) Locking
d) Beat

Answer: c
Clarification: Reads are unaffected by this, since they do not obtain locks.

5. When a transaction Ti attempts to update a data item, it requests a _________ on that data item.
a) Read lock
b) Update lock
c) Write lock
d) Chain lock

Answer: c
Clarification: Reads are unaffected by this, since they do not obtain locks.

6. Each of a pair of transactions has read data that is written by the other, but there is no data written by both transactions, is referred to as
a) Read skew
b) Update skew
c) Write lock
d) None of the mentioned

Answer: d
Clarification: Write skew is the issue addressed here.

7. An application developer can guard against certain snapshot anomalies by appending a ______ clause to the SQL select query.
a) For update
b) For read
c) For write
d) None of the mentioned

Answer: a
Clarification: Adding the for update clause causes the system to treat data that are read as if they had been updated for purposes of concurrency control.

8. Evaluate the CREATE TABLE statement:

CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));

Which statement is true regarding the PROD_ID_PK constraint?
a) It would be created only if a unique index is manually created first
b) It would be created and would use an automatically created unique index
c) It would be created and would use an automatically created no unique index
d) It would be created and remains in a disabled state because no index is specified in the command

Answer: b
Clarification: Syntax: create table table_name(name constraint).

9. Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
a) 1
b) 10
c) 100
d) an error

Answer: a
Clarification: Sequence is used to generate a series of values.

10. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
a) To find the groups forming the subtotal in a row
b) To create group-wise grand totals for the groups specified within a GROUP BY clause
c) To create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from
right to left for calculating the subtotals
d) To create a grouping for expressions or columns specified within a GROUP BY clause in all possible
directions, which is cross-tabular report for calculating the subtotals

Answer: c
Clarification: Sequence is used to generate a series of values.

Database MCQs on “Relational Query Operations and Relational Operators”.

1. Using which language can a user request information from a database?
a) Query
b) Relational
c) Structural
d) Compiler

Answer: a
Clarification: Query language is a method through which the database entries can be accessed.

2. Student(ID, name, dept name, tot_cred)
In this query which attributes form the primary key?
a) Name
b) Dept
c) Tot_cred
d) ID

Answer: d
Clarification: The attributes name, dept and tot_cred can have same values unlike ID.

3. Which one of the following is a procedural language?
a) Domain relational calculus
b) Tuple relational calculus
c) Relational algebra
d) Query language

Answer: c
Clarification: Domain and Tuple relational calculus are non-procedural language. Query language is a method through which database entries can be accessed.

4. The_____ operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a single tuple.
a) Select
b) Join
c) Union
d) Intersection

Answer: b
Clarification: Join finds the common tuple in the relations and combines it.

5. The result which operation contains all pairs of tuples from the two relations, regardless of whether their attribute values match.
a) Join
b) Cartesian product
c) Intersection
d) Set difference

Answer: b
Clarification: Cartesian product is the multiplication of all the values in the attributes.

6. The _______operation performs a set union of two “similarly structured” tables
a) Union
b) Join
c) Product
d) Intersect

Answer: a
Clarification: Union just combines all the values of relations of same attributes.

7. The most commonly used operation in relational algebra for projecting a set of tuple from a relation is
a) Join
b) Projection
c) Select
d) Union

Answer: c
Clarification: Select is used to view the tuples of the relation with or without some constraints.

8. The _______ operator takes the results of two queries and returns only rows that appear in both result sets.
a) Union
b) Intersect
c) Difference
d) Projection

Answer: b
Clarification: The union operator gives the result which is the union of two queries and difference is the one where query which is not a part of second query.

9. A ________ is a pictorial depiction of the schema of a database that shows the relations in the database, their attributes, and primary keys and foreign keys.
a) Schema diagram
b) Relational algebra
c) Database diagram
d) Schema flow

Answer: a
Clarification: None.

10. The _________ provides a set of operations that take one or more relations as input and return a relation as an output.
a) Schematic representation
b) Relational algebra
c) Scheme diagram
d) Relation flow

Answer: b

Database Multiple Choice Questions on “Functions and Procedures”.

1.

Create function dept count(dept_name varchar(20))
begin
declare d count integer;
select count(*) into d count
from instructor
where instructor.dept_name= dept_name
return d count;
end

Find the error in the the above statement.
a) Return type missing
b) Dept_name is mismatched
c) Reference relation is not mentioned
d) All of the mentioned

Answer: a
Clarification: Return integer should be given after create function for this particular function.

2. For the function created in Question 1, which of the following is a proper select statement ?
a)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT() > 12;

b)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT(dept name) > 12;

c)

SELECT dept name, budget
WHERE dept COUNT(dept name) > 12;

d)

SELECT dept name, budget
FROM instructor
WHERE dept COUNT(budget) > 12;

Answer: b
Clarification: The count of the dept_name must be checked for the displaying from instructor relation.

3. Which of the following is used to input the entry and give the result in a variable in a procedure?
a) Put and get
b) Get and put
c) Out and In
d) In and out

Answer: d
Clarification: Create procedure dept count proc(in dept name varchar(20), out d count integer). Here in and out refers to input and result of procedure.

4.

Create procedure dept_count proc(in dept name varchar(20),
out d count integer)
begin
select count(*) into d count
from instructor
where instructor.dept name= dept count proc.dept name
end

Which of the following is used to call the procedure given above ?
a)

Declare d_count integer;

b)

   Declare d_count integer;
   call dept_count proc(’Physics’, d_count);

c)

   Declare d_count integer;
   call dept_count proc(’Physics’);

d)

   Declare d_count; 
   call dept_count proc(’Physics’, d_count);

Answer: b
Clarification: Here the ‘Physics’ is in variable and d_count is out variable.

5. The format for compound statement is
a) Begin ……. end
b) Begin atomic……. end
c) Begin ……. repeat
d) Both Begin ……. end and Begin atomic……. end

Answer: d
Clarification: A compound statement is of the form begin . . . end, and it may contain multiple SQL statements between the begin and the end.A compound statement of the form begin atomic . . . end ensures that all the statements contained within it are executed as a single transaction.

6.

Repeat
sequence of statements;
__________________
end repeat

Fill in the correct option :
a) While Condition
b) Until variable
c) Until boolean expression
d) Until 0

Answer: c
Clarification: None.

7. Which of the following is the correct format for if statement?
a)

If boolean expression
then statement or compound statement
elseif boolean expression
then statement or compound statement
else statement or compound statement
end if

b)

If boolean expression
then statement or compound statement
elsif boolean expression
then statement or compound statement
else statement or compound statement
end if

c)

If boolean expression
then statement or compound statement
elif boolean expression
then statement or compound statement
else statement or compound statement
end if

d)

If boolean expression
then statement or compound statement
else 
 statement or compound statement
else statement or compound statement
end if

Answer: a
Clarification: The conditional statements supported by SQL include if-then-else statements by using this syntax. elif and elsif are not allowed.

8. A stored procedure in SQL is a___________
a) Block of functions
b) Group of Transact-SQL statements compiled into a single execution plan.
c) Group of distinct SQL statements.
d) None of the mentioned

Answer: b
Clarification: If it an atomic statement then the statements are in single transaction.

9. Temporary stored procedures are stored in _________ database.
a) Master
b) Model
c) User specific
d) Tempdb

Answer: d
Clarification: None.

10. Declare out of classroom seats condition

DECLARE exit handler FOR OUT OF classroom seats
BEGIN
SEQUENCE OF statements
END

The above statements are used for
a) Calling procedures
b) Handling Exception
c) Handling procedures
d) All of the mentioned

Answer: b
Clarification: The SQL procedural language also supports the signaling of exception conditions, and declaring of handlers that can handle the exception, as in this code.

Database Multiple Choice Questions on “Functional-Dependency Theory”.

1. We can use the following three rules to find logically implied functional dependencies. This collection of rules is called
a) Axioms
b) Armstrong’s axioms
c) Armstrong
d) Closure

Answer: b
Clarification: By applying these rules repeatedly, we can find all of F+, given F.

2. Which of the following is not Armstrong’s Axiom?
a) Reflexivity rule
b) Transitivity rule
c) Pseudotransitivity rule
d) Augmentation rule

Answer: c
Clarification: It is possible to use Armstrong’s axioms to prove that Pseudotransitivity rule is sound.

3. The relation employee(ID,name,street,Credit,street,city,salary) is decomposed into

employee1 (ID, name)
employee2 (name, street, city, salary)

This type of decomposition is called
a) Lossless decomposition
b) Lossless-join decomposition
c) All of the mentioned
d) None of the mentioned

Answer: d
Clarification: Lossy-join decomposition is the decomposition used here .

4. Inst_dept (ID, name, salary, dept name, building, budget) is decomposed into

instructor (ID, name, dept name, salary)
department (dept name, building, budget)

This comes under
a) Lossy-join decomposition
b) Lossy decomposition
c) Lossless-join decomposition
d) Both Lossy and Lossy-join decomposition

Answer: d
Clarification: Lossy-join decomposition is the decomposition used here .

5. There are two functional dependencies with the same set of attributes on the left side of the arrow:
A->BC
A->B
This can be combined as
a) A->BC
b) A->B
c) B->C
d) None of the mentioned

Answer: a
Clarification: This can be computed as the canonical cover.

6. Consider a relation R(A,B,C,D,E) with the following functional dependencies:

The number of superkeys of R is:
a) 2
b) 7
c) 10
d) 12

Answer: c
Clarification: A superkey is a combination of columns that uniquely identifies any row within a relational database management system (RDBMS) table.

7. Suppose we wish to find the ID’s of the employees that are managed by people who are managed by the employee with ID 123. Here are two possible queries:

I.SELECT ee.empID
  FROM Emps ee, Emps ff
  WHERE ee.mgrID = ff.empID AND ff.mgrID = 123;
II.SELECT empID
  FROM Emps 
  WHERE mgrID IN
  (SELECT empID FROM Emps WHERE mgrID = 123);

Which, if any, of the two queries above will correctly (in SQL2) get the desired set of employee ID’s?
a) Both I and II
b) I only
c) II only
d) Neither I nor I

Answer: a
Clarification: The query can be satisfied by any of the two options.

8. Suppose relation R(A,B) currently has tuples {(1,2), (1,3), (3,4)} and relation S(B,C) currently has {(2,5), (4,6), (7,8)}. Then the number of tuples in the result of the SQL query:

<i>SELECT *
	FROM R NATURAL OUTER JOIN S; </i>IS:

a) 2
b) 4
c) 6
d) None of the mentioned

Answer: a
Clarification: The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with same name of associate tables will appear once only.

9. Suppose now that R(A,B) and S(A,B) are two relations with r and s tuples, respectively (again, not necessarily distinct). If m is the number of (not necessarily distinct) tuples in the result of the SQL query:

	R intersect S;

Then which of the following is the most restrictive, correct condition on the value of m?
a) m = min(r,s)
b) 0 <= m <= r + s
c) min(r,s) <= m <= max(r,s)
d) 0 <= m <= min(r,s)

Answer: d
Clarification: The value of m must lie between the min value of r and s and 0.

10. Suppose relation R(A,B,C,D,E) has the following functional dependencies:

A -> B
B -> C
BC -> A
A -> D
E -> A
D -> E

Which of the following is not a key?
a) A
b) E
c) B, C
d) D

Answer: c
Clarification: Here the keys are not formed by B and C.

Database Multiple Choice Questions on “File Organisations”.

1. Which level of RAID refers to disk mirroring with block striping?
a) RAID level 1
b) RAID level 2
c) RAID level 0
d) RAID level 3

Answer: a
Clarification: RAID (redundant array of independent disks) is a way of storing the same data in different places (thus, redundantly) on multiple hard disks.

2. A unit of storage that can store one or more records in a hash file organization is denoted as
a) Buckets
b) Disk pages
c) Blocks
d) Nodes

Answer: a
Clarification: A unit of storage that can store one or more records in a hash file organization is denoted as buckets.

3. The file organization which allows us to read records that would satisfy the join condition by using one block read is
a) Heap file organization
b) Sequential file organization
c) Clustering file organization
d) Hash file organization

Answer: c
Clarification: All systems in the cluster share a common file structure via NFS, but not all disks are mounted on all other systems.

4. What are the correct features of a distributed database?
a) Is always connected to the internet
b) Always requires more than three machines
c) Users see the data in one global schema.
d) Have to specify the physical location of the data when an update is done

Answer: c
Clarification: Users see the data in one global schema.

5. Each tablespace in an Oracle database consists of one or more files called
a) Files
b) name space
c) datafiles
d) PFILE

Answer: c
Clarification: A data file is a computer file which stores data to use by a computer application or system.

6. The management information system (MIS) structure with one main computer system is called a
a) Hierarchical MIS structure
b) Distributed MIS structure
c) Centralized MIS structure
d) Decentralized MIS structure

Answer: c
Clarification: Structure of MIS may be understood by looking at the physical components of the information system in an organization.

7. A top-to-bottom relationship among the items in a database is established by a
a) Hierarchical schema
b) Network schema
c) Relational schema
d) All of the mentioned

Answer: a
Clarification: A hierarchical database model is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships.

8. Choose the RDBMS which supports full fledged client server application development
a) dBase V
b) Oracle 7.1
c) FoxPro 2.1
d) Ingress

Answer: b
Clarification: RDBMS is Relational Database Management System.

9. One approach to standardization storing of data?
a) MIS
b) Structured programming
c) CODASYL specification
d) None of the mentioned

Answer: c
Clarification: CODASYL is an acronym for “Conference on Data Systems Languages”.

10. The highest level in the hierarchy of data organization is called
a) Data bank
b) Data base
c) Data file
d) Data record

Answer: b
Clarification: Database is a collection of all tables which contains the data in form of fields.

Database Multiple Choice Questions on “Materialized Views”.

1. Which normal form is considered adequate for normal relational database design?
a) 2NF
b) 5NF
c) 4NF
d) 3NF

Answer: d
Clarification: A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.

2. Consider a schema R(A, B, C, D) and functional dependencies A -> B and C -> D. Then the decomposition of R into R1 (A, B) and R2(C, D) is
a) dependency preserving and lossless join
b) lossless join but not dependency preserving
c) dependency preserving but not lossless join
d) not dependency preserving and not lossless join

Answer: d
Clarification: While decomposing a relational table we must verify the following properties:
i) Dependency Preserving Property
ii) Lossless-Join Property.

3. Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
a) Zero
b) More than zero but less than that of an equivalent 3NF decomposition
c) Proportional to the size of F+
d) Indeterminate

Answer: b
Clarification: Redundancy in BCNF is low when compared to 3NF.

4. Which one of the following statements about normal forms is FALSE?
a) BCNF is stricter than 3NF
b) Lossless, dependency-preserving decomposition into 3NF is always possible
c) Lossless, dependency-preserving decomposition into BCNF is always possible
d) Any relation with two attributes is in BCNF

Answer: c
Clarification: Achieving Lossless and dependency-preserving decomposition property into BCNF is difficult.

5. A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:

F1->F3
F2->F4
(F1,F2)->F5

in terms of normalization, this table is in
a) 1NF
b) 2NF
c) 3NF
d) None of the mentioned

Answer: a
Clarification: Since the primary key is not given we have to derive the primary key of the table. Using the closure set of attributes we get the primary key as (F1,F2). From functional dependencies, “F1->F3, F2->F4”, we can see that there is partial functional dependency therefore it is not in 1NF. Hence the table is in 1NF.

6. Which of the following is TRUE?
a) Every relation in 2NF 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: c
Clarification: A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.

7. Consider the following functional dependencies in a database.

   Date_of_Birth->Age            Age->Eligibility 
   Name->Roll_number             Roll_number->Name 
   Course_number->Course_name    Course_number->Instructor 
   (Roll_number, Course_number)->Grade

The relation (Roll_number, Name, Date_of_birth, Age) is
a) In second normal form but not in third normal form
b) In third normal form but not in BCNF
c) In BCNF
d) None of the mentioned

Answer: d
Clarification: For the given relation only some of the above FDs are applicable. The applicable FDs are given below:
Date_of_Birth->Age
Name->Roll_number
Roll_number->Name
Finding the closure set of attributes we get the candidate keys:(Roll_number,Date_of_Birth), and (Name,Date_of_Birth) .
On selecting any one of the candidate key we can see that the FD Date_of_Birth->Age is a partial dependency. Hence the relation is in 1NF.

8. The relation schema Student_Performance (name, courseNo, rollNo, grade) has the following FDs:

name,courseNo->grade
rollNo,courseNo->grade
name->rollNo
rollNo->name

The highest normal form of this relation scheme is
a) 2NF
b) 3NF
c) BCNF
d) 4NF

Answer: b
Clarification: A super key is a combination of prime attributes and one or more non-prime key attribute(s). It also uniquely identifies a record in a table. Primary key can be defined as super key with minimal attributes.

9. The relation EMPDT1 is defined with attributes empcode(unique), name, street, city, state, and pincode. For any pincode, there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms EMPDT1 is a relation in
a) 1NF only
b) 2NF and hence also in 1NF
c) 3NF and hence also in 2NF and 1NF
d) BCNF and hence also in 3NF, 2NF and 1NF

Answer: b
Clarification: Empcode is unique, therefore it is the primary key. Since the primary key consists of a single attribute there will be no partial dependency, hence the relation is in 2NF.
From the question we get the FDs as below:
pincode -> city, state
street,city,state -> pincode
From the FDs we can see that there are transitive dependencies, hence the table is not in 3NF.

10. Which one of the following statements is FALSE?
a) Any relation with two attributes is in BCNF
b) A relation in which every key has only one attribute is in 2NF
c) A prime attribute can be transitively dependent on a key in a 3 NF relation
d) A prime attribute can be transitively dependent on a key in a BCNF relation

Answer: d
Clarification: A table is in 3NF if and only if, for each of its functional dependencies X -> A, at least one of the following conditions holds:
* X contains A (that is, X -> A is trivial functional dependency), or
* X is a superkey, or
* A should be prime attribute.