DBMS Interview Questions and Answers

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.

DBMS 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. Example: MySQL, Oracle, SQLs server, dBASE, Foxpro, DB2, etc.

• Online users
• Native users
• Application programmers
• Database admin
• Sophisticated users and more

• Redundancy is controlled.
• Unauthorized access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

• Hardware
• Software
• Data
• Procedures
• User

• It is easy to understand
• Strictly secured
• Effective in data management
• Sharing information in different systems
• Best access to accurate data in searching
• Facility of better decision making

There are four types of database languages:

1. Data Definition Language (DDL) e.g. CREATE, ALTER, DROP etc.
2. Data Manipulation Language (DML) e.g. SELECT, UPDATE, INSERT etc.
3. DATA Control Language (DCL) e.g. GRANT and REVOKE.
4. Transaction Control Language (TCL) e.g. COMMIT and ROLLBACK.

DMS language is that enable user to access or manipulate data as organized by appropriate data model.

Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.

Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.

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.

Advantages of normalizing database are as follows:

• Prevents duplicate entries
• Conserves storage space
• Improves the performance of queries

Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data.

They are three levels of abstraction:

Physical level: The lowest level of abstraction describes how data are stored.

Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

View level: The highest level of abstraction describes only part of entire database.

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API, Structured Query Language (SQL).

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

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.

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.

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.

Extension: It is the number of tuples present in a table at any instance. This is time dependent.

Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.

Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing.

Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting.

Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that:

• The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this.
• Index, query distribution along with their performance is observed to check the effect.
• Tuning databases to a small collection of problem queries is also recommended.

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

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.

Define B-trees.

A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time.

Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user.

Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are:

• To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition.
• Accessing large parts of a single partition
• Slower and cheaper storage media can be used for data which is seldom used.

Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.

Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.

A-Atomicity

C-Consistency

I-Isolation

D-Durability

A query is a statement requesting the retrieval of information. The portion of a DML that

involves information retrieval is called a query language.

A data model is a conceptual representation of the data structures that are required by a

database.

Types of Data models:

Record based Data model

1. Relational data model
2. Network data model
3. Hierarchical data model

Object based data model

1. Object oriented model
2. Entity relational model

Physical data model

An entity-relationship diagram (or entity-relationship model) is a visual representation of data which is represented as entities, attributes and relationships are set between entities.

DDL stands for Data Definition Language. It is used to specify the database schema by the definitions.

DDL Commands:

• CREATE: It is used to create objects in the database.
• ALTER: It is used to alter the structure of the database.
• DROP: It is used to delete objects from the database.
• TRUNCATE: It is used to delete the records or data from the table, but its structure remains as it is.
• COMMENT: It is used to add comments on data dictionary.
• RENAME: It is used to rename an object.

DML stands for Data Manipulation Language. It is a language used to access or manipulate the data in the database.

DML Commands:

• SELECT: It is used to access or manipulate the data in the database.
• INSERT: It is used to insert the data or adds new records to the database.
• UPDATE: It is used to update existing data within a table.
• DELETE: It is used deletes the records in the database.
• MERGE: UPSERT Operation
• CALL: It calls a PL/SQL or Java subprogram
• EXPLAIN PLAN: It is used to explains the access path to data
• LOCKTABLE: It is used to controls concurrency.

DCL stands for Data Control language. It is used to access the stored or saved data. It is mainly used for revoking and granting user access on a database.

DCL Commands:

SYSTEM: It is used to creating a session, table, etc. are all types of system privilege.

OBJECT: It is used to any command or query to work on tables comes under object privilege. DCL is used to define two commands.

GRANT: It allows only specific user to perform specific tasks.

REVOKE: It allows the cancellation of the previously granted permissions or the denied one.

TCL stands for Transaction control Language.  It is used to manage the transactions in the database. It is used to run the changes made by DML statements.

TCL Commands:

COMMIT: It is used to save the transaction in the database.

ROLLBACK: It is used to restore the database to the last committed state.

SAVEPOINT: it is used to store the transaction temporarily such that one can easily rollback to the transaction when needed.

