DATABASE MANAGEMENT SYSTEMS

UNIT-1
Introduction to Data
Data refers to raw, unorganized facts that need to be processed to be
meaningful. It can be numbers, characters, symbols, images, audio, or video.
Examples of Data:
• A list of names
• Marks scored by students
• Product prices
• Sensor readings
Types of Data:
• Numeric Data (e.g., 25, 49.5)
• Text Data (e.g., "John", "Apple")
• Boolean Data (True/False)
• Multimedia Data (Images, Videos)

Introduction to Database
A Database is a structured collection of data that can be easily accessed,
managed, and updated.
Characteristics:
• Organized in tables (rows and columns)
• Reduces redundancy
• Can handle large volumes of data
• Allows multiple users to access data simultaneously
Example:
A library database might store:
• Book titles
• Authors
• Issued books
P a g e 1 | 100
 DATABASE MANAGEMENT SYSTEMS

• Student records

Introduction to DBMS (Database Management System)

A DBMS is a software system that allows users to define, create, maintain, and
control access to the database.
Functions of DBMS:
• Data storage, retrieval, and update
• User access control
• Backup and recovery
• Data integrity and security
Popular DBMS Software:
• MySQL
• Oracle
• Microsoft SQL Server
• PostgreSQL
• MongoDB (NoSQL DBMS)

Applications of DBMS
A DBMS is used to manage data efficiently in different types of applications
across industries. It helps in storing, retrieving, and managing large volumes of
data securely and quickly. Below are some of the major real-world applications
of DBMS:

1. Banking Systems
• Use of DBMS: To manage customer accounts, transactions, loan details,
and financial records.
• Examples:
P a g e 2 | 100
 DATABASE MANAGEMENT SYSTEMS

o Storing account holder information

o ATM transaction management
o Online fund transfers
o Loan and interest calculations
• DBMS Used: Oracle, IBM Db2

2. Education and Academic Institutions

• Use of DBMS: To manage student records, course registrations, faculty
details, and examination results.
• Examples:
o Storing student information (roll number, marks, attendance)
o Generating report cards
o Managing library databases
o Online learning management systems (LMS)
• DBMS Used: MySQL, PostgreSQL

3. Healthcare and Hospitals

• Use of DBMS: To maintain patient records, medical histories,
prescriptions, billing, and appointments.
• Examples:
o Patient management systems
o Doctor scheduling and appointments
o Laboratory and diagnostic data
o Insurance and billing systems
• DBMS Used: Microsoft SQL Server, MongoDB (for NoSQL)

P a g e 3 | 100
 DATABASE MANAGEMENT SYSTEMS

4. E-Commerce and Online Retail

• Use of DBMS: To track customer orders, product inventories, payments,
and deliveries.
• Examples:
o Product catalog and search data
o Customer reviews and ratings
o Order history and tracking
o Shopping cart data and transactions
• DBMS Used: MySQL, Oracle, Firebase (for mobile apps)

5. Airlines and Railways

• Use of DBMS: To handle schedules, bookings, cancellations, and
passenger information.
• Examples:
o Online ticket reservation systems
o Passenger record management
o Flight/train schedule tracking
o Seat availability and allocation
• DBMS Used: IBM Db2, Oracle

6. Telecommunication
• Use of DBMS: To store call records, billing details, user information, and
mobile usage data.
• Examples:
o Call data records (CDRs)
o SMS logs and internet data usage

P a g e 4 | 100
 DATABASE MANAGEMENT SYSTEMS

o Customer subscription information

o Prepaid and postpaid plan management
• DBMS Used: Oracle, Cassandra (NoSQL)

Advantages of DBMS
1. Reduced Data Redundancy
• DBMS avoids storing duplicate data by using a centralized system.
2. Improved Data Sharing
• Multiple users can access data simultaneously without conflict.
3. Better Data Security
• Access can be restricted to authorized users only.
4. Data Integrity
• Ensures accuracy and consistency through constraints and validation.
5. Backup and Recovery
• Automatic backups and recovery features protect against data loss.
6. Data Independence
• Data and application programs are independent; changing the database
does not require changing applications.

Disadvantages of DBMS
1. Complexity
• DBMS systems can be complex to set up and use for beginners.
2. High Cost
• Enterprise DBMS software can be expensive (hardware, licensing,
maintenance).
3. Large Size
• DBMS software requires significant memory and storage space.
P a g e 5 | 100
 DATABASE MANAGEMENT SYSTEMS

4. Performance Issues
• In large-scale systems, performance might decrease if not optimized
properly.
5. Need for Trained Staff
• Skilled professionals are required to manage and maintain DBMS.

Roles of Database Users and Administrators

In a database environment, different types of users and administrators

interact with the DBMS depending on their needs, technical skills, and
responsibilities.

### A. Database Users

These are the people who use the database for various tasks. They can be
categorized into several types:
1. End Users
• These are the real users who interact with the database through
applications.
• They don’t know the inner structure of the database.
• Example: A student checking their exam results online.
Types of End Users:
• Casual Users: Occasionally access the DB using queries (e.g., managers
viewing monthly reports).
• Naive Users: Use predefined applications (e.g., bank clerks).
• Sophisticated Users: Use advanced tools, write queries directly (e.g.,
analysts).
• Standalone Users: Use desktop-based DB applications without
network (e.g., a personal finance app).

P a g e 6 | 100
 DATABASE MANAGEMENT SYSTEMS

2. Application Programmers
• Write code (Java, Python, PHP, etc.) to create programs that interact
with the database.
• They use APIs like JDBC, ODBC to connect applications to databases.

3. Database Designers
• They design the structure of the database (schemas, tables, constraints).
• Decide how data should be stored, related, and optimized.
• Ensure the system supports business requirements.

4. System Analysts
• Act as a bridge between users and developers.
• Analyze business needs and propose a database-based solution.

### B. Database Administrators (DBA)

The DBA is responsible for managing, maintaining, and securing the
database.

Who is a Database Administrator (DBA)?

A Database Administrator (DBA) is a specialized IT professional who is
responsible for the installation, configuration, operation, and maintenance
of a database system.
The DBA ensures that the database is secure, available, efficient, and
performs well under all conditions.
Key Responsibilities of a DBA
Here is a breakdown of the major tasks performed by a DBA:

P a g e 7 | 100
 DATABASE MANAGEMENT SYSTEMS

1. Database Installation and Configuration

• Installing the DBMS software (e.g., Oracle, MySQL, SQL Server).
• Configuring the system based on the organization’s needs.
• Setting up database instances, memory allocations, and storage structures.

2. Database Design and Implementation

• Collaborating with database designers and application developers.
• Creating logical and physical designs of databases.
• Defining schemas (tables, fields, data types, relationships).
• Implementing constraints like primary keys, foreign keys, and indexes.

3. Data Security Management

• Ensuring that only authorized users can access data.
• Creating and managing user accounts and roles.
• Setting permissions and privileges for data access.
• Applying encryption and data masking techniques to protect sensitive
data.

4. Backup and Recovery

• Creating regular database backups (full and incremental).
• Setting up automatic backup schedules.
• Implementing disaster recovery plans.
• Ensuring quick and complete data restoration in case of system failure,
hardware crash, or data corruption.

5. Performance Monitoring and Tuning

P a g e 8 | 100
 DATABASE MANAGEMENT SYSTEMS

• Monitoring CPU, memory, disk I/O, and query performance.

• Identifying and fixing bottlenecks.
• Using indexes, query optimization, and load balancing.
• Ensuring that the system can handle high-volume data and user load
efficiently.

6. Data Integrity and Consistency

• Enforcing rules and constraints to maintain accurate and consistent data.
• Monitoring and resolving data anomalies or conflicts.
• Validating input data and avoiding duplication or corruption.

7. Storage and Capacity Planning

• Monitoring disk usage and database size.
• Forecasting future storage needs.
• Adding partitions, tablespaces, or hardware resources as needed.

8. User Support and Training

• Assisting developers and end-users with database queries and reports.
• Providing documentation and guidelines.
• Training staff on basic DBMS usage, backup procedures, etc.

9. Software Updates and Patch Management

• Applying security patches and software updates.
• Migrating databases to newer versions of DBMS when needed.

P a g e 9 | 100
 DATABASE MANAGEMENT SYSTEMS

Data Models – Introduction and Importance

1. What is a Data Model?

A Data Model is a conceptual framework that describes how data is
structured, stored, and accessed in a database system.
It defines:
• What data will be stored,
• How the data is organized,
• Relationships between data items,
• Constraints on the data,
• And how the data will be retrieved or manipulated.
Note: A data model is a blueprint or plan that guides the creation of a database.
2. Purpose of Data Models
The purpose of using data models is to:
• Simplify the design of databases.
• Ensure consistency, accuracy, and clarity in data handling.
• Act as a communication bridge between database designers, developers,
and end-users.

Importance of Data Models in DBMS

A Data Model is a conceptual blueprint that defines how data is organized,
related, stored, and accessed in a database system. It plays a critical role in the
design, development, and maintenance of a database.
Why Are Data Models Important?
Here are the key reasons:

1. Improves Database Design

P a g e 10 | 100
 DATABASE MANAGEMENT SYSTEMS

• Data models guide developers and designers in creating a well-structured

and logical database.
• Helps identify the entities (objects), attributes (properties), and
relationships between data items before implementation.
Example: Knowing that a "Student" enrolls in many "Courses" helps build a
many-to-many relationship structure.

2. Ensures Data Consistency and Integrity

• A data model defines rules and constraints (like primary keys, foreign
keys, uniqueness, etc.).
• This prevents data duplication, inaccuracies, and inconsistencies.
Example: A data model ensures that no student record exists without a valid
student ID.

3. Enhances Communication Between Stakeholders

• Acts as a common language between:
o Developers
o Designers
o Business analysts
o End-users
• Makes it easier to understand requirements and expectations visually
(e.g., through ER diagrams).
4. Improves Data Retrieval and Performance
• Models help design tables and relationships that optimize query
performance.
• Logical structuring reduces redundancy and improves data retrieval
speed.
Example: Creating indexes on frequently searched columns is guided by data
modeling.
5. Simplifies Application Development
P a g e 11 | 100
 DATABASE MANAGEMENT SYSTEMS

• Developers can build applications based on the data model structure

without guessing the schema.
• Reduces development errors and increases productivity.

Types of Data Models

The main types of data models in DBMS are:
1. Hierarchical Data Model
2. Network Data Model
3. Relational Data Model
4. Object-Oriented Data Model
Each of these models defines how data is organized, connected, and accessed.

1. Hierarchical Data Model

Definition:
The hierarchical data model organizes data in a tree-like structure where data
is stored as records (nodes) with parent-child relationships.
Structure:
• One-to-many relationship (each parent can have multiple children).
• Data is stored in a hierarchy, much like an organizational chart or file
system.

P a g e 12 | 100
 DATABASE MANAGEMENT SYSTEMS

Example : Consider the below Student database system hierarchical model.

In the above-given figure, we have few students and few course-enroll and a
course can be assigned to a single student only, but a student can enroll in any
number of courses and with this the relationship becomes one-to-many. We can
represent the given hierarchical model like the below relational tables:

Name Dep Course-taught

John CSE CA

Jake CSE SE

Royal CSE DBMS

STUDENT Table

Name Course-enroll Grade

Gami CA 2.0

P a g e 13 | 100
 DATABASE MANAGEMENT SYSTEMS

Mary SE 3.0

Mayen SE 4.0

Advantages of the Hierarchical Model

Simplicity: Data naturally have hierarchical relationship in most of the practical

situations. Therefore, it is easier to view data arranged in tree structure manner.

Security: This database system can enforce varying degree of security feature
unlike flat file system.

Database Integrity: Because of its inherent parent-child structure, database

integrity is highly promoted in these systems.

Efficiency: The hierarchical database model is a very efficient one when the
database contains a large number of 1: N relationships (one-to-many
relationships) and when the users require large number of transactions, using
data whose relationships are fixed.

Disadvantages/Limitations of the Hierarchical Model

Implementation Complexity: Although it is simple and easy to design, it is

quite complex to implement.

Database Management Problem: If we make any changes in the database

structure, then we need to make changes in the entire application program that
access the database.

Lack of Structural Independence: There is a lack of structural independence

because when we change the structure then it becomes compulsory to change
the application too.

Operational Anomalies: Hierarchical model suffers from the insert, delete and
update anomalies, also retrieval operation is difficult.

P a g e 14 | 100
 DATABASE MANAGEMENT SYSTEMS

2. Network Data Model

Definition:
The network data model organizes data using graph structures, allowing
many-to-many relationships using sets.
Structure:
• Uses nodes (records) and links (pointers).
• Each record can have multiple parent and child records.

Structure of a Network Model

