DBMS IMP Q and A

1. Write short note on: Data, Database, Database Management, Data Abstraction, Data
Independence
• Data: Data is any information that can be stored, manipulated, and retrieved by a
computer system. It can be in the form of text, numbers, images, audio, or video.
• Database: A database is a collection of data that is organized in a structured way, so that
it can be easily accessed, managed, and updated. Databases are used to store large
amounts of data that can be accessed by multiple users simultaneously.
• Database Management: Database Management refers to the process of organizing,
storing, and retrieving data from a database. It involves creating and managing the
database schema, defining the data types and relationships, and implementing security
and access controls.
• Data Abstraction: Data Abstraction is a technique used in database management to hide
the complexities of the database structure from the users. It provides a simplified view of
the database schema, making it easier for users to interact with the database. Abstraction
also enables changes to be made to the database schema without affecting the
applications that use it.
• Data Independence: Data Independence is the ability to make changes to the database
schema without affecting the applications that use it. Data independence is important in
database management because it allows for changes to be made to the database structure
without disrupting the applications that use it, which can save time and reduce costs.
There are two types of data independence: logical and physical. Logical data independence
refers to the ability to modify the logical schema of the database without affecting the
application programs that use it. Physical data independence refers to the ability to modify
the physical schema of the database without affecting the logical schema or the
application programs that use it.

2. Explain Database Management System with its application

A database management system (DBMS) is a software system that allows users to create, store,
and manage databases. It is designed to manage large volumes of data and provide fast,
efficient access to that data.
Applications of DBMS:
• Banking: DBMS is used in the banking industry to manage customer information, account
details, and transactions.
• Retail: DBMS is used in the retail industry to manage inventory, sales, and customer
information.
• Healthcare: DBMS is used in the healthcare industry to manage patient information,
medical records, and billing information.
• Manufacturing: DBMS is used in the manufacturing industry to manage inventory,
production data, and quality control.
• Education: DBMS is used in the education industry to manage student information, class
schedules, and grades.

1
 3. Explain Characteristics of DBMS
A Database Management System (DBMS) is a software system that enables users to create,
maintain, and manipulate databases. Here are some of the key characteristics of a DBMS:
• Data Independence: A DBMS is designed to provide data independence, which means that
the data is separated from the application that uses it. This allows changes to be made to
the database schema without affecting the applications that use the data.
• Data Security: A DBMS provides various security mechanisms to protect the data from
unauthorized access, modification, or destruction. It allows the database administrator to
assign user permissions and access levels to ensure the security of the data.
• Data Consistency: A DBMS ensures that the data stored in the database is consistent and
accurate. It provides mechanisms to enforce data integrity constraints, such as unique
keys, foreign keys, and check constraints.
• Concurrent Access: A DBMS allows multiple users to access the database simultaneously.
It provides mechanisms to ensure that transactions are executed in a concurrent and
consistent manner, such as through locking and transaction isolation levels.
• Backup and Recovery: A DBMS provides mechanisms for backup and recovery of the
database in case of system failures, disasters, or other unexpected events.
• Query Language: A DBMS provides a query language that allows users to retrieve and
manipulate data stored in the database. The most commonly used query language for
relational databases is SQL (Structured Query Language).
• Scalability: A DBMS is designed to scale to meet the growing demands of users and data.
It allows the addition of new data, users, and resources without affecting the performance
of the system.
4. Difference between database management system and file processing system
Basis File System DBMS
Structure The file system is software that manages DBMS is software for managing the
and organizes the files in a storage database.
medium within a computer.
Complexity It is less complex as compared to DBMS. It has more complexity in handling as
compared to the file system.
Cost It is less expensive than DBMS. It has a comparatively higher cost than
a file system.
Backup and It doesn’t provide backup and recovery It provides backup and recovery of data
Recovery of data if it is lost. even if it is lost.
Security File systems provide less security in DBMS has more security mechanisms
Constraints comparison to DBMS. as compared to file systems.
User Access Only one user can access data at a time. Multiple users can access data at a
time.
Sharing Data is distributed in many files. So, not Due to centralized nature sharing is
easy to share data easy
Data There is no data independence. In DBMS data independence exists.
Independence
Data It gives details of storage and It hides the internal details of Database
Abstraction representation of data
Data Redundant data can be present in a file In DBMS there is no redundant data.
Redundancy system.
Data There is less data consistency in the file There is more data consistency
Consistency system. because of the process of
normalization.
Example Cobol, C++ Oracle, SQL Server

2
5. Explain different database users
Types of Database users based on their level of expertise and familiarity with the database
system are:

• Naive users: Naive users are those who have little to no experience with database systems.
They may use pre-built applications or tools to interact with the database, such as online
forms or query builders. Naive users may only have access to a limited set of data and may
not have the ability to modify or create new data.

• Application programmers: Application programmers are responsible for writing code that
interacts with the database system. They have a deeper understanding of the database
schema and may need to optimize queries for performance. Application programmers
typically have the ability to create, modify, and delete data from the database.

• Sophisticated users: Sophisticated users are those who have a good understanding of
database systems and can write complex queries to extract and manipulate data. They
may use specialized tools or programming languages to interact with the database, and
they have the ability to create, modify, and delete data.

