DBMS

1. What is DBMS?

DBMS stands for Database Management System, is a set of applications or programs that
enable users to create and maintain a database. DBMS provides a tool or an interface
for performing various operations such as inserting, deleting, updating, etc. into a
database. It is software that enables the storage of data more compactly and securely
as compared to a file-based system. A DBMS system helps a user to overcome
problems like data inconsistency, data redundancy, etc. in a database and makes it
more convenient and organized to use it. Some popular Database Management
System- MySQL, Oracle, SQL Server etc.

2. What is Database?

A database is an organized collection of data, so that it can be easily accessed and

managed. A database is usually controlled by a database management system (DBMS).

3. What is database system?

The collection of database and DBMS software together is known as a database
system. The data will be extracted from the database using DBMS software whenever
required. So, the combination of database and DBMS software enables one to store,
retrieve and access data with considerate accuracy and security.

4. What are the advantages of DBMS?

o Redundancy control
o Restriction for unauthorized access
o Provides multiple user interfaces
o Provides backup and recovery
o Enforces integrity constraints
o Ensure data consistency
o Easy accessibility
o Easy data extraction and data processing due to the use of queries
5. What is RDBMS?

DBMS stands for Database Management System, is a set of applications or

programs that enable users to create and maintain a database. DBMS provides a tool
or an interface for performing various operations such as inserting, deleting,
updating, etc. into a database. It is software that enables the storage of data more
compactly and securely as compared to a file-based system. A DBMS system helps
a user to overcome problems like data inconsistency, data redundancy, etc. in a
database and makes it more convenient and organized to use it.

Examples of popular RDBMS systems are MySQL, Oracle DB, etc.

6. Difference between DBMS and RDBMS?

DBMS RDBMS

DBMS stores data as file. RDBMS stores data in tabular form.

Data elements need to access Multiple data elements can be accessed

individually. at the same time.

Data is stored in the form of tables which

No relationship between data. are related to each other.

Normalization is not present. Normalization is present.

DBMS does not support

distributed database. RDBMS supports distributed database.

It uses a tabular structure where the

It stores data in either a headers are the column names, and the
navigational or hierarchical form. rows contain corresponding values.

It deals with small quantity of

data. It deals with large amount of data.
 DBMS RDBMS

Data redundancy is common in Keys and indexes do not allow Data

this model. redundancy.

It is used for small organization

and deal with small data. It is used to handle large amount of data.

It supports single user. It supports multiple users.

Data fetching is slower for the Data fetching is fast because of

large amount of data. relational approach.

The data in a DBMS is subject to

low security levels with regards There exists multiple levels of data
to data manipulation. security in a RDBMS.

Low software and hardware Higher software and hardware

necessities. necessities.

Examples: XML, Window Examples: MySQL, PostgreSQL, SQL

Registry, etc. Server, Oracle, Microsoft Access etc.

7. Mention the issues with traditional file-based systems that make DBMS
a better choice?

• The other issue is redundancy and inconsistency as files have many duplicate
and redundant data and changing one of them makes all of them
inconsistent. Accessing data is harder in traditional file-based systems
because data is unorganized in them.
• Another issue is the lack of concurrency control, which leads to one
operation locking the entire page, as compared to DBMS where multiple
operations can work on a single file simultaneously.
• Integrity check, data isolation, atomicity, security, etc. are some other issues
with traditional file-based systems for which DBMSs have provided some good
solutions.

8. Explain different languages present in DBMS?

Following are various languages present in DBMS:

• DDL(Data Definition Language): It contains commands which are required

to define the database.
E.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
• DML(Data Manipulation Language): It contains commands which are
required to manipulate the data present in the database.
E.g., SELECT, UPDATE, INSERT, DELETE, etc.
• DCL(Data Control Language): It contains commands which are required to
deal with the user permissions and controls of the database system.
E.g., GRANT and REVOKE.
• TCL(Transaction Control Language): It contains commands which are
required to deal with the transaction of the database.
E.g., COMMIT, ROLLBACK, and SAVEPOINT.

9. What is meant by ACID properties in DBMS?

10. Are NULL values in a database the same as that of blank space or zero?

