dbms interview questions

• 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

• 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.

• What is the difference between DBMS and RDBMS?

DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity

RDBMS = DBMS + REFERENTIAL INTEGRITY

every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.

if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.

• What is a Database system?

The database and DBMS software together is called as Database system.

• 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.

• 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.

• Difference between a file and database

That’s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not
File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.

1. Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.
2. Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.
3. Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.

• 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.

• 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.

• What is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics and constraints

• What is system catalog or catalog relation?

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

• Define the "integrity rules"?

There are two Integrity rules.
Entity Integrity: States that "Primary key cannot have NULL value"
Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.

• What is indexing and what are the different kinds of indexing?

Indexing is a technique for determining how quickly specific data can be found.
Types:

1. Binary search style indexing
2. B-Tree indexing
3. Inverted list indexing
4. Memory resident table
5. Table indexing

• What is a checkpoint and When does it occur?

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

• What are the unary operations in Relational Algebra?

PROJECTION and SELECTION

• Are the resulting relations of PRODUCT and JOIN operation the same?

No.
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.

• What is Buffer Manager?

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cached in memory.

• What is Transaction Manager?

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

• What is File Manager?

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

• What is DDL, DML and DCL ?

1. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL.
2. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc.
3. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

References:
http://www.indiabix.com/technical/dbms-basics/7
http://www.techpreparation.com/mysql-interview-questions-answers1.htm
http://www.dotnetfunda.com/interview/exclusive/showcatquestion.aspx?category=38



DBMS Interview Questions With Answers
http://ihelpstudy.com/dbms-interview-questions-with-answers-database-management-system-most-common-interview-question-788.html

DBMS Interview Questions and Answers
http://vacancyhub.com/?p=800

DBMS - Interview Questions and Answers
http://www.indiabix.com/technical/dbms/