In the above figure, member TWO has only one owner 'ONE' whereas member
FIVE has two owners i.e, TWO and THREE. Here, each link between the two
record types represents 1 : M relationship between them. This model consists of
both lateral and top-down connections between the nodes. Therefore, it allows
1: 1, 1 : M, M : N relationships among the given entities which helps in
avoiding data redundancy problems as it supports multiple paths to the same
record.

Advantages of Network Model

Conceptual Simplicity: Just like hierarchical model it also simple and easy to
implement.

Capability to Handle more Relationship Types: The network model can

handle one to one(1:1) and many to many (N: N) relationship.

Ease to Access Data: The data access is easier than the hierarchical model.

P a g e 15 | 100
 DATABASE MANAGEMENT SYSTEMS

Data Integrity: Since it is based on the parent child relationship, there is always
a link between the parent segment and the child segment under it.

Data Independence: The network model is better than hierarchical model in

case of data independence.

Disadvantages of Network Model

System Complexity: All the records have to maintain using pointers thus the
database structure becomes more complex.

Operational Anomalies: Large number of pointers is required and hence

insertion, deletion and updating records are more complex.

Absence of Structural Independence: There is lack of structural independence

because when we change the structure then it becomes compulsory to change
the application too

3. Relational Data Model

Definition:
The relational data model represents data in tables (relations) consisting of
rows (tuples) and columns (attributes).
Structure:
• Each table has a primary key.
• Tables can be linked through foreign keys.

For example, consider a relation STUDENT with

attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in
the table.

P a g e 16 | 100
 DATABASE MANAGEMENT SYSTEMS

Key Terms in the Relational Model

1. Attribute: Attributes are the properties that define an entity.
• Example: ROLL_NO, NAME, ADDRESS etc.
2. Relation Schema: A relation schema defines the structure of the relation and
represents the name of the relation with its attributes.
• Example: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and
AGE) is the relation schema for STUDENT. If a schema has more than 1
relation it is called Relational Schema.
3. Tuple: A Tuple represents a row in a relation. Each tuple contains a set of
attribute values that describe a particular entity.

4. Relation Instance: The set of tuples of a relation at a particular instance of

time is called a relation instance. It can change whenever there is an insertion,
deletion or update in the database.
5. Degree: The number of attributes in the relation is known as the degree of the
relation.

P a g e 17 | 100
 DATABASE MANAGEMENT SYSTEMS

• Example: The STUDENT relation has a degree of 5, as it has 5

attributes.
6. Cardinality: The number of tuples in a relation is known as cardinality.
• Example: The STUDENT relation defined above has cardinality 4.
7. Column: The column represents the set of values for a particular attribute.
• Example: The column ROLL_NO is extracted from the relation
STUDENT.
8. NULL Values: The value which is not known or unavailable is called a
NULL value. It is represented by NULL.
• Example: PHONE of STUDENT having ROLL_NO 4 is NULL.

Types of Keys in the Relational Model

1. Primary Key:
A Primary Key uniquely identifies each tuple in a relation. It must contain
unique values and cannot have NULL values. Example: ROLL_NO in
the STUDENT table is the primary key.
2. Candidate Key
A Candidate Key is a set of attributes that can uniquely identify a tuple in a
relation. There can be multiple candidate keys, and one of them is chosen as the
primary key.

3. Super Key
A Super Key is a set of attributes that can uniquely identify a tuple. It may
contain extra attributes that are not necessary for uniqueness.
4. Foreign Key
A Foreign Key is an attribute in one relation that refers to the primary key of
another relation. It establishes relationships between
tables. Example: BRANCH_CODE in the STUDENT table is a foreign key
that refers to the primary key BRANCH_CODE in the BRANCH table.
P a g e 18 | 100
 DATABASE MANAGEMENT SYSTEMS

5. Composite Key
A Composite Key is formed by combining two or more attributes to uniquely
identify a tuple. Example: A combination
of FIRST_NAME and LAST_NAME could be a composite key if no one in the
database shares the same full name.

Advantages of Relational Model (Relational Databases)

Simplicity: We have seen that both the hierarchical and network models are
conceptually simple, but relational model is simpler than both of those two.

Ease of Use: Users can easily access/retrieve the required information from the
database. The Relational model in DBMS is easy as tables consisting of rows
and columns are quite

Data independence: The Structure of Relational database can be changed

without having to change any application.

Security: Data is secure as Relational Database Management System allows

only authorized users to directly access the data. No unauthorized user can
access the information.

Collaboration: Multiple users can access the database to retrieve information at

the same time and even if data is being updated.

Disadvantages of Relational Model

Maintenance Problem: The maintenance of the relational database becomes

difficult overtime due to the increase in the data. Developers and programmers
have to spend a lot of time in maintaining the database.

Cost: The relational database system is costly to set up and maintain. The initial
cost of the software alone can be quite high for smaller businesses.

Physical Storage: A relational database is comprised of rows and columns,

which requires a lot of physical memory because each operation performed

P a g e 19 | 100
 DATABASE MANAGEMENT SYSTEMS

depends on separate storage. The requirements of physical memory may

increase along with the increase of data.

Lack of Scalability: While using the relational database over multiple servers,
its structure changes and becomes difficult to handle, especially when the
quantity of the data is large. Due to this, the data is not scalable on different
physical storage servers. Ultimately, its performance is affected i.e. lack of
availability of data and load time etc.

Complexity in Structure: Relational databases can only store data in tabular

form which makes it difficult to represent complex relationships between
objects. This is an issue because many applications require more than one table
to store all the necessary data required by their application logic.

4. Object-Oriented Data Model

Definition:
This model combines object-oriented programming concepts with database
technology.
Data is stored in the form of objects, similar to programming languages like
Java, C++, etc.
Structure:
• Each object contains attributes (data) and methods (functions).
• Supports inheritance, encapsulation, and polymorphism.

An object-oriented data model consists of the following basic object-

oriented concepts:

1. Object: The real-world entities and situations are represented as objects in

the Object-oriented database model.

2. Attributes and Methods: Every object has certain characteristics. These are
represented using Attributes. The behaviour of the objects is represented using
Methods.

3. Class: Similar attributes and methods are grouped together using a class. An
object can be called as an instance of the class.

P a g e 20 | 100
 DATABASE MANAGEMENT SYSTEMS

4. Inheritance: A new class can be derived from the original class. The derived
class contains attributes and methods of the original class as well as its own

Advantages:
• Supports complex data types (e.g., images, audio, video).
• Good for multimedia, CAD/CAM, AI applications.
• Closer to real-world modelling.

Disadvantages:
• Complex to implement.
• Not widely supported like relational databases.
• Slower performance for large datasets.

Database Design – Keys

In database design, keys are used to uniquely identify rows (records) in a table
and define relationships between tables.
They are fundamental to maintaining data integrity, eliminating redundancy,
and enforcing uniqueness.

Types of Keys in DBMS

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Composite Key

1. Primary Key
Definition:
A Primary Key is a column or group of columns that uniquely identifies each
row in a table.
It cannot be NULL and must be unique.
Rules:
• Only one primary key per table.
• Cannot have duplicate or NULL values.

Example:
P a g e 21 | 100
 DATABASE MANAGEMENT SYSTEMS

STUDENT Table
StudentID (PK) Name Age
101 Rohan 20
102 Meena 21
Here, StudentID is the Primary Key because it uniquely identifies each student.

2. Candidate Key
Definition:
A Candidate Key is any column or set of columns that can uniquely identify
rows in a table.
Among all candidate keys, one is chosen as the primary key.
Characteristics:
• All primary keys are candidate keys.
• A table can have multiple candidate keys.
• Candidate keys are minimal (no extra attribute).

Example:
EMPLOYEE Table
EmpID Email Mobile
E001 john@[Link] 9876543210
E002 jane@[Link] 9876543222
Candidate keys:
• EmpID
• Email
• Mobile
(Any of these can uniquely identify a record)

3. Super Key
Definition:
A Super Key is any set of attributes (columns) that can uniquely identify a
row.
It may include extra attributes not necessary for uniqueness.
Difference with Candidate Key:
• Super Key = Candidate Key + Extra columns.
• Candidate Key is a minimal super key.

Example:
P a g e 22 | 100
 DATABASE MANAGEMENT SYSTEMS

In the EMPLOYEE Table:

• {EmpID}, {Email}, {EmpID, Name} are all super keys.
• But only {EmpID} and {Email} are candidate keys (minimal).

4. Foreign Key
Definition:
A Foreign Key is a column (or group of columns) in one table that refers to the
primary key of another table.
Purpose:
• Creates a relationship between two tables.
• Ensures referential integrity – i.e., values in the foreign key must match
values in the referenced primary key.
Example:
DEPARTMENT Table
DeptID (PK) DeptName
1 IT
2 HR
EMPLOYEE Table
EmpID Name DeptID (FK)
101 John 1
102 Alia 2
• DeptID in the EMPLOYEE table is a foreign key referencing the
DEPARTMENT table.

5. Composite Key
Definition:
A Composite Key is a primary key made up of two or more columns.
Together, these columns uniquely identify a record, but individually they
may not.
Example:
ENROLLMENT Table
StudentID CourseID Grade
101 C101 A
P a g e 23 | 100
 DATABASE MANAGEMENT SYSTEMS

StudentID CourseID Grade

101 C102 B
102 C101 A+
• Neither StudentID nor CourseID alone is unique.
• Together, (StudentID, CourseID) forms a Composite Primary Key.

KEYS (Extended Types)

Apart from the standard keys (Primary, Candidate, etc.), some additional
important keys are:
1. Alternate Key
Definition:
An Alternate Key is any candidate key that is not chosen as the primary
key.
Example:
STUDENT Table
RollNo (PK) Email (Alternate Key) Phone
101 john@[Link] 9876543210
102 jane@[Link] 9876543222
• RollNo is chosen as the Primary Key.
• Email is also unique and could have been a primary key — so it is called
an Alternate Key.

2. Unique Key
Definition:
A Unique Key constraint ensures that all values in a column or a combination
of columns are unique across the table.
• Allows one NULL value (unlike Primary Key).
• Can be applied to multiple columns.

Example: SQL
CREATE TABLE Employee (

P a g e 24 | 100
 DATABASE MANAGEMENT SYSTEMS

EmpID INT PRIMARY KEY,

Email VARCHAR (100) UNIQUE
);
• Here, Email must be unique, but it can be NULL for one employee.

3. Surrogate Key
Definition:
A Surrogate Key is a system-generated unique identifier for a record,
typically an auto-incrementing number.
• Not derived from application data.
• Has no business meaning; used internally.

Example:
EmpID (Surrogate Key) Name NationalID
1 Raj IN123456
2 Meera IN654321
• EmpID is a surrogate key (auto-incremented).
• NationalID is business data but not used as the primary key.

TABLE CONSTRAINTS
Constraints are rules applied on table columns to enforce data integrity.

1. PRIMARY KEY Constraint

• Uniquely identifies each record.
• Cannot be NULL.
• One per table.

SQL

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(100)
);

2. FOREIGN KEY Constraint

P a g e 25 | 100
 DATABASE MANAGEMENT SYSTEMS

• Enforces a relationship between two tables.

• Ensures referential integrity.
• Must match a Primary Key in the referenced table.
SQL
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Student (RollNo)
);

3. NOT NULL Constraint

• Prevents NULL values in a column.
• Ensures that the field must be filled.
sql
CREATE TABLE Product (
ProductID INT,
ProductName VARCHAR (100) NOT NULL
);

4. UNIQUE Constraint
• Ensures all values in the column are unique.
• Allows only one NULL.
sql
CREATE TABLE Users (
UserID INT,
Email VARCHAR (100) UNIQUE
);

5. CHECK Constraint
• Ensures values meet a specific condition.
• Used to validate data input.
sql
CREATE TABLE Account (
AccNo INT,
Balance DECIMAL CHECK (Balance >= 0)
);
P a g e 26 | 100
 DATABASE MANAGEMENT SYSTEMS

• This prevents the insertion of a negative balance.

Introduction of ER Model

The Entity-Relationship Model (ER Model) is a conceptual model for designing

a database. This model represents the logical structure of a database, including
entities, their attributes and relationships between them.
• Entity: An object that is stored as data such
as Student, Course or Company.
• Attribute: Properties that describes an entity such
as StudentID, CourseName, or EmployeeEmail.
• Relationship: A connection between entities such as "a student enrolls in
a Course".

The graphical representation of this model is called an Entity-Relation Diagram

(ERD).
Why Use ER Diagrams In DBMS?
• ER diagrams represent the E-R model in a database, making them easy to
convert into relations (tables).
• These diagrams serve the purpose of real-world modeling of objects
which makes them intently useful.
• Unlike technical schemas, ER diagrams require no technical knowledge
of the underlying DBMS used.
• They visually model data and its relationships, making complex systems
easier to understand.

Symbols Used in ER Model

ER Model is used to model the logical view of the system from a data
perspective which consists of these symbols:

P a g e 27 | 100
 DATABASE MANAGEMENT SYSTEMS

• Rectangles: Rectangles represent entities in the ER Model.

