In general, conditions numbers represent the limiting behavior of the worst case scenario. If the condition number is small, then the function is insensitive to sufficiently small changes of input. If the condition number is large, then it is possible to find a small change of the input, which will change the output dramatically.
To be precise, let us consider the proper definition of the relative condition number $\kappa_f(x)$ for a function $f : \mathbb{R} \rightarrow \mathbb{R}$ at a point $x \in \mathbb{R}$.
We begin by defining an auxiliary function $\kappa$ given by
\begin{equation}
\kappa_f( x, \delta) = \sup \left\{ \left| \frac{f(x) - f(y)}{f(x)} \right| \big{/} \left| \frac{x-y}{x} \right| \: : \: 0 < |x-y| < \delta|x| \right\}.
\end{equation}
It is straightforward to verify that $\delta \rightarrow \kappa_f(x,\delta)$ is a nonnegative and nondecreasing function of $\delta$. This implies that the limit
\begin{equation}
\underset{\delta \rightarrow 0_+}{\lim} \kappa_f(x,\delta)
\end{equation}
exists and is nonnegative. Since we are primarily interested in small values of $|x-y|/|x|$ we will use this limit to characterize the sensitivity of $f(x)$ to perturbations of $x$ which are small relative to $x$. This is why we formally define
$$ \kappa_f(x) = \underset{\delta \rightarrow 0_+}{\lim} \kappa_f(x,\delta).$$
We will now discuss how the relative condition number imposes a hard limit on the accuracy which can be achieved when using $f(y)$ to approximate $f(x)$.
Let $\delta > 0$ and let $y \in \mathbb{R}$ be any number such that $0 < |x-y| < \delta|x|$. Then
\begin{equation}
\left|\frac{f(x) - f(y)}{f(x)} \right| \leq \kappa_f(x,\delta)\left|\frac{x-y}{x} \right| \leq \kappa_f(x,\delta) \delta.
\end{equation}
Moreover, if $\delta$ is sufficiently small, then
\begin{equation}
\kappa_f(x,\delta) \approx \kappa_f(x)
\end{equation}
is a good approximation. It follows that we cannot expect that the relative error is smaller than
\begin{equation}
\left| \frac{f(x) - f(y)}{f(x)}\right| \approx \kappa_f(x)\left|\frac{x-y}{x}\right|.
\end{equation}
It is painful to compute the condition number directly from the definition. However, if $f$ is also differentiable, then
$$ \kappa_f(x) = \left| \frac{xf'(x)}{f(x)} \right|.$$
In particular, if $f : (0,\infty) \rightarrow \mathbb{R}$ is given by $$f(x) = x^p,$$ where $p \ge 0$, then $$ \forall x \in (0, \infty) \: : \: \kappa_f(x) = p.$$
In the case of $p \in [0,1)$ we see that the application of $f$ diminishes the relative error on the input and produces output which is more accurate than the input. This is as good as it gets and such a function $f$ certainly deserves to be classified as well-conditioned in the relative sense.
