DBMS Interview Questions

 What is Data?

 Explain Normalization
 Differentiate between 3nf and BCNF.
 Levels of Data abstraction?
 What is DML ?
 How to join two tables?
 Explain Triggers?
 Expalin Group by Clause
 [Link] is a View.
 [Link] is index.
 [Link] is Normalisation?
 [Link]. between 3nf and Bcnf form?
 [Link] diff types of Joins?
 [Link] types of Keys?
 7. What is candidate key?
 8. What is trigger?
 9. What are different types of two phase lockings(2pl)?
 10. What is a deadlock?
 11. What are triggers?
 12. What are different categories of data models?
 13. What is schema?
 14. What are types of schema?
 15. What is Data independency?
 16. What are different DBMS languages?
 17. What are different types of DBMS?
 18. What is an entity?
 19. What are attributes?
 What is schema ?
 A schema is collection of database objects of a user.
 What are advantages of DBMS over traditional file based systems?
Ans: Database management systems were developed to handle the following difficulties of typical
file-processing systems supported by conventional operating systems.
[Link] redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation – multiple files and formats
4. Integrity problems
5. Atomicity of updates
[Link] access by multiple users
7. Security problems
Source: [Link]
 What are super, primary, candidate and foreign keys?
Ans: A superkey is a set of attributes of a relation schema upon which all attributes of the schema
are functionally dependent. No two rows can have the same value of super key attributes.
A Candidate key is minimal superkey, i.e., no proper subset of Candidate key attributes can be a
superkey.
A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important
and becomes the primary key. There cannot be more that one primary keys in a table.
Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another
table. See this for an example.
 What is the difference between primary key and unique constraints?
Ans: Primary key cannot have NULL value, the unique constraints can have NULL values. There is
only one primary key in a table, but there can be multiple unique constrains.
 What is database normalization?
Ans: It is a process of analyzing the given relation schemas based on their functional dependencies
and primary keys to achieve the following desirable properties:
1) Minimizing Redundancy
2) Minimizing the Insertion, Deletion, And Update Anomalies
Relation schemas that do not meet the properties are decomposed into smaller relation schemas that
could meet desirable properties.
Source: [Link]
 What is the difference between having and where clause?
Ans: HAVING is used to specify a condition for a group or an aggregate function used in select
statement. The WHERE clause selects before grouping. The HAVING clause selects rows after
grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.
(See this for examples)
 What is a view in SQL? How to create one
Ans: A view is a virtual table based on the result-set of an SQL statement. We can create using
create view syntax.
 CREATE VIEW view_name AS
 SELECT column_name(s)
 FROM table_name
 WHERE condition
 What are the uses of view?
1. Views can represent a subset of the data contained in a table; consequently, a view can limit the
degree of exposure of the underlying tables to the outer world: a given user may have permission to
query the view, while denied access to the rest of the base table.
2. Views can join and simplify multiple tables into a single virtual table
3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.)
and presents the calculated results as part of the data
4. Views can hide the complexity of data; for example a view could appear as Sales2000 or
Sales2001, transparently partitioning the actual underlying table
5. Views take very little space to store; the database contains only the definition of a view, not a
copy of all the data which it presentsv.
6. Depending on the SQL engine used, views can provide extra security
Source: Wiki Page
 What is a Trigger?
Ans: A Trigger is a code that associated with insert, update or delete operations. The code is
executed automatically whenever the associated query is executed on a table. Triggers can be useful
to maintain integrity in database.
 What is a stored procedure?
Ans: A stored procedure is like a function that contains a set of operations compiled together. It
contains a set of operations that are commonly used in an application to do some common database
tasks.
 What is the difference between Trigger and Stored Procedure?
Ans: Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with
queries.
 What is a transaction? What are ACID properties?
Ans: A Database Transaction is a set of database operations that must be treated as whole, means
either all operations are executed or none of them.
An example can be bank transaction from one account to another account. Either both debit and
credit operations must be executed or none of them.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that
database transactions are processed reliably.
 What are indexes?
Ans: A database index is a data structure that improves the speed of data retrieval operations on a
database table at the cost of additional writes and the use of more storage space to maintain the extra
copy of data.
Data can be stored only in one order on disk. To support faster access according to different values,
faster search like binary search for different values is desired, For this purpose, indexes are created
on tables. These indexes need extra space on disk, but they allow faster search according to different
frequently searched values.
  What are clustered and non-clustered Indexes?
