DBMS Interview Questions
A list of top frequently asked DBMS interview questions and answers are given below.
1) What is DBMS?
DBMS is a collection of programs that facilitates users to create and maintain a database.
2) What is a database?
Database is a logical, consistent and organized collection of data that it can easily be accessed, managed and updated.
For more information:
3) What is a database system?
The collection of database and DBMS software together is known as database system.
4) What are the advantages of DBMS?
- Redundancy control
- Restriction for unauthorized access
- Provides multiple user interfaces
- Provides backup and recovery
- Enforces integrity constraints
5) What is a checkpoint in DBMS?
A Checkpoint is like a snapshot of the DBMS state.
6) When does checkpoint occur in DBMS?
By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.
7) What do you mean by transparent DBMS?
The transparent DBMS is a type of DBMS which keeps its physical structure hidden from users.
8) What are the unary operations in Relational Algebra?
PROJECTION and SELECTION are the unary operations in relational algebra.
9) How do you communicate with an RDBMS?
You have to use Structured Query Language (SQL) to communicate with RDBMS.
10) How many types of database languages are?
There are four types of database languages:
- Data Definition Language (DDL) e.g. CREATE, ALTER, DROP etc.
- Data Manipulation Language (DML) e.g. SELECT, UPDATE, INSERT etc.
- DATA Control Language (DCL) e.g. GRANT and REVOKE.
- Transaction Control Language (TCL) e.g. COMMIT and ROLLBACK.
11) What do you understand by Data Model?
Data model is specified as a collection of conceptual tools for describing data, data relationships data semantics and constraints.
12) Define a Relation Schema and a Relation.
A Relation Schema is 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 specified as a set of tuples.
See this example:
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).
13) What is degree of a Relation?
The degree of relation is a number of attribute of its relation schema.
14) What is the Relationship?
The relationship is defined as an association among two or more entities.
15) What are the disadvantages of file processing systems?
- Inconsistent
- Not secure
- Data redundancy
- Difficult in accessing data
- Data isolation
- Data integrity
- Concurrent access is not possible
16) What is data abstraction in DBMS?
Data abstraction in DBMS is a process of hiding irrelevant details from users. Because Database systems are made of complex data structures so, it makes easy the user interaction with database.
17) What are the three levels of data abstraction?
Following are three levels of data abstraction:
Physical level: It is the lowest level of abstraction. It describes how data are stored.
Logical level: It is the next higher level of abstraction. It describes what data are stored in database and what relationship among those data.
View level: It is the highest level of data abstraction. It describes only part of entire database.
18) What is DDL (Data Definition Language)?
Data Definition Language (DDL) is a standard for commands which define the different structures in a database. Most commonly DDL statements are CREATE, ALTER, and DROP.
19) What is DML (Data Manipulation Language)?
Data Manipulation Language (DML) is a language that enable user to access or manipulate data as organised by appropriate data model.
There are two type of DML:
Procedural DML or Low level DML: It requires a user to specify what data are needed and how to get those data.
Non-Procedural DML or High level DML: It requires a user to specify what data are needed without specifying how to get those data.
20) Explain the functionality of DML Compiler.
The DML Compiler translates DML statements in a query language that the query evaluation engine can understand.
21) What is Relational Algebra?
Relational Algebra is a Procedural Query Language which contains a set of operations that take one or two relations as input and produce a new relation.
22) What is Relational Calculus?
Relational Calculus is a Non-procedural Query Language which uses mathematical predicate calculus instead of algebra.
23) What do you understand by query optimization?
The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost.
24) What do you mean by durability in DBMS?
Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.
25) What is normalization?
Normalization is a process of analyzing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also minimize insertion, deletion and update distractions.
26) What is Denormalization?
Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data.
27) What is functional Dependency?
Functional Dependency is the starting point of normalization. It exists when a relation between two attributes allows you to uniquely determine the corresponding attribute's value.
28) What is E-R model?
E-R model is a short name for Entity Relationship model. This model is based on real world. It contains basic objects (known as entities) and relationship among these objects.
29) What is entity?
Entity is a set of attributes in a database.
30) What is an Entity type?
An entity type is specified as a collection of entities, having same attributes.
31) What is an Entity set?
The entity set specifies the collection of all entities of particular entity type in the database.
32) What is an Extension of entity type?
An extension of entity type is specified as a collection of entities of a particular entity type are grouped together into an entity set.
33) What is Weak Entity set?
When an entity set doesn't 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 called Weak Entity set.
34) What is an attribute?
An attribute is a particular property, which describes the entity.
35) What are the integrity rules in DBMS?
There are two integrity rules in DBMS:
Entity Integrity: It specifies that "Primary key cannot have NULL value."
Referential Integrity: It specifies that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.
36) What do you mean by extension and intension?
Extension: Extension is the number of tuples present in a table at any instance. This is time dependent.
Intension: Intension is a constant value that gives the name, structure of table and the constraints laid on it.
37) What is System R? How many of 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 which is developed 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.
Following are two major subsystems of System R:
- Research Storage
- System Relational Data System
38) What is Data Independence?
Data independence specifies that "the application is independent of the storage structure and access strategy of data".
It makes you able to modify the schema definition in one level should not affect the schema definition in the next higher level.
There are two types of Data Independence:
Physical Data Independence: Modification in physical level should not affect the logical level.
Logical Data Independence: Modification in logical level should affect the view level.
39) What are the three levels of data abstraction?
Physical level: It is the lowest level of abstraction. It describes how data are stored.
Logical level: It is the next higher level of abstraction. It describes what data are stored in database and what relationship among those data.
View level: It is the highest level of abstraction. It describes only part of entire database.
40) What is stored procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
41) What is 1NF?
1NF is the First Normal Form. It is the simplest type of normalization that you can implement in a database. The main objectives of 1NF are to:
- Remove duplicate columns from the same table
- Create separate tables for each group of related data and identify each row with a unique column
42) What is 2NF?
2NF is the Second Normal Form. A table is said to be 2NF, if it follows the following conditions:
- The table is in 1NF.
- Every non-prime attribute is fully functionally dependent on primary key.
43) What is 3NF?
3NF stands for Third Normal Form. A database is called in 3NF if satisfies the following conditions:
- It is in second normal form.
- There is no transitive functional dependency.
44) What is BCNF?
BCMF stands for Boyce-Codd Normal Form. It is an advance version of 3NF so it is also referred as 3.5NF. BCNF is stricter than 3NF.
A table complies with BCNF if it satisfies the following conditions:
- It is in 3NF.
- For every functional dependency X->Y, X should be the super key of the table.
0 comments:
Post a Comment