Department Artificial Intelligence and Data Science

Database Management System (PCC-251- AID)

Unit – 3
Relational DBMS and Intelligent Query
Processing

Relational model concepts:

Attributes

An Attribute is a property or characteristic that describes an entity in a database. It

provides specific information used to identify, categorize, and manage entities
effectively. Without attributes, entities have no meaningful data.

For example:
 Let's take the student as an entity. Students will have multiple attributes such as
roll number, name, and class.
 These attributes are used to describe the student in more detail.

 As shown in the figure, roll_no, name, and class are the attributes of the entity
Student.
 All three attributes give meaning to the entity. The information about the student
entity lies in all 3 attributes.


Types of Attribute
There are 8 types of Attributes in DBMS.

1. Simple Attribute

Simple Attributes are indivisible properties that hold basic information about an
entity, such as name, roll number, or age. They cannot be broken down further and
are often used to build other types of attributes.
Let's understand this with the help of example:
 Here in the below example, Student has roll_no, class, and name as attributes
that cannot be divided into more sub-attributes.
[Link]
[Link]
 These types of attributes are called simple attributes.
 Simple attributes are mainly used to create all other types of attributes.

Simple Attribute

2. Composite Attribute

A composite attribute is formed by combining two or more simple attributes. It is

used to represent complex data structures, like a full name made of first and last
names.
Let's understand this with the help of example:
 Here if we look at the below example, address is the attribute derived from the 3
simple attributes i.e. City, State, and Street.
 To get the value of the address attribute, we have first to know those city, state,
and street attributes.
 This type of attribute is known as a composite attribute.

Composite Attribute

3. Single Valued Attribute

A single-valued attribute contains only one value per entity instance. Mostly these
attributes are used to provide the unique identity to the multiple instances of
attributes.
Let's understand this with the help of Example:

[Link]
 In the given example, we know that the DOB attribute will have only one value.
So we can say that the DOB attribute is nothing but a single-valued attribute and
it cannot have multiple attributes.
 Here roll_no and name will also have mostly one value only.
 We can say that all 3 attributes of the student are single-valued.

Single valued attribute

4. Multivalued Attribute

A multivalued attribute can have multiple values for one entity instance. Unlike
single-valued attributes, it stores several values linked to the entity.
Let's Understand this with the help of Example:
 In the given example, Student has an attribute named phone_no. One student
can have multiple phone_no, so we can say that phone_no can have multiple
values.
 These types of attributes are known as multi-valued attributes.
 Multi-valued attributes are used when more than 1 entries for one attribute need
to be stored in the Database.

Multi-valued attribute

[Link] Attribute

A key attribute has a unique value for every entity and is used to identify it. It plays
an important role in ensuring data is distinct in a database.

[Link]
Let's Understand this with the help of Example:
 For students, we can identify every student with roll_no because each student will
have a unique roll_no.
 This indicates that roll_no will be a Key attribute for the Student entity.
 All operations on the database can be performed only using Key Attributes.

Key attribute

6. Derived Attribute

A derived attribute is an attribute calculated from other attributes in the database and
is not physically stored. Its value is obtained using existing data within the same
database.
Let's understand this with help of Example:
 Here the student has multiple attributes including DOB and age. It is observed
that age can be calculated with the help of the DOB attribute.
 So age is a derived attribute that is derived from an attribute named DOB.

Derived attribute

7. Stored Attribute

Stored attributes are physically stored in the database (unlike derived attributes).
They can change (e.g., a student’s address), but they are stored directly, not
calculated.. It stores permanent information that remains fixed throughout the entity’s
lifetime.
[Link]
 Let's understand this with the help of example
 The student has 3 attributes as shown above. Her name and DOB will remain the
same throughout his/her education. So the student has a fixed value attribute that
will never change in the future.
 These attributes are known as stored attributes.

Stored attributes

[Link] Attribute

A complex attribute is a combination of composite and multivalued attributes. It can

contain multiple levels of sub-attributes. Complex attributes can have an unlimited
number of sub-attributes.
Let's understand this with the help of Example:
 Here for the student, we created an attribute named contact_info which further
decomposed into phone_no + Address.
 The address is a composite attribute which is further divided into simple attributes
and phone_no is a multivalued attribute.
 This indicates that the contact_info attribute is made from the multi-valued and
composite attribute.
 This type of attribute is known as the Complex Attribute.

Complex attributes
Domains
 In DBMS, constraints are the set of rules that ensures that when an authorized user
modifies the database they do not disturb the data consistency and the constraints

[Link]
are specified within the DDL commands like "alter" and "create" command. There
are several types of constraints available in DBMS and they are:
 Domain constraints
 Entity Integrity constraints
 Referential Integrity constraints
 Key constraints

Domain Constraints