No, a NULL value is very different from that of zero and blank space as it represents
a value that is assigned, unknown, unavailable, or not applicable as compared to
blank space which represents a character and zero represents a number.

Example: NULL value in “number_of_courses” taken by a student represents that its

value is unknown whereas 0 in it means that the student hasn’t taken any courses.
11. What is sql?

SQL (Structured Query Language). It is used to perform operations on the records

stored in the database, such as updating records, inserting records, deleting records,
creating and modifying database tables, views, etc.

12. Explain keys and its types?

Keys : It is used to uniquely identify any record or row of data from the table. It is also
used to establish and identify relationships between tables.

1. Primary key :
A primary key is a field in a table which uniquely identifies each row/record in
a database table. Primary keys must contain unique values. A primary key
column cannot have NULL values. A table can have only one primary key.

2. Candidate key :
A candidate key is an attribute or set of attributes that can uniquely identify a
tuple. Except for the primary key, the remaining attributes are considered a
candidate key. The candidate keys are as strong as the primary key.

3. Super key :
Super key is an attribute set that can uniquely identify a tuple. A super key is
a superset of a candidate key.

4. Foreign key :

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the
primary key is called the referenced or parent table.

5. Alternate key :

A candidate key not selected as a primary key is called alternate or secondary

key.

6. Composite key :

Whenever a primary key consists of more than one attribute, it is known as a

composite key.
13. What is meant by normalization and denormalization?

Normalization is a process of reducing redundancy by organizing the data into

multiple tables. Normalization leads to better usage of disk spaces and makes it
easier to maintain the integrity of the database.

Denormalization is the reverse process of normalization as it combines the tables

which have been normalized into a single table so that data retrieval becomes
faster. JOIN operation allows us to create a denormalized form of the data by
reversing the normalization.

14. What is constraint in SQL?

SQL constraints are used to specify rules for the data in a table. Constraints are
used to limit the type of data that can go into a table. This ensures the accuracy
and reliability of the data in the table.

15. What is unique constraint?

The UNIQUE constraint ensures that all values in a column are different. Both the
UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a
column or set of columns.

16. What is Schema?

A schema can be defined as the design of a database. The overall description of the
database is called the database schema. It can be categorized into three parts. These
are:

• Physical Schema
• Logical Schema
• View Schema
A physical schema can be defined as the design of a database at its physical level. In
this level, it is expressed how data is stored in blocks of storage.

A logical schema can be defined as the design of the database at its logical level. In
this level, the programmers, as well as the database administrator (DBA), work. At this
level, data can be described as certain types of data records that can be stored in the
form of data structures. However, the internal details (such as an implementation of
data structure) will be remaining hidden at this level.

View schema can be defined as the design of the database at the view level, which
generally describes end-user interaction with database systems.

17. What is the Relationship?

The Relationship is defined as an association among two or more entities. There are
three type of relationships in DBMS-

One-To-One: Here one record of any object can be related to one record of another
object.

One-To-Many (many-to-one): Here one record of any object can be related to many
records of other object and vice versa.

Many-to-many: Here more than one records of an object can be related to n number
of records of another object.

18. What is data abstraction in dbms and explain various levels of data
abstraction?

Data abstraction in DBMS is a process of hiding irrelevant details from users.

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 the database and what the relationship among those data is.

View level: It is the highest level of data abstraction. It describes only part of
the entire database.

For example- User interacts with the system using the GUI and fill the required
details, but the user doesn't have any idea how the data is being used. So, the
abstraction level is entirely high in VIEW LEVEL.

19. What are the integrity rules in DBMS?

Data integrity is one significant aspect while maintaining the database. So, data
integrity is enforced in the database system by imposing a series of rules. Those set of
integrity is known as the integrity rules.

There are 2 major integrity rules that exist in the DBMS. They are:

• Entity Integrity: This states a very important rule that value of a Primary key can
never have a NULL value.
• Referential Integrity: This rule is related to the Foreign Key which states that
either the value of a Foreign Key is a NULL value or it should be the primary key
of any other relation.

20. 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 at one level without altering the schema definition in the next higher level.

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: Physical data is the data stored in the database.
It is in the bit-format. Modification in physical level should not affect the logical
level.