• Specialist users: Specialist users are experts in a particular domain, such as data analytics
or data modelling. They may use specialized tools or techniques to extract insights from
the database, and they have a deep understanding of the data structures and relationships
within the database. Specialist users may also have the ability to create, modify, and delete
data, as well as make changes to the database schema.

6. Explain ER model symbols

3
7. Explain overall architecture of DBMS in detail
The overall architecture of a Database Management System (DBMS) can be divided into three
main components:
1) Query Processing Components:
I. DDL interpreter: It interprets DDL statements and records the definition.
II. DML Compiler: It translates DML statements in a query language and then to low
level instructions.
III. Query Evaluation engine: It executes low level instructions
2) Storage Manager:
• Storage Manager is a program module that provides interface between low level data
stored & query processor.
• The storage manager is responsible to the following tasks:
- Interaction with the file manager
- Efficient storing, retrieving and updating of data
I. File Manager: It manages the allocation of space on disk.
II. Buffer Manager: It is responsible for fetching of data from disk.
III. Authorization & Integrity Manager: It checks authority of a user & integrity of
data.
IV. Transaction Manager: It ensures that the database remains in a consistent
(correct) state despite system failures (e.g., power failures and operating system
crashes) and transaction failures.
3) Disk Storage:
I. Data Files: It stores the database itself.
II. Data Dictionary: It stores metadata. i.e., data about data
III. Indices: It provides fast access to data items.

4
8. Describe Data Abstraction with neat diagram

Physical Level:
• It is the lowest level of abstraction
• It describes how the data is actually stored and describes the data structure and access
methods to be used by the database. i.e. (files, indices, et al.)
• It is also called as internal level
Conceptual Level:
• It is the next higher level of abstraction
• It describes What data are is actually stored in the database and relationship between
data.
• It is also called as middle level
View Level:
• It is the highest level of abstraction
• It describes only part of entire database.
• It is also called as External level.

9. Explain Instance and schema

Instance: Collection of information stored in the database at a
particular moment of time is called as instance of db.
Schema: The overall design of the database is called as schema.

10. Explain Data Independence and its types

Data independence refers to the ability to modify the schema or the physical organization of
data without affecting the way that applications or users access the data. In other words, it
allows modifications to the database structure without impacting the application programs or
users who access the data.
There are two types of data independence:
• Physical data independence: Physical data independence is the ability to change the
physical storage structure of data without affecting the logical schema. This means that
the way data is stored on the hard disk can be changed without impacting the way that
users or applications access the data. For example, adding a new index to a table or
changing the order of columns in a table can be done without affecting the application.
• Logical data independence: Logical data independence is the ability to change the logical
schema of the data without affecting the application programs that use the data. This
means that changes can be made to the way data is organized without impacting the way
applications access the data. For example, adding a new table, modifying an existing table,
or changing the relationship between tables can be done without affecting the application.

5
11. Write a short note on Database Administrator
A Database Administrator (DBA) is responsible for managing the databases of an
organization. They play a crucial role in ensuring the integrity, security, and availability of
data. Here are some of the key responsibilities of a DBA:
1) Database Design: DBAs are responsible for designing the database schema, defining the
data model, and determining how the data will be stored and accessed.
2) Database Installation and Configuration: DBAs are responsible for installing and
configuring the database management system software, including setting up the
necessary hardware and network infrastructure.
3) Database Backup and Recovery: DBAs are responsible for developing and implementing
a backup and recovery plan to ensure the availability and integrity of data in the event of
system failure or data loss.
4) Database Performance Tuning: DBAs are responsible for optimizing the performance of
the database by tuning queries, indexing, and other database parameters.
5) Database Security: DBAs are responsible for ensuring the security of the database by
implementing access control mechanisms, auditing, and monitoring.
6) Database Monitoring and Maintenance: DBAs are responsible for monitoring the
database for errors and performance issues, and performing regular maintenance tasks
such as database defragmentation and updates.
7) Database Migration and Upgrades: DBAs are responsible for migrating the database to
new hardware or software, and upgrading the database management system to newer
versions.
8) Database Documentation: DBAs are responsible for documenting the database
structure, data model, and data dictionary to ensure that other IT professionals and
stakeholders understand how to access and use the database.

12. Write Short note on: Entity, Entity set, Types of entities
a) Entity: In the Entity-Relationship (ER) model, an entity is a real-world object that has
properties that can be stored in a database. An entity can represent a person, place, thing,
concept, or event. Entities are represented as rectangles in the ER diagram.
b) Entity Set: An entity set is a collection of similar entities. For example, the entity set
"students" would contain all the individual student entities in a university. Entity sets are
represented as a double-lined rectangle in the ER diagram.
c) Types of entities:
• Strong Entity: A strong entity is an entity that exists independently and has its own
unique identity. It can be identified by its own attributes, as well as its relationship with
other entities in the database.
• Weak Entity: A weak entity is an entity that cannot be identified by its own attributes
alone, but depends on another entity for its existence. It is identified by its relationship
with another entity, known as its owner.

