This is actually all because of centripetal acceleration and inertia. You see that when the bucket spins, it actually tries to go straight, however the rope attached restricts this and makes it go in a circle. This makes the water to be pinned against the bottom of the bucket.