Rotating the bucket in circular motion without spilling water

I want to focus on one thing, here. The nature of the normal force.

You write

Doesn't normal force oppose any other force?

which is a easy impression to get when you are introduced to the normal force in the context of things sitting on other things in a gravitational field, but that's not the best way to think about it.

The normal force keeps things from occupying the same space.

So consider a book sitting on the lab bench. It's subject to gravity, and in the absence of other forces would fall. But to fall it would have to occupy the same space as the solid top of the bench. The normal force is the interaction between to two objects that resists their inter-penetrating. In this case it has to provide a force that is equal and opposite to that of gravity to make that happen.

In the case of the water in the bucket, it's inertia would take it in a straight line, gravity modifies that into a parabola, but the sides and bottom of the bucket are moving in a circle for both those things to be true (the bucket goes in a circle and the water goes in a parabola) the water would have to move through the bottom of the bucket. The normal force serves to prevent the water from penetrating the solid material of the bucket and must supply whatever forces (beyond that of gravity) are necessary to cause the motion to be in a circle.

Imagine a scenario where the bucket is rotated at just the right speed so that the centripetal acceleration required to keep the water on a circular path is exactly 9.81 $ms^{-1}$. Then at the top of the rotation, all the centripetal acceleration is supplied by gravity.

However the bucket might be rotating faster in any given scenario but it still rotates on a fixed radius. Since the force required to maintain circular motion is given by

$F=\frac{mv^2}{r}$

the force needs to be greater at higher velocities if the radius is constant. This means that gravity is no longer able to supply all the acceleration required to keep the water inside the bucket on its circular path.

The water wants to move to a larger radius but it cannot because the bucket is in the way. Hence the water feels a reaction force from the bucket. This contact force is simply the force required to hold the water at a given radius minus the force of gravity:

$F_N=\frac{mv^2}{r}-F_g$

Another way to think about it is if you desperately tried to move through a wall the wall would exert a force on you and the force would increase the harder you tried.

In the bucket experiment when the bucket reaches the top of the circle why will it have a normal force acting on the water downwards?

The normal contact force is exerted by the bottom of the bucket as explicitly mentioned by the author. So, it is acting downward when the bucket is inverted.

Doesn't normal force oppose any other force? There is no force acting upwards

hmm... as said by Dirocology the bucket is forced by the contact force from the liquid; the normal force is reactive to that force.

Normal force plays the role of centripetal force when the velocity of the liquid is greater than $\sqrt{rg}\;.$