6
13. Explain attributes and types of attributes with example
In a database management system (DBMS), an attribute is a characteristic or property of an
entity. Attributes describe the entities in a database and can be used to distinguish one entity
from another. There are different types of attributes that can be used in a database schema,
including:
1) Simple attribute: A simple attribute is an attribute that cannot be further subdivided. For
example, in a database of students, "name" and "age" would be simple attributes.
2) Composite attribute: A composite attribute is an attribute that can be further subdivided
into smaller attributes. For example, in a database of students, "address" can be further
subdivided into "street", "city", "state", and "zip code".
3) Derived attribute: A derived attribute is an attribute that can be calculated from other
attributes in the database. For example, in a database of employees, "salary" might be a
derived attribute calculated based on the employee's position and years of experience.
4) Key attribute: A key attribute is an attribute that uniquely identifies each entity in a
database. For example, in a database of students, "student ID" might be the key attribute
that identifies each individual student.
5) Multivalued attribute: A multivalued attribute is an attribute that can have multiple values
for a single entity. For example, in a database of employees, an employee might have
multiple skills, which could be represented as a multivalued attribute.
6) Null attribute: A null attribute is an attribute that has no value assigned to it. It can be
used to represent missing or unknown data.

14. Explain types of keys with example

In a database management system (DBMS), a key is an attribute or set of attributes that
uniquely identifies a record in a table. There are different types of keys that can be used in a
database schema, including:
1) Primary key: A primary key is a unique identifier for a record in a table. It must have a
unique value for each record and cannot be null. For example, in a database of students,
the student ID might be the primary key for the "students" table.
2) Foreign key: A foreign key is an attribute in a table that refers to the primary key of another
table. It is used to establish relationships between tables. For example, in a database of
courses, the instructor ID might be a foreign key that references the primary key of the
"instructors" table.
3) Candidate key: A candidate key is a set of attributes that can be used as a primary key for
a table. It must have a unique value for each record and cannot be null. For example, in a
database of employees, the employee ID and the social security number might both be
candidate keys for the "employees" table.
4) Alternate key: An alternate key is a candidate key that is not selected to be the primary
key. For example, in a database of customers, the email address might be an alternate key
if it is unique for each customer but the customer ID is selected as the primary key.
5) Super key: A super key is a set of attributes that includes a candidate key and additional
attributes. It can uniquely identify a record in a table, but not necessarily in the most
efficient way. For example, in a database of students, a super key might include the student
ID and the student's name.
6) Composite key: A composite key is a key that consists of multiple attributes. For example,
in a database of orders, the order number and the order date might be used as a composite
key to uniquely identify each order.

7
15. Explain Relationship mapping constraints/cardinalities
Relationship mapping constraints, also known as cardinalities, are used to define the type of
relationship that exists between two entities in an entity-relationship (ER) model.
There are three cardinality types:
• One-to-One (1:1) Cardinality: In this type of cardinality, one instance of an entity is
associated with only one instance of another entity. For example, a person can have only
one passport, and a passport can be issued to only one person.

• One-to-Many (1:N) Cardinality: In this type of cardinality, one instance of an entity is

associated with multiple instances of another entity. For example, a customer can place
many orders, but an order can be placed by only one customer.

• Many-to-one (N:1) relationship: In a many-to-one relationship, many records in one table

are associated with one record in another table. For example, in a database of orders and
customers, many orders might be associated with one customer.

• Many-to-Many (N:M) Cardinality: In this type of cardinality, multiple instances of an entity

are associated with multiple instances of another entity. For example, students can enrol
in many courses, and each course can have many students.

8
16. Explain participation constraints and its types
• Total Participation constraint:
When all entities of entity set participate in relationship set then it is called as Total
participation constraint.
• Partial Participation constraint:
When some entities of entity set participating in relationship set then it is called as Partial
participation constraint.

17. Explain degree of relationship and its types

The degree of relationship refers to the number of entities involved in a relationship in an
entity-relationship (ER) model. There are three types of relationships based on degree:
• Unary Relationship: A unary relationship exists when an entity is associated with itself.
This relationship is also known as a recursive relationship. For example, an employee can
be a manager of another employee in the same organization.

• Binary Relationship: A binary relationship exists between two entities. It is the most
common type of relationship in ER modelling. For example, a customer places an order.

• Ternary Relationship: A ternary relationship exists between three entities. It is less

common than a binary relationship. For example, a student enrols in a course taught by a
professor.

• N-ary Relationship: An n-ary relationship involves more than three entity types. A
relationship may exist between a "customer" entity, an "order" entity, and a "product"
entity in a retail database. This is an example of an n-ary relationship because it involves
more than three entity types.

9
18. Explain Extended ER Features with suitable example
Features of Extended ER are:
• Specialization: Specialization is the process of defining a set of subclasses from a
superclass. 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.

• Generalization: Generalization is the process of defining a superclass is defined from a set

of subclasses. For example, Faculty and Student entities can be generalized and create a
higher-level entity, Person.

• Attribute Inheritance: Attribute inheritance is the process of defining attributes in a

superclass that are inherited by its subclasses.
• Aggregation: Aggregation is the process of defining a new entity by combining multiple
entities and treating them as a single entity. For example, we know that a manager not
only manages the employee working under them but he has to manage the project as well.
In such scenario if entity “Manager” makes a “manages” relationship with either
“Employee” or “Project” entity alone then it will not make any sense because he has to
manage both. In these cases, the relationship of two entities acts as one entity. In our
example, the relationship “Works-On” between “Employee” & “Project” acts as one entity
that has a relationship “Manages” with the entity “Manager”.