• Ellipses: Ellipses represent attributes in the ER Model.
• Diamond: Diamonds represent relationships among Entities.
• Lines: Lines represent attributes to entities and entity sets with other
relationship types.
• Double Ellipse: Double ellipses represent multi-valued Attributes, such
as a student's multiple phone numbers
• Double Rectangle: Represents weak entities, which depend on other
entities for identification.

Entities
Definition:
An Entity is a real-world object or concept that can be clearly identified and
stored in a database.
Entities can be:
• Physical objects: Student, Car, Book
• Conceptual objects: Course, Department, Company

Types of Entities:
• Strong Entity: Exists independently and has a primary key
Example: Student, Employee
• Weak Entity: Cannot exist without a related strong entity
Example: OrderItem (dependent on Order)

Attribute

P a g e 28 | 100
 DATABASE MANAGEMENT SYSTEMS

What is an Attribute?
In the Entity-Relationship (ER) Model, an attribute is a property or
characteristic that describes an entity.
• An entity is a real-world object (like a Student, Employee, Book, etc.)
• Attributes store data values related to the entity.

Example:
For the entity Student, typical attributes might be:
• Roll Number
• Name
• Date of Birth
• Email
• Phone Number

Types of Attributes
Attributes are categorized based on their structure, value, and how they relate to
the entity. The main types are:

1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the
key attribute. For example, Roll_No will be unique for each student. In ER
diagram, the key attribute is represented by an oval with an underline.

Key Attribute
2. Composite Attribute
An attribute composed of many other attributes is called a composite attribute.
For example, the Address attribute of the student Entity type consists of Street,
City, State, and Country. In ER diagram, the composite attribute is represented
by an oval comprising of ovals.

P a g e 29 | 100
 DATABASE MANAGEMENT SYSTEMS

Composite Attribute
3. Multivalued Attribute
An attribute consisting of more than one value for a given entity. For example,
Phone_No (can be more than one for a given student). In ER diagram, a
multivalued attribute is represented by a double oval.

Multivalued Attribute
4. Derived Attribute
An attribute that can be derived from other attributes of the entity type is known
as a derived attribute. e.g.; Age (can be derived from DOB). In ER diagram, the
derived attribute is represented by a dashed oval.

Derived Attribute
The Complete Entity Type Student with its Attributes can be represented as:

P a g e 30 | 100
 DATABASE MANAGEMENT SYSTEMS

22

Entity and Attributes

P a g e 31 | 100
 DATABASE MANAGEMENT SYSTEMS

Introduction to Relational Model and Schema

1. What is the Relational Model?
The Relational Model is a foundational concept in database management
systems (DBMS), introduced by E.F. Codd in 1970. It organizes data into
tables (called relations) which are easy to understand and use.
Each relation (or table) consists of:
• Rows (called tuples) – represent records
• Columns (called attributes) – represent data fields

What is Schema?
A schema is the blueprint or structure that defines how data is organized and
stored in a database. It outlines the tables, fields, relationships, views, indexes,
and other elements within the database. The schema defines the logical view of
the entire database and specifies the rules that govern the data, including its
types, constraints, and relationships.

Types of Database Schemas

Physical Database Schema
• A physical schema defines how data is stored in the storage system,
including the arrangement of files, indices and other storage structures. It
specifies the actual code and syntax needed to create the database
structure. Essentially, it determines where and how the data is stored in
the physical storage medium.
• The database administrator decides the storage locations and organization
of data within the storage blocks. This schema represents the lowest level
of abstraction

P a g e 32 | 100
 DATABASE MANAGEMENT SYSTEMS

Logical Database Schema

• A logical database schema defines the logical structure of the data,
including tables, views, relationships, and integrity constraints. It
describes how data is organized in tables and how the attributes of these
tables are connected. The logical schema ensures that the data is stored in
an organized manner, while maintaining data integrity.
• Using Entity-Relationship (ER) modelling, the logical schema outlines
the relationships between different data components. It also defines
integrity constraints to ensure the quality of data during insertion and
updates.
• This schema represents a higher level of abstraction compared to the
physical schema, focusing on logical constraints and how the data is
structured, without dealing with the physical storage details.

3-Tier Schema Architecture in DBMS

In DBMS, the 3-Tier Schema Architecture refers to how the data is logically and
physically organized in three layers:

P a g e 33 | 100
 DATABASE MANAGEMENT SYSTEMS

1. Physical Level

physical level of a database describes how the data is being stored in secondary
storage devices like disks and tapes and also gives insights on additional storage
details.

2. Conceptual Level

At conceptual level, data is represented in the form of various database tables.

For Example, STUDENT database may contain STUDENT and COURSE
tables which will be visible to users but users are unaware of their storage.
Also referred as logical schema, it describes what kind of data is to be stored in
the database.

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

P a g e 34 | 100
 DATABASE MANAGEMENT SYSTEMS

UNIT-2

Introduction of Relational Algebra in DBMS

Relational Algebra is a formal language used to query and manipulate

relational databases, consisting of a set of operations
like selection, projection, union, and join. It provides a mathematical
framework for querying databases, ensuring efficient data retrieval and
manipulation. Relational algebra serves as the mathematical foundation for
query SQL
Relational algebra simplifies the process of querying databases and makes it
easier to understand and optimize query execution for better performance. It is
essential for learning SQL because SQL queries are based on relational algebra
operations, enabling users to retrieve data effectively.
Key Concepts in Relational Algebra
Before explaining relational algebra operations, let's define some fundamental
concepts:
1. Relations: In relational algebra, a relation is a table that consists of rows and
columns, representing data in a structured format. Each relation has a unique
name and is made up of tuples.
2. Tuples: A tuple is a single row in a relation, which contains a set of values for
each attribute. It represents a single data entry or record in a relational table.
3. Attributes: Attributes are the columns in a relation, each representing a
specific characteristic or property of the data. For example, in a "Students"
relation, attributes could be "Name", "Age", and "Grade".
4. Domains: A domain is the set of possible values that an attribute can have. It
defines the type of data that can be stored in each column of a relation, such as
integers, strings, or dates.
Basic Operators in Relational Algebra
Relational algebra consists of various basic operators that help us to fetch and
manipulate data from relational tables in the database to perform certain
operations on relational data. Basic operators are fundamental operations that
include selection (σ), projection (π), union (U), set difference (−), Cartesian
product (×), and rename (ρ).

P a g e 35 | 100
 DATABASE MANAGEMENT SYSTEMS

1. Selection(σ)
The Selection Operation is basically used to filter out rows from a given table
based on certain given condition. It basically allows us to retrieve only those
rows that match the condition as per condition passed during SQL Query.
Example: If we have a relation R with attributes A, B, and C, and we want to
select tuples where C > 3, we write:

A B C

1 2 4

2 2 3

3 2 3

4 3 4

σ(c>3)(R) will select the tuples which have c more than 3.

P a g e 36 | 100
 DATABASE MANAGEMENT SYSTEMS

Output:
A B C

1 2 4

4 3 4

Explanation: The selection operation only filters rows but does not display or
change their order. The projection operator is used for displaying specific
columns.

2. Projection(π)
While Selection operation works on rows, similarly projection operation of
relational algebra works on columns. It basically allows us to pick specific
columns from a given relational table based on the given condition and ignoring
all the other remaining columns.
Example: Suppose we want columns B and C from Relation R.

π(B,C)(R) will show following columns.

Output:
B C

2 4

2 3

3 4

Explanation: By Default, projection operation removes duplicate values.

SET OPERATIONS

3. Union(U)
The Union Operator is basically used to combine the results of two queries into
a single result. The only condition is that both queries must return same number
P a g e 37 | 100
 DATABASE MANAGEMENT SYSTEMS

of columns with same data types. Union operation in relational algebra is the
same as union operation in set theory.
Example: Consider the following table of Students having different optional
subjects in their course.
FRENCH
Student_Name Roll_Number

Ram 01

Mohan 02

Vivek 13

Geeta 17

GERMAN
Student_Name Roll_Number

Vivek 13

Geeta 17

Shyam 21

Rohan 25

If FRENCH and GERMAN relations represent student names in two subjects,

we can combine their student names as follows:
π(Student_Name)(FRENCH) U π(Student_Name)(GERMAN)
Output:
Student_Name

Ram

Mohan

P a g e 38 | 100
 DATABASE MANAGEMENT SYSTEMS

Student_Name

Vivek

Geeta

Shyam

Rohan

Explanation: The only constraint in the union of two relations is that both
relations must have the same set of Attributes.

4. Intersection (∩)
The Intersection Operator is used to fetch common tuples that exist in the
result of both queries. In relational algebra, the intersection operation follows
the same rules as in set theory.
The condition is that both relations must have the same number of
columns and the same data types (i.e., they must be union-compatible).
Example:
Consider the following tables representing students who have chosen different
optional subjects:
FRENCH

Student_Name Roll_Number

Ram 01

Mohan 02

Vivek 13

Geeta 17

P a g e 39 | 100
 DATABASE MANAGEMENT SYSTEMS

GERMAN

Student_Name Roll_Number

Vivek 13

Geeta 17

Shyam 21

Rohan 25

If FRENCH and GERMAN relations represent student names in two subjects,

we can find students common to both using:

π(Student_Name)(FRENCH) ∩ π(Student_Name)(GERMAN)
Output:
Student_Name

Vivek

Geeta

Explanation:
• The intersection returns only those student names that appear in both
FRENCH and GERMAN relations.
• Like union, intersection also requires the same set of attributes in both
relations (here, Student_Name).

P a g e 40 | 100
 DATABASE MANAGEMENT SYSTEMS

5. Set Difference(-)
Set difference basically provides the rows that are present in one table, but not
in another tables. Set Difference in relational algebra is the same set difference
operation as in set theory.
Example: To find students enrolled only in FRENCH but not in GERMAN,
we write:
π(Student_Name)(FRENCH) - π(Student_Name)(GERMAN)
Student_Name

Ram

Mohan

Explanation: The only constraint in the Set Difference between two relations is
that both relations must have the same set of Attributes.
6. Rename(ρ)
Rename operator basically allows you to give a temporary name to a specific
relational table or to its columns. It is very useful when we want to avoid
ambiguity, especially in complex Queries. Rename is a unary operation used for
renaming attributes of a relation.
Example: We can rename an attribute B in relation R to D
A B C

1 2 4

2 2 3

3 2 3

4 3 4

ρ(D/B)R will rename the attribute 'B' of the relation by 'D".

Output Table:

P a g e 41 | 100
 DATABASE MANAGEMENT SYSTEMS

A D C

1 2 4

2 2 3

3 2 3

4 3 4

7. Cartesian Product(X)
The Cartesian product combines every row of one table with every row of
another table, producing all the possible combination. It's mostly used as a
precursor to more complex operation like joins. Let’s say A and B, so the cross
product between A X B will result in all the attributes of A followed by each
attribute of B. Each record of A will pair with every record of B.
Relation A:
Name Age Sex

Ram 14 M

Sona 15 F

Kim 20 M

Relation B:
ID Course

1 DS

2 DBMS

Output: If relation A has 3 rows and relation B has 2 rows, the Cartesian
product A × B will result in 6 rows.

P a g e 42 | 100
 DATABASE MANAGEMENT SYSTEMS

Name Age Sex ID Course

Ram 14 M 1 DS

Ram 14 M 2 DBMS

Sona 15 F 1 DS

Sona 15 F 2 DBMS

Kim 20 M 1 DS

Kim 20 M 2 DBMS

Explanation: If A has 'n' tuples and B has 'm' tuples then A X B will have 'n*m'
tuples.

Derived Operators in Relational Algebra

Derived operators are built using basic operators and include operations like
join, intersection, and division. These operators help perform more complex
queries by combining basic operations to meet specific data retrieval needs.
1. Join Operators
Join operations in relational algebra combine data from two or more relations
based on a related attribute, allowing for more complex queries and data
retrieval. Different types of joins include:
Inner Join
An inner join combines rows from two relations based on a matching condition
and only returns rows where there is a match in both relations. If a record in one
relation doesn't have a corresponding match in the other, it is excluded from the
result. This is the most common type of join.
• Conditional Join: A conditional join is an inner join where the matching
condition can involve any comparison operator like equals (=), greater
than (>),
etc. Example: Joining Employees and Departments on DepartmentID wh

P a g e 43 | 100
 DATABASE MANAGEMENT SYSTEMS