For example: If we want to manipulate the data inside any table that should not
change the format of the table.
 • Logical Data Independence: Logical data in the data about the database. It
basically defines the structure. Such as tables stored in the database.
Modification in logical level should not affect the view level.

For example: If we need to modify the format of any table, that modification
should not affect the data inside it.

21. What is functional Dependency?

The functional dependency is also known as database dependency and defines as the
relationship which occurs when one attribute in a relation uniquely determines another
attribute. It is written as A->B which means B is functionally dependent on A.

22. What is Join with its types?

The Join operation is one of the most useful activities in relational algebra. It is most
commonly used way to combine information from two or more relations. A Join is
always performed on the basis of the same or related column

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the
matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the
matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either
left or right table.

23. What is normalization with its types?

Normalization is a process of reducing redundancy by organizing the data into

multiple tables. Normalization leads to better usage of disk spaces and makes it
easier to maintain the integrity of the database.

There are various types of normal forms such as 1nf, 2nf, 3nf, and bcnf.

1NF:

A relation is said to be in 1 normal form in DBMS (or 1NF) when it consists of an atomic
value. In simpler words, 1NF states that a table's attribute would not be able to hold
various values- it will only be able to hold an attribute of a single value.
2NF:

• It has to be in 1st normal form

• There should be no partial dependency i.e., no non-prime attribute (attributes
which are not part of any candidate key) is dependent on any proper subset of
any candidate key of the table.

3NF:

• It has to be in 2nd normal form

• There should be no transitive dependency for non-prime attributes

Transitive dependency: if A is dependant on B and B is dependant on C then

transitively A is dependant of C

Boyce-Codd Normal Form (BCNF) :

• It has to be in 3rd normal form
• Every functional dependency A->B, then A has to be the Super key of that
particular table.

24. Explain 2 tier architecture?

Two-tier architecture is similar to a basic client-server model. The application at the client
end directly communicates with the database at the server-side. API's like ODBC, JDBC are
used for this interaction. The server side is responsible for providing query processing and
transaction management functionalities. On the client-side, the user interfaces and
application programs are run. The application on the client-side establishes a connection
with the server-side in order to communicate with the DBMS.
An advantage of this type is that maintenance and understanding are easier, compatible
with existing systems. However, this model gives poor performance when there are a large
number of users.
25. Explain three tier architecture?

DBMS 3-tier architecture divides the complete system into three inter-related but
independent modules as shown below:

1. Physical Level: At the physical level, the information about the location of
database objects in the data store is kept. Various users of DBMS are
unaware of the locations of these objects.

2. Conceptual Level: At conceptual level, data is represented in the form of

various database tables. For Example, STUDENT database may contain
STUDENT and COURSE tables which will be visible to users but users are
unaware of their storage.

3. External Level: An external level specifies a view of the data in terms of

conceptual level tables. Each external level view is used to cater to the
needs of a particular category of users. For Example, FACULTY of a
university is interested in looking course details of students, STUDENTS are
interested in looking at all details related to academics, accounts, courses
and hostel details as well. So, different views can be generated for different
users.

26. Write a query to find Nth highest salary from the employee table?

• SELECT name, salary

FROM employee A
WHERE n-1 = (SELECT count(1)
FROM employee B
WHERE B.salary>A.salary)

• SELECT salary
FROM employee
ORDER BY salary desc limit n-1,1
27. What is indexing in dbms?
Indexing is a way to optimize the performance of a database by minimizing the
number of disk accesses required when a query is processed. It is a data structure
technique which is used to quickly locate and access the data in a database.

28. What are sql commands? Explain types of sql commands?

These SQL commands are mainly categorized into four categories as:

1. DDL - Data Definition Language

2. TCL – Transaction Control Language
3. DML - Data Manipulation Language
4. DCL - Data Control Language
1. DDL(Data Definition Language) : DDL or Data Definition Language actually
consists of the SQL commands that can be used to define the database
schema. It simply deals with descriptions of the database schema and is used
to create and modify the structure of database objects in the database.

Examples of DDL commands:

o CREATE - is used to create the database or its objects (like table,
index, function, views, store procedure and triggers).

