Module: DBMS + SQL- Professional Knowledge

Notes
IBPS SO IT Prelims
Contains 45 PDF lessons

Generated: 8 July 2026

KnowledgeGate
Module

DBMS + SQL- Professional Knowledge

Topic
Basics of DBMS

Subtopic
DBMS Fundamentals

Lesson

DBMS Fundamentals

Lesson 1 of 45
 Basics of DBMS
DBMS Fundamentals

[Link]

[Link]
 Data
• The Basic Concept: Data is any collection of facts and figures related to an entity.
• A Technical Definition: From a scientific perspective, data is a set of values consisting of qualitative
(descriptive) or quantitative (numerical) variables.
• The Singular vs. Plural:
○ Datum: A single value of a single variable (e.g., a person's age).
○ Data: The collective set of those values (e.g., the ages of everyone in a classroom).
• Collection & Observation: Data is typically gathered through observation, measurement, or research to be
used as a basis for reasoning or calculation.

[Link]

[Link]
 Information
● While "data" and "information" are often used interchangeably, they represent different stages of
processing.
● Distinct Meanings: Data and information have distinct meanings in the context of analysis.
● The Transformation: Data is transformed into information when it is viewed in context or in post-
analysis.
● Key Definition: Processed Data is called information.
○ Example: A list of temperatures (Data) becomes a weather forecast (Information) once it is
analyzed to predict the weekend's conditions.

[Link]

[Link]
 Data Base
● Introduction: A database provides a structured way to manage the data we have collected, moving it
from simple raw facts into a system where it can be efficiently stored and retrieved.
○ Core Definition: A database is an organized collection of data.
○ Storage and Access: These collections are generally stored and accessed electronically from a
computer system.
○ Purpose: The primary goal of a database is to maintain large amounts of information in a way that
remains easy to find, update, and manage.

[Link]

[Link]
 Data Base Management System
The DBMS is the software "manager" that handles the database for us.

● The Definition: It is the software that interacts with users and applications to capture and analyze data.
● The History: Before DBMS, we used "Flat Files" (like simple text files). This was messy because data was
duplicated, and different people couldn't edit the same file easily.
● The Need: We need a DBMS to handle Concurrency (multiple users), Security (protecting data), and
Integrity (preventing errors).
● Modern Examples:
1. MySQL: Used by companies like Facebook and Twitter to store user profiles.
2. Oracle: Used by banks to manage millions of financial transactions safely.

[Link]

[Link]
 Problem With Traditional File Systems
• Data Redundancy and Inconsistency: The same information is stored in multiple places, leading to
conflicting data. Example: A student changes their phone number in the "Attendance File," but the
"Fees File" still has the old number.
• Difficulty in Accessing Data: There is no easy way to search for specific records quickly. Example:
To find all students from "Delhi" who scored over 90%, a programmer must write a completely new
program from scratch just to get that list.
• Data Isolation: Data is trapped in different formats and locations. Example: Trying to link a "Student
Bio" text file with a "Grade" spreadsheet is nearly impossible because they "don't speak the same
language".
• Integrity Problems: It is difficult to enforce data rules. Example: The system allows a "Marks" field to
be filled with -50 because there is no built-in check to ensure scores are only between 0 and 100.
• Atomicity Problems: Partial updates occur if the system crashes. Example: During a bank transfer,
$500 is deducted from Account A, but the power goes out before it reaches Account B—the money
simply disappears.
• Concurrent Access Anomalies: Multiple users editing at once cause errors. Example: Two clerks
open the same "Library Book" file at the same time; one marks it as "Returned" and the other as "Lost,"
resulting in the last person to hit "Save" overwriting the other's work.
[Link]

[Link]
 Advantages of DBMS
● A Database Management System (DBMS) is specialized software that solves the critical flaws of old file systems by
providing a secure, centralized way to manage data.
● Data Independence: Changes made to the database structure do not require changes to the application programs.
Example: You can change how a "Phone Number" is stored in the database without having to rewrite the entire mobile
app.
● Multi-User Access & Concurrency Control: Multiple users can access and edit the same data at once without
conflicts. Example: Two people can book the last seat on a flight at the exact same time; the DBMS ensures only the
first person to click "Pay" gets the seat.
● Data Security & Authorization: Access is strictly controlled through user roles and passwords. Example: A bank teller
can view your balance, but only a branch manager has the "Authorization" to approve a large loan increase.
● Reduced Data Redundancy: Data is stored centrally in one place, ensuring it remains consistent and reliable.
Example: When you update your address once on a website, it automatically reflects in your "Orders," "Profile," and
"Shipping" sections.
● Database Design Support: DBMS helps architects plan data storage using professional methods like Top-Down and
Bottom-Up design. Example: Designing a school system by first looking at the "Big Picture" (all students) and then
breaking it down into smaller details (classes, subjects).
● Front-End Connectivity: The system can easily connect to modern software tools like Visual Basic, C++, or web apps
to display data to users. Example: An e-commerce app on your phone acts as the "Front-End" that connects to a
powerful DBMS "Back-End" to show you products.
[Link]

[Link]
 Disadvantages of DBMS
While a DBMS offers powerful solutions for data management, it also introduces specific challenges and costs that organizations
must consider.

● High Initial Cost: Setting up a professional system requires significant investment in high-end hardware, expensive software
licenses, and ongoing maintenance. Example: A small startup may find the $50,000+ price tag for a high-performance database
server and licensing too expensive to start with.
● Technical Complexity: Because these systems are sophisticated, they require highly skilled Database Administrators (DBAs)
to manage, design, and keep them running. Example: You cannot just "install and forget" a DBMS; you need a professional to
tune it so it doesn't become slow as your data grows.
● Performance Overhead: For very simple, single-user tasks, a DBMS can actually be slower than a basic file because of the
"checks" it performs. Example: Opening a small list of 10 names in a text file is instant, but doing it through a DBMS requires
login, security checks, and query processing.
● Impact of a System Failure: Since all data is centralized in one place, a single failure can stop the entire organization from
working. Example: If the central database of a hospital goes down, every department—from pharmacy to the ER—loses access
to patient records at once.
● Security Vulnerability: Centralizing all "eggs in one basket" makes the database a high-value target for cyber-attacks.
Example: A hacker only needs to breach one central system to steal all customer credit card numbers instead of hunting
through hundreds of separate files.
● Training Requirements: Both developers and end-users need specific technical training to use the system correctly and safely.
[Link]
Example: An untrained employee might accidentally delete a vital table or write a "bad query" that freezes the system for
everyone else. [Link]
 View of Data (Levels of Abstraction)
A database system should provide a clear, simplified view of data while hiding
the complex technical details. This is achieved through three distinct levels of
abstraction, often called the Three-Schema Architecture.

● Physical Level (The Bottom Level): This describes how the data is
actually stored in the hardware (hard disks, servers). It is the lowest level
of abstraction and involves complex data structures. Example: Only the
Database Administrator (DBA) works at this level to decide which physical
disk stores the customer records.
● Logical Level / Conceptual Level (The Middle Level): This level
describes what data is stored and the relationships between that data. It
defines the database structure using entities and data types. Example: A
table design that shows "Student Name" is a string and "Roll Number" is
an integer—the system knows they are related but doesn't care exactly
where on the disk they sit.
● View Level (The Top Level): This is the highest level of abstraction and
is what the user actually sees. It shows only the parts of the database that
the specific user is interested in. Example: A student can see their own
"Grade Report" but cannot see the "Financial Records" or "Salaries" of the
teachers.
[Link]

[Link]
 Instance and Schemas
To understand a database, we must distinguish between its permanent structure and its ever-changing content. We
can compare this to a variable in a computer program.

● Database Schema: This is the overall design or "skeleton" of the database. It is defined during the initial setup
and remains fixed.
○ Variable Example: Declaring a variable like int studentCount; is the Schema. You have defined that
the name is "studentCount" and it can only hold integers. The structure is now set.
● Database Instance: This is the actual data stored in the database at a specific moment in time.
○ Variable Example: If studentCount = 50; at 9:00 AM, that is the Instance. If five more students join and
the value becomes 60 at 10:00 AM, the schema (the integer variable) stays the same, but you have a new
Instance.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Basics of DBMS

Subtopic
Types & DBA Functions

Lesson

Types & DBA Functions

Lesson 2 of 45
 Basics of DBMS
Types & DBA Functions

[Link]

[Link]
 OLAP Vs OLTP
● Database systems are categorized by how they handle data: OLTP manages day-to-day operations,
while OLAP drives high-level analysis.

Feature OLTP (Online Transaction Processing) OLAP (Online Analytical Processing)

Main Goal Running daily business operations. Supporting planning and decision-making.
Database Size Relatively small (MB to GB). Extremely large (TB to PB).
Data Source Live, operational data (current transactions). Historical, consolidated data (Data Warehouse).
Query Type Short, simple, and very fast. Complex, long-running, and involves many records.
Typical Task Checking a bank balance or booking a flight. Analyzing sales trends over the last 5 years.
Update Style Continuous updates (inserts/deletes) by users. Periodic "Batch" updates to refresh the data.
User Base Thousands of clerks, customers, or online users. A small group of data analysts and managers.
Design Style Normalized (Uses many small, optimized tables). De-normalized (Fewer, larger tables for speed).

[Link]

[Link]
 ● Types of Databases: Databases can be classified based on their structure, usage, storage
methods, and intended application. Understanding these types will help us choose the best
database for our needs.
○ Hierarchical Databases: Hierarchical databases organize data in a tree-like structure,
where each parent record can have multiple child records. This model works well for
scenarios where data follows a predefined hierarchical relationship, where data is
arranged in levels or ranks. For example, in a university, "University" is at the top level,
[Link]

while "Departments" and "Administration" are at lower levels, even though they are distinct
entities. [Link]
 ● Network Databases: A network databases builds on the hierarchical model but allows child
records to link to multiple parent records, creating a web-like structure of interconnected data.
This results in a more flexible structure, often referred to as a graph model, where entities can
be connected in many different ways. For example, in a university database, "Students,"
"Faculty," and "Resources" can be linked to both "Departments" and "Clubs," forming a
flexible, two-directional relationship.
[Link]

[Link]
 ● Object-Oriented Databases: Object-oriented databases are based on the principles of object-
oriented programming (OOP), where data is stored as objects. These objects include attributes
(data) and methods (functions), making them easily referenced and manipulated. These
databases are designed to handle complex data structures such as multimedia, graphics, and
large files.
[Link]

[Link]
 ● Relational Databases: Relational databases are the most widely used type of database today.
They store data in tables, with rows representing records and columns representing attributes of
the records. In this database, every piece of information has a relationship with every other piece of
information. This is on account of every data value in the database having a unique identity in the
form of a record.

[Link]

[Link]
 Database Administrator (DBA)
The DBA is the central authority who manages the entire database system to ensure it is secure, accurate, and fast.

● Schema Definition: Creates the original "blueprint" or structure of the database. Example: Defining that every "Employee"
record must have a unique ID and a department name.
● Storage and Access Design: Determines how data is physically saved on hardware and retrieved efficiently. Example:
Organizing data into "indexes" so a search for a customer name takes milliseconds instead of minutes.
● Schema and Physical Modification: Updates the design or storage as the organization grows. Example: Adding a new
"International Address" field when a company starts shipping globally.
● Granting Authorization: Controls security by deciding who can view or edit specific data. Example: Allowing a student to view
their own grades but preventing them from editing the marks.
● Routine Maintenance: Performs daily health checks to prevent data loss.
○ Backups: Creating copies of data to restore the system after a crash.
○ Monitoring: Ensuring there is enough disk space and the system isn't slowing down.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Basics of DBMS

Subtopic
Data Model, Architecture & Components

Lesson

Data Model & Architecture

Lesson 3 of 45
 Basics of DBMS
Data Model & Architecture

[Link]

[Link]
 ● Data Model:
○ A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain
constraints that the database should obey.

● Data Model Structure and Constraints:

○ Constructs are used to define the database structure
○ Constructs typically include elements (and their data types) as well as groups of elements (e.g. entity, record, table), and
relationships among such groups
○ Constraints specify some restrictions on valid data; these
○ constraints must be enforced at all times

● Data Model Operations:

○ These operations are used for specifying database retrievals and updates by referring to the constructs
of the data model.
○ Operations on the data model may include basic model operations (e.g. generic insert, delete,
update) and user-defined operations (e.g. compute_student_gpa, update_inventory)

[Link]

[Link]
 ● Data Independence
○ Logical Data Independence:
■ The capacity to change the conceptual schema without having to change the external schemas and
their associated application programs.
○ Physical Data Independence:
■ The capacity to change the internal schema without having to change the conceptual schema.
■ For example, the internal schema may be changed when certain file structures are reorganized or new
indexes are created to improve database performance
■ When a schema at a lower level is changed, only the mappings between this schema and higher- level
schemas need to be changed in a DBMS that fully supports data independence.
■ The higher-level schemas themselves are unchanged.
● Hence, the application programs need not be changed since they refer to the external schemas.

[Link]

[Link]
 DBMS Languages
■ Data Definition Language (DDL):
■ Used by the DBA and database designers to specify the conceptual schema of a database.
■ In many DBMSs, the DDL is also used to define internal and external schemas (views).
■ In some DBMSs, separate storage definition language (SDL) and view definition language (VDL)
are used to define internal and external schemas.
■ SDL is typically realized via DBMS commands provided to the DBA and database designers

[Link]

[Link]
 ● Data Manipulation Language (DML)
○ High-Level or Non-procedural Languages: These include the relational language SQL
■ May be used in a standalone way or may be embedded in a programming language
○ Low Level or Procedural Languages:
■ These must be embedded in a programming language

○ Used to specify database retrievals and updates

■ DML commands (data sublanguage) can be embedded in a general-purpose programming
language (host language), such as COBOL, C, C++, or Java.
● A library of functions can also be provided to access the DBMS from a programming
language
■ Alternatively, stand-alone DML commands can be applied directly (called a query language).

[Link]

[Link]
 DBMS Interfaces

● Stand-alone query language interfaces

○ Example: Entering SQL queries at the DBMS interactive SQL interface (e.g. SQL*Plus in ORACLE)
● Programmer interfaces for embedding DML in programming languages
● User-friendly interfaces
○ Menu-based, forms-based, graphics-based, etc.
● Mobile Interfaces:interfaces allowing users to perform transactions using mobile apps

[Link]

[Link]
 DBMS Programming Language Interfaces

■ Programmer interfaces for embedding DML in a programming languages:

■ Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for Java)

■ Procedure Call Approach: e.g. JDBC for Java, ODBC (Open Databse Connectivity) for other

programming languages as API’s (application programming interfaces)

■ Database Programming Language Approach: e.g. ORACLE has PL/SQL, a programming language

based on SQL; language incorporates SQL and its data types as integral components
■ Scripting Languages: PHP (client-side scripting) and Python (server-side scripting) are used to

write database programs.

[Link]

[Link]
 Typical DBMS Component Modules

[Link]

[Link]
 Centralized and Client-Server DBMS Architectures
● Centralized DBMS:
○ Combines everything into single system including- DBMS software, hardware, application
programs, and user interface processing software.
○ User can still connect through a remote terminal – however, all processing is done at centralized
site.

[Link]

[Link]
 A Physical Centralized Architecture

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
ER Diagram

Subtopic
ER Modeling Fundamentals

Lesson

ER Modeling Fundamentals

Lesson 4 of 45
 ER Diagram
ER Modeling Fundamentals

[Link]

[Link]
 E-R DIAGRAM/MODEL
The E-R model serves as a high-level conceptual tool used to design the logical structure of a
database. It bridges the gap between real-world requirements and the final technical
implementation.

● Historical Context: Introduced in 1976 by Dr. Peter Chen, it shifted database design
toward a method based on the perception of the real world rather than just technical
tables.
● Conceptual Design: It is a non-technical design method that works at the conceptual
level, allowing designers to map out "entities" and the "relationships" between them.
● Enterprise Schema: The primary goal of the E-R model is to facilitate database design
by allowing the specification of an enterprise schema, which represents the overall
logical structure of the entire database.

[Link]

[Link]
[Link]

[Link]
 Summary of the E-R Model
The E-R model provides a structured and intuitive way to represent the complexities of a real-world enterprise in
a database format.

● Core Components: It is built using three basic building blocks: Entities (objects), Attributes (properties),
and Relationships (connections between objects).
● Real-World Mapping: The model excels at mapping the meanings and interactions of a business or
enterprise onto a conceptual schema.
● Clarity and Precision: It provides a standardized, logical way to visualize data that is free from technical
ambiguities.
● Accessibility: Because it is a diagrammatical representation, it is easily understood by both developers
and non-technical stakeholders.

[Link]

[Link]
 ENTITY
• An entity is a thing or an object in the real world that is distinguishable from other object
based on the values of the attributes it possesses.
• An entity may be concrete, such as a person or a book, or it may be abstract, such as a course,
a course offering, or a flight reservation.

[Link]

[Link]
 • Types of Entity
• Tangible - Entities which physically exist in real world. E.g. - Car, Pen, locker
• Intangible - Entities which exist logically. E.g. – Account, video.

[Link]

[Link]
 • ENTIY SET- Collection of same type of entities that share the same properties or
attributes. In an ER diagram an entity set is represented by a rectangle. In a
relational model it is represented by a separate table.

• In ER diagram we cannot represent an entity, as entity is an instant not schema, and

ER diagram is designed to understand schema.
• In a relational model entity is represented by a row or a tuple or a record in a table.

[Link]

[Link]
 ATTRIBUTES
• Attributes are the units defines and describe properties and characteristics of entities.
Attributes are the descriptive properties possessed by each member of an entity set. for each
attribute there is a set of permitted values called domain.

[Link]

[Link]
 • In an ER diagram attributes are represented by ellipse or oval connected to rectangle.

• While in a relational model they are represented by independent column. e.g. Instructor (ID,
name, salary, dept_name)

[Link]

[Link]
 Types of Attributes
• Single valued- Attributes having single value at any instance of time for an entity. E.g. –
Aadhar no, dob.

• Multivalued - Attributes which can have more than one value for an entity at same time. E.g.
- Phone no, email, address.
• A multivalued attribute is represented by a double ellipse in an ER diagram and by an
independent table in a relational model.
• Separate table for each multivalued attribute, by taking mva and pk of main table as fk in
new table

[Link]

[Link]
 Customer
Customer ID First Name Surname Telephone Number
123 Pooja Singh 555-861-2025, 192-122-1111
(555) 403-1659 Ext. 53; 182-929-
456 San Zhang
2929
789 John Doe 555-808-9633

जुगाड़ technology

Customer
Telephone Telephone
Customer ID First Name Surname
Number1 Number2
123 Pooja Singh 555-861-2025 192-122-1111
(555) 403-1659 Ext.
456 San Zhang 182-929-2929
53
789 John Doe 555-808-9633

[Link]

[Link]
 Customer
Customer ID First Name Surname Telephone Number
123 Rabri Devi Singh 555-861-2025, 192-122-1111
(555) 403-1659 Ext. 53; 182-929-
456 Imarti Devi Zhang
2929
789 Barfi Devi Doe 555-808-9633

Customer Name Customer Phone Number

Customer ID Telephone Number
Customer ID First Name Surname
123 555-861-2025
123 Pooja Singh
123 192-122-1111
456 San Zhang 456 (555) 403-1659 Ext. 53
789 John Doe 456 182-929-2929
789 555-808-9633