ere Salary > 50000 will return employees in departments with a salary
greater than 50,000
• Equi Join: An equi join is a type of conditional join where the condition
is specifically equality (=) between columns from both
relations. Example: Joining Customers and Orders on CustomerID where
both relations have this column, returning only matching records.
• Natural Join: A natural join automatically combines relations based on
columns with the same name and type, removing duplicate columns in the
result. It’s a more efficient way of
joining. Example: Joining Students and Enrollments where StudentID is
common in both, and the result contains only unique columns.
Outer Join
An outer join returns all rows from one relation, and the matching rows from
the other relation. If there is no match, the result will still include all rows from
the outer relation with NULL values in the columns from the unmatched
relation.
• Left Outer Join: A left outer join returns all rows from the left relation
and the matching rows from the right relation. If there is no match, the
result will include NULL values for the right relation’s
attributes. Example: Joining Employees with Departments using a left
outer join ensures all employees are listed, even those who aren't assigned
to any department, with NULL values for the department columns.
• Right Outer Join: A right outer join returns all rows from the right
relation and the matching rows from the left relation. If no match exists,
the left relation's columns will
contain NULL values. Example: Joining Departments with Employees u
sing a right outer join includes all departments, even those with no
employees assigned, filling unmatched employee columns with NULL.
• Full Outer Join: A full outer join returns all rows when there is a match
in either the left or right relation. If a row from one relation does not have
a match in the other, NULL values are included for the missing
side. Example: Joining Customers and Orders using a full outer join will
return all customers and orders, even if there’s no corresponding order for
a customer or no customer for an order.

P a g e 44 | 100
 DATABASE MANAGEMENT SYSTEMS

Division (÷)
The Division Operator is used to find tuples in one relation that are related to
all tuples in another relation. It’s typically used for "for all" queries.
Student_Course (Dividend Table):
Student_ID Course_ID

101 C1

101 C2

102 C1

103 C1

103 C2

Course (Divisor Table):

Course_ID

C1

C2

Example: Query is to find students who are enrolled in all courses listed in
the Course table. In this case, students must be enrolled in both C1 and C2.
Student_Course(Student_ID, Course_ID)÷ Course(Course_ID)
Output:
Student_ID

101

103

P a g e 45 | 100
 DATABASE MANAGEMENT SYSTEMS

Relational Calculus
Relational calculus is a non-procedural query language used in the context of
relational algebra. It focuses on what data to retrieve, rather than how to
retrieve it, making it different from relational algebra, which is procedural.
In relational calculus, queries are expressed using logical formulas that
describe the desired result, without specifying the exact steps to get there.
There are two types of Relational Calculus
1. Tuple Relational Calculus(TRC)
2. Domain Relational Calculus(DRC)

Tuple Relational Calculus (TRC) is a non-procedural query language used

in relational database management systems (RDBMS) to retrieve data from
tables. TRC is based on the concept of tuples, which are ordered sets of
attribute values that represent a single row or record in a database table.
Syntax: The basic syntax of TRC is as follows:
{ t | P(t) }
where t is a tuple variable and P(t) is a logical formula that describes the
conditions that the tuples in the result must satisfy. The curly braces {} are
used to indicate that the expression is a set of tuples.

For example, let's say we have a table called "Employees" with the
following attributes:

Employee ID

Name

Salary

Department ID

To retrieve the names of all employees who earn more than $50,000 per year,
we can use the following TRC query:
{ t | Employees(t) ∧ [Link] > 50000 }

P a g e 46 | 100
 DATABASE MANAGEMENT SYSTEMS

Domain Relational Calculus (DRC)

Domain Relational Calculus is similar to Tuple Relational Calculus, where it
makes a list of the attributes that are to be chosen from the relations as per the
conditions.
{<a1,a2,a3,.....an> | P(a1,a2,a3,.....an)}
where a1,a2,...an are the attributes of the relation and P is the condition.

Tuple Relational Calculus Examples

Table Customer
Customer name Street City

Saurabh A7 Patiala

Mehak B6 Jalandhar

Sumiti D9 Ludhiana

Ria A5 Patiala

Table Branch
Branch name Branch City

ABC Patiala

DEF Ludhiana

GHI Jalandhar

Table Account
Account number Branch name Balance

1111 ABC 50000

1112 DEF 10000

P a g e 47 | 100
 DATABASE MANAGEMENT SYSTEMS

Account number Branch name Balance

1113 GHI 9000

1114 ABC 7000

Table Loan
Loan number Branch name Amount

L33 ABC 10000

L35 DEF 15000

L49 GHI 9000

L98 DEF 65000

Table Borrower
Customer name Loan number

Saurabh L33

Mehak L49

Ria L98

Table Depositor
Customer name Account number

Saurabh 1111

Mehak 1113

Suniti 1114

P a g e 48 | 100
 DATABASE MANAGEMENT SYSTEMS

Example 1: Find the loan number, branch, and amount of loans greater than or
equal to 10000 amount.
{t| t ∈ loan ∧ t[amount]>=10000}
Resulting relation:
Loan number Branch name Amount

L33 ABC 10000

L35 DEF 15000

L98 DEF 65000

In the above query, t[amount] is known as a tuple variable.

Example 2: Find the loan number for each loan of an amount greater or equal
to 10000.
{t| ∃ s ∈ loan(t[loan number] = s[loan number]
∧ s[amount]>=10000)}
Resulting relation:
Loan number

L33

L35

L98

SQL Commands

SQL commands are the fundamental building blocks for communicating with
a database management system (DBMS). It is used to interact with the database
with some operations. It is also used to perform specific tasks, functions,
and queries of data. SQL can perform various tasks like creating a table, adding
data to tables, dropping the table, modifying the table, set permission for users.
SQL Commands are mainly categorized into five categories:
• DDL – Data Definition Language

P a g e 49 | 100
 DATABASE MANAGEMENT SYSTEMS

• DQL – Data Query Language

• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL - Transaction Control Language

1. DDL - Data Definition Language

DDL or Data Definition Language actually consists of the SQL commands that
can be used for defining, altering and deleting database structures such as tables,
indexes and schemas. It simply deals with descriptions of the database schema
and is used to create and modify the structure of database objects in the database
Common DDL Commands
Command Description Syntax

Create database or its CREATE TABLE table_name

CREATE objects (table, index, (column1 data_type, column2
function, views, store data_type, ...);

P a g e 50 | 100
 DATABASE MANAGEMENT SYSTEMS

Command Description Syntax

procedure, and
triggers)

Delete objects from

DROP DROP TABLE table_name;
the database

ALTER TABLE table_name ADD

Alter the structure of
ALTER COLUMN column_name
the database
data_type;

Remove all records

from a table,
TRUNCATE including all spaces TRUNCATE TABLE table_name;
allocated for the
records are removed

Add comments to the COMMENT 'comment_text' ON

COMMENT
data dictionary TABLE table_name;

Rename an object RENAME TABLE

RENAME existing in the old_table_name TO
database new_table_name;

Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for
employee ID, first name, last name and hire date.

P a g e 51 | 100
 DATABASE MANAGEMENT SYSTEMS

DML - Data Manipulation Language

The SQL commands that deal with the manipulation of data present in the
database belong to DML or Data Manipulation Language and this includes most
of the SQL statements. It is the component of the SQL statement that controls
access to data and to the database. Basically, DCL statements are grouped with
DML statements.
Common DML Commands
Command Description Syntax

Insert data into INSERT INTO table_name (column1,

INSERT
a table column2, ...) VALUES (value1, value2, ...);

Update existing UPDATE table_name SET column1 =

UPDATE data within a value1, column2 = value2 WHERE
table condition;

Delete records
DELETE FROM table_name WHERE
DELETE from a database
condition;
table

Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency

Call a PL/SQL
CALL or JAVA CALL procedure_name(arguments);
subprogram

Describe the
EXPLAIN EXPLAIN PLAN FOR SELECT * FROM
access path to
PLAN table_name;
data

Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into the employees table with the first name
'Jane', last name 'Smith' and department 'HR'.

P a g e 52 | 100
 DATABASE MANAGEMENT SYSTEMS

UPDATE Command
This command is used to alter existing table records. Within a table, it modifies
data from one or more records. This command is used to alter the data which is
already present in a table.

Syntax:
UPDATE <table_name>
SET <column_name = value>
WHERE condition;

Example:
UPDATE students
SET due_fees = 20000
WHERE stu_name = 'Mini';

DELETE Command
It deletes all archives from a table. This command is used to erase some or all of
the previous table's records. If we do not specify the 'WHERE' condition then
all the rows would be erased or deleted.

Syntax:
DELETE FROM <table_name>
WHERE <condition>;
Example:
DELETE FROM students
WHERE stu_id = '001';

Advantages of DML
• DML statements could alter the data that is contained or stored in the
database.
• It delivers effective human contact with the machine.
• User could specify what data is required.
• DML aims to have many different varieties and functionalities between
vendors providing databases.
Disadvantages of DML
• We cannot use DML to change the structure of the database.
• Limit table view i.e., it could conceal some columns in tables.

P a g e 53 | 100
 DATABASE MANAGEMENT SYSTEMS

• Access the data without having the data stored in the object.
• Unable to build or erase lists or sections using DML.

SQL Aggregate functions

SQL Aggregate Functions are used to perform calculations on a set of rows and
return a single value. These functions are particularly useful when we need to
summarize, analyze, or group large datasets in SQL databases. Whether you are
working with sales data, employee records or product inventories, aggregate
functions help us derive meaningful insights.
They are often used with the GROUP BY clause in SQL to summarize data for
each group. Commonly used aggregate functions include COUNT(), SUM(),
AVG(), MIN() and MAX(). In this article, we will explain the most commonly
used SQL aggregate functions, their syntax, practical examples, and how to
make the best use of them in your queries.

Key Features of SQL Aggregate Functions:

• Operate on groups of rows: They work on a set of rows and return a
single value.
• Ignore NULLs: Most aggregate functions ignore NULL values, except
for COUNT(*).
• Used with GROUP BY: To perform calculations on grouped data, you
often use aggregate functions with GROUP BY.
• Can be combined with other SQL clauses: Aggregate functions can be
used alongside HAVING, ORDER BY, and other SQL clauses to filter or
sort results.

Commonly Used SQL Aggregate Functions

Below are the most frequently used aggregate functions in SQL.
1. Count()
The COUNT() function returns the number of rows that match a given condition
or are present in a column.
• COUNT(*): Counts all rows.
• COUNT(column_name): Counts non-NULL values in the specified
column.
• COUNT(DISTINCT column_name): Counts unique non-NULL values
in the column.
P a g e 54 | 100
 DATABASE MANAGEMENT SYSTEMS

Examples:
-- Total number of records in the table
SELECT COUNT(*) AS TotalRecords FROM Employee;

-- Count of non-NULL salaries

SELECT COUNT(Salary) AS NonNullSalaries FROM Employee;

-- Count of unique non-NULL salaries

SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM Employee;

2. SUM()
The SUM() function calculates the total sum of a numeric column.
SUM(column_name): Returns the total sum of all non-NULL values in a
column.
Examples:
-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Calculate the sum of unique salaries

SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM Employee;
3. AVG()
The AVG() function calculates the average of a numeric column. It divides the
sum of the column by the number of non-NULL rows.
AVG(column_name): Returns the average of the non-NULL values in the
column.
Examples:
-- Calculate the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employee;

-- Average of distinct salaries

SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;
4. MIN() and MAX()
The MIN() and MAX() functions return the smallest and largest values,
respectively, from a column.
• MIN(column_name): Returns the minimum value.
• MAX(column_name): Returns the maximum value.

P a g e 55 | 100
 DATABASE MANAGEMENT SYSTEMS

Examples:
-- Find the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Find the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;

SQL - Logical Operators

SQL Logical Operators are essential tools used to test the truth of conditions
in SQL queries. They return Boolean values such as TRUE, FALSE,
or UNKNOWN, making them invaluable for filtering, retrieving,
or manipulating data. These operators allow developers to build complex
queries by combining, negating, or comparing conditions effectively.

What Are Logical Operators in SQL?

Logical operators in SQL are used to combine multiple conditions in a query
to control the flow of execution. They evaluate whether these conditions
are TRUE, FALSE, or NULL, assisting in refining query results effectively. By
using these operators, developers can retrieve highly specific data based on
given conditions.

We will use the following employee table throughout the examples. This table
represents employee details, including their unique ID, name, city,
and country.

P a g e 56 | 100
 DATABASE MANAGEMENT SYSTEMS

1. AND Operator
The AND operator is used to combine two or more conditions in an SQL
query. It returns records only when all conditions specified in the query
are true. This operator is commonly used when filtering data that must satisfy
multiple criteria simultaneously.

Example
Retrieve the records of employees from the employees table who are located
in 'Allahabad' and belong to 'India', ensuring that both conditions are met.
Query:

SELECT * FROM employee WHERE emp_city = 'Allahabad' AND

emp_country = 'India';
Output

2. IN Operator
The IN operator simplifies the process of checking if a value matches any
value in a list, making it more efficient and readable compared to using
multiple OR conditions. This operator is especially helpful when we need to
filter results based on multiple possible values for a given column, reducing
the complexity of the query.
Example
Retrieve the records of employees from the employee table who are located in
either 'Allahabad' or 'Patna'.
Query:
SELECT * FROM employee WHERE emp_city IN ('Allahabad', 'Patna');
Output

P a g e 57 | 100
 DATABASE MANAGEMENT SYSTEMS