o DROP - is used to delete objects from the database.

o ALTER-is used to alter the structure of the database.
o TRUNCATE–is used to remove all records from a table, including all
spaces allocated for the records are removed.
o RENAME –is used to rename an object existing in the database.

2. TCL(Transaction Control Language) : e.g., COMMIT, ROLLBACK, and

SAVEPOINT. These are the commands used for managing transactions in the
database. TCL is used for managing the changes made by DML.
o

3. DML(Data Manipulation Language) : The SQL commands that deals with

the manipulation of data present in the database belong to DML or Data
Manipulation Language and this includes most of the SQL statements.

Examples of DML:
o INSERT – is used to insert data into a table.
o UPDATE - is used to update existing data within a table.
o DELETE – is used to delete records from a database table.
o SELECT – is used to retrieve data from the a database.

4. DCL(Data Control Language) : DCL includes commands such as GRANT

and REVOKE which mainly deals with the rights, permissions and other
controls of the database system.
 Examples of DCL commands:

o GRANT-gives user’s access privileges to database.

o REVOKE-withdraw user's access privileges given by using the
GRANT command.

5. TCL(transaction Control Language) : TCL commands deals with

the transaction within the database.

Examples of TCL commands:

o COMMIT– commits a Transaction.
o ROLLBACK– rollbacks a transaction in case of any error occurs.
o SAVEPOINT–sets a savepoint within a transaction.
o SET TRANSACTION–specify characteristics for the transaction.

29. Explain the difference between a 2-tier and 3-tier architecture in a

DBMS.

The 2-tier architecture refers to the client-server architecture in which applications

at the client end directly communicate with the database at the server end without
any middleware involved.
Example – Contact Management System created using MS-Access or Railway
Reservation System, etc.
 The above picture represents a 2-tier architecture in a DBMS.

The 3-tier architecture contains another layer between the client and the server to
provide GUI to the users and make the system much more secure and accessible. In
this type of architecture, the application present on the client end interacts with an
application on the server end which further communicates with the database
system.

Example – Designing registration form which contains a text box, label, button or a
large website on the Internet, etc.

The above picture represents a 3-tier architecture in a DBMS.

30. What is the difference between a DELETE command and TRUNCATE

command?

DELETE command: DELETE command is used to delete rows from a table based on
the condition that we provide in a WHERE clause.

o DELETE command delete only those rows which are specified with the WHERE clause.
o DELETE command can be rolled back.
o DELETE command maintain a log, that's why it is slow.
 o DELETE use row lock while performing DELETE function.

TRUNCATE command: TRUNCATE command is used to remove all rows (complete

data) from a table. It is similar to the DELETE command with no WHERE clause.

o The TRUNCATE command removes all the rows from the table.
o The TRUNCATE command cannot be rolled back.
o The TRUNCATE command doesn't maintain a log. That's why it is fast.
o TRUNCATE use table log while performing the TRUNCATE function.

31. What is stored procedure?

A stored procedure is a group of SQL statements that have been created and stored
in the database. The stored procedure increases the reusability as here the code or the
procedure is stored into the system and used again and again that makes the work
easy, takes less time in processing and decreases the complexity of the system.

32. What is an entity?

The Entity is a set of attributes in a database. An entity can be a real-world object which
physically exists in this world. All the entities have their attribute which in the real world
considered as the characteristics of the object.

For example: In the employee database of a company, the employee, department, and
the designation can be considered as the entities. These entities have some
characteristics which will be the attributes of the corresponding entity.

33. What is trigger?

• A Trigger is a code that associated with insert, update or delete operations. The
code is executed automatically whenever the associated query is executed on a
table. Triggers can be useful to maintain integrity in database.
• A trigger is a special type of stored procedure that automatically runs when an
event occurs in the database server. DML triggers run when a user tries to
modify data through a data manipulation language (DML) event.
• A trigger is called a special procedure because it cannot be called directly like a
stored procedure. The key distinction between the trigger and procedure is that
a trigger is called automatically when a data modification event occurs against
a table. A stored procedure, on the other hand, can be invoked explicitly by the
user.
34. What is Corelated Subquery in DBMS?