[Link]

[Link]
 • Simple - Attributes which cannot be divided further into sub parts. E.g. Age

• Composite - Attributes which can be further divided into sub parts, as simple
attributes. A composite attribute is represented by an ellipse connected to an
ellipse and in a relational model by a separate column.

[Link]

[Link]
 • Stored - Main attributes whose value is permanently stored in database. E.g.
date_of_birth

• Derived -The value of these types of attributes can be derived from values of other
Attributes. E.g. - Age attribute can be derived from date_of_birth and Date attribute.

[Link]

[Link]
 Descriptive attribute - Attribute of relationship is called descriptive attribute.

• An attribute takes a null value when an entity does not have a value for it. The null
value may indicate “not applicable”— that is, that the value does not exist for the
entity.

[Link]

[Link]
 • Null can also designate that an attribute value is unknown. An unknown value
may be either missing (the value does exist, but we do not have that
information) or not known (we do not know whether or not the value actually
exists).

[Link]

[Link]
 Relationship / Association

• Is an association between two or more entities of same or different entity set.

• In ER diagram we cannot represent individual relationship as it is an instance or
data.
• In an ER diagram it is represented by a diamond, while in relational model
sometimes through foreign key and other time by a separate table. Note: -
normally people use word relationship for relationship type so don’t get confused.

[Link]

[Link]
 • Every relationship type has three components.

• Name- Every relation must have a unique name.

• Degree-

• Structural constraints (cardinalities ratios, participation)

[Link]

[Link]
 Degree of a relationship/Relationship Set
• Means number of entities set(relations/tables) associated(participate) in the relationship set.

• Most of the relationship sets in a data base system are binary.

• Occasionally however relationship sets involve more than two entity sets.

• Logically, we can associate any number of entity set in a relationship called N-ary
Relationship.

[Link]

[Link]
 • Unary Relationship - One single entity set participate in a Relationship, means two entities of
the same entity set are related to each other.

• These are also called as self -referential Relationship set.

• E.g.- A member in a team maybe supervisor of another member in team.

[Link]

[Link]
 • Binary Relationship - Two entity
sets participate in a Relationship. It
is most common Relationship.

• Ternary Relationship - When

three entities participate in a
Relationship. E.g. The University
might need to record which
teachers taught which subjects in
which courses.

• Quaternary Relationship - When

four entities participate in a
Relationship.
[Link]

[Link]
 • N-ary relationship – where n number of entity set are associated

• But the most common relationships in ER models are Binary.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
ER Diagram

Subtopic
Cardinalities, Participation & Entity Strength

Lesson

Cardinalities, Participation & Entity Strength

Lesson 5 of 45
 ER Diagram
Cardinalities, Participation & Entity
Strength

[Link]

[Link]
 Structural constraints (Cardinalities Ratios, Participation)
• An E-R enterprise schema may define certain constraints to which the contents
of a database must conform.
• Express the number of entities to which another entity can be associated via a
relationship set. Four possible categories are-
• One to One (1:1) Relationship.
• One to Many (1: M) Relationship.
• Many to One (M: 1) Relationship.
• Many to Many (M: N) Relationship.

[Link]

[Link]
 One to One (1:1) Relationship - An entity in A is associated with at most one entity in B, and an
entity in B is associated with at most one entity in A.

E.g.- The directed line from relationship set advisor to both entities set indicates that ‘an instructor
may advise at most one student, and a student may have at most one advisor’.

[Link]

[Link]
 One to Many (1: M) Relationship - An entity in A is associated with any number (zero or more)
of entities in B. An entity in B, however, can be associated with at most one entity in A.

E.g.- This indicates that an instructor may advise many students, but a student may have at most
one advisor.

[Link]

[Link]
 Many to One (M: 1) Relationship - An entity in A is associated with at most one entity in
B. An entity in B, however, can be associated with any number (zero or more) of entities
in A.

E.g.- This indicates that student may have many instructors but an instructor can advise
at most one student.

[Link]

[Link]
 Many to Many(M:N) Relationship - An entity in A is associated with any number (zero or
more) of entities in B, and an entity in B is associated with any number (zero or more) of
entities in A.

E.g.- This indicates a student may have many advisors and an instructor may advise many
students.

[Link]

[Link]
 Participation Constraints
• Participation constraint specifies whether the existence of an entity depends on its
being related to another entity via the relationship type.
• These constraints specify the minimum and maximum number of relationship
instances that each entity must/can participate in.
• Max cardinality – it defines the maximum no of times an entity occurrence
participating in a relationship.
• Min cardinality - it defines the minimum no of times an entity occurrence
participating in a relationship.

[Link]

[Link]
 • PARTICIPATION CONSTRAINTS- it defines participations of entities of an entity
type in a relationship.
• PARTIAL PARTICIPATION (min cardinality zero) - In Partial participation only
some entities of entity set participate in Relationship set, that is there exists at
least one entity which do not participate in a relation.
• TOTAL PARTICIPATION (min cardinality at least one) - In total participation
every entity of an entity set participates in at least one relationship in
Relationship set.

[Link]

[Link]
 • Conversion of 1-1 relationship(binary)
• No separate table is required, take pk of one side as pk on other side,
priority must be given to the side having total participation.

• Conversion of 1-n or n-1 relationship (binary)

• No separate table is required, modify n side by taking pk of 1 side a foreign
key on n side.

• Conversion of n-n relationship (binary)

• Separate table is required take pk of both table and declare their
combination as a pk of new table.

[Link]

[Link]
 STRONG AND WEAK ENTITY SET

• An entity set is called strong entity set, if it has a primary key, all the tuples in the set are
distinguishable by that key.

• An entity set that does not process sufficient attributes to form a primary key is called a weak
entity set. It contains discriminator attributes (partial key) which contain partial information
about the entity set, but it is not sufficient enough to identify each tuple uniquely.
Represented by double rectangle.

[Link]

[Link]
 • For a weak entity set to be meaningful and converted into strong entity set, it must be
associated with another strong entity set called the identifying or owner entity set i.e. weak
entity set is said to be existence dependent on the identity set.
• The identifying entity set is said to own weak entity set that it identifies.
• A weak entity set may participate as owner in an identifying relationship with another weak
entity set.

[Link]

[Link]
 • The relationship associating the weak entity set with the identifying entity set is called the
identifying relationship (double diamonds).
• The identifying relationship is many to one from the weak entity set to identifying entity set,
and the participation of the weak entity set in relationship is always total.
• The primary key of weak entity set will be the union of primary key and discriminator
attributes.

[Link]

[Link]
 REASONS TO HAVE WEAK ENTITY SET
• Weak entities reflect the logical structure of an entity being dependent on
another.
• Weak entity can be deleted automatically when their strong entity is deleted.
• Without weak entity set it will lead to duplication and consequent possible
inconsistencies.

[Link]

[Link]
 Q Map the following statements with true (T)/false (F)p-
S1: Participation of the weak entity set in identifying relationship must be total.
S2: Multi valued attributes in E- R diagram require separate tables when converted
into relational model
a) F T

b) T F

c) F F

d) T T
[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
ER Diagram

Subtopic
Conversion of ER Diagram into Relational Model

Lesson

Conversion of ER Diagram into Relational Model

Lesson 6 of 45
 ER Diagram
Conversion of ER Diagram into Relational Model

[Link]

[Link]
 Conversion From ER Diagram To Relational Model
• Entity Set
• Convert every strong entity set into a separate table.
• Convert every weak entity set into a separate table, by making it
dependent into one strong entity set (identifying or owner entity set).
• Relationship(Unary)
• No separate table is required, add a new column as fk which refer
the pk of the same table.
• Relationship(Binary)
• 1:1 No separate table is required, take pk of one side and put it as fk on other
side, priority must be given to the side having total participation.

[Link]

[Link]
 Conversion From ER Diagram Tom Relational Model

• Relationship(Binary)
• 1:n or n:1 No separate table is required, modify n side by taking pk of 1 side a
foreign key on n side

Adhar_no name Age Sim_id Company Number

1 A 19 1 A OS
2 B 18 2 B DBMS
3 C 20 3 C TOC
4 D 20 4 D CN

[Link]

[Link]
 Conversion From ER Diagram Tom Relational Model
• Relationship(Binary)
• (n-n) Separate table is required take pk of both table and declare their
combination as a pk of new table
Roll no name Age Edu_id name Subject
1 A 19 1 A OS
2 B 18 2 B DBMS
3 C 20 3 C TOC
4 D 20 4 D CN
Edu_id name Subject Roll no
Roll no name Age Edu_id 1 A OS 1 Roll no Edu_id
1 A 19 1 2 B DBMS 2 1 1
1 A 19 3 2 B DBMS 3 1 3
1 A 19 4 3 C TOC 1 1 4
2 B 18 2 3 C TOC 2 2 2
2 B 18 3 3 C TOC 4 2 3
3 C 20 2 4 D CN 1 3 2
[Link]
4 D 20 3 4 D CN 4 4 3
4 D 20 4 [Link] 4 4
 Roll no name Age Edu_id name Subject
1 A 19 1 A OS
2 B 18 2 B DBMS
3 C 20 3 C TOC
4 D 20 4 D CN

Roll no Edu_id
1 1
1 3
1 4
2 2
2 3
3 2
4 3
4 4

[Link]

[Link]
 Conversion From ER Diagram To Relational Model

• Relationship(3 or More)
• Take the pk of all participating entity sets as fk and declare their
combinations as pk in the new table.
• Multivalued Attributes
• A separate table must be taken for all multivalued attributes, where we take
pk of the main table as fk and declare combination of fk and multivalued
attribute are pk in the new table.
• Composite Attributes
• A separate column must be taken for all simple attributes of the composite
attribute.

[Link]

[Link]
 Q What should be the condition for total participation of the entity in a
relation?
a) Maximum cardinality should be one

b) Minimum cardinality should be zero

c) Minimum cardinality should be one

d) None of these

[Link]

[Link]
 Q The minimum number of tables required to convert the following ER diagram to
relation model is _____________

[Link]

[Link]
 Q The minimum number of tables required to convert the following ER diagram to
Relational model is ____________

[Link]

[Link]
 Trapes
• It is possible that even after all efforts there remain some problem with ER diagram. These
problems are called trapes. There are two types of trapes in ER diagram.

• FAN TRAP - If two or more 1 to M relationships are emerging out from single entity. Then
there will be a FAN trap.

[Link]

[Link]
 • A single site contains many departments and employs many staff. However,
which staff work in a particular department

• The fan trap is resolved by restructuring the original ER model to represent the
correct association.

[Link]

[Link]
 • CHASM TRAP - If two directly related entities are connected through another(third)
entity with partial participation then there is a chasm trap. So, logic suggests the
existence of a relationship between entity sets, but the relationship does not exist
between certain entity occurrences in ER diagram.

• A model suggests the existence of a relationship between entity types, but the
pathway does not exist between certain entity occurrences. Following is the example
in different notations.

[Link]

[Link]
 • How to eliminate - Create direct relationship between these 2 entities.

[Link]

[Link]
 • ADVANTAGES OF E-R DIGRAM
• Constructs used in the ER diagram can easily be transformed into relational
tables.
• It is simple and easy to understand with minimum training.

• DISADVANTAGE OF E-R DIGRAM

• Loss of information content.
• Limited constraints representation.
• It is overly complex for small projects.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
ER Diagram

Subtopic
Conversion of ER Diagram into Relational Model

Lesson

Trapes

Lesson 7 of 45
 Trapes
• It is possible that even after all efforts there remain some problem with ER diagram. These
problems are called trapes. There are two types of trapes in ER diagram.

• FAN TRAP - If two or more 1 to M relationships are emerging out from single entity. Then
there will be a FAN trap.

[Link]
• A single site contains many departments and employs many staff. However,
which staff work in a particular department

• The fan trap is resolved by restructuring the original ER model to represent the
correct association.

[Link]
• CHASM TRAP - If two directly related entities are connected through another(third)
entity with partial participation then there is a chasm trap. So, logic suggests the
existence of a relationship between entity sets, but the relationship does not exist
between certain entity occurrences in ER diagram.

• A model suggests the existence of a relationship between entity types, but the
pathway does not exist between certain entity occurrences. Following is the example
in different notations.

[Link]
• How to eliminate - Create direct relationship between these 2 entities.

[Link]
• ADVANTAGES OF E-R DIGRAM
• Constructs used in the ER diagram can easily be transformed into relational
tables.
• It is simple and easy to understand with minimum training.

• DISADVANTAGE OF E-R DIGRAM

• Loss of information content.
• Limited constraints representation.
• It is overly complex for small projects.

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Relational Model & Functional Dependencies

Subtopic
Basics of Relational Model & Anomalies

Lesson

Basics of Relational Model & Anomalies

Lesson 8 of 45
 Relational Model &
Functional Dependencies
Basics of Relational Model &
Anomalies

[Link]

[Link]
 RELATIONAL DATABASE MANAGEMENT SYSTEM

• A Relational Database Management System (RDBMS) is a

software system that uses a relational model to create,
manage, and query data in databases through tables linked by
defined relationships. Most modern commercial and open-
source database applications are relational in nature.
• Based on the relational model specified by Edgar F. Codd. The
father of modern relational database design in 1970.

[Link]

[Link]
 BASICS OF RDBMS
• Domain (set of permissible value in particular column) is a set of atomic values. By atomic
we mean that each value in the domain is indivisible as far as the formal relational model is
concerned. A common method of specifying a domain is to specify a data type from which the
data values forming the domain are drawn.

• E.g. Names: The set of character strings that represent names of persons.

Domain/ NAME ID CITY COUNTRY HOBBY

Field/ NISHA 1 AGRA INDIA PLAYING

Column/ NIKITA 2 DELHI INDIA DANCING

Arity/ AJAY 3 AGRA INDIA CHESS

Degree ARPIT 4 PATNA INDIA READING

[Link]

[Link]
 • Table (Relation) - A Relation is a set of tuples/rows/entities/records.

• Tuple - Each row of a Relation/Table is called Tuple.

• Arity/Degree - No. of columns/attributes of a Relation. E.g. - Arity is 5 in Table Student.

• Cardinality - No of rows/tuples/record of a Relational instance. E.g. - Cardinality is 4 in table

Student. NAME ID CITY COUNTRY HOBBY
NISHA 1 AGRA INDIA PLAYING
Rows/Tuples/Record/ NIKITA 2 DELHI INDIA DANCING
Cardinality AJAY 3 AGRA INDIA CHESS
ARPIT 4 PATNA INDIA READING

[Link]

[Link]
 Properties of Relational tables
1. Cells contains atomic values

1. Values in a column are of the same kind

1. Each row is unique

1. No two tables can have the same name in a relational schema.

1. Each column has a unique name

1. The sequence of rows is insignificant

1. The sequence of columns is insignificant.

[Link]

[Link]
 Problems in relational database

• Update Anomalies- Anomalies that cause redundant work to be done during

insertion into and Modification of a relation and that may cause accidental loss
of information during a deletion from a relation
• Insertion Anomalies

• Modification Anomalies

• Deletion Anomalies

[Link]

[Link]
 • Insertion anomalies: An independent piece of information cannot be recorded
into a relation unless an irrelevant information must be inserted together at the
same time.
• Modification anomalies: The update of a piece of information must occur at
multiple locations.
• Deletion Anomalies: The deletion of a piece of information unintentionally
removes other information.

Roll no name Age Br_code Br_name Br_hod_name

1 A 19 101 Cs Abc
2 B 18 101 Cs Abc
3 C 20 101 Cs Abc
4 D 20 102 Ec Pqr
[Link]

[Link]
 Roll no name Age Br_code Br_name Br_hod_name
1 A 19 101 Cs Abc
2 B 18 101 Cs Abc
3 C 20 101 Cs Abc
4 D 20 102 Ec Pqr
PK FK PK

Roll no name Age Br_code Br_code Br_name Br_hod_name

1 A 19 101 101 Cs Abc
2 B 18 101 102 ec Pqr
3 C 20 101
4 D 20 102

[Link]

[Link]
 Purpose of Normalization
• With out normalization data base system may be inaccurate, slow and inefficient and they
might not produce the data we expect. Normalization may be simply defined as refinement
process.
• Which includes creating tables and establishing relationships between those tables according
to rules designed both to protect data and make the database more flexible by eliminating
two factors;
• Redundancy
• Inconsistent Dependency

• Conclusion: Like every paragraph must have a single idea similarly every table must have a
single idea and if a table contains more than one idea then that table must be decomposed
until each table contains only one [Link] need some tools to approach this decomposition
or normalization on large database which contains a number of table, and that tool is
functional dependencies.
[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Relational Model & Functional Dependencies

Subtopic
Functional Dependencies & Axioms

Lesson

Functional Dependencies & Axioms

Lesson 9 of 45
 Relational Model &
Functional Dependencies
Functional Dependencies & Axioms

[Link]

[Link]
 Br_code Br_hod_name

[Link]

Br_code [Link] Br_hod_name

 Functional Dependency कोई बताता नह ,ीं इसक feel आ जात है

[Link]

[Link]
 FUNCTIONAL DEPENDENCY
• A formal tool for analysis of relational schemas.
X Y Z
• In a Relation R, if ‘α’ ⊑ R AND ‘β’ ⊑ R, then attribute or a 1 4 2
Set of attribute ‘α’ Functionally derives an attribute or 1 4 3
set of attributes ‘β’, iff each ‘α’ value is associated with
precisely one ‘β’ value.
2 6 3
3 2 2
• For all pairs of tuples t1 and t2 in R such that
• If T1[α] = T2[α]
• Then, T1[β] = T2[β]

[Link]

[Link]
 • Trivial Functional dependency - If β is a subset of α, then the functional
dependency α → β will always hold.
X Y Z
जजसका होना न होना बराबर हो 1 4 2
1 4 3
2 6 3
3 2 2

[Link]

[Link]
 1. α - Determinant (Determines β value).

1. β - Dependent (Dependent on α).

1. If k → R, the K is a super key of R

1. Note: A functional dependency is a property of the relation schema R, not of a particular

legal relation state/instance r of R.

X Y Z
1 4 2
1 4 3
2 6 3
3 2 2
[Link]

[Link]
 • Shortcut Steps to find weather a FD from α → β can be concluded on a given
instance or not

1. Find Weather all values of α are different or not, if yes then FD valid

1. Find Weather all values of β are same or not, if yes then FD valid

1. जब कुछ भ काम ना करे , Try to find two same values of α on which we get
different values of β

[Link]

[Link]
 Q Consider the following relation instance, Which of the following
dependencies are satisfied by the above relation instance?

a) A → B, BC → A A B C
1 2 4
b) C → B, CA → B 3 5 4
3 7 2
c) B → C, AB → C 1 4 2

d) A → C, BC → A
[Link]

[Link]
 Q Consider the following relation instance, which of the following
dependency doesn’t hold

A) A → b A B C

1 2 3
B) BC → A
4 2 3

C) B → C 5 3 3

D) AC → B
[Link]

[Link]
 Q From the following instance of the relation schema R (A, B, C) we can conclude
that

a) A functionally determines B, B functionally determines C

b) B functionally determines C, C functionally determines A

c) A functionally determines B, but B doesn’t functionally determine C

d) None of these

[Link]

[Link]
 Q Which of the following dependencies are satisfied by the relation
instance?
A B C
A→B 1 1 4
1 2 4
B→C
2 1 3
B→A 2 2 3