3. NOT Operator
The NOT operator is used to reverse the result of a condition,
returning TRUE when the condition is FALSE. It is typically used to exclude
records that match a specific condition, making it useful for filtering out
unwanted data.
Example
Retrieve the records of employees from the employee table whose city names
do not start with the letter 'A'.
Query:
SELECT * FROM employee WHERE emp_city NOT LIKE 'A%';
Output

4. OR Operator
The OR operator combines multiple conditions in a SQL query and
returns TRUE if at least one of the conditions is satisfied. It is ideal for
situations where you want to retrieve records that meet any of several possible
conditions.
Example
Retrieve the records of employees from the employee table who are either
from 'Varanasi' or have 'India' as their country.
Query
SELECT * FROM employee WHERE emp_city = 'Varanasi' OR emp_country =
'India';
Output

P a g e 58 | 100
 DATABASE MANAGEMENT SYSTEMS

5. LIKE Operator
The LIKE operator in SQL is used in the WHERE clause to search for a
specified pattern in a column. It is particularly useful when we want to perform
pattern matching on string data. The LIKE operator works with two main
wildcards:
• %: Represents zero or more characters. It allows matching any sequence
of characters in the string.
• _: Represents exactly one character. It is used when you want to match a
specific number of characters at a given position.

Example
Retrieve the records of employees from the employee table whose city names
start with the letter 'P'.
Query:
SELECT * FROM employee WHERE emp_city LIKE 'P%';
Output

6. BETWEEN Operator
The BETWEEN operator in SQL allows us to test if a value or expression lies
within a specified range. The BETWEEN condition is inclusive, meaning it
includes both the lower and in the results. This operator is particularly useful
when we need to filter records based on a range of values, such as numerical
ranges, dates, or even text values.
Example
Retrieve the records of employees from the employee table
whose emp_id values fall within the range of 101 to 104 (inclusive).
Query:
SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;
Output

P a g e 59 | 100
 DATABASE MANAGEMENT SYSTEMS

7. ALL Operator
The ALL operator in SQL is used to compare a value to all values returned by a
subquery. It returns TRUE if the condition specified is TRUE for all values
retrieved by the subquery. The ALL operator is commonly used
with SELECT, WHERE, and HAVING clauses to ensure that a value satisfies
a condition when compared to a set of values.
Example
Retrieve the records of employees whose emp_id is equal to all emp_id values
in the employees table where the emp_city is 'Varanasi'.
Query:
SELECT * FROM employee WHERE emp_id = ALL
(SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');
Output

8. ANY Operator
The ANY operator in SQL is used to compare a value with the results of
a subquery. It returns TRUE if the value satisfies the condition with any of the
values returned by the subquery. This operator allows for greater flexibility
when you want to check if a value matches at least one of the results in a set of
values.
Example
Retrieve the records of employees whose emp_id matches any of
the emp_id values in the employees table where the emp_city is 'Varanasi'.
Query:
SELECT * FROM employee WHERE emp_id = ANY
(SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');

P a g e 60 | 100
 DATABASE MANAGEMENT SYSTEMS

Output

9. EXISTS Operator
The EXISTS operator in SQL is used to check whether a subquery returns any
rows. It evaluates to TRUE if the subquery results in one or more rows.
The EXISTS operator is typically used with SELECT, UPDATE, INSERT,
and DELETE statements to determine if any rows exist that meet a specified
condition. It is often used in correlated subqueries where the subquery
references columns from the outer query.
Example
Retrieve the names of employees from the employee table if there are any
employees in the employee table who are located in 'Patna'.
Query
SELECT emp_name FROM employee WHERE EXISTS
(SELECT emp_id FROM employee WHERE emp_city = 'Patna');
Output

10. SOME Operator

The SOME operator in SQL is used in conjunction with comparison operators
such as <, >, =, <=, etc., to compare a value with the results of a subquery. It
returns TRUE if the condition is met with any value returned by the subquery.
The SOME operator allows us to perform comparisons with any of the values
returned by a subquery, and it is particularly useful when we want to match a
value against a set of values rather than a single value.

P a g e 61 | 100
 DATABASE MANAGEMENT SYSTEMS

Example
Retrieve the records of employees from the employee table where the emp_id is
less than any of the emp_id values from employees located in 'Patna'.
Query:
SELECT * FROM employee WHERE emp_id < SOME
(SELECT emp_id FROM employee WHERE emp_city = 'Patna');
Output

What Are Predicates in SQL?

A predicate is a condition used in SQL statements (like SELECT, WHERE,
HAVING) to filter data. It evaluates to either TRUE, FALSE, or UNKNOWN
for each row.
Predicates are used to:
• Search specific data (WHERE clause)
• Filter groups (HAVING)
• Create logical conditions

Key SQL Predicates: LIKE, BETWEEN, DISTINCT, ALIAS

1. LIKE Predicate
Purpose: Used to search for a specified pattern in a column (mostly with
CHAR, VARCHAR).
Syntax:
SELECT * FROM table_name WHERE column_name LIKE pattern;

Wildcards:
• % – matches any sequence of characters (including none)
• _ – matches a single character

P a g e 62 | 100
 DATABASE MANAGEMENT SYSTEMS

Examples:
-- Names starting with 'A'
SELECT * FROM students WHERE name LIKE 'A%';

-- Names ending with 'n'

SELECT * FROM students WHERE name LIKE '%n';

-- Names where second character is 'a'

SELECT * FROM students WHERE name LIKE '_a%';

2. BETWEEN Predicate
Purpose: Filters data that lies within a specific range (inclusive).
Syntax:
SELECT * FROM table_name WHERE column_name BETWEEN value1
AND value2;
Examples:
-- Salaries between 30000 and 50000
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;

-- Dates between two specific days

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND
'2024-12-31';

3. DISTINCT Predicate
Purpose: Removes duplicate values from the result set.
Syntax:
SELECT DISTINCT column1, column2 FROM table_name;
Examples:
-- Unique department names
SELECT DISTINCT department FROM employees;

-- Unique combinations of department and location

SELECT DISTINCT department, location FROM employees;

P a g e 63 | 100
 DATABASE MANAGEMENT SYSTEMS

4. ALIAS (Not technically a predicate, but commonly grouped with them)

Purpose: Gives a temporary name to a column or table for easier reference or
improved readability.
Syntax:
SELECT column_name AS alias_name FROM table_name;
SELECT * FROM table_name AS alias_name;
Examples:
-- Column alias
SELECT first_name AS Name, salary AS Income FROM employees;

-- Table alias
SELECT [Link], d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = [Link];

1. GROUP BY – Grouping Rows Based on Column Values

• Purpose:
Used to group rows that have the same values in one or more columns. Often
used with aggregate functions like COUNT(), SUM(), AVG(), etc.
• Syntax:
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1;
• Example:
-- Total salary per department
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

2. HAVING – Filtering Grouped Data

• Purpose:
Used to filter grouped rows after applying GROUP BY. It works like WHERE
but for groups, not individual rows.
• Syntax:
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1
P a g e 64 | 100
 DATABASE MANAGEMENT SYSTEMS

HAVING condition;
Example:

-- Departments with total salary > 100000

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

3. ORDER BY – Sorting Results

• Purpose:
Used to sort the result set by one or more columns in ascending (ASC) or
descending (DESC) order.
• Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;

• Example:
-- List of employees sorted by salary (highest first)
SELECT name, salary
FROM employees
ORDER BY salary DESC;

4. LIMIT / TOP – Restricting Output

• Purpose:
Used to limit the number of rows returned by the query.
• LIMIT is used in MySQL, PostgreSQL, SQLite.
• TOP is used in SQL Server.
• Oracle uses ROWNUM or FETCH FIRST.

Syntax (MySQL):
SELECT * FROM table_name LIMIT 5;
Syntax (SQL Server):
SELECT TOP 5 * FROM table_name;

P a g e 65 | 100
 DATABASE MANAGEMENT SYSTEMS

Example:
-- Top 3 highest-paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Functional Dependencies

What is a Functional Dependency?

A Functional Dependency (FD) occurs when one attribute uniquely
determines another attribute in a relation.
Definition: In a relation R, attribute B is functionally dependent on attribute A
(written as A → B) if, for every valid instance of A, that value of A uniquely
determines the value of B.
Notation: A → B
Example:
Consider a relation (table):
Roll_No Name Department
101 Alice CSE
102 Bob ECE
103 Alice CSE

In this case:
• Roll_No → Name (Each roll number uniquely identifies a name)
• Roll_No → Department (Each roll number belongs to one department)
• But Name → Roll_No is NOT true (Because "Alice" can have multiple
roll numbers)

Types of Functional Dependencies in DBMS

1. Trivial Functional Dependency

Definition:
A functional dependency is trivial if the right-hand side is a subset of the left-
hand side.

P a g e 66 | 100
 DATABASE MANAGEMENT SYSTEMS

Notation:
If X → Y, and Y ⊆ X, then it is a trivial dependency.
Example:
• A→A
• AB → A
These are always true for any table.

2. Non-Trivial Functional Dependency

Definition:
A functional dependency is non-trivial if the right-hand side is not a subset of
the left-hand side.
Notation:
If X → Y, and Y ⊄ X, then it is non-trivial.
Example:
• RollNo → Name
(Right side "Name" is not part of left side "RollNo")

3. Full Functional Dependency

Definition:
A functional dependency is fully dependent if removal of any attribute from the
left-hand side makes the dependency invalid.
Notation:
If AB → C, but A → C or B → C does not hold, then it's a full functional
dependency.
Example:
• StudentID, CourseCode → Grade
The grade depends on both student and course, not just one.

4. Partial Dependency
Definition:
A functional dependency is partial if a non-prime attribute is functionally
dependent on part of a candidate key or composite primary key.
Occurs in:
2NF normalization.
Example:

P a g e 67 | 100
 DATABASE MANAGEMENT SYSTEMS

• If RollNo, SubjectCode → Marks,

and RollNo → StudentName
→ Then StudentName is partially dependent on the key.

5. Transitive Dependency
Definition: A transitive dependency is a type of functional dependency
which happens when it is indirectly formed by two functional dependencies.
A functional dependency X → Z is transitive if:
• X → Y and
• Y → Z,
Then X → Z is transitive.
Occurs in:
3NF normalization.
Example:
• EmpID → DeptID
• DeptID → DeptName
→ So, EmpID → DeptName (transitive dependency)

6. Multivalued Dependency (MVD)

Definition:
If attribute Y is multivalued dependent on X (denoted as X →→ Y), then for a
single value of X, there are multiple independent values of Y.
Occurs in:
4NF normalization.
Example:
• A person can have multiple phone numbers and multiple email
addresses.
So: Person →→ Phone and Person →→ Email

7. Join Dependency
Definition:
A join dependency occurs when a table can be split into two or more relations
and reconstructed by joining them without data loss.
Occurs in:
5NF (Project-Join Normal Form)
Example:

P a g e 68 | 100
 DATABASE MANAGEMENT SYSTEMS

• Relation R(A, B, C) is split into R1(A, B) and R2(B, C)

If you can reconstruct original R by joining R1 and R2, then it has a join
dependency.

Armstrong's Axioms

What are Armstrong's Axioms?

Armstrong’s Axioms are a set of rules that are sound and complete, meaning:
• They only derive correct functional dependencies.
• They can derive all possible correct FDs (closure).
They are used for:
 Testing FD implications
 Computing attribute closure
 Deriving canonical covers
 Aiding normalization

• Axiom of Reflexivity: If A is a set of attributes and B is a subset of A,

then A holds B. If B⊆A then A→B. This property is trivial property.
• Axiom of Augmentation: If A→B holds and Y is the attribute set,
then AY→BY also holds. That is adding attributes to dependencies, does
not change the basic dependencies. If A→B, then AC→BC for any C.
• Axiom of Transitivity: Same as the transitive rule in algebra,
if A→B holds and B→C holds, then A→C also holds. A→B is called A
functionally which determines B. If X→Y and Y→Z, then X→Z.

P a g e 69 | 100
 DATABASE MANAGEMENT SYSTEMS

1. Reflexivity: Since any set of attributes determines its subset, we can

immediately infer the following:
• {A} → {A} (A set always determines itself).
• {B} → {B}.
• {A, C} → {A}.
2. Augmentation: If we know that {A} → {B}, we can add the same attribute
(or set of attributes) to both sides:
• From {A} → {B}, we can augment both sides with {C}: {A, C} → {B, C}.
• From {B} → {C}, we can augment both sides with {A}: {A, B} → {C, B}.
3. Transitivity: If we know {A} → {B} and {B} → {C}, we can infer that:
• {A} → {C} (Using transitivity: {A} → {B} and {B} → {C}).
Although Armstrong's axioms are sound and complete, there are additional rules
for functional dependencies that are derived from them. These rules are
introduced to simplify operations and make the process easier.
Secondary Rules
These rules can be derived from the above axioms.
• Union: If A→B holds and A→C holds, then A→BC holds.
If X→Y and X→Z then X→YZ.
• Composition: If A→B and X→Y hold, then AX→BY holds.
• Decomposition: If A→BC holds then A→B and A→C hold.
If X→YZ then X→Y and X→Z.
• Pseudo Transitivity: If A→B holds and BC→D holds,
then AC→D holds. If X→Y and YZ→W then XZ→W.
Properties of Functional Dependencies:
• Help define keys and relationships between attributes.
• Guide normalization of relational schemas.
• Used to eliminate redundancy.

Normal Forms in DBMS

What is Normalization in DBMS?
Normalization is a systematic approach to organize data within a database to
reduce redundancy and eliminate undesirable characteristics such as insertion,
update, and deletion anomalies. The process involves breaking down large
tables into smaller, well-structured ones and defining relationships between
them. This not only reduces the chances of storing duplicate data but also
improves the overall efficiency of the database.

P a g e 70 | 100
 DATABASE MANAGEMENT SYSTEMS

Why is Normalization Important?

• Reduces Data Redundancy: Duplicate data is stored efficiently, saving
disk space and reducing inconsistency.
• Improves Data Integrity: Ensures the accuracy and consistency of data
by organizing it in a structured manner.
• Simplifies Database Design: By following a clear structure, database
designs become easier to maintain and update.
• Optimizes Performance: Reduces the chance of anomalies and increases
the efficiency of database operations.
1. First Normal Form (1NF): Eliminating Duplicate Records
A table is in 1NF if it satisfies the following conditions:
• All columns contain atomic values (i.e., indivisible values).
• Each row is unique (i.e., no duplicate rows).
• Each column has a unique name.
• The order in which data is stored does not matter.
Example of 1NF Violation: If a table has a column "Phone Numbers" that
stores multiple phone numbers in a single cell, it violates 1NF. To bring it into
1NF, you need to separate phone numbers into individual rows.
Example 2: Violation Example (Not in 1NF):
Student Courses
Raj DBMS, OS

P a g e 71 | 100
 DATABASE MANAGEMENT SYSTEMS

In 1NF:
Student Course
Raj DBMS
Raj OS

2. Second Normal Form (2NF): Eliminating Partial Dependency

Definition:
A relation is in 2NF if:
1. It is already in 1NF, and
2. No partial dependency exists (i.e., no non-prime attribute depends on a
part of a composite primary key).
Applies only when the primary key is composite.
Example:
Before 2NF:
StudentID CourseCode StudentName
1 DB101 Aditi
1 OS102 Aditi

Here, StudentID + CourseCode is the composite key, but StudentName depends

only on StudentID, so it's a partial dependency.
After 2NF (decompose):
• Student(StudentID, StudentName)
• Enrollment(StudentID, CourseCode)

3. Third Normal Form (3NF): Eliminating Transitive Dependency

Definition:
A relation is in 3NF if:
1. It is in Second Normal Form (2NF)
2. No transitive dependency exists — i.e., every non-prime attribute is
only dependent on a candidate key, not on another non-prime attribute.

P a g e 72 | 100
 DATABASE MANAGEMENT SYSTEMS

Formal Rule:
For any functional dependency X → A, at least one of the following must be
true:
• A is a prime attribute (i.e., part of a candidate key)
• X is a super key

Example:
EmpID EmpName DeptID DeptName
1 Alice 10 CSE
2 Bob 20 ECE
FDs:
• EmpID → EmpName, DeptID
• DeptID → DeptName

EmpID is the primary key

DeptName is transitively dependent on EmpID via DeptID
So, this is not in 3NF
Convert to 3NF:
Split into two tables:
Employee Table
EmpID | EmpName | DeptID
Department Table
DeptID | DeptName
Now each non-key attribute depends only on a key, and no transitive
dependencies exist.

4. Boyce-Codd Normal Form (BCNF): The Strongest Form of 3NF

Definition:
A relation is in BCNF if:
• For every non-trivial functional dependency X → Y, X is a super key

Difference from 3NF:

BCNF is stricter than 3NF.
3NF allows certain dependencies if the right side is a prime attribute, but
BCNF does not.

P a g e 73 | 100
 DATABASE MANAGEMENT SYSTEMS

Example Where 3NF Holds but Not BCNF:

Student Course Instructor
Alice DBMS Rao
Bob DBMS Rao
FDs:
• Student, Course → Instructor
• Instructor → Course Instructor is not a key
Violates BCNF

Convert to BCNF:
Instructor Table
Instructor → Course
Enrolment Table
Student, Instructor
Now all FDs have super keys on LHS

Denormalization
Definition:
Denormalization is the process of combining normalized tables into fewer
tables to improve read performance at the cost of redundancy.
Why Denormalize?
Benefit Cost
Faster query performance Increased redundancy
Fewer joins Update anomalies possible
Simpler data retrieval Larger storage
Example:
After normalizing:
Employee Table
EmpID | Name | DeptID
Department Table
DeptID | DeptName

SIn denormalization, you might combine them back into:

Employee_Denormalized Table
EmpID | Name | DeptID | DeptName

P a g e 74 | 100
 DATABASE MANAGEMENT SYSTEMS

UNIT -3

ACID Properties:
In Database Management Systems (DBMS), ACID properties are the key
principles that ensure reliable transactions. They stand for:
1. Atomicity
• "All or nothing" rule: A transaction is treated as a single unit.
• Either all operations in the transaction are performed, or none are.
Example: In a banking transaction, if ₹1000 is transferred from Account A to
Account B:
• Deduction from A and addition to B both must happen.
• If any part fails, the whole transaction is rolled back.

2. Consistency
• A transaction must bring the database from one valid state to another.
• Ensures that all defined rules, constraints, and triggers are maintained.

 Before and after the transaction, the total money in the system remains
same.
 Example: If A = ₹1000 and B = ₹2000, total = ₹3000.
After transfer → A = ₹500, B = ₹2500, total still = ₹3000.

3. Isolation
• Transactions occur independently of one another.
• The intermediate state of a transaction is invisible to others.
• Example: If two people book the last movie ticket at the same time,
isolation ensures that only one transaction succeeds, preventing double-
booking.
Or
 If two people try to transfer money from the same account at the
same time, each transaction runs independently.
 One transaction will finish first, then the other executes — preventing
incorrect balances.

4. Durability
• Once a transaction is committed, it is permanently recorded in the
database, even if the system crashes.
P a g e 75 | 100
 DATABASE MANAGEMENT SYSTEMS

Transaction in DBMS

A transaction refers to a sequence of one or more operations (such as read,

write, update, or delete) performed on the database as a single logical unit of
work.
• A transaction ensures that either all the operations are successfully
executed (committed) or none of them take effect (rolled back).
• Transactions are designed to maintain the integrity, consistency and
reliability of the database, even in the case of system failures or
concurrent access.

Example: Let’s consider an online banking application:

Transaction: When a user performs a money transfer, several operations
occur, such as:
• Reading the account balance of the sender.
• Writing the deducted amount from the sender’s account.
• Writing the added amount to the recipient’s account.
In a transaction, all these steps should either complete successfully or, if any
error occurs, the database should rollback to its previous state, ensuring no
partial data is written to the system.

Operations of Transaction
A user can make different types of requests to access and modify the contents of
a database. So, we have different types of operations relating to a transaction.
They are discussed as follows:
1) Read(X)
A read operation is used to read the value of a particular database element X and
stores it in a temporary buffer in the main memory for further actions such as
displaying that value.
Example: For a banking system, when a user checks their balance, a Read
operation is performed on their account balance:
SELECT balance FROM accounts WHERE account_id = 'A123';
This updates the balance of the user's account after withdrawal.
2) Write(X)