Domain Constraints are user-defined columns that help the user to enter the value
according to the data type. And if it encounters a wrong input it gives the message
to the user that the column is not fulfilled properly. Or in other words, it is an
attribute that specifies all the possible values that the attribute can hold like integer,
character, date, time, string, etc. It defines the domain or the set of values for an
attribute and ensures that the value taken by the attribute must be an atomic
value(Can't be divided) from its domain.
Domain Constraint = data type(integer / character/date / time / string / etc.) +
Constraints(NOT NULL / UNIQUE / PRIMARY KEY /
FOREIGN KEY / CHECK / DEFAULT)
Type of domain constraints:
There are two types of constraints that come under domain constraint and they are:
1. Domain Constraints - Not Null: Null values are the values that are
unassigned or we can also say that which are unknown or the missing attribute
values and by default, a column can hold the null values. Now as we know that the
Not Null constraint restricts a column to not accept the null values which means it
only restricts a field to always contain a value which means you cannot insert a
new record or update a record without adding a value into the field.
Example: In the 'employee' database, every employee must have a name
associated with them.
Create table employee
(employee_id varchar(30),
employee_name varchar(30) not null,
salary NUMBER);
2. Domain Constraints - Check: It defines a condition that each row must
satisfy which means it restricts the value of a column between ranges or we can
say that it is just like a condition or filter checking before saving data into a column.
It ensures that when a tuple is inserted inside the relation must satisfy the predicate
given in the check clause.
Example: We need to check whether the entered id number is greater than 0 or
not for the employee table.
Create table employee
(employee_id varchar(30) not null check(employee_id > 0),
employee_name varchar(30),
salary NUMBER);
The above example creates CHECK constraints on the employee_id column and
specifies that the column employee_id must only include integers greater than 0.
[Link]
 Note: In DBMS a table is a combination of rows and columns in which we have
some unique attribute names associated with it. And basically, a domain is a
unique set of values present in a table. Let's take an example, suppose we have a
table student which consists of 3 attributes as NAME, ROLL NO, and MARKS. Now
ROLL NO attributes can have only numbers associated with them and they won't
contain any alphabet. So we can say that it contains the domain of integer only and
it can be only a positive number greater than 0.
Example 1:
Creating a table ―student‖ with the ―ROLL‖ field having a value greater than 0.
Domain:

Table:

The above example will only accept the roll no. which is greater than 0.
Example 2:
Creating a table "Employee" with the "AGE" field having a value greater than 18.
Domain:

Table:

The above example will only accept the Employee with an age greater than 18.

CODD’s Rules
 Codd's rules are proposed by a computer scientist named Dr. Edgar F. Codd and
he also invent the relational model for database management. These rules are
made to ensure data integrity, consistency, and usability. This set of rules basically
[Link]
signifies the characteristics and requirements of a relational database management
system (RDBMS). In this article, we will learn about various Codd's rules.

Rule 1: The Information Rule

All information, whether it is user information or metadata, that is stored in a
database must be entered as a value in a cell of a table. It is said that everything
within the database is organized in a table layout.

Rule 2: The Guaranteed Access Rule

Each data element is guaranteed to be accessible logically with a combination of
the table name, primary key (row value), and attribute name (column value).

Rule 3: Systematic Treatment of NULL Values

Every Null value in a database must be given a systematic and uniform treatment.

Rule 4: Active Online Catalog Rule

The database catalog, which contains metadata about the database, must be
stored and accessed using the same relational database management system.

Rule 5: The Comprehensive Data Sublanguage Rule

A crucial component of any efficient database system is its ability to offer an easily
understandable data manipulation language (DML) that facilitates defining,
querying, and modifying information within the database.
Rule 6: The View Updating Rule
All views that are theoretically updatable must also be updatable by the system.

Rule 7: High-level Insert, Update, and Delete

A successful database system must possess the feature of facilitating high-level
insertions, updates, and deletions that can grant users the ability to conduct these
operations with ease through a single query.

Rule 8: Physical Data Independence

Application programs and activities should remain unaffected when changes are
made to the physical storage structures or methods.

Rule 9: Logical Data Independence

Application programs and activities should remain unaffected when changes are
made to the logical structure of the data, such as adding or modifying tables.

Rule 10: Integrity Independence

Integrity constraints should be specified separately from application programs and
stored in the catalog. They should be automatically enforced by the database
system.

Rule 11: Distribution Independence

The distribution of data across multiple locations should be invisible to users, and
the database system should handle the distribution transparently.

Rule 12: Non-Subversion Rule

If the interface of the system is providing access to low-level records, then the
interface must not be able to damage the system and bypass security and integrity
constraints.
[Link]
 Integrity constraints:
 Integrity constraints are a set of rules used in DBMS to ensure that the data in a
database is accurate, consistent and reliable. These rules helps in maintaining the
quality of data by ensuring that the processes like adding, updating or deleting
information do not harm the integrity of the database. Integrity constraints also
define how different parts of the database are connected and ensure that these
relationships remain valid. They play an essential role in making sure the data is
meaningful and follows the logical structure of the database.

What are Integrity Constraints ?

Integrity constraints in a Database Management System are rules that help keep
the data in a database accurate, consistent and reliable. They act like a set of
guidelines that ensure all the information stored in the database follows specific
standards.

Integrity Constraints
Example: Making sure every customer has a valid email address & ensuring that
an order in the database is always linked to an existing customer.
Types of Integrity Constraints
There are Different types of Integrity Constraints used in DBMS, these are:
1. Domain Constraints
2. Entity Integrity Constraints
3. Key Constraints
4. Referential integrity constraints
5. Assertion
6. Triggers

1. Domain Constraints

Domain constraints are a type of integrity constraint that ensure the values stored in
a column (or attribute) of a database are valid and within a specific range or domain.
In simple terms, they define what type of data is allowed in a column and restrict
[Link]
invalid data entry. The data type of domain include string, char, time, integer, date,
currency etc. The value of the attribute must be available in comparable domains.
Example: Below table demonstrates domain constraints in action by enforcing rules
for each column
Student_Id Name Semester Age

21CSE100 Aniket Kumar 6th 20

21CSE101 Shashwat Dubey 7th 21

Manvendra
21CSE102 8th 22
Sharma

21CSE103 Ashmit Dubey 5th 20

1. Student_Id: Must be unique and follow a specific format like 21CSE###. No

duplicates or invalid formats allowed.
2. Name: Accepts only valid text (no numbers) and cannot be left empty (NOT
NULL constraint).
3. Semester: Allows specific values like 5th, 6th, etc., and ensures valid input (e.g.,
no 10th if not permitted).
4. Age: Must be an integer within a reasonable range (e.g., 18-30) and cannot
contain invalid data like negative numbers or text.

Types of Domain Constraints:

 NOT NULL Constraint: Ensures No records can have NULL value.
 CHECK Constraint: This Constraint Checks for any specified condition over any
attribute.

Why Domain Constraints Are Important :

 They prevent invalid or inconsistent data from entering the database.
 They ensure the database is reliable and follows predefined business rules.
 They make the database easier to manage and maintain by reducing errors.
Example: Let, the not-null constraint be specified on the "Semester" attribute in the
relation/table given below, then the data entry of 4th tuple will violate this integrity
constraint, because the "Semester" attribute in this tuple contains null value. To
make this database instance a legal instance, its entry must not be allowed by
database management system.
Student_id Name Semester Age

21CSE1001 Sonali Rao 5th 20

21CSE1012 Anjali Gupta 5th 21

[Link]
 Student_id Name Semester Age

21CSE1023 Aastha Singh 5th 22

21CSE1034 Ayushi Singh NULL 20

Read more about Domain Constraints and its types, Here.

2. Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value
because primary key is used to determine individual rows in a relation uniquely, if
primary key contains null value then we cannot identify those rows. A table can
contain null value in it except primary key field.
Key Features of Entity Integrity Constraints:
 Uniqueness: The primary key value must be unique for each row in the table. No
duplicate entries are allowed in the primary key column.
 NOT NULL: The primary key column cannot contain NULL values, as every row
must have a valid identifier.
 Essential for Table Design: Ensures that every record in the table can be
uniquely identified, preventing ambiguity.
Example: It is not allowed because it is containing primary key (Student_id) as
NULL value.
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

NULL Mukesh 5th 20

3. Key Constraints

Key constraints ensure that certain columns or combinations of columns in a table

uniquely identify each row. These rules are essential for maintaining data integrity
and preventing duplicate or ambiguous records.

Why Key Constraints Are Important ?

 Prevent Duplicates: Ensure unique identification of rows.
 Maintain Relationships: Enable proper linking between tables (via foreign keys).
[Link]
 Enforce Data Integrity: Prevent invalid or inconsistent data.
Example: It is now acceptable because all rows must be unique.
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

21CSE102 Mukesh 5th 20

3.1 Primary Key Constraints

It states that the primary key attributes are required to be unique and not null. That
is, primary key attributes of a relation must not have null values and primary key
attributes of two tuples must never be same. This constraint is specified on database
schema to the primary key attributes to ensure that no two tuples are same.
Example: Here, in the below example the Student_id is the primary key attribute.
The data entry of 4th tuple violates the primary key constraint that is specifies on the
database schema and therefore this instance of database is not a legal instance.
Student_id Name Semester Age

101 Ramesh 5th 20

102 Kamlesh 5th 21

103 Akash 5th 22

 Unique Values: Each student_id must be unique. 101, 102, 103 are valid.
Inserting 101 again would result in an error.
 Not NULL: student_id cannot be NULL.
 Invalid: A row with NULL for student_id will be rejected.

3.2 Unique Key Constraints

The Unique key constraint in DBMS ensures that all values in a specified column (or
group of columns) are distinct across the table. It prevents duplicate entries,
maintaining data integrity, but unlike the primary key, it allows one NULL value.
Example: Here, in the below example the Email column has NULL value in 2nd
record.
Employee_ID Email Name

[Link]
 Employee_ID Email Name

1 aniket@[Link] Aniket Kumar

2 NULL Shashwat Dubey

3 shashwat@[Link] Manvendra Sharma

 Unique Values: The email column must contain unique values.

aniket@[Link] and shashwat@[Link] are valid. Adding another
row with aniket@[Link] would result in an error.
 Allows One NULL: The email column can contain one NULL value.
 Valid: NULL in the second row.
 Invalid: Adding another row with NULL in email will be rejected.

4. Referential integrity

Referential integrity constraints are rules that ensure relationships between tables
remain consistent. They enforce that a foreign key in one table must either match a
value in the referenced primary key of another table or be NULL. This guarantees
the logical connection between related tables in a relational database.

Why Referential Integrity Constraints Are Important ?

 Maintains Consistency: Ensures relationships between tables are valid.
 Prevents Orphan Records: Avoids cases where a record in a child table
references a non-existent parent record.
 Enforces Logical Relationships: Strengthens the logical structure of a relational
database.
Example: Here, in below example Block_No 22 entry is not allowed because it is not
present in 2nd table.
Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

23CSE106 Mukesh 2nd 22

Block_No Block Location

[Link]
Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

To read about SQL FOREIGN KEY Constraint.

5. Assertion

An assertion is a declarative mechanism in a database that ensures a specific

condition or rule is always satisfied across the entire database. It is a global integrity
constraint, meaning it applies to multiple tables or the entire database rather than
being limited to a single table or column. An assertion in SQL-92 takes the form:
create assertion <assertion-name> check <predicate>
When an assertion is made, the system tests it for validity. This testing may
introduce a significant amount of overhead; hence assertions should be used with
great care.
Example of an Assertion:
CREATE ASSERTION sum_constraint
CHECK (
NOT EXISTS (
SELECT *
FROM branch
WHERE (
SELECT SUM(amount)
FROM loan
WHERE loan.branch_name = branch.branch_name
) >= (
SELECT SUM(amount)
FROM account
WHERE account.branch_name = branch.branch_name
)
)
);
Explanation:
The following SQL statement creates an assertion to ensure that the total loan
amount at each branch is always less than the total account balances at the same
branch.
 Purpose: This assertion enforces a global business rule. The sum of all loan
amounts for a branch must always be less than the sum of all account balances
in the same branch. This prevents branches from issuing loans beyond their
financial capacity.
 Subqueries: The first subquery (SELECT SUM(amount) FROM loan) calculates
the total loan amount for each branch. The second subquery (SELECT

[Link]
 SUM(amount) FROM account) calculates the total balance of accounts for the
same branch.
 Condition: The NOT EXISTS clause ensures there is no branch where the loan
amount is greater than or equal to the account balance.
 Behavior: If a transaction (e.g., inserting a loan or updating an account) violates
this rule, the operation will be rejected by the database.

6. Triggers

A trigger is a procedural statement in a database that is automatically executed in

response to certain events such as INSERT, UPDATE, or DELETE. Triggers are
often used to enforce complex integrity constraints or implement business rules that
cannot be captured using standard constraints like primary keys or foreign keys.
Example SQL Trigger:
CREATE TRIGGER handle_overdraft
AFTER UPDATE ON account
FOR EACH ROW
BEGIN
-- Check if the balance has become negative after the
update
IF [Link] < 0 THEN
-- Set the account balance to zero
UPDATE account
SET balance = 0
WHERE account_number = NEW.account_number;

-- Create a loan record with the same account number as the loan number
INSERT INTO loan (loan_number, loan_amount)
VALUES (NEW.account_number, ABS([Link])); -- ABS to ensure positive
loan amount
END IF;
END;
Explanation:
 Trigger Type: The trigger runs after an update on the account table.
 Condition: It checks if the account balance is negative after the update.
 Actions: If the balance is negative, the account balance is reset to zero. A loan is
created with the same account number as the loan number, and the loan amount
is the absolute value of the negative balance. The ABS() function ensures the
loan amount is positive.

Enterprise constraints-

Enterprise constraints are additional conditions imposed by an organization on the

database beyond basic integrity constraints.

These constraints ensure that the database follows the policies and requirements of
the organization.

Examples of Enterprise Constraints

1. Employee Salary Constraint

Rule: Salary of an employee must not exceed the salary of the manager.
[Link]
Example Table

Emp_ID Employee_Name Salary Manager_Salary

101 Rahul 40000 60000

102 Amit 70000 60000 ฀

In the second record, the employee salary is greater than the manager salary, which
violates the enterprise constraint.

2. Minimum Attendance Constraint

Rule: A student must have at least 75% attendance to appear for exams.

Example

Student Attendance

A 82%

B 68% ฀

Student B cannot appear for the examination.

3. Bank Withdrawal Constraint

Rule: Account balance should not become negative after withdrawal.

Example

Account Holder Balance Withdrawal

Rohan ₹5000 ₹3000

Priya ₹2000 ₹2500 ฀

Priya’s withdrawal violates the enterprise constraint because balance becomes

negative.

4. Library Constraint
Rule: A student can issue a maximum of 3 books.
Example
Student Books Issued
Sneha 2
Kunal 4 ฀
Kunal violates the library enterprise rule.
Characteristics of Enterprise Constraints
 Based on organizational policies and business requirements.
 Help maintain data consistency and correctness.
 Can be different for different organizations.
 Usually implemented using:
[Link]
  Triggers
 Stored Procedures
 Application Logic
 CHECK constraints

Normalization:
What is Normalization?
 Normalization is the process of organizing the data in the database.
 Normalization is used to minimize the redundancy from a relation or set of

relations.
 It is also used to eliminate undesirable characteristics like Insertion, Update, and
Deletion Anomalies.
 Normalization divides the larger table into smaller and links them using
relationships.
 Most commonly used normal forms:
 First normal form(1NF)

 Second normal form(2NF)

 Third normal form(3NF)
 Boyce & Codd normal form (BCNF)
Why do we need Normalization?

 The main reason for normalizing the relations is removing these anomalies.
Failure to eliminate anomalies leads to data redundancy and can cause data

integrity and other problems as the database grows.

 Update anomalies — When we try to update one data item having its copies
scattered over several places, a few instances get updated properly while a few
others are left with old values. Such instances leave the database in an
inconsistent state.
 Deletion anomalies — We tried to delete a record, but parts of it was left
undeleted because of unawareness, the data is also saved somewhere else.

 Insert anomalies — We tried to insert data in a record that does not exist at all.
 Normalization is a method to remove all these anomalies and bring the database
to a consistent state.

[Link]
 Normalization consists of a series of guidelines that helps to guide you in creating

a good database structure.

First Normal Form (1NF)
Key Principle:
 Atomicity: Each field in a table must contain only a single value; no
multivalued attributes are allowed.
1NF requires that:
 No multivalued attributes exist: Every attribute contains a single, indivisible

value.
 Only atomic values: Each cell in the table should store a single piece of
information.
Example:

Imagine a table where one column stores multiple phone numbers in a single field.
This violates 1NF because each cell is not atomic. Instead, phone numbers should be

stored in separate rows or a related table.

Example 2 :

Without 1NF

[Link]
 IN 1NF
Second Normal Form (2NF)
Key Principle:
 Full Functional Dependency: The table must be in 1NF, and every non-key

attribute must be fully functionally dependent on the entire primary key, not
just part of it.

To achieve 2NF:
 Table must already be in 1NF.
 No partial dependency exists: If you have a composite key (for example, a
key made of columns A and B), then every non-key column must depend on
the full composite key (A and B together) and not just on A or B separately.
 Unique can determine non-unique: For instance, if you have a dependency

like AB → C, ensure that no proper subset (such as A or B alone) can

determine C unless that subset is itself a candidate key.

Example:

Consider a table with a composite key of (A, B) and a non-key attribute C. If attribute
B by itself could determine C, this partial dependency violates 2NF. Only if B is also a
candidate key (i.e., it can uniquely identify rows) is this acceptable.

Example 2:

[Link]
Third Normal Form (3NF)
Key Principle:
 Elimination of Transitive Dependencies: The table must be in 2NF, and
non-key attributes should not depend on other non-key attributes.
For 3NF:
 Table must be in 2NF.

 No transitive dependencies: If attribute X determines Y and Y determines Z,

then a transitive dependency exists. Even if X is the primary key and Y is non-
unique, if Y determines another non-key attribute Z, this is not acceptable.

[Link]
  No non-prime attribute should determine another non-prime

attribute: This helps in preventing hidden redundancy.

Example:

If you have a chain where X (the primary key) determines Y, and Y in turn determines
Z, then indirectly X determines Z through Y. If both Y and Z are non-key (non-prime)
attributes, then this structure violates 3NF because a non-prime attribute (Y) is
determining another non-prime attribute (Z).

Example 2:

IN 3NF
Boyce-Codd Normal Form (BCNF)
Key Principle:
 Stricter Form of 3NF: Every determinant must be a candidate key.
BCNF takes the principles of 3NF a step further:

 Table must be in 3NF.

 LHS of every functional dependency must be a super key or candidate

key: This rule ensures that even subtle anomalies are eliminated by requiring

[Link]
 that the determinant in any dependency is not just functionally complete, but

also uniquely identifies a row in the table.

Impact:

By ensuring that every determinant is a candidate key, BCNF minimizes redundancy

further and safeguards against anomalies that might arise even in a table that meets
3NF.
Example :

Functional dependencies-
A Functional Dependency (FD) in DBMS describes the relationship between
attributes in a relation (table).
It specifies that the value of one attribute uniquely determines the value of another
attribute.

Notation
If attribute A determines attribute B, it is written as:
A→BA \rightarrow BA→B
This means:
 For each value of A, there is only one corresponding value of B.
Example of Functional Dependency
Student Table
Roll_No Student_Name Department
101 Rahul AI & DS
102 Sneha CSE
103 Amit IT
Here:
[Link]
Roll_No→Student_Name, DepartmentRoll\_No \rightarrow Student\_Name,\
DepartmentRoll_No→Student_Name, Department
Because each Roll Number uniquely identifies the student name and department.

Decomposition-
Decomposition refers to the division of tables into multiple tables to produce
consistency in the data. In this article, we will learn about the Database concept. This
article is related to the concept of Decomposition in DBMS. It explains the definition
of Decomposition, types of Decomposition in DBMS, and its properties.

What is Decomposition in DBMS?

When we divide a table into multiple tables or divide a relation into multiple relations,
then this process is termed Decomposition in DBMS. We perform decomposition in
DBMS when we want to process a particular data set. It is performed in a database
management system when we need to ensure consistency and remove anomalies
and duplicate data present in the database. When we perform decomposition in
DBMS, we must try to ensure that no information or data is lost.

Decomposition in DBMS
Types of Decomposition
There are two types of Decomposition:
 Lossless Decomposition
 Lossy Decomposition

[Link]
Types of Decomposition
Lossless Decomposition
The process in which where we can regain the original relation R with the help of
joins from the multiple relations formed after decomposition. This process is termed
as lossless decomposition. It is used to remove the redundant data from the
database while retaining the useful information. The lossless decomposition tries to
ensure following things:
 While regaining the original relation, no information should be lost.
 If we perform join operation on the sub-divided relations, we must get the original
relation.
Example:
There is a relation called R(A, B, C)

A B C

55 16 27

48 52 89

Now we decompose this relation into two sub relations R1 and R2

R1(A, B)

A B

55 16

48 52

R2(B, C)
[Link]
 B C

16 27

52 89

After performing the Join operation we get the same original relation

A B C

55 16 27

48 52 89

Lossy Decomposition
As the name suggests, lossy decomposition means when we perform join operation
on the sub-relations it doesn't result to the same relation which was decomposed.
After the join operation, we always found some extraneous tuples. These extra
tuples genrates difficulty for the user to identify the original tuples.
Example:
We have a relation R(A, B, C)

A B C

1 2 1

2 5 3

3 3 3

Now , we decompose it into sub-relations R1 and R2

R1(A, B)

A B

1 2

2 5

3 3

[Link]
R2(B, C)

B C

2 1

5 3

3 3

Now After performing join operation

A B C

1 2 1

2 5 3

2 3 3

3 5 3

3 3 3

Properties of Decomposition
 Lossless: All the decomposition that we perform in Database management
system should be lossless. All the information should not be lost while performing
the join on the sub-relation to get back the original relation. It helps to remove the
redundant data from the database.
 Dependency Preservation: Dependency Preservation is an important technique
in database management system. It ensures that the functional dependencies
between the entities are maintained while performing decomposition. It helps to
improve the database efficiency, maintain consistency and integrity.
 Lack of Data Redundancy: Data Redundancy is generally termed as duplicate
data or repeated data. This property states that the decomposition performed
should not suffer redundant data. It will help us to get rid of unwanted data and
focus only on the useful data or information.

Introduction to Intelligent Query Processing (IQP):

Definition
Efficient query performance is crucial for modern applications, as databases handle
massive amounts of data. Traditionally, query optimization relied on static cost-
based estimations, which sometimes led to suboptimal execution plans due to
[Link]
incorrect assumptions.
To address this, modern databases—particularly Microsoft SQL Server—have
introduced Intelligent Query Processing (IQP). IQP enhances query execution by
automatically adapting, optimizing, and learning from past executions. This
minimizes performance issues without requiring code changes.
What is Intelligent Query Processing (IQP)?
Intelligent Query Processing (IQP) is a set of advanced query optimization features
in SQL Server (starting from SQL Server 2017 and significantly expanded in SQL
Server 2019 and later).
IQP enhances query performance dynamically by making real-time adjustments
based on execution statistics, feedback loops, and AI-driven techniques.

How is IQP different from Traditional Query Processing?

Traditional Query Intelligent Query Processing
Aspect
Processing (IQP)

Dynamic, adjusts during

Optimization Stage Static, before execution
execution

Query Plan Adapts based on real-time

Based on fixed statistics
Adjustments data

Handling Plan Requires manual Automatically detects &

Regression intervention corrects

DBA-driven tuning Minimal or no code changes

Performance Tuning
required needed

Machine Learning
None Uses feedback loops & AI
Influence

Why Do We Need Intelligent Query Processing?

Traditional query optimization relies on cardinality estimation—predicting the number
of rows a query will process. However, real-world queries often face:
฀ Bad Cardinality Estimates – Outdated statistics or complex predicates lead to poor
execution plans.
฀ Query Plan Regressions – A once-efficient query suddenly slows down due to a
bad plan.
฀ Memory Allocation Issues – Queries either over-allocate (wasting resources) or
under-allocate (causing spills to disk).
฀ Suboptimal Join Strategies – Poor join selection (Nested Loop instead of Hash
Join) causes performance degradation.
IQP fixes these problems automatically, reducing the need for manual performance
tuning.

Key Features of Intelligent Query Processing

IQP introduces a range of powerful enhancements that improve query performance
dynamically. Let’s explore some of its most impactful features.

[Link]
Batch Mode on Rowstore
What it does:
Originally available only for Columnstore indexes, Batch Mode Execution improves
the performance of queries running on rowstore tables (traditional tables with B-tree
indexes).
Benefits:
Uses vectorized execution, reducing CPU usage.
Drastically improves performance for aggregations, joins, and large scans.
No changes needed—SQL Server automatically enables it when beneficial.
Example:
SELECT CustomerID, COUNT(*) FROM [Link] GROUP BY CustomerID;
Without batch mode, this query processes one row at a time. With batch mode, SQL
Server processes thousands of rows at once, leading to faster execution.
Adaptive Joins
What it does:
Instead of selecting a Nested Loop Join, Hash Join, or Merge Join at compile time,
Adaptive Joins allow SQL Server to switch the join strategy dynamically at runtime.
Benefits:
Prevents bad join choices due to incorrect row estimates.
Ensures optimal join selection for varying input sizes.
Example:
If SQL Server expects 100 rows but actually gets 10 million rows, it will switch from a
Nested Loop Join to a Hash Join automatically.
Adaptive Memory Grants
What it does:
Allocates just the right amount of memory for query execution instead of over- or
under-allocating.
Benefits:
Prevents out-of-memory issues for large queries.
Reduces spilling to tempdb, which slows down execution.
Example:
A complex report query initially requests 500MB but actually needs 5GB. SQL Server
dynamically adjusts memory allocation for future executions.

Interleaved Execution for Multi-Statement Table-Valued Functions (MSTVFs)

What it does:
Traditional table-valued functions (TVFs) always assumed fixed row estimates. This
often led to poor query plans.
With Interleaved Execution, SQL Server delays optimization until runtime to get an
accurate row estimate.
Benefits:
Prevents underestimating or overestimating TVF outputs.
Optimizes execution plans based on real row counts.
Example:
SELECT * FROM [Link](@CustomerID);
Before IQP, SQL Server guessed a default row count. Now, it waits until the function
runs and then optimizes the query plan dynamically.

Table Variable Deferred Compilation

What it does:
Table variables previously used fixed row estimates, often leading to poor execution
plans. IQP defers their compilation until runtime, allowing SQL Server to optimize
based on actual data size.
[Link]
Benefits:
Improves performance of queries using table variables.
Prevents incorrect join and index choices.
Example:
DECLARE @TempTable TABLE (ID INT, Value VARCHAR(50));
INSERT INTO @TempTable SELECT ID, Value FROM LargeTable;
SELECT * FROM @TempTable JOIN AnotherTable ON @[Link] =
[Link];
SQL Server waits until the actual row count is known before optimizing the execution
plan.

Importance of IQP-
Easy Interaction with Database
IQP allows users to interact directly with the database system using simple SQL
commands without needing detailed programming knowledge.
Example
SELECT * FROM Student;
This query retrieves all records from the Student table.
2. Faster Data Retrieval
Users can quickly access required information from large databases through
interactive queries.
Example
SELECT Name FROM Employee WHERE Department='HR';
This query displays employees working in the HR department.
3. Supports Real-Time Decision Making
IQP helps organizations obtain real-time information, which improves decision-
making processes.
Example
A bank manager can instantly check account balances or transaction details.
4. Reduces Complexity
Users do not need to understand the internal structure of the database. IQP provides
a simple and user-friendly interface for querying data.
5. Improves Productivity
Employees and users can quickly retrieve reports, records, and analytics, saving
time and increasing efficiency.
6. Helps in Data Analysis
Interactive queries help users analyze data according to different conditions and
requirements.
Example
SELECT AVG(Salary) FROM Employee;
This query calculates the average salary of employees.
7. Supports Database Management Activities
IQP is useful for:
 Data insertion
 Data modification
 Data deletion
 Report generation
Example
UPDATE Student
SET Marks = 85
WHERE Roll_No = 101;
8. Better User Experience
Interactive query systems provide immediate responses and allow users to refine
queries easily.
[Link]
Evolution of query processing techniques-
Query Processing includes translations of high-level Queries into low-level
expressions that can be used at the physical level of the file system, query
optimization, and actual execution of the query to get the actual result.
 High-level SQL is translated into low-level operations/expressions.
 This translation is systematic (methodical) across the query pipeline.
 It’s applied at the physical storage level and during query optimization.
 The final low-level plan is executed to produce the result efficiently.
It needs a basic understanding of relational algebra and file organization. It includes
the variety of tasks involved in getting data out of the database. It consists of
converting high-level database language queries into expressions that can be used
at the file system's physical level.
The process of extracting data from a database is called query processing. It
requires several steps to retrieve the data from the database during query
processing. The actions involved actions are:
1. Parsing and translation
2. Optimization
3. Evaluation
The Block Diagram of Query Processing is as:

It is done in the following steps:

Parsing
During the parse call, the database performs the following checks: Syntax check,
Semantic check, and Shared pool check, after converting the query into relational
algebra because certain activities for data retrieval are included in query processing.
First, high-level database languages like SQL are used to translate the user queries
that have been provided. It is transformed into expressions that can be applied
[Link]
further at the file system's physical level. Following this, the queries are actually
evaluated along with a number of query-optimizing transformations.
Consequently, a computer system must convert a query into a language that is
readable and understandable by humans before processing it. Therefore, the best
option for humans is SQL or Structured Query Language.
Parser performs the following checks (refer to the detailed diagram):
Syntax check: concludes SQL syntactic validity.
Example:
SELECT * FORM employee
Here, the error of the wrong spelling of FROM is given by this check.

Step-1
Semantic check
determines whether the statement is meaningful or not. Example: query contains a
table name that does not exist and is checked by this check.

Shared Pool check

Every query possesses a hash code during its execution. So, this check determines
the existence of written hash code in the shared pool if the code exists in the shared
pool then the database will not take additional steps for optimization and execution.

Step-2
Optimization
During the optimization stage, the database must perform a hard parse at least for
one unique DML statement and perform optimization during this parse. This
database never optimizes DDL unless it includes a DML component such as a
subquery that requires optimization.
It is a process in which multiple query execution plans for satisfying a query are
examined and the most efficient query plan is satisfied for execution. The database
catalog stores the execution plans and then the optimizer passes the lowest-cost
plan for execution.

Row Source Generation

Row Source Generation is software that receives an optimal execution plan from the
optimizer and produces an iterative execution plan that is usable by the rest of
the database. The iterative plan is the binary program that, when executed by
the SQL engine, produces the result set.

Step-3
Evaluation
Finally runs the query and displays the required result.

Need for intelligent query optimization in modern database-

[Link] Large Volumes of Data

Modern databases store massive amounts of data generated from applications,
social media, IoT devices, and business systems.
Example
[Link]
E-commerce websites process millions of customer transactions daily.
2. Improving Query Performance
Complex SQL queries may require large processing time and system resources.
Example
Using indexes instead of full table scans improves speed.
3. Reducing Resource Consumption
Poor query execution increases:
 CPU usage
 Memory usage
 Disk I/O operations
 Network traffic
4. Supporting Real-Time Applications
Applications such as banking, healthcare, and online shopping require instant
responses.
Example
ATM transactions must be processed immediately.
5. Managing Complex Queries
Modern applications use:
 Multiple joins
 Nested queries
 Aggregations
 Distributed queries
6. Efficient Use of Distributed and Cloud Databases
Data is often distributed across multiple servers and cloud platforms.
7. Automatic Decision Making
Traditional optimization techniques may not adapt well to changing workloads.
Features
 Self-tuning databases
 Adaptive query execution
 Predictive optimization
8. Improving User Experience
Users expect fast and accurate responses from applications.

9. Supporting Big Data and Analytics

Big data systems process huge datasets for analysis and reporting.

Advantages of Intelligent Query Optimization

 Faster query execution

 Reduced response time
 Better resource utilization
 Improved scalability
 Efficient handling of large databases
 Automatic tuning and optimization
 Enhanced system performance

[Link]