C→B 2 4 3

C→A
[Link]

A→C [Link]
 Q Which of the following dependencies are satisfied by the relation instance?

XZ → X
X Y Z
XY → Z
1 4 3
Z→Y
1 5 3
Y→Z
4 6 3
XZ → Y
3 2 2

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Relational Model & Functional Dependencies

Subtopic
Functional Dependencies & Axioms

Lesson

Understanding Armstong Axioms notes

Lesson 10 of 45
 ARMSTRONG’S AXIOMS

1. An axiom or postulate is a statement that is taken to be true, to serve as

a premise or starting point for further reasoning and arguments.

1. Armstrong's axioms are a set of axioms (or, more precisely, inference

rules) used to infer all the functional dependencies on a relational
database.

1. They were developed by William W. Armstrong in his 1974 paper.

1. The axioms are sound in generating only functional dependencies in
the closure of a set of functional dependencies (denoted as F+) when
applied to that set (denoted as F).

[Link]
 Armstrong Axioms

• Reflexivity: If Y is a subset of X, then X → Y

• Augmentation: If X → Y, then XZ → YZ

• Transitivity: If X → Y and Y → Z, then X → Z

[Link]
From these rules, we can derive these secondary rules-

• Union: If X → Y and X → Z, then X → YZ

• Decomposition: If X → YZ, then X → Y and X → Z

• Pseudo transitivity: If X → Y and WY → Z, then WX → Z

• Composition: If X → Y and Z → W, then XZ → YW

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Relational Model & Functional Dependencies

Subtopic
Attribute Closure & Equivalence of Sets

Lesson

Attribute Closure & Equivalence of Sets

Lesson 11 of 45
 Relational Model &
Functional Dependencies
Attribute Closure & Equivalence of
Sets

[Link]

[Link]
 ATTRIBUTES CLOSURE/CLOSURE ON ATTRIBUTE SET/ CLOSURE SET OF ATTRIBUTES

• Attribute closure of an attribute set can be defined as set of attributes which can
be functionally determined from F.
• DENOTED BY F+

• Set of all attributes Functionally determined by X either directly from FD’S or

logically derived.

[Link]

[Link]
 DIRECT METHOD

E.g. In a Relation R (A, B, C, D), with set of functional dependencies as-

{
A→B
B→C
AB → D
}
A+ =

[Link]

[Link]
 Q R(ABCDEFG)

A→B

BC → DE

AEG → G

(AC)+ =?
[Link]

[Link]
 Q R(ABCDE)
A → BC

CD → E

B→D

E→A

(B)+ =
[Link]

[Link]
 Q R(ABCDEF)

AB → C

BC → AD

D→E

CF → B

(AB)+ =
[Link]

[Link]
 Q R(ABCDEFGH)
A → BC

CD → E

E→C

D → AEH

ABH → BD

DH → BC

(BCD)+ =
[Link]

[Link]
 Q Consider the following functional dependencies over the relation R (ABCDEF)
A→B

C → DE

AC→F

What is the closure of (AC)?

a) ACF

b) ACFDE

c) ACEFDB
[Link]

d) ACFD [Link]
 Q In a Relation R (A, B, C, D) Given

F= {A → B, B → C, C → D}

To check whether, A → C is valid or not ?

[Link]

[Link]
 ARMSTRONG’S AXIOMS

1. An axiom or postulate is a statement that is taken to be true, to serve as

a premise or starting point for further reasoning and arguments.

1. Armstrong's axioms are a set of axioms (or, more precisely, inference

rules) used to infer all the functional dependencies on a relational
database.

1. They were developed by William W. Armstrong in his 1974 paper.

1. The axioms are sound in generating only functional dependencies in
the closure of a set of functional dependencies (denoted as F+) when
applied to that set (denoted as F).

[Link]

[Link]
 Armstrong Axioms

• Reflexivity: If Y is a subset of X, then X → Y

• Augmentation: If X → Y, then XZ → YZ

• Transitivity: If X → Y and Y → Z, then X → Z

[Link]

[Link]
 From these rules, we can derive these secondary rules-

• Union: If X → Y and X → Z, then X → YZ

• Decomposition: If X → YZ, then X → Y and X → Z

• Pseudo transitivity: If X → Y and WY → Z, then WX → Z

• Composition: If X → Y and Z → W, then XZ → YW

[Link]

[Link]
 Why Armstrong axioms refers to the Sound and Complete

• By sound, we mean that given a set of functional dependencies F specified on a relation

schema R, any dependency that we can infer from F by using the primary rules of Armstrong
axioms holds in every relation state r of R that satisfies the dependencies in F.

• By complete, we mean that using primary rules of Armstrong axioms repeatedly to infer
dependencies until no more dependencies can be inferred results in the complete set of all
possible dependencies that can be inferred from F.

[Link]

[Link]
 APPLICATION OF ATTRIBUTE CLOSURE

Equivalence of Two FD sets-

Two FD sets F1 and F2 are equivalent if –

F1+ = F2+

Or

F1 ⊑ F2+ and F2 ⊑ F1+

Race एक जगह से शुरू होने जरूरी है

[Link]

[Link]
 Q Consider the following set of fd R(ACDEH)

di F G
A→C A → CD

AC → D
E → AH
E → AD

E→H

[Link]

[Link]
 Q R(VWXYZ)
F G
V→W V→W

VW → X V→X

Y → VX Y→V

Y→Z Y→Z

[Link]

[Link]
 Q Consider the following set of fd R(ABCDE)

F G
B → CD B → CDE

AD → E A → BC

B→A AD → E

[Link]

[Link]
 Q consider the following relation R(PQRS)

F G
P→Q P → QR

Q→R
R→S
R→S

[Link]

[Link]
 Q consider the following relation R(ABCD)
F G
A→B A → BC

B→C B→A

C→A C→A

[Link]

[Link]
 Q consider the following relation R(VWXYZ)
F G
W→X W → XY

WX → Y Z → WX

Z → WY

Z→V

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Relational Model & Functional Dependencies

Subtopic
Minimal Cover (Canonical Cover)

Lesson

Minimal Cover (Canonical Cover)

Lesson 12 of 45
 Relational Model &
Functional Dependencies
Minimal Cover (Canonical Cover)

[Link]

[Link]
 To find the MINIMAL COVER /CANONICAL COVER/IRREDUCIBLE SET
• Minimal cover- It means to eliminate any kind of redundancy from a FD set.

• A canonical cover of a set of functional dependencies, F is a simplified set of functional

dependencies that has the same closure as the original set F.

• There may be any following type of redundancy in the set of functional dependencies: -
• Complete production may be Redundant.

• One or more than one attributes may be redundant on right hand side of a production.

• One or more than one attributes may be redundant on Left hand side of a production.

[Link]

[Link]
 Gate aspirant के हाथ ों से minimize ह ने के बाद

[Link]

[Link]
 Q R(ABCD)

A→B

C→B

D → ABC

AC → D

[Link]

[Link]
 Procedure to find MINIMAL COVER
• Use decomposition rule wherever applicable so that RHS of a production/FD contains only
single attribute.

• Remove extraneous attribute on LHS of a production by finding the closure for every possible
subset, if in any case the closure is same it means remaining attributes are redundant.

• For every production find the closure value of LHS of production keeping the production in a
set, and next time ignoring the production to be in a set. If both closure set matches, it means
the production is redundant.

माफ़ कर या साफ़ कर
[Link]

[Link]
 Q Find the minimal cover for the FD set
{AC → BD, A → C, B → C, D → C}

a) {A → B, A → C, B → C, A → D} b) {A → B, A → D, A → C, B → D}

c) {A → B, A → D, A → C, B → C} d) {A → B, A → D, D → C, B → C}

[Link]

[Link]
 Q R(WXYZ)

X→W

WZ → XY

Y → WXZ

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Keys & Integrity Constraints

Subtopic
Super Key, Candidate Key & Primary Key

Lesson

Super Key, Candidate Key & Primary Key

Lesson 13 of 45
 Keys & Integrity
Constraints
Super Key, Candidate Key & Primary Key

[Link]
 Key
• For identifying the uniqueness of a tuple, we take help of an attribute or set of
attributes. Uniqueness of tuple is needed as a Relation is a Set and Set disallows
duplicity of elements.
• Various Keys used in database System are as follows-

[Link]
 Super key
• Set of attributes using which we can identify each tuple uniquely is called Super key, i.e. the
set of attributes used to differentiate each tuple of a relation.

• Let X be a set of attributes in a Relation R , if X+(Closure of X) determines all attributes of R

then X is said to be Super key of R .

• There should be at least one Super key with Not Null constraint.

[Link]
• A relation of ‘n’ attributes with every attribute being a super
key, then there are 2n -1.

• Biggest Super Key possible in a Relation is a Set comprising all

attributes of a Relation

[Link]
 Candidate key
1. Minimum set of attributes that differentiates the tuple of a Relation. No proper
subset as super key Also called as MINIMAL SUPER KEY.
2. There should be at least one candidate key with Not Null constraint.
3. Prime attribute - Attributes that are member of candidate Keys are called Prime
attributes.

[Link]
 Primary key
1. One of the candidate keys is selected by database administrator as a Primary
Key.

2. Primary Key attribute are not allowed to have Null values.

3. At most one Primary Key per table is allowed in RDMS.

4. Candidate key which are not chosen as primary key is alternate key.

[Link]
Q R(WXYZ)

X -> W

WZ -> XY

Y -> WXZ

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Keys & Integrity Constraints

Subtopic
Foreign Key & Referential Integrity

Lesson

Foreign Key & Referential Integrity

Lesson 14 of 45
 Keys & Integrity
Constraints
Foreign Key & Referential Integrity

[Link]

[Link]
 Foreign Keys
• A foreign key is a column or group of columns in a relational database table that refers the
primary key of the same table or some other table to represent relationship.
• The concept of referential integrity is derived from foreign key theory.

[Link]

[Link]
 Roll no name Age Br_code Br_name Br_hod_name
1 A 19 101 Cs Abc
2 B 18 101 Cs Abc
3 C 20 101 Cs Abc
4 D 20 102 Ec Pqr
PK FK PK

Roll no name Age Br_code Br_code Br_name Br_hod_name

1 A 19 101 101 Cs Abc
2 B 18 101 102 ec Pqr
3 C 20 101
4 D 20 102

[Link]

[Link]
 PK FK
Roll no CR
1 1
2 2
3 1
4 2
5 1
6 2
7 1
8 2
9 1
10 2
11 1
12 2
13 1
14 2
[Link]
15 null
[Link]
 Q Consider the following table consisting of two attributes A and B, where ‘B’ is the A B
foreign key referring the candidate key ‘A’ with on – delete cascade option. When we 8 9
delete the tuple (3 2), we need to delete few tuples additionally in order to preserve 4 6
the referential integrity. The number of tuples that are remaining in the table when we 7 6
delete (3 2) and additional tuples if necessary is ______ 3 2
6 5
5 1
1 2
2 3

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Keys & Integrity Constraints

Subtopic
Composite & Alternate Keys

Lesson

Composite & Alternate Keys

Lesson 15 of 45
 Keys & Integrity
Constraints
Composite & Alternate Keys

[Link]

[Link]
 • Composite key – Composite key is a key composed of more than one
column sometimes it is also known as concatenated key.

• Secondary key – Secondary key is a key used to speed up the search

and retrieval contrary to primary key, a secondary key does not
necessary contain unique values.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Normalization (1NF - BCNF)

Subtopic
Introduction to Normalization & 1NF

Lesson

Introduction to Normalization & 1NF

Lesson 16 of 45
 Normalization
(1NF - BCNF)
Introduction to Normalization & 1NF

[Link]

[Link]
 • Normalization of data (Decomposition of Relation) can be considered a process of analyzing the given
relation schema to achieve the desirable properties of minimizing redundancy using Decomposition.
• The tool we use for normalization is functional dependencies and candidate keys.
• Functional dependency can be used only to normalize up to BCNF.
• A series of normal form tests that can be carried out on individual relation schemas so that the
relational database can be normalized to any desired degree.
• 1NF>>>2NF>>3NF>>BCNF

[Link]

[Link]
 FIRST NORMAL FORM
• A Relation table is said to be in first normal form iff each attribute in each cell have single
value(atomic). Means a Relation should not contain any multivalued or composite attributes.

• Other implications of first normal form

• Every row should be unique, that is no two rows should have the same values of all the
attributes.
• There must be a primary key.
• Every column should have a unique name
• Order of row and column is irrelevant

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Normalization (1NF - BCNF)

Subtopic
Second Normal Form (2NF)

Lesson

Second Normal Form (2NF)

Lesson 17 of 45
 Normalization
(1NF - BCNF)
Second Normal Form (2NF)

[Link]

[Link]
 SECOND NORMAL FORM
• Relation R is in 2NF if,
• R should be in 1 NF.
• R should not contain any Partial dependency. (that is every non-prime
attribute should be fully dependent upon candidate key)

[Link]

[Link]
 Prime attribute: - A attribute is said to be prime if it is part of any of the candidate key

Non-Prime attribute: - A attribute is said to be non-prime if it is not part of any of the candidate
key

Eg R(ABCD)
AB→CD
Here candidate key is AB so, A and B are prime attribute, C and D are non-prime attributes.

[Link]

[Link]
 PARTIAL DEPENDENCY- When a non – prime attribute is dependent only on a part (Proper
subset) of candidate key then it is called partial dependency. (PRIME > NON-PRIME)

TOTAL DEPENDENCY- When a non – prime attribute is dependent on the entire candidate key
then it is called total dependency.

e.g. R(ABCD) AB→D, A→C

[Link]

[Link]
 Q R(A, B, C) B→C

A B C A B B C
a 1 X A 1 1 X
b 2 Y B 2 2 Y
a 3 Z A 3 3 z
C 3 Z C 3
D 3 Z D 3
E 3 Z E 3

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Normalization (1NF - BCNF)

Subtopic
Third Normal Form (3NF)

Lesson

Third Normal Form (3NF)

Lesson 18 of 45
 Normalization
(1NF - BCNF)
Third Normal Form (3NF)

[Link]

[Link]
 THIRD NORMAL FORM

• Let R be the relational schema, it is said to be in 3 NF

• R should be in 2NF
• It must not contain any transitive dependency

[Link]

[Link]
 TRANSITIVE DEPENDENCY – A functional dependency from non-Prime attribute to non-Prime
attribute is called transitive

E.g.- R(A, B, C, D) with A as a candidate key

A→B
B→C [ transitive dependency]
C→D [transitive dependency]

[Link]

[Link]
 THIRD NORMAL FORM DIRECT DEFINATION

• A relational schema R is said to be 3 NF if every functional dependency

in R from α→β, either α is super key or β is the prime attribute

[Link]

[Link]
 A B C A B B C
A 1 P A 1 1 P
B 2 Q B 2 2 Q
C 2 Q C 2 3 R
D 2 Q D 2 4 S
E 3 R E 3
F 3 R F 3
G 4 S G 4

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Normalization (1NF - BCNF)

Subtopic
Boyce-Codd Normal Form (BCNF)

Lesson

Boyce-Codd Normal Form (BCNF)

Lesson 19 of 45
 Normalization
(1NF - BCNF)
Boyce-Codd Normal Form (BCNF)

[Link]

[Link]
 BCNF (BOYCE CODD NORMAL FORM)
A relational schema R is said to be BCNF if every functional dependency in R from
α→β

α must be a super key

E.g.- R (A, B, C, D)
{
AB→C [No violation of 2NF,3NF, BCNF]
C→D [violation of BCNF, C not a candidate/super key]
D→A [violation of BCNF, D not a candidate/super key]
} Candidate key= {AB}, {DB}, {CB}

[Link]

[Link]
 R(A, B, C) A B C A B C B
AB → C A C B A B B C
B B C B B C B
C→B B A D B A D A
A A E A A E A
C C B C C
D C B D C
E C B e C
F C B f c

[Link]

[Link]
 Some important note points on Normalization:
• If a relation R does not contain any non- trivial dependency, then R Is in BCNF.

• A Relation with two attributes is always in BCNF.

• A relation schema R consist of only simple candidate key then, R is always in 2NF but may or may not
be in 3NF or BCNF.

• A Relation schema R consist of only prime attributes then R is always in 3NF, but may or may not be in
BCNF.

• A relation schema R in 3NF and with only simple candidate keys, then R surely in BCNF.

[Link]

[Link]
 Q R(ABC) (AB, BC)

AB → C A B C

C→A

[Link]

[Link]
 Q R(ABCD)(AB)

AB → C A B C D

B→D

[Link]

[Link]
 Q R(ABCDE)(CE)

CE → D A B C D E

D→B

C→A

[Link]

[Link]
 Q R(ABCDE)(ACD, BCD, CDE)

A→B A B C D E

BC → E

DE → A

[Link]

[Link]
 Q R(ABCD)(AB, AD, BC, CD)

AB → CD A B C D

C→A

D→B

[Link]

[Link]
 Q R(ABCDE)(AB)

AB → C A B C D E

B→D

D→E

[Link]

[Link]
 Q R(ABCDEFGH)(AE)

A → BC A B C D E F G H

ABE → CDGH

C → GD

D→G

E→F
[Link]

[Link]
 Q R(WXYZ)(Y, XW, XZ)

Z→W W X Y Z

Y → XZ

XW → Y

[Link]

[Link]
 Q R(ABCDEF)(ABC, ACD)

ABC → D A B C D E F

ABD → E

CD → F

CDF → B
[Link]

[Link]
 Q R(ABCDE)(AB)
A B C D E
AB → C

B→D

D→E

[Link]

[Link]
 Q R(ABCDE)(A, E, BC, CD)

A → BC A B C D E

CD → E

B→D

E→A
[Link]

[Link]
 Q (ABCDE)(ACD, BCD, CDE)

A→B A B C D E

BC → E

DE → A

[Link]

[Link]
 Q R(ABCDE)(ABD)
A B C D E

BD → E

A→C

[Link]

[Link]
 Q R(ABCDEF) (A, BC, DEF)

A → BCDEF
A B C D E F
BC → ADEF

DEF → ABC

[Link]

[Link]
 Q R(ABCDE)(ac)
A B C D E
A→B

B→E

C→D

[Link]

[Link]
 Q R(ABCDE)(AB, BC, BD)

AB → CD A B C D E

D→A

BC → DE

[Link]

[Link]
 Q R(ABCD)(AB, BD)

AB → CD A B C D

D→A

[Link]

[Link]
 Q R(ABCDEF)(BF)

AB → C A B C D E F

C→D

B → AE

[Link]

[Link]
 Q R(ABCDEFGHIJ)(AB)

AB → C A B C D E F G H I J

A → DE

B→F

F → GH

D → IJ
[Link]

[Link]
 Q R(ABCDE)(BC, CD)

BC → ADE A B C D E

D→B

[Link]

[Link]
 Q R(ABCD)(AD, BD, CD)

A→B A B C D

B→C

C→A

[Link]

[Link]
 Q R(ABCDEF)(ABD, BCD)

AB → C A B C D E F

DC → AE

E→F

[Link]

[Link]
 Q R(VWXYZ)(VW, XW)

Z→Y V W X Y Z

Y→Z

X → YV

VW → X
[Link]

[Link]
 Q R(ABCDE)(AC)

A→B A B C D E

B→E

C→D

[Link]

[Link]
 Q R(ABCDEF)(C, D, AB, BE, BF)

