UNIT I

Database Management System

Database Management System is a software or technology used to manage
data from a database. Some popular databases are MySQL, Oracle, MongoDB,
etc. DBMS provides many operations e.g. creating a database, Storing in the
database, updating an existing database, delete from the database. DBMS is a
system that enables you to store, modify, and retrieve data in an organized way.
It also provides security to the database.

Characteristics of DBMS

o It uses a digital repository established on a server to store and manage the

information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID properties which maintain data in a healthy state in case
of failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
o It can view the database from different viewpoints according to the
requirements of the user.

Advantages of Database Management System

Some of the great advantages of DBMS are listed below:

1. Better use of data or information - We can easily and efficiently access

well-managed and synchronized forms of data with the help of DBMS. It
makes data handling simple, provides an integrated perspective of how a
certain business is operating and also aids in keeping track of how one
element of the business affects another portion.
2. Secured Data - The likelihood of security problems increases as a
database becomes more functional and accessible. The danger to data
security rises as a result of the rate at which data is shared or transferred
growing along with the user base. It is frequently utilized in the business
world where organizations spend a lot of time, money, and effort making
sure data is protected and handled effectively. Data management systems
(DBMS) offer a stronger framework for data privacy and security policies,
assisting businesses in enhancing data security.
3. Reduces Data Inconsistency and Redundancy - The major issues faced
during the process of storing data are inconsistency and redundancy.
Inconsistent data may lead to a big loss to an individual or a business model
and the storage capacity is not utilized properly because of the data
redundancy. When multiple copies with different versions or values of the
same data exist in various locations, then it causes inconsistency. Data
 Redundancy and inconsistency can both be significantly decreased by
properly designing a database with the help of a database management
system.
4. Better Recovery and Backups - Backup and recovery are handled
automatically by the DBMS. Users don't need to regularly back up their
data because the DBMS handles this for them. Additionally, it returns the
database to its prior state following a crash or system failure.
5. Fast Data Sharing - Database administration makes it possible for
consumers to access more and better-managed data. DBMS enables end
users to quickly scan their environment and react to any alterations made
there.
6. Helps in decision-making - Because of the well-managed data and
improved data access provided by DBMS, we are able to produce better-
quality information and, as a result, make better judgments. Accuracy,
validity, and the time it takes to read data are all improved by better data
quality. Although DBMS does not ensure data quality, it does offer a
framework that makes it simple to enhance data quality.
7. Increases Privacy - The privacy rule in a database specifies the privacy
restrictions that can only be accessed by authorized users. A user can only
view the data he is permitted to view since there are different degrees of
database access. For instance, on social networking sites, different
accounts that a user wishes to access have varying access restrictions and
a user can only see his/her account details, not others.
8. User Friendly - Data are presented in a straightforward and logical manner
by database management systems (DBMS). It is simple to carry out many
activities, such as the addition, deletion, or creation of files or data.
9. Data Abstraction - In order to give users an abstract overview of the data,
database systems are primarily used. Since numerous intricate algorithms
are employed by developers to boost the effectiveness of databases that are
concealed from users by several degrees of data abstraction, consumers can
easily engage with the system.

In simple words, DBMS reduces data inconsistency and redundancy, increases

data security and privacy, and provides a platform to access and share data faster.

Instance and Schema in DBMS

What is DBMS Schema?

Here the DBMS schema means designing the database. For example, if we take
the example of the employee table. The employee table contains the following
attributes. These attributes are EMP_ID, EMP_ADDRESS, EMP_NAME,
EMP_CONTACT. These are the schema of the employee table.

Schema is further divided into three types. These three are as follows.

1. Logical schema.
 2. View schema.
3. Physical schema.

The schema defines the logical view of the database. It provides some knowledge
about the database and what data needs to go where.

In DBMS, the schema is shown in diagram format.

We can understand the relationship between the data present in the database. With
the help of this schema, we can implement the DBMS function such as delete,
insert, search, update, etc.

Let us understand this by the below diagram. There are three diagrams, i.e.,
section, course, and student. This diagram shows the relationship between the
section and the course diagram. Schema is the only type of structural view of the
database that is shown below.

1. Physical schema:
In the physical schema, the database is designed at the physical level. At this
level, the schema describes how the data block is stored and how the storage is
managed.

2. Logical schema:
In the logical schema, the database is designed at a logical level. At this level, the
programmer and data administrator perform their work. Also, at this level, a
certain amount of data is stored in a structured way. But the internal
implementation data are hidden in the physical layer for the security proposed.

3. View schema:
In view schema, the database is designed at the view level. This schema describes
the user interaction with the database system.

Moreover, Data Definition Language (DDL) statements help to denote the

schema of a database. The schema represents the name of the table, the name of
attributes, and their types; constraints of the tables are related to the schema.
Therefore, if users want to modify the schema, they can write DDL statements.

What is DBMS Instance?

In DBMS, the data is stored for a particular amount of time and is called an
instance of the database. The database schema defines the attributes of the
database in the particular DBMS. The value of the particular attribute at a
particular moment in time is known as an instance of the DBMS.

For example, in the above example, we have taken the example of the attribute of
the schema. In this example, each table contains two rows or two records. In the
above schema of the table, the employee table has some instances because all the
data stored by the table have some instances.

Let's take another example: Let's say we have a single table student in the
database; today, the table has 100 records, so today, the instance of the database
has 100 records. We are going to add another 100 records to this table by
tomorrow, so the instance of the database tomorrow will have 200 records in the
table. In short, at a particular moment, the data stored in the database is called the
instance; this change over time as and when we add, delete or update data in the
database.

What is Data Model?

In order to define the connections, organization, and structure of data in a
database management system (DBMS), data models are essential. The
effectiveness of a database’s ability to store, retrieve, and alter data is greatly
influenced by the choice of data model. The Hierarchical, Network, and
Relational models are some of the oldest types of data models; each has special
traits and applications. It is crucial to comprehend how these models vary in
order to choose the right one for a given set of circumstances.

Types of Relational Models

1. Conceptual Data Model
2. Representational Data Model
3. Physical Data Model
It is basically classified into 3 types:-

1. Conceptual Data Model

The conceptual data model describes the database at a very high level and is
useful to understand the needs or requirements of the database. It is this model,
that is used in the requirement-gathering process i.e. before the Database
Designers start making a particular database. One such popular model is
the entity/relationship model (ER model). The E/R model specializes in entities,
relationships, and even attributes that are used by database designers. In terms
of this concept, a discussion can be made even with non-computer science(non-
technical) users and stakeholders, and their requirements can be understood.
Entity-Relationship Model( ER Model): It is a high-level data model which is
used to define the data and the relationships between them. It is basically a
conceptual design of any database which is easy to design the view of data.
Components of ER Model:
1. Entity: An entity is referred to as a real-world object. It can be a name,
place, object, class, etc. These are represented by a rectangle in an ER
Diagram.
2. Attributes: An attribute can be defined as the description of the entity.
These are represented by Ellipse in an ER Diagram. It can be Age, Roll
Number, or Marks for a Student.
3. Relationship: Relationships are used to define relations among
different entities. Diamonds and Rhombus are used to show
Relationships.
Characteristics of a conceptual data model
• Offers Organization-wide coverage of the business concepts.
• This type of Data Models are designed and developed for a business
audience.
• The conceptual model is developed independently of hardware
specifications like data storage capacity, location or software
specifications like DBMS vendor and technology. The focus is to
represent data as a user will see it in the “real world.”
Conceptual data models known as Domain models create a common vocabulary
for all stakeholders by establishing basic concepts and scope
2. Representational/ Logical Data Model
This type of data model is used to represent only the logical part of the database
and does not represent the physical structure of the database. The
representational data model allows us to focus primarily, on the design part of
the database. A popular representational model is a Relational model. The
relational Model consists of Relational Algebra and Relational Calculus. In the
Relational Model, we basically use tables to represent our data and the
relationships between them. It is a theoretical concept whose practical
implementation is done in Physical Data Model.
The advantage of using a Representational data model is to provide a foundation
to form the base for the Physical model
3. Physical Data Model
The physical Data Model is used to practically implement Relational Data
Model. Ultimately, all data in a database is stored physically on a secondary
storage device such as discs and tapes. This is stored in the form of files, records,
and certain other data structures. It has all the information on the format in which
the files are present and the structure of the databases, the presence of external
data structures, and their relation to each other. Here, we basically save tables
in memory so they can be accessed efficiently. In order to come up with a good
physical model, we have to work on the relational model in a better
way. Structured Query Language (SQL) is used to practically implement
Relational Algebra.
This Data Model describes HOW the system will be implemented using a
specific DBMS system. This model is typically created by DBA and developers.
The purpose is actual implementation of the database.
Characteristics of a physical data model:
• The physical data model describes data need for a single project or
application though it maybe integrated with other physical data models
based on project scope.
• Data Model contains relationships between tables that which addresses
cardinality and nullability of the relationships.
• Developed for a specific version of a DBMS, location, data storage or
technology to be used in the project.
• Columns should have exact datatypes, lengths assigned and default
values.
• Primary and Foreign keys, views, indexes, access profiles, and
authorizations, etc. are defined
Other Data Models
What is a Hierarchical Data Model?
The hierarchical data model is the oldest type of the data model. It was
developed by IBM in 1968. It organizes data in a tree-like structure. Hierarchical
model consists of the following :
• It contains nodes which are connected by branches.
• The topmost node is called the root node.
• If there are multiple nodes appear at the top level, then these can be
called root segments.
• Each node has exactly one parent.
• One parent may have many children.

In the above figure, Electronics is the root node which has two children i.e.
Televisions and Portable Electronics. These two has further children for which
they act as parent. For example: Television has children as Tube, LCD and
Plasma, for these three Television act as parent. It follows one to many
relationship.
Advantages of the Hierarchical Data Model
• Because of its tree form, it is easy to grasp.
• Retrieving data in a one-to-many connection is efficient.
Disadvantages of the Hierarchical Data Model
• Inflexibility in reorganizing data.
• accessing complicated data structures may be challenging.
 • redundant data storage, which might cause anomalies and
inconsistencies.
What is a Network Data Model?
It is the advance version of the hierarchical data model. To organize data it uses
directed graphs instead of the tree-structure. In this child can have more than
one parent. It uses the concept of the two data structures i.e. Records and Sets.

In the above figure, Project is the root node which has two children i.e. Project
1 and Project 2. Project 1 has 3 children and Project 2 has 2 children. Total there
are 5 children i.e Department A, Department B and Department C, they are
network related children as we said that this model can have more than one
parent. So, for the Department B and Department C have two parents i.e. Project
1 and Project 2.
Advantages of the Network Data Model
• Because of its numerous parent ties, it is more adaptable than the
hierarchical approach.
• Ideal for managing intricate, many-to-many connections.
Disadvantages of the Network Data Model
• Increased complexity in database design and management.
• requires complex programming in order to manage and work with
data.
What is a Relational Data Model?
The relational data model was developed by E.F. Codd in 1970. There are no
physical links as they are in the hierarchical data model. Following are the
properties of the relational data model :
• Data is represented in the form of table only.
• It deals only with the data not with the physical structure.
• It provides information regarding metadata.
• At the intersection of row and column there will be only one value for
the tuple.
• It provides a way to handle the queries with ease.
Advantages of the Relational Data Model
• High data independence and flexibility.
• offers robust and user-friendly querying features.
• removes duplication by use of normalization.
Disadvantages of the Relational Data Model
• For certain kinds of straightforward data retrieval tasks, they may not
perform as well as hierarchical models.
• demands a deeper comprehension of SQL and normalization
principles.

Difference Between Hierarchical, Network and Relational Data Model

Hierarchical Data Relational Data
Model Network Data Model Model

In this model, to store It organizes records in

data hierarchy method It organizes records to the form of table and
is used. It is the oldest one another through relationship between
method and not in use links or pointers. tables are set using
today. common fields.

It organizes records in
To organize records, it It organizes records in
the form of directed
uses tree structure. the form of tables.
graphs.

In addition to 1:1 and

In addition to 1:1 and 1:n
It implements 1:1 and 1:n it also implements
it also implements many
1:n relations. many to many
to many relationships.
relationships.
Hierarchical Data Relational Data
Model Network Data Model Model

The logical
Pointers are used to A linked list is used to
representation is used
establish relationships establish a relationship
with rows and columns
among records among records
to depict relationship
physically. physically.
among records.

Insertion
anomaly exits in this
model i.e. child node There is no insertion There is no insertion
cannot be inserted anomaly. anomaly.
without the parent
node.

Deletion anomaly
exists in this model i.e. There is no deletion There is no deletion
it is difficult to delete anomaly. anomaly.
the parent node.

Update leads to Updating a record is

inconsistency easy and simple with
No such problem as only
problems because of the process of
one instance of records
the existence of normalization, the
exist.
multiple instances of a redundant data gets
child record. removed.

There is partial data

This model lacks data This model provides
independence in this
independence. data independence.
model.

No such facility for No such facility for

SQL-based declarative
querying database is querying database is
querying is supported.
supported. supported.

It is used to access the It is used to access the It is used to access the

data which is complex data which is complex data which is complex
and asymmetric. and symmetric. and symmetric.
Hierarchical Data Relational Data
Model Network Data Model Model

Difficult to design a
It is easy to
database and manipulate
Difficult to design a comprehend due to
a database because of its
database because of its concealed physical
complexity. Hence, it
complexity. level details from end-
imposes a burden on the
users.
programmer.

It is flexible as compared It is flexible as

It is less flexible. to the hierarchical compared to the
model. hierarchical model.

Object Oriented Data Model :

In Object Oriented Data Model, data and their relationships are contained in a
single structure which is referred as object in this data model. In this, real world
problems are represented as objects with different attributes. All objects have
multiple relationships between them. Basically, it is combination of Object
Oriented programming and Relational Database Model as it is clear from the
following figure :
Object Oriented Data Model = Combination of Object Oriented Programming +
Relational database model
Components of Object Oriented Data Model :

Basic Object Oriented Data Model

• Objects –
An object is an abstraction of a real world entity or we can say it is an
instance of class. Objects encapsulates data and code into a single unit
which provide data abstraction by hiding the implementation details
from the user. For example: Instances of student, doctor, engineer in
above figure.
 • Attribute –
An attribute describes the properties of object. For example: Object is
STUDENT and its attribute are Roll no, Branch, Setmarks() in the
Student class.

• Methods –
Method represents the behavior of an object. Basically, it represents
the real-world action. For example: Finding a STUDENT marks in
above figure as Setmarks().

• Class –
A class is a collection of similar objects with shared structure i.e.
attributes and behavior i.e. methods. An object is an instance of class.
For example: Person, Student, Doctor, Engineer in above figure.

class student
{
char Name[20];
int roll_no;
--
--
public:
void search();
void update();
}
In this example, students refers to class and S1, S2 are the objects of class which
can be created in main function.
• Inheritance –
By using inheritance, new class can inherit the attributes and methods
of the old class i.e. base class. For example: as classes Student, Doctor
and Engineer are inherited from the base class Person.

Advantages of Object Oriented Data Model :

• Codes can be reused due to inheritance.
• Easily understandable.
• Cost of maintenance can reduced due to reusability of attributes and
functions because of inheritance.
Disadvantages of Object Oriented Data Model :
• It is not properly developed so not accepted by users easily.
DBMS Architecture

o The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers,
database servers and other components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation
which are connected via the network.
o DBMS architecture depends upon how users are connected to the database
to get their request done.

Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically,

database architecture is of two types like: 2-tier architecture and 3-tier
architecture.

1-Tier Architecture

o In this architecture, the database is directly available to the user. It means

the user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It
doesn't provide a handy tool for end users.
o The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database for the
quick response.
2-Tier Architecture

o The 2-Tier architecture is same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate with
the database at the server side. For this interaction, API's
like: ODBC, JDBC are used.
o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query
processing and transaction management.
o To communicate with the DBMS, client-side application establishes a
connection with the server side.
Fig: 2-tier Architecture

3-Tier Architecture

o The 3-Tier architecture contains another layer between the client and
server. In this architecture, client can't directly communicate with the
server.
o The application on the client-end interacts with an application server which
further communicates with the database system.
o End user has no idea about the existence of the database beyond the
application server. The database also has no idea about any other user
beyond the application.
o The 3-Tier architecture is used in case of large web application.

Fig: 3-tier Architecture

What is Data Independence in DBMS?

Data independence is a property of a database management system by which we

can change the database schema at one level of the database system without
changing the database schema at the next higher level. In this article, we will
learn in full detail about data independence and will also see its types. If you
read it completely, you will understand it easily.
In the context of a database management system, data independence is the
feature that allows the schema of one layer of the database system to be changed
without any impact on the schema of the next higher level of the database
system. ” Through data independence, we can build an environment in which
data is independent of all programs, and through the three schema architectures,
data independence will be more understandable. Data via two card stencils along
with centralized DBMS data is a form of transparency that has value for
someone.
It can be summed up as a sort of immunity of user applications that adjusts
correctly and does not change addresses, imparting the class of data and their
order. I want the separate applications not to be forced to deal with data
representation and storage specifics because this decreases quality and
flexibility. DBMS permits you to see data with such a generalized sight. It
actually means that the ability to change the structure of the lower-level schema
without presenting the upper-level schema is called data
independence.

Types of Data Independence

There are two types of data independence.
• logical data independence
• Physical data independence
Logical Data Independence
• Changing the logical schema (conceptual level) without changing the
external schema (view level) is called logical data independence.
• It is used to keep the external schema separate from the logical schema.
• If we make any changes at the conceptual level of data, it does not
affect the view level.
• This happens at the user interface level.
• For example, it is possible to add or delete new entities, attributes to
the conceptual schema without making any changes to the external
schema.
Physical Data Independence
• Making changes to the physical schema without changing the logical
schema is called physical data independence.
• If we change the storage size of the database system server, it will not
affect the conceptual structure of the database.
• It is used to keep the conceptual level separate from the internal level.
• This happens at the logical interface level.
• Example – Changing the location of the database from C drive to D
drive.
Difference Between Physical and Logical Data Independence
Physical Data Independence Logical Data Independence

It mainly concerns how the data is It mainly concerns about changes to

stored in the system. the structure or data definition.

It is easier to achieve than logical It is difficult to achieve compared to

independence. physical independence.

To make changes at the physical

To make changes at the logical level,
level we generally do not require
we need to make changes at the
changes at the application program
application level.
level.

It tells about the internal schema. It tells about the conceptual schema.

Whenever the logical structure of

There may or may not be a need for
the database has to be changed, the
changes to be made at the internal
changes made at the logical level are
level to improve the structure.
important.

Example- change in compression

Example – adding/modifying or
technology, hashing
deleting a new attribute.
algorithm, storage device etc.

Structure of DBMS
DBMS means Database Management System, which is a tool or software used to
create the database or delete or manipulate the database. A software programme
created to store, retrieve, query, and manage data is known as a Database
Management System (DBMS). Data can be generated, read, updated, and
destroyed by authorized entities thanks to user interfaces (UIs).

Because they give programmers, Database Managers, and end users a

consolidated view of the data, Database Management Systems are crucial because
they relieve applications and end users of the need to comprehend the physical
location of the data. Application Programme Interfaces (APIs) manage internet
requests and responses for particular sorts of data.

In marketing materials, the phrase "database as a service" (DBaaS) may be used

to refer to both relational and non-relational DBMS components that are given
via the internet.

Users of DBMSs include application programmers, Database Administrators

(DBAs), and end users.
Database Administrators are typically the only people who work directly with a
DBMS. Today, end users read and write to databases using front-end interfaces
made by programmers, while programmers use cloud APIs to connect with
DBMSs.

Three Parts that make up the Database System are:

o Query Processor
o Storage Manager
o Disk Storage

1. Query Processor

The query processing is handled by the query processor, as the name implies. It
executes the user's query, to put it simply. In this way, the query processor aids
the database system in making data access simple and easy. The query processor's
primary duty is to successfully execute the query. The Query Processor
transforms (or interprets) the user's application program-provided requests into
instructions that a computer can understand.
Components of the Query Processor