10
19. Write short note on: Relational model, Relation, Relationship, Attributes, Tuple,
Domain
a) Relational Model: The relational model is a database model that represents data as a
collection of tables, also known as relations. This model is based on mathematical set theory
and uses a set of operations to manipulate data.
b) Relation: A relation is a table in a relational database that consists of a set of tuples with
each tuple representing a row in the table. Each column in the table represents an attribute,
and each row represents a unique combination of attribute values.
c) Relationship: A relationship is a connection between two or more tables in a relational
database. Relationships are established based on the common attributes between tables
and are typically represented by foreign keys.
d) Attributes: Attributes are characteristics of the entities represented in a table, and they
define the columns of the table. Each attribute has a name and a data type, such as integer
or string.
e) Tuple: A tuple is a single row in a relation and represents a unique combination of attribute
values. Each attribute value in a tuple corresponds to a column in the relation.
f) Domain: A domain is a set of possible values for a given attribute. The domain of an attribute
specifies the range of values that the attribute can take on. For example, the domain of a
"birth date" attribute might be dates from January 1, 1900, to the current date.
20. Explain Relational Algebra operators with suitable example
Relational algebra is a set of operations used to manipulate data in a relational database. Here
are some of the most common relational algebra operators along with examples:
• Selection: The selection operator is used to select a subset of tuples from a relation that
satisfies a given condition. It is denoted by the sigma symbol (σ). For example, if we have
a relation "Customers" with attributes "CustomerID", "Name", "Age", and "City", we can
use the selection operator to retrieve all customers who are over the age of 30: σ(Age >
30)(Customers)
• Projection: The projection operator is used to select a subset of columns from a relation.
It is denoted by the pi symbol (π). For example, if we have a relation "Orders" with
attributes "OrderID", "CustomerID", "ProductID", and "Quantity", we can use the
projection operator to retrieve only the "OrderID" and "CustomerID" columns: π(OrderID,
CustomerID)(Orders)
• Union: The union operator is used to combine two relations into a single relation, keeping
only distinct tuples. It is denoted by the U symbol. For example, if we have two relations
"A" and "B" with the same attributes, we can use the union operator to combine them: A
UB
• Intersection: The intersection operator is used to find the common tuples between two
relations. It is denoted by the ∩ symbol. For example, if we have two relations "A" and "B"
with the same attributes, we can use the intersection operator to find the tuples that are
present in both relations: A ∩ B
• Difference: The difference operator is used to find the tuples that are present in one
relation but not in another relation. It is denoted by the - symbol. For example, if we have
two relations "A" and "B" with the same attributes, we can use the difference operator to
find the tuples that are in "A" but not in "B": A - B
• Join: The join operator is used to combine two relations into a single relation based on a
common attribute. It is denoted by the ⋈ symbol. For example, if we have two relations
"Orders" and "Customers" with a common attribute "CustomerID", we can use the join
operator to combine them into a single relation:
Orders ⋈ Customers

11
21. Solved ER Model Problem with Case Study

22. Mapping Rules of ER model to Relational Model

Rule-1: For Strong Entity Set with Simple Attributes and composite attribute:
- Each strong entity set becomes a Table.
- Each simple, single, null attribute becomes a column of table.
- Composite attributes are represented by components.
- The key attribute of the entity set becomes primary key of the table.

Rule-2 : Strong Entity Set With Multi Valued Attributes

- Multi-valued attributes are represented by a separate table
- One table will contain all the simple attributes with the primary key.
- Another table will contain the primary key and all the multi valued attributes.

12
Rule 3-: For Binary Relationship with Cardinality Ratio 1:1
- The primary key of the either of the participants can become a foreign key in other.

Rule 4-: For Binary Relationship With Cardinality Ratio 1:n

- The primary key of the entity on the “1” side of the relationship set becomes a foreign
key in the relation on the “N” side.

Rule 5-: For Binary Relationship with Cardinality Ratio m:1

- The primary key of the entity on the “1” side of the relationship set becomes a foreign
key in the relation on the “N” side.

13
Rule 6-: For Binary Relationship with Cardinality Ratio m:n
- A new table is created to represent relationship set. (Contains two foreign keys each
from left and right strong entity set.)
- The primary key of the new table is the combination of two foreign keys.

Rule 7-: Translating Relationship Set into a Table Attributes of the table are-
- Primary key attributes of the participating entity set
- Its own descriptive attributes if any.
- Set of non-descriptive attributes will be the primary key.

Rule 8-: For Binary Relationship with Weak Entity Set-

– Weak entity set always appears in association with identifying relationship with total
participation constraint.

14
23. Solved Relational model Problems

24. Solved Relational Algebra Problems