AB → C
A B C D E F
C→D

D>BE

E>F

F>A
[Link]

[Link]
 Relation R has eight attributes ABCDEFGH. Fields of R contain only
atomic values.
F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional
dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.
The relation R is:
(a) in 1NF, but not in 2NF
(b) in 2NF, but not in 3NF
(c) in 3NF, but not in BCNF
(d) in BCNF
[Link]

[Link]
 Q Consider the relation schema R (A B C D) with following FD set
F = {A → BC, C → D}; The relation R is in ________
a) 1 NF

A B C D
b) 2 NF

c) 3 NF

d) BCNF
[Link]

[Link]
 Q Consider the relation R (ABCDE) with the FD set F = {A → CE, B → D,
AE → D}. Identify the highest normal form satisfied by the relation R.
a) 1 NF

A B C D E
b) 2 NF

c) 3 NF

d) BCNF
[Link]

[Link]
 Q Consider the relation schema R (A B C D) with following FD set
F = {A → BC, C → D}; The relation R is in ________
a) 1 NF

A B C D
b) 2 NF

c) 3 NF

d) BCNF
[Link]

[Link]
 Q Consider the relation R (ABCDE) with the FD set F = {A → CE, B → D,
AE → D}. Identify the highest normal form satisfied by the relation R.
a) 1 NF

A B C D E
b) 2 NF

c) 3 NF