o DDL Interpreter:
Data Definition Language is what DDL stands for. As implied by the name, the
DDL Interpreter interprets DDL statements like those used in schema definitions
(such as create, remove, etc.). This interpretation yields a set of tables that include
the meta-data (data of data) that is kept in the data dictionary. Metadata may be
stored in a data dictionary. In essence, it is a part of the disc storage that will be
covered in a later section of this article.

o DML Compiler:
Compiler for DML Data Manipulation Language is what DML stands for. In
keeping with its name, the DML Compiler converts DML statements like select,
update, and delete into low-level instructions or simply machine-readable object
code, to enable execution. The optimization of queries is another function of the
DML compiler. Since a single question can typically be translated into a number
of evaluation plans. As a result, some optimization is needed to select the
evaluation plan with the lowest cost out of all the options. This process, known
as query optimization, is exclusively carried out by the DML compiler. Simply
put, query optimization determines the most effective technique to carry out a
query.

o Embedded DML Pre-compiler:

Before the query evaluation, the embedded DML commands in the application
program (such as SELECT, FROM, etc., in SQL) must be pre-compiled into
standard procedural calls (program instructions that the host language can
understand). Therefore, the DML statements which are embedded in an
application program must be converted into routine calls by the Embedded DML
Pre-compiler.

o Query Optimizer:
It starts by taking the evaluation plan for the question, runs it, and then returns
the result. Simply said, the query evaluation engine evaluates the SQL commands
used to access the database's contents before returning the result of the query. In
a nutshell, it is in charge of analyzing the queries and running the object code that
the DML Compiler produces. Apache Drill, Presto, and other Query Evaluation
Engines are a few examples.

2. Storage Manager:

An application called Storage Manager acts as a conduit between the queries

made and the data kept in the database. Another name for it is Database Control
System. By applying the restrictions and running the DCL instructions, it keeps
the database's consistency and integrity. It is in charge of retrieving, storing,
updating, and removing data from the database.

Components of Storage Manager

Following are the components of Storage Manager:
 o Integrity Manager:
Whenever there is any change in the database, the Integrity manager will manage
the integrity constraints.

o Authorization Manager:
Authorization manager verifies the user that he is valid and authenticated for the
specific query or request.

o File Manager:
All the files and data structure of the database are managed by this component.

o Transaction Manager:
It is responsible for making the database consistent before and after the
transactions. Concurrent processes are generally controlled by this component.

o Buffer Manager:
The transfer of data between primary and main memory and managing the cache
memory is done by the buffer manager.

3. Disk Storage

A DBMS can use various kinds of Data Structures as a part of physical system
implementation in the form of disk storage.

Components of Disk Storage

Following are the components of Disk Manager:

o Data Dictionary:
It contains the metadata (data of data), which means each object of the database
has some information about its structure. So, it creates a repository which
contains the details about the structure of the database object.

o Data Files:
This component stores the data in the files.

o Indices:
These indices are used to access and retrieve the data in a very fast and efficient
way.

Database Languages in DBMS

Database language refers to the specific types of commands or instructions

used to communicate with a database. It helps users or applications to create,
manage, update, and retrieve data from a database. database language is like a
set of tools that help you talk to and work with the data stored in a database.
There are different types of database languages, including:
Types of Database Languages in DBMS
There are different types of database languages, including DDL, DCL,
DML, and TCL. Each category language is used for a different purpose Below
I explain each category with proper examples.

Type of Database Language

DDL (Data Definition Language)
• The DDL stands for Data Definition Language, Which is used to
define the database's internal structure and Pattern of the Database.
• The DDL is used for creating tables, indexes, constraints, and schema
in the Database.
• By using DDL statements we can able to create the architecture of the
required database.
Below I list out types of database commands that are used in DDL. By using
those queries we can able to perform the DDL on the Database.
• Create: It is used to create objects in the database
• Alter: It is used to change or alter the structure of the database objects
• Drop: It is used to delete objects from the database
• Truncate: It is used to remove all records from a table
• Rename: It is used to rename the object in the database
• Comment: It is used for comments on the data dictionary.
Now I will explain each command with examples for better understanding the
concepts.
Create Command
It is one of the commands in DDL that is used for creating objects the database
means creating Tables, Users, Triggers, functions, and other objects. Here I will
show how to create a table by using create command from DDL.
Syntax
CREATE TABLE Students (
column1 INT,
column2 VARCHAR(50),
column3 INT
);
Example

create table
Alter Command
It is one of the commands in DDL which is used to change or alter the structure
of the database or table. I already created a student table with some columns you
can see in the above image. Now by using the Alter command, I add a new
column that weight to the student's table.
Syntax
ALTER TABLE Students ADD column_name;
Example

Alter command
Drop Command
The DROP command in a Database Management System (DBMS) is used to
permanently remove a database object, such as a table, database, or index, from
the system. When you use the DROP command, all the data and structure
associated with that object are deleted, and this action cannot be undone
Syntax
DROP Table Table_name;
Example

drop
Truncate Command
The TRUNCATE command in a Database Management System (DBMS) is
used to quickly remove all records from a table, while keeping the table structure
intact. Unlike the DELETE command, which can remove specific rows and can
be rolled back, TRUNCATE is a more efficient way to delete all rows in a table
without logging individual row deletions.
Syntax
TRUNCATE TABLE table_name;
Example

truncate
Rename Command
The RENAME command in a Database Management System (DBMS) is used
to change the name of a database object, such as a table, column, or index. This
command is helpful when you want to give a more meaningful or appropriate
name to an object without needing to recreate it.
Syntax:
ALTER TABLE Old_Table_Name RENAME TO New_Table_Name;
Example

Rename
Comment In SQL
In SQL, a comment is a piece of text within your SQL code that is not executed
as part of the command. Comments are useful for adding explanations, notes, or
reminders about what specific parts of the code do. This helps make your code
more readable and maintainable, especially when working in a team or revisiting
your code later.
Single Line
-- This is a single-line comment
Multi-line comment
/*
This is a
multi-line comment
*/
DCL (Data Control Language)
The DCL stands for Data Control Language means these commands are used to
retrieve the saved data from the database. And one more thing is the DCL
execution is Transactional which means It has rollback parameters. we have two
tasks under the Data Control Language below I listed them
• Grant: It is used to give user access to the database
• Revoke: It is used to take back access or permissions from the user
Now I will explain these commands with proper examples for better
understanding
Grant Command
The GRANT command in a Database Management System (DBMS) is used to
provide specific permissions or privileges to users or roles. This command
allows administrators to control access to database objects, ensuring that only
authorized users can perform certain actions, such as selecting, inserting,
updating, or deleting data.
Syntax
GRANT privileges
ON object
TO user_or_role [WITH GRANT OPTION];
Example
GRANT SELECT, INSERT ON students TO user;
Revoke Command
The REVOKE command in a Database Management System (DBMS) is used
to remove previously granted permissions or privileges from users or roles. This
command is essential for managing access control, ensuring that users do not
have more privileges than necessary to perform their tasks.
Syntax
REVOKE privileges ON object FROM user_or_role;
Example
REVOKE ALL PRIVILEGES ON students FROM user;

DML (Data Manipulation Language)

The Data Manipulation Language is used to Manipulate the data in the database
by using different commands. In this category we can able to perform Insert new
data into Table, Update existing data in the Table, Delete Data from the Table,
and other functions we can perform on data by using these DML commands.
Below I listed those commands for your reference
• Select: It is used to select data from the Table based on the
requirements
• Insert: It is used for Inserting data into an existing table
• Update: It is used to update data in the Table based on the requirement
 • Delete: It is used to delete data from the Table
• Merge: It is used for upsert operations
• Call: It is used to call a structured query language or Java sub-program
• Lock Table: It can control the concurrency
Now I will explain each command with proper example for better understanding
the concept.
Select Command
The SELECT command in SQL (Structured Query Language) is used to
retrieve data from one or more tables in a database. It is one of the most
commonly used commands in SQL, allowing users to specify exactly what data
they want to see and how they want it organized.
Syntax
SELECT * FROM Table_Name
Example

Select Command
Insert Command
The INSERT command in SQL (Structured Query Language) is used to add
new records or rows to a table in a database. This command is essential for
populating a database with data and can be used to insert data into all or specific
columns of a table.
Syntax
INSERT INTO Table_Name (Column 1, Column 2, Column 3, Column 4)
VALUES (Value 1, Value 2,Value 3, Value 4);
Example

Insert command
Update Command
The UPDATE command in SQL (Structured Query Language) is used to
modify existing records in a table. This command allows users to change the
values of one or more columns for specific rows based on specified criteria.
Syntax
UPDATE Table_Name SET Name = 'New_Value' WHERE Name =
'Ola_Value';
Example

Update
Delete Command
The DELETE command in SQL (Structured Query Language) is used to
remove existing records from a table in a database. This command is essential
for managing data, allowing users to delete specific rows based on certain
criteria.
Syntax
DELETE FROM Table_Name WHERE Column = Value;
Example

Delete Command
Merge Command
The Merge command is used to perform upsert(update or insert) operation
means It inserts rows that doesn't exist and updates rows that do.
Example
MERGE INTO target_table AS target
USING source_table AS source
ON ([Link] = [Link])
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES ([Link], [Link]);
CALL Command
The Call command is used to call the user defined functions means it is used to
execute a stored procedure, which is a set of precompiled SQL statements that
can be executed as a single unit.
Example
CALL user_defined_function(parameter 1, parameter 2);
LOCK TABLE
The lock table command is used to lock the table for preventing access from the
others for same Table
Syntax
LOCK TABLE your_table IN EXCLUSIVE MODE;
Example
LOCK TABLE ClassMembers IN EXCLUSIVE MODE;

TCL ( Transaction Control Language )

The TCL full form is Transaction Control Language commands are used to run
the changes made by the DML commands one more thing is TCL can be grouped
into a logical transaction. We have two different commands in this category I
listed them for reference.
• Commit: It is used to save the transaction on the Database. These are
very useful in the banking sector.
• Rollback: It is used to restore the database to its original state from
the last commit. This command also plays an important role in
Banking Sectors.
Now I will explain these two commands for your reference. And I provide
proper examples below I provide those example
Commit Command
The Commit command is used to save the transaction in the database and
changes are made by the DML statements. Now I will provide the example
below Here I used ClassMembers Table. Here I inserted new data into the Table
then I committed It.
Syntax
Database Operation
Commit
Example

commit
ROLLBACK Command
The rollback command is used to restore the database to its original state from
the last commit. Here I provide the example and in this, I already commit what
you can see above, Now I delete one row from the Table and then again roll
back the previous database state.
Syntax
ROLLBACK;
Example

rollback

DBA and its functions

A Database Administrator (DBA) is an individual or person responsible for
controlling, maintaining, coordinating, and operating a database management
system. Managing, securing, and taking care of the database systems is a prime
responsibility. They are responsible and in charge of authorizing access to the
database, coordinating, capacity, planning, installation, and monitoring uses,
and acquiring and gathering software and hardware resources as and when
needed. Their role also varies from configuration, database design, migration,
security, troubleshooting, backup, and data recovery. Database administration
is a major and key function in any firm or organization that is relying on one or
more databases. They are overall commanders of the Database system.

Importance of Database Administrator (DBA)

• Database Administrator manages and controls three levels of database
internal level, conceptual level, and external level of Database
management system architecture and in discussion with the
comprehensive user community, gives a definition of the world view
of the database. It then provides an external view of different users and
applications.
• Database Administrator ensures held responsible to maintain integrity
and security of database restricting from unauthorized users. It grants
permission to users of the database and contains a profile of each and
every user in the database.
 • Database Administrators are also held accountable that the database is
protected and secured and that any chance of data loss keeps at a
minimum.
• Database Administrator is solely responsible for reducing the risk of
data loss as it backup the data at regular intervals.
Role and Duties of Database Administrator (DBA)
• Decides Hardware: They decide on economical hardware, based on
cost, performance, and efficiency of hardware, and best suits the
organization. It is hardware that is an interface between end users and
the database.
• Manages Data Integrity and Security: Data integrity needs to be
checked and managed accurately as it protects and restricts data from
unauthorized use. DBA eyes on relationships within data to maintain
data integrity.
• Database Accessibility: Database Administrator is solely responsible
for giving permission to access data available in the database. It also
makes sure who has the right to change the content.
• Database Design: DBA is held responsible and accountable for
logical, physical design, external model design, and integrity and
security control.
• Database Implementation: DBA implements DBMS and checks
database loading at the time of its implementation.
• Query Processing Performance: DBA enhances query processing by
improving speed, performance, and accuracy.
• Tuning Database Performance: If the user is not able to get data
speedily and accurately then it may lose organization’s business. So
by tuning SQL commands DBA can enhance the performance of the
database.
Various Responsibilities of Database Administrator (DBA)
• Responsible for designing overall database schema (tables & fields).
• To select and install database software and hardware.
• Responsible for deciding on access methods and data storage.
• DBA selects appropriate DBMS software like oracle, SQL server or
MySQL.
• Used in designing recovery procedures.
• DBA decides the user access level and security checks for accessing,
modifying or manipulating data.
• DBA is responsible for specifying various techniques for monitoring
the database performance.
• DBA is responsible for operation managements.
• The operation management deals with the data problems which arises
on day to day basis, and the responsibilities include are:
1. Investigating if any error is been found in the data.
2. Supervising of restart and recovery procedures in case of any event
failure.
3. Supervising reorganization of the databases.
4. Controlling and handling all periodic dumps of data.
Introduction of ER Model

Peter Chen developed the ER diagram in 1976. The ER model was created to
provide a simple and understandable model for representing the structure and
logic of databases. It has since evolved into variations such as the Enhanced ER
Model and the Object Relationship Model
The Entity Relational Model is a model for identifying entities to be represented
in the database and representation of how those entities are related. The ER data
model specifies enterprise schema that represents the overall logical structure of
a database graphically.
The Entity Relationship Diagram explains the relationship among the entities
present in the database. ER models are used to model real-world objects like a
person, a car, or a company and the relation between these real-world objects.
In short, the ER Diagram is the structural format of the database.
Why Use ER Diagrams In DBMS?
• ER diagrams represent the E-R model in a database, making them easy
to convert into relations (tables).
• ER diagrams provide the purpose of real-world modeling of objects
which makes them intently useful.
• ER diagrams require no technical knowledge and no hardware support.
• These diagrams are very easy to understand and easy to create even
for a naive user.
• It gives a standard solution for visualizing the data logically.
A solid grasp of the ER Model is crucial for excelling in exams like GATE,
where database management is a key topic. To enhance your understanding and
boost your exam preparation, consider the GATE CS Self-Paced Course . This
course offers in-depth coverage of the ER Model and other essential database
concepts, helping you build the knowledge needed to succeed in your exams.
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:
• 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.
• Double Rectangle: Double Rectangle represents a Weak Entity.
Symbols used in ER Diagram
Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in
a Database System.

Components of ER Diagram
What is Entity?
An Entity may be an object with a physical existence – a particular person, car,
house, or employee – or it may be an object with a conceptual existence – a
company, a job, or a university course.
What is Entity Set?
An Entity is an object of Entity Type and a set of all entities is called an entity
set. For Example, E1 is an entity having Entity Type Student and the set of all
students is called Entity Set. In ER diagram, Entity Type is represented as:
Entity Set
We can represent the entity set in ER Diagram but can’t represent entity in ER
Diagram because entity is row and column in the relation and ER Diagram is
graphical representation of data.
Types of Entity
There are two types of entity:
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong Entity does
not depend on other Entity in the Schema. It has a primary key, that helps in
identifying it uniquely, and it is represented by a rectangle. These are called
Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each entity in the
entity set. But some entity type exists for which key attributes can’t be defined.
These are called Weak Entity types .
For Example, A company may store the information of dependents (Parents,
Children, Spouse) of an Employee. But the dependents can’t exist without the
employee. So Dependent will be a Weak Entity Type and Employee will be
Identifying Entity type for Dependent, which means it is Strong Entity Type .
A weak entity type is represented by a Double Rectangle. The participation of
weak entity types is always total. The relationship between the weak entity type
and its identifying strong entity type is called identifying relationship and it is
represented by a double diamond.

Strong Entity and Weak Entity

What is Attributes?
Attributes are the properties that define the entity type. For example, Roll_No,
Name, DOB, Age, Address, and Mobile_No are the attributes that define entity
type Student. In ER diagram, the attribute is represented by an oval.
Attribute
Types of Attributes
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 underlying lines.

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.

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:
Entity and Attributes
Relationship Type and Relationship Set
A Relationship Type represents the association between entity types. For
example, ‘Enrolled in’ is a relationship type that exists between entity type
Student and Course. In ER diagram, the relationship type is represented by a
diamond and connecting the entities with lines.

Entity-Relationship Set
A set of relationships of the same type is known as a relationship set. The
following relationship set depicts S1 as enrolled in C2, S2 as enrolled in C1, and
S3 as registered in C3.

Relationship Set
Degree of a Relationship Set
The number of different entity sets participating in a relationship set is called
the degree of a relationship set.
1. Unary Relationship: When there is only ONE entity set participating in a
relation, the relationship is called a unary relationship. For example, one person
is married to only one person.

Unary Relationship
2. Binary Relationship: When there are TWO entities set participating in a
relationship, the relationship is called a binary relationship. For example, a
Student is enrolled in a Course.

Binary Relationship
3. Ternary Relationship: When there are three entity sets participating in a
relationship, the relationship is called a ternary relationship.
4. N-ary Relationship: When there are n entities set participating in a
relationship, the relationship is called an n-ary relationship.
What is Cardinality?
The number of times an entity of an entity set participates in a relationship set
is known as cardinality . Cardinality can be of different types:
1. One-to-One: When each entity in each entity set can take part only once in
the relationship, the cardinality is one-to-one. Let us assume that a male can
marry one female and a female can marry one male. So the relationship will be
one-to-one.
the total number of tables that can be used in this is 2.

one to one cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many: In one-to-many mapping as well where each entity can be
related to more than one entity and the total number of tables that can be used
in this is 2. Let us assume that one surgeon department can accommodate many
doctors. So the Cardinality will be 1 to M. It means one department has many
Doctors.
total number of tables that can used is 3.
One to many cardinality
Using sets, one-to-many cardinality can be represented as:

Set Representation of One-to-Many

3. Many-to-One: When entities in one entity set can take part only once in the
relationship set and entities in other entity sets can take part more than once in
the relationship set, cardinality is many to one. Let us assume that a student can
take only one course but one course can be taken by many students. So the
cardinality will be n to 1. It means that for one course there can be n students
but for one student, there will be only one course.
The total number of tables that can be used in this is 3.

many to one cardinality

Using Sets, it can be represented as:

Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has been taken by
many students.
4. Many-to-Many: When entities in all entity sets can take part more than once
in the relationship cardinality is many to many. Let us assume that a student can
take more than one course and one course can be taken by many students. So
the relationship will be many to many.
the total number of tables that can be used in this is 3.

many to many cardinality

Using Sets, it can be represented as:

Many-to-Many Set Representation

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled
by S1, S3, and S4. So it is many-to-many relationships.
Participation Constraint
Participation Constraint is applied to the entity participating in the relationship
set.
1. Total Participation – Each entity in the entity set must participate in the
relationship. If each student must enroll in a course, the participation of students
will be total. Total participation is shown by a double line in the ER diagram.
2. Partial Participation – The entity in the entity set may or may NOT
participate in the relationship. If some courses are not enrolled by any of the
students, the participation in the course will be partial.
The diagram depicts the ‘Enrolled in’ relationship set with Student Entity set
having total participation and Course Entity set having partial participation.

Total Participation and Partial Participation

Using Set, it can be represented as,
Set representation of Total Participation and Partial Participation
Every student in the Student Entity set participates in a relationship but there
exists a course C4 that is not taking part in the relationship.

Convert ER Diagrams to Tables in DBMS

Step 1: Analyze the Entities

• Identify each distinct entity in the diagram represented by a rectangle.
• Examine the attributes associated with each entity which are its
characteristics represented by ellipses within the rectangle.
Step 2: Create Separate Tables for Each Entity
• For each entity, create a corresponding table in the database with
each attribute as a column in the table.
• Assign the primary key as a column in the respective table.
Step 3: Map Relationships
• For one-to-many relationships, add a foreign key in the "many" table
referencing the "one" table's primary key.
• For many-to-many, create a linking table with foreign keys to both
related tables.
Step 4: Normalize the Tables
• Analyze the tables for redundancies and potential data
inconsistencies.
• Apply normalization techniques like decomposition to split tables and
eliminate redundancy, improving data integrity and reducing storage
space.
Step 5: Verify and Refine
• Review the created tables and relationships for accuracy and
efficiency.
• Refine the schema as needed based on further analysis or specific
requirements of your database.