Ans: Clustered indexes is the index according to which data is physically stored on disk. Therefore,
only one clustered index can be created on a given database table.
Non-clustered indexes don’t define physical ordering of data, but logical ordering. Typically, a tree
is created whose leaf point to disk records. B-Tree or B+ tree are used for this purpose.
 We will soon be covering more DBMS questions. Please write comments if you find anything
incorrect, or you want to share more information about the topic discussed above.

. What is database?
A database is a logically coherent collection of data with some inherent meaning, representing some aspect
of real world and which is designed, built and populated with data for a specific purpose.
2. What is DBMS?
It is a collection of programs that enables user to create and maintain a database. In other words it is
general-purpose software that provides the users with the processes of defining, constructing and
manipulating the database for various applications.
3. What is a Database system?
The database and DBMS software together is called as Database system.
4. What are the advantages of DBMS?
1. Redundancy is controlled.
2. Unauthorised access is restricted.
3. Providing multiple user interfaces.
4. Enforcing integrity constraints.
5. Providing backup and recovery.
5. What are the disadvantage in File Processing System?
1. Data redundancy and inconsistency.
2. Difficult in accessing data.
3. Data isolation.
4. Data integrity.
5. Concurrent access is not possible.
6. Security Problems.
6. Describe the three levels of data abstraction?
The are three levels of abstraction:
1. Physical level: The lowest level of abstraction describes how data are stored.
2. Logical level: The next higher level of abstraction, describes what data are stored in database and what
relationship among those data.
3. View level: The highest level of abstraction describes only part of entire database.
 7. Define the “integrity rules”?
There are two Integrity rules.
1. Entity Integrity: States that “Primary key cannot have NULL value”
2. Referential Integrity: States that “Foreign Key can be either a NULL value or should be Primary Key
value of other relation.
8. What is extension and intension?
1. Extension: It is the number of tuples present in a table at any instance. This is time dependent.
2. Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.
9. What is System R? What are its two major subsystems?
System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a
prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be
used in a real life environment to solve real life problems, with performance at least comparable to that of
existing system.
Its two subsystems are
1. Research Storage
2. System Relational Data System.
10. How is the data structure of System R different from the relational structure?
Unlike Relational systems in System R
1. Domains are not supported
2. Enforcement of candidate key uniqueness is optional
3. Enforcement of entity integrity is optional
4. Referential integrity is not enforced
11. What is Data Independence?
Data independence means that “the application is independent of the storage structure and access strategy
of data”. In other words, The ability to modify the schema definition in one level should not affect the
schema definition in the next higher level.
Two types of Data Independence:
1. Physical Data Independence: Modification in physical level should not affect the logical level.
2. Logical Data Independence: Modification in logical level should affect the view level.
NOTE: Logical Data Independence is more difficult to achieve 12. What is a view? How it is related to
data independence?
A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is
instead derived from one or more underlying base table. In other words, there is no stored file that direct
represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the
effects of restructuring and growth in the database. Hence accounts for logical data independence.
13. What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and constraints.
14. What is E-R model?
This data model is based on real world that consists of basic objects called entities and of relationship
among these objects. Entities are described in a database by a set of attributes.
15. What is Object Oriented model?
This model is based on collection of objects. An object contains values stored in instance variables with in
the object. An object also contains bodies of code that operate on the object. These bodies of code are
called methods. Objects that contain same types of values and the same methods are grouped together into
classes.
16. What is an Entity?
It is a ‘thing’ in the real world with an independent existence.
17. What is an Entity type?
It is a collection (set) of entities that have same attributes.
18. What is an Entity set?
It is a collection of all entities of particular entity type in the database.
19. What is an Extension of entity type?
The collections of entities of a particular entity type are grouped together into an entity set.
20. What is Weak Entity set?
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of
its partial key and primary key of its parent entity, then it is said to be Weak Entity set.
21. What is an attribute?
It is a particular property, which describes the entity.
22. What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of
attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set
tuples (t1, t2, t3, …, tn). Each tuple is an ordered list of n-values t=(v1,v2, …, vn).
23. What is degree of a Relation?
It is the number of attribute of its relation schema.
24. What is Relationship?
It is an association among two or more entities.
25. What is Relationship set?
The collection (or set) of similar relationships.
26. What is Relationship type?
Relationship type defines a set of associations or a relationship set among a given set of entity types.
27. What is degree of Relationship type?
It is the number of entity type participating.
28. What is DDL (Data Definition Language)?
A data base schema is specifies by a set of definitions expressed by a special language called DDL.
29. What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
30. What is SDL (Storage Definition Language)?
 This language is to specify the internal schema. This language may specify the mapping between two