d) BCNF
[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
File Organization & Indexing

Subtopic
Basics & Types of Indexing

Lesson

Basics & Types of Indexing

Lesson 20 of 45
File Organization &
Indexing
Basics & Types of Indexing

[Link]
 Indexing
• Theoretically relational database is derived from set theory, and in a set the order of elements
in a set is irrelevant, so does in relations(tables). But in practice implementation we have to
specify the order.
• A number of properties, like search, insertion and deletion will depend on the order in which
elements are stored in the tables. There are only two ways in which elements can be stored in
a table ordered (Sorted) or unordered (Unsorted).

[Link]
 File organization/ Organization of records in a file
Ordered file organization/Unordered file organization

• All the records in the file are ordered on some search key field.
• Here binary search is possible. (give example of book page searching)
• Maintenance (insertion & deletion) is costly, as it requires reorganization of entire file.
• Notes that we will get binary search only if we are using that key for searching on which
indexing is done, otherwise it will behave as unsorted file

• All the records are inserted usually in the end of the file so not ordered according to any field,
Because of this only linear search is possible, searching is slow.
• Maintenance (insertion & deletion) is easy, as it does not require re organization of entire file.

• Reason for indexing - For a large file when it contains a large number of records which will
eventually acquire large number of blocks, then its access will become slow.
[Link]
• Additional auxiliary access structure is called indexes, a data technique to efficiently retrieve
records from the database files based on some attributes on which the indexing has been
done. Indexing in database systems is similar to what we see in books.

[Link]
• Index typically provides secondary access path, which provide alternative way to access the
records without affecting the physical placement of records in the main file.

• The size of index file is way smaller than that of the main file, as index file record contain only
two columns key (attribute in which searching is done) and block pointer (base address of the
block of main file which contains the record holding the key), while main file contains all the
columns.
• Index can be created on any field of relation (primary key, non-key)

• One index file is designed according to an attribute, means more than one index file can be
designed for a main file.

• Index file is always ordered, irrespective of weather main file is ordered or unordered. So that
we can take the advantage of binary search.

• Indexing gives the advantage of faster time, but space taken by index file will be an overhead.

• Number of access required to search the correct block of main file is log2(number of blocks in
index file) + 1 [Link]
Indexing can be classified on number of criteria’s one of them could be
• Dense Index: In dense index, there is an entry in the index file for every search key value
in the main file. This makes searching faster but requires more space to store index records
itself. Note that it is not for every record, it is for every search key value. Sometime number of
records in the main file > number of search keys in the main file, for example if search key is
repeated.

• Sparse Index: If an index entry is created only for some records of the main file, then it is
called sparse index. No. of index entries in the index file < No. of records in the main file. Note:
- dense and sparse are not complementary to each other, sometimes it is possible that a
record is both dense and sparse.

[Link]
 TYPES OF INDEXING

Index

Single
Multilevel
level

Primary Clustering Secondary Simple

B tree B+ tree
Index Index index multilevel

∙ Single level index means we create index file for the main file, and then stop the
process.

∙ Multiple level index means, we further index the index file and keep repeating
the process until we get one block.
[Link]
 Basic term used in Indexing
• BLOCKING FACTOR = No. of Records per block= ⌊block size/record size⌋

• No of blocks required by file = ⌈no of records / blocking factor⌉

• If file is unordered then no of block accesses required to reach correct block which contain
the desired record is O(n), where n is the number of blocks.

• if file is ordered then no of block accesses required to reach correct block which contain the
desired record is O(log2n), where n is the number of blocks.

[Link]
 PRIMARY INDEXING
• Main file is always sorted according to primary key.

• Indexing is done on Primary Key, therefore called as primary indexing

• Index file have two columns, first primary key and second anchor pointer (base
address of block)

• It is an example of Sparse Indexing.

• Here first record (anchor record) of every block gets an entry in the index file

• No. of entries in the index file = No of blocks acquired by the main file.

[Link]
Q Suppose we have ordered file with records stored r = 30,000 on a disk with Block
Size B = 1024 B. File records are of fixed size and are unspanned with record length
R = 100 B. Suppose that ordering key field of file is 9 B long and a block pointer is 6
B long, Implement primary indexing?

[Link]
 CLUSTERED INDEXING
• Main file will be ordered on some non-key attributes

• No of entries in the index file = no of unique values of the attribute on which

indexing is done.

• It is the example of Sparse as well as dense indexing

[Link]
 SECONDARY INDEXING
• Most common scenarios, suppose that we already have a primary indexing on primary key,
but there is frequent query on some other attributes, so we may decide to have one more
index file with some other attribute.

• Main file is ordered according to the attribute on which indexing is done(unordered).

• Secondary indexing can be done on key or non-key attribute.

• No of entries in the index file is same as the number of entries in the main file.

• It is an example of dense indexing.

[Link]
Q Suppose we have ordered file with records stored r = 30,000 on a disk with Block Size
B = 1024 B. File records are of fixed size and are unspanned with record length R = 100
B. Suppose that ordering key field of file is 9 B long and a block pointer is 6 B long,
Implement Secondary indexing?

[Link]
 MULTILEVEL INDEXING
• Multi-level Index helps in breaking down the index into several smaller indices in order to
make the outermost level so small that it can be saved in a single disk block-0, which can easily
be accommodated anywhere in the main memory.

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
File Organization & Indexing

Subtopic
Basics & Types of Indexing

Lesson

Multilevel Indexing notes

Lesson 21 of 45
 MULTILEVEL INDEXING
• Multi-level Index helps in breaking down the index into several smaller indices in order to
make the outermost level so small that it can be saved in a single disk block-0, which can
easily be accommodated anywhere in the main memory.

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
File Organization & Indexing

Subtopic
B-Trees & B+ Trees (Structure & Insertion)

Lesson

B-Trees & B+ Trees (Structure & Insertion)

Lesson 22 of 45
 File Organization &
Indexing
B-Trees & B+ Trees (Structure &
Insertion)

[Link]

[Link]
 Reason to have B tree and B+ tree
• After studying indexing in detail now we understand that an index file is always sorted in
nature and will be searched frequently, and sometimes index files can be so large that even
we want to index the index file (Multilevel index), therefore we must search best data
structure to meet our requirements.

• There are number of options in data structure like array, stack, link list, graph, table etc. but
we want a data structure which support frequent insertion deletion, and modify it self
accordingly but at the same time also provide speed search and give us the advantage of
having a sorted data.

[Link]

[Link]
 • If we look at the data structures option then tree seem to be the most appropriate but every
kind of tree in the available option have some problems either simple tree or binary search
tree or AVL tree, so we end up on designing new data structure called B-tree which are kind of
specially designed for sorted stored index files in databases.

• In general, with multilevel indexing, we require dynamic structure, b and b+ tree is generalized
implementation of multilevel indexing, which are dynamic in nature, that is increasing and
decreasing number of records. In the first level index file can be easily supported by other
level index.

• B tree and B+ tree also provides efficient search time, as the height of the structure is very less
and they are also perfectly balanced.

[Link]

[Link]
 B tree
• A B-tree of order m if non-empty is an m-way search tree in which.
• The root has at least zero child nodes and at most m child nodes.
• The internal nodes except the root have at least celling(m/2) child nodes and at most m
child nodes.
• The number of keys in each internal node is one less than the number of child nodes and
these keys partition the subtrees of the nodes in a manner similar to that of m-way search
tree.
• All leaf nodes are on the same level(perfectly balanced).
Root Internal except Root Leaf
Rules MAX MIN
Rules MAX MIN Rules MAX MIN
CHILD m 0
CHILD m ⌈m/2⌉ CHILD 0 0
DATA m-1 1
DATA m-1 ⌈m/2⌉ - 1 DATA m-1 ⌈m/2⌉ - 1

[Link]

[Link]
 Insertion in B-TREE

• A B-tree starts with a single root node (which is also a leaf node) at level 0 (zero). Once the root node is full with
m – 1 search key values and we attempt to insert another entry in the tree, the root node splits into two nodes at
level 1.

• Only the middle value is kept in the root node, and the rest of the values are split evenly between the other two
nodes. When a non-roof node is full and a new entry is inserted into it, that node is split into two nodes at the
same level, and the middle entry is moved to the parent node along with two pointers to the new split nodes.

• If the parent node is full, it is also split. Splitting can propagate all the way to the root node, creating a new level
if the root is split.

[Link]

[Link]
 Q Consider the following elements 5, 10, 12, 13, 14, 1, 2, 3, 4
insert them into an empty b-tree of order = 3.

[Link]

[Link]
 Q Consider the following elements 5, 10, 12, 13, 14, 1, 2, 4, 20, 18, 19,
17, 16, 15, 25, 23, 24 insert them into an empty b-tree of order = 5.

[Link]

[Link]
 Q: Consider the Following B-tree of order m = 6, delete the following
nodes H, T, R, E, A, C, S in sequence?
M
B-tree structure: D
/ \
QL
/ \ / \
AC EF AKL NP RS T
|
WXYZ

[Link]

[Link]
 Conclusion
• Very less internal fragmentation, memory utilization is very good.

• Less number of nodes(blocks) are used and height is also optimized, so access will be very fast.

• Difficulty of traversing the key sequentially. Means B-TREE do not hold good for range-based
queries of database.

[Link]

[Link]
 B+ Tree
Q Consider the following elements 5, 10, 12, 13, 14, 1, 2, 3, 4 insert them into an
empty b+ tree of order = 3.

[Link]

[Link]
 Insertion in B+ Tree
• Start from root node and proceed towards leaf using the logic of binary search tree. Value is
inserted in the leaf.

• If overflow condition occurs pick the median and push it into the parent node. Also copy the
median or key inserted in parent node to the left or right child node.

• Repeat this procedure until tree is maintained.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
File Organization & Indexing

Subtopic
Deletion in B-Trees & B+ Trees

Lesson

Deletion in B-Trees & B+ Trees

Lesson 23 of 45
 File Organization &
Indexing
Deletion in B-Trees & B+ Trees

[Link]

[Link]
 B tree
• A B-tree of order m if non-empty is an m-way search tree in which.
• The root has at least zero child nodes and at most m child nodes.
• The internal nodes except the root have at least celling(m/2) child nodes and at most m
child nodes.
• The number of keys in each internal node is one less than the number of child nodes and
these keys partition the subtrees of the nodes in a manner similar to that of m-way search
tree.
• All leaf nodes are on the same level(perfectly balanced).

Root Internal except Root Leaf

Rules MAX MIN Rules MAX MIN Rules MAX MIN
CHILD m 0
CHILD m ⌈m/2⌉ CHILD 0 0
DATA m-1 1
DATA m-1 ⌈m/2⌉ - 1 DATA m-1 ⌈m/2⌉ - 1

[Link]

[Link]
 Q Consider the Following B-tree of order m=6, delete the following
nodes H, T, R, E, A, C, S in sequence?

[Link]

[Link]
 Deletion in B-TREE

• If the deletion is from the leaf node and leaf node is satisfying the minimal condition even after the
deletion, then delete the value directly.

• If deletion from leaf node renders leaf node in minimal condition, then first search the extra key in left
sibling and then in the right sibling. Largest value from left sibling or smallest value from right sibling is
pushed into the root node and corresponding value can be fetched from parent node to leaf node.

• If the deletion is to be from internal node, then first we check for the extra key in the left and then in
the right child. If we find one, we fetch the value in the required node. And delete the key.

[Link]

[Link]
 • If deletion of a value causes a node to be less than half full, it is combined with its neighboring
nodes, and this can also propagate all the way to the root. Hence, deletion can reduce the
number of tree levels.

• It has been shown by analysis and simulation that, after numerous random insertions and
deletions on a B-tree, the nodes are approximately 69 percent full when the number of values
in the tree stabilizes. This is also true of B+ trees.

• If this happens, node splitting and combining will occur only rarely, so insertion and deletion
become quite efficient. If the number of values grows, the tree will expand without a
problem—although splitting of nodes may occur, so some insertions will take more time.

[Link]

[Link]
 Q Consider the following elements 5, 8, 1, 7, 3, 12, 9, 6 insert them into
an empty b+ tree of order = 3. and then delete following nodes in
sequence 9, 8, 12?

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Introduction, Components & Structure

Lesson

Introduction, Components & Structure

Lesson 24 of 45
 SQL
Introduction, Components &
Structure

[Link]

[Link]
 Introduction to SQL
Structured Query Language (SQL) is the most widely used tool for interacting with modern databases. It
allows us to communicate with a Relational Database Management System (RDBMS) using a standardized set
of commands.

● The Industry Standard: While there are over 50 different database languages, SQL is the dominant choice
used in almost every professional programming and data management environment.
● Beyond Simple Queries: Although called a "query language," SQL does much more than just look up data;
it is used to define the database structure, modify existing data, and specify security constraints.
● Domain-Specific Language: Unlike general-purpose programming languages (like C++ or Java), SQL is
specifically designed to manage and manipulate data held in structured tables.
● Mathematical Foundation: SQL is built upon robust mathematical models, specifically Relational Algebra
(which is procedural) and Tuple Relational Calculus (which is non-procedural).

[Link]

[Link]
[Link]

[Link]
[Link]

[Link]
 Overview of the SQL Query Language
SQL was not built overnight; it evolved from a research project into the global standard for data
management we use today.
● Origins at IBM: IBM developed the original version in the early 1970s as part of the "System R"
project. It was originally named Sequel (Structured English Query Language) before being shortened
to SQL.
● The Transition to Standard: As SQL established itself as the standard relational database language,
formal bodies took over to ensure consistency across different software.
● The ANSI/ISO Standards: * 1986: The first official standard, SQL-86, was published by ANSI and
ISO.
○ Evolution: Major updates followed, including SQL-89, SQL-92, and the massive SQL:1999.
○ Modern Status: The language continues to evolve to handle modern data needs, with the most
recent standard being SQL:2023.

[Link]

[Link]
 Parts of SQL
SQL is divided into several sub-languages, each responsible for a different aspect of database management.

● Data-Definition Language (DDL): Used to define, modify, or delete the actual structure (schema) of the
database.
○ Integrity: Includes commands to set rules the data must follow, such as preventing empty values in a Primary
Key.
○ View Definition: Allows for the creation of "Views" or specific ways to display sorted data to users.
○ Authorization: Specifies access rights, such as making a table "Read Only" for certain users.
● Data-Manipulation Language (DML): Provides the ability to interact with the actual information stored in the
tables.
○ Tasks: Includes commands to query (search), insert, delete, and modify records.
● Transaction Control: Commands that manage the beginning and ending of tasks to ensure data safety.
○ Operations: Includes COMMIT to save changes permanently or ROLLBACK to undo changes if an error
occurs.
● Embedded & Dynamic SQL: Defines how SQL statements are placed inside general-purpose programming
languages like C, C++, or Java.

[Link]

[Link]
 Basic Structure of SQL Queries
● A SQL query is a mathematical function that takes one or more tables as input and produces
exactly one unnamed table as output.
● Formal Syntax (Copy the text below for your Sheet):
1. SELECT A1, A2, ..., An
2. FROM r1, r2, ..., rm
3. WHERE P;
4. A represents: The column names you want to see.
5. r represents: The input table names.
6. P represents: The condition (predicate) to filter data.
● Logical Order of Execution The database engine processes the query in this specific internal
sequence:
1. FROM: First, the system identifies the source tables.
2. WHERE: Next, it filters the rows based on the condition.
3. SELECT: Finally, it picks the columns to display in the result.

[Link]

[Link]
 Key Operational Rules

● Input/Output Rule: A query takes one or more tables as input, but output is always a single, anonymous relation.
● Duplicate Handling:
○ ALL (Default): SQL maintains duplicates to save time. You can use the ALL keyword explicitly.
○ DISTINCT: Use this keyword after SELECT to force the removal of duplicate rows.
● Mandatory Clauses: Only SELECT and FROM are strictly required; if WHERE is omitted, the condition is treated as true
for all rows.
● Case Sensitivity: SQL is generally not case-sensitive; SELECT is the same as select.

Select A1, A2,..., An (Column name)

from r1, r2,... , rm (Relation/table name)
Where P; (Condition)

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Select, Where, Distinct

Lesson

Select, Where, Distinct

Lesson 25 of 45
 SQL
Select, Where, Distinct

[Link]

[Link]
 Select Clause
● In SQL, the SELECT clause is used to pick specific columns from a table. This is known as Vertical Filtering.
● Formal Syntax SELECT A1, A2, ..., An
● Relational Algebra Connection: The SELECT clause performs the same function as the Projection (Π)
operation.
● Column Order: The columns will appear in your result in the exact same order you list them in the query.
● The Asterisk (*) Shortcut: To make things easy, you can use SELECT * to tell the system you want to see all
available columns.
● Readability: Even if you want all columns, explicitly using SELECT makes the query more readable and
standardized.
● Arithmetic Expressions: You can use operators like +, -, / and * directly in the SELECT clause to perform
calculations on your data.
○ Crucial Note: These calculations only change how the data is displayed in your result; they do not change
the actual data stored in the table.

[Link]

[Link]
 Q Write a SQL query to find all the details of bank branches?

[Link]

[Link]
 Q Write a SQL query to find each loan number along with loan amount?

[Link]

[Link]
 Q Write a SQL query to find the name of all customer without duplication having
bank account?

[Link]

[Link]
 Q Write a SQL query to find all account_no and balance with 6% yearly interest
added to it?

[Link]

[Link]
 Select Clause with where clause
● The WHERE clause is used to filter records that fulfill a specific condition. While the SELECT clause filters columns vertically,
the WHERE clause performs Horizontal Filtering.
● Relational Algebra Connection: It is equivalent to the Selection ($\sigma$) operation, where we specify a "Predicate" or
condition.
● Comparison Operators: You can use these symbols to compare values, strings, or results of calculations:
○ =, < (less than), > (greater than), <=, >=.
○ <> (not equal to).
● Logical Connectives: To build complex rules, you can join multiple conditions using AND, OR, and NOT.
● The BETWEEN Operator: This simplifies queries where you need a value to fall within a specific range.
○ BETWEEN: Checks if a value is >= a low value AND <= a high value.
○ NOT BETWEEN: Checks if a value falls outside that range.

[Link]

[Link]
 Q Write a SQL query to find all account_no where balance is less the 1000?

[Link]

[Link]
 Q Write a SQL query to find branch name which is situated in Delhi and having
assets less than 1,00,000?

[Link]

[Link]
 Q Write a SQL query to find branch name and account_no which has balance
greater than equal to 1,000 but less than equal to 10,000?

[Link]

[Link]
 DISTINCT Clause

● The DISTINCT clause is used within a SELECT statement to remove duplicate values from your result set, ensuring that only unique records are
returned.
● Why Use DISTINCT?
○ Eliminate Redundancy: Clean up query output by hiding repetitive data.
○ Identify Unique Values: Quickly see all unique entries within a specific column, such as a list of all cities where students reside.
○ Data Analysis: Essential for reporting and getting accurate counts of unique items.
● Syntax & Example
○ Basic Syntax: SELECT DISTINCT column1, column2 FROM table_name;
○ Example Scenario: Consider a Students table where multiple students might live in the same city or have the same name.
● Unique Cities (Single Column): SELECT DISTINCT city FROM Students;
○ Output: Delhi, Mumbai, Pune.
● Unique Name-City Combinations (Multiple Columns): SELECT DISTINCT name, city FROM Students;

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Datatypes, Operators & Null

Lesson

Datatypes, Operators & Null

Lesson 26 of 45
 SQL
Datatypes, Operators & Null

[Link]

[Link]
 SQL Data Types
● The Requirement: In a database table, every column needs a set of "rules" so the system knows exactly
what kind of information to expect.
● What is a Data Type?: It is an attribute that defines the specific kind of data a column can store, such as
whole numbers, text strings, or dates.
● Why They Are Essential:
○ Data Integrity: They act as a filter to prevent "bad data" from entering your table (e.g., you cannot
accidentally save a name in a "Price" column).
○ Storage Efficiency: The database allocates the exact amount of memory needed, which keeps the
system fast and optimized.
○ Operations & Logic: They tell SQL how to interact with the data—allowing you to perform math on
numbers or sort text alphabetically.

[Link]

[Link]
 SQL Data Types
● Numeric Types:
a. INT: Stores whole numbers (e.g., age).
b. DECIMAL(p,s): For exact values like currency (e.g., 10.2).
c. FLOAT: For approximate decimal values used in scientific data.
● String/Character Types:
a. CHAR(n): Fixed-length text (e.g., state codes).
b. VARCHAR(n): Variable-length text (e.g., names or emails).
c. TEXT: For long-form descriptions or notes.
● Date & Time Types:
a. DATE: Stores the date only (YYYY-MM-DD).
b. DATETIME: Stores both the date and the specific time.
● Boolean Type: Stores TRUE or FALSE values.

[Link]

[Link]
 ID Name GPA Weight Gender Bio Birth_Date Entry_Time Is_Active

101 'Amit' 9.25 65.42 'M' 'Enjoys. '2005-12-01' '08:30:00' TRUE

..'

INT VARCHAR DECIMAL FLOAT CHAR(1) TEXT DATE TIME BOOLEAN

Whole Variable Exact Decimal Approx. Fixed Large YYYY-MM-DD HH:MM:SS True/False
Numbers Text Decimal Length Text

[Link]

[Link]
 SQL Operators
● Introduction: SQL uses a rich set of operators to manipulate data, perform calculations, and filter
results effectively. These operators act as the building blocks for creating complex queries and logical
conditions.
● Arithmetic Operators: Used for mathematical calculations: + (Addition), - (Subtraction), *
(Multiplication), / (Division), and % (Modulo/Remainder).
● Comparison Operators: Used to filter data by comparing values: =, != or <> (Not Equal), <, >, <=,
>=, BETWEEN, LIKE, IN, and IS NULL.
● Logical Operators: Used to combine multiple conditions: AND (Conjunction), OR (Disjunction), and
NOT (Negation).
● Set & Concatenation Operators: || (String Concatenation), UNION, INTERSECT, and EXCEPT.

[Link]

[Link]
 SQL Precedence
● SQL evaluates expressions in a specific order to ensure consistent results. Operators on the same line
have equal priority and are evaluated from left to right.

[Link]

[Link]
 Understanding NULL in SQL
● Definition: A NULL value represents data that is missing, unknown, or not applicable; it acts as a
placeholder for a "value that does not exist."
● Important Distinction: NULL is not the same as a zero (0), a field containing spaces, or an empty string ("
").
● The Comparison Rule: Because NULL is an unknown state, you cannot use standard comparison
operators like = or != to find it (e.g., WHERE age = NULL will fail).
● How to Test: To identify missing data, you must use specific NULL operators:
○ IS NULL: Returns records where the field was left blank.
○ IS NOT NULL: Returns records that contain an actual value.
● Example Syntax: SELECT Student_Name FROM Students WHERE Grade_Point IS NULL;

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
DDL, DML, DCL, TCL & Desc

Lesson

DDL, DML, DCL, TCL & Desc

Lesson 27 of 45
 SQL
DDL, DML, DCL, TCL & Desc

[Link]

[Link]
 SQL Command Categories
● The SQL Language Landscape SQL commands are categorized into functional groups based on their impact on the
database. Understanding these groups is essential for mastering database management.
○ DDL (Data Definition Language): Defines and modifies the database structure.
○ DML (Data Manipulation Language): Manages and modifies the data within tables.
○ DCL (Data Control Language): Controls security and user permissions.
○ TCL (Transaction Control Language): Manages logical groups of DML operations.
○ DQL (Data Query Language): Used for retrieving specific data (e.g., SELECT).

[Link]

[Link]
 DDL (Data Definition Language)
● Purpose: Used to define, modify, and manage the structure (schema) of the database. DDL commands
are auto-committed, meaning changes are permanent and cannot be rolled back.

Command Purpose Syntax Example

CREATE Creates a new table or database. CREATE TABLE Student(id INT, name VARCHAR(50));

ALTER Modifies an existing table structure. ALTER TABLE Student ADD marks INT;

DROP Deletes an entire table (structure + data). DROP TABLE Student;

TRUNCATE Deletes all data but keeps the structure. TRUNCATE TABLE Student;

[Link]

[Link]
 The ALTER Command (Structural Changes)
The ALTER command is used when you need to change your table's design after it has already been created.

● ADD Column: Adds a new category to your table.

○ Example: ALTER TABLE Student ADD email VARCHAR(100);
● DROP Column: Removes a category permanently.
○ Example: ALTER TABLE Student DROP COLUMN email;
● MODIFY Column: Changes the data type or size of a column.
○ Example: ALTER TABLE Student MODIFY name VARCHAR(100);
● RENAME Column: Changes the name of an existing column.
○ Example: ALTER TABLE Student RENAME COLUMN marks TO score;

[Link]

[Link]
 The ALTER Command (Constraints & Rules)
You can also use ALTER to add or remove rules (Constraints) that keep your data clean.

● ADD Primary Key: Ensures every student has a unique ID.

○ Example: ALTER TABLE Student ADD CONSTRAINT pk_id PRIMARY KEY(id);
● ADD Foreign Key: Links the Student table to a Department table.
○ Example: ALTER TABLE Student ADD CONSTRAINT fk_dept FOREIGN KEY(dept_id)
REFERENCES Dept(id);
● DROP Constraint: Removes a rule like a Primary Key.
○ Example: ALTER TABLE Student DROP CONSTRAINT pk_id;
● CASCADE vs. RESTRICT:
○ Example: ALTER TABLE Student DROP COLUMN id CASCADE; (Drops the column and everything linked
to it).

[Link]

[Link]
 DML (Data Manipulation Language)
Purpose: Used to work with the data values inside your table.

● INSERT: Adds a new student record.

○ Example: INSERT INTO Student VALUES(101, 'Rahul', 90);
● UPDATE: Changes information for an existing student.
○ Example: UPDATE Student SET marks = 95 WHERE id = 101;
● DELETE: Removes a specific student's record.
○ Example: DELETE FROM Student WHERE id = 101;

Feature DELETE TRUNCATE DROP

Type DML DDL DDL

Action Deletes rows Wipes all data Deletes Table
Undo? Yes (Rollback) No No

[Link]

[Link]
 DCL (Data Control Language)
DCL is used to control access and security within the database. It allows administrators to define who can see or modify
specific data.

● GRANT Command: Used to give specific users access privileges to the database.
○ General Use: To provide "Read-Only" or "Full Access" permissions to a specific table.
○ The "Privileges" Explained:
■ SELECT: Permission to view or read the data.
■ UPDATE: Permission to change existing data.
■ INSERT: Permission to add new rows.
■ DELETE: Permission to remove rows.
○ Syntax: GRANT privilege_name ON object_name TO user_name;.
○ Example: GRANT SELECT, UPDATE ON Student TO 'Teacher_User';.
● REVOKE Command: Used to take away permissions that were previously granted to a user.
○ General Use: Used when a user no longer needs access or their role changes.
○ Syntax: REVOKE privilege_name ON object_name FROM user_name;.
○ Example: REVOKE UPDATE ON Student FROM 'Teacher_User';.

[Link]

[Link]
 TCL (Transaction Control Language)
TCL commands manage "Transactions". A transaction is a group of DML tasks that must either all succeed together or all fail
together.

● COMMIT: Permanently saves all changes made during the current transaction to the database.
○ General Use: To finalize a set of changes (like a successful bank transfer).
○ Syntax: COMMIT;.
○ Example: UPDATE Student SET marks = 90 WHERE id = 101; COMMIT;.
● ROLLBACK: Undoes all changes made since the last COMMIT or SAVEPOINT.
○ General Use: Restores the database to its previous state if an error occurs.
○ Syntax: ROLLBACK;.
○ Example: DELETE FROM Student; ROLLBACK; (This undoes the accidental deletion).
● SAVEPOINT: Sets a temporary "checkpoint" within a transaction.
○ General Use: Allows you to undo only a specific part of a transaction instead of everything.
○ Syntax: SAVEPOINT savepoint_name;.
○ Example: SAVEPOINT update_done;.

[Link]

[Link]
 DESCRIBE Command
This command acts as an "X-ray" for your table. Use it to check the column names, data types, and constraints before you write
complex queries.

● Syntax:
○ DESCRIBE table_name;
○ DESC table_name; (Shorthand version)
● Example: DESCRIBE Student;
● What the Output Reveals:
○ Field: The name of each column.
○ Type: The assigned data type (e.g., int, varchar).
○ Null: Whether the column allows empty (NULL) values.
○ Key: Identifies Indexing/Key information (e.g., PRI for Primary Key).
○ Default: Shows the value used if no data is provided.

Command Purpose What it Shows

DESC table_name Display table structure The "blueprint" (columns, types, keys)

SHOW TABLES List all tables A simple list of table names in the DB

SELECT * FROM table Display table data The actual rows and records stored inside

[Link]

[Link]
 Show Tables Command
In an RDBMS like Oracle, the SHOW TABLES operation is used to display the list of tables present in a database schema. Oracle
does not use a direct SHOW TABLES command like MySQL; instead, it provides system views to list tables.

Command to Show Tables in Oracle: SELECT table_name FROM user_tables;

This command displays all tables created by the current user.

Show All Tables Accessible to User: SELECT table_name FROM all_tables;

Displays all tables that the user has access to.

Show Tables of All Users (DBA): SELECT table_name FROM dba_tables;

Displays tables of all users (requires DBA privileges).

Example Output

STUDENT

EMPLOYEE

MARKS

RESULT
[Link]

Oracle stores table information in data dictionary views. Queries on these views return table names instead of using a direct SHOW
[Link]
TABLES command.
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Order By & Rename Operation (Alias)

Lesson

Order By & Rename Operation (Alias)

Lesson 28 of 45
 SQL
Order By & Rename Operation
(Alias)

[Link]

[Link]
 Alias Operation/rename
1. SQL aliases are used to give a table, or a column in a table, a temporary name. Just create a
new copy but do not change anything in the data base.

1. Aliases are often used to make column names more readable.

1. It uses the as clause, taking the form: old-name as new-name. The as clause can appear in
both the select and from clauses.

1. An alias only exists for the duration of the query.

[Link]

[Link]
 Ordering the Display of Tuples
• SQL offers the user some control over the order in which tuples in a relation are displayed.
The order by clause causes the tuples in the result of a query to appear in sorted order.

[Link]

[Link]
 Q Write a SQL query to find all the branch_name which are situated in Delhi in alphabetic order?

[Link]

[Link]
 Q Write a SQL query to find the account_no along and balance with 8% interest, as
Account, total_balance?

[Link]

[Link]
 Q Write a SQL query to find all the account number along with branch_name and
branch_city?

[Link]

[Link]
 Q Write a SQL query to find the loan_no with maximum loan amount?

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Library Functions (Math, Aggregate, String, Date)

Lesson

Library Functions (Math, Aggregate, String, Date)

Lesson 29 of 45
 SQL
Library Functions (Math, Aggregate,
String, Date)

[Link]

[Link]
 Aggregate Functions
• Aggregate functions are functions that take a collection (a set or multiset) of values as input
and return a single value. SQL offers five built-in aggregate functions:
• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count

[Link]

[Link]
 1. The input to sum and avg must be a collection of numbers, but the other operators can
operate on collections of nonnumeric data types, such as strings, as well.

1. We use the aggregate function count frequently to count the number of tuples in a relation.
The notation for this function in SQL is count (*).

1. Count is the only aggregate function which can work with null, all other aggregate functions
simply ignore null.

[Link]

[Link]
 Q Write a SQL query to find the number of accounts in the bank?

[Link]

[Link]
 Q Write a SQL query to find the average balance of every account in the banks
from south_delhi branch?

[Link]

[Link]
 Q Consider a table along with two query?

Select avg (balance)

from account
Account_no balance Branch_name
Abc123 100 N_delhi
Pqr123 500 S_mumbai
Wyz123 null S_delhi

Select sum(balance)/count(balance)
from account

[Link]

[Link]
 String Operations
1. SQL specifies strings by enclosing them in single quotes, for example, ’Computer’.

1. The SQL standard specifies that the equality operation on strings is case sensitive; as a result the expression
‘Computer’ = ’computer’ evaluates to false.

1. However, some database systems, such as MySQL and SQL Server, do not distinguish uppercase from lowercase
when matching strings; as a result, would evaluate to true on these databases.

1. This default behavior can, however, be changed, either at the database level or at the level of specific
attributes.

[Link]

[Link]
 1. SQL also permits a variety of functions on character strings, such as concatenating, extracting
substrings, finding the length of strings, converting strings to uppercase and lowercase,
removing spaces at the end of the string and so on.

1. There are variations on the exact set of string functions supported by different database
systems.

1. A single quote character that is part of a string can be specified by using two single quote
characters; for example, the string It’s right can be specified by It”s right.

[Link]

[Link]
 • Pattern matching can be performed on strings, using the operator like. We describe patterns
by using two special characters:
• Percent (%): The % character matches any substring.
• Underscore (_): The _ character matches any character.

• ’%Comp%’ matches any string containing “Comp” as a substring, for example, ’Intro to
Computer Science’, and ’Computational Biology’.

• ’_ _ _’ matches any string of exactly three characters.

• ’_ _ _%’ matches any string of at least three characters.

[Link]

[Link]
 Q Write a SQL query to find all the branch name who have exactly 5 character in their name ?

[Link]

[Link]
 Q Write a SQL query to find all the customer name who have ‘kumar’ in their name ?

[Link]

[Link]
 1. For patterns to include the special pattern characters (that is, % and_), SQL allows the
specification of an escape character.

1. The escape character is used immediately before a special pattern character to indicate that
the special pattern character is to be treated like a normal character.

1. We define the escape character for a like comparison using the escape keyword. To
illustrate, consider the following patterns, which use a backslash (\) as the escape character:

1. like ’ab\%cd%’ escape ’\’ matches all strings beginning with “ab%cd”.

1. like ’ab\\cd%’ escape ’\’ matches all strings beginning with “ab\cd”.

[Link]

[Link]
 1. SQL allows us to search for mismatches instead of matches by using the not like comparison
operator. Some databases provide variants of the like operation which do not distinguish
lower and upper case.

1. SQL:1999 also offers a similar to operation, which provides more powerful pattern matching
than the like operation; the syntax for specifying patterns is similar to that used in Unix
regular expressions.

[Link]

[Link]
 SQL String Functions (Case & Length)
Introduction: String functions are used to manipulate text data stored in tables. They are essential for cleaning data and generating readable
reports.

● UPPER()
○ General Use: Converts all characters in a column to uppercase.
○ Syntax: SELECT UPPER(column_name) FROM table_name;
○ Example: SELECT UPPER(name) FROM Student;
■ Result: 'amit' → 'AMIT'
● LOWER()
○ General Use: Converts all characters in a column to lowercase.
○ Syntax: SELECT LOWER(column_name) FROM table_name;
○ Example: SELECT LOWER(name) FROM Student;
■ Result: 'AMIT' → 'amit'
● LENGTH() vs. CHAR_LENGTH()
○ The Difference: CHAR_LENGTH() counts the number of characters, while LENGTH() counts the number of bytes.
○ Why it Matters: Some special characters (like symbols or emojis) take up more than 1 byte.
○ Example Query: SELECT CHAR_LENGTH('©'), LENGTH('©') FROM Student;
■ Result: CHAR_LENGTH → 1, LENGTH → 2 (The symbol is 1 character but occupies 2 bytes).
● CONCAT()
○ General Use: Combines multiple columns or strings into one.
○ Syntax: SELECT CONCAT(col1, ' ', col2) FROM table_name;
[Link]
○ Example: SELECT CONCAT(first_name, ' ', last_name) FROM Student;
■ Result: 'Rahul' + 'Sharma' → 'Rahul Sharma'
[Link]
 SQL String Functions (Extraction & Search)
● SUBSTRING() / SUBSTR()
○ General Use: Extracts a specific portion of a string.
○ Syntax: SELECT SUBSTRING(column, start, length) FROM table_name;
○ Example: SELECT SUBSTRING(name, 1, 3) FROM Student;
■ Result: 'Mumbai' → 'Mum' (Starts at 1, takes 3 characters).
● REPLACE()
○ General Use: Replaces a specific character or pattern within a string with new text.
○ Syntax: SELECT REPLACE(column, 'old', 'new') FROM table_name;
○ Example: SELECT REPLACE(city, 'y', 'i') FROM Student;
■ Result: 'Bombay' → 'Bombai'
● INSTR() / POSITION()
○ General Use: Returns the numerical position of the first occurrence of a character.
○ Syntax: SELECT INSTR(column, 'character') FROM table_name;
○ Example: SELECT INSTR(name, 'a') FROM Student;
■ Result: 'Amit' → 1 (Since 'A' is the first letter).

[Link]

[Link]
 SQL String Functions (Trimming & Formatting)
● TRIM() / LTRIM() / RTRIM()
○ General Use: Removes unwanted empty spaces from text.
○ Syntax: SELECT TRIM(column) FROM table_name;
○ Example: SELECT TRIM(' Amit ') FROM Student;
■ Result: ' Amit ' → 'Amit'
● REVERSE()
○ General Use: Reverses the string.
○ Syntax: SELECT REVERSE(column) FROM table_name;
○ Example: SELECT REVERSE(name) FROM Student;
■ Result: 'SQL' → 'LQS'
● LEFT() & RIGHT()
○ General Use: Grabs characters from the start (Left) or end (Right) of a string.
○ Example: SELECT LEFT(city, 3), RIGHT(city, 3) FROM Student;
■ Result (for 'Mumbai'): 'Mum' and 'bai'

[Link]

[Link]
 Comprehensive SQL String Functions Reference
Category Function Description

Case UPPER Converts all characters in a string to uppercase (e.g., 'amit' → 'AMIT').

LOWER Converts all characters in a string to lowercase (e.g., 'AMIT' → 'amit').

Cleaning LTRIM Removes any leading (left-side) empty spaces from a string.

RTRIM Removes any trailing (right-side) empty spaces from a string.

TRIM Removes empty spaces from both the beginning and the end of a string.

Slicing LEFT Returns a specific number of characters starting from the left side of a string.

RIGHT Returns a specific number of characters starting from the right side of a string.

SUBSTRING Returns a specific portion (middle) of a string based on a start position and length.

Position LEN Returns the total number of characters in a string.

CHARINDEX Returns the numerical starting position of a specific character or substring within a
value.

PATINDEX Returns the starting position of the first occurrence of a pattern in a string.

[Link]

[Link]
 Comprehensive SQL String Functions Reference

Category Function Description

Editing REPLACE Searches for a specific set of characters and replaces them with another.
REVERSE Flips the character order of a string from end to end.

STUFF Deletes a part of a string and inserts a new set of characters at a specific
position.
REPLICAT Repeats a string value a specified number of times.
E
Numeric/Text STR Converts numeric data into a text (string) value.
SPACE Returns a string consisting of a specific number of repeated spaces.

Encoding ASCII Returns the numerical ASCII code value of the leftmost character in a string.
CHAR Converts an integer ASCII code into its corresponding character.

[Link]

[Link]
 Essential Math Functions
Introduction: SQL Math functions allow you to perform arithmetic operations and numerical transformations directly within your queries.

● ABS()
○ General Use: Returns the absolute (positive) value of a number.
○ Syntax: SELECT ABS(column_name) FROM table_name;
○ Example: SELECT ABS(-25); → Output: 25
● ROUND()
○ General Use: Rounds a number to a specified number of decimal places.
○ Syntax: SELECT ROUND(number, decimals);
○ Example: SELECT ROUND(12.567, 2); → Output: 12.57
● CEIL() / CEILING()
○ General Use: Returns the smallest integer that is greater than or equal to a number.
○ Syntax: SELECT CEIL(number);
○ Example: SELECT CEIL(4.3); → Output: 5
● FLOOR()
○ General Use: Returns the largest integer that is less than or equal to a number.
○ Syntax: SELECT FLOOR(number);
○ Example: SELECT FLOOR(4.9); → Output: 4
● MOD()
○ General Use: Returns the remainder of a division.
○ Syntax: SELECT MOD(dividend, divisor);
[Link]
○ Example: SELECT MOD(17, 5); → Output: 2
[Link]
 Advanced Scientific & Trig Functions
● For more complex data analysis, SQL provides a full suite of scientific and trigonometric functions.
Category Function Description

Trigonometry SIN, COS, TAN Returns the Sine, Cosine, or Tangent of an angle (in radians).

ASIN, ACOS, ATAN Returns the inverse (arc) Sine, Cosine, or Tangent.

DEGREES / RADIANS Converts between radians and degrees.

Power & Roots SQRT Returns the square root of a number.

SQUARE Returns the square of a number.

POWER Returns the value of a number raised to a specific power.

EXP Returns e raised to the power of a specified number.

Logarithms LOG / LOG10 Returns the natural logarithm (ln) or base-10 logarithm.

Random/Sign RAND Generates a random decimal number between 0 and 1.

SIGN Returns -1 for negative, 0 for zero, and 1 for positive numbers.

PI Returns the constant value of Pi (approximately 3.14159).

[Link]

[Link]
 Date and Time Functions
● Introduction: Date functions are used to capture the current system time, extract specific parts of a date, or perform calendar-based
calculations.
● CURRENT_DATE / CURDATE()
○ General Use: Returns the current system date.
○ Syntax: SELECT CURRENT_DATE; or SELECT CURDATE();.
○ Example: SELECT CURDATE(); → Output: '2026-01-30'.
● CURRENT_TIMESTAMP / NOW()
○ General Use: Returns the current system date and time.
○ Syntax: SELECT NOW();.
○ Example: SELECT NOW(); → Output: '2026-01-30 08:30:00'.
● EXTRACT()
○ General Use: Extracts a specific part (Year, Month, Day, etc.) from a date.
○ Syntax: SELECT EXTRACT(unit FROM column_name) FROM table_name;.
○ Example: SELECT EXTRACT(MONTH FROM '2026-01-30'); → Output: 1.
● YEAR(), MONTH(), DAY()
○ General Use: Shorthand functions to return specific parts of a date as integers.
○ Syntax: SELECT YEAR(column), MONTH(column), DAY(column) FROM table;.
○ Example: SELECT YEAR('2026-01-30'), MONTH('2026-01-30'), DAY('2026-01-30');
○ Output: 2026, 1, 30.

[Link]

[Link]
 Advanced Date Operations
● These functions allow for more complex date manipulation, such as finding the difference between two dates or
adding time intervals.
Category Function Description Example & Output

Difference DATEDIFF Returns the count of intervals between two dates. DATEDIFF('2026-02-01', '2026-01-01') → 31.

Modification DATEADD Adds or subtracts a specific time interval from a date. DATEADD(day, 5, '2026-01-01') → '2026-01-06'.

End of Month EOMONTH Returns the last day of the month for a given date. EOMONTH('2026-02-01') → '2026-02-28'.

Validation ISDATE Returns 1 if the expression is a valid date, otherwise 0. ISDATE('2026-01-30') → 1.

Date Parts DATENAME Returns a string representing the datepart (e.g., month name). DATENAME(month, '2026-01-30') → 'January'.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Set Operations & Cartesian Product

Lesson

Set Operations & Cartesian Product

Lesson 30 of 45
 SQL
Set Operations & Cartesian Product

[Link]

[Link]
 Set Operation

1. The SQL operations union, intersect, and except/minus operate on relations and corresponds to
the mathematical set-theory operations ∪, ∩ and – respectively.

1. The union operation automatically eliminates duplicates, unlike the select clause, If we want to
retain all duplicates, we must write union all in place of union.

1. The intersect operation automatically eliminates duplicates. If we want to retain all duplicates,
we must write intersect all in place of intersect.

1. If we want to retain duplicates, we must write except all in place of except.

[Link]

[Link]
 Q Write a SQL query to find all the customer name
a) who have a loan or an account or both ?
b) who have both a loan and an account?
c) who have a loan but do not have an account?