Reduction of ER diagram to Table

The database can be represented using the notations, and these notations can be
reduced to a collection of tables.
In the database, every entity set or relationship set can be represented in tabular
form.

The ER diagram is given below:

There are some points for converting the ER diagram to the table:

o Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE
forms individual tables.

o All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the
column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID
form the column of COURSE table and so on.

o A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and
LECTURE_ID are the key attribute of the entity.

o The multivalued attribute is represented by a separate table.

In the student table, a hobby is a multivalued attribute. So it is not possible to
represent multiple values in a single column of STUDENT table. Hence we create
a table STUD_HOBBY with column name STUDENT_ID and HOBBY. Using
both the column, we create a composite key.
 o Composite attribute represented by components.
In the given ER diagram, student address is a composite attribute. It contains
CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these
attributes can merge as an individual column.

o Derived attributes are not considered in the table.

In the STUDENT table, Age is the derived attribute. It can be calculated at any
point of time by calculating the difference between current date and Date of Birth.

Using these rules, you can convert the ER diagram to tables and columns and
assign the mapping between the tables. Table structure for the given ER diagram
is as below:

Figure: Table structure

UNIT II

Relational Data Model

The relational data model was introduced by E. F. Codd in 1970. Currently, it is

the most widely used data model.

The relational model has provided the basis for:

• Research on the theory of data/relationship/constraint

• Numerous database design methodologies
• The standard database access language called structured query language
(SQL)
• Almost all modern commercial database management systems

The relational data model describes the world as “a collection of inter-related

relations (or tables).”

Fundamental Concepts in the Relational Data Model

Relation

A relation, also known as a table or file, is a subset of the Cartesian product of a

list of domains characterized by a name. And within a table, each row represents
a group of related data values. A row, or record, is also known as a tuple. The
columns in a table is a field and is also referred to as an attribute. You can also
think of it this way: an attribute is used to define the record and a record contains
a set of attributes.

The steps below outline the logic between a relation and its domains.

1. Given n domains are denoted by D1, D2, … Dn

2. And r is a relation defined on these domains
3. Then r ⊆ D1×D2×…×Dn
Table

A database is composed of multiple tables and each table holds the data. Figure
7.1 shows a database that contains three tables.

Figure 7.1. Database with three tables.

Column

A database stores pieces of information or facts in an organized way.

Understanding how to use and get the most out of databases requires us to
understand that method of organization.
The principal storage units are called columns or fields or attributes. These house
the basic components of data into which your content can be broken down. When
deciding which fields to create, you need to think generically about your
information, for example, drawing out the common components of the
information that you will store in the database and avoiding the specifics that
distinguish one item from another.

Look at the example of an ID card in Figure 7.2 to see the relationship between
fields and their data.

Figure 7.2. Example of an ID card by A. Watt.

Domain

A domain is the original sets of atomic values used to model data. By atomic
value, we mean that each value in the domain is indivisible as far as the relational
model is concerned. For example:
 • The domain of Marital Status has a set of possibilities: Married, Single,
Divorced.
• The domain of Shift has the set of all possible days: {Mon, Tue, Wed…}.
• The domain of Salary is the set of all floating-point numbers greater than
0 and less than 200,000.
• The domain of First Name is the set of character strings that represents
names of people.

In summary, a domain is a set of acceptable values that a column is allowed to

contain. This is based on various properties and the data type for the column. We
will discuss data types in another chapter.

Records

Just as the content of any one document or item needs to be broken down into its
constituent bits of data for storage in the fields, the link between them also needs
to be available so that they can be reconstituted into their whole form. Records
allow us to do this. Records contain fields that are related, such as a customer or
an employee. As noted earlier, a tuple is another term used for record.
Records and fields form the basis of all databases. A simple table gives us the
clearest picture of how records and fields work together in a database storage
project.

Figure 7.3. Example of a simple table by A. Watt.

The simple table example in Figure 7.3 shows us how fields can hold a range of
different sorts of data. This one has:

• A Record ID field: this is an ordinal number; its data type is an integer.

• A PubDate field: this is displayed as day/month/year; its data type is date.
• An Author field: this is displayed as Initial. Surname; its data type is text.
• A Title field text: free text can be entered here.

You can command the database to sift through its data and organize it in a
particular way. For example, you can request that a selection of records be limited
by date: 1. all before a given date, 2. all after a given date or 3. all between two
given dates. Similarly, you can choose to have records sorted by date. Because
the field, or record, containing the data is set up as a Date field, the database reads
the information in the Date field not just as numbers separated by slashes, but
rather, as dates that must be ordered according to a calendar system.

Degree

The degree is the number of attributes in a table. In our example in Figure 7.3,
the degree is 4.

Properties of a Table

• A table has a name that is distinct from all other tables in the database.
• There are no duplicate rows; each row is distinct.
• Entries in columns are atomic. The table does not contain repeating groups
or multivalued attributes.
• Entries from columns are from the same domain based on their data type
including:
o number (numeric, integer, float, smallint,…)
o character (string)
o date
o logical (true or false)
• Operations combining different data types are disallowed.
• Each attribute has a distinct name.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.

Keys in Relational Model

Keys are one of the basic requirements of a relational database model. It is

widely used to identify the tuples(rows) uniquely in the table. We also use keys
to set up relations amongst various columns and tables of a relational database.
Different Types of Database Keys
• Candidate Key
• Primary Key
• Super Key
• Alternate Key
• Foreign Key
• Composite Key
To master the concepts of keys and other essential topics in databases, consider
enrolling in the GATE CS Self-Paced course . This course provides in-depth
coverage of core subjects, helping you build a strong foundation for GATE and
other competitive exams, while enhancing your understanding of critical
computer science principles.
Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as a
candidate key. For Example, STUD_NO in STUDENT relation.
• It is a minimal super key.
 • It is a super key with no repeated data is called a candidate key.
• The minimal set of attributes that can uniquely identify a record.
• It must contain unique values.
• It can contain NULL values.
• Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only one primary key.
• The value of the Candidate Key is unique and may be null for a tuple.
• There can be more than one candidate key in a relationship.
Example:
STUD_NO is the candidate key for relation STUDENT.
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

• The candidate key can be simple (having only one attribute) or

composite as well.
Example:
{STUD_NO, COURSE_NO} is a composite
candidate key for relation STUDENT_COURSE.
Table STUDENT_COURSE
STUD_NO TEACHER_NO COURSE_NO

1 001 C001

2 056 C005

Note: In SQL Server a unique constraint that has a nullable column, allows the
value ‘ null ‘ in that column only once . That’s why the STUD_PHONE
attribute is a candidate here, but can not be a ‘null’ value in the primary key
attribute.
Primary Key
There can be more than one candidate key in relation out of which one can be
chosen as the primary key. For Example, STUD_NO, as well as
STUD_PHONE, are candidate keys for relation STUDENT but STUD_NO can
be chosen as the primary key (only one out of many candidate keys).
• It is a unique key.
• It can identify only one tuple (a record) at a time.
• It has no duplicate values, it has unique values.
 • It cannot be NULL.
• Primary keys are not necessarily to be a single column; more than one
column can also be a primary key for a table.
Example:
STUDENT table -> Student(STUD_NO, SNAME,
ADDRESS, PHONE) , STUD_NO is a primary key
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Super Key
The set of attributes that can uniquely identify a tuple is known as Super Key.
For Example, STUD_NO, (STUD_NO, STUD_NAME), etc. A super key is a
group of single or multiple keys that identifies rows in a table. It supports NULL
values.
• Adding zero or more attributes to the candidate key generates the super
key.
• A candidate key is a super key but vice versa is not true.
• Super Key values may also be NULL.
Example:
Consider the table shown above.
STUD_NO+PHONE is a super key.

Relation between Primary Key, Candidate Key, and Super Key

Alternate Key
The candidate key other than the primary key is called an alternate key .
• All the keys which are not primary keys are called alternate keys.
• It is a secondary key.
• It contains two or more fields to identify two or more records.
• These values are repeated.
• Eg:- SNAME, and ADDRESS is Alternate keys
Example:
Consider the table shown above.
STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key
(only one out of many candidate keys).

Primary Key, Candidate Key, and Alternate Key

Foreign Key
If an attribute can only take the values which are present as values of some other
attribute, it will be a foreign key to the attribute to which it refers. The relation
which is being referenced is called referenced relation and the corresponding
attribute is called referenced attribute. The referenced attribute of the referenced
relation should be the primary key to it.
• It is a key it acts as a primary key in one table and it acts as
secondary key in another table.
• It combines two or more relations (tables) at a time.
• They act as a cross-reference between the tables.
• For example, DNO is a primary key in the DEPT table and a non-key
in EMP
Example:
Refer Table STUDENT shown above.
STUD_NO in STUDENT_COURSE is a
foreign key to STUD_NO in STUDENT relation.
Table STUDENT_COURSE
STUD_NO TEACHER_NO COURSE_NO

1 005 C001

2 056 C005

It may be worth noting that, unlike the Primary Key of any given relation,
Foreign Key can be NULL as well as may contain duplicate tuples i.e. it need
not follow uniqueness constraint. For Example, STUD_NO in the
STUDENT_COURSE relation is not unique. It has been repeated for the first
and third tuples. However, the STUD_NO in STUDENT relation is a primary
key and it needs to be always unique, and it cannot be null.

Relation between Primary Key and Foreign Key

Composite Key
Sometimes, a table might not have a single column/attribute that uniquely
identifies all the records of a table. To uniquely identify rows of a table, a
combination of two or more columns/attributes can be used. It still can give
duplicate values in rare cases. So, we need to find the optimal set of attributes
that can uniquely identify rows in a table.
• It acts as a primary key if there is no primary key in a table
• Two or more attributes are used together to make a composite key .
• Different combinations of attributes may give different accuracy in
terms of identifying the rows uniquely.
Example:
FULLNAME + DOB can be combined
together to access the details of a student.

Different Types of Keys

RDBMS-

RDBMS stands for Relational Database Management Systems. A database is

an organized collection of data stored in a computer system and usually
controlled by a database management system (DBMS). The data in common
databases is modeled in tables, making querying and processing efficient.

What is RDBMS?
RDBMS stands for Relational Database Management Systems. It is a program
that allows us to create, delete, and update a relational database. A Relational
Database is a database system that stores and retrieves data in a tabular format
organized in the form of rows and columns. It is a smaller subset of DBMS
which was designed by E.F Codd in the 1970s. The major DBMSs like SQL,
My-SQL, and ORACLE are all based on the principles of relational DBMS.
Relational DBMS owes its foundation to the fact that the values of each table
are related to others. It has the capability to handle larger magnitudes of data
and simulate queries easily.

ACID Properties of DBMS

Atomicity:
By this, we mean that either the entire transaction takes place at once or doesn’t
happen at all. There is no midway i.e. transactions do not occur partially. Each
transaction is considered as one unit and either runs to completion or is not
executed at all. It involves the following two operations.
— Abort : If a transaction aborts, changes made to the database are not visible.
— Commit : If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Consider the following transaction T consisting of T1 and T2 : Transfer of 100
from account X to account Y .

If the transaction fails after completion of T1 but before completion of T2 .(

say, after write(X) but before write(Y) ), then the amount has been deducted
from X but not added to Y . This results in an inconsistent database state.
Therefore, the transaction must be executed in its entirety in order to ensure the
correctness of the database state.
Consistency:
This means that integrity constraints must be maintained so that the database is
consistent before and after the transaction. It refers to the correctness of a
database. Referring to the example above,
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700 .
Total after T occurs = 400 + 300 = 700 .
Therefore, the database is consistent . Inconsistency occurs in
case T1 completes but T2 fails. As a result, T is incomplete.
Isolation:
This property ensures that multiple transactions can occur concurrently without
leading to the inconsistency of the database state. Transactions occur
independently without interference. Changes occurring in a particular
transaction will not be visible to any other transaction until that particular
change in that transaction is written to memory or has been committed. This
property ensures that the execution of transactions concurrently will result in a
state that is equivalent to a state achieved these were executed serially in some
order.
Let X = 500, Y = 500.
Consider two transactions T and T”.

Suppose T has been executed till Read (Y) and then T’’ starts. As a result,
interleaving of operations takes place due to which T’’ reads the correct value
of X but the incorrect value of Y and sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of the transaction:
T: (X+Y = 50, 000 + 450 = 50, 450) .
This results in database inconsistency, due to a loss of 50 units. Hence,
transactions must take place in isolation and changes should be visible only after
they have been made to the main memory.
Durability:
This property ensures that once the transaction has completed execution, the
updates and modifications to the database are stored in and written to disk and
they persist even if a system failure occurs. These updates now become
permanent and are stored in non-volatile memory. The effects of the transaction,
thus, are never lost.
Some important points:
Property Responsibility for maintaining properties

Atomicity Transaction Manager

Consistency Application programmer

Isolation Concurrency Control Manager

Property Responsibility for maintaining properties

Durability Recovery Manager

The ACID properties, in totality, provide a mechanism to ensure the correctness

and consistency of a database in a way such that each transaction is a group of
operations that acts as a single unit, produces consistent results, acts in isolation
from other operations, and updates that it makes are durably stored.

DBMS Integrity Constraints

Integrity constraints are the set of predefined rules that are used to maintain the
quality of information. Integrity constraints ensure that the data insertion, data
updating, data deleting and other processes have to be performed in such a way
that the data integrity is not affected. They act as guidelines ensuring that data in
the database remain accurate and consistent. So, integrity constraints are used to
protect databases. The various types of integrity constraints are
Types of Integrity Constraints:
• Domain Constraints
• Not-Null Constraints
• Entity integrity Constraints
• Key Constraints
• Primary Key Constrains
• Referential integrity constraints

•
Domain Constraints
These are defined as the definition of valid set of values for an attribute. The data
type of domain include string, char, time, integer, date, currency etc. The value
of the attribute must be available in comparable domains.
Example:
Student_Id Name Semester Age

21CSE100 Ramesh 5th 20

21CSE101 Kamlesh 5th 21

21CSE102 Aakash 5th 22

Student_Id Name Semester Age

21CSE103 Mukesh 5th 20

Not-Null Constraints
It specifies that within a tuple, attributes overs which not-null constraint is
specified must not contain any null value.
Example:
Let, the not-null constraint be specified on the "Semester" attribute in the
relation/table given below, then the data entry of 4th tuple will violate this
integrity constraint, because the "Semester" attribute in this tuple contains null
value. To make this database instance a legal instance, its entry must not be
allowed by database management system.
Student_id Name Semester Age

21CSE100 Ramesh 5th 20

21CSE101 Kamlesh 5th 21

21CSE102 Akash 5th 22

21CSE103 Mukesh 20

Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value
because primary key is used to determine individual rows in a relation uniquely,
if primary key contains null value then we cannot identify those rows. A table can
contain null value in it except primary key field.
Example:
It is not allowed because it is containing primary key as NULL value.
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

Mukesh 5th 20

Key Constraints
Keys are the entity set that are used to identify an entity within its entity
set uniquely. An entity set can contain multiple keys, bit out of them one key will
be primary key. A primary key is always unique, it does not contain any null value
in table.
Example:
Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

21CSE102 Mukesh 5th 20

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

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Akash 5th 22

21CSE103 Mukesh 5th 20

Referential integrity constraints

It can be specified between two tables. In case of referential integrity constraints,
if a Foreign key in Table 1 refers to Primary key of Table 2 then every value of
the Foreign key in Table 1 must be null or available in Table 2.
Example:
Here, in below example Block_No 22 entry is not allowed because it is not
present in 2nd table.
Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

Student_id Name Semester Block_No

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

Referential Integrity constraints in DBMS

A referential integrity constraint is also known as foreign key constraint. A
foreign key is a key whose values are derived from the Primary key of another
table.

The table from which the values are derived is known as Master or
Referenced Table and the Table in which values are inserted accordingly is
known as Child or Referencing Table, In other words, we can say that the table
containing the foreign key is called the child table, and the table containing
the Primary key/candidate key is called the referenced or parent table. When
we talk about the database relational model, the candidate key can be defined as
a set of attribute which can have zero or more attributes.

The syntax of the Master Table or Referenced table is:

1. CREATE TABLE Student (Roll int PRIMARY KEY, Name varchar(25)

, Course varchar(10) );
Here column Roll is acting as Primary Key, which will help in deriving the value
of foreign key in the child table.
 1. CREATE TABLE Subject (Roll int references Student, SubCode int, Sub
Name varchar(10) );

In the above table, column Roll is acting as Foreign Key, whose values are
derived using the Roll value of Primary key from Master table.

Foreign Key Constraint OR Referential Integrity constraint.

There are two referential integrity constraint:

Insert Constraint: Value cannot be inserted in CHILD Table if the value is not
lying in MASTER Table

Delete Constraint: Value cannot be deleted from MASTER Table if the value is
lying in CHILD Table

Suppose you wanted to insert Roll = 05 with other values of columns in

SUBJECT Table, then you will immediately see an error "Foreign key
Constraint Violated" i.e. on running an insertion command as:

nsert into SUBJECT values(5, 786, OS); will not be entertained by SQL due
to Insertion Constraint ( As you cannot insert value in a child table if the value
is not lying in the master table, since Roll = 5 is not present in the master table,
hence it will not be allowed to enter Roll = 5 in child table )
Similarly, if you want to delete Roll = 4 from STUDENT Table, then you will
immediately see an error "Foreign key Constraint Violated" i.e. on running a
deletion command as:

Delete from STUDENT where Roll = 4; will not be entertained by SQL due
to Deletion Constraint. ( As you cannot delete the value from the master table if
the value is lying in the child table, since Roll = 5 is present in the child table,
hence it will not be allowed to delete Roll = 5 from the master table, lets if
somehow we managed to delete Roll = 5, then Roll = 5 will be available in child
table which will ultimately violate insertion constraint. )

ON DELETE CASCADE.

As per deletion constraint: Value cannot be deleted from the MASTER Table if
the value is lying in CHILD Table. The next question comes can we delete the
value from the master table if the value is lying in the child table without violating
the deletion constraint? i.e. The moment we delete the value from the master table
the value corresponding to it should also get deleted from the child table.

The answer to the above question is YES, we can delete the value from the master
table if the value is lying in the child table without violating the deletion
constraint, we have to do slight modification while creating the child table, i.e.
by adding on delete cascade.

TABLE SYNTAX

1. CREATE TABLE Subject (Roll int references Student on delete cascade,

SubCode int, SubName varchar(10) );
In the above syntax, just after references keyword( used for creating foreign key),
we have added on delete cascade, by adding such now, we can delete the value
from the master table if the value is lying in the child table without violating
deletion constraint. Now if you wanted to delete Roll = 5 from the master table
even though Roll = 5 is lying in the child table, it is possible because the moment
you give the command to delete Roll = 5 from the master table, the row having
Roll = 5 from child table will also get deleted.
The above two tables STUDENT and SUBJECT having four values each are
shown, now suppose you are looking to delete Roll = 4 from STUDENT( Master
) Table by writing a SQL command: delete from STUDENT where Roll = 4;

The moment SQL execute the above command the row having Roll = 4 from
SUBJECT( Child ) Table will also get deleted, The resultant STUDENT and
SUBJECT table will look like:

From the above two tables STUDENT and SUBJECT, you can see that in both
the table Roll = 4 gets deleted at one go without violating deletion constraint.
ON DELETE NULL.

As per deletion constraint: Value cannot be deleted from the MASTER Table if
the value is lying in CHILD Table. The next question comes can we delete the
value from the master table if the value is lying in the child table without violating
the deletion constraint? i.e. The moment we delete the value from the master table
the value corresponding to it should also get deleted from the child table or can
be replaced with the NULL value.

The answer to the above question is YES, we can delete the value from the master
table if the value is lying in child table without violating deletion constraint by
inserting NULL in the foreign key, we have to do slight modification while
creating child table, i.e. by adding on delete null.