15
25. What is SQL? What Can SQL do?
• SQL (Structured Query Language) is a programming language used to manage and manipulate
relational databases. SQL is used to create, modify, and manage databases, as well as to insert,
update, and retrieve data from those databases.
• Some of the thing’s SQL can do include:
• Creating and modifying databases: SQL can be used to create and modify the structure of a
database, including tables, columns, indexes, and constraints.
• Inserting and updating data: SQL can be used to insert new data into a database or update
existing data.
• Retrieving data: SQL can be used to query a database to retrieve data based on certain criteria.
• Sorting and filtering data: SQL can be used to sort and filter data retrieved from a database.
• Aggregating data: SQL can be used to aggregate data, such as calculating the average, sum, or
count of a set of data.
• Joining tables: SQL can be used to join two or more tables in a database based on a common
column, allowing you to retrieve data from multiple tables at once.
26. Categories of SQL command

Types Of SQL Commands

DDL DML DCL TCL
(Data Definition (Data Manipulation (Data Control (Transaction Control)
Language) Language) Language)
CREATE SELECT GRANT COMMIT
ALTER INSERT REVOKE ROLLBACK
DROP UPDATE SAVEPOINT
TRUNCATE DELETE
RENAME MERGE

27. Explain DDL commands with example

DDL (Data Definition Language) commands are used to define the structure of a database,
including creating and modifying tables, indexes, and other database objects. Here are some
examples of DDL commands:
• CREATE TABLE: This command is used to create a new table in a database.
Ex: CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50) );
• ALTER TABLE: This command is used to modify an existing table in a database.
Ex: ALTER TABLE customers ADD phone VARCHAR(20);
• DROP TABLE: This command is used to delete the existing table. It removes the complete data
of a table along with the whole structure or definition permanently from the database.
Ex: DROP TABLE customers;
• TRUNCATE TABLE: This command is used to remove the complete data without removing its
structure.
Ex: TRUNCATE TABLE customers;
• RENAME TABLE: This command is used to rename existing table name with new table name.
Ex: RENAME TABLE customers to new_customers;

16
28. Entity Integrity constraint:
• The term Data integrity refers to the correctness and completeness of the data in a database.
• Entity Integrity constraint are the part of the table definition that are used to limit the values
entered into the columns.
• There are Six types of Entity Integrity constraint

Sr. Entity Integrity Purpose

No. Constraint
1 NOT NULL This constraint specifies that the column cannot have NULL or
empty values. The below statement creates a table with NOT
NULL constraint.
2 UNIQUE This constraint ensures that all values inserted into the column
will be unique. It means a column cannot stores duplicate
values. MySQL allows us to use more than one column with
UNIQUE constraint in a table.
3 CHECK It controls the value in a particular column. It ensures that the
inserted value in a column must be satisfied with the given
condition.
4 DEFAULT This constraint is used to set the default value for the particular
column where we have not specified any value.
5 PRIMARY KEY This constraint is used to identify each record in a table
uniquely. If the column contains primary key constraints, then
it cannot be null or empty.
6 FOREIGN KEY This constraint is used to link two tables together. It is also
known as the referencing key. A foreign key column matches
the primary key field of another table. It means a foreign key
field in one table refers to the primary key field of Another
Table.

29. Explain DML commands with example

DML (Data Manipulation Language) commands are used to manipulate data within a database.
These commands allow you to insert, update, delete, and retrieve data from tables in the
database.
Here are some examples of DML commands:
• INSERT: This command is used to insert new data into a table.
Ex: INSERT INTO customers (name, email) VALUES ('John Smith', 'john.smith@example.com');
• UPDATE: This command is used to update existing data in a table.
Ex: UPDATE customers SET email = 'john.smith@newemail.com' WHERE name = 'John Smith';
• DELETE: This command is used to delete data from a table.
Ex: DELETE FROM customers WHERE email = 'john.smith@newemail.com';
• SELECT: This command is used to retrieve data from one or more tables.
Ex: SELECT * FROM customers WHERE name = 'John Smith';

17
30. Explain Special Operator in DML with example
Special operators in DML (Data Manipulation Language) are used to perform specific operations
on data, such as grouping data, or filtering results based on a condition.
Here are some examples of special operators in DML:
• GROUP BY operator: This operator is used to group data based on one or more columns.
Ex: SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY
department_id;
• HAVING operator: This operator is used to filter results based on a condition that applies to
the results of a GROUP BY operation.
Ex: SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY
department_id HAVING AVG(salary) > 50000;
• IN operator: This operator is used to filter results based on a set of values.
Ex: SELECT * FROM employees WHERE department_id IN (10, 20);
• LIKE operator: This operator is used to filter results based on a pattern.
Ex: SELECT * FROM employees WHERE last_name LIKE 'S%';
• BETWEEN operator: This operator is used to filter results based on a range of values.
Ex: SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

31. Types of Built-in functions in SQL

Aggregate Functions: Aggregate functions are used to perform a calculation on a set of values and
return a single value.
The following are the types of aggregate functions in SQL:
• COUNT(): Returns the number of rows that match a specified condition.
Ex: SELECT COUNT(*) FROM employees;
• SUM(): Returns the sum of a column of numeric values.
Ex: SELECT SUM(salary) FROM employees;
• AVG(): Returns the average of a column of numeric values.
Ex: SELECT AVG(salary) FROM employees;
• MAX(): Returns the maximum value in a column of values.
Ex: SELECT MAX(salary) FROM employees;
• MIN(): Returns the minimum value in a column of values.
Ex: SELECT MIN(salary) FROM employees;