[Link]

[Link]
 Queries on Multiple Relations
• So far, our example queries were on a single relation. Queries often need to access
information from multiple relations.

• The from clause by itself defines a Cartesian product of the relations listed in the clause.

• Cartesian product of two relations, which concatenates each tuple of the first relation with
every tuple of the second.

• Since the same attribute name may appear in both r1 and r 2, we prefix the name of the
relation from which the attribute originally came, before the attribute name.

• For those attributes that appear in only one of the two schemas, we shall usually drop the
relation-name prefix. This simplification does not lead to any ambiguity.

• Cartesian Product is commutative in nature

[Link]

[Link]
 R1 * R2
R1 R2
A B B C
1 P Q X
2 Q R Y
3 R S Z

[Link]

[Link]
 Q Write a SQL query to find the name of all the customers with account balance,
who have an account in the bank?

[Link]

[Link]
 Q Write a SQL query to find the name of all the customers, who have a loan in the bank of less
than 1000 or loan from north_delhi branch?

[Link]

[Link]
 Q Write a SQL query to find the name of the customer who have an account in the branch
situated in Delhi?

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Join Operations (Inner & Outer)

Lesson

Join Operations (Inner & Outer)

Lesson 31 of 45
 SQL
Join Operations (Inner & Outer)

[Link]

[Link]
 Natural Join
1. To make the life of an SQL programmer easier for this common case, SQL supports an
operation called the natural join.
1. The natural join operation like cartesian product operates on two relations and produces a
relation as the result.
1. Natural join considers only those pairs of tuples with the same value on those attributes that
appear in the schemas of both relations.
1. Notice that we do not repeat those attributes that appear in the schemas of both relations;
rather they appear only once.
1. Notice also the order in which the attributes are listed: first the attributes common to the
schemas of both relations, second those attributes unique to the schema of the first relation,
and finally, those attributes unique to the schema of the second relation.
1. commutative in nature
[Link]

[Link]
 A from clause in an SQL query can have multiple relations combined using natural join, as shown here:

select A1, A2,..., An

from r1 natural join r2 natural join .. . natural join rm
where P;

[Link]

[Link]
 R1 R2 R1 * R2
A B B C A R1.B R2.B C
1 P Q X 1 P Q X
2 Q R Y 1 P R Y
3 R S Z 1 P S Z
2 Q Q X
2 Q R Y
2 Q S Z
3 R Q X
R1 ⋈ R2 3
3
R
R
R
S
Y
Z
A B C

[Link]

[Link]
 Q Write a SQL query to find the name of all the customers along with account
balance, who have an account in the bank?

[Link]

[Link]
 Q Write a SQL query to find the name of the customer who have an account in the branch
situated in Delhi?

[Link]

[Link]
 Join /inner join
1. Join or inner join is a operation which works same as cartesian product but when it is used
with “using” keyword it provides additional functionality.

1. Join with using – Provides ability to explicitly chose columns which must be used by join for
comparison and removal of redundant tuples, i.e. if there are more than one column which
are common between two table but we do not want each of them to be considered, then we
can use join with using.

[Link]

[Link]
 1. So join is a form of the natural join construct that allows you to specify exactly which
columns should be equated.
• R1(A, B, C)
• R2(B, C, D)

2. Consider the operation R1 join R2 using ( B). The operation is similar to R1 natural join R2,
except that a pair of tuples t1 from R1 and t2 from R2 match if t1. B = t2. B; even if R1 and R2
both have an attribute named C, it is not required that t1. C = t2. C.

[Link]

[Link]
 Outer Join
1. The join operations we studied earlier that do not preserve nonmatched tuples are called inner join operations,
to distinguish them from the outer-join operations.

1. The problem with natural join or join is only those values that appears in both relations will manage to reach
final table, but if some value is explicitly in table one or in second table then that information will be lost, and
that will be loss of information.

1. The outer join operation works in a manner similar to the join operations we have already studied, but preserve
those tuples that would be lost in a join, by creating tuples in the result containing null values.

[Link]

[Link]
 • There are in fact three forms of outer join:
1. The left outer join (left join) preserves tuples only in the relation named before (to the
left of) the left outer join operation.
2. The right outer join (right join) preserves tuples only in the relation named after (to the
right of) the right outer join operation.
3. The full outer join preserves tuples in both relations.

[Link]

[Link]
 R1 R2 R1 * R2 R1 ⋈ R2
A B B C A R1.B R2.B C A B C
1 P Q X 1 P Q X 2 Q X
2 Q R Y 1 P R Y 3 R Y
3 R S Z 1 P S Z
2 Q Q X
2 Q R Y R1 ⟗ R2
2 Q S Z A B C
3 R Q X
3 R R Y
3 R S Z

R1 ⟕ R2 R1 ⟖R2
A B C A B C

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Group By Clause

Lesson

Group By Clause

Lesson 32 of 45
 SQL
Group By Clause

[Link]

[Link]
 Group by clause
1. There are circumstances where we would like to work on a set of tuples in a relation rather
than working on the whole table as one unit.

1. The attribute or attributes given in the group by clause are used to form groups. Tuples with
the same value on all attributes in the group by clause are placed in one group.

[Link]

[Link]
 Q Write a SQL query to find the average account balance of each branch?

[Link]

[Link]
 Q Write a SQL query to find the branch name of Gwalior city with average balance more than
1500?

[Link]

[Link]
 1. When an SQL query uses grouping, it is important to ensure that the only attributes that
appear in the select statement without being aggregated are those that are present in the
group by clause.

1. In other words, any attribute that is not present in the group by clause must appear only
inside an aggregate function if it appears in the select clause, otherwise the query is treated
as erroneous.

1. The Having Clause - At times, it is useful to state a condition that applies to groups rather
than to tuples.

1. To express such a query, we use the having clause of SQL. SQL applies predicates in the
having clause after groups have been formed.

1. Any attribute that is present in the having clause without being aggregated must appear in
the group by clause, otherwise the query is treated as erroneous.
[Link]

[Link]
 • The meaning of a query containing aggregation, group by, or having clauses is defined by the
following sequence of operations:
1. As was the case for queries without aggregation, the from clause is first evaluated to get
a relation.
2. If a where clause is present, the predicate in the where clause is applied on the result
relation of the from clause.
3. Tuples satisfying the where predicate are then placed into groups by the group by clause
if it is present. If the group by clause is absent, the entire set of tuples satisfying the
where predicate is treated as being in one group.
4. The having clause, if it is present, is applied to each group; the groups that do not satisfy
the having clause predicate are removed.
5. The select clause uses the remaining groups to generate tuples of the result of the query,
applying the aggregate functions to get a single result tuple for each group.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Nested & Correlated Subqueries

Lesson

Nested & Correlated Subqueries

Lesson 33 of 45
 SQL
Nested & Correlated Subqueries

[Link]

[Link]
 Subquery
• The subquery (inner query) executes once before the main query (outer query) executes.

• The main query (outer query) use the subquery result.

Example: EMP(eno, ename, sal, dno)

1. Find the highest salary in the company

2. Find the second highest salary in the company

3. Find the name of second highest salary employee

[Link]

[Link]
 Correlated Subquery

SELECT column1, column2, ...

FROM table1 t1
WHERE column1 operator
(SELECT column
FROM table2
WHERE expr1 = t1.expr2);
Example:
Consider two tables:
Employees: Contains employee details.
Departments: Contains department details.
Task: Find employees whose salary is higher than the average salary of their respective departments.

• Outer Query: Retrieves employee details.

• Inner Query: Calculates the average salary for the department of the current
employee.

• The WHERE clause ensures only employees earning above their department's average
[Link]
salary are selected.
[Link]
[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
SQL

Subtopic
Views & PLSQL Basics

Lesson

Views & PLSQL Basics

Lesson 34 of 45
 SQL
Views & PLSQL Basics

[Link]

[Link]
 Views in SQL

● A View is a virtual table created using a SQL query.

● It does not store data itself; it displays data from one or more tables.

Why Use Views?

● Simplifies complex queries

● Restricts access to sensitive data
● Provides customized data representation

Syntax:
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example: Create a view to show only student names and marks:

CREATE VIEW student_result AS
SELECT name, marks
FROM Student;

Using the View:

SELECT * FROM student_result;

Output behaves like a table, but data comes from Student.

[Link]

[Link]
 PL/SQL Basics

● PL/SQL (Procedural Language/SQL) is Oracle’s extension of SQL

● Combines SQL + programming constructs (loops, conditions)
● Used to write blocks, procedures, functions, triggers

Why PL/SQL?

● Faster execution
● Better error handling
● Code reusability

Used mainly in Oracle DB

[Link]

[Link]
 PL/SQL Data Types

Scalar

● NUMBER – numeric values

● VARCHAR2 – strings
● DATE – date & time
● BOOLEAN – TRUE / FALSE

Special

● %TYPE – variable takes column’s datatype

● %ROWTYPE – entire row structure

Example
v_name VARCHAR2(20);
v_salary NUMBER;

[Link]

[Link]
 PL/SQL Block Structure
Basic execution unit of PL/SQL.

Structure
DECLARE
-- variable declaration
BEGIN
-- executable statements
EXCEPTION
-- error handling
END;

Example
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;

DECLARE & EXCEPTION are optional

[Link]

[Link]
 Procedure
● Named PL/SQL block
● Performs an action
● Does not return a value

Syntax
CREATE PROCEDURE show_msg IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome');
END;

Call: EXEC show_msg;

Used for reusable tasks

[Link]

[Link]
 Function
● Similar to procedure
● Must return a value

Syntax
CREATE FUNCTION get_bonus
RETURN NUMBER IS
BEGIN
RETURN 1000;
END;

Usage
SELECT get_bonus FROM dual;

Used inside SQL queries

[Link]

[Link]
 Trigger
● Automatically executed
● Fires on INSERT / UPDATE / DELETE

Types

● BEFORE Trigger: Executes automatically before an INSERT, UPDATE, or DELETE operation on a table.
● AFTER Trigger: Executes automatically after an INSERT, UPDATE, or DELETE operation on a table.

Example
CREATE TRIGGER trg_insert
BEFORE INSERT ON Student Component Returns Value Called Explicitly
BEGIN Block Yes
DBMS_OUTPUT.PUT_LINE('Record Inserted'); Procedure Yes
END; Function Yes
Trigger No (automatic)
Used for validation & auditing

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Introduction & ACID Properties

Lesson

Introduction & ACID Properties

Lesson 35 of 45
 Transaction
Management
Introduction & ACID Properties

[Link]

[Link]
 TRANSACTION
• Why we study transaction?

• According to general computation principle (operating system) we may have partially

executed program, as the level of atomicity is instruction i.e. either an instruction is
executed completely or not

• But in DBMS view, user perform a logical work(operation) which is always atomic in nature
i.e. either operation is execute or not executed, there is no concept like partial execution.
For example, Transaction T1 which transfer 100 units from account A to B
T1
Read(A)
A = A-100
Write(A)
Read(B)
B = B+100
Write(B)
[Link]

[Link]
 • In this transaction if a failure occurs after Read(B) then the final statue of the system will be
inconsistent as 100 units are debited from account A but not credited in account B, this will
generate inconsistency.

• Here for ‘consistency’ before (A + B) == after (A + B)”

T1
Read(A)
A = A-100
Write(A)
Read(B)
B = B+100
Write(B)

[Link]

[Link]
 What is transaction
• To remove this partial execution problem, we increase the level of atomicity and bundle all
the instruction of a logical operation into a unit called transaction.

• So formally ‘A transaction is a Set of logically related instructions to perform a logical unit of

work’.

T1
Read(A)
A = A-100
Write(A)
Read(B)
B = B+100
Write(B)

[Link]

[Link]
 • As here we are only concerned with DBMS so we well only two basic operation on database

• READ (X) - Accessing the database item x from disk (where database stored data) to memory
variable also name as X.

• WRITE (X) - Writing the data item from memory variable X to disk.

[Link]

[Link]
 Desirable properties of transaction
• Now as the smallest unit which have atomicity in DBMS view is transaction, so if
want that our data should be consistent then instead of concentrating on data
base, we must concentrate on the transaction for our data to be consistent.

T1
Read(A)
A = A-100
Write(A)
Read(B)
B = B+100
Write(B)

[Link]

[Link]
 • Transactions should possess several properties, often called the ACID
properties; to provide integrity and consistency of the data in the database. The
following are the ACID properties:

[Link]

[Link]
 • Atomicity - A transaction is an atomic unit of processing; it should either be performed in its
entirety or not performed at all.

• It is the responsibility of recovery control manager / transaction control manager of DBMS to

ensure atomicity

[Link]

[Link]
 • Isolation - A transaction should appear as though it is being executed in isolation from other
transactions, even though many transactions are executing concurrently.

• That is, the execution of a transaction should not be interfered with by any other transactions
executing concurrently.

• The isolation property of database is the responsibility of concurrency control manager of

database.

[Link]

[Link]
 • Durability - The changes applied to the database by a committed transaction must persist in
the database.

• These changes must not be lost because of any failure. It is the responsibility of recovery
control manager of DBMS.

[Link]

[Link]
 • Consistency - A transaction should be consistency preserving, meaning that if it is completely
executed from beginning to end without interference from other transactions, it should take
the database from one consistent state to another.

• The definition of consistency may change from one system to another. The preservation of
consistency of database is the responsibility of programmers(users) or the DBMS modules that
enforces integrity constraints.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Transaction States

Lesson

Transaction States

Lesson 36 of 45
 Transaction
Management
Transaction States

[Link]

[Link]
 Transaction states

[Link]

[Link]
 Transaction states
• ACTIVE - It is the initial state. Transaction remains in this state while it is executing operations.
• PARTIALLY COMMITTED - After the final statement of a transaction has been executed, the
state of transaction is partially committed as it is still possible that it may have to be aborted
(due to any failure) since the actual output may still be temporarily residing in main memory
and not to disk.
• FAILED - After the discovery that the transaction can no longer proceed (because of hardware
/logical errors). Such a transaction must be rolled back.
• ABORTED - A transaction is said to be in aborted state when the when the transaction has
been rolled back and the database has been restored to its state prior to the start of
execution.
• COMMITTED - A transaction enters committed state after successful completion of a
transaction and final updation in the database.
[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Concurrency Problems

Lesson

Concurrency Problems

Lesson 37 of 45
 Transaction
Management
Concurrency Problems

[Link]

[Link]
 Why we need concurrent execution
• Concurrent execution is necessary because-
• It leads to good database performance , less weighting time.
• Overlapping I/O activity with CPU increases throughput and response time.

[Link]

[Link]
 PROBLEMS DUE TO CONCURRENT EXECUTION OF TRANSACTION

• But interleaving of instructions between transactions may also lead to many problems that
can lead to inconsistent database.

• Sometimes it is possible that even though individual transaction are satisfying the acid
properties even though the final statues of the system will be inconsistent.

[Link]

[Link]
 Lost update problem / Write - Write problem-
• If there is any two write operation of different transaction on same data value,
and between them there is no read operations, then the second write over
writes the first write.

T1 T2
Read(A)
Write(A)
Write(A)
Commit
Commit

[Link]

[Link]
 Dirty read problem/ Read -Write problem
• In this problem, the transaction reads a data item updated by
another uncommitted transaction, this transaction may in future be
aborted or failed.
• The reading transactions end with incorrect results.

T1 T2
Read(A)
Write(A)
Read(A)
Commit
[Link]
Abort
[Link]
 Unrepeatable read problem
• When a transaction tries to read a value of a data item twice, and another transaction
updates the data item in between, then the result of the two read operation of the first
transaction will differ, this problem is called, Non-repeatable read problem

T1 T2
Read(A)
Read(A)
Write(A)
Read(A)

[Link]

[Link]
 Phantom read problem

T1 T2
Read(A)
Delete(A)
Read(A)

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Schedules & Conflict Serializability

Lesson

Schedules & Conflict Serializability

Lesson 38 of 45
 Transaction
Management
Schedules & Conflict Serializability

[Link]

[Link]
 Solution is Schedule
• When two or more transaction executed together or one after another then they can be
bundled up into a higher unit of execution called schedule.

• A schedule of n transactions T1, T2, ..., Tn is an ordering of the operations of the transactions.
Operations from different transactions can be interleaved in the schedule S.

• However, schedule for a set of transaction must contain all the instruction of those
transaction, and for each transaction Ti that participates in the schedule S, the operations of Ti
in S must appear in the same order in which they occur in Ti.

[Link]

[Link]
 • Serial schedule - A serial schedule consists of sequence of instruction belonging to
different transactions, where instructions belonging to one single transaction appear
together. Before complete execution of one transaction another transaction cannot be
started.

• For a set of n transactions, there exist n! different valid serial schedules. Every serial schedule
[Link]

lead database into consistent state. Throughput of system is less.

[Link]
 • Non-serial schedule - A schedule in which sequence of instructions of a
transaction appear in the same order as they appear in individual transaction
but the instructions may be interleaved with the instructions of different
transactions i.e. concurrent execution of transactions takes place.

[Link]

[Link]
 • So the number of schedules for n different transaction T1,T2,T3,---------TN where
each transaction conations n1, n2, n3, -----------,n4 respectively will be.

• {(n1 + n2+ n3 +-----------+nn)!}/ (n1! n2! n3! -----------nn!)}

• {(n1 + n2+ n3 +-----------+nn)!}/ (n1! n2! n3! -----------nn!)} - n!