schemas.
31. What is Data Storage – Definition Language?
The storage structures and access methods used by database system are specified by a set of definition in a
special type of DDL called data storage-definition language.
32. What is DML (Data Manipulation Language)?
This language that enable user to access or manipulate data as organised by appropriate data model.
1. Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those
data.
2. Non-Procedural DML or High level: DML requires a user to specify what data are needed without
specifying how to get those data.
33. What is DML Compiler?
It translates DML statements in a query language into low-level instruction that the query evaluation engine
can understand.
34. What is Query evaluation engine?
It executes low-level instruction generated by compiler.
35. What is DDL Interpreter?
It interprets DDL statements and record them in tables containing metadata.
36. What is Record-at-a-time?
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve
of a record is said to be Record-at-a-time.
37. What is Set-at-a-time or Set-oriented?
The High level or Non-procedural DML can specify and retrieve many records in a single DML statement.
This retrieve of a record is said to be Set-at-a-time or Set-oriented.
38. What is Relational Algebra?
It is procedural query language. It consists of a set of operations that take one or two relations as input and
produce a new relation.

1. What is DBMS ?
Database management system is a collection of programs that enables user to store , retrieve , update and
delete information from a database .

3. What is SQL ?Structured Query Language(SQL) is a language designed specifically for communicating
with databases. SQL is an ANSI (American National Standards Institute) standard .

4. What are the different type of Sql's?

Frequently asked SQL Interview Questions

1. DDL – Data Definition Language

DDL is used to define the structure that holds the data. For example. table

2. DML – Data Manipulation Language

DML is used for manipulation of the data itself. Typical operations are Insert,Delete,Update and retrieving
the data from the table 3. DCL– Data Control Language DCL is used to control the visibility of data like
granting database access and set privileges to create table etc.

5. What are the Advantages of SQL

1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS
supports SQL, so learning this one language will enable programmer to interact with any database like
ORACLE, SQL ,MYSQL etc.
2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren't that
many of them.
3. SQL is actually a very powerful language and by using its language elements you can perform very
complex and sophisticated database operations.
6. what is a field in a database ?
A field is an area within a record reserved for a specific piece of data. Examples: Employee Name ,
Employee ID etc
7. What is a Record in a database ?
A record is the collection of values / fields of a specific entity: i.e. a Employee , Salary etc
8. What is a Table in a database ?
A table is a collection of records of a specific type. For example, employee table , salary table etc

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables.
For example, if we have the department ID of each employee, then we can use this department ID of the
employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data
from the source data sets. Union does not require any condition for joining. For example, if you have 2
employee tables with same structure, you can UNION them to create one result set that will contain all the
employees from both of the tables.

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation
returns only the unique records from the resulting data set whereas UNION ALL will return all the rows,
even if one or more rows are duplicated to each other.

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is,
WHERE clause can only be applied on a static non-aggregated column whereas we will need to use
HAVING for aggregated columns.

How to select first 5 records from a table?

This question, often asked in many interviews, does not make any sense to me. The problem here is how do
you define which record is first and which is second. Which record is retrieved first from the database is not
deterministic. It depends on many uncontrollable factors such as how database works at that moment of
execution etc. So the question should really be – “how to select any 5 records from the table?” But
whatever it is, here is the solution:
In Oracle,