P a g e 76 | 100
 DATABASE MANAGEMENT SYSTEMS

A write operation stores updated data from main memory back to the database.
It usually follows a read, where data is fetched, modified (e.g., arithmetic
changes), and then written back to save the updated value.
Example: For the banking system, if a user withdraws money,
a Write operation is performed after the balance is updated:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A123';
This updates the balance of th e user’s account after withdrawal.
3) Commit
This operation in transactions is used to maintain integrity in the database. Due
to some failure of power, hardware, or software, etc., a transaction might get
interrupted before all its operations are completed. This may cause ambiguity in
the database, i.e. it might get inconsistent before and after the transaction.
Example: After a successful money transfer in a banking system,
a Commit operation finalizes the transaction:
COMMIT;
Once the transaction is committed, the changes to the database are permanent,
and the transaction is considered successful.
4) Rollback
A rollback undoes all changes made by a transaction if an error occurs, restoring
the database to its last consistent state. It helps prevent data inconsistency and
ensures safety.
Example: Suppose during the money transfer process, the system encounters an
issue, like insufficient funds in the sender’s account. In that case, the transaction
is rolled back:
ROLLBACK;

Transaction Schedules
When multiple transaction requests are made at the same time, we need to
decide their order of execution. Thus, a transaction schedule can be defined as a
chronological order of execution of multiple transactions. Example: After a
successful transfer, the updated balance remains safe despite a power failure.
There are broadly two types of transaction schedules discussed as follows:
i) Serial Schedule
In a serial schedule, transactions execute one at a time, ensuring database
consistency but increasing waiting time and reducing system throughput. To
improve throughput while maintaining consistency, concurrent schedules with
strict rules are used, allowing safe simultaneous execution of transactions.
P a g e 77 | 100
 DATABASE MANAGEMENT SYSTEMS

ii) Non-Serial Schedule

Non-serial schedule is a type of transaction schedule where multiple
transactions are executed concurrently, interleaving their operations, instead of
running one after another. It improves system efficiency but requires
concurrency control to maintain database consistency.

Concurrency in DBMS
• In a DBMS, concurrency means that multiple users or transactions can
access the database at the same time.
• Example: In an online shopping system:
o One user is placing an order.
o Another is updating their profile.
o Another is browsing products.
All these transactions are running concurrently on the same
database.

Concurrent Execution can lead to various challenges:

1. Dirty Read
• Definition: One transaction reads uncommitted data from another.
• Example:
o T1: Updates a student's marks from 80 → 90 (but hasn’t committed
yet).
o T2: Reads the marks as 90.
o Later, T1 rolls back (cancels the update → value is back to 80).
o Now T2 has read wrong/dirty data (90) that never actually
existed permanently.

2. Lost Update
• Definition: Two transactions update the same data at the same time, and
one update overwrites the other.
• Example:
o Balance in account = ₹1000.
o T1: Withdraws ₹200 → plans to update balance to ₹800.
o T2: Deposits ₹500 → plans to update balance to ₹1500.
o If both read the balance as ₹1000 before updating, and then both
write:
▪ T1 writes 800, then

P a g e 78 | 100
 DATABASE MANAGEMENT SYSTEMS

▪ T2 writes 1500, overwriting T1’s update.

o Final balance = ₹1500, but correct balance should have been ₹1300
→ T1’s update got lost.

3. Inconsistent Read (Non-repeatable Read)

• Definition: A transaction reads the same data twice, but gets different
results because another transaction changed it in between.
• Example:
o T1: Reads product price = ₹100.
o Meanwhile, T2: Updates the product price → ₹120 and commits.
o T1 again reads the same product price → now gets ₹120.
o Within the same transaction, T1 saw two different values (100
and 120) → inconsistency.

Goal of concurrency in DBMS:

• To increase system performance by serving many users at once.
• To maintain consistency so that the database always remains correct even
if many operations are happening together.

2. Locking in DBMS
• Locking is a concurrency control technique in DBMS.
• It ensures that when one transaction is accessing or modifying a piece of
data, other transactions must wait until the lock is released.
Types of Locks:
1. Shared Lock (S-Lock)
o Allows multiple transactions to read the same data at the same
time.
o But no one can write until all readers are finished.
o Example: Many users checking their account balance.
2. Exclusive Lock (X-Lock)
o Allows only one transaction to read/write the data.
o Prevents others from accessing the same data until the lock is
released.
o Example: Only one user can update the account balance at a time.

P a g e 79 | 100
 DATABASE MANAGEMENT SYSTEMS

Crash Recovery

What is Crash Recovery?

Crash recovery in a Database Management System (DBMS) refers to the
process of restoring the database to a consistent state after an unexpected
failure, such as:
• System crash (hardware failure, OS crash, power failure)
• Transaction failure (logic errors, deadlocks)
• Media failure (disk crash, file corruption)

The goal is to ensure that the database continues to satisfy the ACID
properties, even after a crash.

Types of Failures Requiring Recovery

1. Transaction Failure
o A single transaction fails due to logical errors or deadlock.
o Recovery: Rollback only the failed transaction.
2. System Crash
o Loss of volatile memory (RAM), but disk data remains safe.
o Example: sudden power loss, OS crash.
o Recovery: Use logs to redo/undo transactions.
3. Disk Failure / Media Failure
o Permanent loss of data (e.g., disk head crash).
o Recovery: Restore from backup + redo committed transactions
using logs.
4. Application/Process Failure
o The program accessing the DB crashes, leaving incomplete
updates.
o Recovery: Rollback incomplete work.