[Link]

[Link]
 • Conclusion of schedules
• We do not have any method to proof that a schedule is consistent, but from the above
discussion we understand that a serial schedule will always be consistent, so if somehow
we proof that a non-serial schedule will also have same effects as of a serial schedule that
we get a proof that, this particular non-serial schedule will also be consistent “find those
schedules that are logically equal to serial schedules”.

• For a concurrent schedule to result in consistent state, it should be equivalent to a serial

schedule. i.e. it must be serializable.

[Link]

[Link]
 On the basis of On the basis of
SERIALIZABILITY RECOVERABILITY

Conflict
Recoverable
serializable

View
Cascadeless
serializable

Result
Strict
Equivalent

[Link]

[Link]
 CONFLICT SERIALIZABLE
• The schedules which are conflict equivalent to a serial schedule are called conflict serializable
schedule.

• If a schedule S can be transformed into a schedule S’ by a series of swaps of non- conflicting

instructions, we say that S and S’ are conflict equivalent.

• A schedule S is conflict serializable, if it is conflict equivalent to a serial schedule.

[Link]

[Link]
 T1 T2 T1 T2 T1 T2 T1 T2
R(A) R(B) R(A) W(A)
R(B) R(A) W(A) R(A)

T1 T2 T1 T2 T1 T2 T1 T2
R(A) W(B) R(A) W(A)
W(B) R(A) W(A) R(A)

T1 T2 T1 T2 T1 T2 T1 T2
R(A) R(A) W(A) W(A)
R(A) R(A) W(A) W(A)

[Link]

[Link]
 SERIALIZABILITY

• Conflicting instructions - Let I and J be two consecutive instructions belonging to two

different transactions Ti and Tj in a schedule S, the possible I and J instruction can be as-
• I= READ(Q), J=READ(Q) →Non-conflicting
• I= READ(Q), J=WRITE(Q) →Conflicting
• I= WRITE(Q), J=READ(Q) →Conflicting
• I= WRITE(Q), J=WRITE(Q) →Conflicting

• So, the instructions I and J are said to be conflicting, if they are operations by different
transactions on the same data item, and at least one of these instructions is a write operation.

[Link]

[Link]
 Conflict equivalent – if one schedule can be converted to another schedule by swapping of non-
conflicting instruction then they are called conflict equivalent schedule.

T1 T2 T1 T2
R(A) R(B)
A=A-50 B=B+50
R(B) R(A)
B=B+50 A=A-50
R(B) R(B)
B=B+50 B=B+50
R(A) R(A)
A=A+10 A=A+10
[Link]

[Link]
 Procedure for determining conflict serializability of a schedule
• It can be determined using PRECEDENCE GRAPH method:
• A precedence graph consists of a pair G (V, E)
• V= set of vertices consisting of all the transactions participating in the schedule.
• E= set of edges consists of all edges Ti → Tj, for which one of the following conditions holds:
• Ti executes write(Q) before Tj executes read(Q)
• Ti executes read(Q) before Tj executes write(Q)
• Ti executes write(Q) before Tj executes write(Q)

[Link]

[Link]
 • If an edge Ti → Tj exists in the precedence graph, then in any serial schedule S’ equivalent
to S, Ti must appear before Tj.

• If the precedence graph for S has no cycle, then schedule S is conflict serializable, else it
is not. This cycle detection can be done by cycle detection algorithms, one of them based
on depth first search takes O(n2) time.

• The serialializibility order of transactions of equivalent serial schedule can be determined

using topological order in a precedence graph.

[Link]

[Link]
 Q. Let Ri(z) and Wi(z) denote read and write operations on data item z by
transaction Ti, respectively. Consider the schedule S with four transactions.
S : R4(x) R2(x) R3(x) R1(y) W1(y) W2(x) W3y R4(y) W4(y)

Which one of the following serial schedules is conflict equivalent to S?

A. T1 -> T3 -> T4 -> T2
B. T1 -> T4 -> T3 -> T2
C. T4 -> T1 -> T3 -> T2
D. T3 -> T1 -> T4 -> T2

[Link]

[Link]
 Q. Let ri(z) and wi(z) denote read and write operations respectively on a data
item z by a transaction Ti. Consider the following two schedules.
● S1: r1(x) r1(y) r2(x) r2(y) w2(y) w1(x)
● S2: r1(x) r2(x) r2(y) w2(y) r1(y) w1(x)
Which one of the following options is correct?
A. S1 is conflict serializable, and S2 is not conflict serializable
B. S1 is not conflict serializable, and S2 is conflict serializable
C. Both S1 and S2 are conflict serializable
D. Neither S1 nor S2 is conflict serializable

[Link]

[Link]
 Q. Consider the following schedule for transactions T1, T2 and T3. Which one of
the schedules below is the correct serialization of the above?
A. T1 -> T3 -> T2
B. T2 -> T1 -> T3
C. T2 -> T3 -> T1
D. T3 -> T1 -> T2

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
View Serializability

Lesson

View Serializability

Lesson 39 of 45
 Transaction
Management
View Serializability

[Link]

[Link]
 VIEW SERIALIZABLE

• If a schedule is not conflict serializable, still it can be consistent, so let us study a weaker form
of serializability called View serializability, and even if a schedule is view serializable still it can
be consistent.

• If a schedule is conflict serializable then it will also be view serializable, so we must check view
serializability only if a schedule is not conflict serializable.

[Link]

[Link]
 • if a schedule is not conflict serializable and it does not contain any blind write then it can
never be view serializable, but if not conflict serializable and have blind write then may or
may not be view serializable.

• If a schedule is not conflict serializable and if there exist a blind write.

• First tabulate all serial schedules possible. Then check one by one whether given schedule
is view equivalent to any of the serial schedule.
• If yes then schedule is view serializable otherwise not.

[Link]

[Link]
 Check Conflict
Serializability

Not Conflict Yes Conflict

Serializable Serializable

Blind Write No Blind Yes View

Present Write Serializable

Check For
Not View
View
Serializable
[Link]

Serializability [Link]
 • Two schedules S and S’ are view equivalent, if they satisfy following conditions –
• For each data item Q, if the transaction Ti reads the initial value of Q in schedule S , then
then the transaction Ti must, in schedule S’ ,also read the initial value of Q.
• If a transaction Ti in schedule S reads any data item Q, which is updated by transaction Tj,
then a transaction Ti must in schedule S’ also read data item Q updated by transaction Tj in
schedule S’.
• For each data item Q, the transaction (if any) that performs the final write(Q) operation in
schedule S, then the same transaction must also perform final write(Q) in schedule S’.

[Link]

[Link]
• Complexity wise finding the schedule is view serializable or not is a NP- complete problem.
 View Serializable

• A schedule S is view serializable, if it is view equivalent to a serial schedule.

[Link]

[Link]
 View Serializable

A schedule S is view serializable, if it is view equivalent to a serial schedule.

[Link]

[Link]
 Q A Schedule that is not conflict serializable and contains at least one blind write
then the schedule is
a) Always view serializable

b) Always non-serializable

c) May be View serializable

d) None of the above

[Link]

[Link]
 Q Which of the following statements (s) is/are TRUE?
S1: All view serializable schedules are also conflict serializable.
S2: All conflict serializable schedules are also view serializable.
S3: If a schedule is not conflict serializable then it is not view serializable
S4: If a schedule is not view serializable then it is not conflict serializable.

a) S1 and S2 only

b) S2 and S3 only

c) S2 and S4 only

d) S1 and S3 only

[Link]

[Link]
 Q Consider the following schedule ‘S’ with three transactions.
S: R1(B); R3(C); R1 (A); W2 (A); W1(A), W2 (B); W3 (A); W1 (B); W3 (B), W3 (C)
Which of the following is TRUE with respect to the above schedule?
S
a) It is conflict serializable with sequence [T1, T2 T3] T1 T2 T3
R(B)
b) It is conflict serializable with sequence [T2, T1 T3] R(C)
R(A)
W(A)
c) It is view serializable but not conflict serializable W(A)
W(B)
d) It is neither conflict serializable nor view serializable W(A)
W(B)
W(B)
W(C)

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Recoverability, Cascadeless & Strict Schedules

Lesson

Recoverability, Cascadeless & Strict Schedules

Lesson 40 of 45
 Transaction
Management
Recoverability, Cascadeless & Strict Schedules

[Link]
 NON- RECOVERABLE SCHEDULE
• A schedule in which for each pair of transaction Ti and Tj , such that if Tj reads a data item
previously written by Ti, then the commit or abort operation of Tj appears before Ti. Such a
schedule is called Non- Recoverable schedule.

S
T1 T2
R(X)
W(X)
R(X)
C
C
 RECOVERABLE SCHEDULE
• A schedule in which for each pair of transaction Ti and Tj , such that if Tj reads a data item
previously written by Ti, then the commit or abort of Ti must appear before Tj. Such a schedule
is called Recoverable schedule.

S
T1 T2
R(X)
W(X)
R(X)
C
C
 CASCADING ROLLBACK
• It is a phenomenon, in which a single transaction failure leads to a series of transaction rollbacks, is
called cascading rollback. Even if the schedule is recoverable the, the commit of transaction may
lead lot of transaction to rollback.

• Cascading rollback is undesirable, since it leads to undoing of a significant amount of work.

Uncommitted reads are not allowed in cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
R(X)
W(X)
R(X)
C
C
C
 CASCADING ROLLBACK
• It is a phenomenon, in which a single transaction failure leads to a series of transaction rollbacks, is
called cascading rollback. Even if the schedule is recoverable the, the commit of transaction may
lead lot of transaction to rollback.

• Cascading rollback is undesirable, since it leads to undoing of a significant amount of work.

Uncommitted reads are not allowed in cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
R(X)
W(X)
R(X)
C
C
C
 CASCADELESS SCHEDULE
• To avoid cascading rollback, cascade less schedule are used.

• A schedule in which for each pair of transactions Ti and Tj, such that if Tj reads a data item
previously written by Ti then the commit or abort of Ti must appear before read operation of Tj.
Such a schedule is called cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
C
R(X)
W(X)
C
R(X)
C
 Check Dirty Read

Dirty Read Present Dirty Read not Present

Not Cascadleless Both Recoverable and

Check order of commit Cascadless

If order of commit is
same in which dirty
read is done,
recoverable otherwise
not
 Strict Schedule
• A schedule in which for each pair of transactions Ti and Tj, such that if Tj reads a data item previously written by Ti
then the commit or abort of Ti must appear before read and write operation of Tj.

S1 S2 S3
T1 T2 T1 T2 T1 T2
R(a) R(a) R(a)
W(a) W(a) R(b)
W(a) C W(a)
C W(a) W(b)
R(a) R(a) C
C C R(a)
C
Q Consider the following schedules:
S1
S1: R1(x) W1(x) R1(y) R2(x) W2(x) C2, C1; T1 T2
R(x)
W(x)
R(y)
R(x)
W(x)
C
S2: R2(x) W2(x) R1(y) R1(x) W2(x) C2, C1; C

S2
Which of the following is true? T1 T2
a) Both S1 and S2 are recoverable R(x)
b) S1 is recoverable but S2 is not W(x)
R(y)
c) S2 is recoverable but S1 is not R(x)
d) Both schedule are not Recoverable W(x)
C
C
Q Consider the following schedule ‘S’.
S: r1(X); r2(Z); r3(X); r1(Z); r2(Y); r3(Y); w1(X); c1; w2(Z); w3(Y); w2(Y); c3; c2;
The schedule ‘S’ is
a) Recoverable S
T1 T2 T3
R(X)
b) Cascade less R(Z)
R(X)
R(Z)
c) recoverable and cascade-less R(Y)
R(Y)
d) None W(X)
C
W(Z)
W(Y)
W(Y)
C
C
Q Consider the given schedule
R1(X), R2(Z), R1(Z), R3(X), R3(Y), W1(X), Commit1, W3(Y), Commit3, R2(Y), W2(Z), W2(Y),
Commit2. The given schedule is
a) Recoverable only S
T1 T2 T3
R(X)
b) Cascade less only R(Z)
R(Z)
R(X)
c) recoverable and cascade-less R(Y)
W(X)
C
d) None W(Y)
C
R(Y)
W(Z)
W(Y)
C
Q Consider the following schedule
S: R2(x), w2(x), R3(y), R1(x), R1(y), w1(x), w3(y), R3(x), R1(y), C3, C2, C1;
The above schedule is
a) Recoverable but not cascade less
b) Recoverable and cascade less but not strict
c) Recoverable, cascade less and also strict
S
d) Not recoverable T1
T 2
T 3
R(X)
W(X)
R(Y)
R(X)
R(Y)
W(X)
W(Y)
R(X)
R(Y)
C
C
C
Q Which of the following statement is/are correct
a) Every view serializable schedule is conflict serializable

b) Every strict schedule is conflict serializable

c) Every conflict serializable schedule is cascade less

d) None of the above

Module

DBMS + SQL- Professional Knowledge

Topic
Transaction Management

Subtopic
Recoverability, Cascadeless & Strict Schedules

Lesson

Recoverability, Cascadeless & Strict Schedules

Lesson 41 of 45
 Transaction
Management
Recoverability, Cascadeless & Strict
Schedules

[Link]

[Link]
 NON- RECOVERABLE SCHEDULE
• A schedule in which for each pair of transaction Ti and Tj , such that if Tj reads a data item
previously written by Ti, then the commit or abort operation of Tj appears before Ti. Such a
schedule is called Non- Recoverable schedule.

S
T1 T2
R(X)
W(X)
R(X)
C
C
[Link]

[Link]
 RECOVERABLE SCHEDULE
• A schedule in which for each pair of transaction Ti and Tj , such that if Tj reads a data item
previously written by Ti, then the commit or abort of Ti must appear before Tj. Such a
schedule is called Recoverable schedule.

S
T1 T2
R(X)
W(X)
R(X)
C
C

[Link]

[Link]
 CASCADING ROLLBACK
• It is a phenomenon, in which a single transaction failure leads to a series of transaction rollbacks, is
called cascading rollback. Even if the schedule is recoverable the, the commit of transaction may lead
lot of transaction to rollback.

• Cascading rollback is undesirable, since it leads to undoing of a significant amount of work.

Uncommitted reads are not allowed in cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
R(X)
W(X)
R(X)
C
C
C
[Link]

[Link]
 CASCADING ROLLBACK
• It is a phenomenon, in which a single transaction failure leads to a series of transaction rollbacks, is
called cascading rollback. Even if the schedule is recoverable the, the commit of transaction may lead
lot of transaction to rollback.

• Cascading rollback is undesirable, since it leads to undoing of a significant amount of work.

Uncommitted reads are not allowed in cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
R(X)
W(X)
R(X)
C
C
C
[Link]

[Link]
 CASCADELESS SCHEDULE
• To avoid cascading rollback, cascade less schedule are used.

