Recently Updated DBMS Interview Questions Part – 4
1.What is Index?
An index is a physical structure containing pointers to the data.
2.What is extension and intension?
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.
3.What do you mean by atomicity and aggregation?
Atomicity-
Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails,the entire transaction fails.
Aggregation –
A feature of the entity relationship model that allows a relationship set to participate in another relationship set. This is indicated on an ER diagram by drawing a dashed box around the aggregation.
4.What is RDBMS KERNEL?
Two important pieces of RDBMS architecture are the kernel,which is the software,and the data dictionary,which consists of the system- level data structures used by the kernel to manage the database.
5.Name the sub-systems of a RDBMS?
I/O,Security,Language Processing,Process Control,Storage Management,Logging and Recovery,Distribution Control,Transaction Control,Memory Management,Lock Management.
6.How do you communicate with an RDBMS?
You communicate with an RDBMS using Structured Query Language (SQL)
7.Disadvantage in File Processing System?
. Data redundancy & inconsistency.
. Difficult in accessing data.
. Data isolation.
. Data integrity.
. Concurrent access is not possible.
. Security Problems.
8.What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
9.What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may Specify the mapping between two schemas.
10.Describe concurrency control
Concurrency control is the process managing simultaneous operations against a database so that database integrity is no compromised. There are two approaches to concurrency control.
The pessimistic approach involves locking and the optimistic approach involves versioning.
11.Describe the difference between homogeneous and heterogeneous distributed database
A homogenous database is one that uses the same DBMS at each node.
A heterogeneous database is one that may have a different DBMS at each node.
12.What is a distributed database?
A distributed database is a single logical database that is spread across more than one node or locations that are all connected via some communication link.
13.Explain the difference between two and three-tier architectures
Three-tier architecture includes a client and two server layers. The application code is stored on the application server and the database is stored on the database server. A two-tier architecture includes a client and one server layer. The database is stored on the database server.
14.Briefly describe the three types of SQL commands
Data definition language commands are used to create,alter,and drop tables. Data manipulation commands are used to insert,modify,update,and query data in the database. Data control language commands help the DBA to control the database.
15.List some of the properties of a relation
Relations in a database have a unique name and no multivalued attributes exist. Each row is unique and each attribute within a relation has a unique name. The sequence of both columns and rows is irrelevant.
16.Explain the differences between an intranet and an extranet
An Internet database is accessible by everyone who has access to a Web site. An intranet database limits access to only people within a given organization.
17.What is SQL Deadlock?
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource.
18.What is a Catalog?
A catalog is a table that contains 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.
19.What is data ware housing & OLAP?
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 .
20.Describe the three levels of data 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.
21.What is Data Independence?
Data independence means that the application is independent of the storage structure and access strategy of data.
22.How many types of relationship exist in database designing?
There are three major relationship models:-
One-to-one
One-to-many
Many-to-many
23.What is order by clause?
ORDER BY clause helps to sort the data in either ascending order to descending
24.What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases,i.e.,maintenance,validation task and status checks.
25.What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared.
26.What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.
Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
27.What is Hashing technique?
This is a primary file organization technique that provides very fast access to records on certain search conditions.
28.What is a transaction?
A transaction is a logical unit of database processing that includes one or more database access operations.
29.What are the different phases of Transaction?
Analysis phase
Redo phase
Undo phase