Recovery Techniques
Crash recovery is based on logs (Write-Ahead Logging - WAL principle).
1. Write-Ahead Logging (WAL)
• Every change is first written to a log file before being applied to the
database.
• Ensures durability and recoverability.

P a g e 80 | 100
 DATABASE MANAGEMENT SYSTEMS

2. Log-Based Recovery
Logs keep track of:
• START T → transaction started
• UPDATE T, X, old_v alue, new_value → transaction modified data
• COMMIT T → transaction completed successfully
• ROLLBACK T → transaction was und one

On crash, the system checks the log and:

• Redo committed transactions (to ensure durability).
• Undo uncommitted transactions (to ensure atomicity).

3. Checkpointing
• A checkpoint is a snapshot of the database + log at a certain point.
• It reduces recovery time by marking a safe point from where recovery can
start.
• Example: Instead of scanning the entire log, recovery starts from the last
checkpoint.

4. Shadow Paging
• Instead of updating pages directly, DB creates a shadow copy.
• If the transaction commits → new copy replaces old copy.
• If it crashes → old copy remains intact.
• Simple but less efficient for large systems.

Crash Recovery Workflow

1. System restarts after crash.
2. DBMS scans the log file.
3. Two lists are created:
o Undo List → Transactions that started but did not commit.
o Redo List → Transactions that committed before crash.
4. DBMS executes:
o UNDO all uncommitted transactions.
o REDO all committed transactions.

Advantages of Crash Recovery

• Ensures data consistency after failures.
• Maintains atomicity & durability.

P a g e 81 | 100
 DATABASE MANAGEMENT SYSTEMS

• Minimizes downtime after crashes.

• Protects against data loss with logs + backups.
1. Two-Phase Locking (2PL)
Definition:
Two-Phase Locking (2PL) is a protocol that ensures serializability of
transactions by controlling how locks are acquired and released.
It divides the lifetime of a transaction into two phases:
1. Growing Phase
o Transaction can acquire locks (shared or exclusive).
o Cannot release any lock in this phase.
2. Shrinking Phase
o Transaction can release locks.
o Cannot acquire any new locks in this phase.

Types of 2PL
1. Basic 2PL – Just follow the two phases.
2. Conservative (Static) 2PL – Transaction requests all locks at the start
before execution. Prevents deadlock but reduces concurrency.
3. Strict 2PL – All exclusive (write) locks are released only after
commit/rollback. Ensures recoverability and prevents cascading
rollbacks.
4. Rigorous 2PL – All locks (read + write) are released only at commit.
Strongest isolation.

Example
Transaction T1: Reads and updates A.
Transaction T2: Reads and updates B.
• T1 acquires lock on A → updates A.
• T2 acquires lock on B → updates B.
• If both try to lock each other’s items, 2PL ensures order and
serializability.

2. Serializability
Definition:
Serializability ensures that the concurrent execution of transactions is
equivalent to some serial execution (i.e., one transaction after another).
P a g e 82 | 100
 DATABASE MANAGEMENT SYSTEMS

It is the gold standard for correctness in concurrent execution.

Types of Serializability
1. Conflict Serializability
o Two schedules are conflict equivalent if you can swap non-
conflicting operations to transform one schedule into the other.
o Conflict = when two operations access the same data item and at
least one is a write.
o Ensured by precedence (serialization) graphs.
2. View Serializability
o Two schedules are view equivalent if:
1. They read the same initial values.
2. Transactions read the same values written by others.
3. Final writes are the same.
o Weaker but more general than conflict serializability.

Example
Schedule:
T1: Read(A), Write(A)
T2: Read(A), Write(A)
If T1 writes A first, then T2 overwrites, it must be equivalent to serial order (T1
→ T2).

3. Deadlocks
Definition:
A deadlock occurs when two or more transactions are waiting for each other’s
locks and none of them can proceed.
It’s like two people holding one key each but needing both keys to continue.

Deadlock Example
• T1 locks A, requests B.
• T2 locks B, requests A.
• Neither can proceed → deadlock.

Deadlock Handling
1. Deadlock Prevention
P a g e 83 | 100
 DATABASE MANAGEMENT SYSTEMS

o Ensure deadlocks never happen by ordering resources or using

timeouts.
o Techniques:
▪ Wait-Die (older transaction waits, younger is killed)
▪ Wound-Wait (older preempts younger).
2. Deadlock Detection
o Allow deadlocks, then periodically check using a wait-for graph.
o If a cycle exists → deadlock → kill one transaction.
3. Deadlock Avoidance
o Use algorithms like Banker’s Algorithm to ensure system never
enters unsafe state.

4. Lock Management
Definition:
Lock management is the system mechanism that handles how locks are
requested, granted, and released to maintain consistency and concurrency.

Types of Locks
1. Shared Lock (S) – For reading. Multiple transactions can hold it.
2. Exclusive Lock (X) – For writing. Only one transaction can hold it.
3. Intention Locks – Used in multi-granularity locking (table vs. row).
o IS (Intention Shared) – Intend to acquire shared lock at finer level.
o IX (Intention Exclusive) – Intend to acquire exclusive lock at finer
level.
o SIX – Shared + intention exclusive.

1. File Organization
File organization refers to the way records (rows/tuples) are physically stored in
the database on disk. The choice of organization affects how efficiently data can
be inserted, retrieved, updated, or deleted.
Common Types of File Organization:
• Heap (Unordered) Files
o Records are stored in no particular order.
o New records are simply appended at the end of the file.
o Fast insertion but slower searches (linear scan required).
• Sequential (Ordered) Files

P a g e 84 | 100
 DATABASE MANAGEMENT SYSTEMS

o Records are stored in sorted order based on a search key.

o Efficient for range queries and ordered access.
o Insertions and deletions are expensive (since order must be
preserved).
• Hashed Files
o Records are stored at positions determined by a hash function on a
search key.
o Very fast equality search (e.g., finding a record by ID).
o Not efficient for range queries (since order is lost).
• Clustered Files
o Related records from different tables are stored together (e.g.,
students and their enrollments).
o Improves join performance but more complex to maintain.

In summary: File organization determines how efficiently data operations

(insert, search, update, delete) can be performed.

2. Index Structures
An index is a data structure that improves the speed of searching and
retrieving rows in a database table. It works like the index in a book, pointing to
the location of needed data.
Common Index Structures:
• Single-Level Index
o A simple list of key values and pointers to records.
o Works like a phone book.
• Multi-Level Index
o Uses multiple levels of indexes for large datasets.
o Example: First-level index points to second-level indexes, which
point to actual data blocks.
• B+ Tree Index
o Most commonly used index in databases.
o Balanced tree structure where internal nodes act as a directory, and
leaf nodes store actual data pointers.
o Supports both equality and range queries efficiently.
• Hash Index
o Uses hash functions to map keys directly to locations.
o Very fast for equality lookups (e.g., WHERE id=101) but poor for
range queries.
P a g e 85 | 100
 DATABASE MANAGEMENT SYSTEMS

• Bitmap Index
o Uses bitmaps to represent the existence of a value.
o Efficient for columns with a small number of distinct values (e.g.,
gender, status).

3. Index Tuning
Index tuning means optimizing the use of indexes to improve database
performance.
Activities in Index Tuning:
• Choosing appropriate indexes
o E.g., creating indexes on columns often used in WHERE, JOIN,
GROUP BY, ORDER BY.
• Dropping unused or redundant indexes
o Too many indexes slow down insert/update/delete operations
because all indexes need to be updated.
• Balancing read vs. write performance
o Indexes speed up read operations but add overhead to write
operations.
• Monitoring query performance
o Using tools like query optimizers and execution plans to check
whether indexes are being used effectively.
• Index maintenance
o Rebuilding or reorganizing fragmented indexes to keep them
efficient.

4. Guidelines for Index Selection

When deciding which indexes to create, consider the following rules:
1. Index primary keys and frequently accessed foreign keys
o Since these are often used in joins and searches.
2. Index columns used in WHERE clauses
o Especially those with high selectivity (columns with many distinct
values).
3. Use composite indexes for multi-column queries
o If queries often involve conditions on multiple columns, a
combined index may be better.
4. Avoid indexing columns with low selectivity

P a g e 86 | 100
 DATABASE MANAGEMENT SYSTEMS

o Example: Boolean fields (e.g., is_active) are not good index

candidates.
5. Index columns used in ORDER BY and GROUP BY
o Helps avoid sorting overhead.
6. Balance read and write needs
o Heavy transaction systems (frequent inserts/updates) should have
fewer indexes.
o Analytical systems (frequent reads) can benefit from more indexes.
7. Periodically review and tune indexes
o Monitor usage patterns, drop unused indexes, and optimize as
workload changes.

Unit-4
Introduction to NoSQL

NoSQL
What is NoSQL?
• NoSQL = “Not Only SQL.”
• A type of database that provides non-relational ways to store and
manage data.
• Designed to handle large-scale, unstructured, and semi-structured
data that traditional relational databases (RDBMS) struggle with.

Key Features of NoSQL Databases

1. Schema-less → No fixed table structure; flexible data models.
2. Scalability → Easily handles huge volumes of data (horizontal scaling
across servers).
3. High Performance → Optimized for fast read/write operations.
4. Variety of Data Models → Supports document, key-value, column-
family, and graph models.
5. Distributed → Often built for cloud and big data applications.
6. Support for Unstructured Data → JSON, XML, key-value pairs, wide-
column, graph data.
P a g e 87 | 100
 DATABASE MANAGEMENT SYSTEMS

7. Flexible Data Models → Key-Value, Document, Column-family, and

Graph.

Challenges of NoSQL Databases

• Lack of standardization: NoSQL systems can be vastly different from
one another, making it even harder to choose the right one for a specific
use case.
• Lack of ACID compliance: NoSQL databases may not provide
consistency, which is a disadvantage for applications that need strict data
integrity.
• Narrow focus: Great for storage but lack functionalities as transaction
management, in which relational databases are great.
• Absence of Complex Query Support: They are not designed to handle
complex queries, which means that they are not a good fit for applications
that require complex data analysis or reporting.
• Lack of maturity: Being relatively new, NoSQL may not have the
reliability, security and feature set of traditional relational databases.
• Management complexity: For large datasets, maintaining a NoSQL
database could be quite more complicated than managing a relational
database.
• Limited GUI Tools: While some NoSQL databases, like MongoDB offer
GUI tools like MongoDB Compass, not all NoSQL databases provide
flexible or user-friendly GUI tools.

SQL vs. NoSQL:

SQL (Relational NoSQL (Non-Relational
Feature DB) DB)

Flexible (Documents, Key-

Data Model Structured, Tabular
Value, Graphs)

Scalability Vertical Scaling Horizontal Scaling

Schema Predefined Dynamic & Schema-less

P a g e 88 | 100
 DATABASE MANAGEMENT SYSTEMS

SQL (Relational NoSQL (Non-Relational

Feature DB) DB)

ACID Limited or Eventual

Strong
Support Consistency

Transactional Big data, real-time

Best For
applications analytics

MySQL, MongoDB, Cassandra,

Examples
PostgreSQL, Oracle Redis

Popular NoSQL Databases & Their Use Cases

NoSQL
Database Type Use Cases

Content management, product

MongoDB Document-based
catalogs

Caching, real-time analytics,

Redis Key-Value Store
session storage

Column-Family Big data, high availability

Cassandra
Store systems

Fraud detection, social

Neo4j Graph Database
networks

Use of NoSQL
• Big Data Applications: Efficiently stores and processes massive amounts
of unstructured and semi-structured data.
• Real-Time Analytics: Supports fast queries and analysis for use cases
like recommendation engines or fraud detection.

P a g e 89 | 100
 DATABASE MANAGEMENT SYSTEMS

• Scalable Web Applications: Handles high traffic and large user bases by
scaling horizontally across servers.
• Flexible Data Storage: Manages diverse data formats (JSON, key-value,
documents, graphs) without rigid schemas.

Types of NoSQL Databases

NoSQL databases can be classified into four main types, based on their data
storage and retrieval methods:
1. Document-based databases
2. Key-value stores
3. Column-oriented databases
4. Graph-based databases
Each type has unique advantages and use cases, making NoSQL a preferred
choice for big data applications, real-time analytics, cloud computing and
distributed systems.

P a g e 90 | 100
 DATABASE MANAGEMENT SYSTEMS

Document-Based NoSQL Database

Definition
A document-based database is a type of NoSQL database that stores data in
the form of documents instead of rows and columns.
• Documents are usually stored in JSON (JavaScript Object Notation),
BSON (Binary JSON), or XML format.
• Each document contains key-value pairs, nested structures, and arrays,
making it more flexible than relational tables.