Arithmetic/Numeric Functions: Arithmetic or Numeric functions are used to perform arithmetic

operations on numeric data types.
The following are the types of arithmetic functions in SQL:
• ROUND(): Rounds a number to a specified number of decimal places.
Ex: SELECT ROUND(3.14159265, 2);
• ABS(): Returns the absolute value of a number.
Ex: SELECT ABS(-10);
• CEIL(): Returns the smallest integer greater than or equal to a number.
Ex: SELECT CEIL(3.14159);
• FLOOR(): Returns the largest integer less than or equal to a number.
Ex: SELECT FLOOR(3.14159);

18
Character/String Functions:
Character or String functions are used to manipulate character or string data types.
The following are the types of character functions in SQL:
• CONCAT(): Concatenates two or more strings.
Ex: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
• UPPER(): Converts a string to uppercase.
Ex: SELECT UPPER(first_name) FROM employees;
• LOWER(): Converts a string to lowercase.
Ex: SELECT LOWER(last_name) FROM employees;
• LENGTH(): Returns the length of a string.
Ex: SELECT LENGTH(first_name) FROM employees;

Date Functions:
Date functions are used to manipulate date and time data types.
The following are the types of date functions in SQL:
• NOW(): Returns the current date and time.
Ex: SELECT NOW();
• DATE(): Extracts the date part from a date/time value.
Ex: SELECT DATE('2022-03-15 13:15:00');
• YEAR(): Returns the year part of a date/time value.
Ex: SELECT YEAR('2022-03-15');
• MONTH(): Returns the month part of a date/time value.
Ex: SELECT MONTH('2022-03-15');

32. Explain Join and its types with example

Join is an operation that combines data from two or more tables based on a common column. The
purpose of a join is to retrieve information that is spread across multiple tables in a single result
set.
There are different types of joins, each serving a specific purpose:

• INNER JOIN: This type of join returns only the rows that have matching values in both tables.
It uses the JOIN keyword and can be written as follows:
SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

• LEFT JOIN: This type of join returns all the rows from the left table and the matching rows from
the right table. If there are no matching rows in the right table, the result set will contain NULL
values. It uses the LEFT JOIN or LEFT OUTER JOIN keywords and can be written as follows:
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

19
• RIGHT JOIN: This type of join is similar to the left join, but returns all the rows from the right
table and the matching rows from the left table. If there are no matching rows in the left table,
the result set will contain NULL values. It uses the RIGHT JOIN or RIGHT OUTER JOIN keywords
and can be written as follows:
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
• FULL OUTER JOIN: This type of join returns all the rows from both tables, along with matching
rows from each table. If there are no matching rows in either table, the result set will contain
NULL values. It uses the FULL OUTER JOIN or FULL JOIN keywords and can be written as follows:
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
• SELF JOIN: This type of join where a table is joined with itself. This can be useful when the
table contains hierarchical data or when you want to compare different rows within the same
table. To perform a self-join, you need to use table aliases to distinguish between the two
instances of the same table. Here is an example of a self-join:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

33. Explain Union, Intersect, Minus, Nested with example

In SQL, there are several set operators that allow you to combine the results of two or more SELECT
statements. These set operators include UNION, INTERSECT, and MINUS (or EXCEPT in some
databases). In addition, you can also use nested queries to perform more complex operations.
• UNION: The UNION operator is used to combine the results of two SELECT statements into a
single result set. The two SELECT statements must have the same number of columns, and the
data types of the corresponding columns must be compatible.
Ex: SELECT name, city FROM customers UNION SELECT name, city FROM suppliers;
• INTERSECT: The INTERSECT operator is used to retrieve only the rows that appear in both
SELECT statements. Again, the two SELECT statements must have the same number of
columns, and the data types of the corresponding columns must be compatible.
Ex: SELECT city FROM customers INTERSECT SELECT city FROM suppliers;
• MINUS (or EXCEPT): The MINUS (or EXCEPT) operator is used to retrieve only the rows that
appear in the first SELECT statement but not in the second SELECT statement. As with the other
set operators, the two SELECT statements must have the same number of columns, and the
data types of the corresponding columns must be compatible.
Ex: SELECT city FROM customers MINUS SELECT city FROM suppliers;
• NESTED QUERIES: Nested queries allow you to use the results of one query as input for
another query. This can be useful for performing more complex operations, such as subqueries
or correlated queries.
Ex: SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders
WHERE order_date >= DATEADD(month, -1, GETDATE()));

20
34. Explain Grant and Revoke commands with example
In SQL, security is an important aspect, and there are commands to grant or revoke permissions
on database objects. These permissions can be granted to users or roles to allow or restrict access
to certain database objects. The following are the commonly used commands in SQL for granting
and revoking security:
• GRANT: The GRANT command is used to give permissions to users or roles on specific database
objects.
Syntax: GRANT <permission> ON <object> TO <user or role>
Ex: GRANT SELECT ON employees TO sales;
• REVOKE: The REVOKE command is used to take away permissions from users or roles on
specific database objects.
Syntax: REVOKE <permission> ON <object> FROM <user or role>
Ex: REVOKE SELECT ON employees FROM sales;