TABLE SYNTAX:

CREATE TABLE Subject (Roll int references Student on delete null, SubC
ode int, SubName varchar(10) );
In the above syntax, just after references keyword( used for creating foreign key),
we have added on delete null, by adding such now, we can delete the value from
the master table if the value is lying in the child table without violating deletion
constraint. Now if you wanted to delete Roll = 4 from the master table even
though Roll =4 is lying in the child table, it is possible because the moment you
give the command to delete Roll = 4 from the master table, the row having Roll
= 4 from child table will get replaced by a NULL value.
The above two tables STUDENT and SUBJECT having four values each are
shown, now suppose you are looking to delete Roll = 4 from STUDENT( Master
) Table by writing a SQL command: delete from STUDENT where Roll = 4;

The moment SQL execute the above command the row having Roll = 4 from
SUBJECT( Child ) Table will get replaced by a NULL value, The
resultant STUDENT and SUBJECT table will look like:

From the above two tables STUDENT and SUBJECT, you can see that in table
STUDENT Roll = 4 get deleted while the value of Roll = 4 in the SUBJECT table
is replaced by NULL. This proves that the Foreign key can have null values. If in
the case in SUBJECT Table, column Roll is Primary Key along with Foreign Key
then in that case we could not make a foreign key to have NULL values.
UNIT III

What Is Normalization in SQL?

Normalization is the process to eliminate data redundancy and enhance data

integrity in the table. Normalization also helps to organize the data in the
database. It is a multi-step process that sets the data into tabular form and removes
the duplicated data from the relational tables.

Normalization organizes the columns and tables of a database to ensure that

database integrity constraints properly execute their dependencies. It is a
systematic technique of decomposing tables to eliminate data redundancy
(repetition) and undesirable characteristics like Insertion, Update, and Deletion
anomalies.

In 1970 Edgar F. Codd defined the First Normal Form.

Now let's understand the types of Normal forms with the help of examples.

1st Normal Form (1NF)

• A table is referred to as being in its First Normal Form if atomicity of

the table is 1.
• Here, atomicity states that a single cell cannot hold multiple values. It
must hold only a single-valued attribute.
 • The First normal form disallows the multi-valued attribute, composite
attribute, and their combinations.
Now you will understand the First Normal Form with the help of an example.

Below is a students’ record table that has information about student roll number,
student name, student course, and age of the student.

In the studentsrecord table, you can see that the course column has two values.
Thus it does not follow the First Normal Form. Now, if you use the First Normal
Form to the above table, you get the below table as a result.

By applying the First Normal Form, you achieve atomicity, and also every
column has unique values.

Before proceeding with the Second Normal Form, get familiar with Candidate
Key and Super Key.

Second Normal Form (2NF)

The first condition for the table to be in Second Normal Form is that the table has
to be in First Normal Form. The table should not possess partial dependency. The
partial dependency here means the proper subset of the candidate key should give
a non-prime attribute.

Now understand the Second Normal Form with the help of an example.
Consider the table Location:

The Location table possesses a composite primary key cust_id, storeid. The non-
key attribute is store_location. In this case, store_location only depends on
storeid, which is a part of the primary key. Hence, this table does not fulfill the
second normal form.

To bring the table to Second Normal Form, you need to split the table into two
parts. This will give you the below tables:

As you have removed the partial functional dependency from the location table,
the column store_location entirely depends on the primary key of that table,
storeid.

Third Normal Form (3NF)

• The first condition for the table to be in Third Normal Form is that the
table should be in the Second Normal Form.
• The second condition is that there should be no transitive dependency
for non-prime attributes, which indicates that non-prime attributes
(which are not a part of the candidate key) should not depend on other
non-prime attributes in a table. Therefore, a transitive dependency is a
functional dependency in which A → C (A determines C) indirectly,
because of A → B and B → C (where it is not the case that B → A).
 • The third Normal Form ensures the reduction of data duplication. It is
also used to achieve data integrity.
Below is a student table that has student id, student name, subject id, subject
name, and address of the student as its columns.

In the above student table, stu_id determines subid, and subid determines sub.
Therefore, stu_id determines sub via subid. This implies that the table possesses
a transitive functional dependency, and it does not fulfill the third normal form
criteria.

Now to change the table to the third normal form, you need to divide the table as
shown below:

As you can see in both the tables, all the non-key attributes are now fully
functional, dependent only on the primary key. In the first table, columns name,
subid, and addresses only depend on stu_id. In the second table, the sub only
depends on subid.

Boyce Codd Normal Form (BCNF)

Boyce Codd Normal Form is also known as 3.5 NF. It is the superior version of
3NF and was developed by Raymond F. Boyce and Edgar F. Codd to tackle
certain types of anomalies which were not resolved with 3NF.

The first condition for the table to be in Boyce Codd Normal Form is that the
table should be in the third normal form. Secondly, every Right-Hand Side (RHS)
attribute of the functional dependencies should depend on the super key of that
particular table.

For example :
You have a functional dependency X → Y. In the particular functional
dependency, X has to be the part of the super key of the provided table.

Consider the below subject table:

The subject table follows these conditions:

• Each student can enroll in multiple subjects.

• Multiple professors can teach a particular subject.
• For each subject, it assigns a professor to the student.
In the above table, student_id and subject together form the primary key because
using student_id and subject; you can determine all the table columns.
Another important point to be noted here is that one professor teaches only one
subject, but one subject may have two professors.
Which exhibit there is a dependency between subject and professor, i.e. subject
depends on the professor's name.
to
The table is in 1st Normal form as all the column names are unique, all values are
atomic, and all the values stored in a particular column are of the same domain.
The table also satisfies the 2nd Normal Form, as there is no Partial Dependency.
And, there is no Transitive Dependency; hence, the table also satisfies the 3rd
Normal Form.
This table follows all the Normal forms except the Boyce Codd Normal Form.
As you can see stuid, and subject forms the primary key, which means the subject
attribute is a prime attribute.
However, there exists yet another dependency - professor → subject.
BCNF does not follow in the table as a subject is a prime attribute, the professor
is a non-prime attribute.

To transform the table into the BCNF, you will divide the table into two parts.
One table will hold stuid which already exists and the second table will hold a
newly created column profid.
And in the second table will have the columns profid, subject, and professor,
which satisfies the BCNF.

What is Functional Dependency?

A functional dependency occurs when one attribute uniquely determines another
attribute within a relation. It is a constraint that describes how attributes in a
table relate to each other. If attribute A functionally determines attribute B we
write this as the A→B.
Functional dependencies are used to mathematically express relations among
database entities and are very important to understanding advanced concepts in
Relational Database Systems.
Example:
roll_no name dept_name dept_building

42 abc CO A4

43 pqr IT A3

44 xyz CO A4

45 xyz IT A3

46 mno EC B2

47 jkl ME B2

From the above table we can conclude some valid functional dependencies:
• roll_no → { name, dept_name, dept_building },→ Here, roll_no can
determine values of fields name, dept_name and dept_building, hence
a valid Functional dependency
• roll_no → dept_name , Since, roll_no can determine whole set of
{name, dept_name, dept_building}, it can determine its subset
dept_name also.
• dept_name → dept_building , Dept_name can identify the
dept_building accurately, since departments with different dept_name
will also have a different dept_building
• More valid functional dependencies: roll_no → name, {roll_no,
name} ⇢ {dept_name, dept_building}, etc.
Types of Functional Dependencies in DBMS
1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the
determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial
functional dependency
Example:
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the

dependent name is a subset of determinant set {roll_no,
name}. Similarly, roll_no → roll_no is also an example of trivial functional
dependency.

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of
the determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-
trivial functional dependency.
Example:
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, roll_no → name is a non-trivial functional dependency, since the

dependent name is not a subset of determinant roll_no. Similarly, {roll_no,
name} → age is also a non-trivial functional dependency, since age is not a
subset of {roll_no, name}
3. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set are not
dependent on each other. i.e. If a → {b, c} and there exists no functional
dependency between b and c, then it is called a multivalued functional
dependency.
For example,
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

45 abc 19

Here, roll_no → {name, age} is a multivalued functional dependency, since the

dependents name & age are not dependent on each other(i.e. name →
age or age → name doesn’t exist !)
4. Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly dependent on
determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a →
c. This is a transitive functional dependency.
For example,
enrol_no name dept building_no

42 abc CO 4

43 pqr EC 2

44 xyz IT 1

45 abc EC 2

Here, enrol_no → dept and dept → building_no. Hence, according to the

axiom of transitivity, enrol_no → building_no is a valid functional
dependency. This is an indirect functional dependency, hence called Transitive
functional dependency.
5. Fully Functional Dependency
In full functional dependency an attribute or a set of attributes uniquely
determines another attribute or set of attributes. If a relation R has attributes X,
Y, Z with the dependencies X->Y and X->Z which states that those
dependencies are fully functional.
6. Partial Functional Dependency
In partial functional dependency a non key attribute depends on a part of the
composite key, rather than the whole key. If a relation R has attributes X, Y, Z
where X and Y are the composite key and Z is non key attribute. Then X->Z is
a partial functional dependency in RDBMS.

Relational Decomposition

o When a relation in the relational model is not in appropriate normal form

then the decomposition of a relation is required.
o In a database, it breaks the table into multiple tables.
o If the relation has no proper decomposition, then it may lead to problems
like loss of information.
o Decomposition is used to eliminate some of the problems of bad design
like anomalies, inconsistencies, and redundancy.

Types of Decomposition

Lossless Decomposition

oIf the information is not lost from the relation that is decomposed, then the
decomposition will be lossless.
o The lossless decomposition guarantees that the join of relations will result
in the same relation as it was decomposed.
o The relation is said to be lossless decomposition if natural joins of all the
decomposition give the original relation.
Example:

EMPLOYEE_DEPARTMENT table:

EMP_I EMP_NA EMP_A EMP_CI DEPT_I DEPT_NA

D ME GE TY D ME
22 Denim 28 Mumbai 827 Sales

33 Alina 25 Delhi 438 Marketing

46 Stephan 30 Bangalore 869 Finance

52 Katherine 36 Mumbai 575 Production

60 Jack 40 Noida 678 Testing

The above relation is decomposed into two relations EMPLOYEE and

DEPARTMENT

EMPLOYEE table:
Dependency Preserving

o It is an important constraint of the database.

o In the dependency preservation, at least one decomposed table must satisfy
every dependency.
o If a relation R is decomposed into relation R1 and R2, then the
dependencies of R either must be a part of R1 or R2 or must be derivable
from the combination of functional dependencies of R1 and R2.
o For example, suppose there is a relation R (A, B, C, D) with functional
dependency set (A->BC). The relational R is decomposed into R1(ABC)
and R2(AD) which is dependency preserving because FD A->BC is a part
of relation R1(ABC).

Multivalued Dependency

o Multivalued dependency occurs when two attributes in a table are

independent of each other but, both depend on a third attribute.
o A multivalued dependency consists of at least two attributes that are
dependent on a third attribute that's why it always requires at least three
attributes.
Example: Suppose there is a bike manufacturer company which produces two
colors(white and black) of each model every year.

BIKE_MODEL MANUF_YEAR COLOR

M2011 2008 White

M2001 2008 Black

M3001 2013 White

M3001 2013 Black

M4006 2017 White

M4006 2017 Black

Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL

and independent of each other.

In this case, these two columns can be called as multivalued dependent on

BIKE_MODEL. The representation of these dependencies is shown below:

1. BIKE_MODEL → → MANUF_YEAR
 2. BIKE_MODEL → → COLOR
This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and
"BIKE_MODEL multidetermined COLOR".

Problems of NULL Values and Dangling tuples-

NULL Values in DBMS

When dealing with databases, understanding the intricacies of how null values
work is crucial. By definition, a null value indicates that information for a particular
attribute does not exist or is currently unknown. Unlike other values that hold
explicit data, null represents the absence of value.

Consider this through an example: in a table containing student information, the

'DateOfBirth' column is crucial for calculating students' ages. If some records have
null for the 'DateOfBirth', any calculation attempting to utilize this field, such as
determining the average age of students, is compromised. SQL operations
involving null values require careful considerations since logical operations,
comparisons, and certain functions like AVG() or SUM() can yield unexpected
results or fail to include these records altogether.

For students and database administrators, knowing how to handle null values is
essential to maintain data integrity and accuracy in their systems. Properly
addressing null values can prevent common pitfalls in database reporting and
analysis.

Dangling Tuple Problem

In relational databases, when we establish links between different tables using
foreign keys, it's important to be aware of and guard against the dangling tuple
problem. This issue arises when a tuple—or record—in one table refers to another
tuple in a different table through a foreign key, and the corresponding referenced
tuple is deleted or does not exist.

Take, for example, a student-registration database with students signing up for

various courses. Each student tuple in the 'Students' table includes a foreign key
pointer to a course in the 'Courses' table. If a course tuple is mistakenly deleted
while students are still assigned to it, the students' records would still reference that
now non-existent course. These orphaned references are what we call dangling
tuples.

It is essential to understand the implications of such database design issues as they

can lead to inconsistency, corrupt data retrieval, and errors in the database
operations. Remedial actions often involve implementing cascading deletes or
updates, ensuring that any operation on one table is reflected in the related table to
avoid leaving behind dangling tuples.
What is Query Optimization?

Query optimization is of great importance for the performance of a relational

database, especially for the execution of complex SQL statements. A query
optimizer decides the best methods for implementing each query.

The query optimizer selects, for instance, whether or not to use indexes for a given
query, and which join methods to use when joining multiple tables. These
decisions have a tremendous effect on SQL performance, and query optimization
is a key technology for every application, from operational Systems to data
warehouse and analytical systems to content management systems.

There is the various principle of Query Optimization are as follows −

• Understand how your database is executing your query − The first

phase of query optimization is understanding what the database is
performing. Different databases have different commands for this. For
example, in MySQL, one can use the "EXPLAIN [SQL Query]" keyword
to see the query plan. In Oracle, one can use the "EXPLAIN PLAN FOR
[SQL Query]" to see the query plan.
• Retrieve as little data as possible − The more information restored from
the query, the more resources the database is required to expand to process
and save these records. For example, if it can only require to fetch one
column from a table, do not use 'SELECT *'.
• Store intermediate results − Sometimes logic for a query can be quite
complex. It is possible to produce the desired outcomes through the use
of subqueries, inline views, and UNION-type statements. For those
methods, the transitional results are not saved in the database but are
directly used within the query. This can lead to achievement issues,
particularly when the transitional results have a huge number of rows.

There are various query optimization strategies are as follows −

• Use Index − It can be using an index is the first strategy one should use to
speed up a query.
• Aggregate Table − It can be used to pre-populating tables at higher levels
so less amount of information is required to be parsed.
• Vertical Partitioning − It can be used to partition the table by columns.
This method reduces the amount of information a SQL query required to
process.
• Horizontal Partitioning − It can be used to partition the table by data
value, most often time. This method reduces the amount of information a
SQL query required to process.
• De-normalization − The process of de-normalization combines multiple
tables into a single table. This speeds up query implementation because
fewer table joins are required.
 • Server Tuning − Each server has its parameters and provides tuning server
parameters so that it can completely take benefit of the hardware resources
that can significantly speed up query implementation.

Select operation in Relatiobnal Algebra

Information is retrieved from the database by specifying the sequence of

operations to be performed.

For Example − Relational algebra.

Structure Query language (SQL) is based on relational algebra.

Relational algebra consists of a set of operations that take one or two relations as
an input and produces a new relation as output.

Select operation displays the records that satisfy a condition. It is denoted by

sigma (σ) and is a horizontal subset of the original relation.

Syntax

Its syntax is as follows −

σcondition(table name)
Example

Consider the student table given below −

Regno Branch Section

1 CSE A

2 ECE B
Regno Branch Section

3 CIVIL B

4 IT A

Now, to display all the records of student table, we will use the following
command −

σ(student)

In addition to this, when we have to display all the records of CSE branch in
student table, we will use the following command −

σbranch=cse(student)

Hence, the result will be as follows −

RegNo BranchSection

1 CSE A

To display all the records in student tables whose regno>2, we will use the below
mentioned command −

σRegNo>2(student)

The output will be as follows −

RegNo Branch Section

3 CIVIL B

4 IT A

To display the record of ECE branch section B students, use the given command
−

σbranch=ECE ^ section=B(student)

To display the records of section B CSE and IT branch, use the following
command −

σSection=B ^ Branch=cse ∨ branch=IT(student)

Consider the EMPLOYEE TABLE as another example to know about selection
operations.

Retrieve information about those employees whose salary is greater than 20,000

• If one condition is specified then, we can use the following command −