A Subquery is also known as a nested query i.e. a query written inside some
query. When a Subquery is executed for each of the rows of the outer query
then it is termed as a Correlated Subquery.

Example of Non-Correlated Subquery is: Select * from EMP where „RIYA‟ IN

(Select Name from DEPT where EMP.EMPID=DEPT.EMPID)

35. What is a view in SQL? How to create one?

A view is a virtual table based on the result-set of an SQL statement. We can

create using create view.

syntax. CREATE VIEW view_name AS SELECT column_name(s) FROM

table_name WHERE condition

36. What’s difference between DBMS and RDBMS?

DBMS provides a systematic and organized way of storing, managing and

retrieving from collection of logically related information. RDBMS also provides
what DBMS provides but above that it provides relationship integrity.

37. What is transparent DBMS?

It is one, which keeps its physical structure hidden from user.

38. Explain the difference between an exclusive lock and a shared lock?

An exclusive lock prohibits other users from reading the locked resource; a
shared lock allows other users to read the locked resource, but they cannot
update it.

39. What is Generalization and Specialization?

o Generalization is like a bottom-up approach in which two or more entities of

lower level combine to form a higher level entity if they have some attributes in
common.

For example, Faculty and Student entities can be generalized and create a
higher level entity Person.
 o Specialization is a top-down approach, and it is opposite to Generalization. In
specialization, one higher level entity can be broken down into two lower level
entities.

For example: In an Employee management system, EMPLOYEE entity can be

specialized as TESTER or DEVELOPER based on what role they play in the
company.

40. What is Aggregation?

In aggregation, the relation between two entities is treated as a single entity. In

aggregation, relationship with its corresponding entities is aggregated into a higher
level entity.

41. What are various Aggregate functions in dbms?

o SQL aggregation function is used to perform the calculations on multiple rows

of a single column of a table. It returns a single value.
o It is also used to summarize the data.

1. COUNT FUNCTION

o COUNT function is used to Count the number of rows in a database table. It

can work on both numeric and non-numeric data types.
o COUNT function uses the COUNT(*) that returns the count of all the rows in
a specified table. COUNT(*) considers duplicate and Null.

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on
numeric fields only.

Example: SUM()

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3;

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG
function returns the average of all non-Null values.
 4. MAX Function

MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.

42. What is clustering?

• Database Clustering is the process of combining more than one servers or

instances connecting to a single database. Sometimes one server may not be
adequate to manage the amount of data or the number of requests, that is
when a Data Cluster is needed.
• Clustering takes different forms, depending on how the data is stored and
allocated resources.

43. How to copy data from one table to another table?

• create table new_table as ( select * from old_table);

and then insert

• insert into new_table ( select * from old_table);

44. Difference between primary key and foreign key?

45. What is checkpoint in dbms?

Checkpoints are used for the recovery of the database after the system crash.
Checkpoints are used in the log-based recovery system. When due to a system crash
we need to restart the system then at that point we use checkpoints. So that, we don’t
have to perform the transactions from the very starting.

46. What do you understand by Data Model?

Data models define how the logical structure of a database is modeled. Data Models
are fundamental entities to introduce abstraction in a DBMS. Data models define how
data is connected to each other and how they are processed and stored inside the
system.

E.g: Entity-Relationship Model, Relational Model.

47. What is the difference between Cluster and Non-Cluster Index?

• Clustered index is used for easy retrieval of data from the database by altering
the way that the records are stored. Database sorts out rows by the column
which is set to be clustered index.

• A non-clustered index does not alter the way it was stored but creates a
complete separate object within the table. It points back to the original table
rows after searching.

48. Difference between the DELETE and TRUNCATE command in a DBMS.

DELETE command:
1) This command is needed to delete rows from a table based on the
condition provided by the WHERE clause.
2) It can be rolled back if required.
3) It maintains a log to lock the row of the table before deleting it and hence
it’s slow.

TRUNCATE command:
1) This command is needed to remove complete data from a table in a
database. It is like a DELETE command which has no WHERE clause.
2) It removes complete data from a table in a database.
3) It can be rolled back even if required.
4) It doesn’t maintain a log and deletes the whole table at once and hence it’s
fast.