SET TRANSACTION: It is used to changes the transaction options like isolation level and what rollback segment to use.

Query decomposition is the first phase of query processing. Query decomposition maps a distributed calculus query into an algebraic query on global relations. The techniques used at this layer are those of the centralized DBMS. since relation distribution is not yet considered at this point. The primary targets of query decomposition are to transform a high-level query into a relational algebra query and to check that the query is syntactically and semantically correct. The typical stages of query decomposition are analysis, normalization, semantic analysis, simplification, and query restructuring.

Data Dictionary can be defined as a DBMS component which stores the definition of characteristics of data and relationships. This “data about data” are labeled as metadata. Data Dictionary provides the DBMS with its self-describing characteristic.

Data Replication is a process in which data is copied to different locations, to one site or node, either computers or servers, in order to surge the improvement of the data.

5 types of Data Replications are:

• Snapshot Replication
• Transactional Replication
• Merge Replication
• Heterogeneous Replication
• Peer to Peer Transactional Replication

It is a normal form used in database normalization which needs particularly that the database contains no restrictions other than domain constraints and key constraints.

It is a non-procedural language. It is used for selecting those tuples that satisfy the given condition.

Precedence graph is also known as serialization graph or conflict graph, It is used for testing Conflict Serializability of a schedule in the condition that forms the setting of concurrency control in databases.

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

Functional dependency is a set of constraints between two attributes in a relation. It says that if two tuples have same values for attributes A1, A2… An, then those two tuples must have to have same values for attributes B1, B2, …, Bn. It is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side.

4 Types of Functional Dependencies:

• Trivial functional dependency
• non-trivial functional dependency
• Multivalued dependency
• Transitive dependency

1NF: As per the rule of first normal form, A table is in first normal form (1NF) if and only if all columns contain only atomic values, that is, each column can have only one value for each row in the table.

2NF: A table is in second normal form (2NF) if and only if it is in 1NF and every non key attribute is fully dependent on the primary key.

3NF: A table is in third normal form (3NF) if and only if for every nontrivial functional dependency X->A, where X and A are either simple or composite attributes, one of two conditions must hold.

BSNF:  BSNF stands for Boyce and Codd Normal Form. It is a higher version of the 3NF. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied: R(table) must be in 3rd Normal Form and, for each functional dependency ( X → Y ), X should be a super Key.

4NF: A table is said to be in the Fourth Normal Form(4NF) when, it is in the Boyce-Codd Normal Form.And, it doesn’t have Multi-Valued Dependency.

5NF: 5NF is also known as project-join normal form (PJ/NF). A table is said to be in the fifth Normal Form(5NF) when, It is in the Fourth Normal Form (4NF). And, it doesn’t have no join dependency and also the joining must be lossless.

Serializability is the concept in a transaction that helps to identify which non-serial schedule is correct and will maintain the database consistency. It relates to the isolation property of transaction in the database. It is the concurrency scheme where the execution of concurrent transactions is equivalent to the transactions which execute serially.

HAVING is used in select statement for an aggerated function or to specify the condition of a group.

WHERE clause is used before grouping. The WHERE clause doesn’t contain aggerated functions.

• Primary Key
• Composite Key
• Foreign Key
• Simple Key
• Super key
• Candidate key
• Compound key
• Secondary or Alternative key
• Non- key attribute
• Non- prime attribute
• Artificial key

Foreign key is a key of one table, which points to the primary key in second table. It has a relationship with primary key in another table.

When primary key is very large and complex, then Artificial keys are used.

In DBMS,This rule is a concurrency control mechanism that is implemented to maintain and manage the concurrent transactions and processes in the database management system. It is an enhanced version of the timestamp ordering protocol that is useful for the concurrency control process.

ACID Properties of Transaction:

A-Atomic: Process all of a transaction or none of it; transaction cannot be further subdivided (like an atom)

C-Consistent: Data on all systems reflects the same state

I-Isolated: Transactions do not interact/interfere with one another; transactions act as if they are independent

D-Durable: Effects of a completed transaction are persistent