σ salary > 20,000 (emp)
• If more than one condition specified in the query then ( AND: ^, OR:∨ ,
Not:#, equal: =, >, <, >=, <=)

Relational operator will be used to combine the multiple conditions into a single
statement.

Example − In order to retrieve the information of those employee whose salary >
20,000 and working in HOD and Dept no is 20, we can use the following
command −

σ salary > 20,000 ^LOC=HOD ^Deptno=20(emp)

Projection operation

It displays the specific column of a table. It is denoted by pie (∏). It is a vertical

subset of the original relation. It eliminates duplicate tuples.

Syntax

The syntax is as follows −

∏regno(student)

Example

Consider the student table:

Regno Branch Section

1 CSE A

2 ECE B

3 CIVIL B

4 IT A

To display regno column of student table, we can use the following command −
∏regno(student)
Output
RegNo

To display branch, section column of student table, use the following command
−

∏branch,section(student)

The result is as follows −

Branch Section

CSE A

ECE B

CIVIL B

IT A

To display regno, section of ECE students, use the following command −

∏regno,section(σbranch=ECE(student))
Output
Regno Section

2 B

Note: Conditions can be written in select operation but not in projection

operation.
Consider the employee table to know more about projection.

• If no condition is specified in the query then, Π empid, ename, salary,

address, dno (emp).
• If condition is specified then, the composition of the select and projection
is as follows −
∏ empid, ename, salary, address, dno (σ salary >20,00 ^ LOC = HOD ^ dno=20)
(emp)

Join Operations

Relational algebra is a formal system used to manipulate relations (tables) in a

database. The inner join operators, including theta join, equijoin, and natural join,
are used to combine data from two or more tables based on specified conditions.
Here’s an explanation of each:

Theta Join: Theta join is a binary operation that combines rows from two tables
based on a condition specified using a theta (θ) symbol. This condition can be
any boolean expression involving attributes (columns) from both tables. The
result of a theta join includes all combinations of rows from the two tables that
satisfy the specified condition. For example, suppose you have two
tables: Employees and Departments. You could perform a theta join to find all
pairs of employees and departments where the department's location matches the
employee's city:
Employees ⨝_{[Link] = [Link]} Departments

Equijoin: (Theta Join with Equality Condition): Equijoin is a special case of

theta join where the condition involves the equality (=) operator. It combines
rows from two tables where the specified columns have equal values. Equijoin is
often used to find related information between tables based on common
attributes.

Continuing with the previous example, if you only want to match employees
with departments based on their location and city, you would use an equijoin:
Employees ⨝_{[Link] = [Link]} Departments
Natural Join: Natural join is an operation that combines rows from two tables
based on columns that have the same name and data type. It implicitly performs
an equijoin on all columns with the same name in both tables. Natural join
eliminates duplicate columns from the result.

For example, suppose both Employees and Departments tables have a column
named DepartmentID. You can perform a natural join as follows:
Employees ⨝ Departments

This operation will match rows from both tables

where [Link] = [Link].

Each of these inner join operators serves different purposes, but they all allow
you to combine data from multiple tables in a meaningful way based on
specified conditions.

Examples:

Certainly! Let’s consider a simple example with two

tables: Employees and Departments. Here are the structures of these tables:

Theta Join: (Using a Theta Condition):

Suppose we want to find employees who earn more than $55,000 and are in the
HR department. We can use a theta join with the condition:
Employees ⨝_{[Link] > 55000 AND [Link] =
[Link] AND [Link] = 'HR'} Departments

The result of this theta join would include rows where the employee’s salary is
greater than $55,000 and they belong to the HR department. In this case, only
Alice would be included in the result.

Equijoin: (Using an Equality Condition):

Let’s say we want to match employees with their respective departments based
on the common attribute DepartmentID. We can perform an equijoin as follows:
Employees ⨝_{[Link] = [Link]}
Departments

The result of this equijoin would include all employees matched with their
corresponding departments based on the DepartmentID. The result would
contain all employees, along with the department information they belong to.

Natural Join:

In a natural join, we don’t explicitly specify the join condition. It automatically

joins the tables based on columns with the same name.
Employees ⨝ Departments

The natural join would automatically match rows

where [Link] = [Link]. The result
would include all columns from both tables, with duplicate columns
(e.g., DepartmentID) eliminated.
Outer Join

Outer Join in Relational algebra returns all the attributes of both the table
depending on the condition. If some attribute value is not present for any one of
the tables it returns NULL in the respective row of the table attribute.

• It is further classified as:

o Left Outer Join
o Right Outer Join
o Full Outer Join

Let's see how these Joins are performed.

Left Outer Join

It returns all the rows of the left table even if there is no matching row for it in
the right table performing Left Outer Join.

A Left Outer Join B

Let's perform Left Outer Join on table Boys and Interest and find out all the
boys of class 12th and their sports interest.

If we perform Left Outer Join on table Boys and table Interest such
that [Link] = [Link] . Then Result of the Join will be:

Boys.I [Link] [Link] Interest. [Link] [Link] [Link]

D me age ID me der ort
1 Rohan 56 1 Rohan M Chess
2 Rohit 85 1 Rohan M Chess
3 Amit 75 1 Rohan M Chess
4 Ravi 79 1 Rohan M Chess
5 Saiz 65 1 Rohan M Chess
6 Tejan 84 1 Rohan M Chess
7 Rishabh 75 NUll NULL NULL NULL

Clearly, we can observe that all the rows of the left table, i.e., table Boys is
present in the result.

Right Outer Join

It returns all the rows of the second table even if there is no matching row for it
in the first table performing Right Outer Join.
ARight Outer Join B

Let's perform Right Outer Join on table Boys and Interest and find out all the
boys of class 12th and their sports interest.

If we perform Right Outer Join on table Boys and table Interest such
that [Link] = [Link] . Then Result of the join will be:

Boys.I [Link] [Link] Interest. [Link] [Link] [Link]

D me age ID me der ort
1 Rohan 56 1 Rohan M Chess
2 Rohit 85 1 Rohan M Chess
3 Amit 75 1 Rohan M Chess
4 Ravi 79 1 Rohan M Chess
5 Saiz 65 1 Rohan M Chess
6 Tejan 84 1 Rohan M Chess
NULL NULL NULL 23 Aman M Chess
NULL NULL NULL 10 Shreya F Badminton
NULL NULL NULL 15 Sakshi F Chess
NULL NULL NULL 16 Tejan M Chess
NULL NULL NULL 35 Shubhi F Cricket

Clearly, we can observe that all the rows of the right table, i.e., table Interest is
present in the result.

Full Outer Join

It returns all the rows of the first and second Table.

A Full Outer Join B

Let's perform Full Outer Join on table Boys and Interest and find out all the
boys of class 12th and their sports interest.

If we perform Full Outer Join on Table Boys and Table Interest such
that [Link] = [Link] . Then the result of the join will be:

Boys.I [Link] [Link] Interest. [Link] [Link] [Link]

D me age ID me der ort
1 Rohan 56 1 Rohan M Chess
Boys.I [Link] [Link] Interest. [Link] [Link] [Link]
D me age ID me der ort
2 Rohit 85 1 Rohan M Chess
3 Amit 75 1 Rohan M Chess
4 Ravi 79 1 Rohan M Chess
5 Saiz 65 1 Rohan M Chess
6 Tejan 84 1 Rohan M Chess
7 Rishabh 75 NUll NULL NULL NULL
NULL NULL NULL 23 Aman M Chess
NULL NULL NULL 10 Shreya F Badminton
NULL NULL NULL 15 Sakshi F Chess
NULL NULL NULL 16 Tejan M Chess
NULL NULL NULL 35 Shubhi F Cricket

Cost-Based Query Optimization in DBMS

Query optimization is the process of selecting the most efficient way to execute
a SQL statement. Because SQL is a nonprocedural language, the optimizer can
merge, restructure, and process data in any sequence.

The Optimizer allocates a cost in numerical form for each step of a feasible plan
for a given query and environment, and then discovers these values together to
get a cost estimate for the plan or possible strategy. The Optimizer aims to find
the plan with the lowest cost estimate after evaluating the costs of all feasible
plans. As a result, the Optimizer is sometimes known as the Cost-Based
Optimizer.

• Execution Plans:

An execution plan specifies the best way to execute a SQL statement.

The plan describes the steps taken by Oracle Database to execute a SQL
statement. Each step physically retrieves or prepares rows of data from the
database for the statement's user.

An execution plan shows the total cost of the plan, which is stated on line 0, as
well as the cost of each individual operation. A cost is an internal unit that appears
solely in the execution plan to allow for plan comparisons. As a result, the cost
value cannot be fine-tuned or adjusted.

• Query Blocks The optimizer receives a parsed representation of a SQL

statement as input. Each SELECT block in the original SQL statement is
internally represented by a query block. A query block can be a statement
at the top level, a subquery, or an unmerged view. Let’s take an example
where the SQL statement that follows is made up of two query sections.
The inner query block is the subquery in parentheses. The remainder of the
outer query block of the SQL statement obtains the names of employees in
the departments whose IDs were supplied by the subquery. The query form
specifies how query blocks are connected.

SELECT first_name, last_name

FROM [Link]

WHERE department_id

IN (SELECT department_id

FROM [Link]

WHERE location_id = 1800);

• Query Sub Plans

The optimizer creates a query sub-plan for each query block.

From the bottom up, the database optimizes query blocks separately. As a result,
the database optimizes the innermost query block first, generating a sub-plan for
it, before generating the outer query block, which represents the full query.

The number of query block plans is proportional to the number of items in

the FROM clause. As the number of objects rises, this number climbs
exponentially. The possibilities for a join of five tables, for example, are far
higher than those for a connection of two tables.

• Analogy for the Optimizer

An online trip counselor is one analogy for the optimizer.

A biker wishes to find the most efficient bicycle path from point A to point B. A
query is analogous to the phrase "I need the quickest route from point A to
point B" or "I need the quickest route from point A to point B via point C". To
choose the most efficient route, the trip advisor employs an internal algorithm
that takes into account factors such as speed and difficulty. The biker can sway
the trip advisor's judgment by saying things like "I want to arrive as quickly as
possible" or "I want the simplest route possible.”

In this example, an execution plan is a possible path generated by the travel

advisor. Internally, the advisor may divide the overall route into multiple
subroutes (sub plans) and compute the efficiency of each subroute separately. For
example, the trip advisor may estimate one subroute to take 15 minutes and be of
medium difficulty, another subroute to take 22 minutes and be of low difficulty,
and so on.

Based on the user-specified goals and accessible facts about roads and traffic
conditions, the advisor selects the most efficient (lowest cost) overall route. The
better the guidance, the more accurate the statistics. For example, if the advisor
is not kept up to date on traffic delays, road closures, and poor road conditions,
the proposed route may prove inefficient (high cost).

Heuristic Based Optimization-

In DBMS, heuristic optimization is a procedure that is aimed at the rapid

exploration of almost all execution plans in a quick and efficient way. Unlike
the exhaustive optimization methods that postulate all the feasible plan
alternatives and then apply them to the optimization process, the heuristic
optimization rules, with the use of derived empirical knowledge and
approximate algorithms, tend to speed up the process. Through the
implementation of heuristics, the DBMS optimizers spur efficient execution
query plan convergence while also minimizing computational cost.

Rules

Heuristic optimization transforms the expression-tree by using a set of rules

which improve the performance. These rules are as follows −

• Perform the SELECTION process foremost in the query. This should be

the first action for any SQL table. By doing so, we can decrease the number
of records required in the query, rather than using all the tables during the
query.
• Perform all the projection as soon as achievable in the query. Somewhat
like a selection but this method helps in decreasing the number of columns
in the query.
• Perform the most restrictive joins and selection operations. What this
means is that select only those sets of tables and/or views which will result
in a relatively lesser number of records and are extremely necessary in the
query. Obviously any query will execute better when tables with few
records are joined.
Steps in heuristic optimization

Let’s see the steps involve in heuristic optimization, which are explained below
−

• Deconstruct the conjunctive selections into a sequence of single selection

operations.
• Move the selection operations down the query tree for the earliest possible
execution.
• First execute those selections and join operations which will produce
smallest relations.
• Replace the cartesian product operation followed by selection operation
with join operation.
• Deconstructive and move the tree down as far as possible.
• Identify those subtrees whose operations are pipelined.

UNIT IV

What does a Transaction mean in DBMS?

• Transaction in Database Management Systems (DBMS) can be
defined as a set of logically related operations.
• It is the result of a request made by the user to access the contents of
the database and perform operations on it.
• It consists of various operations and has various states in its
completion journey.
• It also has some specific properties that must be followed to keep the
database consistent.
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:
i) Read(X)
• A read operation is used to read the value of X from the database
and store it in a buffer in the main memory for further actions such as
displaying that value.
• Such an operation is performed when a user wishes just to see any
content of the database and not make any changes to it. For example,
 when a user wants to check his/her account’s balance, a read
operation would be performed on user’s account balance from the
database.
ii) Write(X)
• A write operation is used to write the value to the database from the
buffer in the main memory. For a write operation to be performed,
first a read operation is performed to bring its value in buffer, and
then some changes are made to it, e.g. some set of arithmetic
operations are performed on it according to the user’s request, then to
store the modified value back in the database, a write operation is
performed.
• For example, when a user requests to withdraw some money from
his account, his account balance is fetched from the database using a
read operation, then the amount to be deducted from the account is
subtracted from this value, and then the obtained value is stored back
in the database using a write operation.
iii) 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.
• To ensure that further operations of any other transaction are
performed only after work of the current transaction is done, a
commit operation is performed to the changes made by a transaction
permanently to the database.
iv) Rollback
• This operation is performed to bring the database to the last saved
state when any transaction is interrupted in between due to any
power, hardware, or software failure.
• In simple words, it can be said that a rollback operation does undo
the operations of transactions that were performed before its
interruption to achieve a safe state of the database and avoid any kind
of ambiguity or inconsistency.

What is Serializability in DBMS?

In the field of computer science, serializability is a term that is a property of the

system that describes how the different process operates the shared data. If the
result given by the system is similar to the operation performed by the system,
then in this situation, we call that system serializable. Here the cooperation of the
system means there is no overlapping in the execution of the data. In DBMS,
when the data is being written or read then, the DBMS can stop all the other
processes from accessing the data.

What is a Serializable Schedule?

In DBMS, the Serializable schedule is a property in which the read and write
operation sequence does not disturb the serializability property. This property
ensures that the transaction is executed automatically with the other transaction.
In DBMS, the order of the serializability must be the same as some serial
schedules of the same transaction.
Non-serial Schedule
A schedule where the transactions are overlapping or switching places. As they
are used to carry out actual database operations, multiple transactions are
running at once. It’s possible that these transactions are focusing on the same
data set. Therefore, it is crucial that non-serial schedules can be serialized in
order for our database to be consistent both before and after the transactions are
executed.
Example:
Transaction-1 Transaction-2

R(a)

W(a)

R(b)

W(b)

R(b)

R(a)

W(b)

W(a)
We can observe that Transaction-2 begins its execution before Transaction-1 is
finished, and they are both working on the same data, i.e., “a” and “b”,
interchangeably. Where “R”-Read, “W”-Write

Types of Serializability
There are two ways to check whether any non-serial schedule is serializable.
 Types of Serializability – Conflict & View

1. Conflict serializability
Conflict serializability refers to a subset of serializability that focuses on
maintaining the consistency of a database while ensuring that identical data
items are executed in an order. In a DBMS each transaction has a value and all
the transactions, in the database rely on this uniqueness. This uniqueness ensures
that no two operations with the conflict value can occur simultaneously.
For example lets consider an order table and a customer table as two instances.
Each order is associated with one customer even though a single client may
place orders. However there are restrictions for achieving conflict serializability
in the database. Here are a few of them.
1. Different transactions should be used for the two procedures.
2. The identical data item should be present in both transactions.
3. Between the two operations, there should be at least one write
operation.
Example
Three transactions—t1, t2, and t3—are active on a schedule “S” at once. Let’s
create a graph of precedence.
Transaction – 1 (t1) Transaction – 2 (t2) Transaction – 3 (t3)

R(a)

R(b)

R(b)

W(b)

W(a)

W(a)
Transaction – 1 (t1) Transaction – 2 (t2) Transaction – 3 (t3)

R(a)

W(a)
It is a conflict serializable schedule as well as a serial schedule because the
graph (a DAG) has no loops. We can also determine the order of transactions
because it is a serial schedule.

DAG of transactions

As there is no incoming edge on Transaction 1, Transaction 1 will be executed

first. T3 will run second because it only depends on T1. Due to its dependence
on both T1 and T3, t2 will finally be executed.
Therefore, the serial schedule’s equivalent order is: t1 –> t3 –> t2
Note: A schedule is unquestionably consistent if it is conflicting serializable. A
non-conflicting serializable schedule, on the other hand, might or might not be
serial. We employ the idea of View Serializability to further examine its serial
behavior.
2. View Serializability
View serializability is a kind of operation in a serializable in which each
transaction should provide some results, and these outcomes are the output of
properly sequentially executing the data item. The view serializability, in
contrast to conflict serialized, is concerned with avoiding database
inconsistency. The view serializability feature of DBMS enables users to see
databases in contradictory ways.
To further understand view serializability in DBMS, we need to understand the
schedules S1 and S2. The two transactions T1 and T2 should be used to establish
these two schedules. Each schedule must follow the three transactions in order
to retain the equivalent of the transaction. These three circumstances are listed
below.
1. The first prerequisite is that the same kind of transaction appears on
every schedule. This requirement means that the same kind of group
of transactions cannot appear on both schedules S1 and S2. The
schedules are not equal to one another if one schedule commits a
transaction but it does not match the transaction of the other
schedule.
 2. The second requirement is that different read or write operations
should not be used in either schedule. On the other hand, we say that
two schedules are not similar if schedule S1 has two write operations
whereas schedule S2 only has one. The number of the write operation
must be the same in both schedules, however there is no issue if the
number of the read operation is different.
3. The second to last requirement is that there should not be a conflict
between either timetable. execution order for a single data item.
Assume, for instance, that schedule S1’s transaction is T1, and
schedule S2’s transaction is T2. The data item A is written by both
the transaction T1 and the transaction T2. The schedules are not
equal in this instance. However, we referred to the schedule as
equivalent to one another if it had the same number of all write
operations in the data item.
What is view equivalency?
Schedules (S1 and S2) must satisfy these two requirements in order to be viewed
as equivalent:
1. The same piece of data must be read for the first time. For instance,
if transaction t1 is reading “A” from the database in schedule S1,
then t1 must also read A in schedule S2.
2. The same piece of data must be used for the final write. As an
illustration, if transaction t1 updated A last in S1, it should also
conduct final write in S2.
3. The middle sequence need to follow suit. As an illustration, if in S1
t1 is reading A, and t2 updates A, then in S2 t1 should read A, and t2
should update A.
View Serializability refers to the process of determining whether a schedule’s
views are equivalent.
Example
We have a schedule “S” with two concurrently running transactions, “t1” and
“t2.”
Schedule – S:
Transaction-1 (t1) Transaction-2 (t2)

R(a)

W(a)

R(a)

W(a)

R(b)

W(b)
Transaction-1 (t1) Transaction-2 (t2)

R(b)

W(b)
By switching between both transactions’ mid-read-write operations, let’s
create its view equivalent schedule (S’).
Schedule – S’:
Transaction-1 (t1) Transaction-2 (t2)

R(a)

W(a)

R(b)

W(b)

R(a)

W(a)

R(b)

W(b)
It is a view serializable schedule since a view similar schedule is conceivable.
Note: A conflict serializable schedule is always viewed as serializable, but
vice versa is not always true.

Advantages of Serializability
1. Execution is predictable: In serializable, the DBMS’s threads are all
performed simultaneously. The DBMS doesn’t include any such
surprises. In DBMS, no data loss or corruption occurs and all
variables are updated as intended.
2. DBMS executes each thread independently, making it much simpler
to understand and troubleshoot each database thread. This can greatly
simplify the debugging process. The concurrent process is therefore
not a concern for us.
3. Lower Costs: The cost of the hardware required for the efficient
operation of the database can be decreased with the aid of the
serializable property. It may also lower the price of developing the
software.
 4. Increased Performance: Since serializable executions provide
developers the opportunity to optimize their code for performance,
they occasionally outperform non-serializable equivalents.

Testing of Serializability in DBMS with Examples

Serializability is a type of property of DBMS in which each transaction is

executed independently and automatically, even though these transactions are
executed concurrently. In other words, we can say that if there are several
transactions executed concurrently, then the main work of the serializability
function is to arrange these several transactions in a sequential manner.

For better understanding, let's explain these with an example. Suppose there are
two users Sona and Archita. Each executes two transactions. Let's transactions
T1 and T2 are executed by Sona, and T3 and T4 are executed by Archita. Suppose
transaction T1 reads and writes the data item A, transaction T2 reads the data item
B, transaction T3 reads and writes the data item C and transaction T4 reads the
data item D. Lets the schedule the above transaction as below.

•
T1: Read A → Write A→ Read B → Write B`
•
`T2: Read B → Write B`
•
`T3: Read C → Write C→ Read D → Write D`
• `T4: Read D → Write D
Let's first discuss why these transactions are not serializable.

In order for a schedule to be considered serializable, it must first satisfy the

conflict serializability property. In our example schedule above, notice that
Transaction 1 (T1) and Transaction 2 (T2) read data item B before either writing
it. This causes a conflict between T1 and T2 because they are both trying to read
and write the same data item concurrently. Therefore, the given schedule does not
conflict with serializability.

However, there is another type of serializability called view serializability which

our example does satisfy. View serializability requires that if two transactions
cannot see each other's updates (i.e., one transaction cannot see the effects of
another concurrent transaction), the schedule is considered to view serializable.
In our example, Transaction 2 (T2) cannot see any updates made by Transaction
4 (T4) because they do not share common data items. Therefore, the schedule is
viewed as serializable.

It's important to note that conflict serializability is a stronger property than view
serializability because it requires that all potential conflicts be resolved before
any updates are made (i.e., each transaction must either read or write each data
item before any other transaction can write it). View serializability only requires
that if two transactions cannot see each other's updates, then the schedule is view
serializable & it doesn't matter whether or not there are potential conflicts
between them.

All in all, both properties are necessary for ensuring correctness in concurrent
transactions in a database management system.

Recoverability in DBMS

Recoverability is a property of database systems that ensures that, in the event

of a failure or error, the system can recover the database to a consistent state.
Recoverability guarantees that all committed transactions are durable and that
their effects are permanently stored in the database, while the effects of
uncommitted transactions are undone to maintain data consistency.
The recoverability property is enforced through the use of transaction logs,
which record all changes made to the database during transaction processing.
When a failure occurs, the system uses the log to recover the database to a
consistent state, which involves either undoing the effects of uncommitted
transactions or redoing the effects of committed transactions.
There are several levels of recoverability that can be supported by a database
system:
No-undo logging: This level of recoverability only guarantees that committed
transactions are durable, but does not provide the ability to undo the effects of
uncommitted transactions.
Undo logging: This level of recoverability provides the ability to undo the
effects of uncommitted transactions but may result in the loss of updates made
by committed transactions that occur after the failed transaction.
Redo logging: This level of recoverability provides the ability to redo the
effects of committed transactions, ensuring that all committed updates are
durable and can be recovered in the event of failure.
Undo-redo logging: This level of recoverability provides both undo and redo
capabilities, ensuring that the system can recover to a consistent state
regardless of whether a transaction has been committed or not.
In addition to these levels of recoverability, database systems may also use
techniques such as checkpointing and shadow paging to improve recovery
performance and reduce the overhead associated with logging.
Overall, recoverability is a crucial property of database systems, as it ensures
that data is consistent and durable even in the event of failures or errors. It is
important for database administrators to understand the level of recoverability
provided by their system and to configure it appropriately to meet their
application’s requirements.