35. Explain Normalization and its types

• Normalization is a database design technique that reduces data redundancy and eliminates
undesirable characteristics like Insertion, Update and Deletion Anomalies.
• Normalization rules divides larger tables into smaller tables and links them using relationships.
• The purpose of Normalization is to eliminate redundant (repetitive) data and ensure data is
stored logically.
• Normalization is process to convert bad database to good database design.
• Database Normal Forms
a) 1NF (First Normal Form)
If and only if all attributes of the Relation R are atomic in nature.
b) 2NF (Second Normal Form)
- R is said to in second normal form if and only if:
- It is in the 1NF.
- No Partial dependency exists between non-key attributes and Key attribute.
- Group such partial dependent attributes as a separate table and identify Key and name
of newly created table.
c) 3NF (Third Normal Form)
- R is said to in second normal form if and only if:
- It is in the 2NF.
- No Transitive dependency exists between non-key attributes and Key attribute.
- Group such Transitive dependent attributes as a separate table and identify Key and name
of newly created table.
d) BCNF (Boyce-Codd Normal Form)
A table is in Boyce-Codd normal form (BCNF) if every determinant in the table is a
candidate key.
e) 4NF (Fourth Normal Form)
- R is said to in second normal form if and only if :
- It is in the 3NF.
- No Multivalued dependency exists between non-key attributes and Key attribute.
- Group such Multivalued attributes as a separate table and identify Key and name of newly
created table.

21
22
36. Explain Anomalies and its types
• Insert Anomaly: If you want to add new branch details to the relation. The new branch details
can be added when customer takes loan.
• Update Anomaly: If we want to update the assets of D Branch then we have to update the
same amount in two rows of D branch or the data will become inconsistent. If somehow, the
correct assets get updated in one Branch of D but not in other then as per the database,
Branch would be having two different assets, which is not correct and would lead to
inconsistent data.
• Delete Anomaly: Suppose, if at a point of time the L-23 of R branch Paid all loan amount then
deleting the rows that is having L-23 loan-no would also delete the information of Branch and
customer since ‘S’ customer is only one to this Branch.
37. Terminologies for normalization:
• Armstrong's Axioms:
- If F is a set of functional dependencies, then the closure set of F, denoted as F+, is the set
of all functional dependencies logically implied by F.
- Armstrong's Axioms are a set of rules, that when applied repeatedly, generates a closure
of functional dependencies.
• Reflexive rule − If alpha is a set of attributes and beta issubset alpha, then alpha holds beta.
• Augmentation rule − If a → b holds and y is attribute set, then ay → by also holds.
• Union Rule − If a → b and a → y holds then a → by.
• Decomposition Rule − If a → by holds then a → b and a → y.
• Pseudo transitivity Rule − If a → b and yb → d holds then ay → d.
• Transitivity Rule − If a → b and b → y then a → y.

23
38. Explain Transaction
A transaction is a unit of program execution that accesses and possibly updates
various data items
• Transaction to transfer $50 from account A to account B:
read(A)
A := A – 50
write(A)
read(B)
B := B + 50
write(B)

39. Explain Transaction State Diagram

Transaction States:
• Active: the initial state; the transaction stays in this state while it is executing
• Partially committed: after the final statement has been executed.
• Failed: after the discovery that normal execution can no longer proceed.
• Aborted: after the transaction has been rolled back and the database restored to its state prior
to the start of the transaction.
Two options after it has been aborted:
- restart the transaction – only if no internal logical error
- kill the transaction
- Committed: after successful completion.

40. Explain ACID Properties

To ensure the integrity of the data, we required that database system must maintain the following
properties:
ACID Properties:
1) Atomicity: Either all operations of the transaction are properly reflected in the database or
none are.
- Responsibility: Transaction Management Component
2) Consistency: Execution of a transaction in isolation preserves the consistency of the database.
- Responsibility: Application programmer
3) Isolation: Although multiple transactions may execute concurrently, each transaction must be
unaware of other concurrently executing transactions.
- Responsibility: Concurrency control Component
4) Durability: After a transaction completes successfully, the changes it has made to the database
persists, even if there are system failures.
- Responsibility: Recovery Management Component

24
41. Explain Serial Execution with example

42. Explain Concurrent execution with example

25
43. Explain Serializability and its types
• We know that serial execution of a set of transactions preserves database consistency and
concurrent schedule may not.
• So Serializability technique is used to ensure which concurrent schedule will maintain the
consistency or not.
• But Concurrent schedule result in a consistent state if it is equivalent to serial schedule of that
transaction and such Concurrent schedule is known as Serializability.
There are 2 forms of Serializability:
Conflict Serializability
Instructions li and lj of transactions Ti and Tj respectively, conflict if and only if there exists
some item Q accessed by both li and lj, and at least one of these instructions wrote Q.
a) li = read(Q), lj = read(Q). : li and lj don’t conflict.
b) li = read(Q), lj = write(Q). : They conflict.
c) li = write(Q), lj = read(Q). : They conflict
d) li = write(Q), lj = write(Q). : They conflict