SELECT *
FROM EMP
WHERE ROWNUM <= 5;
6. Define Primary Key?
The primary key is the columns used to uniquely identify each row of a
table.
A table can have only one primary key.
No primary key value can appear in more than one row in the table.
7. Define Unique Key?
Unique key is a one or more column that must be unique for each row of the
table.
It is similar to primary key. Primary key column will not accept a null.
Whereas the unique key column will accept a null values.
8. Compare and contrast TRUNCATE and DELETE for a table?
Both the truncate and delete command have the desired outcome of getting
rid of all the rows in a table. The difference between the two is that the
truncate command is a DDL operation and just moves the high water mark
and produces a now rollback. The delete command, on the other hand, is a
DML operation, which will produce a rollback and thus take longer to
complete.
9. What is cursors?
Cursor is a database object used by applications to manipulate data in a set
on a row-by-row basis, instead of the typical SQL commands that operate on
all the rows in the set at one time.

10. Define Foreign Key?

A foreign Key is a combination of columns with value is based on the
primary key values from another table. A foreign key constraint also known
as Referential Integrity Constraint.
11. Define SubQuery?
Nesting of Queries one within the other is called as a Subquery.
A table can have only one primary key.
12. What are the different types of subquery?
Single row subquery
Multiple row subquery
Correlated row subquery
13. What are the different types of replication?
The SQL Server 2000-supported replication types are as follows

Transactional
Snapshot
Merge
DISCUSS
14. What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can
accept 0 or more parameters and return a single scalar data value or a table
data type.
DISCUSS
15. Define Self Join?
Self join means joining one table with itself.
The self join can be viewed as a join of two copies of the same table.
16. Define Joins?
A Join combines columns and data from two or more tables (and in rare
cases, of one table with itself).
DISCUSS
17. What are the types of Joins?
Equi joins
Cartesian Joins
 Outer Joins
Self Joins.
18. Define Equi Joins?
A Equi Join is a join in which the join comparison operator is an equality.
When two tables are joined together using equality or values in one or more
columns, they make an Equi Join.

19. Define Cartesian Join?

Joining two tables without a whereclause produces a Cartesian join which
combines every row in one table with every row in another table.

20. What are tree SQL keywords used to change or set someone's permissions?
GRANT, DENY, and REVOKE

Q.1 Which of the following relational algebra operations do not require the participating tables to be
union-compatible?
(A) Union (B) Intersection
(C) Difference (D) Join

Ans: (D)

Q.2 Which of the following is not a property of transactions?

(A) Atomicity (B) Concurrency
(C) Isolation (D) Durability

Ans: (B)

Q.3 Relational Algebra does not have

(A) Selection operator. (B) Projection operator.
(C) Aggregation operators. (D) Division operator.

Ans: (C )

Q.4 Checkpoints are a part of

(A) Recovery measures. (B) Security measures.
(C ) Concurrency measures. (D) Authorization measures.

Ans: (A)

Q.5 Tree structures are used to store data in

(A) Network model. (B) Relational model.
(C) Hierarchical model. (D) File based system.

Ans: (C )

Q.6 The language that requires a user to specify the data to be retrieved without specifying
exactly how to get it is
 (A) Procedural DML. (B) Non-Procedural DML.
(C) Procedural DDL. (D) Non-Procedural DDL.

Ans: (B)

Q.7 Precedence graphs help to find a

(A) Serializable schedule. (B) Recoverable schedule.
(C) Deadlock free schedule. (D) Cascadeless schedule.

Ans: (A)

Q.8 The rule that a value of a foreign key must appear as a value of some specific table is called a
(A) Referential constraint. (B) Index.
(C) Integrity constraint. (D) Functional dependency.

Ans: (A) The rule that a value of a foreign key must appear as a value of some specific table
is called a referential constraint. (Referential integrity constraint is concerned with foreign key)

Q.9 The clause in SQL that specifies that the query result should be sorted in ascending or descending
order based on the values of one or more columns is
(A) View (B) Order by
(C) Group by (D) Having

Ans: (B) The clause in SQL that specifies that the query result should be sorted in ascending or
descending order based on the values of one or more columns is ORDER BY. (ORDER BY clause
is used to arrange the result of the SELECT statement)

Q.10 What is a disjoint less constraint?

(A) It requires that an entity belongs to no more than one level entity set.
(B) The same entity may belong to more than one level.
(C) The database must contain an unmatched foreign key value.
(D) An entity can be joined with another entity in the same level entity set.

Ans: (A) Disjoint less constraint requires that an entity belongs to no more than one level entity set.
(Disjoint less constraint means that an entity can be a member of at most one of the subclasses of
the specialization.)