Log based Recovery in DBMS

Log and log records
The log is a sequence of log records, recording all the updated activities in the
database. In stable storage, logs for each transaction are maintained. Any
operation which is performed on the database is recorded on the log. Prior to
performing any modification to the database, an updated log record is created to
reflect that modification. An update log record represented as: <Ti, Xj, V1, V2>
has these fields:
1. Transaction identifier: Unique Identifier of the transaction that
performed the write operation.
2. Data item: Unique identifier of the data item written.
3. Old value: Value of data item prior to write.
4. New value: Value of data item after write operation.
Other types of log records are:
1. <Ti start> : It contains information about when a transaction Ti
starts.
2. <Ti commit> : It contains information about when a transaction Ti
commits.
3. <Ti abort> : It contains information about when a transaction Ti
aborts.
Undo and Redo Operations
Because all database modifications must be preceded by the creation of a log
record, the system has available both the old value prior to the modification of
the data item and new value that is to be written for data item. This allows
system to perform redo and undo operations as appropriate:
1. Undo: using a log record sets the data item specified in log record to
old value.
2. Redo: using a log record sets the data item specified in log record to
new value.
The database can be modified using two approaches –
1. Deferred Modification Technique: If the transaction does not
modify the database until it has partially committed, it is said to use
deferred modification technique.
2. Immediate Modification Technique: If database modification occur
while the transaction is still active, it is said to use immediate
modification technique.
Recovery using Log records
After a system crash has occurred, the system consults the log to determine
which transactions need to be redone and which need to be undone.
1. Transaction Ti needs to be undone if the log contains the record <Ti
start> but does not contain either the record <Ti commit> or the
record <Ti abort>.
2. Transaction Ti needs to be redone if log contains record <Ti start>
and either the record <Ti commit> or the record <Ti abort>.
Use of Checkpoints – When a system crash occurs, user must consult the log.
In principle, that need to search the entire log to determine this information.
There are two major difficulties with this approach:
1. The search process is time-consuming.
 2. Most of the transactions that, according to our algorithm, need to be
redone have already written their updates into the database. Although
redoing them will cause no harm, it will cause recovery to take
longer.

Checkpoints Based Recovery in DBMS

The Checkpoint is used to declare a point before which the DBMS was in a
consistent state, and all transactions were committed. During transaction
execution, such checkpoints are traced. After execution, transaction log files
will be created. Upon reaching the savepoint/checkpoint, the log file is
destroyed by saving its update to the database. Then a new log is created with
upcoming execution operations of the transaction and it will be updated until
the next checkpoint and the process continues.
Why do We Need Checkpoints?
Whenever transaction logs are created in a real-time environment, it eats up lots
of storage space. Also keeping track of every update and its maintenance may
increase the physical space of the system. Eventually, the transaction log file
may not be handled as the size keeps growing. This can be addressed with
checkpoints. The methodology utilized for removing all previous transaction
logs and storing them in permanent storage is called a Checkpoint.
Steps to Use Checkpoints in the Database
1. Write the begin_checkpoint record into a log.
2. Collect checkpoint data in stable storage.
3. Write the end_checkpoint record into a log.
The behavior when the system crashes and recovers when concurrent
transactions are executed is shown below:

Understanding Checkpoints in multiple Transactions

Transactions and operations of the above diagram:

Transaction 1 Transaction 2 Transaction 3 Transaction 4
(T1) (T2) (T3) (T4)

START

START

COMMIT

START

COMMIT

START

FAILURE

• The recovery system reads the logs backward from the end to the last
checkpoint i.e. from T4 to T1.
• It will keep track of two lists – Undo and Redo.
• Whenever there is a log with instructions <Tn, start>and <Tn,
commit> or only <Tn, commit> then it will put that transaction in
Redo List. T2 and T3 contain <Tn, Start> and <Tn, Commit> whereas
T1 will have only <Tn, Commit>. Here, T1, T2, and T3 are in the redo
list.
• Whenever a log record with no instruction of commit or abort is found,
that transaction is put to Undo List <Here, T4 has <Tn, Start> but no
<Tn, commit> as it is an ongoing transaction. T4 will be put on the
undo list.
All the transactions in the redo list are deleted with their previous logs and then
redone before saving their logs. All the transactions in the undo list are undone
and their logs are deleted.
Types of Checkpoints
There are basically two main types of Checkpoints:
1. Automatic Checkpoint
2. Manual Checkpoint
1. Automatic Checkpoint: These checkpoints occur very frequently like every
hour or every day. These intervals are set by the database administrator. They
are generally used by heavy databases as they are frequently updated, and we
can recover the data easily in case of failure.
2. Manual Checkpoint: These are the checkpoints that are manually set by the
database administrator. Manual checkpoints are generally used for smaller
databases. They are updated very less frequently only when they are set by the
database administrator.
Relevance of Checkpoints
A checkpoint is a feature that adds a value of C in ACID-compliant to RDBMS.
A checkpoint is used for recovery if there is an unexpected shutdown in the
database. Checkpoints work on some intervals and write all dirty pages
(modified pages) from logs relay to data file from i.e from a buffer to a physical
disk. It is also known as the hardening of dirty pages. It is a dedicated process
and runs automatically by SQL Server at specific intervals. The synchronization
point between the database and transaction log is served with a checkpoint.
Advantages of Checkpoints
• Checkpoints help us in recovering the transaction of the database in
case of a random shutdown of the database.
• It enhancing the consistency of the database in case when multiple
transactions are executing in the database simultaneously.
• It increasing the data recovery process.
• Checkpoints work as a synchronization point between the database and
the transaction log file in the database.
• Checkpoint records in the log file are used to prevent unnecessary redo
operations.
• Since dirty pages are flushed out continuously in the background, it
has a very low overhead and can be done frequently.
• Checkpoints provide the baseline information needed for the
restoration of the lost state in the event of a system failure.
• A database checkpoint keeps track of change information and enables
incremental database backup.
• A database storage checkpoint can be mounted, allowing regular file
system operations to be performed.
• Database checkpoints can be used for application solutions which
include backup, recovery or database modifications.

Disadvantages of Checkpoints
1. Database storage checkpoints can only be used to restore from logical errors
(E.g. a human error).
2. Because all the data blocks are on the same physical device, database
storage checkpoints cannot be used to restore files due to a media failure.
Concurrency Control in DBMS
• Executing a single transaction at a time will increase the waiting time
of the other transactions which may result in delay in the overall
execution. Hence for increasing the overall throughput and efficiency
of the system, several transactions are executed.
 • Concurrency control is a very important concept of DBMS which
ensures the simultaneous execution or manipulation of data by
several processes or user without resulting in data inconsistency.
• Concurrency control provides a procedure that is able to control
concurrent execution of the operations in the database.
• The fundamental goal of database concurrency control is to ensure
that concurrent execution of transactions does not result in a loss of
database consistency. The concept of serializability can be used to
achieve this goal, since all serializable schedules preserve
consistency of the database. However, not all schedules that preserve
consistency of the database are serializable.
• In general it is not possible to perform an automatic analysis of low-
level operations by transactions and check their effect on database
consistency constraints. However, there are simpler techniques. One
is to use the database consistency constraints as the basis for a split
of the database into subdatabases on which concurrency can be
managed separately.
• Another is to treat some operations besides read and write as
fundamental low-level operations and to extend concurrency control
to deal with them.
Concurrency Control Problems
There are several problems that arise when numerous transactions are executed
simultaneously in a random manner. The database transaction consist of two
major operations “Read” and “Write”. It is very important to manage these
operations in the concurrent execution of the transactions in order to maintain
the consistency of the data.
Dirty Read Problem(Write-Read conflict)
Dirty read problem occurs when one transaction updates an item but due to some
unconditional events that transaction fails but before the transaction performs
rollback, some other transaction reads the updated value. Thus creates an
inconsistency in the database. Dirty read problem comes under the scenario of
Write-Read conflict between the transactions in the database
1. The lost update problem can be illustrated with the below scenario
between two transactions T1 and T2.
2. Transaction T1 modifies a database record without committing the
changes.
3. T2 reads the uncommitted data changed by T1
4. T1 performs rollback
5. T2 has already read the uncommitted data of T1 which is no longer
valid, thus creating inconsistency in the database.
Lost Update Problem
Lost update problem occurs when two or more transactions modify the same
data, resulting in the update being overwritten or lost by another transaction.
The lost update problem can be illustrated with the below scenario between two
transactions T1 and T2.
1. T1 reads the value of an item from the database.
2. T2 starts and reads the same database item.
3. T1 updates the value of that data and performs a commit.
 4. T2 updates the same data item based on its initial read and performs
commit.
5. This results in the modification of T1 gets lost by the T2’s write
which causes a lost update problem in the database.

Concurrency Control Protocols

Concurrency control protocols are the set of rules which are maintained in order
to solve the concurrency control problems in the database. It ensures that the
concurrent transactions can execute properly while maintaining the database
consistency. The concurrent execution of a transaction is provided with
atomicity, consistency, isolation, durability, and serializability via the
concurrency control protocols.
• Locked based concurrency control protocol
• Timestamp based concurrency control protocol
Locked based Protocol
In locked based protocol, each transaction needs to acquire locks before they
start accessing or modifying the data items. There are two types of locks used
in databases.
• Shared Lock : Shared lock is also known as read lock which allows
multiple transactions to read the data simultaneously. The transaction
which is holding a shared lock can only read the data item but it can
not modify the data item.
• Exclusive Lock : Exclusive lock is also known as the write lock.
Exclusive lock allows a transaction to update a data item. Only one
transaction can hold the exclusive lock on a data item at a time.
While a transaction is holding an exclusive lock on a data item, no
other transaction is allowed to acquire a shared/exclusive lock on the
same data item.

There are two kind of lock based protocol mostly used in database:
• Two Phase Locking Protocol : Two phase locking is a widely used
technique which ensures strict ordering of lock acquisition and
release. Two phase locking protocol works in two phases.
o Growing Phase : In this phase, the transaction starts
acquiring locks before performing any modification on the
data items. Once a transaction acquires a lock, that lock can
not be released until the transaction reaches the end of the
execution.
o Shrinking Phase : In this phase, the transaction releases all
the acquired locks once it performs all the modifications on
the data item. Once the transaction starts releasing the
locks, it can not acquire any locks further.

• Strict Two Phase Locking Protocol : It is almost similar to the two

phase locking protocol the only difference is that in two phase
locking the transaction can release its locks before it commits, but in
case of strict two phase locking the transactions are only allowed to
release the locks only when they performs commits.
Timestamp based Protocol
• In this protocol each transaction has a timestamp attached to it.
Timestamp is nothing but the time in which a transaction enters into
the system.
• The conflicting pairs of operations can be resolved by the timestamp
ordering protocol through the utilization of the timestamp values of
the transactions. Therefore, guaranteeing that the transactions take
place in the correct order.

Advantages of Concurrency
In general, concurrency means, that more than one transaction can work on a
system. The advantages of a concurrent system are:
• Waiting Time: It means if a process is in a ready state but still the
process does not get the system to get execute is called waiting time.
So, concurrency leads to less waiting time.
• Response Time: The time wasted in getting the response from the
cpu for the first time, is called response time. So, concurrency leads
to less Response Time.
• Resource Utilization: The amount of Resource utilization in a
particular system is called Resource Utilization. Multiple
transactions can run parallel in a system. So, concurrency leads to
more Resource Utilization.
• Efficiency: The amount of output produced in comparison to given
input is called efficiency. So, Concurrency leads to more Efficiency.
Disadvantages of Concurrency
• Overhead: Implementing concurrency control requires additional
overhead, such as acquiring and releasing locks on database objects.
This overhead can lead to slower performance and increased resource
consumption, particularly in systems with high levels of concurrency.
• Deadlocks: Deadlocks can occur when two or more transactions are
waiting for each other to release resources, causing a circular
dependency that can prevent any of the transactions from completing.
Deadlocks can be difficult to detect and resolve, and can result in
reduced throughput and increased latency.
• Reduced concurrency: Concurrency control can limit the number of
users or applications that can access the database simultaneously.
This can lead to reduced concurrency and slower performance in
systems with high levels of concurrency.
• Complexity: Implementing concurrency control can be complex,
particularly in distributed systems or in systems with complex
transactional logic. This complexity can lead to increased
development and maintenance costs.
• Inconsistency: In some cases, concurrency control can lead to
inconsistencies in the database. For example, a transaction that is
rolled back may leave the database in an inconsistent state, or a long-
running transaction may cause other transactions to wait for extended
periods, leading to data staleness and reduced accuracy.
Types of Locks in Concurrency Control
A Lock is a variable assigned to any data item in order to keep track of the
status of that data item so that isolation and non-interference is ensured
during concurrent transactions.
At its basic, a database lock exists to prevent two or more database users from
performing any change on the same data item at the very same time. Therefore,
it is correct to interpret this technique as a means of synchronizing access, which
is in stark contrast to other sets of protocols such as those using timestamps and
multiversion timestamps. In layman’s terms, this may be further simplified to
the metaphorical ‘lock’ that is put on a data item so that no other user may
unlock the ability to perform any update query.

In Case 2, which has been portrayed above, if the user/session on the right
attempts an update, it will be met with a LOCK WAIT state or otherwise
be STALLED until access to the data item is unlocked. In some situations – if
the stall exceeds a time limit – the session is terminated and an error statement
is returned.

Binary Locks :
Remember that a lock is fundamentally a variable which holds a value. A
binary lock is a variable capable of holding only 2 possible values, i.e., a 1
(depicting a locked state) or a 0 (depicting an unlocked state). This lock is
usually associated with every data item in the database ( maybe at table level,
row level or even the entire database level).

Should item X be unlocked, then a corresponding object lock(X) would return

the value 0. So, the instant a user/session begins updating the contents of item
X, lock(X) is set to a value of 1. Due to this, for as long as the update query
lasts, no other user may access the item X – even read or write to it!
There are 2 operations used to implement binary locks. They are lock_data( )
and unlock_data( ). The algorithms have been discussed below (only algorithms
have been entertained due to the diversity in DBMS scripts):
The locking operation :
lock_data(X):
label: if lock(X) == 0
{
then lock(X) = 1;
}
else //when lock(X) == 1 or item X is locked
{
wait (until item is unlocked or lock(X)=0) //wait for the user to finish
the update query
go to label
}
Note that ‘label:‘ is literally a label for the line which can be referred to at a
later step to transfer execution to. The ‘wait’ command in the else block
basically puts all other transactions wanting to access X in a queue. Since it
monitors or keeps other transactions scheduled until access to the item is
unlocked, it is often taken to be outside the lock_data(X) operation i.e., defined
outside.
The unlocking operation :
unlock_data(X):
lock(X) = 0 //we unlock access to item X
if (transactions are in queue)
{
then grant access or 'wake' the next transaction in line;
}
Merits of Binary Locks :
• They are simple to implement since they are effectively mutually
exclusive and establish isolation perfectly.
• Binary Locks demand less from the system since the system must only
keep a record of the locked items. The system is the lock manager
subsystem which is a feature of all DBMSs today.
Drawbacks of Binary Locks :
• Binary locks are highly restrictive.
• They do not even permit reading of the contents of item X. As a result,
they are not used commercially.
Shared or Exclusive Locks :
The incentive governing these types of locks is the restrictive nature of binary
locks. Here we look at locks which permit other transactions to make read
queries since a READ query is non-conflicting. However, if a transaction
demands a write query on item X, then that transaction must be given exclusive
access to item X. Ergo, we require a kind of multi-mode lock which is what
shared/exclusive locks are. They are also known as Read/Write locks.
Unlike binary locks, Read/Write locks may be set to 3 values,
i.e., SHARED, EXCLUSIVE or UNLOCKED. Hence, our lock, i.e., lock(X),
may reflect either of the following values:
1. READ-LOCKED –
If a transaction only requires to read the contents of item X and the
lock only permits reading. This is also known as a shared lock.
2. WRITE-LOCKED –
If a transaction needs to update or write to item X, the lock must
restrict all other transactions and provide exclusive access to the
current transaction. Thus, these locks are also known as exclusive
locks.
3. UNLOCKED –
Once a transaction has completed its read or update operations, no lock
is held and the data item is unlocked. In this state, the item may be
accessed by any queued transactions.

A Shared/Exclusive lock may hold any of the 3 states.

The most popular way of implementing these locks is by introducing a LOCK-

TABLE which keeps track of the number of read-locks on the data items and
the transactions with write-locks on different items. The table has been
described below.

Note that if an item is write-locked, it is logically supposed to have no reads

on account of the fact that it is now exclusive. As a result, the ‘Locking
Transaction’ column holds only one value – the transaction ID of the current
transaction. If an item is read-locked, it is shared by multiple transactions and
therefore, the ‘Locking Transaction’ column lists the transaction IDs of all the
transactions. Since there are 3 states that the lock may hold, there must be 3
operations which would execute the change to those states. These are as
follows :
The read_lock operation –
read_lock(X):
label: if lock(X) = "unlocked"
{
then lock(X) = "read-locked";
no_of_reads = 1; //since only the first transaction in queue is now able
to read item X
}
else if lock(X) = "read-locked"
{
then no_of_reads +=1; //simply increment as a new transaction is now
reading the item X
}
else //lock(X) write-locked
{
wait (until lock(X) is "unlocked");//transactions observe a LOCK
WAIT during this time
go to label;
}
When the lock(X) is set to “write-locked” (in the final else clause), the item is
exclusively being accessed by a transaction. In order for other transactions to
access it, the LOCK WAIT must end (updating process must finish) and
lock(X) = “unlocked”. This is what we wait for in the next line.
The write_lock operation –
write_lock(X):
label: if lock(X) = "unlocked"
{
then lock(X) = "write-locked"
}
else //if a read-lock is issued to item X
{
wait (until lock(X) is "unlocked"); //so that the lock manager may
wake up the next transaction
go to label;
}
If an item is unlocked, we simply write-lock it to grant exclusive access to the
current transaction. Now the lock manager system must put all other
transactions in a queue. If the item is in a read-lock state, the write-lock may
NOT be directly issued. The item must first be unlocked before it can be write-
locked. In doing so, the lock manager system also wakes up the queued
transactions.
The unlock operation –
unlock(X):
if lock(X) = "write-locked"
{
 then lock(X) = "unlocked";
//the transactions in queue, if any, may now access item X in the manner
they demand
}
else if lock(X) = "read-locked"
{
then
no_of_reads-=1; //the transaction is done reading.
if no_of_reads == 0 //no transactions reading the item
{
lock(X) = "unlocked";
//transactions in queue, if any, may now access item X in the manner
they demand
}
}
The first case is straightforward enough. However, in the second case, we
must check for the condition that there are no more current transactions
sharing or reading item X. If item X is being read, we leave the situation and
simply decrement the no_of_reads as the last transaction has terminated. The
point here is that an item may be “unlocked only if:
• the ‘write’ operation terminates or is completed
• all ‘read’ operations terminate or are completed
Here are a few rules that Shared/Exclusive Locks must obey:
1. A transaction T MUST issue the unlock(X) operation after all read
and write operations have finished.
2. A transaction T may NOT issue a read_lock(X) or write_lock(X)
operation on an item which already has a read or write-lock issued to
itself.
3. A transaction T is NOT allowed to issue the unlock(X) operation
unless it has been issued with a read_lock(X) or write_lock(X)
operation.
Drawbacks of Shared/Exclusive Locks :
• Do not guarantee serializability of schedules on their own. A separate
protocol must be followed to ensure this.
• Commercially not optimized for speedy transactions; not the best
solution due to lock contention issues.
• Performance overhead is not negligible.
Certify Locks :
The motivation behind introducing certify locks is the failure of previously
mentioned locks to deliver an efficient and promising architecture which does
not compromise on speed of processing transactions. Here we briefly look at a
form of multiple-mode locking scheme which allows for the lock to be
characterized by 3 locked states and 1 unlocked state.

Transactions may issue any of 3 locked states or 1 unlocked state

The states an item may be issued are :