Key Features
1. Schema-less → No fixed schema like SQL tables. Each document can
have different fields.
2. Human-readable format → JSON-style documents are easy to
understand.
3. Flexible structure → Can store complex/nested data in a single
document.
4. Indexing support → Allows indexing on fields for fast queries.
5. Horizontal scalability → Easy to scale across multiple servers.

Popular Document Databases & Use Cases

Database Use Case

Content management, product catalogs, user

MongoDB
profiles

CouchDB Offline applications, mobile synchronization

Firebase
Real-time apps, chat applications
Firestore

2. Key-Value Stores
A key-value store is a nonrelational database. The simplest form of a NoSQL
database is a key-value store. Every data element in the database is stored in
key-value pairs. The data can be retrieved by using a unique key allotted to
each element in the database. The values can be simple data types like

P a g e 91 | 100
 DATABASE MANAGEMENT SYSTEMS

strings, numbers or complex objects. A key-value store is like a relational

database with only two columns which is the key and the value.
Key features of the key-value store:
• Simplicity: Data retrieval is extremely fast due to direct key access.
• Scalability: Designed for horizontal scaling and distributed storage.
• Speed: Ideal for caching and real-time applications.

Popular Key-Value Databases & Use Cases

Database Use Case

Caching, real-time leaderboards, session

Redis
storage

Memcached High-speed in-memory caching

Amazon
Cloud-based scalable applications
DynamoDB

3. Column Oriented Databases

A column-oriented database is a non-relational database that stores the data
in columns instead of rows. That means when we want to run analytics on a
small number of columns, we can read those columns directly without
consuming memory with the unwanted data. Columnar databases are
designed to read data more efficiently and retrieve the data with greater
speed. A columnar database is used to store a large amount of data.
Key features of Columnar Oriented Database
• High Scalability: Supports distributed data processing.
• Compression: Columnar storage enables efficient data compression.
• Faster Query Performance: Best for analytical queries.
Popular Column-Oriented Databases & Use Cases
Database Use Case

Apache Cassandra Real-time analytics, IoT applications

Google Bigtable Large-scale machine learning, time-series data

P a g e 92 | 100
 DATABASE MANAGEMENT SYSTEMS

Database Use Case

HBase Hadoop ecosystem, distributed storage

4. Graph-Based Databases
Graph-based databases focus on the relationship between the elements. It
stores the data in the form of nodes in the database. The connections between
the nodes are called links or relationships, making them ideal for complex
relationship-based queries.
• Data is represented as nodes (objects) and edges (connections).
• Fast graph traversal algorithms help retrieve relationships quickly.
• Used in scenarios where relationships are as important as the data itself.
Key features of Graph Database
• Relationship-Centric Storage: Perfect for social networks, fraud
detection, recommendation engines.
• Real-Time Query Processing: Queries return results almost instantly.
• Schema Flexibility: Easily adapts to evolving relationship structures
Popular Graph Databases & Use Cases
Database Use Case

Neo4j Fraud detection, social networks

Amazon Neptune Knowledge graphs, AI recommendations

ArangoDB Multi-model database, cybersecurity

CAP Theorem (Brewer’s Theorem)

Proposed by Eric Brewer (2000) and later proved by Gilbert & Lynch
(2002).
It states that:
In a distributed database system, you can only guarantee two out of the
following three properties at the same time:
1. Consistency (C)
• Every read gives the most recent write or an error.
• Means all nodes in the system show the same data at the same time.

P a g e 93 | 100
 DATABASE MANAGEMENT SYSTEMS

• Example: In a banking app, if you transfer ₹1000, your balance must

update immediately on all servers.
2. Availability (A)
• Every request receives a response, even if some nodes are down.
• The system always responds (it may not always give the latest data).
• Example: Social media feed loads even if one server is not updated.
3. Partition Tolerance (P)
• The system continues to operate even if there is a network partition
(communication break) between nodes.
• In real-world distributed systems, partitions will happen, so P is usually
non-negotiable.

Why only 2 of 3?
Because:
• If the system tries to be Consistent + Available, it cannot tolerate
partitions.
• If the system is Consistent + Partition tolerant, some requests may be
denied (low availability).
• If the system is Available + Partition tolerant, it may serve outdated
data (no strong consistency).

CAP Theorem Combinations

1. CA (Consistency + Availability)
o No partition tolerance.
o Works only when there is no network failure.
o Example: Traditional RDBMS (single-node or tightly coupled
systems).
2. CP (Consistency + Partition Tolerance)
o Sacrifices availability.
o During network issues, some nodes may reject requests to preserve
consistency.
o Example: MongoDB, HBase, Redis (configured for strong
consistency).
3. AP (Availability + Partition Tolerance)
o Sacrifices consistency.
o During partitions, system keeps running but may give stale data.
o Example: Cassandra, DynamoDB, Couchbase.

P a g e 94 | 100
 DATABASE MANAGEMENT SYSTEMS

Real-World Analogy
Imagine an ATM network:
• Consistency → Every ATM shows the exact same account balance.
• Availability → You can always withdraw cash, even if the bank server is
busy.
• Partition Tolerance → ATMs still work even if some servers lose
connection.
You can’t have all 3 at once:
• If the ATM is available during a partition, it might give outdated balance
(AP).
• If the ATM insists on consistency, it may refuse service when
disconnected (CP).

3. BASE vs ACID
Feature ACID (Relational DB) BASE (NoSQL DB)
Atomicity, Consistency, Basically Available, Soft
Stands for
Isolation, Durability State, Eventually Consistent
Data
Strong, reliable Relaxed, flexible
Integrity
Immediate (strong
Consistency Eventual consistency
consistency)
Supports complex
Transaction Lightweight transactions
transactions
Banking, finance, Big Data, real-time apps,
Use Case
sensitive data IoT, social networks

4. CRUD Operations
CRUD = Create, Read, Update, Delete → Fundamental operations for any
database.
• Create → Insert new data (SQL: INSERT, MongoDB: insertOne).
• Read → Retrieve data (SQL: SELECT, MongoDB: find).
• Update → Modify existing data (SQL: UPDATE, MongoDB:
updateOne).
• Delete → Remove data (SQL: DELETE, MongoDB: deleteOne).

P a g e 95 | 100
 DATABASE MANAGEMENT SYSTEMS

MongoDB operators
MongoDB operators are special keywords used inside queries and updates to
perform operations like filtering, comparisons, logical conditions, updates,
and aggregation.

Types of MongoDB Operators

1. Comparison Operators
Used to compare values (like <, >, = in SQL).
Operator Meaning Example
{ age: { $eq: 25 } } → Finds users
$eq Equal to
with age = 25
$ne Not equal to { age: { $ne: 25 } }
$gt Greater than { age: { $gt: 20 } }
$gte Greater than or equal { age: { $gte: 20 } }
$lt Less than { age: { $lt: 30 } }
$lte Less than or equal { age: { $lte: 30 } }
Matches any value in { city: { $in: ["Delhi", "Mumbai"]
$in
an array }}
{ city: { $nin: ["Delhi", "Mumbai"]
$nin Not in array
}}

2. Logical Operators
Used to combine multiple conditions.
Operator Meaning Example
Returns documents matching { $and: [ { age: { $gt: 18 }
$and
all conditions }, { city: "Delhi" } ] }
Returns documents matching { $or: [ { age: 18 }, { city:
$or
at least one condition "Delhi" } ] }
{ age: { $not: { $gt: 30 } }
$not Negates a condition
}
Opposite of OR (none of the { $nor: [ { age: 18 }, { city:
$nor
conditions should match) "Delhi" } ] }

P a g e 96 | 100
 DATABASE MANAGEMENT SYSTEMS

3. Element Operators
Used to check presence/type of fields.
Operator Meaning Example
$exists Checks if a field exists { email: { $exists: true } }
$type Checks data type { age: { $type: "int" } }

4. Evaluation Operators
Used for pattern matching or condition evaluation.
Operator Meaning Example
Matches regex { name: { $regex: "^A" } } →
$regex
patterns names starting with A
Allows expressions in { $expr: { $gt: ["$salary",
$expr
queries "$expenses"] } }
$text Performs text search { $text: { $search: "developer" } }

5. Array Operators
Work with arrays inside documents.
Operator Meaning Example
Matches arrays containing { tags: { $all: ["mongodb",
$all
all specified elements "database"] } }
Matches if at least one array { scores: { $elemMatch: {
$elemMatch
element satisfies condition $gt: 80, $lt: 90 } } }
Matches arrays of a specific
$size { tags: { $size: 3 } }
size

6. Update Operators
Used in updateOne, updateMany, etc.
Operator Meaning Example
$set Updates a field’s value { $set: { age: 30 } }
$unset Removes a field { $unset: { email: "" } }
$inc Increments a field { $inc: { age: 1 } }
$mul Multiplies a field { $mul: { salary: 2 } }

P a g e 97 | 100
 DATABASE MANAGEMENT SYSTEMS

Operator Meaning Example

{ $rename: { "oldName":
$rename Renames a field
"newName" } }
$push Adds an item to array { $push: { tags: "newTag" } }
Removes matching value
$pull { $pull: { tags: "mongodb" } }
from array
Adds value only if it { $addToSet: { tags:
$addToSet
doesn’t exist "uniqueTag" } }

7. Aggregation Operators
Used in aggregation pipeline for analytics.
Operator Meaning Example
{ $group: { _id: null, total: { $sum:
$sum Sums values
"$salary" } } }
{ $group: { _id: null, avgAge: { $avg:
$avg Average value
"$age" } } }
{ $group: { _id: null, maxSalary: {
$max Maximum value
$max: "$salary" } } }
{ $group: { _id: null, minSalary: {
$min Minimum value
$min: "$salary" } } }
Groups
$group Group by department, etc.
documents
Filters
$match Similar to find() conditions
documents
Selects specific
$project { $project: { name: 1, salary: 1 } }
fields

Big Data Overview

1. What is Big Data?
Big Data refers to extremely large, complex, and fast-growing datasets
that cannot be efficiently handled by traditional databases (like RDBMS).
• Data can be structured (tables), semi-structured (XML, JSON), or
unstructured (images, videos, logs, social media posts).

P a g e 98 | 100
 DATABASE MANAGEMENT SYSTEMS

• Traditional systems struggle to store, process, and analyze such massive

data, so special technologies (Hadoop, Spark, NoSQL) are used.

2. Characteristics of Big Data (5 Vs)

Originally 3Vs, later extended to 5Vs:
1. Volume → Huge amount of data (TB, PB, EB).
o Example: Facebook generates petabytes of data daily.
2. Velocity → Speed at which data is generated and processed.
o Example: Stock market trades, IoT sensors, social media streams.
3. Variety → Different types of data.
o Example: Text, images, audio, video, logs, JSON, SQL tables.
4. Veracity → Trustworthiness and quality of data.
o Example: Fake news on social media vs. verified information.
5. Value → Extracting useful insights from raw data.
o Example: E-commerce analyzing customer behavior to improve
sales.

3. Types of Big Data

• Structured Data → Tabular, stored in rows & columns (e.g., banking
records).
• Semi-Structured Data → No strict schema (e.g., JSON, XML, email).
• Unstructured Data → Raw formats like videos, social media posts,
documents.

1. Hadoop Overview
Hadoop is an open-source framework for distributed storage and
processing of Big Data.
Core Components of Hadoop
1. HDFS (Hadoop Distributed File System)
o Stores very large files across clusters.
o Data is split into blocks and replicated for fault tolerance.
2. MapReduce
o A programming model for batch processing.
o Splits tasks into smaller parts, processes them in parallel, and
combines results.
3. YARN (Yet Another Resource Negotiator)
o Manages cluster resources.

P a g e 99 | 100
 DATABASE MANAGEMENT SYSTEMS

o Handles job scheduling and task allocation.

4. Hadoop Ecosystem Tools
o Hive → SQL-like queries on Hadoop.
o Pig → High-level scripting for data analysis.
o HBase → NoSQL database on top of Hadoop.
o Sqoop/Flume → Import/export data between Hadoop and other
systems.
Use Cases:
• Batch data processing.
• Data warehousing and analytics.
• Processing log files, sensor data, clickstream data.

2. Cassandra Overview
Apache Cassandra is a distributed NoSQL database built to handle huge
volumes of data across many servers with no single point of failure.
Key Features of Cassandra
• Decentralized (Peer-to-Peer) Architecture → No master-slave model.
• AP Model (Availability + Partition Tolerance) from CAP theorem.
• Wide-Column Store → Data is stored in tables with flexible columns.
• Linear Scalability → Adding new nodes increases performance.
• Fault-Tolerant → Data replicated across multiple nodes.
• High Write Performance → Optimized for fast inserts.
When to use Cassandra?
• Applications needing 24/7 uptime.
• Systems requiring high-speed writes (logs, sensor data, IoT).
• Social media apps, messaging apps, recommendation engines.

Use Cases:
• Netflix, Instagram, Uber use Cassandra for real-time data handling.

P a g e 100 | 100