Q.11 According to the levels of abstraction, the schema at the intermediate level is called
(A) Logical schema. (B) Physical schema.
(C) Subschema. (D) Super schema.

Ans: According to the levels of abstraction, the schema at the intermediate level is
called conceptual schema.
(Note: All the options given in the question are wrong.)

Q.12 It is an abstraction through which relationships are treated as higher level entities
(A) Generalization. (B) Specialization.
(C) Aggregation. (D) Inheritance.

Ans: (C ) It is an abstraction through which relationships are treated as higher level entities
Aggregation. (In ER diagram, aggregation is used to represent a relationship as an entity set.)
Q.13 A relation is in ____________ if an attribute of a composite key is dependent on an attribute of
other composite key.
(A) 2NF (B) 3NF
(C) BCNF (D) 1NF

Ans: (B) A relation is in 3 NF if an attribute of a composite key is dependent on an attribute of

other composite key. (If an attribute of a composite key is dependent on an attribute of other
composite key then the relation is not in BCNF, hence it has to be decomposed.)

Q.14 What is data integrity?

(A) It is the data contained in database that is non redundant.
(B) It is the data contained in database that is accurate and consistent.
(C) It is the data contained in database that is secured.
(D) It is the data contained in database that is shared.

Ans: (B) (Data integrity means that the data must be valid according to the given constraints.
Therefore, the data is accurate and consistent.)

Q.15 What are the desirable properties of a decomposition

(A) Partition constraint. (B) Dependency preservation.
(C) Redundancy. (D) Security.

Ans: (B) What are the desirable properties of a decomposition – dependency preserving.
(Lossless join and dependency preserving are the two goals of the decomposition.)

Q.16 In an E-R diagram double lines indicate

(A) Total participation. (B) Multiple participation.
(C) Cardinality N. (D) None of the above.

Ans: (A)

Q.17 The operation which is not considered a basic operation of relational algebra is
(A) Join. (B) Selection.
(C) Union. (D) Cross product.

Ans: (A)

Q.18 Fifth Normal form is concerned with

(A) Functional dependency. (B) Multivalued
dependency.
(C) Join dependency. (D) Domain-key.

Ans: (C)

Q.19 Block-interleaved distributed parity is RAID level

(A) 2. (B) 3
(C) 4. (D) 5.

Ans: (D)

Q.20 Immediate database modification technique uses

 (A) Both undo and redo. (B) Undo but no redo.
(C) Redo but no undo. (D) Neither undo nor redo.

Ans: (A)

Q.21 In SQL the statement select * from R, S is equivalent to

(A) Select * from R natural join S. (B) Select * from R cross join S.
(C) Select * from R union join S. (D) Select * from R inner join S.

Ans: (B)

Q.22 Which of the following is not a consequence of concurrent operations?

(A) Lost update problem. (B) Update anomaly.
(C) Unrepeatable read. (D) Dirty read.

Ans: (B)