1. READ-LOCKED –
same as the read-locked state explained earlier for Shared/Exclusive
Locks
2. WRITE-LOCKED –
same as the write-locked state explained earlier for Shared/Exclusive
Locks
3. CERTIFY-LOCKED –
This is an exclusive lock. This is used when 2 different transactions
must be read and write respectively, to item X. In order for this to
happen, a committed and a local version of the data item is created.
The committed version is used by all transactions which have a read-
lock issued to X. The local version of X is accessed by T only when a
write-lock has been acquired by T. Once the writing or updating
operation has been carried out by T on item X, T must obtain a
certify-lock so that the committed version of data item X may be
updated to the local version’s contents and the local version may be
discarded.
4. UNLOCKED –
same as the write-locked state explained earlier for Shared/Exclusive
Locks
Here is how a certify lock is used in multi-version concurrency control
techniques:
In Step 2, two versions of Item X are created i.e., the committed and local
[Link] Step 4, the transaction must issue a certify lock on all writes it has
made. Only then can we proceed to the next step. The updates on Local
version are now final.

Step 5

In order for multiple transactions to access the contents of data item X, a

compatibility table must be drawn so that any collision or error is not returned,
which may delay the process.

Locks on X Read Write Certify

Read Yes Yes No

Write Yes No No

Certify No No No

The correct way of interpreting this table is as follows. Consider two

transactions, T and T’. Assign transaction T to either the rows or the columns.
Do the exact opposite for T’. Now the compatibility between locks issued to
an item X by T and T’ can be cross-referenced. For example, assign T to rows
and T’ to the columns. If T issues a read-lock on X and T’ issues a write-lock
on X, the result is a ‘Yes’ – this scenario is feasible. However, if T intends a
write-lock on X and T’ intends a certify-lock on X, the result is a ‘No’ –
implying an impossible scenario.
Validation Based Protocol in DBMS
Validation Based Protocol is also called Optimistic Concurrency Control
Technique. This protocol is used in DBMS (Database Management System)
for avoiding concurrency in transactions. It is called optimistic because of the
assumption it makes, i.e. very less interference occurs, therefore, there is no
need for checking while the transaction is executed.
In this technique, no checking is done while the transaction is been executed.
Until the transaction end is reached updates in the transaction are not applied
directly to the database. All updates are applied to local copies of data items
kept for the transaction. At the end of transaction execution, while execution
of the transaction, a validation phase checks whether any of transaction
updates violate serializability. If there is no violation of serializability the
transaction is committed and the database is updated; or else, the transaction is
updated and then restarted.
Optimistic Concurrency Control is a three-phase protocol. The three phases for
validation based protocol:

1. Read Phase:
Values of committed data items from the database can be read by a
transaction. Updates are only applied to local data versions.

2. Validation Phase:
Checking is performed to make sure that there is no violation of
serializability when the transaction updates are applied to the
database.

3. Write Phase:
On the success of the validation phase, the transaction updates are
applied to the database, otherwise, the updates are discarded and the
transaction is slowed down.

The idea behind optimistic concurrency is to do all the checks at once; hence
transaction execution proceeds with a minimum of overhead until the
validation phase is reached. If there is not much interference among
transactions most of them will have successful validation, otherwise, results
will be discarded and restarted later. These circumstances are not much
favourable for optimization techniques, since, the assumption of less
interference is not satisfied.
Validation based protocol is useful for rare conflicts. Since only local copies
of data are included in rollbacks, cascading rollbacks are avoided. This method
is not favourable for longer transactions because they are more likely to have
conflicts and might be repeatedly rolled back due to conflicts with short
transactions.
In order to perform the Validation test, each transaction should go through the
various phases as described above. Then, we must know about the following
three time-stamps that we assigned to transaction T i, to check its validity:
1. Start(Ti): It is the time when Ti started its execution.
2. Validation(Ti): It is the time when Ti just finished its read phase and begin
its validation phase.
3. Finish(Ti): the time when Ti end it’s all writing operations in the database
under write-phase.
Two more terms that we need to know are:
1. Write_set: of a transaction contains all the write operations that
Ti performs.
2. Read_set: of a transaction contains all the read operations that Ti performs.
In the Validation phase for transaction T i the protocol inspect that Ti doesn’t
overlap or intervene with any other transactions currently in their validation
phase or in committed. The validation phase for T i checks that for all
transaction Tj one of the following below conditions must hold to being
validated or pass validation phase:
1. Finish(Tj)<Starts(Ti), since Tj finishes its execution means completes its
write-phase before Ti started its execution(read-phase). Then the serializability
indeed maintained.
2. Ti begins its write phase after Tj completes its write phase, and the read_set
of Ti should be disjoint with write_set of T j.
3. Tj completes its read phase before Ti completes its read phase and both
read_set and write_set of Ti are disjoint with the write_set of T j.
Ex: Here two Transactions Ti and Tj are given, since TS(Tj)<TS(Ti) so the
validation phase succeeds in the Schedule-A. It’s noteworthy that the final
write operations to the database are performed only after the validation of both
Ti and Tj. Since Ti reads the old values
of x(12) and y(15) while print(x+y) operation unless final write operation take
place.
Schedule-A
Tj Ti

r(x) // x=12

r(x)

x=x-10
r(y) //y=15

y=y+10
r(x)

<validate>
print(x+y)

<validate>

w(x)
 Tj Ti

w(y)
Schedule-A is a validated schedule
Advantages:
1. Avoid Cascading-rollbacks: This validation based scheme avoid cascading
rollbacks since the final write operations to the database are performed only
after the transaction passes the validation phase. If the transaction fails then no
updation operation is performed in the database. So no dirty read will happen
hence possibilities cascading-rollback would be null.
2. Avoid deadlock: Since a strict time-stamping based technique is used to
maintain the specific order of transactions. Hence deadlock isn’t possible in
this scheme.
Disadvantages:
1. Starvation: There might be a possibility of starvation for long-term
transactions, due to a sequence of conflicting short-term transactions that cause
the repeated sequence of restarts of the long-term transactions so on and so
forth. To avoid starvation, conflicting transactions must be temporarily
blocked for some time, to let the long-term transactions to finish.

UNIT V

What is SQL?
SQL stands for Structured Query Language. SQL is a computer language used
to interact with relational database systems. SQL is a tool for organizing,
managing, and retrieving archived data from a computer database.
When data needs to be retrieved from a database, SQL is used to make the
request. The DBMS processes the SQL query retrieves the requested data and
returns it to us. Rather, SQL statements describe how a collection of data
should be organized or what data should be extracted or added to the database.
In common usage, SQL encompasses DDL and DML commands
for CREATE, UPDATE, MODIFY, or other operations on database
structure.

How SQL Works?

A server machine is used in the implementation of the structured query
language (SQL), processing database queries and returning results. The
following are some of the software elements that the SQL process goes
through.
Parser
The parser begins by replacing some of the words in the SQL statement with
unique symbols, a process known as tokenization. The statement is then
examined for the following:
Correctness
The parser checks to see if the SQL statement complies with the rules, or SQL
semantics, that guarantee the query statement’s accuracy. The parser, for
instance, looks to see if the SQL command ends with a semicolon. The parser
returns an error if the semi-colon is absent.
Authorization
The parser additionally confirms that the user executing the query has the
required permissions to alter the relevant data.
Relational Engine
The relational engine, also known as the query processor, develops a strategy
for efficiently retrieving, writing, or updating relevant data. For instance, it
looks for queries that are similar to others, uses earlier data manipulation
techniques, or develops a new one. Byte code, an intermediate-level
representation of the SQL statement, is used to write the plan. To efficiently
perform database searches and modifications, relational databases use byte
code.
Storage Engine
The software element that interprets the byte code and executes the intended
SQL statement is known as the storage engine, also known as the database
engine. The data in the database files on the physical disc storage is read and
stored. The storage engine delivers the outcome to the requesting application
after completion.

What is the Physical File System?

Physical files have the real data saved on an iSeries system and describe how the
data is to be displayed to or retrieved from a program. They only have one type
of record and one or more members. A field-level description or a record-level
description can be used to describe records in database files.
The field-level description defines the fields of the record. On the other hand, the
record-level description defines the length of the record. Program-described files
are database files that are prepared with record-level descriptions. Externally
specified files are database files that are prepared with field-level descriptions. It
means that the fields in the record must be described in your ILE C/C++ software.

Externally specified or an ILE C/C++ program can use program-described files.

If the program requires an externally described file, the ILE C/C++ compiler may
extract data from it and automatically add field information to it. The field
information does not need to be defined in your program. A keyed sequence
access path can exist for a physical file. It means that data is given to an ILE
C/C++ program in a specific sequence that is based on various key fields in the
file.

What is the Logical File System?

A logical file represents or perspectives multiple physical files. Logical files don't
have data. They have a description of the records found in multiple physical files.
Multi-format logical files are logical files that contain various formats.

If your program is processing a logical file with multiple record formats, you may
use the _Rformat() function to specify which format to use. Some operations on
logical files are not allowed. When you open a logical file for stream and record
file processing using the open modes W, W+, WB, WB+, WR, or WR+, the file is
opened but not cleared. A field-level description or a record-level description can
be used to describe records in iSeries database files.

The record's field-level description describes all fields and how they are
organized in this record. Database files built with a field-level description are
externally described because the fields' description and layout are preserved
within the database file rather than in your ILE C/C++ software.

Table Spaces in DBMS

A table space is a storage structure, it contains tables, indexes, large objects, and
long data. It can be used to organize data in a database into logical storage
group which is related with where data stored on a system. This tablespaces are
stored in database partition groups.

Benefits of tablespaces in database

The table spaces are beneficial in database in various ways given as follows:

Recoverability: Tablespaces make backup and restore operations more

convenient. Using a single command, you can make backup or restore all the
database objects in tablespaces.

Automatic storage Management: Database manager creates and extends

containers depending on the needs.
Memory utilization: A single bufferpool can manage multiple tablespaces. You
can assign temporary tablespaces to their own bufferpool to increase the
performance of activities such as sorts or joins.

Container

Tablespaces contains one or more containers. A container can be a directory

name, a device name, or a filename. In a database, a single tablespace can have
several containers on the same physical storage device. If the tablespace is
created with automatic storage tablespace option, the creation and management
of containers is handled automatically by the database manager. If it is not
created with automatic storage tablespace option, you need to define and
manage the containers yourself.

Default tablespaces

When you create a new database, the database manager creates some default
tablespaces for database. These tablespace is used as a storage for user and
temporary data. Each database must contain at least three tablespaces as given
here:

1. Catalog tablespace
2. User tablespace
3. Temporary tablespace

Catalog tablespace: It contains system catalog tables for the database. It is

named as SYSCATSPACE and it cannot be dropped.

User tablespace: This tablespace contains user-defined tables. In a database, we

have one default user tablespace, named as USERSPACE1. If you do not
specify user-defined tablespace for a table at the time you create it, then the
database manager chooses default user tablespace for you.

Temporary tablespace: A temporary tablespace contains temporary table data.

This tablespace contains system temporary tablespaces or user temporary
tablespace.

System temporary tablespace holds temporary data required by the database

manager while performing operation such as sorts or joins. A database must
have at least one system temporary tablespace and it is named as
TEMPSPACE1. It is created at the time of creating the database. User
temporary tablespace holds temporary data from tables. It is created with
DECLARE GLOBAL TEMPORARY TABLE or CREATE GLOBAL
TEMPORARY TABLE statement. This temporary tablespace is not created by
default at the time of database creation.

Tablespaces and storage management:

Tablespaces can be setup in different ways, depending on how you want to use
them. You can setup the operating system to manage tablespace allocation, you
can let the database manager allocate space or you can choose automatic
allocation of tablespace for your data.

The following three types of managed spaces are available:

System Managed Space (SMS): The operating system’s file system manager
allocates and manages the space where the table is stored. Storage space is
allocated on demand. This model consists of files representing database objects.
This tablespace type has been deprecated in Version 10.1 for user-defined
tablespaces, and it is not deprecated for catalog and temporary tablespaces.

Database Managed Space (DMS): The Database Server controls the storage
space. Storage space is pre- allocated on the file system based on container
definition that you specify when you create the DMS table space. It is
deprecated from version 10.1 fix pack 1 for user-defined tablespaces, but it is
not deprecated for system tablespace and temporary tablespace.

Automatic Storage Tablespace: Database server can be managed

automatically. Database server creates and extends containers depend on data
on database. With automatic storage management, it is not required to provide
container definitions. The database server looks after creating and extending
containers to make use of the storage allocated to the database. If you add
storage space to a storage group, new containers are automatically created when
the existing container reach their maximum capacity. If you want to use the
newly-added storage immediately, you can rebalance the tablespace.

Page, table and tablespace size:

Temporary DMS and automatic storage tablespaces, the page size you choose
for your database determines the maximum limit for the tablespace size. For
table SMS and temporary automatic storage tablespaces, the page size
constrains the size of table itself. The page sizes can be 4kb, 8kb, 16kb or 32kb.

4K page
Tablespace 8K page size 32K page
size 16K page size limit
type limit size limit
limit

DMS, non-
temporary
automatic
64G 128G 256G 512G
storage
tablespace
regular

DMS,
temporary DMS 1892G 16384G 32768G 65536G
and non-
 temporary
automatic
storage table
space large

Introduction to Data Blocks, Extents, and Segments

The units of database space allocation are data blocks, extents, and
segments. Figure 2-1 shows the relationships among these data structures:

Figure 2-1 The Relationships Among Segments, Extents, and Data Blocks

At the finest level of granularity, DBMS stores data in data blocks (also
called logical blocks, DBMS blocks, or pages). One data block corresponds to
a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific

number of contiguous data blocks allocated for storing a specific type of
information.

The level of logical database storage above an extent is called a segment. A

segment is a set of extents, each of which has been allocated for a specific data
structure and all of which are stored in the same tablespace. For example, each
table's data is stored in its own data segment, while each index's data is stored
in its own index segment. If the table or index is partitioned, each partition is
stored in its own segment.

DBMS allocates space for segments in units of one extent. When the existing
extents of a segment are full, DBMS allocates another extent for that segment.
Because extents are allocated as needed, the extents of a segment may or may
not be contiguous on disk.

A segment and all its extents are stored in one tablespace. Within a tablespace, a
segment can include extents from more than one file; that is, the segment can
span datafiles. However, each extent can contain data from only one datafile.

Although you can allocate additional extents, the blocks themselves are
allocated separately. If you allocate an extent to a specific instance, the blocks
are immediately allocated to the free list. However, if the extent is not allocated
to a specific instance, then the blocks themselves are allocated only when the
high water mark moves. The high water mark is the boundary between used
and unused space in a segment.

Data Block Format

The DBMS data block format is similar regardless of whether the data block
contains table, index, or clustered data. Figure 2-2 illustrates the format of a
data block.

Figure 2-2 Data Block Format

Header (Common and Variable)

The header contains general block information, such as the block address and
the type of segment (for example, data or index).
Table Directory

This portion of the data block contains information about the table having rows
in this block.

Row Directory

This portion of the data block contains information about the actual rows in the
block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's
overhead, this space is not reclaimed when the row is deleted. Therefore, a
block that is currently empty but had up to 50 rows at one time continues to'
have 100 bytes allocated in the header for the row directory. DBMS reuses this
space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to
collectively as overhead. Some block overhead is fixed in size; the total block
overhead size is variable. On average, the fixed and variable portions of data
block overhead total 84 to 107 bytes.

Row Data

This portion of the data block contains table or index data. Rows can span
blocks.

Extents Overview

An extent is a logical unit of database storage space allocation made up of a

number of contiguous data blocks. One or more extents in turn make up a
segment. When the existing space in a segment is completely used, DBMS
allocates a new extent for the segment.

When Extents Are Allocated

When you create a table, DBMS allocates to the table's data segment an initial
extent of a specified number of data blocks. Although no rows have been
inserted yet, the DBMS data blocks that correspond to the initial extent are
reserved for that table's rows.

If the data blocks of a segment's initial extent become full and more space is
required to hold new data, DBMS automatically allocates an incremental
extent for that segment. An incremental extent is a subsequent extent of the
same or greater size than the previously allocated extent in that segment.

For maintenance purposes, the header block of each segment contains a

directory of the extents in that segment.
 Note:

This chapter applies to serial operations, in which one server

process parses and runs a SQL statement. Extents are allocated
somewhat differently in parallel SQL statements, which entail
multiple server processes.

Determine the Number and Size of Extents

Storage parameters expressed in terms of extents define every segment.

Storage parameters apply to all types of segments. They control how DBMS
allocates free database space for a given segment. For example, you can
determine how much space is initially reserved for a table's data segment or you
can limit the number of extents the table can allocate by specifying the storage
parameters of a table in the STORAGE clause of
the CREATE TABLE statement. If you do not specify a table's storage
parameters, then it uses the default storage parameters of the tablespace.

Prior to DBMS8i, all tablespaces were created as dictionary managed.

Dictionary managed tablespaces rely on data dictionary tables to track space
utilization. Beginning with DBMS8i, you could create locally managed
tablespaces, which use bitmaps (instead of data dictionary tables) to track used
and free space. Because of the better performance and greater ease of
management of locally managed tablespaces, the default for non-
SYSTEM permanent tablespaces is locally managed whenever the type of
extent management is not explicitly specified.

A tablespace that manages its extents locally can have either uniform extent
sizes or variable extent sizes that are determined automatically by the system.
When you create the tablespace,
the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the
type of allocation.

• For system-managed extents, you can specify the size of the initial extent
and DBMS determines the optimal size of additional extents, with a
minimum extent size of 64 KB. This is the default for permanent
tablespaces.
• For uniform extents, you can specify an extent size or use the default size,
which is 1 MB. Temporary tablespaces that manage their extents locally
can only use this type of allocation.

The storage
parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS,
and DEFAULT STORAGE are not valid for extents that are managed locally.
How Extents Are Allocated

DBMS uses different algorithms to allocate extents, depending on whether they

are locally managed or dictionary managed.

With locally managed tablespaces, DBMS looks for free space to allocate to a
new extent by first determining a candidate datafile in the tablespace and then
searching the datafile's bitmap for the required number of adjacent free blocks.
If that datafile does not have enough adjacent free space, then DBMS looks in
another datafile.

When Extents Are Deallocated

In general, the extents of a segment do not return to the tablespace until you
drop the schema object whose data is stored in the segment (using
a DROP TABLE or DROP CLUSTER statement). Exceptions to this include
the following:

• The owner of a table or cluster, or a user with

the DELETE ANY privilege, can truncate the table or cluster with
a TRUNCATE...DROP STORAGE statement.
• A database administrator (DBA) can deallocate unused extents using the
following SQL syntax:
• ALTER TABLE table_name DEALLOCATE UNUSED;
• Periodically, DBMS deallocates one or more extents of a rollback
segment if it has the OPTIMAL size specified.

When extents are freed, DBMS modifies the bitmap in the datafile (for locally
managed tablespaces) or updates the data dictionary (for dictionary managed
tablespaces) to reflect the regained extents as available space. Any data in the
blocks of freed extents becomes inaccessible.

Segments Overview

A segment is a set of extents that contains all the data for a specific logical
storage structure within a tablespace. For example, for each table, DBMS
allocates one or more extents to form that table's data segment, and for each
index, DBMS allocates one or more extents to form its index segment.

Introduction to Data Segments

A single data segment in an DBMS database holds all of the data for one of the
following:

• A table that is not partitioned or clustered

• A partition of a partitioned table
• A cluster of tables
DBMS creates this data segment when you create the table or cluster with
the CREATE statement.

The storage parameters for a table or cluster determine how its data segment's
extents are allocated. You can set these storage parameters directly with the
appropriate CREATE or ALTER statement. These storage parameters affect the
efficiency of data retrieval and storage for the data segment associated with the
object.

Introduction to Index Segments

Every nonpartitioned index in an DBMS database has a single index segment to

hold all of its data. For a partitioned index, every partition has a single index
segment to hold its data.

DBMS creates the index segment for an index or an index partition when you
issue the CREATE INDEX statement. In this statement, you can specify storage
parameters for the extents of the index segment and a tablespace in which to
create the index segment. (The segments of a table and an index associated with
it do not have to occupy the same tablespace.) Setting the storage parameters
directly affects the efficiency of data retrieval and storage.

Introduction to Temporary Segments

When processing queries, DBMS often requires temporary workspace for

