DBMS Interview Questions and Answers
1.What is a Database?
Ans: A database is a collection of related data .A database is a
logically coherent
collection of data with some inherent meaning.
2. What is DBMS?
Ans: Database Management system is a collection of programs that enables
user to create and maintain a database.
Thus a DBMS is a general purposed s/w system that facilitates the process of
defining constructing and manipulating a database for various applications.
(Defining a data base involves specifying the data types, structures and
constraints for the data to be stored in the data database.
Constructing a data base is the process of storing data itself on some storage
medium that is controlled by DBMS. Manipulating a database includes such
functions as querying the data base to retrieve specific data, updating the
database to reflect the changes in the mini-world.
3. What is a Catalog?
Ans: A catalog is a table that contain the information such as structure
of each file ,
the type and storage format of each data item and various constraints on the
data .
The information stored in the catalog is called Metadata . Whenever a request
is
made to access a particular data, the DBMS s/w refers to the catalog to
determine
the structure of the file.
4. What is data ware housing &
OLAP?
Ans: Data warehousing and OLAP (online analytical processing ) systems
are the
techniques used in many companies to extract and analyze useful information
from very large databases for decision making .
5. What is real time database
technology?
Ans: These are all the techniques used in controlling industrial and
manufacturing
processes.
6. What is program-data
independence?
Ans: Unlike in the traditional file sys. the structure of the data files
is stored in the
DBMS catalog separately from the access programs . This property is called
program-data independence.i.e. We needn’t to change the code of the DBMS if the
structure of the data is changed .Which is not supported by traditional file
sys .
7. What is ORDBMS?
Ans: Object oriented RDBMS is a relational DBMS in which every thing is
treated as
objects. User can define operations on data as a part of the database
definition.
8. What is program-operation
independence?
Ans: An operation is specified in two parts .
1. Interface (operation name and data types of its arguments).
2. Implementation (the code part)
The implementation part can be changed without affecting the interface. This is
called
program-operation independence.
9. What is a view?
Ans: A view may be a subset of the database or it may contain virtual
data that is
derived from the database files but is not explicitly stored .
10. What is OLTP?
Ans: Online transaction processing is an application that involve
multiple database accesses
from different parts of the world . OLTP needs a multi-user DBMS s/w to ensure
that concurrent
transactions operate correctly.
11. What is the job of DBA?
Ans: A database administrator is a person or a group responsible for
authorizing access to the
database, for coordinating and monitoring its use, and for acquiring s/w and
h/w resources as needed.
12. Who are db designer?
Ans: Data base designers are responsible for identifying the data to be
stored in the database and for
choosing appropriate structure to represent and store this data .
13. What are different types of end
users?
Ans:
1. Casual end-users
2. Naive or parametric end users
3. Sophisticated end users
4. Stand alone users.
14. What are the advantages of using
a dbms?
Ans:
1. Controlling redundancy.
2. Restricting unauthorized access.
3. Providing persistent storage for program objects and data structures.
4. Permitting inferencing and actions using rules.
5. Providing multi-user interfaces.
6. Representing complex relationships among data.
7. Enforcing integrity constraints.
8. Providing backups and recovery.
15. What are the disadvantages of
using a dbms?
Ans:
1. High initial investments in h/w, s/w, and training.
2. Generality that a DBMS provides for defining and processing data.
3. Overhead for providing security, concurrency control, recovery, and
integrity functions.
16. What is a data model?
Ans: It is a collection of concepts that can be used to describe the
structure of a database. It provides necessary means to achieve this
abstraction. By structure of a database we mean the data types, relations, and
constraints that should hold on the data.
17. What are different categories of
data models?
Ans:
1. High-level or conceptual data models.
2. Representational data models.
3. Low-level or physical data models.
High level data models provide the concepts that are close to the way many
users perceive data.
Representational data models are provide concepts that provide the concepts
that may be understood by end users but that are not too far removed from
organization of data in the database.
Physical data models describe the details of how data is stored in the
computers.
18. What is schema?
Ans: The description of a data base is called the database schema ,
which is specified during database design and is not expected to change
frequently . A displayed schema is called schema diagram .We call each object
in the schema as schema construct.
19. What are types of schema?
Ans:
1. internal schema.
2. Conceptual schema.
3. External schemas or user views.
20. What is Data independency?
Ans: Data independency is defined as the capacity to change the
conceptual schema without having to change the schema at the next higher level.
We can define two types of data independence:
1. Logical data independence.
2. Physical data independence.
LDI is the capacity to change the conceptual schema without having to change
external schemas or application programs.
PDI is the capacity to change the internal schema without having to change conceptual
(or external) schemas.
21. What are different DBMS
languages?
Ans:
1. DDL (Data definition language)
2. SDL (Storage definition language)
3. VDL (View definition language)
4. DML (Data manipulation language)
22. What are different types of
DBMS?
Ans:
1. DBMS
2. RDBMS (Relational)
3. ORDBMS (Object Relational)
4. DDBMS (Distributed)
5. FDBMS (Federated)
6. HDDBMS (Homogeneous)
7. HDBMS (Hierarchical)
8. NDBMS (Networked)
23. What is an entity?
Ans: An entity is a thing in the real world with an independent
existence.
24. What are attributes?
Ans: These are the particular properties that describe an entity.
25. What are diff. types of
attributes?
Ans:
1. Composite Vs simple attributes.
2. Single valued Vs multi-valued attributes.
3. Stored Vs derived attribute.
4. Null valued attributes.
5. Complex attributes.
26. What is difference between
entity set and entity type?
27. What is domain value or value
set of an attribute?
Ans: It is the set of values that may be assigned to that attribute for
each individual entities .
28. What is degree of a
relationship?
Ans: The no of entities participating in that relation .
29. What is recursive relationship?
Ans: It is the relationship where both the participating entities belong
to same entity type .
30. What are relationship
constraints?
Ans:
1. Cardinality ratio.
2. Participation constraints.
31. What is Cardinality ratio?
Ans: The cardinality ratio for a binary relationship specifies the
number of relationship instances that an entity can participate in.
32. What is a Participation
constraint?
Ans: The participation constraint specifies whether the existence of an
entity depends on its being related to another entity via the relationship
type. This is of two types:
1. Total participation.
2. Partial participation.
33. What is a weak entity types?
Ans: The entity types that do not have key attributes of their own are
called weak entity types.
Rests are called strong entity types .The entity that gives identity to a weak
entity is called owner entity. And the relationship is called identifying
relationship. A weak entity type always has a total participation constraint
with respect to its identifying relationship.
34. What is an ER Diagram?
Ans:
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.
35. What is an EER?
Ans:
==
36. What is specialization?
Ans: It is the process of defining a set of subclasses of an entity type
where each subclass contain all the attributes and relationships of the parent
entity and may have additional attributes and relationships which are specific
to itself.
37. What is generalization?
Ans: It is the process of finding common attributes and relations of a
number of entities and defining a common super class for them.
38. What are constraints on
generalization and specialization?
Ans:
1. disjoint ness constraints.
2. Completeness constraints.
Disjointness constraint specifies that the subclasses of the specialization
must be disjoint .i.e. an entity can be a member of at most one of the
subclasses of the specialization. The reverse of it is overlapping.
Completeness constraint is a participation constraint which may be
1. Total
2. Partial
Total specialization constraint tells that each entity in the super class must
be a member of some subclass in the specialization. And partial specialization
constraint allows an entity not to belong to any of the subclasses .Thus we do
have the following 4 types of constraints on specialization:
1. Disjoint, total
2. Disjoint, partial
3. Overlapping, total
4. Overlapping, partial
39. What is a ternary relationship?
Ans: A relationship with a degree 3 is called a ternary relationship.
40. What is aggregation and
association?
Ans: Aggregation is an abstraction concept for building composite
objects from their component objects. The abstraction of association is used to
associate objects from several independent classes.
41. What is RAID Technology?
Ans: Redundant array of inexpensive (or independent) disks. The main
goal of raid technology is to even out the widely different rates of
performance improvement of disks against those in memory and microprocessor.
Raid technology employs the technique of data striping to achieve higher
transfer rates.
42. What is Hashing technique?
Ans: This is a primary file organization technique that provides very
fast access to records on certain search conditions. The search condition must
be an equality condition on a single field, called hash field of the file.
1. Internal hashing
2. External hashing
3. Extendible hashing
4. Linear hashing
5. Partitioned hashing
43. What are different types of
relational constraints?
Ans:
1. Domain constraints
2. Key constraints
3. Entity integrity constraints
4. Referential integrity constraints
Domain constraints specify that the value of each attribute must be an atomic
value from the domain of the attributes.
Key constraints tell that no two tuples can have the same combination of values
for all their attributes.
Entity integrity constraint states that no primary key value can be null.
Referential integrity constraints states that a tuple in one relation that
refers to another relation must refer to an existing tuple in that relation it
is specified between two relations and is used to maintain the consistency
among tuples of the two relations.
44. What is difference between a
super key, a key, a candidate key and a primary key?
Ans: A super key specifies a uniqueness constrain that no two distinct
tuples in a state
can have the same value for the super key. Every relation has at least one
default super key.
A key is a minimal super key or the subset of the super key which is obtained
after
removing redundancy. A relation schema may have more than one key .In this case
each key is called a candidate key. One of the candidate key with minimum
number
of attributes is chosen as primary key.
45. What is a foreign key?
Ans: A key of a relation schema is called as a foreign key if it is the
primary key of
some other relation to which it is related to.
46. What is a transaction?
Ans: A transaction is a logical unit of database processing that
includes one or more
database access operations.
47. What are the properties of
transaction?
Ans:
1. Atomicity
2. Consistency preservation
3. Isolation
4. Durability (permanence)
48. What are the basic data base
operations?
Ans:
1. Write_item(x)
2. Read_item(x)
49. What are the disadvantages of
not controlling concurrency?
Ans:
1. Lost update problem
2. Temporary update(Dirty read) problem
3. Incorrect summary problem
50. What are serial, non serial?
Ans: A schedule S is serial if, for every transaction T participating in
the schedule, all the operations of T is executed consecutively in the
schedule, otherwise, the schedule is called non-serial schedule.
51. What are conflict serializable
schedules?
Ans: A schedule S of n transactions is serializable if it is equivalent
to some serial schedule of the same n transactions.
52. What is result equivalent?
Ans: Two schedules are called result equivalent if they produce the same
final state of the data base.
53. What are conflict equivalent
schedules?
Ans: Two schedules are said to be conflict equivalent if the order of
any two conflicting operations is the same in both schedules.
54. What is a conflict serializable
schedule?
Ans: A schedule is called conflict serializable if it is conflict
equivalent to some serial schedule.
55. What is view equivalence?
Ans: Two schedules S and S’ are said to be view equivalent if the
following three conditions hold :
1. Both S and S’ contain same set of transactions with same operations in them.
2. If any read operation read(x) reads a value written by a write operation or
the original value of x the same conditions must hold in the other schedule for
the same read(x) operation.
3. If an operation write1(y) is the last operation to write the value of y in
schedule S then the same operation must be the last operation in schedule S’.
56. What is view serializable?
Ans: A schedule is said to be view serializable if it is view equivalent
with some serial schedule.
57. What are the various methods of
controlling concurrency?
Ans:
1. Locking
2. Time stamp
Locking data item to prevent multiple transactions from accessing the item
concurrently.
A time stamp is a unique identifier for each transaction, generated by the
system.
58. What is a lock?
Ans: A lock is a variable associated with a data item that describes the
status of the item with respect to the possible operations that can be applied
to it.
59. What are various types of
locking techniques?
Ans:
1. a binary lock
2. Shared/Exclusive lock
3. Two phase locking
60. What is a binary lock?
Ans: A binary lock can have two states or values:
1. locked (1)
2. unlocked(0)
If locked it cannot be accessed by any other operations, else can be.
61. What is shared or exclusive
lock?
Ans: It implements multiple-mode lock. Allowing multiple accesses for
read operations but exclusive access for write operation.
62. Explain two phase locking?
Ans: All the locking operations must precede the first unlock operation
in the transaction .It does have two phases:
1. expanding phase (Locks are issued)
2. Shrinking phase (Locks are released)
63. What are different types of two
phase lockings (2pl)?
Ans:
1. Basic
2. Conservative
3. Strict
4. Rigorous
this is the basic technique of 2pl described above.
Conservative 2pl requires a transaction to lock all the items it accesses
before the transaction begins its execution, by pre-declaring it’s read-set and
write-set.
Strict 2pl guarantees that a transaction doesn’t release any of its exclusive
locks until after it commits or aborts.
Rigorous guarantees that a transaction doesn’t release any of its locks
(including shared locks) until after it commits or aborts.
64. What is a deadlock?
Ans: Dead lock occurs when each transaction T in a set of two or more
transactions is waiting for some item that is locked by some other transaction
T’ in the set. Hence each transaction is in a waiting queue, waiting for one of
the other transactions to release the lock on them.
65. What are triggers?
Ans: Triggers are the PL/SQL blocks definining an action the database
should take when some database related event occurs. Triggers may be used to
supplement declarative referential integrity, to enforce complex business
rules, or to audit changes to data.
---------------------------------------------------------------------------------
Posted By Sundeep aka SunTechie
Sundeep
is a Founder of Youth Talent Auzzar, a passionate blogger, a
programmer, a developer, CISE and these days he is pursuing his
graduation in Engineering with Computer Science dept.
Add Sundeep as a Friend on