Q.23 As per equivalence rules for query transformation, selection operation distributes over
(A Union. (B) Intersection (C) Set difference. (D) All of the above.

Ans: (D)
Q.24 The metadata is created by the
(A) DML compiler (B) DML pre-processor
(C) DDL interpreter (D) Query interpreter

Ans: (C)

Q.25 When an E-R diagram is mapped to tables, the representation is redundant for
(A) weak entity sets (B) weak relationship sets
(C) strong entity sets (D) strong relationship sets

Ans: (B)

Q.26 When , then the cost of computing is

(A the same as R S (B) greater the R S
(C) less than R S (D) cannot say anything

Ans: (A)

Q.27 In SQL the word ‘natural’ can be used with

(A) inner join (B) full outer join
(C) right outer join (D) all of the above

Ans: (A)
.28 The default level of consistency in SQL is
(A) repeatable read (B) read committed
(C) read uncommitted (D) serializable

Ans: (D)

Q.29 If a transaction T has obtained an exclusive lock on item Q, then T can

 (A) read Q (B) write Q
(C) both read and write Q (D) write Q but not read Q

Ans: (C)

Q.30 Shadow paging has

(A) no redo (B) no undo
(C) redo but no undo (D) neither redo nor undo

Ans: (A)

Q.31 If the closure of an attribute set is the entire relation then the attribute set is a
(A) superkey (B) candidate key
(C) primary key (D) not a key

Ans: (A)

Q.32 DROP is a ______________ statement in SQL.

(A) Query (B) Embedded SQL
(C) DDL (D) DCL

Ans: (C)

Q.33 If two relations R and S are joined, then the non matching tuples of both R and S are ignored in
(A) left outer join (B) right outer join
(C) full outer join (D) inner join

Ans: (D)

Q.34 The keyword to eliminate duplicate rows from the query result in SQL is
(A) DISTINCT (B) NO DUPLICATE
(C) UNIQUE (D) None of the above

` Ans: (C)

Q.35 In 2NF
(A) No functional dependencies (FDs) exist.
(B) No multivalued dependencies (MVDs) exist.
(C) No partial FDs exist.
(D) No partial MVDs exist.

Ans: (C)

Q.36 Which one is correct statement?

Logical data independence provides following without changing application programs:
(i) Changes in access methods.
(ii) Adding new entities in database
(iii) Splitting an existing record into two or more records
(iv) Changing storage medium
(A) (i) and (ii) (B) (iv) only, (C) (i) and (iv) (D) (ii) and (iii)
 Ans: (D)

Q.37 In an E-R, Y is the dominant entity and X is a subordinate entity. Then which of the
following is incorrect :
(A) Operationally, if Y is deleted, so is X
(B) existence is dependent on Y.
(C) Operationally, if X is deleted, so is Y.
(D) Operationally, if X is deleted, & remains the same.

Ans: (C)

Q.38 Relational Algebra is

(A) Data Definition Language .
(B) Meta Language
(C) Procedural query Language
(D) None of the above

Ans: (C)

Q.39 Which of the following aggregate functions does not ignore nulls in its results?.
(A) COUNT . (B) COUNT (*)
(C) MAX (D) MIN

Ans: (B)

Q.40 R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency
preserving BCNF decomposition
(A) AgB, BgCD (B) AgB, BgC, CgD .
(C) ABgC, CgAD (D) AgBCD

Ans: (D)

Q.41 Consider the join of relation R with a relation S. If R has m tuples and S has n tuples, then
the maximum and minimum size of the join respectively are
(A) m+n and 0 (B) m+n and |m-n|
(C) mn and 0 (D) mn and m+n

Ans: (C)

Q.42 Maximum height of a B+ tree of order m with n key values is

(A) Logm(n) (B) (m+n)/2
(C) Logm/2(m+n) (D) None of these

Ans: (D)

Q.43 Which one is true statement :

(A) With finer degree of granularity of locking a high degree of concurrency is possible.
(B) Locking prevents non – serializable schedules.
(C) Locking cannot take place at field level.
(D) An exclusive lock on data item X is granted even if a shared lock is already held on X.
 Ans: (A)

Q.44 Which of the following statement on the view concept in SQL is invalid?
(A) All views are not updateable
(B) The views may be referenced in an SQL statement whenever tables are referenced.
(C) The views are instantiated at the time they are referenced and not when they are
defined.
(D) The definition of a view should not have GROUP BY clause in it.

Ans: (D)

Q.45 Which of the following concurrency control schemes is not based on the serializability
property?
(A) Two – phase locking (B) Graph-based locking
(C) Time-stamp based locking (D) None of these .

Ans: (D)

Q.46 Which of the following is a reason to model data?

(A) Understand each user’s perspective of data
(B) Understand the data itself irrespective of the physical representation
(C) Understand the use of data across application areas
(D) All of the above

Ans: (D)

Q.47 If an entity can belong to only one lower level entity then the constraint is
(A) disjoint (B) partial
(C) overlapping (D) single

Ans: (B)

Q.48 The common column is eliminated in

(A) theta join (B) outer join
(C) natural join (D) composed join

Ans: (C )

Q.49 In SQL, testing whether a subquery is empty is done using

(A) DISTINCT (B) UNIQUE
(C) NULL (D) EXISTS

Ans: (D)

Q.50 Use of UNIQUE while defining an attribute of a table in SQL means that the attribute values are
(A) distinct values (B) cannot have NULL
(C) both (A) & (B) (D) same as primary key

Ans: (C)

Q.51 The cost of reading and writing temporary files while evaluating a query can be reduced by
 (A) building indices (B) pipelining
(C) join ordering (D) none of the above

Ans: (B)

Q.52 A transaction is in __________ state after the final statement has been executed.
(A) partially committed (B) active
(C) committed (D) none of the above

Ans: (C)

Q.53 In multiple granularity of locks SIX lock is compatible with

(A) IX (B) IS
(C) S (D) SIX

Ans: (B)
Q.54 The statement that is executed automatically by the system as a side effect of the modification of
the database is
(A) backup (B) assertion
(C) recovery (D) trigger

Ans: (D)
Q.55 The normal form that is not necessarily dependency preserving is
(A) 2NF (B) 3NF
(C) BCNF (D) 4NF

Ans: (A)

Q.56 A functional dependency of the form is trivial if

(A) (B)
(C) (D)

Ans: (A)

Q.57 The normalization was first proposed by ______________.

(A) Code (B) Codd
(C) Boyce Codd (D) Boyce

Ans: (B)

Q.58 The division operator divides a dividend A of degree m+n by a divisor relation B of degree n and
produces a result of degree
(A) m – 1 (B) m + 1
(C) m * m (D) m

Ans: (D)

Q.59 Which of the following is not a characteristic of a relational database model?

(A) Table (B) Tree like structure
(C) Complex logical relationship (D) Records
 Ans: (B)

Q.60 Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R.
(A) It will result in a deadlock situation.
(B) It will immediately be rejected.
(C) It will immediately be granted.
(D) It will be granted as soon as it is released by A .

Ans: (C)
Q.61 In E-R Diagram total participation is represented by
(A) double lines (B) Dashed lines
(C) single line (D) Triangle

Ans: (A)

Q.63 The graphical representation of a query is ________.

(A) B-Tree (B) graph
(C) Query Tree (D) directed graph

Ans: (C)
Q.64 Union operator is a :
(A) Unary Operator (B) Ternary Operator
(C) Binary Operator (D) Not an operator

Ans: (C)

Q.65 Relations produced from an E-R model will always be

(A) First normal form. (B) Second normal form.
(C) Third normal form. (D) Fourth normal form.

Ans: (A)

Q.66 Manager salary details are hidden from the employee .This is
(A) Conceptual level data hiding.
(B) External level data hiding.
(C) Physical level data hiding.
(D) None of these.

Ans: (A)

Q.67 Which of the following is true for network structure?

(A) It is a physical representation of the data.
(B) It allows many to many relationship.
(C) It is conceptually simple.
(D) It will be the dominant database of the future.

Ans: (A)

Q.68 Which two files are used during operation of the DBMS?
 (A) Query languages and utilities
(B) DML and query language
(C) Data dictionary and transaction log
(D) Data dictionary and query language

Ans: (C )

Q.69 A list consists of last names, first names, addresses and pin codes. If all people in the list have
the same last name and same pin code a useful key would be
(A) the pin code
(B) the last name
(C) the compound key first name and last name
(D) Tr from next page

Ans: (C )

Q.70 In b-tree the number of keys in each node is ____ than the number of its children.
(A) one less (B) same
(C) one more (D) half

Ans: (A)

Q.71 The drawback of shadow paging technique are

(A) Commit overhead (B) Data fragmentation
(C) Garbage collection (D) All of these

Ans: (D)

Q.72 Which normal form is considered adequate for normal relational database design?
(A) 2NF (B) 5NF
(C) 4NF (D) 3NF

Ans: (D)

Q.73 Which of the following addressing modes permits relocation without any change over in the
code?
(A) Indirect addressing (B) Indexed addressing
(C) PC relative addressing (D) Base register addressing

Ans: (B)

Q.74 In a multi-user database, if two users wish to update the same record at the same time, they
are prevented from doing so by
(A) jamming (B) password
(C) documentation (D) record lock

Ans: (D)

Q.75 The values of the attribute describes a particular_____________

(A) Entity set (B) File
(C) Entity instance (D) Organization
 Ans: (C)

Q.76 Which of the following relational algebraic operations is not from set theory?
(A) Union (B) Intersection
(C) Cartesian Product (D) Select

Ans: (D)

Q.77 Which of the following ensures the atomicity of the transaction?

(A) Transaction management component of DBMS
(B) Application Programmer
(C) Concurrency control component of DBMS
(D) Recovery management component of DBMS

Ans: (A)

Q.78 If both the functional dependencies : X®Y and Y®X hold for two attributes X and Y then the
relationship between X and Y is
(A) M:N (B) M:1
(C) 1:1 (D) 1:M

Ans: (C)

Q.79 What will be the number of columns and rows respectively obtained for the operation, A- B, if A
B are Base union compatible and all the rows of a are common to B? Assume A has 4 columns and
10 rows; and B has 4 columns and 15 rows
(A) 4,0 (B) 0,0
(C) 4,5 (D) 8,5

Ans: (A)

Q.80 For correct behaviour during recovery, undo and redo operation must be
(A) Commutative (B) Associative
(C) idempotent (D) distributive

Ans: (C)

Q.81 Which of the following is not a consequence of non-normalized database?

(A) Update Anomaly (B) Insertion Anomaly
(C) Redundancy (D) Lost update problem

Ans: (D)

Q.82 Which of the following is true for relational calculus?

(A) "x(P(x))ºØ($x)(ØP(x)) (B) "x(P(x))ºØ($x)(P(x))
(C) "x(P(x))º($x)(ØP(x)) (D) "x(P(x))º($x)(P(x))

Ans: (A)

Q.83 The part of a database management system which ensures that the data remains in a consistent state
is
 (A) authorization and integrity manager
(B) buffer manager
(C) transaction manager
(D) file manager

Ans: (C)

Q.84 Relationships among relationships can be represented in an-E-R model using

(A) Aggregation (B) Association
(C) Weak entity sets (D) Weak relationship sets

Ans: (A)

Q.85 In tuple relational calculus P1 AND P2 is equivalent to

(A) (ØP1ORØP2). (B) Ø(P1ORØP2).
(C) Ø(ØP1OR P2). (D) Ø(ØP1OR ØP2).

Ans: (D)

Q.86 If a®b holds then so does

(A) ga®gb (B) a®®gb
(C) both (A) and (B) (D) None of the above

Ans: (A)

Q.87 Cascading rollback is avoided in all protocol except

(A) strict two-phase locking protocol.
(B) tree locking protocol
(C) two-phase locking protocol
(D) validation based protocol.

Ans: (D)

Q. 88 Wait-for graph is used for

(A) detecting view serializability. (B) detecting conflict serializability.
(C) deadlock prevention (D) deadlock detection

Ans: (D)

Q.90 The clause alter table in SQL can be used to

(A) add an attribute
(B) delete an attribute
(C) alter the default values of an attribute
(D) all of the above

Ans: (D)

Q. 91 The data models defined by ANSI/SPARC architecture are

(A) Conceptual, physical and internal
(B) Conceptual, view and external
 (C) Logical, physical and internal
(D) Logical, physical and view

Ans: (D)

Q.92 Whenever two independent one-to-many relationships are mixed in the same relation, a _______
arises.
(A) Functional dependency (B) Multi-valued dependency
(C) Transitive dependency (D) Partial dependency

Ans:(B)

Q.93 A table can have only one

(A) Secondary key (B) Alternate key
(C) Unique key (D) Primary key

Ans: (D)

Q.94 Dependency preservation is not guaranteed in

(A) BCNF (B) 3NF
(C) PJNF (D) DKNF

Ans: (A)

Q.96 Which of the following constitutes a basic set of operations for manipulating relational data?
(A) Predicate calculus (B) Relational calculus
(C) Relational algebra (D) SQL
Ans: (C)

Q.97 An advantage of views is

(A) Data security (B) Derived columns
(C) Hiding of complex queries (D) All of the above

Ans: (A)

Q.98 Which of the following is not a recovery technique?

(A) Deferred update (B) Immediate update
(C) Two-phase commit (D) Shadow paging

Ans: (C)

Q.99 Isolation of the transactions is ensured by

(A) Transaction management (B) Application programmer
(C) Concurrency control (D) Recovery management

Ans: (C)

Q.100 _______ operator is used to compare a value to a list of literals values that have been specified.
(A) Like (B) COMPARE
(C) BETWEEN (D) IN
Ans: (A)