• A schedule in which for each pair of transactions Ti and Tj, such that if Tj reads a data item previously
written by Ti then the commit or abort of Ti must appear before read operation of Tj. Such a schedule
is called cascade less schedule.

S
T1 T2 T3
R(X)
W(X)
C
R(X)
W(X)
C
R(X)
C
[Link]

[Link]
 Check Dirty Read

Dirty Read Present Dirty Read not Present

Not Cascadleless
Both Recoverable and
Check order of Cascadless
commit

If order of commit is
same in which dirty
[Link]
read is done,
recoverable otherwise
not [Link]
 Strict Schedule
• A schedule in which for each pair of transactions Ti and Tj, such that if Tj reads a data item previously written by Ti
then the commit or abort of Ti must appear before read and write operation of Tj.

S1 S2 S3
T1 T2 T1 T2 T1 T2
R(a) R(a) R(a)
W(a) W(a) R(b)
W(a) C W(a)
C W(a) W(b)
R(a) R(a) C
C C R(a)
C

[Link]

[Link]
[Link]

[Link]
 Q Consider the following schedules:
S1
S1: R1(x) W1(x) R1(y) R2(x) W2(x) C2, C1; T1 T2
R(x)
W(x)
R(y)
R(x)
W(x)
C
S2: R2(x) W2(x) R1(y) R1(x) W2(x) C2, C1; C

S2
Which of the following is true? T1 T2
a) Both S1 and S2 are recoverable R(x)
b) S1 is recoverable but S2 is not W(x)
R(y)
c) S2 is recoverable but S1 is not
R(x)
d) Both schedule are not Recoverable W(x)
C
[Link]

C
[Link]
 Q Consider the following schedule ‘S’.
S: r1(X); r2(Z); r3(X); r1(Z); r2(Y); r3(Y); w1(X); c1; w2(Z); w3(Y); w2(Y); c3; c2;
The schedule ‘S’ is
a) Recoverable S
T1 T2 T3
R(X)
b) Cascade less R(Z)
R(X)
c) recoverable and cascade-less R(Z)
R(Y)
R(Y)
d) None W(X)
C
W(Z)
W(Y)
W(Y)
C
[Link]

C
[Link]
 Q Consider the given schedule
R1(X), R2(Z), R1(Z), R3(X), R3(Y), W1(X), Commit1, W3(Y), Commit3, R2(Y), W2(Z),
W2(Y), Commit2. The given schedule is
a) Recoverable only S
T1 T2 T3
R(X)
b) Cascade less only R(Z)
R(Z)
R(X)
c) recoverable and cascade-less R(Y)
W(X)
C
d) None W(Y)
C
R(Y)
W(Z)
W(Y)
C

[Link]

[Link]
 Q Consider the following schedule
S: R2(x), w2(x), R3(y), R1(x), R1(y), w1(x), w3(y), R3(x), R1(y), C3, C2, C1;
The above schedule is
a) Recoverable but not cascade less
b) Recoverable and cascade less but not strict
c) Recoverable, cascade less and also strict
S
d) Not recoverable T1 T 2 T 3
R(X)
W(X)
R(Y)
R(X)
R(Y)
W(X)
W(Y)
R(X)
R(Y)
C
[Link]
C
[Link]
C
 Q Which of the following statement is/are correct
a) Every view serializable schedule is conflict serializable

b) Every strict schedule is conflict serializable

c) Every conflict serializable schedule is cascade less

d) None of the above

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Concurrency Control

Subtopic
Introduction to Concurrency Control

Lesson

Introduction to Concurrency Control

Lesson 42 of 45
 Concurrency
Control
Introduction to Concurrency Control

[Link]

[Link]
 CONCURRENCY CONTROL

• Now we understood that if there is a schedule how to check whether it will work
correctly or not i.e. weather it will maintain the consistency of the data base or not.
(conflict serializability, view serializability, recoverability and cascade less)
• Now we will understand those protocol which guarantee how to design those
schedules which ensure conflict serializability or other [Link] are different
approach or idea to ensure conflict serializability which is the most important
property.
• So first we must understand what is the possibility of conflict between two instruction
and if somehow, we manage than the generated schedule will always be conflict
serializable

[Link]

[Link]
 • If we remember, two instructions are conflicting if and only if three things
happen simultaneously

1. Belong to different transactions

1. Must operate on same data value

1. At least one of them should be a write instruction

[Link]

[Link]
 • if we think sufficiently, there will be no way to change any of these three
conditions. But actual problem is not that two instructions are trying to access
same data base but, they are trying to do that at same time.
• So point if we somehow by any technique manage that two transaction do not
access same data at same time then ensuring conflict serializability will be easy.
Now question is how to approach conflict serializability, there are Three popular
approaches to go forwards.

[Link]

[Link]
 • Time stamping based method: - where before entering the system, a specific
order is decided among the transaction, so in case of a clash we can decide
which one to allow and which to stop.

[Link]

[Link]
 • Lock based method: - where we ask a transaction to first lock a data item before
using it. So that no different transaction can use a data at the same time,
removing any possibility of conflict.

• 2 phase locking
1. Basic 2pl
2. Conservative 2pl
3. Rigorous 2pl
4. Strict 2pl

• Graph based protocol

[Link]

[Link]
 • Validation based protocol – Majority of transactions are read only transactions,
the rate of conflicts among the transaction may be low, thus many of
transaction, if executed without the supervision of a concurrency control
scheme, would nerveless leave the system in a consistent state.

[Link]

[Link]
 Goals of a Protocol: - We desire the following properties from schedule generating
protocols

• Concurrency should be as high as possible, as this is our ultimate goal

because of which we are making all the effort.

• The time taken by a transaction should also be less.

• Desirable Properties satisfied by the protocol

• Easy to understand and implement

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Concurrency Control

Subtopic
Timestamp Ordering Protocols

Lesson

Timestamp Ordering Protocols

Lesson 43 of 45
 Concurrency
Control
Timestamp Ordering Protocols

[Link]

[Link]
 TIME STAMP ORDERING PROTOCOL
• Basic idea of time stamping is to decide the order between the transaction
before they enter in the system using a stamp (time stamp), in case of any
conflict during the execution order can be decided using the time stamp.
• Let’s understand how this protocol works, here we have two idea of
timestamping, one for the transaction, and other for the data item.

[Link]

[Link]
 • Time stamp for transaction,
• With each transaction ti, in the system, we associate a unique fixed timestamp, denoted
by TS(ti).
• This timestamp is assigned by database system to a transaction at time transaction enters
into the system.
• If a transaction has been assigned a timestamp TS(ti) and a new transaction tj , enters into
the system with a timestamp TS(tj), then always TS(ti) <TS(tj).

[Link]

[Link]
 • Two things are to be noted
1. First time stamp of a transaction remain fixed throughout the execution
1. Second it is unique means no two transaction can have the same timestamp.

• The reason why we called time stamp not stamp, because for stamping we use
the value of the system clock as stamp, advantage is,
• Tt will always be unique as time never repeats
• There is no requirement of refreshing and starting with fresh value.

[Link]

[Link]
 • The time stamp of the transaction also determines the serializability order.
• Thus if TS(ti) <TS(tj), then the system must ensure that the produced schedule is
equivalent to a serial schedule in which transaction ti appears before transaction
tj.

[Link]

[Link]
 • Time stamp with data item, in order to assure such scheme, the protocol maintains for each
data item Q two timestamp values:

1. W-timestamp(Q) is the largest time-stamp of any transaction that executed write(Q)

successfully.

1. R-timestamp(Q) is the largest time-stamp of any transaction that executed read(Q)

successfully.

• These timestamps are updated whenever a new read(Q) or write(Q) instruction is executed.

[Link]

[Link]
 • Suppose a transaction Ti request a read(Q)

1. If TS(Ti) < W-timestamp(Q), then Ti needs to read a value of Q that was already
overwritten. Hence, the read operation is rejected, and Ti is rolled back.

1. If TS(Ti)≥ W-timestamp(Q), then the read operation is executed, and R-timestamp(Q) is

set to the maximum of R-timestamp(Q) and TS(Ti).

[Link]

[Link]
 • Suppose that transaction Ti issues write(Q).
1. If TS(Ti) < R-timestamp(Q), then the value of Q that Ti is producing was needed previously, and
the system assumed that that value would never be produced. Hence, the write operation is
rejected, and Ti is rolled back.

1. If TS(Ti) < W-timestamp(Q), then Ti is attempting to write an obsolete value of Q. Hence, this
write operation is rejected, and Ti is rolled back.

1. If TS(Ti) ≥ R-timestamp(Q), then the write operation is executed, and W-timestamp(Q) is set to
max(W-timestamp(Q), TS(Ti)).

1. If TS(Ti) ≥ W-timestamp(Q), then the write operation is executed, and W-timestamp(Q) is set to
max(W-timestamp(Q), TS(Ti)).

[Link]

[Link]
 • If a transaction Ti is rolled back by the concurrency control scheme as a result of
either a read or write operation, the system assigns it’s a new timestamp and
restarts it.

[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering

Thomas Write Rule

Basic 2PL
Conservative 2PL
Rigorous 2PL
Strict 2PL

[Link]

[Link]
 Properties
• Time stamp ordering protocol ensures conflict serializability. Because conflicting operations
are processed in timestamp order, since all the arcs in the precedence graph are of the form
thus, there will be no cycles in the precedence graph.

• As we know that view is liberal form conflict so view serializability also holds good

• As there is a possibility of dirty read, and no restriction on when to commit, so can be

irrecoverable and may suffer from cascading rollback.

• At the time of request, here either we allow or we reject, so there is no idea of deadlock.

• If a schedule is not conflict serializable then it is not allowed by time stamp ordering scheme.

• But it is not necessary that all conflict serializable schedule generated by time stamping.

[Link]

[Link]
 Conclusion

• It may cause starvation to occur, as if a sequence of conflicting transactions causes repeated

restarting of the long transaction, and then there is a possibility of starvation of long
transaction.

• It is relatively slow as before executing every instruction we have to check conditions before.
Time stamping protocol ensure that the schedule designed through this protocol will always
be conflict serializable.

• This protocol can also be used for determining the serializability order (order in which
transaction must execute) among the transaction in advance.

[Link]

[Link]
 • Further modifications are possible if we want to ensure recoverability and cascade lessness,
using different approaches

• By performing all writes together at the end of the transaction, i.e. while writers are in
progress, no transaction is permitted to access any of data items that have been written.

• By using a limited form of locking, where by read of uncommitted items are postponed
until the transaction that uploaded the item commit.

• Recoverability alone can be ensured by tracking uncommitted writes and allowing a

transaction ti to commit only after the commit of any transaction that wrote a value that ti
read.

[Link]

[Link]
 THOMAS WRITE RULE
• Thomas write is an improvement in time stamping protocol, which makes some modification
and may generate those protocols that are even view serializable, because it allows greater
potential concurrency.

• It is a Modified version of the timestamp-ordering protocol in which Blind write operations

may be ignored under certain circumstances.

• The protocol rules for read operations remain unchanged. while for write operation, there is
slightly change in Thomas write rule than timestamp ordering protocol.

[Link]

[Link]
 When Ti attempts to write data item Q,

• if TS(Ti ) < W-timestamp(Q), then Ti is attempting to write an obsolete value of

{Q}. Rather than rolling back Ti as the timestamp ordering protocol would have
done, this {write} operation can be ignored.

[Link]

[Link]
 • This modification is valid as the any transaction with TS(Ti ) < W-timestamp(Q), the value
written by this transaction will never be read by any other transaction performing Read(Q)
ignoring such obsolete write operation is considerable.

• Thomas' Write Rule allows greater potential concurrency. Allows some view-serializable
schedules that are not conflict serializable.

[Link]

[Link]
[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering YES YES NO NO YES

Thomas Write Rule
Basic 2PL
Conservative 2PL
Rigorous 2PL
Strict 2PL

[Link]

[Link]
 Q Which of the following time stamp ordering protocol(s) allow(s) the following
schedules?
S: W1(A); W2(A); W3(A); R2(A) ; R4(A);
Time stamps : T1 : 5, T2 : 10, T3 : 15; T4 : 20
a) Thomas write rule b) Basic time stamp

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Concurrency Control

Subtopic
Lock-Based Protocols & Basic 2PL

Lesson

Lock-Based Protocols & Basic 2PL

Lesson 44 of 45
 Concurrency
Control
Lock-Based Protocols & Basic 2PL

[Link]

[Link]
 Lock Based Protocols
• To ensure isolation is to require that data items be accessed in a mutually
exclusive manner i.e. while one transaction is accessing a data item, no other
transaction can modify that data item. Locking is the most fundamental
approach to ensure this.
• Lock based protocols ensure this requirement. Idea is first obtain a lock on the
desired data item then if lock is granted then perform the operation and then
unlock it.

[Link]

[Link]
 • In general, we support two modes of lock because, to provide better concurrency.

• Shared mode
• If transaction Ti has obtained a shared-mode lock (denoted by S) on any data item Q, then
Ti can read, but cannot write Q, any other transaction can also acquire a shared mode lock
on the same data item(this is the reason we called this shared mode).

• Exclusive mode
• If transaction Ti has obtained an exclusive-mode lock (denoted by X) on any data item Q,
then Ti can both read and write Q, any other transaction cannot acquire either a shared or
exclusive mode lock on the same data item. (this is the reason we called this exclusive
mode)

[Link]

[Link]
 Lock –Compatibility Matrix

• Conclusion shared is compatible only with shared while exclusive is not compatible either with
shared or exclusive.
• To access a data item, transaction Ti must first lock that item, if the data item is already locked
by another transaction in an incompatible mode, or some other transaction is already waiting
in non-compatible mode, then concurrency control manager will not grant the lock until all
incompatible locks held by other transactions have been released. The lock is then granted.

[Link]

[Link]
 • Lock based protocol do not ensure serializability as granting and releasing of lock do not
follow any order and any transaction any time may go for lock and unlock. Here in the
example below we can see, that even this transaction in using locking but neither it is conflict
serializable nor independent from deadlock.
T1 T2
LOCK-X(A)
READ(A)
WRITE(A)
UNLOCK(A)
LOCK-S(B)
READ(B)
UNLOCK(B)
LOCK-X(B)
READ(B)
WRITE(B)
UNLOCK(B)
LOCK-S(A)
READ(A)
[Link]
UNLOCK(A)
[Link]
 • If we do not use locking, or if we unlock data items too soon after reading or writing them, we
may get inconsistent states, as there exists a possibility of dirty read. On the other hand, if we
do not unlock a data item before requesting a lock on another data item, concurrency will be
poor.

• We shall require that each transaction in the system follow a set of rules, called a locking
protocol, indicating when a transaction may lock and unlock each of the data items for e.g. 2pl
or graph based locking.

• Locking protocols restrict the number of possible schedules.

[Link]

[Link]
Module

DBMS + SQL- Professional Knowledge

Topic
Concurrency Control

Subtopic
Variations of 2PL (Strict, Rigorous, Conservative)

Lesson

Variations of 2PL (Strict, Rigorous, Conservative)

Lesson 45 of 45
 Concurrency
Control
Variations of 2PL (Strict, Rigorous,
Conservative)

[Link]

[Link]
 Two phase locking protocol(2PL)
• The protocol ensures that each transaction issue lock and unlock requests in two phases, note
that each transaction will be 2 phased not schedule.

• Growing phase- A transaction may obtain locks, but not release any locks.

• Shrinking phase- A transaction may release locks, but may not obtain any new locks.

• Initially a transaction is in growing phase and acquires lock as needed and in between can
perform operation reach to lock point and once a transaction releases a lock, it can issue no
more lock requests i.e. it enters the shrinking phase.

[Link]

[Link]
 T1 T2
LOCK-X(A)
READ(A)
WRITE(A)
LOCK-S(B)
READ(B)
LOCK-X(B)
READ(B)
WRITE(B)
LOCK-S(A)
READ(A)
UNLOCK(B)
UNLOCK(A)
UNLOCK(B)
UNLOCK(A)

[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering YES YES NO NO YES

Thomas Write Rule NO YES NO NO YES
Basic 2PL
Conservative 2PL
Rigorous 2PL
Strict 2PL

[Link]

[Link]
 Properties
• 2PL ensures conflict serializability, and the ordering of transaction over lock points is itself a
serializability order of a schedule in 2PL.

• If a schedule is allowed in 2PL protocol then definitely it is always conflict serializable. But it is
not necessary that if a schedule is conflict serializable then it will be generated by 2pl.
Equivalent serial schedule is based on the order of lock points.

• View serializability is also guaranteed.

• Does not ensure freedom from deadlock

• May cause non-recoverability.

• Cascading rollback may occur.

[Link]

[Link]
 Conservative 2PL
• The idea is there is no growing phase transaction start directly from lock point,
i.e. transaction must first acquire all the required locks then only it can start
execution. If all the locks are not available then transaction must release the
acquired locks and must wait.
• Shrinking phase will work as usual, and transaction can unlock any data item
anytime.
• we must have a knowledge in future to understand what is data required so
that we can use it

[Link]

[Link]
 Q In conservative two phase locking protocol, a transaction
a) Should release exclusive locks only after the commit operation
b) Should release all the locks only at beginning of the transaction
c) should acquire all the locks at beginning of the transaction
d) Should acquire all the exclusive locks at beginning transaction

[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering YES YES NO NO YES

Thomas Write Rule NO YES NO NO YES
Basic 2PL YES YES NO NO NO
Conservative 2PL
Rigorous 2PL
Strict 2PL

[Link]

[Link]
 RIGOROUS 2PL
• Requires that all locks be held until the transaction commits.
• This protocol requires that locking be two phase and also all the locks taken be
held by transaction until that transaction commit.
• Hence there is no shrinking phase in the system.

[Link]

[Link]
 Q In a Rigorous 2 phase protocol
a) All shared locks held by the transaction are related after the
transaction is committed
b) All exclusive locks held by the transaction are released after the
transaction is committed
c) All locks held by the transaction are released after the transaction is
committed
d) All locks held by the transaction are released before the transaction is
committed

[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering YES YES NO NO YES

Thomas Write Rule NO YES NO NO YES
Basic 2PL YES YES NO NO NO
Conservative 2PL YES YES NO NO YES
Rigorous 2PL
Strict 2PL

[Link]

[Link]
 STRICT 2PL
• that all exclusive-mode locks taken by a transaction be held until that transaction commits.
This requirement ensures that any data written by an uncommitted transaction are locked in
exclusive mode until the transaction commits, preventing any other transaction from reading
the data.

• This protocol requires that locking be two phase and also that exclusive –mode locks taken by
transaction be held until that transaction commits.

• So it is simplified form of rigorous 2pl

[Link]

[Link]
 Conflict View Recoverability Cascadelessness Deadlock
Serializability Serializability Freedom

Time Stamp Ordering YES YES NO NO YES

Thomas Write Rule NO YES NO NO YES
Basic 2PL YES YES NO NO NO
Conservative 2PL YES YES NO NO YES
Rigorous 2PL YES YES YES YES NO
Strict 2PL

[Link]

[Link]
 Q Which of the following statement is/are correct
a) Every conflict serializable schedule allowed under 2PL protocol is
allowed by basic time stamping protocol.
b) Every schedule allowed under basic time stamping protocol is
allowed by Thomas-write rule
c) Every schedule allowed under Thomas-write rule is allowed by basic
time stamping protocol
d) none

[Link]

[Link]