View Serializability
Let S and S´ be two schedules with the same set of transactions. S and S´ are view equivalent
if the following three conditions are met:
• For each data item Q, if transaction Ti reads the initial value of Q in schedule S, then
transaction Ti must, in schedule S´, also read the initial value of Q.
• For each data item Q if transaction Ti executes read(Q) in schedule S, and that value was
produced by transaction Tj (if any), then transaction Ti must in schedule S´ also read the
value of Q that was produced by transaction Tj .
• For each data item Q, the transaction (if any) that performs the final write(Q) operation
in schedule S must perform the final write(Q) operation in schedule S´.

26
44. Explain Recoverable Schedule
• If a transaction Tj reads a data item previously written by a transaction Ti , the commit
operation of Ti appears before the commit operation of Tj.
• T9 Transaction performs only one instruction i.e. Read(A)
• Suppose T9 Allows commit operation immediately after executing
the Read(A) instruction.
• T9 commits before T8
• Now suppose T8 fails before it commits.
• T8 has to rollback. But before that T9 has to rollback because T9 is
dependence on T8.
• T9 is already committed so cannot be rollback. Hence schedule is
non – recoverable schedule.
• And DBMS does not want non-recoverable schedule.
• Hence the commit operation of T9 will be only after the commit operation of T8 for
Recoverable schedule.

45. Explain Cascading Rollback

• Cascading rollback a single transaction failure leads to a series of
transaction rollbacks.
• Consider the following schedule where none of the transactions
has yet committed (so the schedule is recoverable)
• If T10 fails, T11 and T12 must also be rolled back.
• Can lead to the undoing of a significant amount of work

46. Explain Lock Based Protocol

• A lock is a mechanism to control concurrent access to a data item
• Data items can be locked in two modes:
• exclusive (X) mode: Data item can be both read as well as written X-lock is requested using
lock-X instruction.
• shared (S) mode: Data item can only be read. S-lock is requested using lock-S instruction. Lock
requests are made to concurrency-control manager. Transaction can proceed only after
request is granted.
Lock-compatibility matrix:

27
47. Explain Two Phase Locking Protocol
This is a protocol which ensures conflict-serializable schedules.
Phase 1: Growing Phase:
• Transaction may obtain locks
• Transaction may not release locks
Phase 2: Shrinking Phase:
• Transaction may release locks
• Transaction may not obtain locks
• The protocol assures serializability. It can be proved that the transactions can be serialized
in the order of their lock points

There two Main disadvantage of Two-Phase locking Protocol:

• Deadlock
• Cascading Rollbacks
Cascading Rollbacks can be avoided by modification of Two-Phase locking Protocol:
Strict two-phase locking:
Here a transaction must hold all its exclusive locks till it commits/aborts.
Rigorous two-phase locking:
is even stricter: here all locks are held till commit/abort.

28
48. Explain Recovery System
• Logical errors: transaction cannot complete due to some internal error condition
• System errors: the database system must terminate an active transaction due to an error
condition (e.g., deadlock)
• System crash: a power failure or other hardware or software failure causes the system to
crash.
• Recovery algorithms are techniques to ensure database consistency and transaction
atomicity and durability despite failures.
We study two approaches:
Log-based recovery, and Shadow-paging

49. Explain types of log-based recovery

Deferred DB Modification:
• Execution of write operation in DB is done when transaction is committed.
• It used recovery procedure Redo (Ti) to update values to Dbase.
• Deferred stores only new values in log file.

29
 Immediate DB Modification:
• Execution of write operation in DB is while transaction is in Active state.
• It used recovery procedure Redo(Ti) to update new values to Dbase and undo(T1) to
update old values.
• Deferred stores only old as well as new values in log file.

50. Explain Deadlock occurrences and prevention

Deadlock is a situation that occurs in a computer system when two or more processes are
waiting for each other to release resources that they need in order to continue execution. In
other words, a deadlock occurs when two or more processes are stuck in a circular wait, and
none of them can proceed until the other process releases the resources it needs.
The necessary conditions for a deadlock to occur are:
• Mutual exclusion: At least one resource is held in a non-shareable mode. This means that
only one process can use the resource at a time.
• Hold and wait: A process is holding at least one resource and is waiting to acquire
additional resources that are currently being held by other processes.
• No pre-emption: Resources cannot be forcibly removed from a process that is holding
them.
• Circular wait: A circular chain of two or more processes is waiting for the other process
to release the resources it needs.
To avoid deadlock, several techniques can be used:
• Resource allocation and release: One technique to avoid deadlock is to ensure that all
resources are allocated at the beginning of the process and released when they are no
longer needed. This way, a process will never hold a resource while waiting for another
resource to become available.
• Deadlock avoidance: Another technique is to use a resource allocation algorithm that
avoids circular wait situations. This can be done by dynamically monitoring resource
allocation and predicting potential deadlock situations before they occur.
• Deadlock detection and recovery: This technique involves periodically checking the
system to detect and recover from deadlocks. If a deadlock is detected, the system can
either terminate one of the processes or release one or more of the resources held by
the processes to break the circular wait.
• Pre-emption: This technique involves forcibly removing resources from a process that is
holding them if another process needs them. This can be done if the process holding the
resource has exceeded its allotted time or priority.

30