intermediate stages of SQL statement parsing and execution. DBMS
automatically allocates this disk space called a temporary segment. Typically,
DBMS requires a temporary segment as a work area for sorting. DBMS does
not create a segment if the sorting operation can be done in memory or if
DBMS finds some other way to perform the operation using indexes.

Operations that Require Temporary Segments

The following statements sometimes require the use of a temporary segment:

• CREATE INDEX
• SELECT ... ORDER BY
• SELECT DISTINCT ...
• SELECT ... GROUP BY
• SELECT ... UNION
• SELECT ... INTERSECT
• SELECT ... MINUS

Some unindexed joins and correlated subqueries can require use of a temporary
segment. For example, if a query contains a DISTINCT clause, a GROUP BY,
and an ORDER BY, DBMS can require as many as two temporary segments. If
applications often issue statements in the previous list, the database
administrator can improve performance by adjusting the initialization
parameter SORT_AREA_SIZE.

Segments in Temporary Tables and Their Indexes

DBMS can also allocate temporary segments for temporary tables and indexes
created on temporary tables. Temporary tables hold data that exists only for the
duration of a transaction or session.

How Temporary Segments Are Allocated

DBMS allocates temporary segments differently for queries and temporary

tables.

Allocation of Temporary Segments for Queries

DBMS allocates temporary segments as needed during a user session in the

temporary tablespace of the user issuing the statement. Specify this tablespace
with a CREATE USER or an ALTER USER statement using
the TEMPORARY TABLESPACE clause.

Note:

You cannot assign a permanent tablespace as a user's temporary

tablespace.

If no temporary tablespace is defined for the user, then the default temporary
tablespace is the SYSTEM tablespace. The default storage characteristics of the
containing tablespace determine those of the extents of the temporary segment.
DBMS drops temporary segments when the statement completes.

Because allocation and deallocation of temporary segments occur frequently,

create a special tablespace for temporary segments. By doing so, you can
distribute I/O across disk devices, and you can avoid fragmentation of
the SYSTEM and other tablespaces that otherwise hold temporary segments.

Note:

When the SYSTEM tablespace is locally managed, you must

define a default temporary tablespace when creating a database.
A locally managed SYSTEM tablespace cannot be used for
default temporary storage.
Entries for changes to temporary segments used for sort operations are not
stored in the redo log, except for space management operations on the
temporary segment.

Allocation of Temporary Segments for Temporary Tables and Indexes

DBMS allocates segments for a temporary table when the first INSERT into
that table is issued. (This can be an internal insert operation issued
by CREATE TABLE AS SELECT.) The first INSERT into a temporary table
allocates the segments for the table and its indexes, creates the root page for the
indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in the temporary tablespace of the
user who created the temporary table.

DBMS drops segments for a transaction-specific temporary table at the end of

the transaction and drops segments for a session-specific temporary table at the
end of the session. If other transactions or sessions share the use of that
temporary table, the segments containing their data remain in the table.

What is a Dedicated Server DBMS?

A dedicated server is a type of web hosting where an entire server is rented for
a specific individual or organization. Unlike shared hosting, where multiple
clients share a single server, dedicated servers provide access to all the server's
resources. This is especially beneficial for running a DBMS, which requires
substantial resources for optimal performance.

Key Features of Dedicated Server DBMS

• Enhanced Performance: With a dedicated server, you have the full

capacity of the hardware, meaning that your database operations will run
faster.
• Increased Security: Dedicated servers offer better security as you're not
sharing resources with any other clients.
• Customizability: Users have the freedom to install and configure any
software they need.
• Full Control: Complete administrative access allows users to manage
settings, security protocols, and other important features.

Benefits of Using a Dedicated Server for DBMS

Utilizing a dedicated server for your Database Management System comes with
numerous advantages:
1. Performance

When your database is on a dedicated server, it can handle larger volumes of

data and transactions. This leads to improved response times and better user
experience.

2. Scalability

As your business grows, your database demands will increase. A dedicated

server can easily be upgraded with additional resources such as CPU, RAM, and
storage to accommodate your growing needs.

3. Security

Data breaches can be catastrophic for any organization. A dedicated server

offers advanced security features, including firewalls, anti-virus protection, and
data encryption, ensuring your database is secure from unauthorized access.

4. Flexibility

The flexibility of a dedicated server means you can choose your

preferred DBMS software and configurations. Whether you're using MySQL,
PostgreSQL, or SQL Server, you have the freedom to create the environment
that suits your needs best.

How to Choose the Right Dedicated Server for DBMS

When selecting a dedicated server, consider these critical factors:

1. Hardware Specifications

Ensure that the server has high-performance components (CPU, RAM, and SSD
storage) to meet your workload demands.

2. Bandwidth

Evaluate your bandwidth needs. A dedicated server should offer more than
enough bandwidth to handle your traffic without any slowdowns.

3. Support Services

Look for a provider that offers 24/7 support. Quick and efficient customer
service is crucial when managing a dedicated server.
4. Backup Solutions

A good backup solution is imperative. Make sure your provider offers reliable
and automated backup services to protect your data.

Popular DBMS for Dedicated Servers

Here are some of the most popular DBMS choices for dedicated servers:

1. MySQL: Ideal for web applications, it’s open-source, robust, and widely
used.
2. PostgreSQL: Known for its advanced features, it supports a wide range
of data types and complex queries.
3. SQL Server: Microsoft's answer to database management; it is feature-
rich and integrates well with other Microsoft services.
4. Oracle Database: A powerful relational database widely used by large
enterprises for its scalability and performance.
5. SQLite: While typically used for smaller databases, it can be quite
effective in specific environments.

Top Use Cases for Dedicated Server DBMS

Dedicated server DBMS is suitable for various applications:

• E-commerce: Handle large amounts of customer and transaction data

efficiently.
• Big Data Analysis: Process and store vast amounts of data quickly.
• Mobile Applications: Provide reliable and fast back-end database
services.
• Web Applications: Run database-intensive web apps that require high
performance.
•

Distributed Database System

A distributed database is basically a database that is not limited to one system,
it is spread over different sites, i.e, on multiple computers or over a network of
computers. A distributed database system is located on various sites that don’t
share physical components. This may be required when a particular database
needs to be accessed by various users globally. It needs to be managed such
that for the users it looks like one single database.
Types:
1. Homogeneous Database:
In a homogeneous database, all different sites store database identically. The
operating system, database management system, and the data structures used –
all are the same at all sites. Hence, they’re easy to manage.
2. Heterogeneous Database:
In a heterogeneous distributed database, different sites can use different
schema and software that can lead to problems in query processing and
transactions. Also, a particular site might be completely unaware of the other
sites. Different computers may use a different operating system, different
database application. They may even use different data models for the
database. Hence, translations are required for different sites to communicate.

Distributed Data Storage :

There are 2 ways in which data can be stored on different sites. These are:
1. Replication –
In this approach, the entire relationship is stored redundantly at 2 or more
sites. If the entire database is available at all sites, it is a fully redundant
database. Hence, in replication, systems maintain copies of data.
This is advantageous as it increases the availability of data at different sites.
Also, now query requests can be processed in parallel.
However, it has certain disadvantages as well. Data needs to be constantly
updated. Any change made at one site needs to be recorded at every site that
relation is stored or else it may lead to inconsistency. This is a lot of overhead.
Also, concurrency control becomes way more complex as concurrent access
now needs to be checked over a number of sites.
2. Fragmentation –
In this approach, the relations are fragmented (i.e., they’re divided into smaller
parts) and each of the fragments is stored in different sites where they’re
required. It must be made sure that the fragments are such that they can be
used to reconstruct the original relation (i.e, there isn’t any loss of data).
Fragmentation is advantageous as it doesn’t create copies of data, consistency
is not a problem.

Fragmentation of relations can be done in two ways:

• Horizontal fragmentation – Splitting by rows –

The relation is fragmented into groups of tuples so that each tuple is
assigned to at least one fragment.
• Vertical fragmentation – Splitting by columns –
The schema of the relation is divided into smaller schemas. Each
fragment must contain a common candidate key so as to ensure a
lossless join.
In certain cases, an approach that is hybrid of fragmentation and replication is
used.
Applications of Distributed Database:
• It is used in Corporate Management Information System.
• It is used in multimedia applications.
• Used in Military’s control system, Hotel chains etc.
• It is also used in manufacturing control system.
•
Advantages of Distributed Database System :
1) There is fast data processing as several sites participate in request
processing.
2) Reliability and availability of this system is high.
3) It possess reduced operating cost.
4) It is easier to expand the system by adding more sites.
5) It has improved sharing ability and local autonomy.
Disadvantages of Distributed Database System :
1) The system becomes complex to manage and control.
2) The security issues must be carefully managed.
3) The system requires deadlock handling during the transaction processing
otherwise
the entire system may be in inconsistent state.
4) There is need of some standardization for processing of distributed database
system.
 SQL NOTES

Introduction to SQL

SQL is a standard language for accessing and manipulating databases.

What is SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition
Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database

• UPDATE - updates data in a database
• DELETE - deletes data from a database
• ISERT ITO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify
links between tables, and impose constraints between tables. The most important DDL statements in SQL
are:

• CREATE DATABASE - creates a new database

• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
 • CREATE IDEX - creates an index (search key)
• DROP IDEX - deletes an index

The SQL SELECT Statement

The SELECT statement is used to select data from a database. The result is stored in a result table, called the
result-set.

SQL SELECT Syntax

SELECT column_name(s)
FROM table_name

and

SELECT * FROM table_name

ote: SQL is not case sensitive. SELECT is the same as select.

An SQL SELECT Example

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to select the content of the columns named "LastName" and "FirstName" from the table
above.

We use the following SELECT statement:

SELECT LastName,FirstName FROM Persons

The result-set will look like this:

LastName FirstName

Hansen Ola

Svendson Tove

Pettersen Kari

SELECT * Example
Now we want to select all the columns from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

Tip: The asterisk (*) is a quick way of selecting all columns!

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

SQL Syntax using WHERE condition

SELECT Company, Country FROM Customers WHERE Country = 'USA'

SQL Result

Company Country
Island Trading UK
Galería del gastrónomo Spain
Laughing Bacchus Wine Cellars Canada
Paris spécialités France
Simons bistro Denmark
Wolski Zajazd Poland

The SQL SELECT DISTICT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes
you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTICT Example

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to select only the distinct values from the column named "City" from the table above.

We use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result-set will look like this:

City

Sandnes

Stavanger
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

WHERE Clause Example

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to select only the persons living in the city "Sandnes" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City='Sandnes'

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).

Although, numeric values should not be enclosed in quotes.

For text values:

This is correct:

SELECT * FROM Persons WHERE FirstName='Tove'

This is wrong:

SELECT * FROM Persons WHERE FirstName=Tove

For numeric values:

This is correct:

SELECT * FROM Persons WHERE Year=1965

This is wrong:

SELECT * FROM Persons WHERE Year='1965'

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

ote: In some versions of SQL the <> operator may be written as !=

The AD & OR Operators
The AD & OR operators are used to filter records based on more than one condition

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

AD Operator Example

The "Persons" table:

P_Id Lastame Firstame Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to
"Svendson":

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName='Tove'
AND LastName='Svendson'

The result-set will look like this:

P_Id Lastame Firstame Address City

2 Svendson Tove Borgvn 23 Sandnes

OR Operator Example
Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to
"Ola":

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName='Tove'
OR FirstName='Ola'

The result-set will look like this:

P_Id Lastame Firstame Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes

Combining AD & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to
"Tove" OR to "Ola":

We use the following SELECT statement:

SELECT * FROM Persons WHERE

LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

The result-set will look like this:

P_Id Lastame Firstame Address City

2 Svendson Tove Borgvn 23 Sandnes

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set.

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC
ORDER BY Example
The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Tom Vingvn 23 Stavanger

Now we want to select all the persons from the table above, however, we want to sort the persons by their
last name.

We use the following SELECT statement:

SELECT * FROM Persons

ORDER BY LastName

The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

4 Nilsen Tom Vingvn 23 Stavanger

3 Pettersen Kari Storgt 20 Stavanger

2 Svendson Tove Borgvn 23 Sandnes

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons
descending by their last name.

We use the following SELECT statement:

SELECT * FROM Persons

ORDER BY LastName DESC
The result-set will look like this:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Tom Vingvn 23 Stavanger

1 Hansen Ola Timoteivn 10 Sandnes

The ISERT ITO Statement

The INSERT INTO statement is used to insert a new row in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name

VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

SQL ISERT ITO Example

We have the following "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

INSERT INTO Persons

VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.

The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the
"FirstName" columns:

INSERT INTO Persons (P_Id, LastName, FirstName)

VALUES (5, 'Tjessem', 'Jakob')

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

5 Tjessem Jakob
The UPDATE Statement
The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

ote: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or
records that should be updated. If you omit the WHERE clause, all records will be updated!

SQL UPDATE Example

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

5 Tjessem Jakob

Now we want to update the person "Tjessem, Jakob" in the "Persons" table.

We use the following SQL statement:

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

5 Tjessem Jakob Nissestien 67 Sandnes

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'

The "Persons" table would have looked like this:

P_Id LastName FirstName Address City

1 Hansen Ola Nissestien 67 Sandnes

2 Svendson Tove Nissestien 67 Sandnes

3 Pettersen Kari Nissestien 67 Sandnes

4 Nilsen Johan Nissestien 67 Sandnes

5 Tjessem Jakob Nissestien 67 Sandnes

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value

ote: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or
records that should be deleted. If you omit the WHERE clause, all records will be deleted!
SQL DELETE Example
The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

5 Tjessem Jakob Nissestien 67 Sandnes

Now we want to delete the person "Tjessem, Jakob" in the "Persons" table.

We use the following SQL statement:

DELETE FROM Persons

WHERE LastName='Tjessem' AND FirstName='Jakob'

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

4 Nilsen Johan Bakken 2 Stavanger

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure,
attributes, and indexes will be intact:

DELETE FROM table_name or

DELETE * FROM table_name

ote: Be very careful when deleting records. You cannot undo this statement!
The CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

CREATE DATABASE Example

Now we want to create a database called "my_db".

We use the following CREATE DATABASE statement:

CREATE DATABASE my_db

Database tables can be added with the CREATE TABLE statement.

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

The data type specifies what type of data the column can hold.

CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName,
Address, and City.

We use the following CREATE TABLE statement:

CREATE TABLE Persons

(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City
columns are of type varchar with a maximum length of 255 characters.

The empty "Persons" table will now look like this:

P_Id LastName FirstName Address City

SQL Constraints
Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table
is created (with the ALTER TABLE statement).

We will focus on the following constraints:

• OT ULL- The NOT NULL constraint enforces a column to NOT accept NULL values. The
NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert
a new record, or update a record without adding a value to this field.
• UIQUE- The UNIQUE constraint uniquely identifies each record in a database [Link]
UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or
set of columns.A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on
[Link] that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
• PRIMARY KEY- The PRIMARY KEY constraint uniquely identifies each record in a database
[Link] keys must contain unique values.A primary key column cannot contain NULL
[Link] table should have a primary key, and each table can have only one primary key.
• CHECK- The CHECK constraint is used to limit the value range that can be placed in a [Link]
you define a CHECK constraint on a single column it allows only certain values for this [Link]
you define a CHECK constraint on a table it can limit the values in certain columns based on values
in other columns in the row.
• DEFAULT- The DEFAULT constraint is used to insert a default value into a [Link] default
value will be added to all new records, if no other value is specified.
The ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow
deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

SQL ALTER TABLE Example

Look at the "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ADD DateOfBirth date

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type
specifies what type of data the column can hold
The "Persons" table will now like this:

P_Id LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ALTER COLUMN DateOfBirth year

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or four-
digit format.

DROP COLUM Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

DROP COLUMN DateOfBirth

The "Persons" table will now like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

SQL Joins (Inner, Left, Right and Full Join)
SQL Join operation combines data or rows from two or more tables based on
a common field between them.
In this article, we will learn about Joins in SQL, covering JOIN types, syntax,
and examples.
SQL JOIN
SQL JOIN clause is used to query and access data from multiple tables by
establishing logical relationships between them. It can access data from
multiple tables simultaneously using common key values shared across
different tables.
We can use SQL JOIN with multiple tables. It can also be paired with other
clauses, the most popular use will be using JOIN with WHERE clause to
filter data retrieval.
SQL JOIN Example
Consider the two tables below as follows:
Student:

StudentCourse :

Both these tables are connected by one common key (column) i.e ROLL_NO.
We can perform a JOIN operation using the given SQL query:
SELECT s.roll_no, [Link], [Link], [Link], [Link], sc.course_id
FROM Student s
JOIN StudentCourse sc ON s.roll_no = sc.roll_no;
Output:
ROLL_N AG COURSE_
NAME ADDRESS PHONE
O E ID

XXXXXXXX
1 HARSH DELHI 18 1
XX

XXXXXXXX
2 PRATIK BIHAR 19 2
XX

RIYANK XXXXXXXX
3 SILGURI 20 2
A XX

RAMNAG XXXXXXXX
4 DEEP 18 3
AR XX

SAPTAR XXXXXXXX
5 KOLKATA 19 1
HI XX

Types of JOIN in SQL

There are many types of Joins in SQL. Depending on the use case, you can use
different type of SQL JOIN clause.
Here are the frequently used SQL JOIN types:
Table of Content
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• Natural join
SQL INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the
condition is satisfied. This keyword will create the result-set by combining all
rows from both the tables where the condition satisfies i.e value of the common
field will be the same.
Syntax:
The syntax for SQL INNER JOIN is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
• table1: First table.
• table2: Second table
• matching_column: Column common to both the tables.
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as
INNER JOIN.

INNER JOIN Example

Let’s look at the example of INNER JOIN clause, and understand it’s working.
This query will show the names and age of students enrolled in different
courses.
SELECT StudentCourse.COURSE_ID, [Link], [Link] FROM
Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:
SQL LEFT JOIN
LEFT JOIN returns all the rows of the table on the left side of the join and
matches rows for the table on the right side of the join. For the rows for which
there is no matching row on the right side, the result-set will contain null.
LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
The syntax of LEFT JOIN in SQL is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
• table1: First table.
• table2: Second table
• matching_column: Column common to both the tables.
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are
the same.

LEFT JOIN Example

Let’s look at the example of LEFT JOIN clause, and understand it’s working
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
SQL RIGHT JOIN
RIGHT JOIN returns all the rows of the table on the right side of the join and
matching rows for the table on the left side of the [Link] is very similar to
LEFT JOIN For the rows for which there is no matching row on the left side,
the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER
JOIN.
Syntax:
The syntax of RIGHT JOIN in SQL is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
• table1: First table.
• table2: Second table
• matching_column: Column common to both the tables.
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both
are the same.

RIGHT JOIN Example:

Let’s look at the example of RIGHT JOIN clause, and understand it’s working
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
SQL FULL JOIN
FULL JOIN creates the result-set by combining results of both LEFT JOIN
and RIGHT JOIN. The result-set will contain all the rows from both tables.
For the rows for which there is no matching, the result-set will
contain NULL values.

Syntax
The syntax of SQL FULL JOIN is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
• table1: First table.
• table2: Second table
• matching_column: Column common to both the tables.
FULL JOIN Example
Let’s look at the example of FULL JOIN clause, and understand it’s working
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 2

DEEP 3

SAPTARHI 1

DHANRAJ NULL

ROHIT NULL

NIRAJ NULL

NULL 4

NULL 5

NULL 4
SQL Natural join (?)
Natural join can join tables based on the common columns in the tables being
joined. A natural join returns all rows by matching values in common columns
having same name and data type of columns and that column should be present
in both tables.
Both table must have at least one common column with same column name and
same data type.
The two table are joined using Cross join.
DBMS will look for a common column with same name and data type Tuples
having exactly same values in common columns are kept in result.
Natural join Example:
Look at the two tables below- Employee and Department
 Employee

Emp_id Emp_name Dept_id

1 Ram 10

2 Jon 30

3 Bob 50

Department

Dept_id Dept_name

10 IT

30 HR

40 TIS

Problem: Find all Employees and their respective departments.

Solution Query: (Employee) ? (Department)
Emp_id Emp_name Dept_id Dept_id Dept_name

1 Ram 10 10 IT

2 Jon 30 30 HR

Employee data Department data